Page 1 of 1

Problems calling DB2 stored procedures

Posted: Mon Mar 26, 2007 6:34 pm
by dmccreary
From page 124 of the DB2 9 pureXML Guide the following stored procedure uses question marks to pass parameters:

Code: Select all

CALL DB2XMLFUNCTIONS.XMLUPDATE(
'<updates>
<update action="append" col="1" path="/Customer">
<Email>newEmail@yahoo.com</Email>
</update>
</updates>',
'select doc from xps where id=1',
'update xps set doc=? where id=1', ?, ?);
When I try to execute this SQL using the DB2 connector I get error messages that imply that the parsing fails at the question mark.

Here is the exact error message:

Code: Select all

Severity and Description	Path	Resource	Location	Creation Time	Id
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: ;;ere cid=1000', ?, ?);END-OF-STATEMENT DB2 Testing append-email-to-customer.sql Unknown 1174924098779 1759
The error message for 42601 is:

Code: Select all

A character, token, or clause is invalid or missing.
Which you can lookup here http://publib.boulder.ibm.com/infocente ... /apval.htm

Can you tell me if it is possible to call DB2 stored procedures that include question marks from the oXygen eclipse plugin? Do I need to escape question marks?

A link to the DB2 User guide is here:
http://www.redbooks.ibm.com/redbooks/pdfs/sg247315.pdf

Thanks - Dan

Posted: Tue Mar 27, 2007 4:18 pm
by stefan
It is possible to call DB2 stored procedures that include question marks from oXygen. Question marks are place holders for input parameters so don't forget to add the parameters in the transformation scenario parameters dialog.

Here is an example:
I created a new SQL file and I will try to execute a stored procedure (sysproc.admin_cmd is an administrative routine already available on server)

call sysproc.admin_cmd(?)

As you can see I used the question mark in order to transfer to the procedure the input parameter. I need to associate a value for it so I go to the corresponding transformation scenario and add a new parameter in the parameters dialog.
Please note that the parameter name you see in the parameters dialog (something like 'SQL-Parameter-1') does not matter, it only matter the order of parameters (so that the first parameter is associated with the first question mark a.s.o)

After setting the following string:
describe select * from staff
as the parameter value I was able to execute the SQL call and get the proper results.

Regarding your example, please avoid the usage of ; from the statement end. That is causing the depicted error message. Please make sure that you followed the previous steps described the DB2 9 pure XML Guide (page 122-123) and you did the DB2XMLFUNCTIONS.XMLUPDATE registration correctly.

Best regards,
Stefan.

Posted: Tue Mar 27, 2007 5:13 pm
by dmccreary
Hello Stefan,

Thank you again for your quick reply.

I tried taking out the semicolon and I added both one and five parameters (not sure how many I need) but I could not get the query to run. In this example, the values of the parameters are in the document itself so I am note sure why I need to specify them as transformation parameters.

The updates works fine from the DB2 console so I know that the update stored procedure is installed correctly.

Here is the new error message I am getting:

Code: Select all

Severity and Description	Path	Resource	Location	Creation Time	Id
[ibm][db2][jcc][10143][10845] Invalid parameter 1: Parameter is not set nor registered. db2-training update-county-sequence-id.sql Unknown 1175005426446 1780
The following runs without any problems from the DB2 Command Center console:

Code: Select all

-- Example of how to update the sequence ID for aitkin county
-- This will not run in
CALL DB2XMLFUNCTIONS.XMLUPDATE(
'<updates>
<update action="compute" col="1" path="//County[Code="aitkin"]/NextID/text()">
?+1
</update>
</updates>',
'select COUNTY from ECRVADMIN.COUNTY where id=0',
'update ECRVADMIN.COUNTY set COUNTY=? where id=0',?,?)
I will create a full example and post it if that would help.

Thanks again for your prompt replies. Our team hear really appreciates your support. I really do think you have a great product, I just think that DB2v9 is very new and the documentation just needs a little tuneup and I will stop bothering you :wink:

If we can get this to work I think we will be rockin. :D

Take care - Dan[/img]

Posted: Fri Mar 30, 2007 5:25 pm
by stefan
My example used a stored procedure that has only input parameters and these kind of stored procedures can be executed in oXygen SQL editor. We do not have yet support for output parameters (like your stored procedure has) .

We are working to a solution for this case. Please contact us at support@oxygenxml.com for more details.