Embedded SQL Exception/Error Handling
April 2, 2014 Paul Tuohy
|
At times, I wish embedded SQL would act more like RPG. A case in point is with exception/error handling. Embedded SQL assumes you are using SQLCODE or SQLSTATE to check whether or not each statement worked. In RPG, the equivalent would be have an E extender on every file operation code and checking the %error() after the operation code. Wouldn’t it be nice if you could get embedded SQL to cause the program to fail if it gets an unexpected error–just like RPG? Well, you can, just by adding one line of code after each SQL statement. Figure 1 shows how the check_SQLState() subprocedure is called after a statement. The call to check_SQLState() after the second insert statement would cause the program to fail because the insert is attempting to insert a duplicate key. The call to check_SQLState() after the Fetch shows how the subprocedure can be used to detect an end of file/row not found condition. /free
exec SQL
insert into empa values(1, 'Paul');
check_SQLState();
exec SQL
insert into empa values(1, 'Fred');
check_SQLState();
exec sql
fetch next from C001 into :data ;
if (check_SQLState());
// It was EOF
endIf;
*inLR = *on;
/end-Free
Figure 1: Using the SQL exception/error handler. Figure 2 shows the results in the job log after a call to check_SQLState() fails. The message that causes the program to fail is that the call to check_SQLState ended in error but there is an earlier message that shows that a duplicate key value specified followed by the same message preceded by the SQLSTATE value (this message is sent by the check_SQLState() subprocedure). Duplicate key value specified. 23505 Duplicate key value specified. Function check. CPF9897 unmonitored by SQLCATCH at statement 0000003600, instruction X'0000'. The call to check_SQLS ended in error (C G D F) Figure 2: Result of a program failure in the joblog. So what exactly does the check_SQLState() subprocedure do? Based on the value of SQLSTATE, it will either:
Figure 3 shows the prototype for the check_SQLState() subprocedure. There are no parameters and it returns a true indicator if an EOF status is detected. The prototype should be a copy member that is included in any program that will use the check_SQLState() subprocedure. //**/ @desc Check SQL status code. <br />
// Checks the status code of the previously executed SQL
// statement and, depending on the status, will send a message
// to the caller. <br />
// if the status is a warning (SQLSTATE='01nnn'/SQLCODE>0), a
// diagnostic message is sent to the caller <br />
// if the status is an error(SQLSTATE='03nnn'/SQLCODE<0), an
// escape message is sent to the caller <br />
// The function also indicates if "Row Not Found" is detected
// @author Paul Tuohy
// @return Status <br />
// True if BAD error (but escape message has been sent, so
// caller should fail) <br >
// True if "Row not Found" - SQLSTATE='02nnn'/SQLCODE=100
// @category utility
// @category SQL
// @category error
D check_SQLState PR n extProc('check_SQLState')
Figure 3: The prototype for the check_SQLS() subprocedure. Figure 4 shows the check_SQLState() subprocedure. This is coded in a separate module should be placed in your service program of choice. In order to work, the subprocedure must be in the same activation group as the program. The subprocedure contains a prototype for a call to the Send Program Message (QMHSNDPM) API. The QMHSNDPM API is used to send a diagnostic message (warning) or escape message (error) to the calling procedure. The subprocedure starts by using a GET DIAGNOSTICS statement to retrieve the last SQLSTATE. Then, based on the value of SQLSTATE, will set conditions for everything is OK, and end of file condition, send a diagnostic message, or send a failure message. To complete the process, if required, a message is sent. H noMain option(*srcStmt: *noDebugIO)
// ***NOTE*** Module must be in the same AG as the caller
/include prototypes
P check_SQLState B export
D PI n
// Standard API Error Data Structure
d APIError DS qualified
d bytesProvided 10i 0 inz(%size(APIError))
d bytesAvail 10i 0 inz(0)
d msgId 7a
d 1a
d msgData 240a
// Prototype for QMHSNDPM (Send Program Message) API
D sendProgramMessage...
D PR extPgm('QMHSNDPM')
D messageID 7a const
D messageFile 20a const
D messageData 256a const
D messageDataLength...
D 10i 0 const
D messageType 10a const
D callStackEntry...
D 10a const
D callStackCount...
D 10i 0 const
D messageKey 4a
D errorCode likeds(APIError)
// Work fields
D messageKey s 4a
D messageType s 10a
D messageText s 1024a
D status s n
D DS
D lastState 5a
D status_SQL 2a overLay(lastState)
// Constants
D W_DIAGNOSTIC C '*DIAG'
D W_EOF C '02'
D W_ESCAPE C '*ESCAPE'
D W_MSGF C 'QCPFMSG *LIBL'
D W_MSGID C 'CPF9897'
D W_STACK_ENTRY C '*'
D W_STACK_COUNT1 C 1
D W_SUCCESS C '00'
D W_WARNING C '01'
/free
// Get last state
exec SQL
get diagnostics condition 1 :lastState = RETURNED_SQLSTATE;
// All OK - just return
if (status_SQL = W_SUCCESS);
// EOF - return true - but no message
elseIf (status_SQL = W_EOF);
status = *on;
// Warning - send Diagnostic message
elseIf (status_SQL = W_WARNING);
messageType = W_DIAGNOSTIC;
exec SQL
get diagnostics condition 1 :messageText = MESSAGE_TEXT;
// Anything else - send an Escape message
else;
messageType = W_ESCAPE;
exec SQL
get diagnostics condition 1 :messageText = MESSAGE_TEXT;
status = *on;
endIf;
if (messageType <> *blanks);
messageText = lastState + ' ' + messageText;
sendProgramMessage( W_MSGID
: W_MSGF
: messageText
: %len(%trimr(messageText))
: messageType
: W_STACK_ENTRY
: W_STACK_COUNT1
: messageKey
: APIError );
endif;
return status;
/end-free
P E
Figure 4: The check_SQLS() subprocedure. Finally, there are a few other points that you might want to consider.
Just as I finished writing this article I came across this interesting thread on the very same topic. Check it out for some alternatives and other thoughts. Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page. RELATED STORY Error Checking and Embedded SQL
|
