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

[Stuart Rowe <rowestu@xxxxxxxxx>]

/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.