Guru: Beware of SQL Precompiler Variables

September 26, 2022 Ted Holt

In a famous Henny Youngman joke, a patient says, “Doctor, it hurts when I do this,” to which the doctor replies, “Then don’t do that.” Corny jokes aside, I have spent decades trying to identify programming practices that hurt when I do them, and having identified them, cease to do them. A case in point is the misuse of the variables that the SQL precompiler defines in my RPG programs, variables such as SQLCODE, SQLSTATE, and SQLER3.

“So what,” I hear you ask, “is the problem with these variables? ” Well, they’re global, and global variables are evil.   Global variables are sneaky and will change their value when you least expect it. Thanks to global variables, I have spent hours debugging when I’d rather been doing something more enjoyable. I’ve seen programs run for weeks or months or years without problem and suddenly go haywire because of a global variable.

This is only one reason to be careful with the precompiler variables, but if you like reliable programs, one reason is enough.

Let’s look at an example and see what can go wrong. You may be surprised to see how easily such problems are avoided.

Here’s a program that uses a multi-row fetch to process all the records in a physical file. (In case you’re not aware, the QCUSTCDT file is in library QIWS.)

**free
ctl-opt  actgrp(*new) option(*srcstmt);

dcl-f qsysprt printer(132);

dcl-c   cSQLEOF            '02000';

dcl-ds  CustomerInfo
           extname('QCUSTCDT') dim(5) qualified inz  end-ds;

dcl-s   MaxRows            uns(5)    inz(%elem(CustomerInfo));
dcl-s   ndx                uns(5);

*inlr = *on;

exec sql  declare c1 cursor for select * from qcustcdt order by lstnam;

exec sql  open c1;
if SQLSTATE > cSQLEOF;
   snd-msg *escape ('Open failed, state=' + SQLState);
endif;

dow *on;

   exec sql  fetch c1 for :MaxRows rows into :CustomerInfo;

   if SQLState > cSQLEOF;
      snd-msg *escape ('Fetch failed, state=' + SQLState);
   endif;

   if SQLState = cSQLEOF;
      leave;
   endif;

   for ndx = 1 to SQLER3;
      writeln (%editc(CustomerInfo(ndx).CUSNUM: '4') + ' ' +
                      CustomerInfo(ndx).LSTNAM       + ' ' +
               %editc(CustomerInfo(ndx).CDTDUE: 'L'));
   endfor;

enddo;

exec sql  close c1;
return;

dcl-proc writeln;
   dcl-pi *n;
      inString   varchar(132)   const;
      inPosition uns(3)         const   options(*nopass);
   end-pi;

   dcl-ds   ReportLine   len(132)   end-ds;
   dcl-s    Position     uns(3);

   if %parms() >= %ParmNum(inPosition);
      Position = inPosition;
   else;
      Position = 1;
   endif;

   %subst(ReportLine: Position) = inString;
   write qsysprt ReportLine;

end-proc writeln;

Here’s the output from calling the program.

583990 Abraham      .00
846283 Alison       .00
475938 Doe       100.00
938472 Henning      .00
938485 Johnson    33.50
839283 Jones        .00
192837 Lee          .50
389572 Stevens     1.50
693829 Thomas       .00
397267 Tyron        .00
392859 Vine         .00
593029 Williams     .00

Notice that there are 12 lines of output.

After this stellar specimen of software engineering has been in production for a few months, Junior J. Programmer is told to make the program write the customer account number and credit due to another table when the credit due amount is at least 25 dollars. Here’s the table Junior is to write to.

create table CreditDue as
   (select cusnum, cdtdue from qiws.qcustcdt)
definition only

Junior, being a modern programmer, adds an INSERT command inside the loop.

**free

ctl-opt  actgrp(*new) option(*srcstmt);

dcl-f qsysprt printer(132);

dcl-c   cSQLEOF            '02000';

dcl-ds  CustomerInfo
           extname('QCUSTCDT') dim(5) qualified inz  end-ds;

dcl-s   MaxRows            uns(5)    inz(%elem(CustomerInfo));
dcl-s   CountFetchedRows   uns(5);
dcl-s   ndx                uns(5);

dcl-s   XCUSNUM    zoned(6);
dcl-s   XCDTDUE    packed(9:2);

exec sql set option commit=*none;

*inlr = *on;

exec sql  declare c1 cursor for select * from qcustcdt order by lstnam;

exec sql  open c1;
if SQLSTATE > cSQLEOF;
   snd-msg *escape ('Open failed, state=' + SQLState);
endif;

dow *on;

   exec sql  fetch c1 for :MaxRows rows into :CustomerInfo;

   if SQLState > cSQLEOF;
      snd-msg *escape ('Fetch failed, state=' + SQLState);
   endif;

   if SQLState = cSQLEOF;
      leave;
   endif;

   for ndx = 1 to SQLER3;
      writeln (%editc(CustomerInfo(ndx).CUSNUM: '4') + ' ' +
                      CustomerInfo(ndx).LSTNAM       + ' ' +
               %editc(CustomerInfo(ndx).CDTDUE: 'L'));
      XCUSNUM = CustomerInfo(ndx).CUSNUM;
      XCDTDUE = CustomerInfo(ndx).CDTDUE;
      if XCDTDUE >= 25.00;
         exec sql  insert into CreditDue values (:XCUSNUM, :XCDTDUE);
         if SQLState > cSQLEOF;
            snd-msg *escape ('Insert failed, state=' + SQLState);
         endif;
      endif;
   endfor;

enddo;

exec sql  close c1;

return;

dcl-proc writeln;
   dcl-pi *n;
      inString   varchar(132)   const;
      inPosition uns(3)         const   options(*nopass);
   end-pi;

   dcl-ds   ReportLine   len(132)   end-ds;
   dcl-s    Position     uns(3);

   if %parms() >= %ParmNum(inPosition);
      Position = inPosition;
   else;
      Position = 1;
   endif;

   %subst(ReportLine: Position) = inString;
   write qsysprt ReportLine;

end-proc writeln;

Junior’s modification does not work properly.

• Johnson’s data does not go into the CREDITDUE table.
• Junior broke the existing report.

583990 Abraham      .00
846283 Alison       .00
475938 Doe       100.00
839283 Jones        .00
192837 Lee          .50
389572 Stevens     1.50
693829 Thomas       .00
397267 Tyron        .00
392859 Vine         .00
593029 Williams     .00

There are only 10 lines of output. What happened to Henning and Johnson?

The loop is conditioned to the SQLER3 variable, which contains the number of fetched rows, so let’s look at the value after each FETCH and after each INSERT.

   writeln ('FETCH --> SQLER3 = ' + %char(SQLER3));         
   for ndx = 1 to SQLER3;                                   
      writeln (%editc(CustomerInfo(ndx).CUSNUM: '4') + ' ' +
                      CustomerInfo(ndx).LSTNAM       + ' ' +
               %editc(CustomerInfo(ndx).CDTDUE: 'L'));      
      XCUSNUM = CustomerInfo(ndx).CUSNUM;
      XCDTDUE = CustomerInfo(ndx).CDTDUE;
      if XCDTDUE >= 25.00;
         exec sql  insert into CreditDue values (:XCUSNUM, :XCDTDUE);
         if SQLState > cSQLEOF;
            snd-msg *escape ('Insert failed, state=' + SQLState);
         endif;
      endif;
      writeln ('--> SQLER3 = ' + %char(SQLER3));            
   endfor;                                                  

Here’s the output.

FETCH --> SQLER3 = 5    
583990 Abraham      .00 
--> SQLER3 = 5          
846283 Alison       .00 
--> SQLER3 = 5          
475938 Doe       100.00 
--> SQLER3 = 1          
FETCH --> SQLER3 = 5    
839283 Jones        .00 
--> SQLER3 = 5          
192837 Lee          .50 
--> SQLER3 = 5          
389572 Stevens     1.50 
--> SQLER3 = 5          
693829 Thomas       .00 
--> SQLER3 = 5          
397267 Tyron        .00 
--> SQLER3 = 5          
FETCH --> SQLER3 = 2    
392859 Vine         .00
--> SQLER3 = 2         
593029 Williams     .00
--> SQLER3 = 2         

Notice the value of SQLER3 after Doe, who had a credit balance of $100. The INSERT changed the value of SQLER3 to 1, since only one row was inserted.  The RPG compiler allows the terminal value of the FOR command to be changed during the execution of the loop, and that’s what happened here.  Having reached the updated terminal value of 1, the loop stopped and the program continued with the next FETCH.

The solution is to quit conditioning the loop to the SQLER3 variable.

dcl-s   CountFetchedRows   uns(5);

   CountFetchedRows = SQLER3;
   for ndx = 1 to CountFetchedRows;

Of course, you can also use GET DIAGNOSTICS to avoid SQLER3.

dcl-s   CountFetchedRows   uns(5);

   exec sql  get diagnostics :CountFetchedRows = Row_Count;
   for ndx = 1 to CountFetchedRows;

That’s fine, and I won’t say it’s wrong, but I don’t use GET DIAGNOSTICS in this manner because I see no reason to call a program to retrieve a value that’s already in memory.  That’s like buying a soft drink when the event you’re attending provides them.

I have seen a multitude of loops that test the SQLCODE (or SQLCOD) variable in various shops where I’ve worked.  Here’s one that’s very common.

EXEC SQL FETCH . . .
DOW SQLCOD <> 100;
   . . . more stuff . . .
   EXEC SQL FETCH . . .
ENDDO

Again, I won’t say that this is wrong, but be very careful. I have seen such loops fail to produce the desired behavior. I prefer the DOW *ON method I used in the example programs above.

I have traced so many bugs to these SQL precompiler variables, that I finally decided to avoid them as much as possible.  I have developed a couple (so far) of rules of thumb.

• When you need to use the value of a precompiler variable, use it immediately. If you will need that value later in the program, immediately save the value to a variable of your own.
• Be very careful when conditioning the execution of a loop to a precompiler variable.

If you can add to the list, I’ll be most grateful.

RELATED STORIES

Global Variables Are Evil (Phil Koopman)

Henny Youngman Doctor Jokes