|
Hi Rob, sorry, but SELECT ... INTO ... is not supported by dynamic SQL! The following static SQL-Statement will do the job. C/EXEC SQL Select cnum, lname, fname C+ into :HostNum, :HostLName, :HostFName C+ from MyTable C+ where cnum = :HostVar C+ Fetch first row only C/END-EXEC And why dynamic when static is possible? Just a few comments: 1. For static SQL access plans are stored in the program object and can be used and validated each time the SQL-Statement gets executed. Even different access plans for a single statement can be stored and be validated. For dynamic SQL access plans are built, but not stored and must be rebuilt each time the prepare statement gets executed. 2. That's why PRTSQLINF can only be used for static SQL-Statements. 3. The run-time overhead is greater for statements processed using dynamic SQL than for static SQL statements. The additional process is similar to that required for syntax checking, precompiling, binding, and then running a program, instead of only running it. Therefore, only applications requiring the flexibility of dynamic SQL should use it. In most cases dynamic SQL can be replaced with static SQL. It's only necessary when tables or schemas must be used as variables or different select fields are needed. Mit freundlichen Gru?en / Best regards Birgitta "Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) -----Ursprungliche Nachricht----- Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von rob@xxxxxxxxx Gesendet: Mittwoch, 15. Marz 2006 18:02 An: RPG programming on the AS400 / iSeries Betreff: Re: Simple SQL in RPG question. 1 - You do not need to use a cursor on a single row fetch! You can do a direct 'select into' instead of a declare, open, fetch, close. 2 - I already gave you an example of using "parameter markers" in that code sample I gave you. Remember the stmt1='select cnum, lname, fname ' + 'into :cnum, :lname, :fname ' + 'from customer where cnum=?'; /end-free C/EXEC SQL C+ Prepare S1 from :stmt1 C/END-EXEC ... C/EXEC SQL C+ Execute S1 using :ThisCust C/END-EXEC See the 'where cnum=?'? ThisCust would be what was passed into the program, or prompted from the screen. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Mike Troxclaire" <mike_troxclaire@xxxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 03/15/2006 11:30 AM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To rpg400-l@xxxxxxxxxxxx cc Subject Simple SQL in RPG question. Hello All; With all your help and input, I am able to run a SQL embedded RPG program and produce a report. Now, I want the user to be able to go to the green screen and say CALL MYPROG('CSTNUM') and the results should be displayed on the screen(not create a report). Right now, I have hardcoded the customer number. The SQL statement is very complicated one so for simplicity I am just using a small one over here. I will further improve on it using a DSPF but for now I just want to call my program and pass the customer number as a parameter. Here is what I coded till now. Please overlook the coding inadequacies of a novice programmer. Any help in this matter will be much appreciated. **************************************************************************** ** FQSYSPRT O F 132 PRINTER D CSTNUM S 10 D CSNAME S 30 D CSADD1 S 30 D COUNT S 7 0 C EXSR PROCESS C EXSR ENDPGM C PROCESS BEGSR C EXSR WORK01 C ENDSR C WORK01 BEGSR C/EXEC SQL C+ DECLARE MYCUR CURSOR FOR SELECT CSTNUM, CSNAME, C+ CSADD1 FROM LIBRARY1/CSTMST WHERE CSTNUM = ' XXXXXXX' C/END-EXEC C/EXEC SQL C+ OPEN MYCUR C/END-EXEC C EXCEPT HEAD1 C EXSR @@FETCH C DOW SQLCOD = 0 C EXCEPT DET1 C EVAL COUNT = COUNT + 1 C EXSR @@FETCH C ENDDO C/EXEC SQL C+ CLOSE MYCUR C/END-EXEC C EXCEPT DET2 C ENDSR C @@FETCH BEGSR C/EXEC SQL C+ FETCH NEXT FROM MYCUR INTO :CSTNUM, :CSNAME, :CSADD1 C/END-EXEC C ENDSR C ENDPGM BEGSR C EVAL *INLR = *ON C ENDSR OQSYSPRT E HEAD1 1 O +0 'CSTNUM' O +1 'CSNAME' O +1 'CSADD1' O E DET1 1 O CSTNUM +0 O CSNAME +2 O CSADD1 +2 O E DET2 1 O COUNT 4 +0 **************************************************************************** ** Cheers, Mike. _________________________________________________________________ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- 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.
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
