Calling Stored Proc From Rpgle

[donna_ny]

hi

Can some one post code of how to call SQL stored procedure in an RPGLE.

Donna.

[kpmac]

Re: Calling Stored Proc From Rpgle

Hey, can you explain in words what you are trying to do?

I see that you are posting the same message all over the forum.

It might be easier for you to explain what the main objective of your stored procedure is and we will suggest ways for you to implement.

[donna_ny]

Re: Calling Stored Proc From Rpgle

I have a SQL stored proc which takes many input parameters and produces a result set.
I want to create a file out of this result set, since I understood I won't be able to call stored proc from CL program and QMQRY is nt working due to some constraints on my source files, I decided to code RPGLE in which Iam going to call this stored proc , my objective is when I call this RPGLE it should create a file/report of my resultset out of stored proc.

[kpmac]

Re: Calling Stored Proc From Rpgle

Do you want it to create a file or a report out of your result set?

Is your stored prodedure returning a result set or is it inputing data into a file (i.e. what is the return type of your procedure) ?

If it is createing a file and it inputs into a file (does not return a value) you can use RUNSQLSTM.

Paste the declaration lines of your procedure. It should look something like this:

CREATE PROCEDURE MyLib.MyProcedure
        LANGUAGE SQL 
        DETERMINISTIC
        MODIFIES SQL DATA
        DYNAMIC RESULT SETS 0 

[donna_ny]

Re: Calling Stored Proc From Rpgle

CREATE PROCEDURE MyLib.MyProcedure
IN (


VARIABLES;


)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN





END.

It looks like above , so now suggest me please?

Donna

[kpmac]

Re: Calling Stored Proc From Rpgle

Here is a quick and dirty example. Note that you must out some sort of loop around the fetch statement. You will be able to find many examples on this site that demonstrate how to use embedded sql.

You should really print out these articles and go through them. Chapter 7 of the Iseries Application Data Access - A Roadmap Cornerstone contains examples of everything you are asking.

http://code400.com/forum/showthread.php?t=2440&highlight=book

C/Exec Sql                                                        
C+  Call MyLib/MyProc()                                           
C/End-Exec                                                        
C/Exec Sql                                                        
C+ DECLARE C1 CURSOR FOR SELECT * FROM MyLib/WhateverMyFileNameIs 
C/End-Exec                                                        
C/Exec SQL                                                        
C+ FETCH NEXT FROM C1  INTO :MyResultFields                       
C/End-exec 

[donna_ny]

Re: Calling Stored Proc From Rpgle

what does dynamic result sets 1 means ?

[kpmac]

Re: Calling Stored Proc From Rpgle

The dynamic results sets 1 is your problem.

You cannot return a result from a procedure to RPGLE or SQL. You can only return a result set from a procedure when you are using odbc or jdbc.

Try changing your procedure to a function.

[donna_ny]

Re: Calling Stored Proc From Rpgle

how do I change to a function?

or is it okay if I simply change result sets 1 to 0 in stored proc?

also what does it exactly mean if result sets 1, does it mean that I can produce result set when using with jdbc or odbc only?
Thanks for your help

Donna