Topic
6 replies Latest Post - ‏2011-08-04T12:21:32Z by Kirsi
Kirsi
Kirsi
11 Posts
ACCEPTED ANSWER

Pinned topic XMLTABLE in SQL procedure and exception handling

‏2011-06-29T11:36:45Z |
Hi again,

I hit to another problem again. Isn't it possible to catch SQLEXCEPTION to a error handler in SQL Procedure when reading XML with XMLTABLE?

I am declaring a cursor for reading with XMLTABLE and tested with some wrong values in XML and it does not go to the error handler, but thows an ugly error message. I am trying to return the SQLCODE and ERROR_MESSAGE in out-parameters but haven't succeeded.

And again, I am on DB2 z/os v9.1.

Br, Kirsi
Updated on 2011-08-04T12:21:32Z at 2011-08-04T12:21:32Z by Kirsi
  • MatthiasNicola
    MatthiasNicola
    320 Posts
    ACCEPTED ANSWER

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-06-29T16:20:39Z  in response to Kirsi
    Hi Kirsi,

    you can certainly catch SQL errors when processing XML in a SQL procedure. It's hard to say what went wrong without seeing your procedure and the "ugly" error message that you received.

    Note that this forum allows you to attach files to any given post, in case you want to share the procedure and a sample XML document with us.

    An example of error handling is also available in section 6.1.2 in this RedBook:
    http://www.redbooks.ibm.com/abstracts/sg247915.html?Open

    Matthias

    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    • Kirsi
      Kirsi
      11 Posts
      ACCEPTED ANSWER

      Re: XMLTABLE in SQL procedure and exception handling

      ‏2011-07-01T12:33:18Z  in response to MatthiasNicola
      We are not yet in version 10, we are on 9.1 at least till the end of this year.

      In the end you can see what I am trying to do. I am first relying to the data, and after the insert fails, I will store the failed documents and run validation to them on later phase.

      If I put decimal value to the first element that should be intege, the next procedure does not return any diagnostics in outparameters P_SQLCODE and P_SQLMESSAGE, but it fails in "ugly" way and trows an error:
      A database manager error occurred.SQLCODE: -16061, SQLSTATE: 10608 - DB2 SQL Error: SQLCODE=-16061, SQLSTATE=10608, SQLERRMC=1.0;xs:integer, DRIVER=3.61.65

      And the tool that I am using when testing tells that procedure run failed.

      Br, Kirsi

      CREATE PROCEDURE TEST.MXMLREAD ( IN P_XML CLOB(1M) CCSID UNICODE, OUT P_SQLCODE, OUT P_SQLMESSAGE)
      VERSION "1"
      PARAMETER CCSID UNICODE
      QUALIFIER TESTB
      VALIDATE BIND
      DYNAMIC RESULT SETS 1
      READS SQL DATA
      P1: BEGIN
      DECLARE SQLCODE INTEGER DEFAULT 0;
      -- Declare cursor
      DECLARE cursor1 CURSOR WITH RETURN FOR
      SELECT * FROM
      XMLTABLE('$i/material/changes/change'
      PASSING XMLPARSE(P_AKEXML) AS "i"
      COLUMNS
      ORDERNO INTEGER PATH 'orderno',
      CHANGETYPE CHAR(1) PATH 'changetype',
      ACTIONCODE CHARACTER(3) PATH 'actioncode',
      ACTIONTIME CHARACTER(19) PATH 'actiontime',
      IDENTITYNUMBER CHARACTER(20) PATH 'identitynumber',
      PRODUCTNO CHARACTER(30) PATH 'order/productno',
      PRODUCTCLASS CHARACTER(3) PATH 'order/productclass'
      ) AS x ;
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      GET DIAGNOSTICS CONDITION 1 P_SQLMESSAGE = MESSAGE_TEXT , P_SQLCODE = DB2_RETURNED_SQLCODE;

      OPEN cursor1;
      SET P_SQLMESSAGE = 'Ok!'

      END P1
      @
      • Kirsi
        Kirsi
        11 Posts
        ACCEPTED ANSWER

        Re: XMLTABLE in SQL procedure and exception handling

        ‏2011-07-01T13:11:53Z  in response to Kirsi
        Hi,
        even if I tried to add this :
        DECLARE INVALID_DATA CONDITION FOR SQLSTATE '10608';

        and change the exit handler like this:
        DECLARE EXIT HANDLER FOR INVALID_DATA
        GET DIAGNOSTICS CONDITION 1 P_SQLMESSAGE = MESSAGE_TEXT , P_SQLCODE = DB2_RETURNED_SQLCODE;

        It failed as before.

        It looks like XMLTABLE is in another sequence and does not return diagnostics and control to upper level = to the procedure.

        Br,
        Kirsi
        • Jane Man
          Jane Man
          10 Posts
          ACCEPTED ANSWER

          Re: XMLTABLE in SQL procedure and exception handling

          ‏2011-07-07T04:23:25Z  in response to Kirsi
          I would suggest you to open a PMR to IBM.

          I can recreate your problem, but I get a better sqlerr message:

          Value of output parameters

          Parameter Name : P_SQLCODE
          Parameter Value : 0
          Parameter Name : P_SQLMESSAGE
          Parameter Value : Ok!
          ORDERNO CHANGETYPE ACTIONCODE ACTIONTIME IDENTITYNUMBER PRODU
          CTNO PRODUCTCLASS
          sqlcode : -16061 sqlstate: 10608
          sqlerr Message: THE VALUE 12.11 CANNOT BE CONSTRUCTED AS, OR CAST (USING AN IMPLICIT OR EXPLICIT CAST) TO THE DATA TYPE xs:integer. ERROR QNAME=err:FORG0001. SQLCODE=-16061, SQLSTATE=10608, DRIVER=3.58.82
          Below is how I call the procedure.

          CALL TEST.MXMLREAD(
          '<material>
          <changes>
          <change>
          <orderno>12.11
          </orderno>
          </change>
          </changes>
          </material>
          ', ?, ?)
          • Kirsi
            Kirsi
            11 Posts
            ACCEPTED ANSWER

            Re: XMLTABLE in SQL procedure and exception handling

            ‏2011-08-04T10:58:42Z  in response to Jane Man
            Hi,
            Had some vacation in the meantime :) but now at the office again.

            So, you can also see what is the problem. Thought the exception handler is an exit handler, XMLTABLE function does not give you the actual SQLCODE and MESSAGE needly to your parameters but 'throws' them in an ugly way.

            FYI: After having some four weeks rest, I do not know what has happened but at the moment I can also reseive that better message as you. Mysterious world of IT.

            I will start to gather the material for PMR.

            Thanks again a lot!
            Kirsi
            • Kirsi
              Kirsi
              11 Posts
              ACCEPTED ANSWER

              Re: XMLTABLE in SQL procedure and exception handling

              ‏2011-08-04T12:21:32Z  in response to Kirsi
              FYI:
              I did some test with inserting the result from the XMLTABLE into a table, then I can receive the SQLCODE and MESSAGE as I want.

              Naturally with that solution I don't use any cursors.

              Br,
              Kirsi