About Exit Handler

[Suresh]

Hi All
I have one STATES table with two fields (STATECODE, NAME).
I had written following stored procedure on this table

CREATE PROCEDURE ESCRIP.STATES_GETALL(OUT @ERRORCODE INT, OUT
@ERRORDESCRIPTION VARCHAR(200))
LANGUAGE SQL
READS SQL DATA
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- This stored procedure return all rows from STATE table
-- Created by Suresh
-- Created Date 05/17/2006
-- Version 1.0
------------------------------------------------------------------------
P1: BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE CURSTATE CURSOR WITH RETURN TO CALLER FOR SELECT STATECODE,
NAME FROM ESCRIP.STATES ORDER BY STATECODE WITH CS;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET @ERRORDESCRIPTION=SQLSTATE;
SET @ERRORCODE = SQLCODE;
END;
OPEN CURSTATE;
SET @ERRORCODE = SQLCODE;
END P1

This stored procedure retuens me CURSOR .
But when STATES table is in 'check pending state' this stored procedure
gives me SQLTATE error though i had written exit handler for the stored
procedure.
As per my expectation it should return me SQLCODE and SQLSATE in output
parameter.
So please explain me what is problem in this stored procedure.

Thanks
Suresh

[Artur]

Suresh,

The problem is simple. SQLSTATE and SQLCODE variables are set EACH TIME
SQL instruction is executed.

[1] SET @ERRORDESCRIPTION=SQLSTATE;
[2] SET @ERRORCODE = SQLCODE;

Line [1] sets proper error state and finishes successfully.
Line [2] sets sqlcode to zero because line [1] was successful !

Get the values using single SQL statement:
values (SQLSTATE, SQLCODE) into var1, var2 ;

-- Artur Wronski

[Suresh]

As table is in check pending state it does execute line one also.
means its not getting value for SQLSTATE also.

After OPEN CURSTATE
it gives folowing error message ....

ESCRIP.STATES_GETALL - Debug started.
STATES_GETALL - Exception occurred while debugging:
A database manager error occurred.[IBM][CLI Driver][DB2/LINUX] SQL0668N
Operation not allowed for reason code "1" on table "ESCRIP.STATES".
SQLSTATE=57016

STATES_GETALL - Roll back completed successfully.
ESCRIP.STATES_GETALL - Debug failed.
Can any one suggest me why this is happening

[Artur]

For me it is a bug (for 8.1.11, Viper C1)

SQLSTATE '57016' is not passed to handler. If you raise the exception
using SIGNAL statement, handler code is activated (the same problem is
if tablespace is quiesced).

Call tech support and tell them to reporduce it on database SAMPLE:

CREATE PROCEDURE S57016(OUT P_SQLSTATE CHAR(5))
DYNAMIC RESULT SETS 1

P1: BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR SELECT EMPNO FROM
EMPLOYEE;

DECLARE EXIT HANDLER FOR SQLSTATE '57016'
BEGIN
SET P_SQLSTATE= '57016';
END;

--SIGNAL SQLSTATE '57016';
OPEN CUR1;

END P1

set integrity for employee off

call s57016(?)

-- Artur Wronski

[Josh Tiefenbach]

> For me it is a bug (for 8.1.11, Viper C1)

SQLSTATE '57016' is not passed to handler. If you raise the exception
using SIGNAL statement, handler code is activated (the same problem is
if tablespace is quiesced).

This is not a bug in the exit handler implementation. The reason that
the handler is not triggered is that the OPEN call does not raise the
57016 condition.

The 57016 is raised by the first fetch from the cursor.

[Artur]

Josh,

you are right !
I was too quick with the conclusion :-)

Suresh,

You have to handle the error in program that receives the result set.
The procedure below fetch first row within the procedure body, and the
handler is activated.

CREATE PROCEDURE S57016(OUT P_SQLSTATE CHAR(5))
DYNAMIC RESULT SETS 1

P1: BEGIN
DECLARE SQLCODE INT DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE V_EMPNO CHAR(6);
DECLARE CUR1 CURSOR WITH RETURN TO CALLER FOR SELECT EMPNO FROM
EMPLOYEE;

DECLARE EXIT HANDLER FOR SQLSTATE '57016'
BEGIN
SET P_SQLSTATE= '57016';
END;
OPEN CUR1;
FETCH FROM CUR1 INTO V_EMPNO;

END P1

-- Artur Wronski

[Josh Tiefenbach]

> You have to handle the error in program that receives the result set.

The procedure below fetch first row within the procedure body, and the
handler is activated.

Be careful. As coded, that procedure will consume the first row of the
result set - which will not be returned back to the calling
application. There's no way to push back a row into the result set, so
you'll have to either

a) declare another cursor for the express purpose of checking for the
57016 state.
b) return the value you just fetched in an OUT parameter, and have the
calling application be aware of that.

Personally, I'd be more likely to scrap all that, and have the calling
application test for the 57016 condition.

[Artur]

The "fetch from ..." was only to prove that you are right,

that the first fetch is raising the error (not opening the cursor).

-- Artur

[Artur]

To check if a table is in check pending state you can also query
SYSCAT.TABLES:

SELECT ACCESS_MODE, STATUS, TABNAME
FROM SYSCAT.TABLES WHERE STATUS = 'C'

http://publib.boulder.ibm.com/infoce...n/r0001063.htm

-- Artur