| Author |
Message
|
| tlandich |
 Posted: Tue Jul 23, 2002 11:08 am Post subject: Calling External Stored Procedures from ESQL (WMQI v2.1.2) |
 |
|
Novice
Joined: 23 Jul 2002
Posts: 11
|
Has anyone tried calling an external stored procedure from ESQL, using the CREATE PROCEDURE statement in v2.1.2? I keep getting an error at runtime.
Here's my ESQL code:
DECLARE strtblname CHARACTER;
DECLARE strcolname CHARACTER;
DECLARE strvlist CHARACTER;
DECLARE strfound CHARACTER;
DECLARE strnotfound CHARACTER;
SET strtblname = InputBody.Test.TableName;
SET strcolname = InputBody.Test.ColName;
SET strvlist = InputBody.Test.ValueList;
CALL CodeSetLookup(strtblname, strcolname, strvlist, strfound, strnotfound);
SET OutputRoot.XML.Test.Results.FoundList = strfound;
SET OutputRoot.XML.Test.Results.NotFoundList = strnotfound;
CREATE PROCEDURE CodeSetLookup (
IN tblname CHARACTER,
IN colname CHARACTER,
IN vlist CHARACTER,
OUT foundlist CHARACTER,
OUT notfoundlist CHARACTER
) EXTERNAL NAME TestProc1;
--- end of code ---
In my DB2 database, I have defined a stored procedure, called 'TESTPROC1'.
I added the DSN and an arbitrary table as inputs in my compute node.
Has anyone worked on something similar?
 |
|
| Back to top |
|
 |
| kirani |
| Posted: Tue Jul 23, 2002 1:11 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
What is the Error message you are getting at runtime? Could you 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 |
|
|
| tlandich |
| Posted: Tue Jul 23, 2002 4:58 pm Post subject: exception details |
|
|
Novice
Joined: 23 Jul 2002
Posts: 11
|
The exception list contains a database exception, with the following inserted message:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
The trace file indicates the same. Here's an excerpt:
The following error occurred during execution of a database SQL statement against datasource ''. The SQL statement was '{ CALL TestProc1( ? , ? , ? , ? , ? ) }'. The parameters passed were ''.
2002-07-23 18:07:55.655998 463 DatabaseException BIP2321E: Database error: ODBC return code '-1'.
The message broker encountered an error whilst executing a database operation. The ODBC return code was '-1'. See the following messages for information obtained from the database pertaining to this error.
Use the following messages to determine the cause of the error. This is likely to be such things as incorrect datasource or table names. Then correct either the database or message broker configuration.
2002-07-23 18:07:55.655998 463 DatabaseException BIP2322E: Database error: SQL State 'IM002'; Native Error Code '0'; Error Text '[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified'.
The error has the following diagnostic information: SQL State 'IM002' SQL Native Error Code '0' SQL Error Text '[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified' |
|
| Back to top |
|
|
| kirani |
| Posted: Tue Jul 23, 2002 7:20 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Could you print complete ExceptionList here? From information you have posted here, I can see the Stored procedure parameters are getting displayed.
I have tried calling Stored Procedure in MS-SQL Server using PASSTHRU statement and it worked fine. Do you have valid table name entered in a compute name? Try calling sample stored procedure using PASSTHRU and see if that works.
_________________
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 |
|
|
| kolban |
| Posted: Wed Jul 24, 2002 3:52 am Post subject: |
|
|
Grand Master
Joined: 22 May 2001
Posts: 1072
Location: Fort Worth, TX, USA
|
Notice that in your ESQL, you never specified the name of the database on which the stored procedure was located.... as such, the error is stating that the target database is unknown.
I am assuming that this is a compute node....
In the compute node, press the "Input Data" button (this might not be the correct name). It is the button at top left panel. Enter the database name and some table (any table name).
Retry and let us known what happens. |
|
| Back to top |
|
|
| tlandich |
| Posted: Wed Jul 24, 2002 11:00 am Post subject: |
|
|
Novice
Joined: 23 Jul 2002
Posts: 11
|
First of all, thanks for your all your responses.
I've had success with PASSTHRU before, even with v2.0.2. But now I need to use the CREATE PROCEDURE statement so I can get values returned through output parameters.
When I specified my Database name (same as DSN) and a table in the input pane of my compute node, my message flow grinds to a halt at runtime. It won't even run in debug mode. I have to go to Task Manager and force the termination of DataFlowEngine.exe. processes, and stop/start my broker so I can get my machine operational. I know it's wierd but you gotta try it... |
|
| Back to top |
|
|
| tlandich |
| Posted: Fri Jul 26, 2002 2:16 pm Post subject: |
|
|
Novice
Joined: 23 Jul 2002
Posts: 11
|
Has anyone successfully called an external stored procedure (in DB2 v7.1) from ESQL using the CREATE PROCEDURE statement (in WMQI v2.1)?
I tried everything, including a simple stored procedure without any queries, but it still does not work for me. The message flow "hangs" right after it enters the input node. |
|
| Back to top |
|
|
| bolek |
| Posted: Sun Jul 28, 2002 10:20 pm Post subject: |
|
|
Apprentice
Joined: 25 Jul 2002
Posts: 35
Location: Germany
|
| tlandich wrote: |
Has anyone successfully called an external stored procedure (in DB2 v7.1) from ESQL using the CREATE PROCEDURE statement (in WMQI v2.1)?
I tried everything, including a simple stored procedure without any queries, but it still does not work for me. The message flow "hangs" right after it enters the input node. |
WMQI 2.1.2 + DB2 7.1 FixPack 4 = works fine on AIX. |
|
| Back to top |
|
|
| tlandich |
| Posted: Thu Aug 01, 2002 12:14 pm Post subject: |
|
|
Novice
Joined: 23 Jul 2002
Posts: 11
|
I finally got this to work! I simply recreated my DB2 database using the MQSI service account, which was originally using 'db2admin'. I suppose it was an authentication issue. I just wish the error message would say so.
Thanks again for all your responses. |
|
| Back to top |
|
|
|
|
