Guru: Alternate SQL Row-Selection Criteria Revisited Revisited

September 27, 2021 Ted Holt

I am not a “do as I say and not as I do” kind of person. After I wrote the article Guru: Alternate SQL Row-Selection Criteria Revisited, I got serious about using dynamic SQL in my RPG programs instead of enabling and disabling logical expressions in the WHERE clause. I’m reliving the days when OPNQRYF was my best friend, trying to make apostrophes play nice with string concatenation.

Overall, I’m happy with the change. When I have converted the switch-laden version to dynamic SQL, I have seen a noticeable performance improvement in some cases, and at least a slight performance in most cases. In some cases, it doesn’t make any difference, even though Visual Explain shows fewer steps to retrieve the data.

This story contains code, which you can download here.

One thing is an especially annoying nuisance, namely having to code multiple OPEN cursor statements to accommodate multiple parameter lists. If you look at the aforementioned article, you’ll see this in the last code snippet:

select;
   when SelectByClass = '1';
      exec sql  open Inp using :iClass;
   when SelectByZip = '1';
      exec sql  open Inp using :iFromZip, :iThruZip;
   other;
      exec sql  open Inp;
endsl;

I had to use multiple open statements to accommodate different numbers of parameter markers, the question marks that are used in dynamic SQL as place holders for parameter values. The more options you give the user, the more open statements you end up having to use.

However, IBM thoughtfully came up with a way to use one open statement regardless of the number of parameter markers in a query string. It’s easy and I like it a lot.

One single OPEN statement takes the place of the three OPEN statements shown above.

exec sql  open Inp using subset :iClass    :Indic_Class,
                                :iFromZip  :Indic_Zip,
                                :iThruZip  :Indic_Zip;

Notice the word SUBSET after USING. This tells SQL that you may or may not use all of the parameters. In fact, you may not use any of them at all. In this case, I have three potential parameter markers in the query to represent class, from ZIP code, and thru ZIP code.

After each parameter, I placed an indicator variable to tell SQL whether that parameter is being used or not. An indicator variable is a five-digit integer value.

dcl-s  Indic_Zip         int(5)     inz(Unassigned);
dcl-s  Indic_Class       int(5)     inz(Unassigned);

dcl-c  Assigned          const(*zero);
dcl-c  Unassigned        const(-7);

In this example, I have defined two indicator variables — one for class and one for the two ZIP code fields.

Each indicator variable should be set to one of two values. Zero means that the variable is to be used and negative seven means the variable is to be ignored. I defined two numeric constants in an attempt to make the code more understandable.

The other thing to do is to make sure that the indicator variables are consistent with the query string.

Stmt = 'select cusnbr, cusname, cuszip, cusclass from customers';

select;
   when iClass <> *blanks;
      Stmt += ' where cusclass = ?';
      Indic_Class = Assigned;
   when iFromZip <> *blanks;
      Stmt += ' where cuszip between ? and ?';
      Indic_Zip = Assigned;
endsl;

In this example, I allow the user to retrieve all rows, only rows for a certain class, or all rows within a range of ZIP codes,

Of course, there’s no reason I couldn’t allow the user to select for both class and range of ZIP codes.

dcl-s  Indic_From_Zip    int(5)     inz(Unassigned);
dcl-s  Indic_Thru_Zip    int(5)     inz(Unassigned);
dcl-s  Indic_Class       int(5)     inz(Unassigned);

dcl-c  Assigned          const(*zero);
dcl-c  Unassigned        const(-7);

dcl-s  Stmt              varchar( 512);
dcl-s  Where             varchar( 128);
dcl-s  And               char   (   5);

Stmt = 'select cusnbr, cusname, cuszip, cusclass from customers';

if iClass <> *blanks;
   Where = ' (cusclass = ?) ';
   Indic_Class = Assigned;
endif;

if iFromZip <> *blanks;
   Indic_From_Zip = Assigned;
   if Where <> *blanks;
      And = ' and ';
   endif;
   if iThruZip <> *blanks
   and iThruZip <> iFromZip;
      Where += (And + ' (cuszip between ? and ?)');
      Indic_Thru_Zip = Assigned;
   else;
      Where += (And + ' (cuszip = ?)');
   endif;
endif;

if Where <> *blanks;
   Stmt += ' Where ' + Where;
endif;


exec sql  open Inp using subset :iClass    :Indic_Class,
                                :iFromZip  :Indic_From_Zip,
                                :iThruZip  :Indic_Thru_Zip;

I’ve put the source code for these examples in the downloadable code in case it may be of use to you.

I could avoid the nuisance of multiple opens by concatenating the values themselves into the command strings, which would eliminate the need for parameter markers. The likelihood of one of my programs being targeted for SQL injection is slim. Then again, there’s a lot of old code that’s still running and shows no sign of going away any time soon.

Besides, I would lose the advantage of preparing a statement once, then opening it with different values as needed.

RELATED STORIES

Guru: Alternate SQL Row-Selection Criteria Revisited

The Dangers of Dynamic SQL