Insert opaque-type 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.

The following table shows the type and length fields for the dynamic-management structures.
Table 1. 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

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 are not 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’;