What's the correct way to check sql found condition in ILE RPG?

Question

When working with embedded SQL in RPG, you often end up with a cursor and a dow-loop for processing all rows in your result. The condition in the loop is somehow dependent on SQLCOD and/or SQLSTT, some globally available variables in an SQLRPGLE-program?

But what is the correct way of checking these values? Some suggest SQLCOD = 0 others not (SQLCOD = +100 or SQLSTT = '02000'). One fails on all warnings, the other does not fail on some errors, so I'm not content.

To illustrate what I do with some code:

Pmain             B
D                 PI
Dmy_ds          E DS                  extname(SOME_TABLE)
D                                     qualified
 /free
  exec sql
    DECLARE cur CURSOR FOR
      SELECT *
      FROM some_table;
  exec sql 
    OPEN cur;
  exec sql
    FETCH cur
     INTO :my_ds;
  dow sql_found();
      exec sql
        FETCH cur
         INTO :my_ds;
  enddo;
  exec sql
    CLOSE cur;
 /end-free
Pmain             E


Psql_found        B
D                 PI              N
 /free
  // insert return statement here...
 /end-free
Psql_found        E

I'm looking for the correct return statement here, that will make me go through all rows if no error occurs and lets me leave when an error occurs. Bonus points for some decent way to check for errors.

Answer 1

SQLSTATE is better, and recommended by IBM.

From IBM's InfoCenter SQL Messages and Codes Reference: SQLCODE and SQLSTATE concepts

SQLSTATE is the preferred standard return code.

SQLSTATE is 5 characters, with the first two bytes identifying a class of conditions.

• '00' = Unqualified Successful Completion
• '01' = Warning
• '02' = No Data

Anything else is an error. I generally only check for '00'.

Simple. Easy. More portable.

Using SQLCODE often involves lists of codes which are, IMHO, less than developer friendly.

Example:

Personally, I generally include definitions and code like this:

 D xSQLState@      s               *   inz( %addr(SQLState) )
 D xSQLState       ds             5    based(xSQLState@)
 D  xSQLState2                    2a
 D   
 D Success_On_SQL  C                   const('00')
 D Warning_On_SQL  C                   const('01')
 D NoData_On_SQL   C                   const('02')

Then after any SQL operation, I generally check

   if xSQLState2 <> Success_On_Sql;
     someflag = true;
   endif;