Accessing the Internal Format of an Opaque Type
You can access the internal or binary format of an opaque data type with an ESQL/C host variable in two ways:
A fixed-length opaque data type has a predefined size for its data. This size is equal to the size of the internal data structure for the opaque data type.
A varying-length data type holds data whose size might vary from row to row or instance to instance.
Both the fixed binary and var binary data types have a one-to-one mapping between their declaration and the internal data structure of the opaque data type. The database server invokes the following support functions of the opaque data type when the application transfers data in the fixed binary or var binary host variables:
The database server invokes the receive support function for operations such as INSERT and UPDATE statements that send the internal format of an opaque type to the database server.
The database server invokes the send support function for operations such as SELECT and FETCH statements that send the internal format of an opaque type to the client application.
The following sections describe the fixed binary and var binary data types.
Accessing a Fixed-Length Opaque Type
The fixed binary data type allows you to access a fixed-length opaque-type column in its internal format. Follow these steps to transfer the internal format of a fixed-length opaque-type column between the database server and the ESQL/C application:
1. Declare a fixed binary host variable
2. Use the fixed binary host variable in an SQL statement to perform any select, insert, update or delete operations on the internal format of the fixed-length opaque-type column.
The following sections describe each of these steps in more detail.
Declaring fixed binary Host Variables
Use the fixed binary data type to declare host variables that access the internal format of a fixed-length opaque data type. To declare a fixed binary host variable, use the following syntax.
To use a fixed binary host variable, you must reference a C data structure that maps the internal data structure of the opaque data type. You specify this C data structure as the structure name in the fixed binary declaration.
Informix suggests that you create a C header file (.h file) for the C data structure that defines a fixed-length opaque data type. You can then include this header file in each ESQL/C source file that uses fixed binary host variables to access the opaque data type.
For example, the following code fragment declares a fixed binary host variable called my_circle for the circle opaque data type:
In this example, the circle.h header file contains the declaration for the circle_t structure (see Figure 10-1), which is the internal data structure for the circle opaque type. The declaration for the my_circle host variable specifies both the name of the opaque data type, circle, and the name of its internal data structure, circle_t.
The Opaque Type
When you declare a fixed binary host variable, you must specify the opaque type as a quoted string.
Important: Both the single quote (') and the double quote (") are valid quote characters. However, the beginning quote and ending quote characters must match.
The opaque type name is optional; it affects the declaration as follows:
You can use the fixed binary 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 fixed binary host variable can hold data only for the specified opaque type data type.
You can declare several fixed binary variables in a single declaration. However, all variables must have the same opaque type, as the following declaration shows:
Using fixed binary Host Variables
Your ESQL/C program must handle all manipulation of the internal data structure for the fixed binary host variable; it must explicitly allocate memory and assign field values.
Figure 10-7 shows how to use a fixed binary 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 binary Host Variable
To insert the data that a fixed binary host variable contains into an opaque-type column, the code fragment in Figure 10-7 takes the following steps:
1. Includes the definition of the internal structure of the circle opaque data type.
The definition of the circle_t internal data structure, which Figure 10-1 shows, must be available to your ESQL/C program. Therefore, the code fragment includes the circle.h header file, which contains the definition of the circle_t structure.
2. Stores the data for the fixed binary host variable into the internal data structure, circle_t.
The declaration of the fixed binary host variable associates the circle_t internal data structure with the fbin_circle host variable. The code fragment assigns a value to each member of the circle_t data structure.
3. Inserts the data that the fbin_circle host variable contains into the circle_col opaque-type column.
When the database server executes the INSERT statement, it calls the receive support function for the circle data type (circle_rcv) to perform any translation necessary between the internal format of the data that the ESQL/C client application has sent (circle_t) and the internal format of the circle data type on disk.
To insert a null value into an opaque-type column with a fixed binary host variable, set an indicator variable to -1. The following code fragment inserts a null value into the circle_col column with the fbin_circle host variable:
For more information on indicator variables, see page 1-42.
Selecting into a fixed binary Host Variable
To select the data that an opaque-type column contains into a fixed binary host variable, the code fragment in Figure 10-7 takes the following steps:
1. Selects the data that the circle_col opaque-type column contains into the fbin_circle host variable.
When the database server executes the SELECT statement, it calls the send support function for circle (circle_snd) to perform any translation necessary between the internal format that it retrieved from disk and the internal format that the ESQL/C application uses. 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.
2. Accesses the circle data from the fixed binary host variable.
After the SELECT statement, the fbin_circle host variable contains data in the internal format of the circle data type. The code fragment obtains the value of the (x,y) coordinate from the members of the circle_t data structure.
When you select a null value from an opaque-type column into a fixed binary host variable, ESQL/C sets any accompanying indicator variable to -1.
Accessing a Varying-Length Opaque Type
The var binary data type allows you to access the internal format of either of the following opaque data types:
Follow these steps to transfer the internal format of either of these opaque data type columns between the database server and the ESQL/C application:
1. Declare a var binary host variable
2. Use the var binary host variable in an SQL statement to perform any select, insert, update, or delete operations on the internal format of the opaque-type column.
The following sections describe each of these steps in more detail.
Declaring var binary Host Variables
To declare a var binary host variable, use the following syntax.
Figure 10-8 shows declarations for three var binary variables.
In the declaration of a var binary host variable, the name of the opaque type must be a quoted string.
Important: Both the single quote (') and the double quote (") are valid quote characters. However, the beginning quote and ending quote characters must match.
The opaque type name is optional; it affects the declaration as follows:
The advantage of the omission of opaque type is that you can use the var binary host variable to hold data that has been selected from several different opaque types (as long as the database server is able to find the appropriate support functions).
The loss of ambiguity that the opaque type name provides can make data conversion more efficient. However, in this case, the var binary host variable can only hold data from the specified opaque type data type.
You can declare several var binary variables in a single declaration line. However, all variables must have the same opaque type, as Figure 10-8 shows.
Using var binary Host Variables
In an ESQL/C program, the varying-length C structure, ifx_varlena_t, stores a binary value for a var binary host variable. This data structure allows you to transfer binary data without knowing the exact structure of the internal format for the opaque data type. It provides a data buffer to hold the data for the associated var binary host variable.
This section uses a varying-length opaque data type called image to demonstrate how an ESQL/C var binary host variable accesses an opaque data type. The image data type encapsulates an image such as a JPEG, GIF, or PPM file. If the image is less than 2 kilobytes, the data structure for the data type stores the image directly. However, if the image is greater than 2 kilobytes, the data structure stores a reference (an LO-pointer structure) to a smart large object that contains the image data. Figure 10-9 shows the internal data structure for the image data type in the database.
Figure 10-10 shows the CREATE TABLE statement that creates a table called image_tab that has a column of type image and an image identifier.
Figure 10-11 shows how to use a var binary host variable to insert and select data in the image_col column of the image_tab table (see Figure 10-10).
For more information on the ifx_var_flag(), ifx_var_alloc(), ifx_var_setdata(), ifx_var_getdata(), and ifx_var_dealloc() functions, see "The lvarchar pointer and var binary Library Functions".
Inserting from a var binary Host Variable
To insert the data that a var binary host variable contains into an opaque-type column, the code fragment in Figure 10-11 takes the following steps:
1. Loads the image data from an external JPEG, GIF, or PPM file into the image_t internal data structure.
The load_image() C routine loads the user_image structure from an external file. The definition of the image_t internal data structure, which Figure 10-9 shows, must be available to your ESQL/C program. Therefore, the code fragment includes the image.h header file, which defines the image_t structure.
The getsize() C function is provided as part of the ESQL/C support for the image opaque type; it returns the size of the image_t structure.
2. Allocates memory for the data buffer of the var binary host variable, vbin_image.
The ifx_var_flag() function with a flag value of 0 notifies ESQL/C that the application will perform memory allocation for the vbin_image host variable. The ifx_var_alloc() function then allocates for the data buffer the number of bytes that the image data requires (imgsz).
3. Stores the image_t structure in the data buffer of the vbin_image host variable.
The ifx_var_setdata() function saves the data that the user_image structure contains into the vbin_image data buffer. This function also requires the size of the data buffer, which the getsize() function has returned.
4. Inserts the data that the vbin_image data buffer contains into the image_col opaque-type column.
When the database server executes the INSERT statement, it calls the receive support function for the image data type to perform any translation necessary between the internal format of the data that the ESQL/C client application has sent (image_t) and the internal format of the image data type on disk.
5. Deallocates the data buffer of the vbin_image host variable.
The ifx_var_dealloc() function deallocates the vbin_image data buffer.
To insert a null value into an opaque-type column with a var binary host variable, you can use either of the following methods:
The following code fragment uses the image_ind indicator variable and the vbin_image host variable to insert a null value into the circle_col column:
#include <image.h>;
EXEC SQL BEGIN DECLARE SECTION;
var binary 'image' vbin_image;
int image_ind;
EXEC SQL END DECLARE SECTION;
image_ind = -1;
EXEC SQL insert into image_tab
values (:vbin_image:image_ind);
For more information on indicator variables, see page 1-42.
For the same vbin_image host variable, the following lines use the ifx_var_setnull() function to insert a null value into the circle_col column:
ifx_var_setnull(&vbin_image, 1);
EXEC SQL insert into image_tab values (:vbin_image);
For more information on the ifx_var_setnull() function, see page 10-46.
Selecting into a var binary Host Variable
To select the data that an opaque-type column contains into a var binary host variable, the code fragment in Figure 10-11 takes the following steps:
1. Allocates memory for the data buffer of the var binary host variable, vbin_image.
The ifx_var_flag() function with a flag value of 1 notifies ESQL/C that it is to allocate a new data buffer for the vbin_image host variable. (This data buffer had been deallocated after the INSERT statement completed.) ESQL/C performs this allocation when it receives the data from the SELECT statement.
2. Selects the data that the image_col opaque-type column contains into the vbin_image data buffer.
When the database server executes the SELECT statement, it calls the send support function for image to perform any translation necessary between the internal format of the image data type on disk and the internal format that the ESQL/C client application has sent (image_t).
3. Stores the data that the data buffer of the vbin_image host variable contains in an image_t structure.
After the SELECT statement, the data buffer of the vbin_image host variable contains data in the internal format of the image data type. The ifx_var_getdata() function returns the contents of this data buffer into an image_t data structure. Because the ifx_var_getdata() function returns the data buffer as a "void *" value, this call to ifx_var_getdata() casts this return value as a pointer to an image_t structure before it assigns it to the image_ptr variable.
4. Unloads the image data from the image_t internal data structure to an external JPEG, GIF, or PPM file.
The unload_image() routine unloads the user_image structure to an external file.
5. Deallocates the data buffer of the vbin_image host variable.
The ifx_var_dealloc() function deallocates the vbin_image data buffer. You must explicitly deallocate the data buffer even when ESQL/C allocated it for you.
To check for a null value from an opaque-type column with a var binary host variable, you can use either of the following methods:
The following code fragment uses the image_ind indicator variable and the vbin_image host variable to check for a null value from the circle_col column:
#include <image.h>;
EXEC SQL BEGIN DECLARE SECTION;
var binary 'image' vbin_image;
int image_ind;
EXEC SQL END DECLARE SECTION;
EXEC SQL select image_col into :vbin_image:image_ind
from image_tab
where image_id = 1;
if (image_ind == -1)
.
.
.
For more information on indicator variables, see page 1-42.
For the same vbin_image host variable, the following lines use the ifx_var_isnull() function to check for a null value in the circle_col column:
EXEC SQL select image_col into :vbin_image
from image_tab
where image_id = 1;
if (ifx_var_isnull(&vbin_image) == 1)
.
.
.
For more information on the ifx_var_isnull() function, see page 10-41.
|
