FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

2015-03-01 16:19 GMT+00:00 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.
>

NUL.... odd but true... why not a single space? NUL is terrible to
handle, for instance using fgets with C you then cannot understand
where the string terminate. I committed the patch. I would be curious
to see what happen with TEXT/IMAGE and VARBINARY. For varbinary(1) for
instance a single NUL byte is perfectly fine. Perhaps binaries in BCP
files are stored as ASCII hexadecimal?

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

Good. Glad you changed also bcp test. Looking at test I'm wondering if
this test is doing what it should. It seems it insert some data into a
table but the test for data is mainly missing (it just print a select
result on output without any check). I think t0016 is much better
although is not complete either. The problem with t0016 is that it
insert from bcp, dump from table and then check differences from
inserted to extracted (which must be the same) but is not 100% sure on
the table we have the data we want. Would be also nice if test could
have even more than 2 rows.

Frediano