Wednesday, March 24, 2021

SQL's whenever gets modernized

additions to sql whenever for rpg

Many years ago, and yes it is so long ago I cannot remember exactly how long ago, when I was first embedding SQL into my RPG/400 programs I was introduced to the WHENEVER SQL statement. This statement can be used to cope with SQL errors and warnings returned from the previously executed statement. Personally I do not like what it does, and I do not use it in my programs.

In the announcement for the latest Technology Refreshes, IBM i 7.4 TR3 and 7.3 TR9, was mention of enhancements made to this SQL statement, which I think has won me over.

Before I explain what these enhancements I need to show the way the WHENEVER statement works without these enhancements. Even after the TRs you can still use the same old way.

The WHENEVER allows you to handle three types of exceptions:

  1. SQLERROR:  Is used to identify an exception that is an error, which is a SQL code of less than zero.
  2. SQLWARNING:  Identifies a warning exception, a SQL code of greater than zero except 100.
  3. NOT FOUND:  Is used to identify not found exceptions, the SQL code is 100. This value is returned by FETCH, SELECT, UPDATE, INSERT, etc. when the row could not be found.

When one of the following occurs I can do one of the following:

  1. CONTINUE:  Ignore the error.
  2. GOTO:  Go to a tag in my program.

As the GOTO and TAG operation codes are not supported in free format RPG the second option is useless in modern code.

In fixed form RPGLE the syntax for these statements is:

C/EXEC SQL  WHENEVER SQLERROR GO TO TAG_NAME_HERE
C/END-EXEC

C/EXEC SQL  WHENEVER SQLWARNING CONTINUE
C/END-EXEC

C/EXEC SQL  WHENEVER NOT FOUND GO TO ANOTHER_TAG
C/END-EXEC

When you see how this looks in a RPG you can understand why I will not use the WHENEVER statement.

01 C/EXEC SQL  WHENEVER SQLERROR GO TO TAG_ERROR
02 C/END-EXEC

03 C/EXEC SQL  WHENEVER SQLWARNING GO TO TAG_WARN
04 C/END-EXEC

05 C/EXEC SQL  WHENEVER NOT FOUND GO TO TAG_NOT_FND
06 C/END-EXEC

07 C/EXEC SQL  DROP TABLE QTEMP/FILE1
08 C/END-EXEC

09 C     'No error'    DSPLY
10 C                   GOTO      TAG_CONTINUE

11 C     TAG_WARN      TAG
12 C     'Warning'     DSPLY
13 C                   GOTO      TAG_CONTINUE

14 C     TAG_ERROR     TAG
15 C     'Error'       DSPLY
16 C                   GOTO      TAG_CONTINUE

17 C     TAG_NOT_FND   TAG
18 C     'Not found'   DSPLY
19 C                   GOTO      TAG_CONTINUE

20 C     TAG_CONTINUE  TAG
21 C     SQLCOD        DSPLY

22 C                   MOVE      *ON           *INLR

Lines 1 - 6: These are the WHENEVER statements that would cover any kind of exception error. Each one will direct to its own tag.

Line 7 – 8: I am trying to DROP a Table/file from QTEMP that does not exist. Therefore, an exception error is generated and the program would go to the TAG_ERROR on line 14.

Now I get to the part of the program I think is really "messy", and the reason I would not use WHENEVER.

Lines 9 and 10: If there is no exception "No error" is displayed and the program goes to the TAG_CONTINUE tag on line 20.

Lines 11 – 13: A warning exception is directed here where "Warning" is displayed, before jumping to TAG_CONTINUE.

Line 14 – 16: An error exception goes here, and "Error" is displayed, before going to TAG_CONTINUE.

Line 17 – 19: When a not found exception is encountered this piece of the program is executed.

Line 20: All of the exceptions come to the TAG_CONTINUE tag to bypass the logic for the other types of exceptions.

Line 21: I want to display the SQL code of the exception.

When I call this program I see the following:

DSPLY  Error     
DSPLY        204-

Next I replaced lines 7 and 8 with this:

07 C/EXEC SQL  UPDATE TESTFILE SET FLD002 = 'TEST'
   C+           WHERE FLD001 = 1234
08 C/END-EXEC

When the program is called as there is no record in TESTFILE where FLD002 is "TEST" the statement returns a SQL code of 100, which is a not found exception:

DSPLY  Not found 
DSPLY        100

Having gone into the history of the WHENEVER clause let's go right up to date with what the new TRs added to it.

You can still use the WHENEVER with the CONTINUE. The TAG RPG operation code is not supported in modern RPG, therefore, I cannot use it to handle the exceptions.

The new TR allows me to either call a RPG subroutine or procedure when using WHENEVER:

exec sql WHENEVER SQLWARNING CONTINUE ;
exec sql WHENEVER SQLERROR DO EXSR Subroutine ;
exec sql WHENEVER NOT FOUND DO Procedure() ;

Those of you familiar with my writing know that I don't use subroutines, I just use subprocedures.

I rewrote the program I showed above in all free RPG so I could show how cool these new additions are:

01  **free
02  ctl-opt option(*nodebugio:*srcstmt) dftactgrp(*no) ;

03  dcl-ds GetDiag qualified ;
04    RowsCount int(10) ;
05    SqlCode char(5) ;
06    SqlState char(5) ;
07    MsgId char(10) ;
08    MsgId1 char(7) ;
09    MsgId2 char(7) ;
10    MsgLength int(5) ;
11    MsgText char(1000) ;
12  end-ds ;

13  exec sql WHENEVER SQLERROR DO GetDiagnostics() ;
14  exec sql WHENEVER SQLWARNING DO GetDiagnostics() ;
15  exec sql WHENEVER NOT FOUND DO GetDiagnostics() ;

16  exec sql DROP TABLE QTEMP/FILE1 ;

17  *inlr = *on ;

Lines 3 – 12: I use the GET DIAGNOSTICS statement to get the details of the last SQL statement executed. This is the data structure returned from my procedure with all of the relevant information I want to know about an error.

Lines 13 – 15: I am going to call the procedure that gets the diagnostic data for me directly from the WHENEVER clauses.

Line 16: There is not FILE1 in QTEMP, therefore, the SQLERROR will be executed.

Line 17: I have put a debug statement here so I can see what is in the returned data structure.

This is what the GetDiagnostics procedure looks like:

30  dcl-proc GetDiagnostics ;
31    dcl-pi *n char(1040) ;
32    end-pi ;

33    clear GetDiag ;

34    exec sql GET DIAGNOSTICS
                 :GetDiag.RowsCount = ROW_COUNT;

35    exec sql GET DIAGNOSTICS CONDITION 1
36               :GetDiag.SqlCode = DB2_RETURNED_SQLCODE,
37               :GetDiag.SqlState = RETURNED_SQLSTATE,
38               :GetDiag.MsgId = DB2_MESSAGE_ID,
39               :GetDiag.MsgId1 = DB2_MESSAGE_ID1,
40               :GetDiag.MsgId2 = DB2_MESSAGE_ID2 ,
41               :GetDiag.MsgLength = MESSAGE_LENGTH,
42               :GetDiag.MsgText = MESSAGE_TEXT ;

43    return GetDiag ;
44  end-proc ;

I am not going to describe what happens here. If you are interested read the post where I wrote about using it here.

When I call the program, the DROP statement errors and the GetDiagnostics procedure is called.

When the program's logic reaches line 17 I can see what is in the GetDiag data structure's subfields:

> EVAL getdiag
GETDIAG.ROWSCOUNT = 0
GETDIAG.SQLCODE = '-204 '
GETDIAG.SQLSTATE = '42704'
GETDIAG.MSGID = 'SQL0204   '
GETDIAG.MSGID1 = '       '
GETDIAG.MSGID2 = '       '
GETDIAG.MSGLENGTH = 36
GETDIAG.MSGTEXT =
     ....5...10...15...20...25...30...35.
1   'FILE1 in QTEMP type *FILE not found.

Now I change line 16 to be:

16  exec sql UPDATE TESTFILE SET FLD002 = 'TEST'
              WHERE FLD001 = 1234 ;

As before this will generate a "not found" exception. When the program reaches the debug on line 17 I see:

> EVAL getdiag
GETDIAG.ROWSCOUNT = 0
GETDIAG.SQLCODE = '100  '
GETDIAG.SQLSTATE = '02000'
GETDIAG.MSGID = 'SQL0100   '
GETDIAG.MSGID1 = '       '
GETDIAG.MSGID2 = '       '
GETDIAG.MSGLENGTH = 25
GETDIAG.MSGTEXT =
     ....5...10...15...20...25
1   'Row not found for UPDATE.

I can see me using the WHENEVER calling this procedure as this make it super easy to be able to know what errors occurred, and add code to my programs to cope with them using the information from the GetDiag data structure subfields.

 

You can learn more about the WHENEVER SQL clause from the IBM website here.

 

This article was written for IBM i 7.4 TR3 and 7.3 TR9.

4 comments:

  1. Excellent article! I would never have thought to take another look at this statement after finding out it required using GOTO's. I will definitely look into using it again. I am curious about performance with and without the GET DIAGNOSTICS. The method I currently use does a GET DIAGNOSTICS after every SQL statement. I have never tried to measure the performance hit of that but I will give it a try.

    ReplyDelete
  2. For SQL error handling I always use the SELECT/When/Other/EndSL expression which allows me to test various SQL codes and take the applicable course of action. It works like a champ and gives me ample coding flexibility.

    Example:

    Select;
    When Sqlcode < 0
    Subprocedure_A();

    When Sqlcode = 100
    Subprocedure_B();

    Other; // If needed
    Subprocedure_C();

    EndSl;

    ReplyDelete
  3. slromano@us.ibm.comMarch 24, 2021 at 5:33 PM

    A side comment on the lack of TAG in free format RPG. To enable complete modernization of WHENEVER in free format RPG, an SQL TAG statement was added a year or so ago to generate the non-free format RPG TAG for you, allowing the source code to be maintained as completely free format.

    I believe this newer enhancement to allow a call on the WHENEVER is a much better coding practice, but when modernizing existing code without rewriting the logic, the knowledge that there is an SQL TAG statement could be useful.

    ReplyDelete
  4. Very nice! Thanks Simon!

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.