Empty character strings

Informix^® SQL and ORACLE SQL handle empty quoted strings differently. ORACLE SQL does not distinguish between '' and NULL, while Informix SQL treats'' ( or "" ) as a string with a length of zero.

Using literal string values that are empty ('') for INSERT or UPDATE statements will result in the storage of NULLs with ORACLE, while Informix would store the value as a string with a length of zero:

insert into tab1 ( col1, col2 ) values ( NULL, '' )

Using the comparison expression (col='') with ORACLE has no meaning because an empty string is equivalent to NULL; (col=NULL) expressions will always evaluate to FALSE because this is not a correct expression: The expression should be (col IS NULL).

select * from tab1 where col2 IS NULL

With Informix 4GL and Genero BDL, when setting a variable with an empty string constant, it is automatically set to a NULL value. When using one or more space characters, the value is set to one space character:

define x char(10)
let x = ""
if x is null then -- evaluates to TRUE
let x = " "
if x = " " then -- evaluates to TRUE

Solution

The ORACLE database interface cannot automatically convert comparison expressions like (col="") to (col IS NULL) because this would require an SQL grammar parser. The interface could convert expressions like (col=""), but it would do this for the whole SQL statement:

UPDATE tab1 SET col1 = "" WHERE col2 = ""

would be converted to an incorrect SQL statement:

UPDATE tab1 SET col1 IS NULL WHERE col2 IS NULL

To increase portability, you should avoid the usage of literal string values with a length of zero in SQL statements; this would resolve storage and boolean expressions evaluation differences between Informix and ORACLE.

NULL or program variables can be used instead. Program variables set with empty strings (let x="")are automatically converted to NULL by BDL and therefore are stored as NULL when using both Informix or ORACLE databases.