[freetds] [PATCH] Handle empty char/varchar in bcp round trip (Re: bp with empty char/varchar column broken)
Craig A. Berry
craigberry at mac.com
Sun Mar 1 11:19:54 EST 2015
> 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."
-------------- next part --------------
A non-text attachment was scrubbed...
Size: 4952 bytes
Desc: not available
More information about the FreeTDS