|
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 <vhamberg@xxxxxxxxxxx> 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 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.-- 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
