Informix NULLs and Empty Strings

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
(where bind_variable contains a null value). Such a query will not return any rows. The only way to select rows based on a column containing a null value is to use the IS NULL clause.

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS