FreeTDS Development Group

["Lowden, James K" <LowdenJK AT bernstein.com>]

> From: Frediano Ziglio [mailto:freddyz77 AT tin.it]
> Sent: February 5, 2004 10:37 AM
> Il mer, 2004-02-04 alle 22:05, Lowden, James K ha scritto:
> > 
> > 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 didn't understand this paragraphs that much... What do you mean by
> "something that associates a chunk of a file with a column" ?

bcp_bind() associates a buffer in a host variable with a column.  When the 
column is sent (with bcp_sendrow), the data are read from the buffer.  

For text/binary data, I propose bcp_bind_tmpfile() to do the same thing, but 
using a workfile instead of a memory buffer.  The column's data would be held 
in a workfile, where it wouldn't tax virtual memory.  

My other point is that we don't have to allocate a buffer to hold the row 
image, before sending it to the server.  It's enough to know where each 
column's data will be read from.  

> >     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 */
> 
> fwrite where ? to a temporary file ?

Yes.  That's what I mean by a "workfile".  

> >                             }
> >                     }
> >                     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).  
> > 
> 
> The harder thing it's length. You need first to send length and then
> data. To know length you need to scan all column if terminated or to
> convert to proper charset if we need to convert it (text). 

Exactly.  The server wants each column's length before its data.  That's why 
we read the whole row, passing each column's data (depending on the datatype) 
through iconv() and dbconvert().  The server-ready data are parked, either in 
memory or on disk, and the sizes noted.  When the whole row has been read 
from the file, processed, and parked, we're ready to call bcp_sendrow(), 
which reads the prepared data and writes them column-by-column to the server. 
 

> If we know length before we can just copy from file to wire. 

No, we can't.  If the row crosses packet boundaries, we'd send the first part 
of the row before we're done reading the rest of it from disk.  If we 
encounter a problem -- iconv(), unexpected EOF -- we have no way to retract 
the partial row from the server, and no clean way to end the session.  

> I don't think it's
> good to use temporary files just to avoid fseek... perhaps it would be
> better to test if we can fseek and then use temporary file.

I'm not proposing to use temporary files just to avoid fseek().  I'm saying:

1.  We need then to support bcp_moretext(), because there's no reason to 
allocate memory for very large columns.  
2.  To do that, we have to restructure bcp_exec(), something we'll have to 
work on anyway when we move bcp wire handling to libtds.  
3.  When we restructure bcp_exec(), we get an opportunity to reorder the 
processing, such that "measuring" the data file is no longer needed.  It's 
that "measuring" that requires fseek(3).  

If you're worried about throughput, don't.  :-)  Disk I/O is orders of 
magnitude faster than network I/O.  We involve workfiles only for huge 
columns, and we need them for text/ntext, if we're going to pass the data 
through iconv().  

Is that any clearer?  

--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