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?

You should remove the VALUES table constructor. This should work:

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();
========================================

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

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.