05 Multiple SQL commands with EXECUTE IMMEDIATES

**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;
          dsply   msg;
    when sqlstt = '02000'; // no records found
         msg =   'No records found for ' + updlib;
         dsply   msg;
    other; // other errors
         msg =   'Error ' + sqlstt + ' trying to update ' + updlib;
         dsply   msg;
    endsl;
endfor;

*inlr = *on;

** CTDATA   LibNames
LibA
LibB
Mylib     

Comments

Post a Comment

Popular posts from this blog

07 Using CURSOR and FETCH into host DS

**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;
Read more

12 FETCH and sub-procedures

**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...
Read more