Question:
I am having trouble with my SQLWindows code translating Informix nulls and empty strings ( ” ) as the same value ( ” ).

When I select a null value from the backend into a local bind variable, then try to pass it back on an update (in a where clause) it can’t find the row because it’s looking for an empty string ( ” ), not a NULL value. I’ve heard rumors that SQLWindows is now able to somehow differentiate between the two, but how? I’m using 5.0.3 (no PTF) w/Informix router 2.2.0.

Answer:
In SQLWindows 5.0.2 we introduced a new SQL.INI keyword to support conversion of zero-length strings to NULL. This version of the SQL/API lets you automatically convert zero-length strings (input bind variables only) to NULLs with the setzerolengthstringstonull keyword. This keyword is a temporary feature until the availability of the NULL_String constant in the next major release of SQLWindows.

The keyword lets you mimic the behavior of an earlier version of the SQL/API, which automatically converted zero-length strings (in bind variables only) to NULL. Note that this keyword applies only to bind variables, not to literals or SQLWindows INTO variables.

An additional consideration is the correct behavior of NULL bind variables in WHERE clauses. Since a NULL variable is an undefined variable, it will not match another NULL variable. Hence, a query that selects rows based on a column containing a null value cannot use the following syntax:

where column_name = :bind_variable

For any datatype except (long) string, there are constants to specify null, i.e. NUMBER_Null. SQLWindows previously assumed that the empty string will work as a null, and it does against SQLBase and a few other DBMSs. However, the empty string is not acceptable to some DBMSs. Sybase, for example, treats the empty string the same as a single space, and that’s what will get inserted into the database. DB2 gives an error when it receives the empty string. And as you have noticed, Informix also treats it as a space. For this reason, until SQLWindows provides a null constant for strings (STRING_Null), this switch can be set in sql.ini to control how empty strings should be treated when encountered in a bind variable. This requires at least SQLAPIW.DLL from SQLBase 5.2.1PTF2 for proper operation.

Note that this discussion does not apply to Centura Builder, which supports the STRING_Null constant.