| Author |
Message
|
| WMB_User |
 Posted: Mon Dec 08, 2008 10:21 am Post subject: BIP2111 |
 |
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
While calling a stored procedure that returns a NUMBER:
| Code: |
| FUNCTION getPackageId RETURN NUMBER; |
I encounter the followng exception:
| Code: |
RecoverableException
File:CHARACTER:F:\build\S610_P\src\DataFlowEngine\ImbDatabaseManager.cpp
Line:INTEGER:2341
Function:CHARACTER:ImbDatabaseManager::storedProcedureParameterTypeCheck()
Type:CHARACTER:
Name:CHARACTER:
Label:CHARACTER:
Catalog:CHARACTER:BIPv610
Severity:INTEGER:3
Number:INTEGER:2111
Text:CHARACTER:ImplementationError, invalid ESQL parameter direction |
My ESQL code is:
| Code: |
CALL dbProc4() IN Database.{VISTADSN}.{VISTASchema};
CREATE PROCEDURE dbProc4() LANGUAGE DATABASE EXTERNAL NAME "MIKEM.IP_ATTACHMENTS.getPackageId"; |
It appears that I'm not satisfying the RETURN NUMBER statement. I've tried a number of variations where I set an INTEGER parameter to the results of the called procedure, but to no avail. Can anyone assist? |
|
| Back to top |
|
 |
| mgk |
| Posted: Mon Dec 08, 2008 10:54 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Hi.
Could you post the stored procedure definition, and the DB type and version please. Also the broker version would be good.
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 |
|
|
| WMB_User |
| Posted: Mon Dec 08, 2008 1:30 pm Post subject: |
|
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
Oracle 9iR2 & WMB 6.1.0.2
| Code: |
FUNCTION getPackageId RETURN NUMBER IS
l_Id NUMBER;
Cursor GetNextId is
SELECT Pckg_Seq.NEXTVAL
FROM DUAL;
BEGIN
OPEN GetNextId;
FETCH GetNextId INTO l_Id;
CLOSE GetNextId;
RETURN l_Id;
END getPackageId; |
|
|
| Back to top |
|
|
| mgk |
| Posted: Tue Dec 09, 2008 5:35 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Hi.
I have had a look at this. The code is putting out the wrong error message in this case. The actual problem is caused by a signature mismatch between the DB function and the ESQL procedure. Because the DB function returns a value you must make the ESQL procedure also return a value like this:
| Code: |
DECLARE myInteger INTEGER;
CALL dbProc4() INTO myDecimal IN Database.{VISTADSN}.{VISTASchema};
CREATE PROCEDURE dbProc4() RETURNS DECIMAL LANGUAGE DATABASE EXTERNAL NAME "MIKEM.IP_ATTACHMENTS.getPackageId"; |
or this (depending of whether you need a DECIMAL or INTEGER
| Code: |
DECLARE myDecimal DECIMAL;
CALL dbProc4() INTO myInteger IN Database.{VISTADSN}.{VISTASchema};
CREATE PROCEDURE dbProc4() RETURNS INTEGER LANGUAGE DATABASE EXTERNAL NAME "MIKEM.IP_ATTACHMENTS.getPackageId"; |
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 |
|
|
| WMB_User |
| Posted: Tue Dec 09, 2008 9:39 am Post subject: |
|
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
mgk, that was one of the attempts I had made previously, but the toolkit flags the statement as a syntax error "Syntax error. Valid options include:; "
| Code: |
DECLARE packageId INTEGER;
CALL dbProc4() INTO packageId IN Database.{VISTADSN}.{VISTASchema}; |
|
|
| Back to top |
|
|
| mgk |
| Posted: Tue Dec 09, 2008 10:30 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Hi.
I mixed up the syntax when I posted. The syntax should be:
| Code: |
DECLARE packageId INTEGER;
CALL dbProc4() IN Database.{VISTADSN}.{VISTASchema} INTO packageId ;
|
_________________
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 |
|
|
| WMB_User |
| Posted: Tue Dec 09, 2008 12:14 pm Post subject: |
|
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
Looks like the toolkit doesn't like this statement either. A new syntax error - "The called procdure must return a result"
| Code: |
DECLARE packageId INTEGER;
CALL dbProc4() IN Database.{VISTADSN}.{VISTASchema} INTO packageId; |
|
|
| Back to top |
|
|
| mgk |
| Posted: Tue Dec 09, 2008 2:08 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1647
|
Hi.
Did you also add the matching RETURNS INTEGER syntax to the procedure definition as shown?
| Code: |
| CREATE PROCEDURE dbProc4() RETURNS INTEGER LANGUAGE DATABASE EXTERNAL NAME "MIKEM.IP_ATTACHMENTS.getPackageId"; |
If you did and you are still getting this error, can you post the version of the broker toolkit you are using?
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 |
|
|
| WMB_User |
| Posted: Wed Dec 10, 2008 12:55 pm Post subject: |
|
|
Apprentice
Joined: 17 Nov 2008
Posts: 31
|
You were correct, I did forget to add the matching RETURNS INTEGER syntax to the procedure definition. It's working.
Thanks for the assist !! |
|
| Back to top |
|
|
|
|
