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 ""?
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.
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.
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.
Who is online
Users browsing this forum: [Ccbot] and 0 guests