Re: Embedded SQL with DSPF problem. -- RPG400-L

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.