Steve Richter wrote:
> I would like to capture and display the sqlstate code in an sql
> procedure when an exception occurs. Problem is, I get a compile error
> when I try to set the value of SQLSTATE to a variable. Can I do
> this? In the following code I guessed right from the documentation
> that the sqlstate for "duplicate key on insert" is 23505. What I want
> to do is run the code, get the error, then view the sqlstate to
> monitor for.
>
> thanks,
>
>
> begin
> declare continue handler for sqlstate '23505'
> set vDummy = '5' ;
> declare continue handler for sqlstate '23500'
> set vDummy = '7' ;
> insert into facBillp
> values ( vBillRcdId,
> vFactCode, vVendInvn, vVendInvDat,
> vHandleCost, vShipCost, vItemCost, vLineCx,
> vPayAmt,
> vPayRcdId ) ;
> end ;
>
> set vCh10 = sqlState ;
>
> MSG ID SEV RECORD TEXT
> SQL0312 30 221 Position 1 Variable SQLSTATE not defined or not
> usable.
>

Depending on your version/release, one option is the GET DIAGNOSTICS
statement.

declare sql_state char(5);
declare sql_code integer;
declare continue handler for sqlexception
get diagnostics condition 1 sql_state = RETURNED_SQLSTATE,
sql_code = DB2_RETURNED_SQLCODE ;

http://publib.boulder.ibm.com/infoce...ode=int_174164

You could also use return-codes-declaration in the procedure definition
to define SQLSTATE or SQLCODE.

http://publib.boulder.ibm.com/infoce...ode=int_174218

--
Karl Hanson

I believe that you're just missing a declaration of the SQLSTATE variable.
DECLARE SQLSTATE CHAR(5);

Steve Richter wrote:
> I would like to capture and display the sqlstate code in an sql
> procedure when an exception occurs. Problem is, I get a compile error
> when I try to set the value of SQLSTATE to a variable. Can I do
> this? In the following code I guessed right from the documentation
> that the sqlstate for "duplicate key on insert" is 23505. What I want
> to do is run the code, get the error, then view the sqlstate to
> monitor for.
>
> thanks,
>
>
> begin
> declare continue handler for sqlstate '23505'
> set vDummy = '5' ;
> declare continue handler for sqlstate '23500'
> set vDummy = '7' ;
> insert into facBillp
> values ( vBillRcdId,
> vFactCode, vVendInvn, vVendInvDat,
> vHandleCost, vShipCost, vItemCost, vLineCx,
> vPayAmt,
> vPayRcdId ) ;
> end ;
>
> set vCh10 = sqlState ;
>
> MSG ID SEV RECORD TEXT
> SQL0312 30 221 Position 1 Variable SQLSTATE not defined or not
> usable.
>

--
Kent Milligan
ISV Enablement - System i
kmill@us.eye-bee-m.com (spam trick) GO HAWKEYES!!
>>> ibm.com/iseries/db2
(opinions stated are not necessarily those of my employer)