Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Unanswered: CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger

    Hi everybody,

    I am using DB2 express-C ver9.

    I am using db2XUfunctions.XMLUPDATE stored procedure.
    the signature of XMLUPDATE stored procedure is

    XMLUPDATE(IN COMMANDSQL VARCHAR(32000), IN QUERYSQL VARCHAR(32000), IN UPDATESQL VARCHAR(32000), OUT errorCode INTEGER, OUT errorMsg VARCHAR(32000))

    When I call the stored procedure from the SQl prombet or Command Editor, it is working.
    the call is
    ==================================================
    Call db2XUfunctions.XMLUPDATE (
    '<updates>
    <update action="replace" col="1" path="Plan//state/value/@endTime">2007-07-05 13:36:13.336092</update>
    <update action="append" col="1" path="Plan//state">
    <value startTime="2007-07-05 13:36:13.336092" endTime="NOW">registered</value>
    </update>
    </updates>',

    'Select CIDoc from aim_ComplexInfo_tab',

    'update aim_ComplexInfo_tab set CIDoc=?' ,?,?)
    ================================================== =====
    Problem 1:

    I am trying to call the stored procedure with the same parameters from a java application using the next code:
    CallableStatement cstmt = main.conn.prepareCall("{call db2XUfunctions.XMLUPDATE (?,?,?,?,?)}");
    cstmt.setString(1, param1);
    cstmt.setString(2,param2 );
    cstmt.setString (3,param3 );
    cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
    cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
    cstmt.executeUpdate();

    At the run time, I get the next error

    Update: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -16132, SQLSTATE: 2200M, SQLERRMC: null
    ================================================== ==========
    Problem 2:
    I am trying to call the stored procedure with the same parameters from a trigger using the next code:

    CREATE TRIGGER DB2INST1.TEST NO CASCADE BEFORE UPDATE OF NUMBER_OF_DAYS ON DB2INST1.AIM_TIMINGDEVENT_TAB REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL
    WHEN ( newrow.Number_of_Days = 2 )
    BEGIN ATOMIC
    values(
    Call db2XUfunctions.XMLUPDATE (
    '<updates>
    <update action="replace" col="1" path="Plan//state/value/@endTime">2007-07-05 13:36:13.336092</update>
    <update action="append" col="1" path="Plan//state">
    <value startTime="2007-07-05 13:36:13.336092" endTime="NOW">registered</value>
    </update>
    </updates>',
    'Select CIDoc from aim_ComplexInfo_tab',
    'update aim_ComplexInfo_tab set CIDoc=?' ,?,?)
    );
    END

    I get the next error, when I try to create the trigger

    [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token
    "db2XUfunctions" was found following "ATOMIC values( Call".
    Expected tokens may include: "<space>". LINE NUMBER=5.
    SQLSTATE=42601


    Could you please help me in solving problem 1 and 2?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by emansour
    At the run time, I get the next error

    Update: com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -16132, SQLSTATE: 2200M, SQLERRMC: null
    Code:
    $ db2 "? sql16132"
    
    
    SQL16132N XML document contains an invalid document structure.
    
    Explanation:
    
     While parsing an XML document, the parser encountered an invalid
    document structure. The parser encountered non-whitespace
    character data in the prolog of an instance document or in the
    external subset of a DTD.
    
     Parsing or validation did not complete.
    
    User Response:
    
     Correct the XML document and try the operation again.
    
     sqlcode :  -16132
    
     sqlstate : 2200M
    CREATE TRIGGER DB2INST1.TEST NO CASCADE BEFORE UPDATE OF NUMBER_OF_DAYS ON DB2INST1.AIM_TIMINGDEVENT_TAB REFERENCING NEW AS newrow FOR EACH ROW MODE DB2SQL
    WHEN ( newrow.Number_of_Days = 2 )
    BEGIN ATOMIC
    values(
    Call db2XUfunctions.XMLUPDATE (
    '<updates>
    <update action="replace" col="1" path="Plan//state/value/@endTime">2007-07-05 13:36:13.336092</update>
    <update action="append" col="1" path="Plan//state">
    <value startTime="2007-07-05 13:36:13.336092" endTime="NOW">registered</value>
    </update>
    </updates>',
    'Select CIDoc from aim_ComplexInfo_tab',
    'update aim_ComplexInfo_tab set CIDoc=?' ,?,?)
    );
    END

    I get the next error, when I try to create the trigger

    [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token
    "db2XUfunctions" was found following "ATOMIC values( Call".
    Expected tokens may include: "<space>". LINE NUMBER=5.
    SQLSTATE=42601
    You should remove the VALUES table constructor. This should work:
    Code:
    BEGIN ATOMIC
       CALL db2XUfunctions.XMLUPDATE (...);
    END
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jul 2007
    Posts
    6

    CALL db2XUfunctions.XMLUPDATE stored procedure from A Java Application or Trigger

    Thanks stolze,

    Regarding the 2nd problem "Calling the Stored Procedure (SP) from a trigger":

    I have followed your advice. I got an error which is

    SQL0418N A statement contains a use of a parameter marker that is not valid.

    sqlcode : -418
    sqlstate : 42610


    I tried to solve it by modifying the trigger stmt to be
    --------------------------------------------------
    create trigger AIM_Trg_PP1234_R01
    after update of Number_of_Days on aim_TimingDEvent_tab
    referencing old as oldrow new as newrow
    for each row
    mode db2sql
    WHEN ( newrow.Number_of_Days = 2 )
    BEGIN ATOMIC
    CALL DB2XUFUNCTIONS.XMLUPDATE(
    '<updates><update action="replace" col="1" path="Plan//rule[1]/state/value[@endTime=&quot;NOW&quot;]/@endTime">2007-07-10 15:36:13.336092</update></updates>',
    'Select CIDoc from aim_ComplexInfo_tab',
    'update aim_ComplexInfo_tab set CIDoc=UCASE(CAST(? AS xml))' ,
    UCASE(CAST(? AS INTEGER)),
    UCASE(CAST(? AS VARCHAR(32000)))
    );
    END #

    It did not work.


    I have discovered that it is not allowed to call a stored procedure with output parameter from a action of trigger. Is that correct?

    I removed the output parameter from the stored procedure and it is working.
    create trigger AIM_Trg_PP1234_R01
    after update of Number_of_Days on aim_TimingDEvent_tab
    referencing old as oldrow new as newrow
    for each row
    mode db2sql
    WHEN ( newrow.Number_of_Days = 2 )
    BEGIN ATOMIC
    CALL DB2XUFUNCTIONS.XMLUPDATE2(
    '<updates><update action="replace" col="1" path="Plan//rule[1]/state/value[@endTime=&quot;NOW&quot;]/@endTime">2007-07-10 15:36:13.336092</update></updates>',
    'Select CIDoc from aim_ComplexInfo_tab',
    'update aim_ComplexInfo_tab set CIDoc=?');
    END #

    I would like to know is there any way to call a stored procedure with output parameter from a trigger action?

    Regarding the first problem "Calling the Stored Procedure (SP) from a java application":

    As, I mentioned I am using the same parameters, which are working when I call the SP from SQL Command Editor.

    I can not recognize any problem in the XML parameter.

    this is the code I am using
    String commandXML =
    "<updates><update action=\"replace\" col=\"1\""
    + " path=\"Plan//state/value/@endTime\">"+"2007-07-10"+"</update>"
    + "</updates>";

    String querySQL = "Select CIDoc from aim_ComplexInfo_tab ";
    String updateSQL="update aim_ComplexInfo_tab set CIDoc=? ";

    String procName = "DB2XUFUNCTIONS.XMLUPDATE";
    String sql = "CALL " + procName + "(?,?,?,?,?)";

    CallableStatement cstmt = main.conn.prepareCall(sql);
    cstmt.setString(1, commandXML);
    cstmt.setString(2,querySQL );
    cstmt.setString(3,updateSQL );
    cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
    cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);

    cstmt.execute();
    ========================================

  4. #4
    Join Date
    Jul 2007
    Posts
    6

    partially solved

    I would like to thank you all

    I think I was wrong, it is allowed to call a stored procedure from an action of a trigger.
    Please see the next code:
    ================
    create trigger AIM_Trg_PP1234_R01
    after update of Number_of_Days on aim_TimingDEvent_tab
    referencing old as oldrow new as newrow
    for each row
    mode db2sql
    WHEN ( newrow.Number_of_Days = 2 )
    BEGIN ATOMIC
    DECLARE errorCode INTEGER ;
    DECLARE errorMsg VARCHAR(320);
    CALL DB2XUFUNCTIONS.XMLUPDATE (
    '<updates><update action="replace" col="1" path="Plan//state/value/@endTime">2007-07-05 09:36:13.336092</update>
    <update action="append" col="1" path="Plan//state"><value startTime="2007-07-05 09:36: 13.336092" endTime="NOW">registered</value></update></updates>',
    'Select CIDoc from aim_ComplexInfo_tab',
    'update aim_ComplexInfo_tab set CIDoc=?' ,
    errorCode,errorMsg);
    END #
    ================

    I have test it. It is working well.

    I am still looking for a solution to the problem of calling the XMLUPDATE stored procedure from a java application.

    Thank you again,

    Essam the beginner in db2

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I believe that your parameters are not properly sent to DB2. Have you tried to collect a Java trace and looked at the actual data that DB2 receives? Maybe some sort of character conversion interferes and causes the error to be raised.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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