You are currently on IBM Systems Media’s archival website. Click here to view our new website.


Expecting Unexpected Errors

In RPG code, errors happen. If your code doesn’t expect and check for the error (and hopefully handle it appropriately), the program stops and the user sees an escape message—unless the error occurs in an SQL statement, in which case, the program just keeps on going, often producing some very interesting results and hours of debugging fun! Embedded SQL programmers have all experienced this fun little difference in behavior from time to time.

Hopefully everyone who uses embedded SQL understands the need to check either SQLSTATE or SQLCODE after each SQL statement to detect and get control over such error conditions programmatically. But it gets a bit cumbersome to code all of the logic to check SQLSTATE each time and so we often only code it when we “expect” that an error is likely, leaving those unexpected conditions to become harder to debug when they occur.

In a recent blog post, we wrote about using SQL’s GET DIAGNOSTICS statement to retrieve specific information about an SQL runtime error. We promised to revisit and expand on the subject, so here we are.

As luck would have it, just a few days after that blog published, an interesting thread on a related topic appeared on the RPG Cafe forum on the IBM developerWorks site. The original question—an excellent one posed by Robert Damery—was about a best practice recommendation for checking and/or handling SQL runtime errors. We found some excellent ideas in the responses, thanks to a few people, most notably Scott Klement, Birgitta Hauser and Zachary Johnson.

Their suggestions were about having a generic subprocedure that would check SQLSTATE to reduce the code to be written after each statement. Scott’s suggestion went an ingenious step further—his subprocedure sends an *Escape message back to the RPG program so that the program will at least stop when an error occurs unless it’s monitored for in the original RPG logic. In other words, using his subprocedure, an error returned by an SQL statement would react in a very similar fashion to any other RPG runtime error. Scott also passed in some user-provided text to the error handler so that the error message could help developers debug the error when it occurs. His code sends a diagnostic message for SQL warnings or conditions such as end of data along with the user-supplied text. In that case, even though the RPG program won’t stop, the extra detail in the job log about what was happening when the error occurred can help with debugging runtime issues.

Birgitta and Zachary added the idea of using GET DIAGNOSTICS (as we shared in our blog) to retrieve some additional information about any error or warning that may have occurred.

We decided to put some of those excellent suggestions together with some ideas we came up with when writing the blog and create our own generic SQL condition handling procedure.

First, instead of having our SQL error procedure return an indicator, we decided to have it return a character field. This allows us to return more detail about the status of the SQL statement. We chose to return a value of:

	“OK” when SQLSTATE begins with “00”, 
	“WRN” when SQLSTATE begins with “01”, 
	“EOD” when SQLSTATE begins with “02” and 
	“ERR” when SQLSTATE begins with anything else (“03” or above)

This way, the programmer can distinguish between an End of Data condition and a successful completion with data, which a simple indicator couldn’t do, but doesn’t have to deal with parsing out the first two positions of SQLSTATE. If a developer needs to see the full SQLSTATE value for some reason, we take care of that with the optional second parameter.

Here is a simple Open Cursor statement followed by a loop to fetch the rows. Notice that we can use the procedure to control the loop rather than directly checking SQLSTATE or SQLCODE.

  Exec SQL Open ProdCsr;                                                                                                          
  If SQLErrChk( 'Open Cursor ProdCsr' ) = “ERR”;                       
    // An error has occurred - An escape msg was sent
    // Pgmr could MONITOR for this to handle the failure if needed                         
  Exec SQL Fetch next from ProdCsr                                     
     Into :ProductDS;                                                  
  DoU SQLErrChk( 'Fetch from Cursor' ) = “EOD”;      
    If *inOF = *on;                                                    
      Except HDG;                                                      
    Except Detail;                                                     
    Exec SQL Fetch next from ProdCsr                                   
     Into :ProductDS;                                                  

  Exec SQL Close ProdCsr;                           

When we wrote the blog, we’d been working with validating XML from an RPG program using SQL. In that case, we wanted to retrieve some of the details of the validation diagnostics so we could react programmatically to some conditions. So, we added that twist to our SQL error procedure as well. We wanted the developer to have the option to get some diagnostic details back into our program rather than have a failure result in an escape message. So we have included an optional parameter—using Options(*NoPass)—that may be passed to the SQL error check procedure. If the parameter (a data structure) is passed, the procedure puts the diagnostic data into it and sends no *Escape message. If the second parameter isn’t passed, it sends either a diagnostic message or an escape message.

Jon Paris is a technical editor with IBM Systems Magazine and co-owner of Partner400.

Susan Gantner is a technical editor with IBM Systems Magazine and co-owner of Partner400.



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

New and Improved XML-INTO

Namespace support makes the opcode a viable option

Authenticating on the Web

The finer points of OpenRPGUI, Part 1

The Microphone is Open

Add your voice: Should IBM i include open-source RPG tools?

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters