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.
I normally use a (SELECT+WHEN) like
ReplyDeleteSELECT ;
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 ;