Q: I'm using the SQL Call Level Interface (CLI) from my ILE RPG program. Can I load multiple rows at once? I want to fill up a data structure array in one fetch, rather than having to call fetch in a loop.

A: The SQL CLI includes an API called SQLExtendedFetch() that can fetch more than one record at a time. In this article, I explain how to use that API to load data into a data structure array.

For the sake of example, let's say that you want to load the customer number, name, and city from a table called CUSTFILE into a data structure array. The CUSTFILE table was created using the following DDS source:

     A                                      UNIQUE
     A          R RCUSTFILE
     A            CUSTNO         4S 0
     A            NAME          25A
     A            STREET        25A
     A            CITY          25A
     A            STATE          2A
     A            ZIP           10A
     A          K CUSTNO

(Alternatively, you could create the same table with the Create Table SQL statement. For the sake of this example, how the table was created doesn't matter.)

In your RPG program, you need to create a data structure array that contains the columns that you want to read. The columns in your program must be contiguous in memory. That means that you can't have any extra fields in your data structure array besides the ones that you load from the file. Furthermore, you can't use separate arrays for each field. They have to be in a data structure array, an overlay array, or a multiple-occurrence data structure (MODS) so that they're laid out like a record.

For this example, because I'm fetching the customer number, name, and city, I can code my data structure array as follows:

     D FETCH_SIZE      C                   const(10)

     D Row             ds                  qualified
     D                                     dim(FETCH_SIZE)
     D   Cust                         4P 0
     D   Name                        25A
     D   City                        15A

I coded the number of records that I want to fetch in the FETCH_SIZE named constant because I need to use this number in several places. This way, if I ever want to change the amount that I fetch at once, I have to change only the constant.

Now that I have an array to receive the rows that I fetch, I also need another array to receive the length of each column fetched. The lengths are always stored in SQLINTEGER fields, but they have to be laid out in an array of contiguous elements, just like the actual data read from the row.

Here's how I define the array of column lengths in my example program:

     D FetchLen        ds                  qualified
     D                                     dim(FETCH_SIZE)
     D   Cust                              like(SQLINTEGER)
     D   Name                              like(SQLINTEGER)
     D   City                              like(SQLINTEGER)

You create your CLI environment, connection handle, and statement handle the same way that you'd typically create them in a program that uses CLI. After you have a statement handle, you need to set the statement's ROWSET_SIZE attribute to the number of rows that you want to fetch at once. While you're doing that, to improve performance, tell SQL that you plan to use this statement only for fetching and not for updating:

          rc = SQLSetStmtAttrI( stmt
                              : SQL_ATTR_FOR_FETCH_ONLY
                              : SQL_TRUE
                              : 0 );
          check_error(rc: env: conn: stmt
                     : 'SQLSetStmtAttr(SQL_ATTR_FOR_FETCH_ONLY)');

          rc = SQLSetStmtAttrI( stmt
                              : SQL_ATTR_ROWSET_SIZE
                              : FETCH_SIZE
                              : 0 );
          check_error(rc: env: conn: stmt
                     : 'SQLSetStmtAttr(SQL_ATTR_ROWSET_SIZE)');

The SQLSetStmtAttrI() prototype is called to change a statement's attribute. The check_error() subprocedure is a subprocedure in my program that I run after each statement to check whether an error occurred. If you'd like to see the check_error() routine, please download the code that accompanies this article (a link to the code is at the end of this article).

After I have those attributes set, I'm ready to tell SQL which rows to fetch with a Select statement. For the sake of keeping this article simple, I fetch all the rows in my CUSTFILE table sorted by the City column.

          rc = SQLExecDirect( stmt
                            : 'select CustNo,Name,City'
                            + ' from CUSTFILE'
                            + ' order by City'
                            : SQL_NTS );
          check_error(rc: env: conn: stmt: 'SQLExecDirect');

Note: I used SQLExecDirect() because I plan to run this SQL statement only once. If you plan to run your statement repeatedly, it's more efficient to call SQLPrepare once, followed by SQLExecute each time that you want to run it.

Now I tell SQL which variables to load the results of the Select statement into. When fetching into an array, you always tell it to use the fields from the first element of the array. SQL automatically inserts subsequent rows into subsequent array elements (to the limit that I specified with the SQL_ATTR_ROWSET_SIZE attribute).

Here's the code that binds the database columns to my array of data structures:

          rc = SQLBindCol( stmt
                         : 1
                         : SQL_DECIMAL
                         : %addr(Row(1).Cust)
                         : %len(Row(1).Cust)*256 + %decpos(Row(1).Cust)
                         : fetchlen(1).Cust );
          check_error( rc: env: conn: stmt: 'SQLBindCol(1)');

          rc = SQLBindCol( stmt
                         : 2
                         : SQL_CHAR
                         : %addr(Row(1).Name)
                         : %size(Row.Name)
                         : fetchlen(1).Name );
          check_error(rc: env: conn: stmt: 'SQLBindCol(2)');

          rc = SQLBindCol( stmt
                         : 3
                         : SQL_CHAR
                         : %addr(Row(1).City)
                         : %size(Row.City)
                         : fetchlen(1).City );
          check_error(rc: env: conn: stmt: 'SQLBindCol(3)');

This code tells SQL to fetch the first column from the Select statement (column number 1) into the Cust field from the first element of my Row data structure. Likewise, the second column is fetched into Row(1).Name, and the third into Row(1).City.

This code also tells SQL that the lengths for each of these rows should be fetched into the fetchlen array.

Now that all the setup is done, we're ready to call SQLExtendedFetch() so that the array is populated from the database.

          dow '1'; // loop indefinitely.

              rc = SQLExtendedFetch( stmt
                                   : SQL_FETCH_NEXT
                                   : 0
                                   : count
                                   : status );
              check_error(rc: env: conn: stmt: 'SQLExtendedFetch()');

              if (rc = SQL_NO_DATA_FOUND);
                 leave;
              endif;

              for x = 1 to count;
                   // use the contents of the ROW array here.
              endfor;

          enddo;

SQLExtendedFetch() takes the SQL statement handle as its first parameter.

The second parameter tells SQL where the cursor should be moved after fetching the rows from the database. If I had used a scrollable cursor, I could've used this parameter to move forward, backward, to the start, to the end, or to any relative position by specifying SQL_FETCH_NEXT, SQL_FETCH_PRIOR, SQL_FETCH_FIRST, SQL_FETCH_LAST, or SQL_FETCH_RELATIVE, respectively. However, because I did not use a scrollable cursor, I can move only forward. Therefore, SQL_FETCH_NEXT is the only valid option for the second parameter in my example.

SQL_FETCH_NEXT causes the cursor to move to the next row after the 10 rows that I fetch.

The third parameter is used only when you specify SQL_FETCH_RELATIVE. A positive number specifies the number of rows to move forward for each row read. If the number is negative, the cursor moves backward instead. Because I specified SQL_FETCH_NEXT, this parameter is ignored.

The fourth parameter, in which I specified count, returns the number of rows read. When I reach the end of the result set, I might not have enough rows left to fill my array. This variable tells me how many rows were actually loaded.

The final parameter is an array of SQLSMALLINT fields that tells me, for each row, whether it was fetched. Each element is set either to SQL_ROW_SUCCESS or SQL_ROW_NOROW if the row could be fetched, or not, respectively. Essentially, this parameter gives you the same information that the count parameter gives you, so I omit it in my example.

You can download the sample program for this article, including my prototypes and constants for the SQL CLI APIs and my check_error() subprocedure, from the following link:
http://www.pentontech.com/IBMContent/Documents/article/52912_92_SqlCliArray.zip