FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

2010/5/10 Damien Churchill <damoxc AT gmail.com>:
> On 10 May 2010 15:01, Jackson, Craig (Gale) <Craig.Jackson AT cengage.com> 
> wrote:
>> I believe that the real issue is in the protocol spec and the databases.
>>
>> Sybase, in particular, does not distinguish zero-length strings from NULLs 
>> in the database.
>>
>> Is there an expectation that they would be stored differently?
>>
>> Craig
>>
>> -----Original Message-----
>> From: freetds-bounces AT lists.ibiblio.org 
>> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Damien Churchill
>> Sent: Monday, May 10, 2010 6:21 AM
>> To: avictor.za AT gmail.com
>> Cc: FreeTDS Development Group
>> Subject: Re: [freetds] Empty string in db-lib
>>
>> On 10 May 2010 08:22, avictor.za AT gmail.com <avictor.za AT gmail.com> wrote:
>>> hi,
>>>
>>>>>> Is it possible to send an empty string as a parameter when making rpc
>>>>>> calls? All my efforts so far seem to result in the parameter ending up
>>>>>> being NULL when doing a trace on the server!
>>>>>
>>>>> It works.  Cf. src/dblib/unittests/rpc.c.
>>>
>>>>> According to Sybase, "If the value of the RPC parameter is NULL, pass
>>>>> datalen as 0, even if type is a fixed-length datatype."  That's what the
>>>>> unit test does, and the server recognizes them as NULL.
>>>
>>>> >From that it looks like it isn't possible to just send an empty string
>>>> '' which of course is different to NULL? Is this a limitation of
>>>> db-lib or the TDS protocol?
>>>
>>> We had the same issue with CTLib.
>>> Search mailing-list archive for "FreeTDS always setting empty
>>> VARCHAR's to NULL" (24 May 2009).
>>>
>>> A similar fix might be required for dblib.
>>>
>>>
>>
>> Ahh excellent, I will have to investigate this a bit further once I
>> have my machine back up and running! Thanks for pointing me in the
>> right direction!
>>
>> Damien
>
> I believe the issue will be when passing the parameters to a stored
> procedure they (being my co-workers) were checking for the varchar to
> be "", so just required fixing of the SQL in the sprocs. It was more a
> curiosity thing since you can have empty strings in MSSQL so wondered
> if it was a limitation of FreeTDS or the TDS protocol!
>
> Damien

In Sybase and former SQL Servers there were no way to distinguish
between NULLs and empty varchar. This cause length was an unsigned
value (0-255) and 0 was reserved to NULLs. In SQL Server 7+ token
accept -1 (2 bytes, signed number) as NULL and use 0 as empty. What
happen if we insert an empty string using sql syntax like

INSERT INTO xxx(field) VALUES('') ??

In former dbs a single space was inserted (" ") (also stored in
database as single space and returned as single space).
dblib and ctlib support this kind of db so design does not take into
account empty strings... dblib has a way to distinguish empty string
from NULLs getting data from db but I don't know about passing data
via RPC...

Regards
  Frediano Ziglio