External Stored Procedures and Client/Server Error Messages

by Michael Sansoterra

[The code for this article is available for download.]

With the explosion of integration tools, it's becoming common to have scheduled PC side scripts or programs retrieve data from the iSeries and plant the data somewhere on a PC, a network drive, or a remote database. For security and performance reasons, the data retrieval schemes fetching the data are often written using SQL stored procedures (written in the SQL Procedure Language) or external stored procedures (written in high level languages like RPG or COBOL).

Error-handling is straightforward when working with SQL stored procedures. When using external stored procedures, however, there's a subtle "gotcha" that needs to be addressed.

For simplicity, consider the following SQL stored procedure. It was created using the system naming convention, which means the library list will be searched for table Orders when the procedure is invoked:

CREATE PROCEDURE TESTSQL
LANGUAGE SQL 
RESULT SETS 1 
READS SQL DATA 
SET OPTION COMMIT=*NONE
BEGIN 
    DECLARE ORDERS CURSOR FOR
     SELECT *
       FROM ORDERS 
   ORDER BY ORDERID; 

   OPEN ORDERS; 
END

You can easily retrieve the stored procedure result set with a client-side program such as the sample VBScript for this article. (To use this sample code, you need to save it with an extention of .VBS and have the Client Access ODBC driver and scripting enabled on your Windows PC.)

If, for instance, the library list is setup incorrectly and the Orders table is not found when the procedure is called, the SQL procedure is kind enough to return a message to the client.

If there is a problem, the client receives an error message

However, an external procedure written to do the same thing as the above SQL procedure will not generate the SQL0204 message, or any other message for that matter. Consider the following embedded SQL RPG program written to mimic the SPL example shown above:

 * RPG program TESTSQL
 * 
 * CREATE PROCEDURE TESTSQL
 * EXTERNAL 
 * LANGUAGE RPGLE  
 * READS SQL DATA 
 * RESULT SETS 1 
 * SIMPLE CALL 
 
C/EXEC SQL 
C+  DECLARE ORDERS CURSOR FOR
C+   SELECT * 
C+     FROM ORDERS 
C+ ORDER BY ORDERID 
C/END-EXEC 

C/EXEC SQL 
C+ OPEN ORDERS 
C/END-EXEC 

C                   Eval      *InLR=*On
C                   Return

When using external stored procedures, embedded SQL statements do not generate exception error messages. Instead, it is left up to the programmer to check either the SQL State (SQLSTT) variable or the SQL Code (SQLCOD) variable to determine if an error occurred. However, this doesn't help us in our client/server environment, because the client program or script will not know that the cursor failed to open. The client program will eventually crash when it tries to process the procedure's result set (because there's no result set to process), but the message generated by the script won't be a true indication of the problem.

This little problem can be corrected by having the RPG program send an *ESCAPE message back to the client program when an embedded SQL statement encounters an error. To do this, the QMHSNDPM (send program message) API can be used to send the message. Consider the following revised code, which adds a call to procedure SQLErrorMsg if an SQL error occurred while opening the cursor:

C/EXEC SQL
C+ OPEN ORDERS
C/END-EXEC
 * 
 * An SQLCOD<0 means an error occurred
 *
C                   If        SQLCOD<*Zero
C                   CallP(E)  SQLErrorMsg
C                   EndIf
C                   Eval      *InLR=*On
 *=========================================
 * Subprocedure: Send SQL Error Message
 *=========================================
P SQLErrorMsg     B
 *
C                   Eval      MsgID='SQL'+
C                             %Subst(%EditW(%Abs(
C                             SQLCOD):'0         '):7)
C                   Eval      MsgFile='QSQLMSG   *LIBL'
C                   Eval      MsgDta=SQLERM
C                   Eval      MsgDtaLen=SQLERL
C                   Eval      MsgType='*ESCAPE'
C                   CallP(E)  QMHSNDPM(MsgId:
C                                      MsgFile:
C                                      MsgDta:
C                                      MsgDtaLen:
C                                      MsgType:
C                                      MsgStack:
C                                      MsgStack#:
C                                      MsgKey:
C                                      dsErrCode)
C                   Return
P SQLErrorMsg     E

If the SQL statement fails, how do you know what message to send back to the client? Fortunately, the SQL Communications Area data structure contains all of the information needed to do this. For an error condition, the SQLCOD contains a negative number. If you remove the sign, this number will relate to a message identifier found in the QSQLMSG message file. All you need to do to build the message identifier is prefix the SQL code with the letters SQL (for example, SQLCOD -204 becomes message id SQL0204.) The message data and message data length are stored in variables SQLERM and SQLERL respectively. You now have all of the necessary information related to the message itself (message file name, message id, message data, and message data length) The only remaining key is to set the message-stack-number variable to 3, which will send the escape message three rungs up the call stack ladder, and will be relayed back to the client program.

Using this method, when the client program executes the external stored procedure, it will be notified if there is a problem opening the cursor, and will therefore have an indicator of the true nature of the problem, just like what would happen when running the SQL stored procedure.

Click here to see the full version of the RPG TESTSQL program. It requires a little work to implement this solution, but if you need to use external stored procedures it will end up saving you lots of debugging and troubleshooting time.

Michael Sansoterra is a programmer/analyst for SilverLake Resources, an IT services firm based in Grand Rapids, Michigan. E-mail: msansoterra@silver-lake.com

Sponsored By ADVANCED SYSTEMS CONCEPTS

Quoted from an experienced programmer new to the iSeries (AS/400): "The best thing about working on the AS/400 is using ASC's SEQUEL product." If you're tired of the limitations imposed by Query/400 or ODBC-based query and reporting tools, you need SEQUEL. Discover how thousands of sites around the world have improved access to iSeries data using SEQUEL's Windows- and Web-interfaces. It's the one tool you can rely on for virtually all your iSeries data access needs. FREE trial available. Read More about SEQUEL