Re: Looking for BASIC example of using embedded SQL for retrieving field value

[Stuart Rowe <rowestu@xxxxxxxxx>]

Typically we'd allow passing in a "where clause" to do selection -- for
example, what if the file had 7 keys?  Anyway, you'll have to use a cursor
in this case.  I will not be a fab performer so don't rely on this type of
access heavily.

d aStatement    s          8192a  varying
d p1Where       s          2048a  varying (a parm from somewhere)

/free

 aStatement = 'select ' + %trim( PARM5 ) + ' +
               from ' + %trim( PARM2 ) + '/' +
                        %trim( PARM1 ) + ' +
               where ' + p1Where;
 // or with your parm
               where ' + %trim( PARM3 ) +
               ' = ''' + %trim( PARM4 ) + ''';

 exec sql prepare $stmt from :aStatement;
 exec sql declare $curs cursor for $stmt;
 exec sql open $curs;

 // minimal error handler -- there are better ways.
 if sqlcode <> *zero
   // I usually send an escape message to the caller here using the message
id
   // and data in the sqlca. At any rate, do not continue.
 endif;

 exec sql fetch from curs into :wrkfld;

 // minimal error handler -- there are better ways.
 if sqlcode <> *zero;
   clear wrkFld;
 endif;

 exec sql close $curs;

 /end-free

Stu



On Thu, Nov 12, 2009 at 17:15, <rob@xxxxxxxxx> wrote:

> Just to repeat, you should check SQLSTT after every exec sql statement.
> See my sample earlier.
>
> Also, since you cannot do a 'select into' on a prepared statement you will
> have to use a cursor.
>
> There are some who say if you are using a cursor in SQL you are using it
> wrong.  I am NOT in that camp.
>
> Under normal circumstances I do think it's rather silly to use a cursor
> for a single row fetch.  But if you really have a need for a generic
> utility like you are trying to do then you'll have to go that route.
>
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Alan Campin <alan0307d@xxxxxxxxx>
> To:     "RPG programming on the IBM i / System i" <rpg400-l@xxxxxxxxxxxx>
> Date:   11/12/2009 06:09 PM
> Subject:        Re: Looking for BASIC example of using embedded SQL for
> retrieving      field value
> Sent by:        rpg400-l-bounces@xxxxxxxxxxxx
>
>
>
> Your sql is error out and your are not check the value of SQLSTATE. Cannot
> do a Select Into with a prepared statement.
>
> On Thu, Nov 12, 2009 at 3:55 PM, John Allen <jallen@xxxxxxxxxxx> wrote:
>
> > I got the basic program working and I now inderstand the basics!!
> >
> > Thanks to everyone that replied, the suggestions were are very helpful
> and
> > simple.
> >
> > I have even been able to add some more non-basic to it and keep it
> working.
> > (ie Build the select screen with variables and execute it)
> >
> > I do have one question:
> > I am hoping to make this a program that can retrieve a field value from
> any
> > file (or almost any file)
> > The incoming parms will be
> > 1) The physical file name
> > 2) The physical file library name
> > 3) The field name of the key field in the physical file
> > 4) The key field value
> > 5) The name of the field I want the value of returned
> >
> > C     *ENTRY        PLIST
> > C                   PARM                    PARM1
> > C                   PARM                    PARM2
> > C                   PARM                    PARM3
> > C                   PARM                    PARM4
> > C                   PARM                    PARM5
> > Don't yell at me for the programming technique and naming conventions
> > this is only a simple test program not for production
> >
> > So I want to pass:
> > Parm1 = VNDMAST
> > Parm2 = *LIBL
> > Parm3 = VENDORNBR
> > Parm4 = 00100
> > Parm5 = VENDORNM
> > If I build the select statement dynamically it does not seem to work.
> > Is what I am trying to do possible?
> > If yes, what do I have coded incorrectly?
> > (Select VENDORNM into a work field (WRKFLD)
> > from VNDMAST Where VENDORNBR is 00100)
> > C                   eval      W$SQLCmd = 'Select '
> > C                             + PARM5
> > C                             + ' into :'
> > C                             + 'WRKFLD'
> > C                             + ' from '
> > C                             + PARM1
> > C                             + ' Where '
> > C                             + PARM3
> > C                             + ' = :'
> > C                             + ''''
> > C                             + PARM4
> > C                             + ''''
> > C/exec SQL
> > C+  Set option Commit = *NONE
> > C/End-Exec
> >  *
> > C/exec SQL
> > C+ execute immediate :W$SQLCmd
> > C/End-Exec
> >
> > This program runs without an error but does not retrieve any value
> >
> > I was hoping it would put the vendor name into the work field WrkVndNm
> >
> >
> > Thanks
> >
> > John
> >
> >
> > -----Original Message-----
> > From: rpg400-l-bounces@xxxxxxxxxxxx [
> mailto:rpg400-l-bounces@xxxxxxxxxxxx]
> > On Behalf Of Stuart Rowe
> > Sent: Thursday, November 12, 2009 4:58 PM
> > To: RPG programming on the IBM i / System i
> > Subject: Re: Looking for BASIC example of using embedded SQL for
> retrieving
> > field value
> >
> > /free
> >
> >  exec sql select ifnull(ComanyName,'') into :workvariable from FILENAME
> > where CompanyNbr = 'keyvalue'
> >
> >  if sqlcode <> *zero;
> >   clear workvariable;
> >  endif;
> >
> > /end-free
> >
> > ought to do it just fine.  Nothing fancy.  No error handling per se.
> >  Nothing else needed really.  If you need to qualify the filename, use a
> > slash like LIB/FILE.
> >
> > If you want the "variable length-ness" to come in as well then declare
> > workvariable as varying.
> >
> >
> > Stu
> >
> >
> >
> > On Thu, Nov 12, 2009 at 10:59, <rob@xxxxxxxxx> wrote:
> >
> > > Should be on the rpg list.
> > >
> > >     H ActGrp(*CALLER)
> > >     H DftActGrp(*NO)
> > >      *ENTRY PLIST
> > >     D JohnAllen       PR                  extpgm('JOHNALLEN')
> > >     D  CompanyNbr                         like(cmpny)
> > >     D JohnAllen       PI
> > >     D  CompanyNbr                         like(cmpny)
> > >     D RCO           E DS                  extname(RCO)
> > >
> > >
> > >      /free
> > >       *inlr=*on;
> > >
> > >       // SQL's version of the H spec
> > >       EXEC SQL
> > >        Set Option
> > >            Naming    = *Sys,
> > >            Commit    = *None,
> > >            UsrPrf    = *User,
> > >            DynUsrPrf = *User,
> > >            Datfmt    = *iso,
> > >            CloSqlCsr = *EndMod;
> > >       exec sql
> > >        select cmpnam into :cmpnam
> > >        from rco
> > >        where cmpny = :CompanyNbr;
> > >       // Did it work?
> > >       Select;
> > >         When SqlStt='00000';
> > >           dsply cmpnam;
> > >         Other;
> > >           dsply SqlStt;
> > >       EndSl;
> > >       return;
> > >      /end-free
> > >
> > >
> > > Rob Berendt
> > > --
> > > Group Dekko Services, LLC
> > > Dept 01.073
> > > Dock 108
> > > 6928N 400E
> > > Kendallville, IN 46755
> > > http://www.dekko.com
> > >
> > >
> > >
> > >
> > >
> > > From:   "John Allen" <jallen@xxxxxxxxxxx>
> > > To:     <MIDRANGE-L@xxxxxxxxxxxx>
> > > Date:   11/12/2009 11:38 AM
> > > Subject:        Looking for BASIC example of using embedded SQL for
> > > retrieving field        value
> > > Sent by:        midrange-l-bounces@xxxxxxxxxxxx
> > >
> > >
> > >
> > > I have finally got the opportunity to learn embedded SQL in RPG ILE.
> > >
> > > I have been searching on the web for the most basic example I can find
> > for
> > > my application
> > > but everything I find has more then I need (I think) and it is
> confusing.
> > > I find I can learn much quicker by seeing an example.
> > >
> > > Does anyone have or can anyone point me to an example that does the
> > > following (No more, no extra stuff that will confuse me)
> > >
> > >
> > >
> > > I have a physical file with 10 fields (COMPANY)
> > >
> > > The unique key field (Company Number) is two character field (Alpha),
> > > The 5th field in the company file is a variable length field (Company
> > > Name).
> > >
> > > The only parameter I have coming in is the key value.
> > >
> > >
> > >
> > > I would like to use SQL to retrieve the record for the key value
> (Company
> > > Nbr) and get the value in the 5th field (Company Name) and place it in
> a
> > > work field.
> > >
> > >
> > >
> > > No updating, No deleting, just take the key value retrieve the company
> > > name
> > > and place it in a work field
> > >
> > >
> > >
> > > I know this is very easy to do with a simple chain, but I believe we
> > could
> > > do a lot more with embedded SQL in other programs
> > > so I want to learn how to do this with SQL. Then hopefully all the
> other
> > > examples on the web will make sense to me.
> > >
> > >
> > >
> > >
> > >
> > > Thanks in advance
> > >
> > > John
> > >
> > >
> > >
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > > list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> > >
> > >
> > > --
> > > This is the RPG programming on the IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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 IBM i / System i (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.