| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| CALL statement - IN clause |
« View previous topic :: View next topic » |
| Author |
Message
|
| WMB_User |
 Posted: Mon Nov 17, 2008 7:24 am Post subject: CALL statement - IN clause |
 |
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
Broker environment: Linux/6.1.0.2
Database environment: Oracle/9iR2
I'm having difficulty getting the IN clause on a CALL statement to work properly.
When I hard-code the DataSource name on the properties tab of the Compute node, the following stored procedure call within the node works successfully, which tells me that the ODBC setting, DataSource, Schema, Package and Stored Procedure are all in place and defined correctly.
| Code: |
CALL dbProc('CustId','123456',Environment.TransCustId,Environment.ErrorCode,Environment.ErrorMessage);
CREATE PROCEDURE dbProc(IN P1 CHARACTER, IN P2 CHARACTER, OUT P3 CHARACTER, OUT P4 CHARACTER, OUT P5 CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME "EMSDEV.TranslationRule.getCustomerId"; |
But, when I simply remove the DataSource name from the properties tab on the Compute node, and use the following code I receive an IM002 SQL error: -- [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified --
| Code: |
DECLARE MyDSN CHARACTER 'EMSD';
CALL dbProc('CustId','123456',Environment.TransCustId,Environment.ErrorCode,Environment.ErrorMessage) IN Database.{'MyDSN'};
CREATE PROCEDURE dbProc(IN P1 CHARACTER, IN P2 CHARACTER, OUT P3 CHARACTER, OUT P4 CHARACTER, OUT P5 CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME "EMSDEV.Translation.getCustomerId"; |
Am I missing something elementary? |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Mon Nov 17, 2008 7:36 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| You still need to set a DSN on the node, even if you don't use the node level DSN. |
|
| Back to top |
|
|
| WMB_User |
| Posted: Mon Nov 17, 2008 8:04 am Post subject: |
|
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
Thanks for the reply, that indeed was elementary.
I made the recommended change, but now am receiving the following exception. As you can see it appears that the schema name (EMSDEV) has been replaced with another value (MYDSN).
Any thoughts?
| Code: |
RecoverableException
File:CHARACTER:F:\build\S610_P\src\DataFlowEngine\ImbDatabaseManager.cpp
Line:INTEGER:2039
Function:CHARACTER:ImbDatabaseManager::getDBProcedureParameterInformation
Type:CHARACTER:ComIbmDatabaseConnectionManager
Name:CHARACTER:ComIbmDatabaseConnectionManager
Label:CHARACTER:ComIbmDatabaseConnectionManager
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:2920
Text:CHARACTER:The procedure is unknown to the database and no definition could be found.
Insert
Type:INTEGER:5
Text:CHARACTER:EMSD.MYDSN.TRANSLATION.GETCUSTOMERID |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Nov 17, 2008 8:06 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| WMB_User wrote: |
Any thoughts?
|
Try {MyDSN} not {'MyDSN'}
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| WMB_User |
| Posted: Mon Nov 17, 2008 8:26 am Post subject: |
|
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
Not sure what I'm still doing wrong. The latest code still results in an exception (below), where the schema name (EMSDEV) id appears to be over-layed.
| Code: |
DECLARE MyDSN CHARACTER 'EMSD';
CALL dbProc('CustId','123456',Environment.TransCustId,Environment.ErrorCode,Environment.ErrorMessage) IN Database.{MyDSN};
CREATE PROCEDURE dbProc(IN P1 CHARACTER, IN P2 CHARACTER, OUT P3 CHARACTER, OUT P4 CHARACTER, OUT P5 CHARACTER)
LANGUAGE DATABASE EXTERNAL NAME "EMSDEV.Translation.getCustomerId"; |
| Code: |
RecoverableException
File:CHARACTER:F:\build\S610_P\src\DataFlowEngine\ImbDatabaseManager.cpp
Line:INTEGER:2039
Function:CHARACTER:ImbDatabaseManager::getDBProcedureParameterInformation
Type:CHARACTER:ComIbmDatabaseConnectionManager
Name:CHARACTER:ComIbmDatabaseConnectionManager
Label:CHARACTER:ComIbmDatabaseConnectionManager
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:2920
Text:CHARACTER:The procedure is unknown to the database and no definition could be found.
Insert
Type:INTEGER:5
Text:CHARACTER:EMSD.EMSD.TRANSLATION.GETCUSTOMERID |
Also, as suggested previously, the properties tab has EMSD as the DataSource value. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
