Accessing the External Format of an Opaque Type
Use the lvarchar data type for operations on an opaque-type column that has an external representation of a character string.
To use the external format of an opaque type in an SQL statement, the opaque data type must have input and output support functions defined. When the client application uses an lvarchar host variable to transfer data to or from an opaque-type column, the database server invokes the following support functions of the opaque data type:
The database server invokes the input support function for operations such as INSERT and UPDATE statements that send the external format of an opaque type to the database server.
The database server invokes the output support function for operations such as SELECT and FETCH statements that send the external format of an opaque type to the client application.
Follow these steps to transfer the external format of an opaque-type column between the database server and the ESQL/C application:
1. Declare an lvarchar host variable
2. Use the lvarchar host variable in an SQL statement to perform any select, insert, update or delete operations on the external format of the opaque-type column.
The following sections describe each of these steps in more detail.
Declaring lvarchar Host Variables
Use the lvarchar data type to declare a host variable for the external format of an opaque data type. The following diagram illustrates the syntax to declare an lvarchar host variable. To declare, use the lvarchar keyword as the variable data type, as the following syntax shows.
Figure 10-5 shows declarations for four lvarchar variables that hold the external formats of opaque-type columns.
You can declare several lvarchar variables in a single declaration line. However, all variables must have the same opaque type, as the declarations for circle1 and circle2 in Figure 10-5 show. Figure 10-5 also shows the declaration of an lvarchar pointer for the a_crcl_ptr host variable.
An lvarchar Host Variable of a Fixed Size
If you do not specify the size of an lvarchar host variable, the size is equivalent to a 1-byte C-language char data type. If you specify a size, the lvarchar host variable is equivalent to a C-language char data type of that size. When you specify a fixed-size lvarchar host variable, any data beyond the specified size is truncated when the column is fetched. Use an indicator variable to check for truncation.
Because an lvarchar host variable of a fixed size is equivalent to a C-language char data type, you can use C-language character string operations to manipulate them.
For more information on how to use an lvarchar host variable of a fixed size, see Chapter 3, "Working with Character and String Data Types."
The lvarchar Pointer Host Variable
The lvarchar pointer host variable is designed for inserting or selecting user-defined or opaque types that can be represented in a character-string format. The size of the character-string representation for opaque type columns can vary for each row so that the size of the data is unknown until the column is fetched into a host variable. The size of the data that an lvarchar pointer host variable references can range up to 2 gigabytes.
The lvarchar pointer type is not equivalent to a C-language char pointer. ESQL/C maintains its own internal representation for the lvarchar pointer type. This representation is identical to the representation of a var binary host variable, except that it supports ASCII data as opposed to binary data. You must use the ifx_var() functions to manipulate an lvarchar pointer host variable. The ifx_var() functions can only be used for lvarchar variables declared as pointers and for var binary variables, but not for lvarchar variables of a fixed size. For more information on the ifx_var() functions, see "The lvarchar pointer and var binary Library Functions".
Because the size of the data in opaque type columns can vary from one row in the table to another, you cannot know the maximum size of the data that the database server will return. When you use an lvarchar pointer host variable, you can either let ESQL/C allocate memory to hold the data, based on the size of the data coming from the database server, or you can allocate the memory yourself. Use the ifx_var_flag() function to specify which method you will use. In either case you must explicitly free the memory, using the ifx_var_dealloc() function.
To Allocate Memory Yourself
If you choose to allocate the memory to store data for an lvarchar host variable you must first fetch the data into sqlda or a system descriptor structure. You can then use the ifx_var_getllen() function to obtain the length of the data and use the ifx_var_alloc() function to allocate memory of that size.
The Opaque Type Name
This opaque type name is optional; its presence affects the declaration as follows:
You can use the lvarchar host variable to hold data for several different opaque types (as long as the database server is able to find the appropriate support functions).
Using opaque type can make data conversion more efficient. In this case, however, the lvarchar host variable can hold data only for the specified opaque type.
In the declaration of an lvarchar host variable, the name of the opaque type must be a quoted string.
Using lvarchar Host Variables
Your ESQL/C program must manipulate the external data for an lvarchar host variable. If the length of the data that come from an opaque type column does not vary, or if you know the maximum length of the data in an opaque type column, you can use a fixed-size lvarchar host variable. If the size of the data varies from one table row to another, however, use an lvarchar pointer variable and manipulate the data with the ifx_var() functions.
The following sections describe how to use both an lvarchar variable of a fixed size and an lvarchar pointer variable.
Using Fixed-Size lvarchar Host Variables
Figure 10-6 shows how to use a fixed-size lvarchar host variable to insert and select data in the circle_col column of the circle_tab table (see Figure 10-4).
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 Figure 10-6:
1. Define the fixed-size lvarchar host variable.
The example explicitly reserves 30 bytes for the lv_circle host variable.
2. 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 Figure 10-3 shows).
3. 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 ESQL/C client application has sent, to the internal format that it stores on disk.
Figure 10-6 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:
The following code fragment inserts a null value into the circle_col column with the lv_circle host variable:
For more information on indicator variables, see page 1-42.
Selecting into a Fixed-Size lvarchar Host Variable
To select data from an opaque type column into a fixed-size lvarchar host variable, the code fragment in Figure 10-6 takes the following steps:
1. Selects the data that the circle_col opaque-type column contains into the lv_circle host variable.
When the database server executes the SELECT statement, it calls the output support function for the circle data type (circle_out) to translate the internal format that it retrieved from disk to the external format that the ESQL/C application requests. This SELECT statement also uses a user-defined function called radius (see Figure 10-2) to extract the radius value from the opaque-type column. This function must be registered with the database server for this SELECT statement to execute successfully.
2. Accesses the circle data from the lvarchar host variable.
After the SELECT statement, the lv_circle host variable contains data in the external format of the circle data type.
When you select a null value from an opaque-type column into an lvarchar host variable, ESQL/C sets any accompanying indicator variable to -1.
Using lvarchar Pointer Variables
The following sections illustrate how to insert to and select from an opaque type column with an lvarchar pointer host variable. The structural representation of the opaque type column that the examples use is referred to as polygon_ type, and is defined in the following lines:
The following line illustrates the string representation of this column:
Inserting from an lvarchar Pointer Host Variable
The following example code illustrates the steps to insert data from an lvarchar pointer host variable to an opaque type column. To simplify the example, the code does not check returned values for errors.
/* String representation of mypoly1 copied into buffer*/
strcpy(buffer, "5, 10, 20 15, 10, 5, -1, 0, 0");
size = strlen(buffer);
ifx_var_alloc(&myploy1, size+1); /* Allocate memory for data in mypoly1 */
ifx_var_setlen(&myploy1, size); /* Set length of data bufferin mypoly1 */
ifx_var_setdata(&mypoly1, buffer, size); /* Store data inside mypoly1 */
exec sql insert into polygon_tab values (1, :mypoly1);
ifx_var_setnull(&mypoly1, 1); /* Set data buffer in mypoly1 to NULL */
ifx_var_dealloc(&mypoly1); /* Deallocate the data buffer in mypoly1 */
free (buffer);
The example code performs the following steps:
1. It declares the lvarchar pointer host variable, *mypoly1.
2. It creates a table that consists of an integer ID column, p_id, and a column of polygons, polygon_type.
3. It calls the ifx_var_flag() function to specify that it will allocate memory for the data buffer (flag equals 0).
4. It creates a buffer, copies the string representation of the polygon to it, and sets the size variable to the size of the buffer.
5. It calls ifx_var_alloc(), ifx_var_setlen(), and ifx_var_setdata() to allocate the data transfer buffer, set the length of the buffer, and copy the data from the application buffer to the data transfer buffer.
6. It inserts an ID value of 1 and mypoly1 to the polygon_tab table.
Selecting into an lvarchar Pointer Host Variable
The following example code illustrates the steps to select data from an opaque type column into an lvarchar pointer host variable. To simplify the example, the code does not check returned values for errors.
The example code performs the following steps:
1. It declares the lvarchar pointer host variable, *mypoly1.
2. It calls the ifx_var_flag() function to specify that it will let ESQL/C allocate memory for the data buffer (flag equals 1). ESQL/C allocates the memory by default if you do not call ifx_var_flag().
3. It selects the column poly into the *mypoly host variable.
4. It calls ifx_var_getdata() to obtain the address of the data buffer, casting the return value to char * and storing the address in buffer.
5. It calls ifx_var_getlen() to illustrate how to obtain the length of the data that was retrieved.
6. It deallocates the memory that ESQL/C allocated for *mypoly1.
|
