|
midrange.com
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.
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.
