capture sqlstate in an sql procedure

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.

Steve Richter wrote:[color=blue]
> 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.
>[/color]

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 ;

[url]http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/db2/rbafzgetdiag.htm&tocNode=int_174164[/url]

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

[url]http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/index.jsp?topic=/db2/rbafzcompoundstmt.htm&tocNode=int_174218[/url]

--
Karl Hanson

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

Steve Richter wrote:[color=blue]
> 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.
>[/color]

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