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;

Answer 2

The best practice is to process the SQLCODEs you expect (as part of the expected processing) and to add exception code to handle the ones you don't. One implementation:

  dow 1=1;  // forever
      exec sql
        FETCH cur
         INTO :my_ds;
  // normal exit         
  if sqlstt = SQL_NODATA;
    SFLEND = *on;        
    leave;               
  endif;                 

  // can't CAST a value
  if sqlstt = SQL_CAST;         // CAST error                               
    ... tell user there's an error and read another
    iter;                                                                  
  endif;                                                                   

  // decimal data error
  if sqlstt = SQL_DDE;
    tell user to call IT and stop reading
    leave;                                      
  endif;                                        


  // whoops! not expected at all.  Dump for post-mortem
  if sqlstt <> SQL_NORMAL;                             
    ... tell user to call IT and stop reading
    dump(a);                             
    leave;                                              
  endif;                                               

  // test for end of loop
  // filled subfile page?
  enddo;  // forever

With this type of implementation you have to intentionally leave the loop; whether you've filled a subfile page, loaded the highest element in an array or hit an error. I'm not sure there is a single, generic implementation that will handle all circumstances. Sometimes you might want to leave the read loop if you have a record lock and sometimes you want to issue a message and try again (for example).