| Author |
Message
|
| andrewhirst |
 Posted: Tue Jul 06, 2004 12:14 am Post subject: DB2 Stored Procedures |
 |
|
Apprentice
Joined: 06 Jul 2004
Posts: 33
Location: UK
|
Hello,
I'm trying to set up a compute node that calls a DB2 stored procedure under MQSI 2.1. I have been looking for a useful example of what needs to be done in ESQL, but there do not seem to be many around.
I have created an external procedure that refers to the stored procedure that I want to execute and made a call to the procedure - shown below:
--Call the external database stored procedure
CALL LabourRate( 'BB', ' 002', '076');
--define the external database stored procedure
CREATE PROCEDURE LabourRate(
IN BRAND CHARACTER, IN RTLR CHARACTER, IN MODEL CHARACTER
)EXTERNAL NAME "LAB_RATE";
It doesn't work..... 
I have a few questions regarding the ESQL:
1. How can I tell the ESQL where the stored procedure is located?
2. How can I retreive the resultsets that this procedure returns?
If anyone can supply a complete sample and some guidance, it would be greatly appreciated.
Many thanks,
Andrew Hirst |
|
| Back to top |
|
 |
| mgk |
| Posted: Tue Jul 06, 2004 2:15 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
| Quote: |
| 1. How can I tell the ESQL where the stored procedure is located? |
Unless you provide a schema name as part of the EXTERNAL NAME clause ESQL will assume that the SP lives in a schema that has the same name as the brokers db user name (the one you give when you create the broker) You can override this in you wish, e.g EXERNAL NAME "mySchema.LAB_RATE". The actual DB we look in is provided by the DATASOURCE property on the node.
| Quote: |
| 2. How can I retreive the resultsets that this procedure returns? |
You can't with the CREATE PROCEDURE syntax. The PASSTHRU statement allows you to CALL a SP that returns one (and only one) result set, but that ;procedure cannot have any OUT or INOUT parameters.
e.g. (note I have not tested this code before typing, but the principle does work even if my syntax is out slightly) SET OutputRoot.XML.Top[] = PASSTHRU( 'CALL MySchema.MyProc( p1, p2)' ); Note you can (and should use parameter markers rather than hard coding values. See the PASSTHRU statement docs.
Regards,
[/quote]
_________________
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 |
|
|
| fjcarretero |
| Posted: Tue Jul 06, 2004 5:08 am Post subject: |
|
|
Voyager
Joined: 13 Oct 2003
Posts: 88
|
mgk,
Does the passthru function allow you to call stored functions?
If so, can you return any data type?
Cheers
Felipe |
|
| Back to top |
|
|
| andrewhirst |
| Posted: Tue Jul 06, 2004 5:54 am Post subject: RE: DB2 Stored Procedures |
|
|
Apprentice
Joined: 06 Jul 2004
Posts: 33
Location: UK
|
MKG,
Many thanks for your help.
| Quote: |
| The actual DB we look in is provided by the DATASOURCE property on the node. |
When I specify the datasource, the dialog box also asks for a table. I used the name of the SP here. Is that correct?
TIA. |
|
| Back to top |
|
|
| mgk |
| Posted: Tue Jul 06, 2004 7:24 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Felipe, no there is no way at present to call a stored function that has an actual return value. You could try to wrap the call to the function inside a call to a procedure and return the return value as an OUT parameter.
Andrew, You should not need to specify a table, just the datasource.
Cheers,
_________________
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 |
|
|
| JT |
| Posted: Tue Jul 06, 2004 8:03 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
|
| Back to top |
|
|
| djeripo |
| Posted: Tue Jul 06, 2004 9:14 am Post subject: |
|
|
Master
Joined: 25 Jan 2004
Posts: 225
|
Andrew,
I have been successfully using CALL statement to call a SP for quite a some time . I am not sure how PASSTHRU works as I have never tried that .
I call DB2 StoredProcedures by 'CALL' statement .
I will give you an example how it works,
Here CTP1020 is the name of a SP.
CALL CTP1020(PLCY_POLICY_NUMBER,PLCY_INDICATOR,OUTPUT_VAL_FORM_CODE,
OUTPUT_PLAN_CODE,OUTPUT_RETURN_CODE,OUTPUT_MESSAGE);
For me the SP is success only if the OUTPUT_RETURN_CODE = 0(SQL Return Code) .
Else its a failure (Meaning SP is not returning all the Output Parameters) .
This is how the person who has written this SP has developed .
This may not be the same case every where .
Create Procedure part is included at the end of ESQL .
CREATE PROCEDURE CTP1020 (
IN IC_AR_POL_ACCT_NUM CHARACTER,
IN IC_SBG_COV_IND CHARACTER,
OUT OC_LF1_VAL_FRM_CDE CHARACTER,
OUT OC_PLAN_NAME CHARACTER,
OUT OI_RETURN_CDE INTEGER ,
OUT OC_MESSAGE CHARACTER
) EXTERNAL NAME "PCTP001.CTP1020";
Andrew Wrote :
| Quote: |
| 1. How can I tell the ESQL where the stored procedure is located? |
In the compute node when you click on Add button it asks for Data Source and Table Name .
For Data Source specify the DATABASE name and for
Table Name specify the StoredProcedure name CTP1020 (As in the eg:)
I am sorry if I confuse you between CALL & PASSTHRU at this point. |
|
| Back to top |
|
|
| fjcarretero |
| Posted: Wed Jul 07, 2004 2:01 am Post subject: |
|
|
Voyager
Joined: 13 Oct 2003
Posts: 88
|
Matthew,
I have created the following stored procedure in Oracle:
| Code: |
CREATE OR REPLACE package types
as
type cursorType is ref cursor;
end;
/
CREATE OR REPLACE function wbimb_proc (l_cursor IN OUT types.cursortype)
as
begin
open l_cursor for select 23 "gb" from dual;
end;
/
|
And this is my ESQL:
| Code: |
SET OutputLocalEnvironment.Variables.Test[] = PASSTHRU('{ call wbimb_proc() }');
|
This works fine. I want to know if this is what you meant when you said
| Quote: |
| The PASSTHRU statement allows you to CALL a SP that returns one (and only one) result set, but that ;procedure cannot have any OUT or INOUT parameters. |
as this is the only way I know to return a resultset (as an (IN)OUT param).
If not, is the above code supported by IBM.
TIA.
Cheers
Felipe |
|
| Back to top |
|
|
| andrewhirst |
| Posted: Thu Jul 08, 2004 2:06 am Post subject: |
|
|
Apprentice
Joined: 06 Jul 2004
Posts: 33
Location: UK
|
Many thanks for all of your contributions. Call and passthru now work fine on my system. There was one snag at the end and I was getting an ODBC error. That turned out to be due to a priveledges problem as the DB administrator had not allowed the broker to execute the SPs.
Regards,
Andrew. |
|
| Back to top |
|
|
|
|
