FreeTDS Development Group

["Craig A. Berry" <craigberry AT mac.com>]

> On Nov 21, 2014, at 9:43 AM, Craig A. Berry <craigberry AT mac.com> wrote:
> 
> 
>> On Nov 21, 2014, at 5:58 AM, Frediano Ziglio <freddy77 AT gmail.com> wrote:
>> 
>> 2014-11-20 19:53 GMT+00:00 Craig A. Berry <craigberry AT mac.com>:
>>> The following worked in 0.91 but is broken as of 
>>> branch-0_92-839-gd788046.  If we bulk out a table with an empty (but not 
>>> NULL) char or varchar column that is less than 256 bytes long, 256 spaces 
>>> get written to the output file for that column.  Which then causes an 
>>> overflow error if you try to bulk it back in.
>> 
> 
>> Quite strange. Why does it happen only for empty strings? Should even
>> happen with small ones. The function has to pad or not based on type.
> 
> The actual length of the string received on the wire is passed as the 
> source length to dbconvert, and dbconvert (usually) uses that as its return 
> value, and that return value is passed to fwrite for moving data to the 
> file.
> 
> But if the source length is zero, dbconvert uses the destination length as 
> its return value as long as the destination length is greater than zero.  
> Since it's now always 256, we get 256 spaces.
> 
>> Actually is not clear to me what an empty column should look like in
>> the text bcp file.
> 
> I'm pretty sure a zero-length column in a bcp file indicates NULL, and one 
> or more spaces indicates empty string and will be trimmed on bulk in.

I have studied this a bit more now.  What the Microsoft client does 
consistently is output a single ASCII NUL to indicate an empty string in the 
database column and an empty string to indicate a database NULL.  I don't 
have any Sybase clients to test with and I haven't found any documentation 
indicating how it works so I don't know if this is part of the BCP spec or 
just something Microsoft did.

The attached patch modifies _bcp_exec_out and _bcp_read_hostfile so FreeTDS 
does the same thing Microsoft bcp does.  Without this, we don't handle empty 
strings correctly round trip in bcp.


________________________________________
Craig A. Berry
mailto:craigberry AT mac.com

"... getting out of a sonnet is much more
 difficult than getting in."
                 Brad Leithauser

Attachment: 0001-Handle-empty-char-varchar-in-bcp-round-trip.patch
Description: Binary data