RE: SQL Update in RPGLE.

[rob@xxxxxxxxx]

Gotta agree with Eric here, either use the RPG, or use the SQL.  There are 
three ways to redo that program.  Note:  I don't have time to look up the 
sql states.  I used the V5R4 free format execsql.

1 - Straight RPG.
=============================================================
     FCUST      UF   E             DISK    RENAME(CUSTR:CSREC)
     D RowsUpdated   S            5i 0

      /FREE
       DoW not  %eof(CUST);
         Read CUST;
         If not %eof(CUST) and cussta='TN';
           Exsr UpdateCSREC;
         EndIf;
       EndDo;
       *inlr=*on;
       dsply 'UPDATE SUCCESSFUL';

      /eject
       BegSR UpdateCSREC;
       cussta='TX';
       update(e) csrec %fields(cussta);
       if not %error;
         RowsUpdated+=1;
       EndSR;
      /END-FREE
=============================================================

2 - SQL, no cursor
=============================================================
      /FREE
       execsql update cust set cussta='TX'
               where cussta='TN';
       if sqlstt= ...;
         // technically, there is a field in the sql data structure
         // that you could append to your dsply message to tell
         // you exactly how many rows were updated.
         dsply 'UPDATE SUCCESSFUL';
       EndIf;
       *inlr=*on;
      /END-FREE
=============================================================

3 - SQL, with cursor
=============================================================
      /FREE
       execsql declare C1 cursor for 
               select cussta from cust
               where cussta='TN';
       execsql open C1;
       If sqlstt ...;
         // abort because we couldn't open cursor
       EndIf;
       dou sqlstt=...;
         execsql fetch C1 into :cussta;
         if sqlstt ...;  // not eof
           // Only reason to drag this simple program out to make it a 
           // cursor is maybe you want an audit report display what
           // was changed
           // if so, put that logic here
           execsql update cust where current of c1;
           // that's the best way to do a single row update, when 
           // using a cursor
           if sqlstt ... ;
             // now, if you want to put a dsply 'update successful'
             // for each row, here's the spot to do so.
             RowsUpdated+=1;
           EndIf;
         EndIf;
       EndDo;
       execsql close C1;
       *inlr=*on;
       msg=%char(RowsUpdated) + ' rows successfully updated';
       dsply msg;
      /END-FREE
=============================================================

Rob Berendt