Wednesday, May 16, 2018

Deleting the current row when using cursor

delete current sql cursor row

In a previous post I wrote about how to perform an update of the current row/record when using a SQL cursor in a RPG program. I have been asked what could be considered the obvious follow on question: How I delete the current row/record when using a SQL cursor?

Fortunately it is as simple as the Update process. All I need to do is...

  • Define the cursor
  • Open the cursor
  • Fetch from the cursor
  • Delete
  • Close the cursor

In its simplest form a program could just be like this:

01  **free                                                         
02  dcl-ds Record extname('TESTFILE') ;
03  end-ds ;

04  exec sql DECLARE C0 CURSOR FOR 
              SELECT * FROM TESTFILE 
              WITH NC ;

05  exec sql OPEN C0 ;

06  exec sql FETCH NEXT FROM C0 INTO :Record ;

07  exec sql DELETE TESTFILE WHERE CURRENT OF C0 ;

08  exec sql CLOSE C0 ;

09  *inlr = *on ;

This program will delete the first row/record it fetches.

Line 1: I want my RPG code to be free!.

Line 2 and 3: I will be fetching the row of data into this data structure. I have used the EXTNAME keyword to use the file's definition to define the data structure.

Line 4: My cursor is defined to select all the rows/fields from the table/file, and with no commitment control.

Line 5: I open the cursor.

line 6: I fetch the next, first, row from the cursor and move the fetched data to the data structure defined.

Line 7: This delete uses the WHERE CURRENT OF followed by the cursor's name. Therefore, the current row/record retrieved is deleted.

Line 8: At the end I close the cursor.

Yes, it is as simple as that. Having compiled this program when I run it, and as I would expect the first record is deleted.

Now let me make this more complicated. In the next example I want to update certain cursor rows/records and delete others. My example program looks like:

01  **free
02  ctl-opt option(*nodebugio:*srcstmt:*nounref) ;

03  dcl-ds Record extname('TESTFILE') qualified ;
04  end-ds ;

05  exec sql DECLARE C0 CURSOR FOR
               SELECT * FROM TESTFILE
                  FOR UPDATE OF PSTAMP
               WITH NC ;

06  exec sql OPEN C0 ;

07  dow (1 = 1) ;
08    exec sql FETCH NEXT FROM C0 INTO :Record ;
09    if (SQLCOD <> 0) ;
10      leave ;
11    endif ;

12    if (Record.RECSTS = 'U') ;
13      exec sql UPDATE TESTFILE
                    SET PSTAMP = CURRENT_TIMESTAMP
                  WHERE CURRENT OF C0 ;
14    elseif (Record.RECSTS = 'D') ;
15      exec sql DELETE TESTFILE
                  WHERE CURRENT OF C0 ;
16    endif ;
17  enddo ;

18  exec sql CLOSE C0 ;

19  *inlr = *on ;

How does this differ from my first example.

Line 2: I have defined my favorite control options.

Line 3: I have used the QUALFIED keyword in the data structure definition, therefore, I must prefix the data structures subfields with the data structure name.

Line 5: The cursor is defined almost the same as it was for the update of the current cursor. The only difference is that I only want to update one column/field, PSTAMP, in the cursor.

Lines 7 – 17: I am going to fetch all the rows/records from the cursor, therefore, I am using a Do loop.

Line 8: Here is the fetch of the cursor's data to the data structure.

Line 9 – 11: If the returned SQL code is not zero then an error occurred, which is more than likely the "end of file".

Line 12: If the Record Status subfield in the data structure is "U"...

Line 13: ... Then I want to update the current row/record column/field PSTAMP with the current timestamp.

Line 14: If (I prefer IF-ELSEIF to SELECT) the Record Status is "D"...

Line 15: ... Then I want to delete the current row/record.

Line 18: After all the rows/records have been fetched I want to close the cursor.

When I run this program it does exactly what I want, see below. All the rows/records with a Record Status of "D" were deleted, and those with "U" contain timestamp of when the row/record was updated.

BEFORE
KEYFLD  RECSTS  PSTAMP
  1       U     0001-01-01-00.00.00.000000
  2       D     0001-01-01-00.00.00.000000
  3       U     0001-01-01-00.00.00.000000
  4       D     0001-01-01-00.00.00.000000
  5       U     0001-01-01-00.00.00.000000
  6       D     0001-01-01-00.00.00.000000

AFTER
KEYFLD  RECSTS  PSTAMP
  1       U     2018-05-16-02.09.15.936215
  3       U     2018-05-16-02.09.15.940090
  5       U     2018-05-16-02.09.15.940173

 

This article was written for IBM i 7.3, and should work for earlier releases too.

1 comment:

  1. I normally use a (SELECT+WHEN) like

    SELECT ;
    when (Record.RECSTS = 'U') ;
    exec sql UPDATE TESTFILE
    SET PSTAMP = CURRENT_TIMESTAMP
    WHERE CURRENT OF C0 ;
    when (Record.RECSTS = 'D') ;
    exec sql DELETE TESTFILE
    WHERE CURRENT OF C0 ;
    ENDSL ;

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.