Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Using "where current of" in an SQL Update
has no ratings.
Published: 06 Nov 2013
Revised: 07 Nov 2013 - 1323 days ago
Last viewed on: 22 Jun 2017 (2760 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Using "where current of" in an SQL Update Published by: Chris Proctor on 06 Nov 2013 view comments(3)

Good morning all. I'm having an issue and I can't figure out what the problem might be. I have a program that is passed a file and field name and I a dynamically creating the SELECT and the UPDATE statements, but I believe there might be an issue. I just want to verify with you that I have these set up correctly.

First of all, I build my SELECT statement based on the parms passed to the program:

// format SQL statements for file to be updated  
sltstmt = 'select ' + %trim(pfldnm) + ' from ' + %trim(plibnm) + '/' +
%trim(pfilnm) + ' for update of ' + %trim(pfldnm) +           ' with nc';

// prepare, declare the cursor 
exec sql prepare styleCursor from :sltstmt;
exec sql declare c1 cursor for styleCursor;
exec sql open c1;
     dow sqlcode = 0;
       exec sql fetch next from c1 into :stylno;
       if sqlcode = 0;
         // retrieve material number
        rtvMaterialNo(stylno:matno);             
        // update record
          updstmt = 'update ' + %trim(plibnm) + '/' + %trim(pfilnm) + 
   ' set ' + %trim(pfldnm) + ' = ''' + 
                          %trim(matno)   ''' where current of c1';
           exec sql execute immediate :updstmt;                        

First of all, I'd like to confirm that "fetch next" will only retrieve one record. Next, I want to make sure that the "where current of will only update the record that was retrieved by the c1 cursor.

Any comments would be greatly appreciated.

Chris                                     

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS