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

Cool! Here is the link to the redbook:

http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf

I also highly recommend www.iseries.ibm.com/db2 and look at thesupport tab - there are links to the manuals and various examples.

Calling the stored procedure with a CALL statement in a QMQRY will donothing, or it will get you an error that there is not data to SAVEif you try to put it to an outfile.

Maybe for this the preferred way to create the procedure is to useRUNSQLSTM, not STRQMQRY. But you should not have to run the CREATEPROCEDURE more than once, in any case. And never in a productionenvironment - I know, never say never.


Anyway, good luck
Vern

At 09:58 AM 11/19/2006, you wrote:

I already have stored procedure in my system which exceeds 32767characters, I just want to create result set into a file and show ituser, thats all!I could do it by running ops navigator but user does n't 'veaccess to it so I thought I should create a program in which I runthis stored proc to generate a file in outfile or print on display.


  You are true I get below errors :
  _________________________________________________________
  Message . . . . : IMPORT source record length exceeds 79 characters.
Cause . . . . . : The record length of the source file from which query QRY1
in library dlester was imported, exceeded the record length recommended by
query management. Any data in excess of 79 characters is truncated.
Recovery . . . : Query management recommends that a source file have a
record length of 91 characters (line number=6, date=6, data=79). If valid
data was truncated, create the source data again, and ensure valid data does
not exceed 79 characters.
  _____________________________________________________________
  AND
  SQL LENGTH IS TOO BIG.....


  As per you I cant create a file to user unless I use CLI ? right?

  I have nt read the red book, if you have a link to it do post here.

  Thanks a lot for your help!

  Donna.

Vernon Hamberg <vhamberg@xxxxxxxxxxx> wrote:
  Hi Donna

Well, let me try to understand better. First you have to have created
a stored procedure. That is a one-time thing. If the CREATE PROCEDURE
statement is in member QRY1 and you run the STRQMQRY over it, you
don't do it again. Now QMQRY does have limits - here are some from
the Query Management Programming manual:

Query restrictions in Query Management
The following restrictions apply to queries handled by Query Management:
- A query is limited by the size of the source file.
- A single line of the query cannot exceed 79 bytes.
- Substitution variable values can be up to 55 characters long.
- Substitution variable names cannot exceed 30 characters.
- Comments must be preceded by a double hyphen (--). Everything
between the double hyphen and the
end of the line is considered to be part of the comment.
- The total query cannot exceed 32767 bytes after comments and blanks
are removed and variable
substitution is made.

Does your create procedure statement exceed 32767 characters? If not,
then the message is probably a mistake but you still can't create the
procedure.

Also, what is the record length for QSQLSRC - the record length for
QMQRY source has to be 91 - 12 more than the 79 limit above, to
accommodate source date and sequence number.

On the other hand, if you have created the procedure, then your QMQRY
should be a CALL - but it will not work anyway.

RESULT SETS 1 says how many result sets there can be from the stored
procedure - there can be more than 1. If you were using an external
stored procedure (another RPG program) each unclosed cursor is a
result set, as well as 1 result set based on an MODS.

I assume you have see the redbook on stored procedures, functions and
triggers. The advice you got to declare a cursor over a stored
procedure just was not right. The redbook clearly states where you
can use result sets from stored procedures.

I guess I am still confused - things don't seem to line up as I am
used to seeing them.

HTH
Vern

At 08:45 AM 11/19/2006, you wrote:

>Hi Vern,
>
> I have the stored procedure saved as TXT under QSQLSRC in my lib.
> So I tried CRTQMQRY QMQRY(QRY1) SRCFILE(DLESTER/QSQLSRC) , it
> worked and created QRY1 then
> I issued STRQMQRY QMQRY(DLESTER/QRY1) OUTPUT(*OUTFILE)
> OUTFILE(QTEMP/TESTFILE) , but it failed citing the reason SQL
> length is too big.
>
> On some advise I thought I should try
>
> C/Exec Sql
>C+ Call MyLib/MyProc()
>C/End-Exec
>C/Exec Sql
>C+ DECLARE C1 CURSOR FOR SELECT * FROM MyLib/stored proc
>C/End-Exec
>C/Exec SQL
>C+ FETCH NEXT FROM C1 INTO :MyResultFields
>C/End-exec
>
>
> but was nt successful in handling such a big stored procedure.
>
> The body of stored procedure is as below:
> _________________________________________________________________
>
> CREATE PROCEDURE MYPROCEDURE
> (IN
> VARIABLES;
> VARIABLES;
> .....
> .....
> )
> DYNAMIC RESULT SETS 1
> LANGUAGE SQL
> BEGIN
> /*******declare variables*********/
>
> /*****declare cursors and select &where conditions*********/
> /*****begin code for error handling************/
> /****Main logic*****************************/
> Call xxxx(parm1, parm2.............)
> INSERT statement
> select statements etc
>
> end
> /******end main logic**************/
>
> __________________________________________________________________
>
> Hope it is clear now what Iam trying to do(main objective is to
> produce result set into a file and show it to user as user does nt
> have access to operations navigator), also I donot understand what
> RESULTSETS 1 means?
>
> As suggested I 'll try CLI and update soon.
>
>
> Donna.
>
>
>
>
>
>
>Vernon Hamberg wrote:
> Hi Donna
>
>I think I'm a little confused - are you calling a stored procedure in
>your QMQRY? That would not seem to take, what, 32000+ lines. Besides,
>the result set from a stored procedure is not available in a QMQRY
>either. If you specify the display for output, it just returns to the
>command line. If you say outfile, you get and error, because there is
>no usable result set.
>
>Somewhere do you have a CREATE PROCEDURE statement?
>
>Or do you have the body of what WOULD go into a stored procedure in
>the QMQRY? What kind of error are you getting?
>
>Options are ODBC, JDBC, OpsNav, or CLI - only the latter is available
>relatively easily in RPG - Java might be doable - there are ways now
>to call java from rpg - but that is not practical, as there'd be lots
>of stuff that would be no less complex than using CLI - the latter is
>not hard - hardly more than embedded, although it is function calls.
>
>Also, here is a link to a post i made to the list - it has a couple
>good items that helped me when starting with CLI -
>http://archive.midrange.com/midrange-l/200603/msg00104.html
>
>Good luck!
>Vern
>
>At 09:26 PM 11/18/2006, you wrote:
>
> >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 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 highlyappreciated.

> > > >
> > > > ------------------------------------------------
> > > > 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.
> >
> >--
> >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.
> >
> >---------------------------------
> >Sponsored Link
> >
> >$200,000 mortgage for $660/mo - 30/15 yr fixed, reduce debt, home
> >equity - Click now for info
> >--
> >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.
>
>---------------------------------
>Sponsored Link
>
>Mortgage rates near 39yr lows. $420,000 Mortgage for $1,399/mo -
>Calculate new house payment
>--
>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.