Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Using PREPARE to Call a Stored procedure

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Gowri Janakiraman

New User


Joined: 13 Oct 2012
Posts: 4
Location: India

PostPosted: Fri Oct 26, 2012 2:19 am    Post subject: Using PREPARE to Call a Stored procedure
Reply with quote

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
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1738
Location: Bloomington, IL

PostPosted: Fri Oct 26, 2012 2:53 am    Post subject: Reply to: Using PREPARE to Call a Stored procedure
Reply with quote

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
View user's profile Send private message
Gowri Janakiraman

New User


Joined: 13 Oct 2012
Posts: 4
Location: India

PostPosted: Fri Oct 26, 2012 3:42 pm    Post subject: Reply to: Using PREPARE to Call a Stored procedure
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Oct 26, 2012 5:21 pm    Post subject:
Reply with quote

The link you gave is a link to DB2 LUW manual.
this is what I found in DB2 z/OS 9.1

http://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
View user's profile Send private message
Gowri Janakiraman

New User


Joined: 13 Oct 2012
Posts: 4
Location: India

PostPosted: Wed Oct 31, 2012 4:57 pm    Post subject: Reply to: Using PREPARE to Call a Stored procedure
Reply with quote

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
View user's profile Send private message
Gowri Janakiraman

New User


Joined: 13 Oct 2012
Posts: 4
Location: India

PostPosted: Wed Jan 30, 2013 12:31 am    Post subject: Reply to: Using PREPARE to Call a Stored procedure
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Feb 05, 2013 3:46 pm    Post subject:
Reply with quote

I think you have to set the SQLDA for all parameters : IN,INOUT and OUT before calling
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Cobol EZASOKET call to SETSOCKOPT fails Andi1982 COBOL Programming 6 Thu Oct 06, 2016 7:12 pm
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us