Geek asks and answers

What is the correct way to check the status of sql found in an ILE RPG?

When working with embedded SQL in an RPG, you often get a cursor and dow-loop to process all the lines in your result. Does the condition in the loop somehow depend on SQLCODand / or SQLSTTsome globally available variables in SQLRPGLE?

But what is the correct way to check these values? Some offer SQLCOD = 0others not (SQLCOD = +100 or SQLSTT = '02000'). One of them does not work with all warnings, the other does not lead to some errors, so I am not satisfied.

To illustrate what I'm doing 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

, , , , . .

+5
3

SQLSTATE IBM.

IBM InfoCenter SQL: SQLCODE SQLSTATE

SQLSTATE .

SQLSTATE 5 , .

  • '00 '=
  • '01 '=
  • '02 '=

. "00".

Simple. . .

SQLCODE , , , , .

:

:

 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')

SQL

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

SQLCODE ( ) , . :

  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 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

; , . , , . , , ().

+2

- IBM ():

The SQLCODE is also set by the database manager after each SQL 
statement is executed as follows: 
  - If SQLCODE = 0 and SQLWARN0 is blank, execution was successful.
  - If SQLCODE = 100, no data was found. For example, a FETCH 
    statement returned no data, because the cursor was positioned 
    after the last row of the result table.
  - If SQLCODE > 0 and not = 100, execution was successful with a 
    warning.
  - If SQLCODE = 0 and SQLWARN0 = 'W', execution was successful 
    with a warning.
  - If SQLCODE < 0, execution was not successful.

sql_found() :

Pfound_sql        B
D                 PI              N
 /free
  return (SQLCOD >= 0) and (SQLCOD<>100);
 /end-free
Pfound_sql        E

. , - , ( , , , ).

0

More articles:


All Articles