| Author |
Message
|
| jh.devos |
 Posted: Tue Oct 18, 2005 4:46 am Post subject: Overloaded versions issue on Oracle stored procedure call |
 |
|
Newbie
Joined: 18 Oct 2005
Posts: 6
|
I'm trying to make a call of an Oracle stored procedure through a simple flow. In order to do that, I've define a local procedure which points towards the remote procedure (EXTERNAL NAME) and the node data source points to the correct remote database. The remote procedure exists and is reachable from the deployment location. An ODBC connection is correctly set and the mqsisetdbparms command is well executed. The account used owns the sufficient right to run the procedure. Moreover the parameters schema of the local procedure is exactly the same than the stored procedure.
Actually, when I try to process a message, the flow fails and the trace indicates that the call cannot be executed due overloaded versions procedures for a specific schema.
However, the procedure isn't overloaded at all and I can't understand why such an error is raised.
I fear an ODBC driver bug. Did somebody had the same issue ? Has anybody an idea about the reason of this trouble ?
Thanks in advance for your assistance |
|
| Back to top |
|
 |
| JT |
| Posted: Tue Oct 18, 2005 2:30 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
What's the version/CSD-level of WBIMB?
What the version of Oracle?
Provide the exact text of the error message.
We issue numerous calls to Oracle stored procedures from our message brokers, and have yet to encounter that error. |
|
| Back to top |
|
|
| jh.devos |
| Posted: Wed Oct 19, 2005 12:22 am Post subject: |
|
|
Newbie
Joined: 18 Oct 2005
Posts: 6
|
I'm currently using CSD-Level 5
The Oracle database version is 8.1.7, the oracle ODBC driver too.
The error message is :
BIP2922E: The database reports that the procedure 'IP_500_validatezipcode' in schema 'MQSI' has '2' overloaded versions. This is not supported, as it cannot be determined which of the '2' definitions is required.
If the procedure 'IP_500_validatezipcode' is not already qualified with a schema name, qualification may remove this problem. Otherwise ensure that the procedure 'IP_500_validatezipcode' is the only procedure of this name within schema 'MQSI'
Thx |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Oct 19, 2005 4:02 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Try removing the _ character from the procedure name. Also can you post the ESQL you are using to define the procedure.
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 |
|
|
| jh.devos |
| Posted: Wed Oct 19, 2005 5:30 am Post subject: |
|
|
Newbie
Joined: 18 Oct 2005
Posts: 6
|
| Thx MGK but I've already try to remove underscore from the procedure name. the problem remains ! |
|
| Back to top |
|
|
| JT |
| Posted: Wed Oct 19, 2005 5:54 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Post the relevent ESQL code. |
|
| Back to top |
|
|
| jh.devos |
| Posted: Wed Oct 19, 2005 11:31 pm Post subject: |
|
|
Newbie
Joined: 18 Oct 2005
Posts: 6
|
CREATE COMPUTE MODULE TestFlow_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
DECLARE zipCode CHAR '1000';
DECLARE energyType CHAR 'E';
DECLARE isValideZipCode BOOLEAN;
DECLARE eMail CHAR;
CALL ValidZipCode( zipCode, energyType, isValideZipCode, eMail);
SET OutputRoot.XML.OT.Valid = isValideZipCode;
SET OutputRoot.XML.OT.Mail = eMail;
RETURN TRUE;
END;
CREATE PROCEDURE ValidZipCode(
IN zipCode CHAR,
IN energyType CHAR,
OUT isZipCodeValid BOOLEAN,
OUT eMail CHAR
) EXTERNAL NAME "MQSI.validatezipcode";
END MODULE;
I've also try without specifying DB-schema, or by mentioning it on the procedure call ( CALL ... EXTERNAL SCHEMA 'MQSI' - with EXTERNAL NAME="%.validatezipcode") |
|
| Back to top |
|
|
| JT |
| Posted: Thu Oct 20, 2005 3:05 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Quote: |
| I fear an ODBC driver bug. |
What driver are you using ? Post the .odbc.ini file entry. |
|
| Back to top |
|
|
| jh.devos |
| Posted: Thu Oct 20, 2005 4:36 am Post subject: |
|
|
Newbie
Joined: 18 Oct 2005
Posts: 6
|
[ODBC 32 bit Data Sources]
WBRKBKDB=IBM DB2 ODBC DRIVER (32 bit)
WBRKCMDB=IBM DB2 ODBC DRIVER (32 bit)
MS Access Database=Microsoft Access Driver (*.mdb) (32 bit)
dBASE Files=Microsoft dBase Driver (*.dbf) (32 bit)
Excel Files=Microsoft Excel Driver (*.xls) (32 bit)
TEST=Oracle 8.1.7 (32 bit)
[WBRKBKDB]
Driver32=C:\IBM\SQLLIB\BIN\DB2CLI.DLL
[WBRKCMDB]
Driver32=C:\IBM\SQLLIB\BIN\DB2CLI.DLL
[MS Access Database]
Driver32=C:\WINDOWS\System32\odbcjt32.dll
[dBASE Files]
Driver32=C:\WINDOWS\System32\odbcjt32.dll
[Excel Files]
Driver32=C:\WINDOWS\System32\odbcjt32.dll
[TEST]
Driver32=C:\Program Files\ORANT8\bin\sqora32.dll |
|
| Back to top |
|
|
| JT |
| Posted: Thu Oct 20, 2005 5:07 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
|
| Back to top |
|
|
| jh.devos |
| Posted: Thu Oct 20, 2005 5:51 am Post subject: |
|
|
Newbie
Joined: 18 Oct 2005
Posts: 6
|
Things seem to work quite much better !
Some other config troubles appear, but I think I'm on track again.
Thank you so much for the pointer ! |
|
| Back to top |
|
|
|
|
