Inserting from a fixed-size lvarchar host variable

To insert the data from a fixed-size lvarchar host variable into an opaque-type column, take the following steps, which are illustrated in Accessing the external format of the circle opaque data type:

Define the fixed-size lvarchar host variable.
The example explicitly reserves 30 bytes for the lv_circle host variable.
Put the character string that corresponds to the external format of the opaque data type into the lvarchar host variable.
When you put data into an lvarchar host variable, you must know the external format of the opaque type. For the INSERT statement to succeed, the data in the lvarchar host variable lv_circle must conform to the external format of the opaque data type (which External Format of the circle Opaque data type shows).
Insert the data that the lvarchar host variable contains into the opaque-type column.
When the database server executes the INSERT statement, it calls the input support function for the circle data type (circle_in) to translate the external format of the data that the client application sent to the internal format that it stores on disk.

Accessing the external format of the circle opaque data type also shows an INSERT of literal values into the circle_col column. Literal values in an INSERT (or UPDATE) statement must also conform to the external format of the opaque data type.

You can use a fixed-size lvarchar host variable to insert a null value into an opaque-type column with the following steps:

Set the lvarchar host variable to an empty string.
Set an indicator variable for the lvarchar host variable to -1.

The following code fragment inserts a null value into the circle_col column with the lv_circle host variable:

EXEC SQL BEGIN DECLARE SECTION;
   lvarchar lv_circle[30];
   int circle_ind;
EXEC SQL END DECLARE SECTION;
;

strcpy(lv_circle, "");
circle_ind = -1;
EXEC SQL insert into circle_tab 
   values (:lv_circle:circle_ind)l;