INFORMIX
Informix-ESQL/C Programmer's Manual
Chapter 15: Determining SQL Statements
Home Contents Index Master Index New Book

Using a Fetch Array

 A fetch array enables you to increase the number of rows that a single FETCH statement returns from the fetch buffer to an sqlda structure in your program. A fetch array is especially useful when you fetch simple large object (TEXT or BYTE) data. A fetch of simple large object data without a fetch array requires the following two exchanges with the database server:

When you use a fetch array, ESQL/C sends a series of simple large object descriptors to the database server and the database server returns the corresponding column data all at one time.

To use a fetch array

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

    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.

    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.

    6. Repeat Steps 4 and 5 until all rows are fetched.

    7. Free the memory that the sqlda structure uses.

Important: The FetArrSize feature does not work when both the Deferred-PREPARE and OPTOFC features are enabled. When these two features are enabled, ESQL/C 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.
The sample program below shows how to perform the preceding steps. It uses separate functions to initialize, print, and free the sqlda structure. These functions are described in the following sections.

Allocating Memory for the Fetch Arrays

 The DESCRIBE...INTO statement allocates memory for the sqlda structure and its sqlvar_struct structures. However, it does not allocate memory for the sqldata field of the sqlvar_struct structures. The sqldata field holds the fetch array for a retrieved column. Therefore, you must allocate sufficient memory to each sqldata field to hold the elements of the fetch array.

A new global variable, FetArrSize, indicates the number of rows to be returned per FETCH statement. This variable is defined as a C language short integer data type. It has a default value of zero, which disables the fetch array feature. You can set FetArrSize to any integer value in the following range:

The MAXSMINT value is the maximum amount of the data type that ESQL/C can retrieve. It's value is 32767 bytes (32 kilobytes). If the size of the fetch array is greater than MAXSMINT, ESQL/C automatically reduces its size to 32 kilobytes.

You can use the following calculation to determine the appropriate size of the fetch array:

The preceding equation uses the following information:

fetch-array
size

The size of the fetch array, which the FetArrSize global variable indicates

fetch-buffer
size

The size of the fetch buffer, which the FetBufSize global variable indicates. For information about the size of the fetch buffer, see "Optimizing Cursor Execution".

row size

The size of the row to be fetched. To determine the size of the row to be fetched, call the rtypmsize() function for each column of the row. This function returns the number of bytes that are needed to store the data type. For more information on the rtypmsize() function, see Chapter 2, "INFORMIX-ESQL/C Data Types."

However, if you set FetArrSize so that the following relationship is true,

ESQL/C automatically resizes the fetch buffer (FetBufSize) as follows to hold the size of the fetch array:

If the result is greater than 32 kilobytes (MAXSMINT), ESQL/C sets FetBufSize to 32 kilobytes and FetArrSize as follows:

Important: The FetArrSize global variable can be used in thread safe ESQL/C applications.
Follow these steps to allocate memory for a fetch array:

    1. Determine the size of the row that you are retrieving from the database.

    2. Determine the size of the fetch array and set the FetArrSize global variable to this value.

    3. For each simple large object column (TEXT or BYTE) , allocate a fetch array of loc_t structures.

    4. For each simple large object column (TEXT or BYTE), initialize the loc_t data structures as follows.

    5. Allocate memory for the indicator variable.

    6. For all other columns, allocate a fetch array that holds the data type of that column.

The following example code illustrates how you would allocate memory for fetch arrays for the following prepared query:

The function is called init_sqlda():

For more information on how to allocate memory for the sqldata field, see "Allocating Memory for the sqlda Structure".

Obtaining Values from Fetch Arrays

Each FETCH attempts to return FetArrSize number of values into the sqldata fields of the sqlvar_struct structures of the sqlda structure. You can check the sqlca.sqlerrd[2] value to determine the actual number of rows that the FETCH did return.

Each fetch array holds the values for one column of the query. To obtain a row of values, you must access the element at the same index of each the fetch arrays. For example, to obtain the first row of values, access the first element of each of the fetch arrays.

The sample program calls the print_sqlda() function to obtain values from the fetch arrays for the following prepared query:

Freeing Memory for a Fetch Array

 ESQL/C does not release resources for the sqlda structure. When your application no longer needs the sqlda structure, it must free all memory that it uses. For more information, see "Freeing Memory Allocated to an sqlda Structure".

The sample program calls the free_sqlda() function to free the memory that the sqlda structure uses.



Informix-ESQL/C Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.