| Author |
Message
|
| udayaj |
 Posted: Thu Apr 17, 2003 9:53 am Post subject: calling Oracle Stored Proc with ESQL |
 |
|
Newbie
Joined: 11 Jul 2001
Posts: 4
|
Hi
I am trying to execute a oralce stored produce with in a compute node and I am getting an error. I am trying to pass a part of an xml string which is coming as input.
PASSTHRU('{call testpkg.test(?)}', InputRoot.XML.PRoot.(XML.Element)DataElement);
SqlExternalDbStmt::executeStmt
ImbOdbcHandle::checkRcInner
Root SQL exception
I would appreciate any information related to calling stored proc's using using xml strings from esql.
Thank you,
Uday |
|
| Back to top |
|
 |
| kirani |
| Posted: Thu Apr 17, 2003 10:31 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Is it possible to post your ExceptionList here?
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| udayaj |
| Posted: Thu Apr 17, 2003 10:53 am Post subject: |
|
|
Newbie
Joined: 11 Jul 2001
Posts: 4
|
Here is my exp list:
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbDataFlowNode.cpp'
(0x3000000)Line = 536
(0x3000000)Function = 'ImbDataFlowNode::createExceptionList'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '7da40a4b-f400-0000-0080-cd421d12712c'
(0x3000000)Label = 'TEST.Compute'
(0x3000000)Text = 'Node throwing exception'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)RecoverableException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbRdl/ImbRdlExternalDb.cpp'
(0x3000000)Line = 163
(0x3000000)Function = 'SqlExternalDbStmt::executeStmt'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '7da40a4b-f400-0000-0080-cd421d12712c'
(0x3000000)Label = 'TEST.Compute'
(0x3000000)Text = 'The following error occurred during execution of an SQL statement'
(0x3000000)Catalog = 'WMQIv210'
(0x3000000)Severity = 3
(0x3000000)Number = 2519
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '21'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '4'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = 'TES_TABLES'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '{call testpkg.test(?)}'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ''', '
)
(0x1000000)DatabaseException = (
(0x3000000)File = '/build/S210_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 143
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Root SQL exception |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Apr 17, 2003 12:45 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Your ExceptionList is not complete!
What is the value in your input XML field (DataElement)? As per the ExceptionList it's getting , into it.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| udayaj |
| Posted: Fri Apr 18, 2003 5:27 am Post subject: |
|
|
Newbie
Joined: 11 Jul 2001
Posts: 4
|
That's all it shows in the exp. list.
Here is how my compute node looks like (I have tried it two ways):
1:
PASSTHRU('{call testpkg.test(?)}', InputRoot.XML.PRoot.(XML.Element)DataElement);
RETURN;
SET OutputRoot.XML.PRoot.(XML.Element)DataElement = InputRoot.XML.PRoot.(XML.Element)DataElement;
2:
DECLARE InputStr character;
SET InputStr = InputRoot.XML.PRoot.(XML.Element)DataElement;
PASSTHRU('{call testpkg.test(?)}', InputStr);
RETURN;
SET OutputRoot.XML.PRoot.(XML.Element)DataElement = InputStr;
Both the ways it is giving the same error.
Is there any information available on esql and xml ?? |
|
| Back to top |
|
|
| kirani |
| Posted: Fri Apr 18, 2003 12:01 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Could you add a Trace node before this compute node and print ${Root} in it. First let's see what value you are getting into InputRoot.XML.PRoot.(XML.Element)DataElement field.
If you have WMQI2.1 CSD2 or above try using CREATE PROCEDURE command to call a Stored procedure. You may refer to ESQL Reference Manual for more information on this command.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| udayaj |
| Posted: Mon Apr 21, 2003 10:16 am Post subject: |
|
|
Newbie
Joined: 11 Jul 2001
Posts: 4
|
Now it is going thru.But the problem is, it is not updating the string?
Can the stored procedure return the output value in the same argument.
i.e Like if I code..
SET OutputRoot.XML = InputRoot.XML;
PASSTHRU('{call test_sql(?)}', OutputRoot.XML.TESTROOT.(XML.tag)TESTDATA);
TESTDATA is having more child elements under that. I want to pass complete xml string with tags under the TESTDATA.
example:
<TESTROOT><TESTDATA><DATA1></DATA1><DATA2></DATA2>....</TESTDATA></TESTROOT>
I want to pass the complete <TESTDATA>.....</TESTDATA> string to stored proc and then receive the converted string into the same tags.
the result I am looking for is some thing like this.
<TESTROOT><TESTDATA><DATA1%lt</DATA1%lt<DATA2%lt</DATA2%lt</TESTROOT>
When I call like this, the msg is not failing but it is not giving the right output. Is it the right way to pass xml string to a stored proc. If it is then can I use the same argument to get the return value from the stored proce??
Thank you,
Uday. |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Apr 22, 2003 10:40 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Uday,
There are some limitations when calling a stored procdure using PASSTHRU. Please take a look at following thread,
http://www.mqseries.net/phpBB2/viewtopic.php?t=6434&highlight=stored+procedure
I'd recommend that you use CREATE PROCEDURE command to do this.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
|
|
