[freetds] Bulkcopy in ct-lib

Lowden, James K LowdenJK at bernstein.com
Wed Feb 4 16:05:54 EST 2004


> From: Thompson, Bill D (London) [mailto:bill_d_thompson at ml.com]
> Sent: February 4, 2004 11:11 AM
> 
> > Do you still want me to add src/tds/bcp.c?  
> 
> yes - at the least it could hold the wire handling code that 
> currently is in
> dblib/bcp.c - I'd prefer to call it src/tds/bulk.c though...

OK.  

> We already know that we may have problems with partial packets being
> submitted to the wire, because we may find an error as we process each
> column.
> So, it would make sense for us to construct the "row" buffer 
> beforehand (as
> we do with TDS 5.0).

Short answer: I reread _bcp_read_hostfile().  It constructs a row buffer; _bcp_exec_in() sends it.  

Long answer:

Whenever I think about this, I think about Freddy.  As a design, building a row image and shipping it is attractive; I've said before that the file uploading functions could be implemented terms of bcp_bind(), et. al.  But Freddy comes to mind because he's always concerned with text and binary datatypes, and copies thereof, and what happens when the varbinary column reads in a 4 GB file?  You obviously can't build a row image in such a case; you have to spoon-feed the data packet-by-packet to the server, until EOF (in effect).  

I implemented a partial solution to this with tds_iconv_fread(), but that function is called from _bcp_read_hostfile(), which allocates for it an output buffer large enough to hold the whole column after passing the data through iconv().  Obviously, that doesn't avoid the row-image-in-memory issue.  

The best alternative I've come up with is to support bcp_moretext().  That would require, ISTM, some kind of "bcp_bind_tmpfile()", something that associates a chunk of a file with a column.  The row image need not be precomposed in memory; rather, a "row image description" -- a list of pointers/handles and lengths of post-iconv, post-dbconvert, server-ready data -- would be enough.  

I would also like to remove my recently added restriction on bcp inputs: fseek().  We seek on the input data file to determine the column size.  That precludes using freebcp, say, as a filter on stdin.  Binding extra large datatypes to a workfile would let us not seek on the input.  

bcp_exec() [in] can then be implemented as:

	for each column {
		bcp_bind() or bcp_bind_tmpfile()
	}
	for each row {
		for each column {
			for each chunk {
				fread() /* to iconv input */
				if (char type)
					iconv() /* to temp buffer */
				if (varbinary etc.) {
					fwrite() /* no dbconvert */
				}
			}
			dbconvert() /* to bound buffer */
		}
		bcp_sendrow()
		for each such column {
			bcp_moretext()
		}
		if (! nrows % batchsize == 0)
			bcp_batch()
	}
	bcp_done()

The subtle part being that bcp_sendrow() and bcp_moretext() are really front-ends for a _bcp_send_columns() that interleaves the columns correctly and ships the packet(s).  

One nice outcome of this arrangement is that "for each chunk" becomes the only part sensitive to the existence of a prefix or terminator.  We save a few hundred lines of code right there.  

Tell me what you think of all this.  I'd like implement some of it.  

--jkl

-----------------------------------------
The information contained in this transmission may contain privileged and confidential information and is intended only for the use of the person(s) named above. If you are not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender immediately by reply e-mail and destroy all copies of the original message. Please note that we do not accept account orders and/or instructions by e-mail, and therefore will not be responsible for carrying out such orders and/or instructions.
If you, as an intended recipient of this commercial e-mail or advertisement, would not like to receive further e-mail correspondence from the sender, please "reply" to the sender indicating your wishes.
In the U.S.: 1345 Avenue of the Americas, New York, NY 10105






More information about the FreeTDS mailing list