Getting the number of rows affected by SQL statement

In a comment submitted to last week's post, blocking fetches to get more than one row at a time, Karl Hanson informed me of a way to retrieve how many rows were fetched by the "Fetch number of rows" statement. This way can also be used to retrieve how many rows/records were inserted, fetched, updated, and deleted.

I thought it would be best to illustrate this if I performed each of these statements using the example file I did in last week's post. I start my making an empty copy of the original file, TESTFILE, called TESTFILE2 using the CRTDUPOBJ command.

CRTDUPOBJ OBJ(TESTFILE) FROMLIB(MYLIB) OBJTYPE(*FILE) TOLIB(TESTFILE2) CST(*NO) TRG(*NO)

All of the SQL statements I am going to execute I am embedding in a RPG program. I am not going to show the entire program in each of the examples below, just the SQL statements. I think it is only fair if I show you the statements that are at the top of the program that are executed before the first SQL statement.

01 ctl-opt option(*nodebugio:*srcstmt:*nounref) ; 02 dcl-s Rows int(10) ; 03 exec sql SET OPTION COMMIT = *NONE ;

Or in fixed format:

01 H option(*nodebugio:*srcstmt:*nounref) 02 D Rows S 10I 0 /free 03 exec sql SET OPTION COMMIT = *NONE ;

Line 1 is my standard control options/H-specs for more information on what they mean see here. Line 2 defines the variable will contain the count of the number of rows. Line 3 is an SQL statement to turn off commitment control.

First I need to insert the rows (records) from TESTFILE into TESTILFE2. I do this using the INSERT statement:

01 exec sql INSERT INTO TESTFILE2 (FLD1,FLD2) SELECT FLD1,FLD2 FROM TESTFILE ; 02 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 03 dsply ('No. of rows inserted = ' + %char(Rows)) ;

Line 1 is the inserting all the columns (fields) and all the rows (records) in TESTFILE into TESTFILE2.

Line 2 is the interesting line. GET_DIAGNOSTICS allows me to get a variety of information about the last SQL statement executed. In this example I just want the number of records that were inserted, which is ROW_COUNT parameter. The value is moved into the variable Rows which is then displayed on line 3.

DSPLY No. of rows inserted = 16

Now I have data in TESTFILE2 I can fetch it using the fetch for x rows. I will be performing the same fetch as I did in last week's post into the data structure TestFile_ds, line 1 and 2 below. If you want to see it coded in fixed form go to last week's post here. Rather than use a variable for the number of rows to fetch I am hard coding it at 10 rows, line 7.

01 dcl-ds TestFile_ds extname('TESTFILE2') qualified dim(10) ; 02 end-ds ; 03 exec sql DECLARE C0 CURSOR FOR SELECT * FROM TESTFILE2 ORDER BY FLD1 ; 04 exec sql OPEN C0 ; 05 dow (1 = 1) ; 06 clear TestFile_ds ; 07 exec sql FETCH C0 FOR 10 ROWS INTO :TestFile_ds ; 08 if (SQLCOD <> 0) ; 09 leave ; 10 else ; 11 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 12 dsply ('No. of rows in array = ' + %char(Rows)) ; 13 endif ; 14 enddo ; 15 exec sql CLOSE C0 ;

The GET DIAGNOSTIC, line 11, is only performed when SQLCOD is zero. As there are 16 records/rows in TESTFILE2 the GET DIAGNOSTIC and DSPLY, line 12, would only be executed twice to give me the following output:

DSPLY No. of rows in array = 10 DSPLY No. of rows in array = 6

The next think I need to is to update the file:

01 exec sql UPDATE TESTFILE2 SET FLD2 = 'Keep' WHERE FLD1 >= 6 ; 02 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 03 dsply ('No. of rows updated = ' + %char(Rows)) ;

As there are ten records where FLD1 is greater or equal to 6 then the display operation code, line 3, would display:

DSPLY No. of rows updated = 10

Finally we have the delete. In this example I am going to delete the first six rows (records).

01 exec sql DELETE FROM TESTFILE2 WHERE FLD1 < 6 ; 02 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 03 dsply ('No. of rows deleted = ' + %char(Rows)) ;

As the first record in the file has zero in FLD1, you will not be surprised to learn that the Display operation code displays:

DSPLY No. of rows deleted = 6

With knowing how many rows have been inserted, fetched, updated, and/or deleted you can give this information to the user of the program or use it to condition other processing a more complex program than the examples I have given above.

 

You can learn more about GET DIAGNOSTICS on the IBM website here.

 

This article was written for IBM i 7.2, and it should work with earlier releases too.