Tech Tip: Retaining NULL States with INSERT in External Databases

PRODUCT: 4D | VERSION: 12 | PLATFORM: Mac & Win

Published On: September 10, 2010

When using SQL to insert data into an external database you need to take into consideration the vendor of the external database. This is especially true if you want to preserve NULL states of fields.

As an example, consider dates. Within 4D the value !00/00/00! is recognized as a NULL date. If the external database is a 4D database the SQL statement is very straightforward as shown in the example below. If either "Start_D" or "End_D" contain the 4D NULL date value of !00/00/00! the target fields in the external database will retain their NULL state.

Begin SQL
  INSERT INTO employees
  (
    custID,
    title,
    position,
    startDate,
    endDate
  )
  VALUES
  (
    :CustID_L,
    :Title_T,
    :Position_T,
    :Start_D,
    :End_D
  )
End SQL

However, if the vendor is not 4D the SQL becomes a little more intricate.

ODBC drivers from other vendors don't translate 4D data of !00/00/00! to a NULL. Those that will accept it will assign the value of '0000-00-00' to the column and it will lose it's NULL state. They need to be given explicit NULL or a real data value.

The code below uses the SQL CASE statement of accomplish the task. Some ODBC drivers may understand a 4D var with a data of !00/00/00! as '00/00/00' or '0000-00-00'. This will vary vendor to vendor and you will need to test to know which works.

Begin SQL
  INSERT INTO employees
  (
    custID,
    title,
    position,
    startDate,
    endDate
  )
  VALUES
  (
    :CustID_L,
    :Title_T,
    :Position_T,
    CASE :Start_D
      WHEN '0000-00-00' THEN NULL
      ELSE :End_D
    END,
    CASE :DeleteDate
      WHEN '0000-00-00' THEN NULL
      ELSE :End_D
    END
  )
End SQL