Using a fetch array

About this task

To use a fetch array:

Procedure

  1. Declare an sqlda structure to hold the columns you want to fetch.

    You cannot use host variables or system-descriptor areas in a FETCH statement to hold fetch arrays for columns. You must use an sqlda structure and the FETCH...USING DESCRIPTOR statement. For information about how to declare and use sqlda structures, see An sqlda structure.

  2. Use the DESCRIBE...INTO statement to initialize the sqlda structure and obtain information about the prepared query.

    The DESCRIBE...INTO statement allocates memory for the sqlda structure and the sqlvar_struct structures.

  3. For the sqldata field, allocate a buffer that is large enough to hold the fetch array for each column.

    To allocate the memory for an sqldata field, you must set the FetArrSize global variable to the size of the fetch array for the associated column. For more information, see Allocate memory for the fetch arrays.

  4. Issue the FETCH...USING DESCRIPTOR statement to retrieve the column data into the fetch arrays.

    The FETCH statement puts the retrieved rows into the sqldata fields of the sqlvar_struct structures in sqlda. Each FETCH statement returns into the sqldata fields the number of values specified by FetArrSize.

  5. Obtain the column values from the fetch arrays of each sqlvar_struct structure.

    You must obtain these values from the fetch arrays before you perform the next FETCH statement. You can check the sqlca.sqlerrd[2] field to determine the number of valid rows that the FETCH has returned. The value in sqlerrd[2] is equal to or smaller than the value you set in FetArrSize. For information about the sqlerrd array, see Exception handling. For more information about obtaining the column values, see Obtain values from fetch arrays.

  6. Repeat steps 4 and 5 until all rows are fetched.
  7. Free the memory that the sqlda structure uses.

    As with other uses of the sqlda structure, does not release resources for this structure. Your application must free memory allocated to the sqlda structure when it no longer needs it. For more information, see Free memory for a fetch array.

Results

Important: The FetArrSize feature does not work when both the Deferred-PREPARE and OPTOFC features are enabled. When these two features are enabled, does not know the size of a row until after the FETCH statement completes. By this time, it is too late for the fetch buffer to be adjusted with the FetArrSize value.