INFORMIX-ESQL/C Programmer's Manual Determining SQL Statements

Consider a dynamic-management structure when you execute an SQL statement under the following conditions:

• Something is not known about the structure of an SQL statement:
• The type of statement to execute is unknown.
• The table name is unknown and therefore the columns to be accessed are unknown.
• The WHERE clause is missing.
• Something is not known about the number or type of values that passes between the ESQL/C program and the database server:
• The number and data types of columns in the select list of a SELECT or in a column list of an INSERT
• The number and data types of input parameters in the statement are unknown
• The number and data types of return values of a user-defined function (executed with the EXECUTE FUNCTION statement) are unknown

The following sections briefly outline how to handle each of the categories of unknown number and data types of values.

For a SELECT statement, the columns in the select list identify the column values that are received from the database server. In the SELECT statement described and illustrated in the demo1.ec example program (see page 1-60 of this manual), the values returned from the query are placed into the host variables that are listed in an INTO host_var clause of the SELECT statement.

However, when your program creates a SELECT statement at runtime, you cannot use an INTO clause because you do not know at compile time what host variables are needed. If the type and number of the values that your ESQL/C program receives are not known at compile time, your program must perform the following tasks:

1. Declare a dynamic-management structure to serve as storage for the select-list column definitions. This structure can be either a system-descriptor area or an sqlda structure.

Use of the system-descriptor area conforms to X/Open standards.

2. Use the DESCRIBE statement to examine the select list of the prepared SELECT statement and describes the columns.
3. Specify the dynamic-management structure as the location of the data fetched from the database. From the dynamic-management structure, the program can move the column values into host variables.

Important: Use a dynamic-management structure only if you do not know the number and data types of the select-list columns at compile time.

For information about how to execute a SELECT if you do know the number and data types of select-list columns, see Executing SELECT Statements. For information on how to identify columns in the select list of a SELECT statement with a system-descriptor area, see page 16-14. For more information on how to use an sqlda structure, see page 17-17.

For an INSERT statement, the values in the VALUES clause identify the column values to be inserted into the new row. If the data types and number of the values that the ESQL/C program inserts are not known at compile time, you cannot simply use host variables to hold the data being inserted. Instead, your program must perform the following tasks:

1. Define a dynamic-management structure to serve as storage for the unknown column definitions. This structure can be either a system-descriptor area or an sqlda structure.

Use of the system-descriptor area conforms to X/Open standards.

2. Use the DESCRIBE statement to examine the column list of the prepared INSERT statement and describe the columns.
3. Specify the dynamic-management structure as the location of the data to be inserted when the INSERT statement executes.

Important: Use a dynamic-management structure only if you do not know the number and data types of the column-list columns at compile time. For information about how to execute an INSERT if you do know the number and data types of column-list columns, see Executing Non-SELECT Statements.

For information on how to identify columns in the VALUES column list of an INSERT statement with a system-descriptor area, see page 16-32. To use an sqlda structure, see page 17-31.

If you know the data types and number of input parameters of an SQL statement, use the USING host_var clause (see page 14-53). However, if you do not know the data types and number of these input parameters at compile time, you cannot use host variables to provide the parameter values; you do not have enough information about the parameters to declare the host variables.

Neither can you use the DESCRIBE statement to define the unknown parameters because DESCRIBE does not examine:

• a WHERE clause (for a SELECT, UPDATE, or DELETE statement
• the arguments of a user-defined routine (for an EXECUTE FUNCTION or EXECUTE PROCEDURE statement)

Your ESQL/C program must follow these steps to define the input parameters in any of the preceding statements:

1. Determine the number and data types of the input parameters. Unless you write a general-purpose, interactive interpreter, you usually have this information. If you do not have it, you must write C code that analyzes the statement string and obtains the following information:
• The number of input parameters [question marks (?)] that appear in the WHERE clause of the statement string or as arguments of a user-defined routine
  • The data type of each input parameter based on the column (for WHERE clauses) or parameter (for arguments) to which it corresponds
2. Store the definitions and values of the input parameters in a dynamic-management structure. This structure can be either a system-descriptor area or an sqlda structure.

Use of the system-descriptor area conforms to X/Open standards.

3. Specify the dynamic-management structure as the location of the input parameter values when the statement executes.

Important: Use a dynamic-management structure only if you do not know the number and data types of the input parameters at compile time. For information about how to execute a parameterized SQL statement if you do know the number and data types of column-list columns, see Executing Statements with Input Parameters.

For information on how to handle input parameters in the WHERE clause of a dynamic SELECT statement with a system-descriptor area, see Handling a Parameterized SELECT Statement; to use an sqlda structure, see Handling a Parameterized SELECT Statement. For information on how to handle input parameters as arguments of a user-defined function with a system-descriptor area, see Handling a Parameterized User-Defined Routine; to use an sqlda structure, see Handling a Parameterized User-Defined Routine. For information on how to handle input parameters in the WHERE clause of a dynamic UPDATE or DELETE statement with a system-descriptor area, see Handling a Parameterized UPDATE or DELETE Statement; to use an sqlda structure, see Handling a Parameterized UPDATE or DELETE Statement.

For an EXECUTE FUNCTION statement, the values in the INTO clause identify where to store the return values of a user-defined function. If the data types and number of the function return values are not known at compile time, you cannot use host variables in the INTO clause of EXECUTE FUNCTION to hold the values. Instead, your program must perform the following tasks:

1. Define a dynamic-management structure to serve as storage for the definitions of the value(s) that the user-defined function returns.

You can use either a system-descriptor area or an sqlda structure to hold the return value(s).

Use of the system-descriptor area conforms to X/Open standards.

2. Use the DESCRIBE statement to examine the prepared EXECUTE FUNCTION statement and describe the return value(s).
3. Specify the dynamic-management structure as the location of the data returned by the user-defined function.

From the dynamic-management structure, the program can move the return values into host variables.

Important: Use a dynamic-management structure only if you do not know at compile time the number and data types of the return values that the user-defined function returns. If you know this information at compile time, refer to Executing User-Defined Routines in Informix Dynamic Server for more information.

For information on how to use a system-descriptor area to hold function return values, see Handling Unknown Return Values. To use an sqlda structure to hold return values, see Handling Unknown Return Values.

This section provides information about how to perform dynamic SQL on statements that contain columns with the following user-defined data types:

• Opaque data types: an encapsulated data type that the user can define
• Distinct data types: a data type that has the same internal storage representation as its source type, but has a different name

For dynamic execution of opaque-type columns, keep the following items in mind:

• You must ensure that the type and length fields of the dynamic-management structure (system-descriptor area or sqlda structure) match the data type of the data you insert into an opaque-type column.
• ESQL/C truncates opaque-type data at 32 kilobytes if the host variable is not large enough to hold the data.

When the DESCRIBE statement describes a prepared INSERT statement, it sets the type and length fields of a dynamic-management structure to the data type of the column. Figure 15-12 shows the type and length fields for the dynamic-management structures.

If the INSERT statement contains a column whose data type is an opaque data type, the DESCRIBE statement identifies this column with one of the following type-field values:

• The SQLUDTFIXED constant for fixed-length opaque types
• The SQLUDTVAR constant for varying-length opaque types

These data-type constants represent an opaque type in its internal format.

When you put opaque-type data into a dynamic-management structure, you must ensure that the type field and length field are compatible with the data type of the data that you provide for the INSERT, as follows:

• If you provide the opaque-type data in internal format, then the type and length fields that DESCRIBE sets are correct.
• If you provide the data in external format (or any format other than the internal format), you must change the type and length fields that DESCRIBE has set to be compatible with the data type of the data.

The input and output support functions for the opaque type do not reside on the client computer. Therefore, the client application cannot call them to convert the opaque-type data in the dynamic-management structure from its external to its internal format. To provide the opaque-type data in its external representation, set the type-field value to a character data type. When the database server receives the character data (the external representation of the opaque type), it calls the input support function to convert the external representation of the opaque type to its internal representation. If the data is some other type and valid support or casting functions exist, the database server can call these functions instead to convert the value.

For example, suppose you use a system-descriptor area to hold the insert values and you want to send the opaque-type data to the database server in its external representation. In the following code fragment, the SET DESCRIPTOR statement resets the TYPE field to SQLCHAR, so that the TYPE field matches the data type of the host variable (char) that it assigns to the DATA field:

EXEC SQL BEGIN DECLARE SECTION;

	char extrn_value[100];

	int extrn_lngth;

	int extrn_type;

EXEC SQL END DECLARE SECTION;


EXEC SQL allocate descriptor 'desc1' with max 100;

EXEC SQL prepare ins_stmt from 

	'insert into tab1 (opaque_col) values(?)';

EXEC SQL describe ins_stmt using sql descriptor 'desc1';



/* At this point the TYPE field of the item descriptor is 

  * SQLUDTFIXED

  */



stcopy("(1, 2, 3, 4)", extrn_value);

extrn_lngth = stleng(extrn_value);

dtype = SQLCHAR;



/* This SET DESCRIPTOR statement assigns the external 

  * representation of the data to the item descriptor and 

  * resets the TYPE field to SQLCHAR.

  */

EXEC SQL set descriptor 'desc1' value 1

	data = :extrn_value, type = :extrn_type, 

	length = :extrn_lngth;

EXEC SQL execute ins_stmt using sql descriptor `desc1';

If you specify a host variable that is not large enough to hold the full return value from the server, ESQL/C normally truncates the data to fit the host variable and puts the actual length in an indicator variable. This indicator variable can be one that you explicitly provide or, for dynamic SQL, one of the following fields of a dynamic-management structure.

However, these indicator fields are defined as a short integer and therefore can only store sizes up to 32 kilobytes.

This size limitation of the indicator field affects how ESQL/C handles truncation of opaque-type data that is larger than 32 kilobytes. When ESQL/C receives opaque-type data that is larger than 32 kilobytes and the host variable is not large enough to hold the opaque-type data, ESQL/C truncates the data to 32 kilobytes. ESQL/C performs this truncation at 32 kilobytes even if you program a host variable that is larger than 32 kilobytes (but still not large enough for the data).

For dynamic execution of distinct-type columns, the dynamic-management structures have been modified to hold the following information about a distinct type:

• The data-type constant (from sqltypes.h) for the source type of the distinct-type column
• The extended identifier for the source type of the distinct-type column

These values are in the following fields of a dynamic-management structure.

When the DESCRIBE statement describes a prepared statement, it stores information about columns of the statement in a dynamic-management structure. (For more information, see Using the DESCRIBE Statement.) There is no special constant in the sqltypes.h file to indicate a distinct data type. Therefore, the type field of the dynamic-management structure cannot directly indicate a distinct type. (Figure 15-12 on page 15-31 shows the type fields of the dynamic-management structures.)

Instead, the type field in the dynamic-management structure has a special value to indicate that a distinct bit is set for a distinct-type column. The type field indicates the source type of the distinct data combined with the distinct bit. The sqltypes.h header file provides the following data-type constants and macros to identify the distinct bit for a distinct column.

Use the following algorithm to determine if a column is a distinct type:

if (one of the distinct bits is set)
{
/* Have a distinct type, now find the source type */
if (ISDISTINCTLVARCHAR(sqltype))
{
/* Is a distinct of LVARCHAR:
* type field = SQLUDTVAR + SQLDLVARCHAR
* source-type field = 0
* source-id field = extended identifier of lvarchar
*/
}
else if (ISDISTINCTBOOLEAN(sqltype))
{
/* Is a distinct of BOOLEAN
* type field = SQLUDTFIXED + SQLDBOOLEAN
* source-type field = 0
* source-id field = extended id of boolean
*/
}
else
{
/* SQLDISTINCT is set */
if (ISUDTTYPE(sqltype))
{
/* Source type is either a built-in simple type or an
* opaque data type
*/
if (source-id field > 0)
/* Is a distinct of an opaque type.
* Pick up the xtended identifier of the source type
* from the source-id field
*/
else
/* Is a distinct of a built-in simple type.
* Pick up the type id of the source type from the
* source-type field
*/
}
else
{
/* Source type is a non-simple type, a complex type.
* Both the source-type and source-id fields should be 0,
* the source type is embedded in the type field:
* type = source type + SQLDISTINCT
*/
}
}
}

The following table summarizes the pseudo-code of the preceding algorithm.