RpgSQLCode

**FREE ctl-opt ActGrp(*CALLER) DftActGrp(*NO); //********** Prototypes for Subprocedures ********** dcl-pr OpenCursor  ind end-pr; dcl-pr FetchCursor ind end-pr; dcl-pr CloseCursor ind end-pr; //********** Stand Alones ********** dcl-s MyLib char(10); dcl-s MyFile char(10); //********** Main Loop ********** *inlr=*on; if not OpenCursor();          // perform error routine to alert the troops Else;    Dow FetchCursor();        // putting the fetchcursor on the do loop allows the user of            // iter, and thus iter will not perform an infinite loop            // normal processing here...    EndDo;    CloseCursor(); EndIf; return; //********** OpenCursor ********** dcl-proc OpenCursor; dcl-pi   OpenCursor like(ReturnVar) end-pi; dcl-s ReturnVar ind; // The immediately following /EXEC SQL is SQL's version...

**FREE ctl-opt option(*nodebugio:*srcstmt); dcl-ds FILEAx extname('FILEA') qualified end-ds; dcl-ds FILEBx   extname('FILEB  ') qualified end-ds; dcl-ds FILEAds extname('FILEA') end-ds; dcl-ds FILEBds   extname('FILEB'  );        FIELDA_     extfld('FIELDA');        FIELDB_    extfld('FIELDB'); end-ds; dcl-c SqlEof const('02000'); exec sql declare c1 cursor for          select a.*,b.*          from pgctrans/FILEA a          left outer join pgctrans/FILEB b          on a.FIELDA=b.FIELDA and a.FIELDB=b.FIELDB          where b.FIELDX>' '          order by a.FIELDA, b.FIELDX; exec sql open c1; exec sql fetch c1 into :FILEAx, :FILEBx; if sqlStt <> sqlEof;    FILEAds = FILEAx;    FILEBds = FILEBx...

**FREE dcl-s MaxSize zoned(15:0); dcl-s AvgSize zoned(15:0); exec sql select avg(odobsz), max(odobsz) into :avgsize, :maxsize from pfdata; if sqlstt <> '00000'; // Error     // handle the error endif; *inlr=*on;

**FREE dcl-s SzParm zoned(5:0); dcl-s SQLStmt char(100) inz('Select * from FILEA where FIELDA=? and FIELDB>?'); dcl-ds InRec extname(FILEA) end-ds; dcl-pi *n;        parm1 char(5);        parm2 char(5); end-pi;       szparm = %int(parm2) exec sql Prepare s1 from :SQLStmt; exec sql DECLARE C1 CURSOR FOR S1; exec sql open c1 using :parm1, :szparm; exec sql fetch c1 into :inrec;       dou sqlstt <> '00000'    // Do something with each record    exec sql fetch c1 into :inrec; enddo;       exec sql close c1; *inlr=*on;

**FREE dcl-ds InRec extname(FILEA) end-ds; exec sql declare c1 cursor for select * from FILEA; exec sql open c1; exec sql fetch c1 into :inrec; dou sqlstt <> '00000';    //  Do something with each record    exec sql fetch c1 into :inrec; enddo; exec sql close c1; *inlr=*on;

**FREE Dcl-ds InRec extname(FILEA) end-ds; dcl-s FIELDA2 like(FIELDA); dcl-s FIELDB2 like(FIELDB); exec sql declare c1 cursor for select FIELDA, FIELDB from FILEA; exec sql open c1; exec sql fetch c1 into :FIELDA2, :FIELDB2; dou sqlstt <> '00000';     //  Do something with each record     exec sql fetch c1 into :FIELDA2, :FIELDB2; enddo; exec sql close c1; *inlr = *on;   

**FREE dcl-s UpdLib char(10); dcl-s x zoned(3:0); dcl-s msg char(50); dcl-s SQLStmt char(200); // ------------------------------------------------------------- // File PFDATA was created by doing a DSPOBJD to an outfile // ------------------------------------------------------------- dcl-s SQLMain char(100)  inz('Update pfdata set odssze = '); dcl-s SQLWhere char(50)   inz(' where odlbnm = '); dcl-s LibNames char(10) dim(3)  perrcd(1) ctdata; dcl-pi *n;        parm1 char(5); end-pi; for x = 1 to 3;     updLib = libnames(x);     // Build the complete SQL statement     SQLStmt = %trim(SQLMain) + parm1 + %trim(SQLWhere) + '''' + %trim(updlib) + '''';     exec sql execute immediate :SQLStmt;     select;     when  sqlstt = '00000'; // completed normally           msg =   %char(sqler3) + ' records found for ' + updlib;   ...

**FREE dcl-s UpdLib char(10); dcl-s x zoned(3:0); dcl-s msg char(50); dcl-s SQLStmt char(100) inz('Update FILEA set FIELDA = '); dcl-s SQLWhere char(50) inz(' where FIELDA = ?'); dcl-s LibNames char(10) dim(3) perrcd(1) ctdata; dcl-pi *n;        parm1 char(5); end-pi; SQLStmt = %trim(SQLStmt) + ' ' + parm1 + ' ' + %trim(SQLWhere); exec sql prepare SQLString from :SQLStmt; for x = 1 to 3;     updLib = libnames(x);     exec sql    execute SQLString using  :updlib;     select;     when sqlstt = '00000'; // completed normally          msg =   %char(sqler3) + ' records found for ' + updlib;          dsply   msg;     when sqlstt = '02000'; // no records found          msg =   'No records found for ' + updlib;          dsply   msg;   ...

**FREE dcl-s SQLStmt char(100) inz('Update FileA set FieldA = '); dcl-pi *n;        parm1 char(10); end-pi; SQLStmt = %trim(SQLStmt) + parm1; exec sql execute immediate :SQLStmt; if sqlstt <> '00000';    // handle the error condition else;    // show how many records were updated endif; *inlr = *on;            

**FREE dcl-pi *n;        parm1 char(6);        parm2 char(10); end-pi; exec sql update FileA set FieldA = :parm1 where FieldB = :parm2; if sqlstt <> '00000';    // handle the error condition else;    // Show how many records were updated endif; *inlr = *on;      

**FREE exec sql update FileA set FieldA = FieldB +1; if sqlstt <> '00000';    // handle the error condition else;    // show how many records were updated endif; *inlr = *on;