CTD and Sql Server 2005

[petedelux]

Hi all -
In an application using ODBC (NOT Native Client) to attach to a SqlServer (2005) database, I set a table column to STRING_Null. After the update is issued, in Sql Server the value is the empty string not a null value when i query in Enterprise Manager.

When I run this against Sqlbase, of course it's updated as a null since according to the documentation, "empty strings are defined as null."

Does anyone know a work-around short of changing all the sql in the source code? Is there an ODBC setting that I'm missing? Do I have to redefine STRING_Null as a different value other than ""?

[jmccall]

I would be inclined to use the word "NULL" in the SQL string rather than putting STRING_Null into a bind variable. I think that would be more standards compliant and therefore should work on both SQL Server and SQLBase.

[Nils Jänicke]

Try

[win32client]
setzerolengthstringstonull=1

in the sql.ini, I think this should work.

[mschoelzel]

It seem's to me, that the SQL.INI- parameter

SetSeroLengthStringsToNull=1

doesn't work on SQLServer 2008. There are any experiences with that?

Matthias

[Jeff Luther]

Matthias -- I see you commented about MS SS 2008 in your latest posting. Let me clarify for you -- this helps all of us -- that this is a different (it is related, I know) issue than that topic for this forum thread. This topic is CTD and MS SS 2005 and it works best if the thread stays with this topic.

If you have an issue with setzerolengthstringstonull and MS SS 2008 kindly open a new thread here, provide details about CTD or TD version (if v5.1 or 5.2, post in those sections) and provide us a small test case + SQL so the test can be run to verify your problem. Better provide us your TD SQL.INI in the zip/rar too.

Nils had a good comment for MS SS 2005 for the other user, and taking a 'right turn' by introducing a different MS SS version is better added to a new forum thread. Thanks for your understanding.

[Nils Jänicke]

If you are setting setzerolengthstringstonull = 1, then the statement
Update mytable Set stringfield = :sValue
sets the field to NULL if sValue is empty (STRING_Null or '')
But if you are querying
SELECT * FROM mytable WHERE stringfield = :sValue
you will not get data if sValue is empty. This does not work at SqlServer 2005, not with SqlServer 2008 and not with SqlBase. Here you must query
SELECT * FROM mytable WHERE stringfield IS NULL
if you want to get the data records with empty fields.

Ant the other thing: setzerolengthstringstonull = 1 does not work with long string values and database fields defined as text, ntext, image, varchar(max), nvarchar(max) or varbinary(max). Here
Update mytable Set multilinefield = :lValue
sets the field to '' if lValue is empty, regardless of the setzerolengthstringstonull setting. So
SELECT * FROM mytable WHERE multilinefield IS NULL
is getting no data if the field is filled with an empty string, but
Update mytable Set multilinefield = NULL
makes the database field empty so the previous SELECT statement gives you the data records.
I don't know if this is a bug, but I think this was so at older TD versions too.
Maybe if Gupta wants to change this behavior, they should define a new setting like setzerolengthlongstringstonull = 1.

[Jeff Luther]

For those so inclined to RTFM (Read the 'Fabulous' Manual) the TD ebook ccod.pdf pg. 1-15 I see has a section "Nulls, empty strings, and spaces"

Nils - LONG-type columns... yes, very different than std. non-long types, which is why setzerolengthstringstonull will not work. So, no not a bug because this INI setting was never intended so far as I know to work with LONG-type columns.

[mschoelzel]

Sorry, I was wrong. The keyword SetZeroLenghtStringsToNull relates only to the use of bind variables in TD and this works in all versions. I have tried this with the Literal '' in SQLTalk and TD an got errors, but now I've read, that this keyword wasn't made for literals .

Thank you,

Matthias