RE: SQL Embedded in RPG ILE.

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/