Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    76

    Question Unanswered: Get SQLSTATE, SQLCODE in one statement without using sysibm.sysdummy1

    Hi All,

    I don't have access to SYSIBM schema and SYSIBM>SYSDUMMY1. I want to get value of SQLSTATE & SQLCODE into exit handler and store into output parameters.

    I tried following code:
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN NOT ATOMIC
    SET SQLCODE_OUT = SQLCODE; ---1
    SET SQLSTATE_OUT = SQLSTATE; ---2
    END;

    but for second statement (--2) it always gives value '00000' as previous one executed successfully.

    Can anyone let me know how to get value of SQLSTATE AND SQLCODE in single statement without using SYSIBM.SYSDUMMY1.

    Thanks in Advance,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try:
    SET (SQLCODE_OUT,SQLSTATE_OUT) = (SQLCODE,SQLSTATE);

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    or
    VALUES(SQLCODE,SQLSTATE) INTO SQLCODE_OUT,SQLSTATE_OUT
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2004
    Posts
    76
    Thanks a lot for quick reply.

    "VALUES(SQLCODE,SQLSTATE) INTO SQLCODE_OUT,SQLSTATE_OUT" will work but "SET (SQLCODE_OUT,SQLSTATE_OUT) = (SQLCODE,SQLSTATE);" won't work. To use SET statement in above manner you have to use TableName.ColumnName as per my today's understanding. Please correct me in case.

  5. #5
    Join Date
    Jan 2005
    Posts
    191
    "I don't have access to SYSIBM schema and SYSIBM>SYSDUMMY1". Tell your DBA that access to this table (actually in some versions it is a view) is a basic requirement of writing good SQL. Not granting select on it to PUBLIC is an indication of someone who doesn't understand what they are doing.

    What version/FP of DB2 are you using? A few years ago there were some fixes in this area to change the processing of SET SQLCODE_OUT = SQLCODE from an SQL statement (which destroyed SQLSTATE) into a C language statement.

    James Campbell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •