SQL's whenever gets modernized

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.