Managing an sqlda Structure
Your ESQL/C program can manipulate an sqlda structure with the SQL statements that Figure 17-1 summarizes.
Figure 17-1
SQL Statements That Can Be Used to Manipulate an sqlda Structure
In addition, your ESQL/C program can manage an sqlda structure in the following ways:
Defining an sqlda Structure
The ESQL/C sqlda.h header file defines the sqlda structure. To define an sqlda structure, the ESQL/C program must take the following actions:
The ESQL/C preprocessor automatically includes the sqlhdr.h file, which includes the sqlda.h header file. You can explicitly include sqlda.h to improve the readability of your programs, as follows:
EXEC SQL include sqlda.h;
The following line of code declares the da_ptr variable as an sqlda pointer:
struct sqlda *da_ptr;
Allocating Memory for the sqlda Structure
Once you define a host variable as a pointer to an sqlda structure, you must ensure that memory is allocated for all parts of this structure, as follows:
The following DESCRIBE statement obtains information about the prepared statement st_id, allocates memory for an sqlda structure, and puts the address of the sqlda structure in the pointer da_ptr:
EXEC SQL describe st_id into da_ptr;
If you use the sqlda structure to define input parameters, you cannot use a DESCRIBE statement. Therefore, your program must explicitly allocate memory for both the sqlda structure and the sqlvar_struct structures within it. (See page 17-14.)
Initializing the sqlda Structure
To send or receive column values in the database, your ESQL/C program must initialize the sqlda structure so that it describes the unknown columns of the prepared statement. To initialize the sqlda structure, you must perform the following steps:
In addition to allocating memory for the sqlda structure (see page 17-6), the DESCRIBE...INTO statement also initializes this structure with information about the prepared statement. The information that DESCRIBE...INTO can provide depends on which SQL statement it has described.
If the prepared statement is a SELECT (with no INTO TEMP clause), INSERT, or EXECUTE FUNCTION statement, the DESCRIBE...INTO statement can determine information about columns in the column list. Therefore, the DESCRIBE...INTO statement takes the following actions to initialize an sqlda structure:
For most data types, the sqllen field holds the length, in bytes, of the column. If the column is a collection type (SET, MULTISET, or LIST), a row type (named or unnamed), or an opaque type, the sqllen field is zero.
The DESCRIBE statement provides information about the columns of a column list. Therefore, you usually use DESCRIBE...INTO after a SELECT (without an INTO TEMP clause), INSERT, or EXECUTE FUNCTION statement has been prepared. The DESCRIBE...INTO statement not only initializes the sqlda structure, but also returns the type of SQL statement prepared. For more information, see "Determining Statement Type".
The following DESCRIBE statement also allocates memory for an sqlda structure and for two sqlvar_struct data structures (one for the customer_num column and another for the company column) and then initializes the pointer da_ptr->sqlvar with the initial address of the memory that is allocated to the sqlvar_struct structure:
The preceding DESCRIBE...INTO statement returns an SQLCODE value of zero (0) to indicate that the prepared statement was a SELECT statement.
Figure 17-2 shows a sample sqlda structure that this DESCRIBE...INTO statement might initialize.
If some other SQL statement has been prepared, the DESCRIBE...INTO statement cannot initialize the sqlda structure. To send or receive column values in the database, your program must perform this initialization explicitly, as follows:
You can use system memory-allocation functions such as malloc() or calloc() and assign the address to sqlvar, as follows:
da_ptr->sqlvar = (struct sqlvar_struct *) calloc(count,
sizeof(struct sqlvar_struct));
Set the sqltype, sqllen, and sqlname fields (for CHAR type data or for the qualifier for DATETIME or INTERVAL data) to provide information about a column to the database server.
To provide the column data, your program must also allocate space for this data and set the sqldata field of each sqlvar_struct structure to the appropriate location within this space. For more information, see "Allocating Memory for Column Data". If you send column data to the database server, be sure to set the sqlind field appropriately.
If you use the sqlda structure to define input parameters, you cannot use a DESCRIBE statement to initialize the sqlda structure. Your code must explicitly set the appropriate fields of the sqlda structure to define the input parameters. (See page 17-14.)
Allocating Memory for Column Data
The sqlda structure stores a pointer to the data for each column in the sqldata field of an sqlvar_struct structure. Unlike the DESCRIBE...USING SQL DESCRIPTOR statement, the DESCRIBE...INTO statement does not allocate memory for this data. When the DESCRIBE...INTO statement allocates memory for the sqlda pointer, it initializes the sqldata fields of each sqlvar_struct structure to null.
To send or receive column data in the database, your ESQL/C program must perform the following tasks:
To allocate memory for the sqldata fields, you can use a system memory-allocation function such as malloc() or calloc(). As an alternative to the malloc() system memory-allocation function, your program can declare a static character buffer for the data buffer. Figure 17-3 shows a code fragment that allocates column data from a static character buffer called data_buff.
You could replace the code fragment in Figure 17-3 with a series of system memory-allocation calls within the for loop. However, system memory-allocation calls can be expensive so it is often more efficient to have a single memory allocation and then align pointers into that memory area.
When you allocate the column data, make sure that the allocated memory is formatted for the column data type. This data type is one of the ESQL/C or SQL data types defined in the sqltypes.h header file. (See page 15-20.) Make the allocated memory large enough to accommodate the maximum size of the data in the column.
You must also ensure that the data for each column begins on a proper word boundary in memory. On many hardware platforms, integer and other numeric data types must begin on a word boundary. The C language memory-allocation routines allocate memory that is suitably aligned for any data type, including structures, but the routines do not perform alignment for the constituent components of the structure.
Using the proper word boundaries assures that data types are machine independent. To assist you in this task, ESQL/C provides the following memory-management functions:
This function accepts two arguments: the current position in the data buffer and the integer ESQL/C or SQL data type for which you want to allocate space.
This function accepts two arguments: the integer ESQL/C or SQL data type (in sqltype) and the length (in sqllen) for each column value.
These ESQL/C library functions are described in more detail in Chapter 2, "INFORMIX-ESQL/C Data Types."
When you allocate memory for the DATETIME or INTERVAL data types, you can take any of the following actions to set the qualifiers in the dtime_t and intrvl_t structures:
For examples that allocate memory for the sqldata fields, see the demo3.ec (page 17-18) and unload.ec demonstration programs that are supplied with ESQL/C.
Assigning and Obtaining Values from an sqlda Structure
When you use the sqlda structure with dynamic SQL, you must transfer information in and out of it with C-language statements.
Assigning Values
To assign values to fields in the sqlda and sqlvar_struct structures, use regular C-language assignment to fields of the appropriate structure. For example:
Set sqlda fields to provide values for input parameters in a WHERE clause (page 17-14) or to modify the contents of a field after you use the DESCRIBE...INTO statement to fill the sqlda structure (page 17-10).
Obtaining Values
To obtain values from the sqlda fields, you must also use regular C-language assignment from fields of the structure. For example:
Typically, you obtain sqlda field values to examine descriptions of columns in a SELECT, INSERT, or EXECUTE FUNCTION statement. You might also need to access these fields to copy a column value that is returned by the database server from the sqlda structure into a host variable (page 17-14).
The data type of the host variable must be compatible with the type of the associated field in the sqlda structure. When you interpret the sqltype field, make sure you use the data-type values that match your environment. For some data types, X/Open values differ from Informix values. For more information, see "Determining the Data Type of a Column".
Specifying Input Parameter Values
Since the DESCRIBE...INTO statement does not analyze the WHERE clause, your program must explicitly allocate an sqlda structure and the sqlvar_struct structures (see page 17-6). To describe the input parameters you must determine the number of input parameters and their data types and store this information in the allocated sqlda structure. For general information on how to define input parameters dynamically, see page 15-26.
When you execute a parameterized statement, you must include the USING DESCRIPTOR clause to specify the sqlda structure as the location of input parameter values, as follows:
- For input parameters in the WHERE clause of a SELECT statement, use the OPEN...USING DESCRIPTOR statement. This statement handles a sequential, scrolling, hold, or update cursor. If you are certain that the SELECT will return only one row, you can use the EXECUTE...INTO...USING SQL DESCRIPTOR statement instead of a cursor. For more information, see "Handling a Parameterized SELECT Statement".
- For input parameters in the WHERE clause of a non-SELECT statement such as DELETE or UPDATE, use the EXECUTE...USING DESCRIPTOR statement. For more information, see "Handling a Parameterized UPDATE or DELETE Statement".
- For input parameters in the VALUES clause of an INSERT statement, use the EXECUTE...USING SQL DESCRIPTOR statement. If the INSERT is associated with an insert cursor, use the PUT...USING DESCRIPTOR statement. For more information, see "Handling an Unknown Column List".
Putting Column Values into an sqlda Structure
When you create a SELECT statement dynamically, you cannot use the INTO host_var clause of FETCH because you cannot name the host variables in the prepared statement. To fetch column values into an sqlda structure, use the USING DESCRIPTOR clause of FETCH instead of the INTO clause. The FETCH...USING DESCRIPTOR statement puts each column value into the sqldata field of its sqlvar_struct structure.
Using the FETCH...USING DESCRIPTOR statement assumes that a cursor is associated with the prepared statement. You must always use a cursor for SELECT statements and cursory functions (EXECUTE FUNCTION statements that return multiple rows). However, if either of these statements returns only one row, you can omit the cursor and retrieve the column values into an sqlda structure with the EXECUTE...INTO DESCRIPTOR statement.
Once the column values are in the sqlda structure, you can transfer the values from the sqldata fields to the appropriate host variables. You must use the sqllen and sqltype fields to determine, at runtime, the data types for the host variables. You might need to perform data type or length conversions between the SQL data types in the sqltype fields and the ESQL/C data types that are needed for host variables that hold the returned value.
For more information on how to execute SELECT statements dynamically, see "Handling an Unknown Select List". For more information on how to execute a user-defined function dynamically, see page 17-26.
Freeing Memory Allocated to an sqlda Structure
Once you finish with an sqlda structure, free the associated memory. If you execute multiple DESCRIBE statements and you neglect to free the memory allocated by these statements, your application might run into memory constraints and the database server might exit.
To release the memory that the sqlda structure occupies, use the standard C library free() function, as shown in the following example:
If your ESQL/C program executes a DESCRIBE statement multiple times for the same prepared statement and allocates a separate sqlda structure for each DESCRIBE, it must explicitly deallocate each sqlda structure. Figure 17-4 shows an example.
If your program allocated space for column data (see "Allocating Memory for Column Data"), you must also deallocate the memory allocated to the sqldata fields. For information on how to free other program resources, see "Freeing Resources".
|
