| Author |
Message
|
| catwood2 |
 Posted: Wed Jul 27, 2005 2:25 pm Post subject: WBIMB, Stored Proc, Oracle, Overloading (Is this obvious?) |
 |
|
Centurion
Joined: 17 May 2002
Posts: 108
|
Thanks in advance for thoughts.
Oracle10, WBIMB CSD5
Simple flow trying to execute the dbswapparms proc in the manual:
| Code: |
CREATE COMPUTE MODULE HelloWorld_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyMessageHeaders();
-- CALL CopyEntireMessage();
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = 'Hello';
SET inputOutputParm = 'World';
CALL swapParms( inputParm, outputParm, inputOutputParm );
Set OutputRoot.XML.msg.result= outputParm;
RETURN;
RETURN TRUE;
END;
CREATE PROCEDURE swapParms (
IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER
) EXTERNAL NAME "portal.HelloWorld2.dbSwapParms";
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE;
|
Trace the message and get the following error:
| Quote: |
2005-07-27 15:08:46.696216 10348 RecoverableException BIP2922E: The database reports that the procedure 'PORTAL.dbSwapParms' in schema 'PORTAL' has '2' overloaded versions. This is not supported, as it cannot be determined which of the'2' definitions is required.
If the procedure 'PORTAL.dbSwapParms' is not already qualified with a schema name, qualification may remove this problem. Otherwise ensure that the procedure 'PORTAL.dbSwapParms' is the only procedure of this name within schema 'PORTAL'
|
Of course, there is only 1 dbSwapParms in the db (as far as I can see in Toad and what the db guys say).
There are a variety of Hello World procs I've been trying to execute and they all come back overloaded by 2 error. As the manual is pretty clear on the External Name syntax - I don't see how that could be a factor.
thx
catwood2 |
|
| Back to top |
|
 |
| catwood2 |
| Posted: Wed Jul 27, 2005 2:50 pm Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
slight correction:
| Code: |
| EXTERNAL NAME "portal.HelloWorld2.dbSwapParms" |
should read
| Code: |
| EXTERNAL NAME "portal.dbSwapParms" |
thx |
|
| Back to top |
|
|
| JT |
| Posted: Wed Jul 27, 2005 3:03 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Can your problem be related to the fact that Oracle 10g is "not supported" for any OS platform except Linux ? |
|
| Back to top |
|
|
| catwood2 |
| Posted: Wed Jul 27, 2005 3:26 pm Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
|
| Back to top |
|
|
| JT |
| Posted: Wed Jul 27, 2005 3:29 pm Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
|
| Back to top |
|
|
| mgk |
| Posted: Thu Jul 28, 2005 1:35 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Can you get an ODBC trace of the first message through the broker after a deploy (which is when the broker reads the DB SP definitions) and post the sections that relate to the SQLProceures and SQLProcedureColumns calls please.
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 |
|
|
| Ian |
| Posted: Thu Jul 28, 2005 5:28 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
WBIMB v5 does not support Oracle 10g (yet).
WBIMB v5 currently ships the DataDirect 4.1 ODBC Driver Manager and DataDirect 4.1 Oracle driver which support Oracle 8.1.7 and Oracle 9i.
The DataDirect 5.0 ODBC Driver Manager and DataDirect 5.0 Oracle driver support Oracle 10g.
WBIMB v5 on Linux supports Oracle 10g via LCR016 (which includes the DataDirect 5.0 driver manager and driver).
If you are not running on Linux with LCR016 then you are not supported for Oracle 10g.
Are you enabled for WBIMB v5 and Oracle 10g ?
_________________
Regards, Ian |
|
| Back to top |
|
|
| catwood2 |
| Posted: Thu Jul 28, 2005 6:02 am Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
Thanks for the responses:
mgk - To date, I have not been able to get the ODBC for the oracle related activities...it will show me everything db2 related. I'll see if I can get somebody to look at that and generate the trace.
ian -
| Quote: |
| Are you enabled for WBIMB v5 and Oracle 10g ? |
I am not using the DataDirect 5.0 ODBC Manager and Oracle Data Direct5.0 driver. If I do will that enable me for Oracle 10 using WBIMB wintel?
thx
catwood |
|
| Back to top |
|
|
| Ian |
| Posted: Thu Jul 28, 2005 7:24 am Post subject: |
|
|
Disciple
Joined: 22 Nov 2002
Posts: 152
Location: London, UK
|
| Quote: |
mgk - To date, I have not been able to get the ODBC for the oracle related activities...it will show me everything db2 related. I'll see if I can get somebody to look at that and generate the trace.
|
???
ODBC trace will show all the ODBC API calls, for either Oracle or DB2, or both.
If you are not on Linux and using LCR016 then you can not use Oracle 10g and which case this discussion is a non starter as your Oracle environment will not work.
_________________
Regards, Ian |
|
| Back to top |
|
|
|
|
