Embedded SQL and Nulls

Q. How can I assign a null value to a table column using embedded SQL? The RPG %Nullind function doesn’t seem to work for an SQL Update or Insert statement.

A. Embedded SQL does not recognize the value of the %Nullind function. Instead, it uses what it calls an indicator variable to process a null value.An indicator variable is a two-byte (five-digit) integer used to communicate the null state of its associated host variable. If an indicator variable’s value is -1, then its associated host variable is null; if the indicator variable’s value is 0, the host variable is not null. (As a matter of style, you should use a named constant to represent the -1 value.) In the SQL statement, you associate the indicator variable with its host variable by specifying the indicator variable (preceded by : ) immediately after the host variable:

Dcl-c Null -1;

Dcl-s Myfieldnull Int(5);

Exec SQL Select Myfield
         Into :Myfield:Myfieldnull
         From Myfile
         Where Mykey = :Mykey;

After this statement executes, if the RPG variable  Myfieldnull is -1, then Myfield is null.

If you are using a host structure instead of individual host variables, you can associate an indicator structure with the host structure. An indicator structure is an array of two-byte integers. Here’s one way to code it:

Dcl-ds Mydata Extname(Myfile) End-ds;

Dcl-ds Nulls;
  Myfield1 Int(5);
  Myfield2 Int(5);
  Myfield3 Int(5);
  Myfieldn Int(5); 
  Mynulls Int(5) Dim(4) Pos(1);
End-ds;

Exec SQL Select *
         Into :Mydata:Mynulls
         From Myfile
         Where Mykey = :Mykey;

After this statement executes, if Mynulls(3) — or Myfield3 — is -1, then the third column in the result set (in this case, the third field in the record format) record format is null.

The array must have the same number of elements as the total number of columns in the result set, even if all the columns don’t support nulls. But in this example, we could skip defining variables for the columns that don’t support nulls, or that we don’t care to test:

Dcl-ds Mydata Extname(Myfile) End-ds;

Dcl-ds Nulls;
  *N       Char(4);
  Myfield3 Int(5);
  *N       Char(2);
  Mynulls  Int(5) Dim(4) Pos(1);
End-ds;

Exec SQL Select *
         Into :Mydata:Mynulls
         From Myfile
         Where Mykey = :Mykey;

Or, you could altogether forego overlaying variable names over the array:

Dcl-ds Mydata Extname(Myfile) End-ds;

Dcl-ds *N;
  Mynulls Int(5) Dim(4);
End-ds;

Exec SQL Select *
         Into :Mydata:Mynulls
         From Myfile
         Where Mykey = :Mykey;

In this case, you’d refer to Mynulls(3) instead of a variable name to test the null value of the third column.

The SQL Update and Insert statements can also use indicator variables to set null values in a table. To set a null value for a host variable, you would simply set its associated indicator variable to -1:

Dcl-c Null -1;

Myfieldnull = Null;
Exec SQL Update Myfile
         Set Myfield = :Myfield:Myfieldnull
         Where Mykey = :Mykey;

Or:

Dcl-c Null -1;

Myfield3 = Null;
Exec SQL Insert Into Myfile
         Values(Mydata:Mynulls);