capture sqlstate in an sql procedure - IBM AS400

This is a discussion on capture sqlstate in an sql procedure - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 3 of 3

Thread: capture sqlstate in an sql procedure

  1. 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.


  2. Re: capture sqlstate in an sql procedure

    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

  3. Re: capture sqlstate in an sql procedure

    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)

+ Reply to Thread