IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Using PREPARE to Call a Stored procedure


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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: 1788
Location: Bloomington, IL

PostPosted: Fri Oct 26, 2012 2:53 am
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
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: 1281
Location: Belgium

PostPosted: Fri Oct 26, 2012 5:21 pm
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

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
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
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: 1281
Location: Belgium

PostPosted: Tue Feb 05, 2013 3:46 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Error while running web tool kit REXX... CLIST & REXX 5
No new posts Call program, directly from panel CLIST & REXX 9
No new posts Batch call online program, EXCI task ... CICS 3
No new posts CSQBGET - Call giving completion code... COBOL Programming 3
No new posts Invoke stored procedure via batch JCL. DB2 2
Search our Forums:

Back to Top