Re: stored proc with dynamic result sets 1, can I return result set in RPGLE ? how?

Hi Vern
   
  Thanks for useful information.
   
  My stored proc is real big one, I felt (1)CRTQMQRY and then STRQMQRY with 
OUTFILE would be the perfect and simple solution but its nt working due to 
large no. of SQL lines in stored procedure. It is throwing exception when I run 
STRQMQRY.
   
  My users dont have access to operations navigator to see the result set when 
they call this stored procedure. If I have no simple option I have to go for 
SQL CLI as suggested by you.
   
  But is there any simple solution available like QMQRY to provide result set 
in the form of file to the user ?
   
  Donna.

Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
  You cannot get a result set from a stored procedure using embedded 
SQL in RPG. You have to use the SQL CLI (call-level interface). It 
works fine, is just function calls into a service program. There are 
some /copy members on the net that have the prototypes, etc. I wrote 
my own for a product of ours that accommodates multiple target 
releases, but you'd do fine with just the latest, probably.

Try this link - http://www.tools400.de/Downloads/Freeware/Code/sqlcli.zip

And the manual at infocenter is essential.

HTH
Vern

At 11:33 AM 11/18/2006, you wrote:

I have a stored proc as below, I like to write RPGLE by issuing 
CALL to stored proc to generate result set into a file? is that 
possible for below stored procedure format?

CREATE PROCEDURE MyLib.MyProcedure
IN (


VARIABLES;

)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN





END.

Vernon Hamberg wrote:
Donna - you have to use CLI (call level interface) to get result sets
from stored procedures in RPG. It's not too hard - you could do a
search in the list archives and find a link to some good starting out stuff.

At 09:10 PM 11/17/2006, you wrote:

Hi
I have a stored proc as below which is very huge, when I try to
run STRQMQRY it says SQL length is big and its failing.

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.

Some one advised me that I cannot return a result from a
procedure in RPGLE or SQL when dynamic result sets 1 (see below
code). You can only return a result set from a procedure when you
are using odbc or jdbc. Is that true?

How do I create a file out of my result set in stored proc in
RPGLE? any examples/sample code or any pointers are highly appreciated.

------------------------------------------------
I cant publish my stored proc since its very huge around 5000 lines.

CREATE PROCEDURE MyLib.MyProcedure
IN (


VARIABLES;


)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN





END.
----------------------------------------------------------------


Thanks,

Donna.

---------------------------------
Check out the all-new Yahoo! Mail beta - Fire up a more powerful
email and get things done faster.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




Thanks,

Donna.

---------------------------------
Everyone is raving about the all-new Yahoo! Mail beta.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.