Ü   Embedded SQL: Error handling technique

·         There is always possibility of the program getting crashed if the error related to that if there is any unhandled error in the program.

·         There are some errors that we should always check in our embedded SQLRPGLE program.

      e.g.

Error while opening/closing the cursor. e.g. The cursor you are trying to open is already open. 

Error while fetching the record. E.g. Can’t fetch the record from a closed cursor, record not found condition.

Error in SQL statement.

 

·         There is mainly 2 technique of handling SQL errors.

 

I.       EMBEDDED SQL ERROR HANDLING USING SQLCOD/SQLSTT 

 

Every time the new statement comes the SQLCODE/SQLSTT is reset to default and after the execution the new value is updated in this SQLCOD/SQLSTT.

 

Below is the value structure for both:

 

Condition

SQLCODE

       SQLSTATE Class

 

 

(1st 2 positions)

Successful

0

'00'

Warning

>0

'01'

End of Data

100

'02'

Row not found

 

 

Error

<0

>='03'

 

 

Example

 


C/EXEC SQL
C+ DECLARE C1 SCROLL CURSOR FOR
C+ select PNAME, PNUM,PADDR from AMIT/PARTYD
C+ order by PNUM
C/END-EXEC
*
*
C/EXEC SQL
C+ OPEN C1
C/END-EXEC
*
C/EXEC SQL
C+ FETCH LAST FROM C1 INTO :S_PNUM, :S_PNAME,:S_PADDR
C/END-EXEC
*
C                   DOW       SQLCOD=0
C                   EVAL      RRN=RRN+1
C                   IF        RRN>9999
C                   LEAVE
C                   ENDIF
C                   WRITE     EMBED_SFL
C/EXEC SQL
C+ FETCH PRIOR FROM C1 INTO :S_PNUM, :S_PNAME,:S_PADDR
C/END-EXEC
C                   IF        SQLCOD<0
C                   LEAVE
C                   ENDIF
C                   IF        SQLCOD=100
C                   SETON                                        45
C                   LEAVE
C                   ENDIF
C                   ENDDO
*
*
C/EXEC SQL
C+ CLOSE C1
C/END-EXEC

 

 

                       

 

 

 

 

II.    EMBEDDED SQL ERROR HANDLING USING WHENEVER  

 

This is another way of monitoring of SQL errors.

Syntax:

WHENEVER   Error-condition      Action

 

Below are the error-condition used in the syntax:

  • NOT FOUND—Condition to check if there is no row found by the cursor fetch operation. This is equivalent to SQLCOD=‘100’ and SQLSTT=’02000’.
  • SQLWARNINGCondition to check if there has been successful execution of the statement but still there are some warning.
  • SQLERROR—Condition to check if any error has been found.

Action is the response to the error condition to handle it. Possible values are shown below:

  • CONTINUE--Continue the execution from the next statement.
  • GOTO  Label--Go to the defined Label in the program.

 

 

Example

 

Physical file used in the program: PARTYD

 

 

PNAME       PNUM          PADDR       OPNDD  OPNMM  OPNYY   OPNSTS
IROBO       P00000000001  CHENNAI        1      1   2,010      2
IROBO       P00000000002  EMPHAL         2     10   2,012      2
SHMANT      P00000000003  CHENNAI        3      5   2,011      2
AFTAB       P00000000007  HOMEIIEA      13      1   2,010      2
AMI         P00000000008  HELLY CORN    12     12   2,012      2
JPY         P00000000009  JEKINSAN      11     11   2,011      2
SHANN       P00000000010  KUPUNUS       24      1   2,012      2
HAPART      P00000000011  KOLINSA       16      1   2,010      2
********  End of data  ********


 

 


*************** Beginning of data **********************************************
HDEBUG(*YES)
HOPTION(*NODEBUGIO)
*
DFLD0001          S             10A   INZ('IROBO')
DFLD0002          S             12A   INZ('P00000000022')
DSQLSTMT1         S            350
*
C                   eval      SQLSTMT1='UPDATE PARTYD SET PNAME=? ' +
C                                      'WHERE PNUM=?'
C/EXEC SQL
C+ WHENEVER SQLERROR GOTO @ERROR
C/END-EXEC
*
C/EXEC SQL
C+ WHENEVER NOT FOUND GOTO @NODATA
C/END-EXEC
*
C/EXEC SQL
C+ WHENEVER SQLWARNING CONTINUE
C/END-EXEC
*
C/EXEC SQL
C+ PREPARE UPD_STMT FROM :SQLSTMT1
C/END-EXEC
*
C/EXEC SQL
C+ EXECUTE UPD_STMT USING :FLD0001,:FLD0002
C/END-EXEC
*
C     'UPDATED'     DSPLY
C                   SETON                                        LR
C                   RETURN
*
C     @ERROR        TAG
C                   SETON                                        LR
C     'NO DATA'     DSPLY
C                   RETURN
C*_________________________________________________
C     @NODATA       TAG
C     'NO DATA'     DSPLY
C                   SETON                                        LR
C*_________________________________________________
****************** End of data *************************************************


 

 

 

OUTPUT

 

NO DATA

 










User Comments:



Subscribe

  Email:



Copyright © www.go4as400.com, 2013-2017. Copyright notice