|
Did you look in your job log? SQL error messages are usually pretty good at telling you where to look. You may need to start debugging before you run your program to get the full descriptions. You don't need to put your program into debug to get this info. Typing STRDBG UPDPROD(*YES) before running it is all you need to do. Matt -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jake M Sent: Friday, September 22, 2006 3:14 PM To: RPG programming on the AS400 / iSeries 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 probablydropthe 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
toyour 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 codeyou'llstill have the program name of the original program in there. Or atleasthave the potential for that. There's lots of other cool stuff in
the
PSDSyou 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
inthat 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.Madeit 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 logicrightbefore 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 samething?/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 willhandle.Endif; Enddo; // delete this line // No Item record found or F12 pressed - display prompt //trashline 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 havepointedout. Rob: I followed your instructions closely like always and I was confusedaboutone 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
intothe 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 comefromthe 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
sql2 - 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
theitem file? Doesn't the file have a primary key constraint? If
you
areonly 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
itand open it within your loop. Notice that the open on the cursor
has
aWHERE clause? Notice that you are selecting on an item number
before
youare prompted for it? That will work on the parameter that you
passed
intothe 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
inthe display file. The SQLRPGLE program compiles but it bombs out
when
Itry and run it. I have a display file and a SQLRPGLE program which
calls
thedisplay 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 sqlrpglecodeand 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
listTo 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
listTo 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
listTo 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
listTo 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
