We have an old application that reads in SQL text files and sends them to Sybase ASE 12.51. Our legacy app was written in Delphi 5 and is using the BDE TQuery component for this process and accessing Sybase with the BDE SQLinks for Sybase.
Pseudo code:
SQLText=readSQLFile;
aTQuery.SQL.add(SQLText);
aTQuery.ExecSQL;
Recently we moved our DB access layer to the Delphi XE ADO implementation - TADOQuery, using the Sybase supplied ADO provider, still using same model:
SQLText=readSQLFile;
aTADOQuery.SQL.add(SQLText)
aTADOQuery.ExecSQL;
After migrating to ADO, we discovered that certain data was missing. We traced the failure to this SQL construct:
Select myColumn from myTable
Where tranID = null
Knowing that this construct is sematically questionable at best, I did a 'double take' when I saw this code, but Sybase 12.5 accepts it - however using ADO, this segment fails.
We decided to change:
Where tranID = null
to
Where tranID is null
Then the missing data was loaded - problem solved, for this segment and several others as well.
Does anyone have an explanation for this behavior? Where/why did ADO apparently intercept and reject this seqment whereas the BDE passed it thru?
TIA
"NULL" has a very special meaning, and SQL needs a special handling. You can't compare a value to "NULL", that's why there is the special operator IS (NOT) NULL to check for it. An exhaustive explanation would take some space, here a simple explanation.
From a "mathematical" point of view, NULL can be thought as "infinity". You can't compare two infinite values easily, for example think about the set of integer numbers and even numbers. Both are infinite, but it seems logical the former is larger than the latter. All you can say IS both sets are infinite.
That's also helps to explain for example why 1 + NULL returns NULL and so on (usually only aggregate functions like SUM() ecc. may ignore NULL values - ignore, not turn NULLs into zeroes).
This metaphor may not hold true in sorting, because some databases choose to consider NULL less than any value (some kind of -infinity and thereby returning them first in ascending order), other the other way round. Some have an option to set where to return NULLs.
Check your database documentation about NULL arithmetics and NULL comparisons. field = NULL should have never been used, don't know if Sybase accepts it, but most SQL implementations don't, and I guess it is not SQL standards compliant. It is far better to get used to the IS (NOT) NULL syntax.
Update: Sybase has a "set ansinull" option, from the documentation (always RTFM!), in 12.5.1 it was expanded to disallow the '= NULL' syntax (when set to ON to make it compliant with SQL standards). When set to OFF '= NULL' works as 'IS NULL'.
Maybe the SQL Links or the ADO provider set it to one value or the other.