| Author |
Message
|
| kirani |
 Posted: Wed Jan 23, 2002 6:42 pm Post subject: |
 |
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I have a very simple stored procedure defined in SQL Server 2000 database. The code to create the stored procedure is as follws,
CREATE PROCEDURE sp_bank
@CODE char (3)
AS
Begin
print 'Value is ' + @CODE
select DETAILS from tbl_fu_bank WHERE CODE = @CODE
End
GO
In my compute node, I am calling this stored procedure using following ESQL,
SET OutputRoot.WKSPACE.Output[] = PASSTHRU('exec sp_bank 111');
I tried executing the stored procedure from SQL Query Analyzer by making following call,
exec sp_bank 112
and it works fine.
I get following error during runtime. The Exception List is ...
(
(0x1000000)RecoverableException = (
(0x3000000)File = 'F:/build/S202_P/src/DataFlowEngine/ImbComputeNode.cpp'
(0x3000000)Line = 354
(0x3000000)Function = 'ImbComputeNode::evaluate'
(0x3000000)Type = 'ComIbmComputeNode'
(0x3000000)Name = '90bfc895-eb00-0000-0080-ff4d00d758d4'
(0x3000000)Label = 'kiran_group_msg.Compute3'
(0x3000000)Text = 'Caught exception and rethrowing'
(0x3000000)Catalog = 'MQSIv202'
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)DatabaseException = (
(0x3000000)File = 'F:/build/S202_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 121
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Root SQL exception'
(0x3000000)Catalog = 'MQSIv202'
(0x3000000)Severity = 3
(0x3000000)Number = 2321
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '-1'
)
(0x1000000)DatabaseException = (
(0x3000000)File = 'F:/build/S202_P/src/DataFlowEngine/ImbOdbc.cpp'
(0x3000000)Line = 200
(0x3000000)Function = 'ImbOdbcHandle::checkRcInner'
(0x3000000)Type = ''
(0x3000000)Name = ''
(0x3000000)Label = ''
(0x3000000)Text = 'Child SQL exception'
(0x3000000)Catalog = 'MQSIv202'
(0x3000000)Severity = 3
(0x3000000)Number = 2322
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '24000'
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = '0'
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = '[Microsoft][ODBC SQL Server Driver]Invalid cursor state'
)
)
)
)
)
Can anyone help me in this regard? Am I missing something?
I tried changing my ESQL to
SET OutputRoot.WKSPACE.Output[] = PASSTHRU('exec sp_bank ''111''');
But I get the same error.
Thanks in advance.
Kiran
|
|
| Back to top |
|
 |
| amigupta1978 |
| Posted: Wed Jan 23, 2002 8:05 pm Post subject: |
|
|
Centurion
Joined: 22 Jan 2002
Posts: 132
Location: India
|
Hi,
Just try calling this way
Passthru ('call PROCNAME(?)','111')
or
Passthru ('exec PROCNAME(?)','111')
Amit |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Jan 23, 2002 10:57 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I had already tried that, but it gave me a different error.
Thanks
Kiran
|
|
| Back to top |
|
|
| Outdesign |
| Posted: Thu Jan 24, 2002 10:27 am Post subject: |
|
|
Apprentice
Joined: 16 Sep 2001
Posts: 38
Location: Hampshire, UK
|
The syntax for invoking a stored procedure within MQSI is :
Database Node :
===============
PASSTHRU('call proc_insert(?,?)', Body.Message.Param1, Body.Message.Param2);
In theory you ESQL statement should look something like :
Compute Node :
==============
SET OutputRoot.WKSPACE.Output[] = PASSTHRU('call sp_bank(?)', '111');
HOWEVER,
read the section on PASSTHRU in the MQSIv2.0.1 ESQL Reference manual
and there is a "Limitations" sub-section ...
1. MQSeries Integrator Version 2.0.* (and 2.1) only supports input parameters.
Your example expects data to be returned from the stored procedure.
We have found that in some instances this works but it is limited and more importantly,
it is NOT supported.
IBM will hopefully provide this functionality (support for input/output parameters)
be in a future release of the product.
|
|
| Back to top |
|
|
| Galichet |
| Posted: Thu Jan 31, 2002 5:12 am Post subject: |
|
|
Acolyte
Joined: 26 Jun 2001
Posts: 69
Location: Paris - France
|
A suggestion :
If you have only one value to return, I think you could have a look to stored functions. I think I could work in your case.
The stored function can be invoked in MQSI by a Select ... (in a Passthru)
I've used it one day for a specific flow and all worked fine
_________________
Eric Galichet
SMABTP
France |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Jan 31, 2002 8:46 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
| could you please post an example here. |
|
| Back to top |
|
|
| Galichet |
| Posted: Fri Feb 08, 2002 3:29 am Post subject: |
|
|
Acolyte
Joined: 26 Jun 2001
Posts: 69
Location: Paris - France
|
Hi, Here is an example calling a stored function from a compute node
DECLARE DELIM CHAR;
SET DELIM = TRIM(' '' ');
SET OutputRoot.XML.MSG.TEMP[] = PASSTHRU('SELECT FCT2(' || DELIM || InputRoot.XML.MSG.RAYON || DELIM || ') FROM DUAL');
SET OutputRoot.XML.MSG.RESULTAT = OutputRoot.XML.MSG.TEMP[1].*[1];
SET OutputRoot.XML.MSG.TEMP = UNKNOWN;
The stored function is FCT2
_________________
Eric Galichet
IBM Global Services
France
[ This Message was edited by: galichet on 2002-02-08 03:30 ] |
|
| Back to top |
|
|
| MikeTamari |
| Posted: Tue Feb 12, 2002 5:37 am Post subject: |
|
|
Apprentice
Joined: 04 Feb 2002
Posts: 26
|
Can I use passthru to envoke Oracle packages ?
Mike T |
|
| Back to top |
|
|
|
|
