Guru: One Way To Deal With Two Null Formats

July 8, 2019 Ted Holt

Hey, Ted:

We are building a new system and want to use modern programming and database techniques. I have had quite a time trying to get nulls to act right. It gets confusing fast because RPG handles them differently than the way embedded SQL does. When using SQL for I/O, how do we handle the two null formats?

— Brian

There are several ways to go about the “problem” of nulls. Let me give you one simple method, but keep in mind that it’s not the only way.

First, let’s create a table and put some data into it.

create table omembers
  ( id      dec (7),
    name    varchar(20),
    born    date,
   primary key (id))

insert into omembers values
( 1, 'Billy Rubin', '1992-05-07'),
( 2, 'Sally Varygland', NULL),
( 3, NULL, '2001-12-25'),
( 4, NULL, NULL)

So far so good. All three columns supposedly are permitted to be null — the Display File File Description (DSPFFD) command says they are. However, if you try to insert a row with a null ID, you’ll get message SQL0545 (INSERT, UPDATE, or MERGE not allowed by CHECK constraint) because a primary key can’t be null.

Before tackling SQL, let’s look at native I/O. Here’s a simple program that reads the file and displays the data.

**free
ctl-opt actgrp(*new)
        option(*srcstmt: *nodebugio)
        alwnull(*usrctl);

dcl-f   oMembers   keyed  rename(oMembers: Member);

dcl-ds  MemberInfo   likerec(Member);

*inlr = *on;
dow '1';
   read Member MemberInfo;
   if %eof();
      leave;
   endif;
   DoIt (MemberInfo);
enddo;
return;

dcl-proc DoIt;
   dcl-pi *n;
     inMember  likerec(Member) options(*nullind);
   end-pi;

   dcl-ds Message    len(52)   qualified;
      ID         char ( 7);
      *n         char ( 1);
      IDNull     ind;
      *n         char ( 1);
      Name       char (20);
      *n         char ( 1);
      NameNull   ind;
      *n         char ( 1);
      Born       char (10);
      *n         char ( 1);
      BornNull   ind;
   end-ds Message;

   Message . ID       = %char(inMember.ID);
   Message . IDNull   = %nullind(inMember.ID);
   Message . Name     = inMember.Name;
   Message . NameNull = %nullind(inMember.Name);
   Message . Born     = %char(inMember.Born);
   Message . BornNull = %nullind(inMember.Born);

   dsply Message;

end-proc DoIt;

Let me point out a few things.

First, notice ALWNULL(*USRCTL) in the header (CTL-OPT) specs. You need this option in order to work directly with nulls in externally-described database tables.

Second, data structure MemberInfo includes a map of the nulls — one indicator per field. You do not have to define another data structure just to store nulls.

Third, subprocedure DoIt receives the null map because of OPTIONS(*NULLIND). Without this option, DoIt would not be able to determine when a field is null. That is, within the DoIt subprocedure, the %NULLIND function would always return *OFF, never *ON.

Here’s the output from running the program. Notice the zeros and ones that indicate whether or not a field is null.

1  0 Billy Rubin     0 1992-05-07 0
2  0 Sally Varygland 0 0001-01-01 1
3  0                 1 2001-12-25 0
4  0                 1 0001-01-01 1

Let’s do the same thing with SQL. (I regret dropping back to fixed-format code, but the SQL precompiler did not like some of my free-form code.)

H dftactgrp(*no) actgrp(*new)
H option(*srcstmt: *nodebugio)
H alwnull(*usrctl)

D MemberInfo    e ds                  extname(OMEMBERS)
D                                     qualified
D NullIndicators  s              5i 0 dim(3)

  *inlr = *on;

  exec sql declare cWJR0011R cursor for
             select * from oMembers;
  exec sql open cWJR0011R;
  dow '1';
     exec sql
        fetch cWJR0011R into :MemberInfo :NullIndicators;
     if sqlstate >= '02000';
        leave;
     endif;
     %nullind(MemberInfo.ID)   = (NullIndicators (1) < *zero);
     %nullind(MemberInfo.Name) = (NullIndicators (2) < *zero);
     %nullind(MemberInfo.Born) = (NullIndicators (3) < *zero);
     DoIt (MemberInfo);
  enddo;
  exec sql close cWJR0011R;

  return;

P DoIt            b
(as before)
P DoIt            e

Now we’re using a cursor. Notice the differences:

• MemberInfo is defined using the EXTNAME keyword, since there is no longer a file declaration of OMEMBERS. It includes the null map, as before.
• NullIndicators is a three-element array of two-byte signed integers for SQL use. A value of zero means that the column (field) is not null. Negative one means that it is null.
• Converting the SQL 0/-1 to the RPG *OFF/*ON is easy. Use a simple assignment.

     %nullind(MemberInfo.ID)   = (NullIndicators (1) < *zero);
     %nullind(MemberInfo.Name) = (NullIndicators (2) < *zero);
     %nullind(MemberInfo.Born) = (NullIndicators (3) < *zero);

This answers half of your question. What about converting the RPG null format to the SQL null format? It’s slightly more work, but not difficult. Here’s a short demo of how to insert a row using null indicators.

H alwnull(*usrctl)

D MemberInfo    e ds                  extname(OMEMBERS)
D                                     qualified
D NullIndicators  s              5i 0 dim(3)

D NullValue       c                   const(-1)

  // Plug data for one row into MemberInfo.
  // This would usually be done elsewhere.
  MemberInfo . ID     = 7;
  MemberInfo . Name   = 'Polly Unsaturated';

  %nullind (MemberInfo . ID)   = *off;
  %nullind (MemberInfo . Name) = *off;
  %nullind (MemberInfo . Born) = *on; // <-- null!

  // There is data in MemberInfo. Insert it!
  NullIndicators (*) = *zero;    // assume no nulls
  if %nullind(MemberInfo . ID) = *on;
     NullIndicators (1) = NullValue;
  endif;
  if %nullind(MemberInfo . Name) = *on;
     NullIndicators (2) = NullValue;
  endif;
  if %nullind(MemberInfo . Born) = *on;
     NullIndicators (3) = NullValue;
  endif;

  exec sql insert into omembers (ID, Name, Born)
             values (:MemberInfo :NullIndicators);

Begin by setting all elements of the SQL null indicators array to not-null (zero). Then test each column in turn and set the corresponding null indicator if necessary.

  NullIndicators (*) = *zero; // assume no nulls
  if %nullind(MemberInfo . ID) = *on;
     NullIndicators (1) = NullValue;
  endif;
  if %nullind(MemberInfo . Name) = *on;
     NullIndicators (2) = NullValue;
  endif;
  if %nullind(MemberInfo . Born) = *on;
     NullIndicators (3) = NullValue;
  endif;

Did it work? Of course it did.

Certainly having two different conventions of null indication is a nuisance, but it isn’t difficult. I’m sure you’ve tackled more difficult challenges many times.