Ü 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’.
- SQLWARNING—Condition 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