• 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

Handling an Unknown Select List

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.

X/O

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".

Handling an Unknown Column List

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.

X/O

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".

Determining Unknown Input Parameters

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)

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.

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".

Determining Return Values Dynamically

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).

X/O

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.

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 Universal Server" for more information.

For information on how to use a system-descriptor area to hold function return values, see page 16-22. To use an sqlda structure to hold return values, see page 17-26.

Handling Statements That Contain 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

• 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.

Figure 15-13
Type and Length fields of Dynamic-Management Structures

Dynamic-Management Structure	Type Field	Length Field
system-descriptor area	TYPE field of an item descriptor	LENGTH field of an item descriptor
sqlda structure	sqltype field of an sqlvar_struct structure	sqllen field of an sqlvar_struct structure

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

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.

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';

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).

SQL Statements with Distinct-Type Columns

• 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

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-13 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.

Source Type	Distinct-Bit Constant	Distinct-Bit Macro
LVARCHAR	SQLDLVARCHAR	ISDISTINCTLVARCHAR(type_id)
BOOLEAN	SQLDBOOLEAN	ISDISTINCTBOOLEAN(type_id)
Any other data type	SQLDISTINCT	ISDISTINCTTYPE(type_id)

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
			  */
			}  
		}

	}