View previous topic :: View next topic
|
Author |
Message |
Gowri Janakiraman
New User
Joined: 13 Oct 2012 Posts: 4 Location: India
|
|
|
|
Hi,
We are trying to create a generic COBOL program which can call any stored procedure. We are planing to give the input parameters and output variables using SYSIN. We are trying to execute the Call statement dynamically using PREPARE. I am getting SQL CODE -302. It would be great if you could provide some help or ideas to dynamically call any stored procedure. |
|
Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009 Posts: 1788 Location: Bloomington, IL
|
|
|
|
As you will see from the explanation of SQLCODE -302 in the fine manual, you strictly speaking cannot write a COBOL problem that can call any stored procedure. You can, though, write a COBOL program that will edit input to exclude garbage, with suitable error processing, and then execute the calls that stand some chance of working. |
|
Back to top |
|
|
Gowri Janakiraman
New User
Joined: 13 Oct 2012 Posts: 4 Location: India
|
|
|
|
Thanks for the reply. I already got the explanation for the SQLCODE-302. I am looking for ways to call the stored procedure using PREPARE statement in COBOL. Our code to call the stored procedure using static SQL is working fine using COBOL. I could see in the link PREPARE STATEMENT can be used for CALL statements too. I would like to know how the CALL statment should be coded along with its input and output parameters. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
The link you gave is a link to DB2 LUW manual.
this is what I found in DB2 z/OS 9.1
publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_call.htm
Quote: |
This statement can be embedded in an application program. This statement can be executed interactively using the command line processor. Refer to DB2 Application Programming and SQL Guide for information about using the command line processor with the CALL statement. This statement can also be dynamically prepared, but only from an ODBC or CLI driver that supports dynamic CALL statements. IBM's ODBC and CLI drivers provide this capability |
I haven't tried it, but I guess you could
Code: |
EXEC SQL CALL :procnm USING DESCRIPTOR :sqlda END-EXEC |
|
|
Back to top |
|
|
Gowri Janakiraman
New User
Joined: 13 Oct 2012 Posts: 4 Location: India
|
|
|
|
Yes, you are correct ,using PREPARE to call stored procedure does not work with DB2 in Mainframe environment, thanks for catching that. I tried using descriptor to call stored procedure and it worked. I queried SYSIBM.SYSPARMS tables to get all the parameters for the stored procedure and then set the SQLDA varaibles for input parameters. |
|
Back to top |
|
|
Gowri Janakiraman
New User
Joined: 13 Oct 2012 Posts: 4 Location: India
|
|
|
|
In COBOL program -
I queried SYSIBM tables and got the input and output parameters for the given stored proc. Used CAll USING SQLDA to call the stored procedure.I have the details about the resultset in SQLDA and am able to read the resultsets also using Associate Locators. But I am struck with finding ways to get the values set by the stored proc in the output parameters. I cannot find any field in the SQLDA that would specify the value set by the stored proc for output parameters.
Please let me know if there is any way to get them. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
I think you have to set the SQLDA for all parameters : IN,INOUT and OUT before calling |
|
Back to top |
|
|
|