[freetds] sending text data

ZIGLIO Frediano Frediano.Ziglio at vodafoneomnitel.it
Thu Apr 24 04:50:42 EDT 2003

> I worked out tonight what's wrong with my bcp uploads.  The 
> symptom was:
> Msg 4810, Level 17, State 1
> Server 'CPRO200', Line 1
>         Expected the TEXT token in data stream for bulk copy 
> of text or
> image data.
> The problem was: I was lying about the size of the data.  My 
> TDS_ROW token
> (0xD1) was promising to send as many bytes as were read from the data
> file, not the number output by iconv.  In case you don't 
> seize immediately
> on the details: when sending a row of data, each column is 
> prefixed by the
> data's (not the column's) size.  
> Heretofore, bcp.c simply read the file and sent it, no conversion, no
> nothing.  Unless the file happened to have UCS-2 data, it was 
> not possible
> afaik to load nchar columns.  Well, not usefully.  This 
> simple processing
> made it fairly easy to prefix the data with its size: just 
> keep track of
> the number of bytes read or, sometimes, output by tds_convert.  
> Now, the datafile will be encoded according to the client's 
> character set.
>  bcp.c will read the datafile, convert it to the column's datatype and
> then (in the case of character data) to the server's encoding.  
> So far, tds_iconv has worked with relatively small chunks of data (256
> bytes), to avoid allocating memory dynamically.  We haven't had a
> situation where the caller needed to have the whole buffer 
> converted at
> once; it has always been sufficient to convert some, write some, and
> repeat.  Now we have a situation where we can't do that.  

Well... you don't need to have the whole buffer. You need to have converted length before conversion (this is sligtly different, see below).

> For most columns, this is no big deal: even nvarchar columns have a
> maximum of 8000 bytes.  Text columns OTOH can be very big, and might
> easily exceed available memory.  
> What to do?  There are a few options:
> 1.  Use a chain of dynamically allocated pointers, similar to
> _bcp_get_term_data().  
> 2.  Make tds_iconv available through tds_convert, and use 
> that interface. 
> 3.  Write the iconv outputs to a temporary file until we 
> reach the end. 
> Then we'll know the length, and can read the file back in for 
> shipment to
> the server.  
> I think #3 is the most robust, but slowest.  #2 would be 
> convenient to use
> but would be limited to 50% of available memory (because tds_convert()
> wouldn't know how big a chunk to return until iconv was done, 
> and iconv
> would have to park its output somewhere).  

Mmm... you work with only client charset coding, so tds_convert shouldn't handle charset conversions. Problem is sending data to and from wire (query.c have the same problem...).
Is conversion is from fixed-size to fixed-size (ie: iso8859-1 to ucs2) just compute converted size. Is there is no conversion (ie: utf8 to utf8) no problem at all. Problem is conversions from fixed-size to variable size (ie: ucs2 to utf8) and viceversa or variable to variable (ie: big5 to utf8).
Be optimistic: string is very short, allocate a small fixed-size buffer (1024 bytes). No problem at all.
Be pessimistic: string is very large (IE: an entire xml version of a book) and do not fit in memory (or even on disk free space).
The solution is to compute converted size, output it and then start converting. This require only to be able to seek in source bcp file (to compute converted size you need to convert one time) after size computing. This require also two conversion but few memory.

Another similar problem raise with ODBC. With ODBC you can prepare a query, and provide a parameter dynamically, using SQLPutData and using chunked parameters. This require also some changes to libTDS (be able to send partial parameter) and also have same problem for text. MS solution is simple. Old ODBC API use only not-converted data or only single-byte (there are not big5 or utf8 coding in mssql...) and new API (3.5) use unicode (xxxxW ODBC api version)... So compute size is simple...

> I guess I'm leaning toward #3.  I find myself wishing for a flavor of
> fprintf(3) that would take an iconv conversion descriptor and 
> write the
> converted data to a stream.  

I don't follow you....


More information about the FreeTDS mailing list