Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    7

    Unanswered: Capturing the error message text in a 'continue handler'

    Hi all,

    I'm wanting to capture a error message text such as:

    SQL0530N The insert or update value of the FOREIGN KEY
    "TST01.TEST_RETURNS.F_EXAM_TYPE" is not equal to any value of the
    parent key of the parent table. SQLSTATE=23503

    in a sql stored procedure continue handler so it can be logged as to why it couldn't be processed and the next record processed.

    I've got code that records the sqlcode or sqlstate for logging. Whilst the sqlcode/sqlstate indicate that there has been a FK violation, it doesn't indicate on what FK. Important on tables with lots of FKs!


    thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS?

    This is for V8 and higher for LUW:

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;

    END;


    HTH

    Andy

  3. #3
    Join Date
    Oct 2007
    Posts
    7
    Hi Andy,

    thanks for your quick response! Just what I was looking for. It is simple when you know how - I just didn't know what I should have been looking for.

Posting Permissions

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