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 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:

D Null            C                   -1

D Myfieldnull     S              5I 0

  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:

D Mydata        E Ds                  Extname(Myfile)

D Nulls           Ds
D   Myfield1                     5I 0
D   Myfield2                     5I 0
D   Myfield3                     5I 0
D   Myfieldn                     5I 0
D   Mynulls                      5I 0 Dim(4)
D                                     Overlay(Nulls)

  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:

D Mydata        E Ds                  Extname(Myfile)

D Nulls           Ds
D                                4
D   Myfield3                     5I 0
D                                2
D   Mynulls                      5I 0 Dim(4)
D                                     Overlay(Nulls)

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

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

D Mydata        E Ds                  Extname(Myfile)

D                 Ds
D   Mynulls                      5I 0 Dim(4)

  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:

D Null            C                   -1

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

Or:

D Null            C                   -1

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