Wednesday, April 15, 2015

Getting the number of rows affected by SQL statement

get number of rows from SQL insert, fetch, delete, and update

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.

9 comments:

  1. You don't need get diagnostics to get the number of processed rows -> Variable "SQLER3" contains the number of updated/inserted/deleted/selected rows

    http://www-01.ibm.com/support/knowledgecenter/ssw_i5_54/db2/rbafzmstfielddescsqlca.htm

    SQLER3 For a CONNECT for status statement, SQLERRD(3) contains information on the connection status. See CONNECT (Type 2) for more information.

    For INSERT, UPDATE, REFRESH, and DELETE, shows the number of rows affected.

    For a FETCH statement, SQLERRD(3) contains the number of rows fetched.

    For the PREPARE statement, contains the estimated number of rows selected. If the number of rows is greater than 2 147 483 647, then 2 147 483 647 is returned.


    BR,
    Sam

    ReplyDelete
    Replies
    1. I believe SQLERRD(3) is only associated with the PREPARE statement. Simon's method would work with any embedded SQL.

      Delete
    2. Nope: sqlerrd(3) works exactly as described.Source: I've used it for several years.

      Delete
  2. Great article as usual Simon!!! Thanks!!!

    ReplyDelete
  3. That is correct - row count can be retrieved from the SQLCA as well. However the GET DIAGNOSTICS statement provides access to a superset of information contained in the SQLCA. This is because the SQLCA is limited in size, so is not really extensible to provide more information to an application. The SQL DIAGNOSTICS AREA however, is not limited by size, allowing GET DIAGNOSTICS to provide more information.

    https://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajp/rzajpdiagnostics.htm

    >> The SQL diagnostics area is used to keep the returned information
    >> for an SQL statement that has been run in a program. It contains
    >> all the information that is available to you as an application
    >> programmer through the SQLCA.

    Simon, Here is an unrelated comment. In your example there is a test for SQLCOD <> 0 after the FETCH. I believe a statement can be successful (in this case rows are fetched) but certain warning conditions can return a positive SQLCOD (SQLCODE) value. If using the SQLCA interface, the SQLSTATE value may specify the specific warning condition, as well as the array of single character SQLWARN values. If using GET DIAGNOSTICS, the RETURNED_SQLSTATE can be used to determing the specific condition (there may be other GET DIAGNOSTICS options that apply - I didn't check). But my main point is that a positive SQLCOD value should not necessarily be considered an error condition.

    ReplyDelete
  4. I use this all the time to loop on the number of rows I return in a for loop

    ReplyDelete
  5. Zwargbart “Tingel” LaibachApril 16, 2015 at 7:06 AM

    This is very helpful. Thanks!

    ReplyDelete
  6. Isn't this cleaner and better? No dow 1=1, which would get you laughed out of any other language group.

    07 exec sql FETCH C0 FOR 10 ROWS
    INTO :TestFile_ds ;
    08 dow (SQLCOD <> 0) ;
    11 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ;
    12 dsply ('No. of rows in array = ' + %char(Rows)) ;
    07 exec sql FETCH C0 FOR 10 ROWS
    INTO :TestFile_ds ;
    xx enddo

    ReplyDelete
    Replies
    1. Actually not all "language group" would laugh at this. Some, even those considered experts, use it in places I've seen.

      I think it's better practice to use a more meaningful condition that says something about the code. But some conditions are just too complex.

      Maybe a max of a million or something...

      Delete

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.