Progress
Embedded SQL-92
Guide and Reference


Using an SQLDA for Array Fetches

ESQLC supports fetches for multiple rows with the execution of a single FETCH statement. See the "Array Fetches" section in "Query Statements," for a discussion of array fetches using explicitly declared arrays.

A similar technique is available in ESQLC using an SQLDA and dynamically allocated storage arrays. When a single execution of a FETCH operation is to retrieve multiple rows, the application must set the SQLDA field SQLD_ARRAYSZ to the maximum number of rows that can be fetched. The application determines the size requirements for the values with a DESCRIBE operation, then allocates a buffer for storing the values the query is to retrieve, and a buffer for storing indicator variables, if required. The application sets the SQLDA components SQLD_VARPTRS array and SQLD_IVARPTRS to the correct sizes.

These are the general steps to follow when you are using dynamically declared arrays for array fetches:

  1. PREPARE the SELECT statement.
  2. DECLARE a cursor for the SELECT statement.
  3. Allocate an INPUT SQLDA, if needed, for INPUT host variables.
  4. Allocate an initial OUTPUT SQLDA.
  5. DESCRIBE BIND VARIABLES, if needed, into the INPUT SQLDA.
  6. OPEN CURSOR for the SELECT statement.
  7. DESCRIBE SELECT LIST into the OUTPUT SQLDA.
  8. Reallocate the OUTPUT SQLDA if SQLD_NVARS indicates the size is insufficient.
  9. Set SQLD_ARRAYSZ to the maximum number of rows to fetch.
  10. Assign values to the SQLDA components SQLD_LENGTHS and SQLD_TYPES with information from the DESCRIBE SELECT LIST.
  11. Calculate the sizes needed for the output values array and indicator variables array.
  12. Allocate storage for output values and indicator variables and store addresses in the OUTPUT SQLDA.
  13. FETCH data recursively.
  14. CLOSE CURSOR.
  15. Free allocated storage for variables and indicator variables.
  16. Free allocated INPUT SQLDA and OUTPUT SQLDA.
Allocating for SQLDA Data Buffers and Indicator Variables

The PRO_SQLDA_Allocate function does not allocate memory for data buffers or indicator variable buffers. The application program performs these allocations and sets pointers in the SQLDA to point to them.

Assign values to the SQLDA components SQLD_LENGTHS, SQLD_TYPES, and SQLD_ARRAYSZ before allocating data buffers and indicator variable buffers.

EXAMPLE

The following code fragment shows how to determine the size requirements for the buffers, assign the values in the SQLDA, and allocate the buffers:

/*
** Allocate for sqld_varptrs buffer and sqld_ivarptrs buffer;
** set pointers in the SQLDA to address the allocation.
*/
 
for (colindex = 0 ; colindex < sqldaptr->sqld_size ; colindex++)
{
     if (!( sqldaptr->sqld_varptrs [colindex] =
          (char *) calloc (sqldaptr->sqld_lengths [colindex],
                          sizeof (char) ))
 
     || !( sqldaptr->sqld_ivarptrs [colindex] = calloc(sizeof(dh_i32_t) 
                          sizeof (char))))
     {
          printf ("Insufficient memory for dynamic buffer allocation \n") ;
          exit (1) ;
     }
} 

NOTE: This example assumes that SQLD_ARRAYSZ is 1.


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