| Author |
Message
|
| Ash_MB |
 Posted: Fri Sep 21, 2012 4:24 am Post subject: Stored procedure Call from ESQL |
 |
|
Novice
Joined: 10 Sep 2012
Posts: 12
|
Hi,
I'm newbie to Message broker.
In my esql code, i'm trying to call stored procedure. But i'm recieving below exception:
The procedure '&1' with '&2' parameters could not be match with a corresponding Database stored procedure.
But the parameters i have cross checked with the DB team.
Please post your suggestion
Thanks  |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Fri Sep 21, 2012 5:10 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
The arg/return types don't match the signature of your SP; therefore, the database cannot reference the SP you are trying to use.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| Ash_MB |
| Posted: Fri Sep 21, 2012 5:18 am Post subject: |
|
|
Novice
Joined: 10 Sep 2012
Posts: 12
|
I'm trying to access SQL server, Can you confirm if this is correct syntax :
CALL Storedproc( param1, param2, param3, Environment.ResultSet1[]) IN Database.{DBSOURCE}.{SCHEMANAME};
CREATE PROCEDURE Storedproc(IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER) LANGUAGE DATABASE DYNAMIC RESULT SETS 1 EXTERNAL NAME "Storedproc";
 |
|
| Back to top |
|
|
| mgk |
| Posted: Fri Sep 21, 2012 5:27 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
From the Infocenter: ( http://publib.boulder.ibm.com/infocenter/wmbhelp/v8r0m0/topic/com.ibm.etools.mft.doc/ak04970_.htm )
| Quote: |
"SQL Server considers OUTPUT parameters from stored procedures as INPUT/OUTPUT parameters. If you declare them as OUT parameters in your ESQL you encounter a type mismatch error at run time. To avoid that mismatch you must declare SQL Server OUTPUT parameters as INOUT in your ESQL.
Use the SET NOCOUNT ON option, as shown in the preceding example, with SQL stored procedures for the following reasons:
To limit the amount of data returned from SQL Server to the broker.
To allow result sets to be returned correctly. " |
So change your OUT to be INOUT.
Kind Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Ash_MB |
| Posted: Fri Sep 21, 2012 5:59 am Post subject: |
|
|
Novice
Joined: 10 Sep 2012
Posts: 12
|
Thank you  |
|
| Back to top |
|
|
| Ash_MB |
| Posted: Fri Sep 21, 2012 6:08 am Post subject: |
|
|
Novice
Joined: 10 Sep 2012
Posts: 12
|
I have Array of output from Stored Proc to be displayed in csv format.
This= Environment.ResultSet1[]
SET OutputRoot.Properties.MessageFormat = 'CSV2';
SET OutputRoot.Properties.MessageSet = 'L7TVBAS002001';
SET OutputRoot.Properties.MessageType = 'LPCOUNT';
I have Used Like this to Convert Output to CSV.
But not sure how to Set the output as array.
Please help |
|
| Back to top |
|
|
|
|
