More developers are using embedded SQL in their applications, and some treat the SQLCOD return value as a two-valued field, just like the on/off end-of-file indicator in native file access. In this article, I want to show you that treating SQLCOD like an on/off indicator can be dangerous.

Have you ever written code like this?

This construction is based on the assumption that after you fetch a row from a cursor, SQLCOD will contain either zero, meaning a row was fetched successfully, or 100, meaning that there are no more rows to return. Most of the time this construction works, but there are exceptions. When an exception occurs, your program may not crash, but it may fail to process all the rows you intended to process.

A Simple Embedded SQL Program

To demonstrate why you need additional checking, I have contrived a stripped-down application that might exist in a retail organization. The database has a table that contains the promotion number (PROMO), the first day the promo is active (FIRST), and the last day the promo is active (LAST). Table SQL_DEMOP has only a few rows, and a query over the table, ordered by First, shows data like this:

Application program SQL_DEMOR (Figure 1), lists all the promos that are active on a given date, in promo order. It employs the DOW SQLCOD = 0 construction, and I created it with the CRTSQLRPGI command, taking the IBM-supplied defaults.

Figure 1: This is the original source for SQL_DEMOR.

To keep the code simple, I hard-coded a search date of November 23, 2007, in the program. When I run SQL_DEMOR, I get a list like this, correctly showing the three promos in effect on November 23, 2007:

Add More, Get Less

Suppose the creative folk in advertising create promo 555 and on November 19 decree it "to be in effect immediately and to remain in effect until further notice." The data entry folk key it with a first day of 2007-11-19, but since they weren't given an end date and are fed up with the imprecise instructions they constantly get from advertising, they use a date way, way, way in the future, 2099-12-31.

A query over the table, again ordered by First, now gives data like this:

Clearly, promo 555 is in effect on November 23, 2007, but if I run SQL_DEMOR again, promo 555 does not show up. In fact, now it lists only two promos! I get this:

Not only is promo 555 missing, but promo 923 has also disappeared!

This is a contrived application, but it could bear some resemblance to the real world. What would have happened if the advertising department had put out a holiday season flyer that featured promo 555, but your program hadn't told the point of sales system about it? You would have some irate customers and some equally irate sales staff who had to explain to the irate customers why the promo discount was not ringing up at the cash register.

SQL_DEMOR did not crash, but it gave wrong results. Before reading further, can you figure out why?

SQLCOD = Minus 181?

When I look in the job log, I find an SQL0181 diagnostic message, telling me that a value in a date, time, or timestamp string is not valid. SQL_DEMOR was created with the default IBM CRTSQLRPGI command, which defaults to DATFMT(*JOB). In my shop, and probably in most shops in the United States, the job date default is *MDY. This tells the compiler that only dates between 1940-01-01 and 2039-12-31 are valid in this program. SQL_DEMOR works as long as there are no dates outside this range, so it receives an SQLCOD value of -181 when it encounters the 2099 date. As written, the program then thinks it has processed the last row in the cursor and ends normally.

The Fix

There are two remarkably easy fixes specific to this program.

You can recompile the program specifying DATFMT(*ISO) on the CRTSQLRPGI command. Or, if you're afraid you'll forget this option when you next compile the program or put it into production (and you know you will), instead add this to the code:

set option datfmt=*iso

Figure 2 shows the revised source.

Figure 2: This is the beginning of the revised source for SQL_DEMOR.

Is It the Right Fix?

I do not consider either of the above fixes robust coding techniques for handling SQL return codes. Granted, either one solves the immediate date-handling issue, but the program is still treating SQLCOD as a two-value field. The original version of SQL_DEMOR demonstrated that SQLCOD can have at least a third value we didn't know about, and who knows what other values will turn up that will give a false end-of-data indication?

There are way more than three positive SQLCOD values documented by IBM in the iSeries Information Center, though many will never occur when reading through a cursor. The ones you are most likely to encounter relate to date and time stamps, truncation of string data if your host variable is too short, and conversion between database and host variable data types.

In a follow-up article, I will show a better technique using SQLSTT instead of SQLCOD. I will provide with it a template program with more robust error-checking, which you can use as a skeleton for many of your embedded SQL programs.

Downloadable Source

If you want to experiment, you can download the code from this article from the MC Press Web site. Included is the source of both versions of SQL_DEMOR, an SQL script to create and load table (file) SQL_DEMOP, and an SQL script to insert a record that causes the first version of SQL_DEMOR to fail.

Sam Lennon is a Systems Analyst and iSeries geek at a major electronics retailer. He has worked on the AS/400/iSeries/System i platform for 16 years and no longer admits to how long he's been writing code. You may reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

Sam Lennon is an analyst, developer, consultant and IBM i geek. He started his programming career in 360 assembly language on IBM mainframes, but moved to the AS400 platform in 1991 and has been an AS400/iSeries/i5/IBM i advocate ever since.

Also Read

TechTip: Prevent Unintentional Deletion of Objects with RESTRICT ON DROP

SQL 101: DDL Recap—Improving Our Basic SQL Tables, Part 2

TechTip: Automate Complex Tasks Using SQL and User-Defined Table Functions