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

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
    322 Posts

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-06-29T16:20:39Z  
    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

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-07-01T12:33:18Z  
    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/
    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

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-07-01T13:11:53Z  
    • Kirsi
    • ‏2011-07-01T12:33:18Z
    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
    @
    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
    13 Posts

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-07-07T04:23:25Z  
    • Kirsi
    • ‏2011-07-01T13:11:53Z
    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
    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

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-08-04T10:58:42Z  
    • Jane Man
    • ‏2011-07-07T04:23:25Z
    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>
    ', ?, ?)
    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

    Re: XMLTABLE in SQL procedure and exception handling

    ‏2011-08-04T12:21:32Z  
    • Kirsi
    • ‏2011-08-04T10:58:42Z
    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
    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