|
Thanks, Rob. That solved my problem. I used option 2 just by experience of using SQL for so long. Couple of reasons why I would be against using 'Select *...' are... - The design of the table and the order of the fields might change and the code might behave differently because of that. -I have always grabbed only the fields which I need and we never need all the fields in most of the cases anyways so there is no reason why I should be hoggin' the bandwidth. The 'Select *..' is actually translated into 'Select itmnbr, itmdescr,...' in my case and this is a overhead. Thanks again for all your help, it has been a good learning experience. I have to make peace with the usage of indicators. I have never used indicators before and they have been throwing me off a bit. cheers, Jake. On 9/22/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote:
Bookmark this: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzala/rzalafinder.htm Too many host variables have been specified on SELECT INTO or FETCH. Attack of the ds strikes again! Change this select ITMNBR, ITMDESCR into :myDS You have two choices to change it to. Option 1 select * into :myDS Option 2 select ITMNBR, ITMDESCR into :itmnbr, :itmdescr Now, you explain to me which one you select, and why. Actually, in very early versions of i5/os. No, before that, os/400, you could get away with what you are doing. IBM locked that down. But most people "used" to do it as option 1. And that generally worked. UNLESS you added more fields to the file and did not recompile your program. Then it still worked, until IBM "enhanced" os/400 and came up with the error you are getting. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com "Jake M" <jakeroc@xxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 09/22/2006 03:13 PM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> cc Subject Re: Embedded SQL with DSPF problem. Okay, I think I am really close but I am getting a SQL state of 01557. Any thoughts on that? ********************************************************************************************************** // Declare Files FITEMINQSQLCF E WORKSTN IndDS(WkstnInd) Fqsysprt o f 132 printer Dpsds sds D pgmname 1 10a D HeadingDone S N inz(*off) D WkstnInd DS D NotFound 40 40N D Exit 03 03N D Cancel 12 12N D myDS E ds extname(item_pf) D item S 5 0 C/EXEC SQL C+ Set Option C+ Naming = *Sys, C+ Commit = *None, C+ UsrPrf = *User, C+ DynUsrPrf = *User, C+ Datfmt = *iso, C+ CloSqlCsr = *EndMod, C+ Commit = *NONE C/END-EXEC /FREE PgmNam = pgmname; except heading; if not HeadingDone; except heading; HeadingDone=*on; EndIf; Exfmt Prompt; Dow NOT Exit; dsply 'item number'; dsply itmnbr; item = itmnbr; dsply 'item :'; dsply item; /END-FREE c/exec sql c+ select ITMNBR, ITMDESCR into :myDS from c+ testing/item_pf where itmnbr=:item c/end-exec /FREE dsply 'sql state follows'; dsply SQLSTT; If SQLSTT = '00000'; except detail; Else; NotFound=*on; endif; //Enddo; // No Item record found or F12 pressed - display prompt Exfmt Prompt; Enddo; *InLR = *ON; /END-FREE Oqsysprt e heading 1 O + 1 'ITEM NUMBER' O + 20 'ITEM DESCRIPTION' Oqsysprt e detail 3 O ITMNBR + 1 O ITMDESCR + 20 ***************************************************************************************************** I am sure that the item number is coming into the program. I have some dsply statements which show the item number entered on the dspf. Any help would be much appreciated. Thanks again for all the help. cheers, Jake. On 9/22/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote: > > Buck answered #1. > > Yes, you'll have to define HeadingDone. > D HeadingDone S N inz(*off) > > Rob Berendt > -- > Group Dekko Services, LLC > Dept 01.073 > PO Box 2000 > Dock 108 > 6928N 400E > Kendallville, IN 46755 > http://www.dekko.com > > > > > > "Jake M" <jakeroc@xxxxxxxxx> > Sent by: rpg400-l-bounces@xxxxxxxxxxxx > 09/22/2006 12:12 PM > Please respond to > RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> > > > To > "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> > cc > > Subject > Re: Embedded SQL with DSPF problem. > > > > > > > first of all, thanks. > > about to implement your suggestions...two quick questions. > 1.How will the SQL statement know the item number to be queried coming > from > the display file? > > c/exec sql > c+ select ITMNBR, ITMDESCR into :myDS from > c+ testing/item_pf where itmnbr=:itemnum > c/end-exec > > if I remove the PR and PI, how will the SQL statement know 'itemnum'.? > > 2.Headingdone is a named indicator? > > Thanks for all your guidance, > > Jake. > > On 9/22/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote: > > > > 1 - If you are not passing in itemnum as a parameter, then yes, take it > > out of the prototypes. In fact, with no parameters you can probably > drop > > the prototypes. > > 2 - On your confusion, you dropped the cursor and used a single select > > statement. Good. However you left some old cursor logic in there. For > > example, this is an infinite loop: > > dow SQLSTT = '00000'; > > except detail; > > enddo; > > Change it to something like > > If SQLSTT = '00000'; > > except detail; > > Else; > > NotFound=*on; > > enddo; > > > > 3 - What are you doing with PgmNam? If you are planning on adding this > to > > your report heading that is a good idea. However, never hardcode this. > > Check the RPGLE manuals for "program status data structure" and get the > > program name from that. The problem is that if you copy this code > you'll > > still have the program name of the original program in there. Or at > least > > have the potential for that. There's lots of other cool stuff in the > PSDS > > you can put on the report headings. Like who ran the report. > > 4 - What will happen to your page headings when you reach end of page? > > Search also for "file information data structure". Look for two fields > in > > that data structure. Overflow line and current line. Except another > > heading when one approaches the other. There's something else called > the > > "fetch overflow" concept but coming from your background you'd probably > > appreciate the INFDS better. > > > > "Back in the day" we had our heading line start at line 3 and not 1. > Made > > it easier for operators to line up green bar paper in printers. However > > we've not purchased stock continuous bar paper in over a decade. Don't > > know if that is a concern where you are at. > > > > I've interspersed a lot of comments in your code. > > > > By the way, good work using named indicators on the screen. > > > > Look more at this: > > // run the set option once and forget about it. Take out of loop > > // it would only run once anyway, but why confuse the eyeball > > // reading of the loop logic? > > C/EXEC SQL > > C+ Set Option > > C+ Naming = *Sys, > > C+ Commit = *None, > > C+ UsrPrf = *User, > > C+ DynUsrPrf = *User, > > C+ Datfmt = *iso, > > C+ CloSqlCsr = *EndMod, > > C+ Commit = *NONE > > C/END-EXEC > > > > /FREE > > PgmNam = 'ITEMINQSQL'; // get this from the psds instead > > except heading; // I moved initial headings outside of the loop. > > // Unless if they input NO item's you don't want > > any report. > > // Then we often use a flag like this logic > right > > before your > > // except detail > > if not HeadingDone; > > except heading; > > HeadingDone=*on; > > EndIf; > > // or better yet > > if not HeadingDone or > > infds.CurrentLine>=infds.OverflowLine > > except heading; > > HeadingDone=*on; > > EndIf; > > Exfmt Prompt; > > Dow NOT Exit or Cancel; // Don't they really mean the same > thing? > > /END-FREE > > > > > > c/exec sql > > c+ select ITMNBR, ITMDESCR into :myDS from > > c+ testing/item_pf where itmnbr=:itemnum > > c/end-exec > > > > /FREE > > Dow NOT Cancel; // Display details // delete this line > > // blast the "Dow NOT Cancel" loop. You already have an exfmt > > loop. Use it. > > If SQLSTT = '00000'; > > except detail; > > Else; > > NotFound=*on; > > enddo; > > > > If Exit; // this If is redundant > > // you already have it on the initial dow loop. > > *InLR = *ON; // trash line - redundant > > Return; // trash line. Your loop control will > handle. > > Endif; > > Enddo; // delete this line > > > > // No Item record found or F12 pressed - display prompt // > trash > > line > > Cancel = *Off; // trash line > > NotFound = *on; // trash line > > Exfmt Prompt; > > Enddo; // F3 Exit or F12 Cancel > > *InLR = *ON; > > /END-FREE > > > > > > Rob Berendt > > -- > > Group Dekko Services, LLC > > Dept 01.073 > > PO Box 2000 > > Dock 108 > > 6928N 400E > > Kendallville, IN 46755 > > http://www.dekko.com > > > > > > > > > > > > "Jake M" <jakeroc@xxxxxxxxx> > > Sent by: rpg400-l-bounces@xxxxxxxxxxxx > > 09/22/2006 10:20 AM > > Please respond to > > RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> > > > > > > To > > "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> > > cc > > > > Subject > > Re: Embedded SQL with DSPF problem. > > > > > > > > > > > > > > All; > > Thanks for taking out time to help me correcting my code. I am really > > thankful for that. I have made the corrections that you guys have > pointed > > out. > > > > Rob: > > I followed your instructions closely like always and I was confused > about > > one very good point that you made. > > > > >>>Notice that you are selecting on an item number before you > > >>>are prompted for it? That will work on the parameter that you passed > > into > > >>>the program, but not on any values prompted. > > > > I am using the exfmt PROMPT; > > before the SQL statement. > > > > is that wrong? > > > > My code might still have a lot of mistakes in it but I will struggle and > > learn...:-0) > > > > > > ******************************************************************************************************* > > // Declare Files > > FITEMINQSQLCF E WORKSTN IndDS(WkstnInd) > > Fqsysprt o f 132 printer > > > > > > > > D itemchoice PR EXTPGM('ITEMINQSQL') > > D itemnum 5 0 > > > > D itemchoice PI > > D itemnum 5 0 > > > > is this even necessary if I am just looking for itemnum to come > from > > the user by prompting? > > > > > > > > > > D WkstnInd DS > > D NotFound 40 40N > > D Exit 03 03N > > D Cancel 12 12N > > > > > > D myDS E ds extname(item_pf) > > > > /FREE > > PgmNam = 'ITEMINQSQL'; > > Exfmt Prompt; > > Dow NOT Exit; > > /END-FREE > > > > C/EXEC SQL > > C+ Set Option > > C+ Naming = *Sys, > > C+ Commit = *None, > > C+ UsrPrf = *User, > > C+ DynUsrPrf = *User, > > C+ Datfmt = *iso, > > C+ CloSqlCsr = *EndMod, > > C+ Commit = *NONE > > C/END-EXEC > > > > > > c/exec sql > > c+ select ITMNBR, ITMDESCR into :myDS from > > c+ testing/item_pf where itmnbr=:itemnum > > c/end-exec > > > > /FREE > > Dow NOT Cancel; // Display details > > except heading; > > dow SQLSTT = '00000'; > > except detail; > > enddo; > > If Exit; > > *InLR = *ON; > > Return; > > Endif; > > Enddo; > > > > // No Item record found or F12 pressed - display prompt > > Cancel = *Off; > > NotFound = *on; > > Exfmt Prompt; > > Enddo; > > *InLR = *ON; > > /END-FREE > > > > > > > > Oqsysprt e heading 1 > > O + 1 'ITEM NUMBER' > > O + 20 'ITEM DESCRIPTION' > > > > > > Oqsysprt e detail 3 > > O ITMNBR + 1 > > O ITMDESCR + 20 > > > > > > *********************************************************************************************************** > > > > Thanks again for everybody's help, > > > > cheers, > > > > Jake. > > > > On 9/22/06, rob@xxxxxxxxx <rob@xxxxxxxxx> wrote: > > > > > > A number of things: > > > 1 - You do not need the external file name if you are accessing it via > > sql > > > 2 - I'd use an external data structure for field definitions. Like > > > D myds eds extname(item_pf) > > > 3 - How many items with the same item number do you expect to find in > > the > > > item file? Doesn't the file have a primary key constraint? If you > are > > > only expecting one, then get rid of the cursor and use a direct select > > > into. If you insist on using the cursor, then you will need to close > it > > > and open it within your loop. Notice that the open on the cursor has > a > > > WHERE clause? Notice that you are selecting on an item number before > > you > > > are prompted for it? That will work on the parameter that you passed > > into > > > the program, but not on any values prompted. > > > 4 - skip %found, use sqlstt for the select into, or the fetch. > > > > > > If you need to test for multiple item numbers you may want to try > > > select count(*), itemnbr, max(itemdescr) from item_pf > > > group by itemnbr > > > having count(*)>1 > > > > > > Rob Berendt > > > -- > > > Group Dekko Services, LLC > > > Dept 01.073 > > > PO Box 2000 > > > Dock 108 > > > 6928N 400E > > > Kendallville, IN 46755 > > > http://www.dekko.com > > > > > > > > > > > > > > > > > > "Jake M" <jakeroc@xxxxxxxxx> > > > Sent by: rpg400-l-bounces@xxxxxxxxxxxx > > > 09/21/2006 04:38 PM > > > Please respond to > > > RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> > > > > > > > > > To > > > "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> > > > cc > > > > > > Subject > > > Embedded SQL with DSPF problem. > > > > > > > > > > > > > > > > > > > > > Hello All; > > > Once again I am running into some newbie problems. This program is > > > supposed > > > to kick out a simple report based on the item number given by the user > > in > > > the display file. The SQLRPGLE program compiles but it bombs out when > I > > > try > > > and run it. I have a display file and a SQLRPGLE program which calls > the > > > display file. I think I am doing something wrong in the coding part > > > itself. > > > Should I be using an extra CL program with this? I do not know if this > > > kind > > > of report generation could be done with only one piece of sqlrpgle > code > > > and > > > a dspf file. If anybody could point me in the right direction, I would > > > really appreciate it. > > > > > > ITEMINQSQL.dspf > > > > > > > > > > > > ************************************************************************************************************************** > > > A*%%TS DD 20060921 151811 jakem REL-V5.0.1 WDSc > > > A*%%FD Example: Item Inquiry > > > A*%%EC > > > A DSPSIZ(24 80 *DS3) > > > A REF(*LIBL/ITEM_PF) > > > A CA03(03) > > > A INDARA > > > A R PROMPT > > > A*%%TS DD 20060921 151811 jakem REL-V5.0.1 WDSc > > > A PGMNAM 10A O 3 7 > > > A 3 35'Item Inquiry' > > > A COLOR(WHT) > > > A 3 64DATE > > > A EDTCDE(Y) > > > A 8 20'Item Number . . . . . .:' > > > A ITMNBR R D I 8 45 > > > A 40 ERRMSG('Item not found on > > file > > > - > > > pl- > > > A ease correct' 40) > > > A 20 7'Press Enter to continue' > > > A 21 7'F3=Exit' > > > A COLOR(BLU) > > > A*%%GP SCREEN1 > > > 01 > > > > > > > > > > > > ****************************************************************************************************************************** > > > > > > ITEMINQSQL.sqlrpgle > > > > > > > > > > > > ****************************************************************************************************************************** > > > // Declare Files > > > FITEM_PF IF E K DISK Rename(ITEM_PF:RITEMPF) > > > FITEMINQSQLCF E WORKSTN IndDS(WkstnInd) > > > Fqsysprt o f 132 printer > > > > > > > > > > > > D itemchoice PR EXTPGM('ITEMINQSQL') > > > D itemnum 20 > > > > > > D itemchoice PI > > > D itemnum 20 > > > > > > > > > > > > > > > D WkstnInd DS > > > D NotFound 40 40N > > > D Exit 03 03N > > > D Cancel 12 12N > > > > > > > > > D myDS ds LIKEREC(RITEMPF) > > > > > > > > > /FREE > > > PgmNam = 'ITEMINQSQL'; > > > Exfmt Prompt; > > > Dow NOT Exit; > > > /END-FREE > > > > > > C/EXEC SQL > > > C+ Set Option > > > C+ Naming = *Sys, > > > C+ Commit = *None, > > > C+ UsrPrf = *User, > > > C+ DynUsrPrf = *User, > > > C+ Datfmt = *iso, > > > C+ CloSqlCsr = *EndMod, > > > C+ Commit = *NONE > > > C/END-EXEC > > > > > > > > > c/exec sql > > > c+ declare mainCursor Cursor > > > c+ for > > > c+ select * from testing/item_pf where itmnbr=:itemnum > > > c/end-exec > > > c/exec sql > > > c+ open mainCursor > > > c/end-exec > > > c/exec sql > > > c+ fetch next > > > c+ from mainCursor > > > c+ into :myDS > > > c/end-exec > > > > > > /FREE > > > If %found(Item_PF); // Item Number valid? > > > NotFound = *off; > > > Dow NOT Cancel; // Display details > > > except heading; > > > dow SQLSTT = '00000'; > > > except detail; > > > /END-FREE > > > c/exec sql > > > c+ fetch next > > > c+ from mainCursor > > > c+ into :myDS > > > c/end-exec > > > /FREE > > > enddo; > > > If Exit; > > > *InLR = *ON; > > > Return; > > > Endif; > > > Enddo; > > > > > > Endif; > > > // No Item record found or F12 pressed - display prompt > > > Cancel = *Off; > > > NotFound = *on; > > > Exfmt Prompt; > > > Enddo; > > > *InLR = *ON; > > > /END-FREE > > > > > > > > > > > > Oqsysprt e heading 1 > > > O + 1 'ITEM NUMBER' > > > O + 20 'ITEM DESCRIPTION' > > > > > > > > > Oqsysprt e detail 3 > > > O ITMNBR + 1 > > > O ITMDESCR + 20 > > > > > > > > > > > > **************************************************************************************************************************************** > > > > > > Thanks in advance, > > > > > > cheers, > > > > > > Jake. > > > -- > > > 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. > > > > > > > > -- > > 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. > > > > > -- > 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. > > -- 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
