Empty Strings and NULL - Oracle to SQL Server Migration in PowerBuilder

PowerBuilder initializes a variable to the the default initial value for the datatype, not to NULL unless you specify a value when you declare the variable.

PowerBuilder:

 // str is initialized to "" (empty string), not to NULL 
 string str
 
 // str2 is initialized to "abc" 
 string str2 = "abc"

Oracle does not support empty strings, so when you insert an emtpty string it is converted to NULL in the database:

Oracle:

  CREATE TABLE strings (value VARCHAR2(70));
 
  -- NULL will be inserted
  INSERT INTO strings VALUES ('');
 
  SELECT COUNT(*) FROM strings WHERE value IS NULL;
  # 1

SQL Server supports empty strings, so when you insert an emtpty string it is not converted to NULL in the database:

SQL Server:

  CREATE TABLE strings (value VARCHAR(70));
 
  -- NULL will be inserted
  INSERT INTO strings VALUES ('');
 
  -- Count is 0 now
  SELECT COUNT(*) FROM strings WHERE value IS NULL;
  # 0

You can see that non-initialized string were inserted as NULL value in the original PowerBuilder application for Oracle, now they are inserted as empty values when the application works with SQL Server.

For many reasons it may be required to preserve the Oracle behavior i.e. continue inserting NULL values.

In some cases you have to insert NULL values, not empty strings. For example, in a foreign key column you have to use NULL to specify that there is no relationship with the parent table. If you try to insert empty string to the foreign key, the INSERT statement fails.

In Oracle, the empty string was converted to NULL by the database. Now you can explicitly set the value to NULL before inserting data to SQL Server using PowerBuilder SetNull() built-in function:

PowerBuilder:

 // str is initialized to "" (empty string), not to NULL 
 string str
 
 // Assign NULL to str 
 SetNull(str)

If you are unable or do not want to modify the PowerBuilder application you can use an INSTEAD OF INSERT trigger to replace empty string with NULL in SQL Server:

SQL Server:

  -- Replace '' with NULL on insert
  CREATE TRIGGER strings_inst_tr ON strings
     INSTEAD OF INSERT
  AS
    INSERT INTO strings
      SELECT NULLIF(value, '') FROM inserted;
  GO

• Oracle to SQL Server Migration Tools and Reference
• Online SQL Conversion Tool

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - October 2013.