| Author |
Message
|
| akil |
 Posted: Thu Jul 31, 2014 2:54 am Post subject: ESQL/MSSQL Procedure Name |
 |
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
I have the following MSSQL procedures defined
| Code: |
CREATE PROCEDURE myProc1(IN batchID INTEGER, IN partnerID INTEGER, IN fileName CHARACTER) LANGUAGE DATABASE EXTERNAL NAME "dbo.dbSwapParams";
CREATE PROCEDURE myProc2(IN batchID INTEGER, IN partnerID INTEGER, IN fileName CHARACTER) LANGUAGE DATABASE EXTERNAL NAME "dbo.[HO-ABC]";
|
myProc1 gets called, while myProc2 fails with the following error.
| Code: |
The message broker detected an error whilst executing the function or procedure ''myProc2''. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error. : /build/slot2/S000_P/src/DataFlowEngine/ImbRdl/ImbRdlRoutine.cpp: 1362: SqlRoutine::invoke: :
BIP2920E: When attempting to obtain a procedure definition, the database reported that the procedure ''contact admin.dbo.[HO-ABC]'' does not exist, or cannot be accessed.
If the procedure ''contact admin.dbo.[HO-ABC]'' does not exist, you must create it before it can be called. Otherwise, ensure that the EXTERNAL NAME matches the name by which the procedure is known to the database, including any necessary schema or package qualifiers.
Make sure that this procedure exists, and that you have authority to access it and have qualified the EXTERNAL NAME if necessary, then redeploy the message flow. : /build/slot2/S000_P/src/DataFlowEngine/MessageServices/ImbDatabaseManager.cpp: 2882: ImbDatabaseManager::getDBProcedureParameterInformation: ComIbmDatabaseConnectionManager: ComIbmDatabaseConnectionManager
|
The procedure exists as I am able to call it via the SQLDeveloper (a JDBC based client), as follows
| Code: |
dbSwapParams 1,1,'he'
[HO-ABC] 2,2,'abc'
|
Looks like this is because of the '-' character in the name ..Is there any way to make this work?
_________________
Regards |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Thu Jul 31, 2014 3:07 am Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Can you create the same Stored Proc without the '-' and after changing the ESQL to reflect it, test the flow.
If that works then it may well be a problem with the ODBC Connection.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| akil |
| Posted: Thu Jul 31, 2014 4:03 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Yes, that works, the problem is with the hyphen (-) character..not sure how to go about bypassing it ..
Even tables names have the hyphen character, but that is handled correctly by the mapping node as long as the tables are put within square brackets..
Seems like some difference between odbc & jdbc... what can I do?
_________________
Regards |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jul 31, 2014 4:27 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Make sure the dashes are dashes and not a graphical representation of a character that looks like a dash (m-dash, n-dash).
This happens often when pasting and without looking at the hex values it is really hard to know whether you have the proper '-' character in your source.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| akil |
| Posted: Thu Jul 31, 2014 7:16 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
I typed it multiple times (typing & re-typing), I see that the problem occurs for an underscore character as well. Is there a setup that I am missing?
Then I thought of setting up a synonym (without an underscore / hyphen) , and then I see that synonyms are not supported as well ..
Looks like I've to create new wrapper procedures in the database which'll just call the existing ones..
_________________
Regards |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Aug 27, 2014 9:34 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Did you try it without the square brackets?
_________________
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 |
|
|
| akil |
| Posted: Wed Aug 27, 2014 9:37 am Post subject: |
|
|
Partisan
Joined: 27 May 2014
Posts: 338
Location: Mumbai
|
Hi
Yes, in that case it gives an ODBC access violation, should I paste the trace?
_________________
Regards |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Aug 27, 2014 9:42 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Yes, but it sounds like you need to raise a PMR...
_________________
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 |
|
|
|
|
