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.