Progress
Embedded SQL-92
Guide and Reference


Array Fetches

As described in previous sections, the FETCH statement returns one row at a time from the active set selected by the OPEN statement. ESQL provides the Array Fetch facility for fetching multiple rows at a time from the active set into an explicitly declared array. This is more efficient for fetching a large number of rows since it greatly reduces the number of calls made to the database. See the "Using an SQLDA for Array Fetches" section in "Dynamic SQL Management in ESQL-92," for a description of array fetches using a dynamically declared array.

NOTES

All ESQLC arrays (excluding character arrays) are mapped into a host language structure consisting of the target array and the current size of the array. This is the C Language structure form:

SYNTAX
struct new_type_name {
  long array_size;
  element_type_name actual_array[ constant_id ]
} ; 

Host language statements can manipulate the array assuming that it is a structure with the same name as the array name and having two components actual_array and array_size. The actual_array component is the array and the array_size component contains the current size of the array.

EXAMPLE

The following example shows how to use two arrays to select up to fifty rows in one fetch:

/*
** Fetch up to 50 rows in one fetch call 
*/
#define ARRAYSZ  50
#define NAMESZ  30
 
EXEC SQL BEGIN DECLARE SECTION;
 
TYPE customer_name_t IS AN ARRAY OF CHAR WITH SIZE NAMESZ ;
TYPE customer_id_t IS OF TYPE LONG INTEGER ;
 
customer_name_array IS AN ARRAY OF customer_name_t WITH SIZE ARRAYSZ ;
customer_id_array IS AN ARRAY OF customer_id_t WITH SIZE ARRAYSZ ;
 
EXEC SQL END DECLARE SECTION;
 
EXEC SQL
     DECLARE customer_cursor CURSOR FOR
     SELECT last_name, cust_no FROM customer ;
 
if (sqlca.sqlcode != 0) goto err ;
 
EXEC SQL OPEN customer_cursor ;
 
if (sqlca.sqlcode != 0 ) goto err ; 
for (;;)
{
     int  i ;
 
     EXEC SQL FETCH customer_cursor
     INTO :customer_name_array, :customer_id_array ;
 
     /*
     ** Note that with array fetches, one or more rows
     ** could have been returned by the current execution of the
     ** FETCH statement even if the status code returned is
     ** SQL_NOT_FOUND.
     */
 
     if ((sqlca.sqlcode != 0)  && (sqlca.sqlcode != SQL_NOT_FOUND))
          break ; 
     for (i = 0 ; i < customer_name_array.tpe_size ; i++ )
          {
               printf ("Customer id = %ld Customer Name : %s\n",
               customer_id_array.tpe_array[i],
               customer_name_array.tpe_array[i]) ;
          }
 
     if (sqlca.sqlcode != 0) break ;
} /* end for (;;) */
if (sqlca.sqlcode != SQL_NOT_FOUND) goto err ;
 
EXEC SQL CLOSE customer_cursor ;
 
if (sqlca.sqlcode != 0) goto err ;
 
EXEC SQL COMMIT WORK ;
if (sqlca.sqlcode < 0)
     printf ( "COMMIT WORK returned error %ld\n", sqlca.sqlcode) ;
return ;
 
err:
     EXEC SQL ROLLBACK WORK ;
     return ; 

Executing the FETCH statement sets tpe_size to the actual number of rows returned.

NOTE: The array fetch facility provided by ESQL is an extension to the SQL-92 standard.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095