RE: SQL Embedded in RPG ILE. -- RPG400-L

IN addition, I have an issue w/ this work file. How to end up doing it is
one part. How to allow more than one user at this is another problem.

> Mike:
> Welcome to the RPG world.  Just to illustrate why we rpg people love
> rpg: Below is how simple that is to do the same thing in RPG (Compare to
> using sql or java, vb...).
>
> FCUSTOMER  IF   E           K disk
>
> D SampleCust                          like(cnum) inz(12345)
>
> C     SampleCust    chain(n)  CUSTOMER
> C                   if        %found
>  *                  .... normal processing here...
> C                   endif
>
> C                   eval      *inlr = *on
>
> ....
> The power of using embedded SQL in rpg start to come in when you want to
> do fetch all records where last name sounded like 'Berendt', as Rob
> indicated below.
>
> -----Original Message-----
> From: rpg400-l-bounces+lim.hock-chai=usamobility.com@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces+lim.hock-chai=usamobility.com@xxxxxxxxxxxx] On
> Behalf Of rob@xxxxxxxxx
> Sent: Tuesday, March 14, 2006 11:01 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: SQL Embedded in RPG ILE.
>
> That's a single record fetch, right?  There's only 1 customer in your
> CUSTOMER file with that customer number?
>
> Then the following would work.  If, however, you wanted to fetch all
> records where last name sounded like 'Berendt' then let me know.  That
> involves using a cursor and I can whip up a sample like that also.
>
>      H ActGrp(*CALLER)
>      H DftActGrp(*NO)
>
>      D Customer      e ds                  EXTNAME(CUSTOMER)
>      D OpenStuff       PR              n
>      D FetcuStuff      PR              n
>      D SampleCust                          like(cnum) inz(12345)
>
>       /free
>        *inlr=*on;
>        if not OpenStuff();
>          // perform error routine to alert the troops
>          // ...
>        Else;
>          If FetchStuff(SampleCust);
>
>            // normal processing here...
>
>          EndIf;
>        EndIf;
>        return;
>       /end-free
>       // End of program is right here.
>
>
>      P OpenStuff       B
>      D OpenStuff       PI                  like(ReturnVar)
>      D ReturnVar       s               n
>
>      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/END-EXEC
>
>
>       /free
>        stmt1='select cnum, lname, fname ' +
>              'into :cnum, :lname, :fname ' +
>              'from customer where cnum=?';
>       /end-free
>      C/EXEC SQL
>      C+ Prepare S1 from :stmt1
>      C/END-EXEC
>
>       /free
>        Select;
>          When SqlStt='00000';
>            return *on;
>          Other;
>            return *off;
>        EndSl;
>       /end-free
>      P OpenStuff       E
>
>       /eject
>      D FetchStuff      PI                  like(ReturnVar)
>      D  ThisCust                           like(cnum)
>      D ReturnVar       s               n
>
>      C/EXEC SQL
>      C+ Execute S1 using :ThisCust
>      C/END-EXEC
>
>      P FetchStuff      E
>
>
> Rob Berendt
> --
> Group Dekko Services, LLC
> Dept 01.073
> PO Box 2000
> Dock 108
> 6928N 400E
> Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> "Mike Troxclaire" <mike_troxclaire@xxxxxxxxxxx>
> Sent by: rpg400-l-bounces@xxxxxxxxxxxx
> 03/14/2006 11:30 AM
> Please respond to
> RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
>
>
> To
> rpg400-l@xxxxxxxxxxxx
> cc
>
> Subject
> SQL Embedded in RPG ILE.
>
>
>
>
>
>
> Hello Gurus,
> I am a novice RPG programmer. I think I have got the basics down and
> starting to appreciate the language. I want to explore the power of SQL
> in
>
> RPG. I am looking for a very basic program to follow. For example if I
> have
> a file called CUSTOMER in a library MYLIB.
>
>
> My SQL statement would be :
>
> select * from MYLIB.CUSTOMER where CNUM = '12345'
>
> I want to create a report using a RPG program which utilizes the above
> SQL
>
> statement. The output file could be QPRINT.
>
> The CUSTOMER table has only 3 fields. CNUM, FNAME, LNAME.
>
> Any help in this matter would be much appreciated.
>
> Cheers,
>
> Mike.
>
> _________________________________________________________________
> Don't just search. Find. Check out the new MSN Search!
> http://search.msn.click-url.com/go/onm00200636ave/direct/01/
>
> --
> 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.
>
>