Multiple Row

This section is about the Multiple Row inserts and Multiple Row fetches support in Rational® Business Developer V9.0.

Rational Business Developer V9.0 introduces a new Multiple Row feature to improve performance when accessing SQL tables. With this new feature, you can fetch or insert as many number of rows as demanded during one interaction with the database.

The difference between Single-row fetch and Multi-row fetch can be demonstrated as follows:

Single Row Fetch vs Multi Row fetch

Rational Business Developer V9.0 supports Multiple Row operations on open, get, add, and replace statements for SQL records. For details, see the open considerations for SQL, get considerations for SQL, add considerations for SQL, delete considerations for SQL, and replace considerations for SQL statements.

Example

The following sample shows how to fetch the number of rows as demanded. Adapt any sections of this code to your own application.

Multiple Row or rowset processing is activated in EGL SQL by specifying a rowsetsize property on a dynamic array variable declaration or the use of the rowsetsize property on the open statement.
employees employeeRecord[0]{rowsetsize=10};  

//assume table EMPLOYEETEST contains 10,000 rows, the result 
// set for the below SQL will return 10,000 rows

// it uses rowset processing to interact with the database

Open abc with #sql{
    select eID, uName, PASSWORD, fName, lName, office, sex, EMail
    from EMPLOYEETEST
    order by eID
} for employees;

get next employees;  //this returns an 10 element array with 
                     //the first 10 rows of the result set


while ( sysvar.sqlData.sqlcode == 0 )

   // sysvar.sqlData.sqlerrd[3] will contains the number of returned rows

   for ( i int from 1 to sysvar.sqlData.sqlerrd[3] )

     // if eid is greater than 2000, stop fetching more rows.
     // This is what is meant by "demanded" 
        if ( employees[i].eID > 2000 ) 
            exit while;
        end
        SysLib.writeStdout( employees[i].fName );
    end

    get next employees;  //this returns the next 10 rows and 
                         //replaces rows in array

end
close abc;
Table 1. Compatibility considerations for delete
Platform Issue
Java generation and Debug The get diagnostics statement is not supported. Generation will be successful, but the use of the get diagnostics statement will return a sqlException at runtime or when running this through the debugger.

Feedback