INFORMIX-ESQL/C Programmer's Manual Working with Opaque Data Types of the Universal Data Option

You can access the internal or binary format of an opaque data type with an ESQL/C host variable in two ways:

• Use the fixed binary data type to access a fixed-length opaque data type for which you have the C-language data structure that represents the opaque data type.

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.

• Use the var binary data type to access a varying-length opaque data type or to access a fixed-length opaque data type for which you do not have the C-language data structure.

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 receive support function describes how to transfer the opaque-type data from the fixed binary or var binary host variable into the opaque-type column.

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 send support function describes how to transfer the opaque-type data from the opaque-type column to the fixed binary or var binary host variable.

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.

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.

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.

Important: A fixed binary host variable is only valid for a column of a fixed-length opaque data type. If the opaque data type is of varying length, use a var binary host variable. If you do not know the internal data structure of a fixed-length opaque data type, you must also use a var binary host variable to access it.

For more information, see Accessing a Varying-Length Opaque Type.

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:

#include <circle.h>	/* contains definition of circle_t */



EXEC SQL BEGIN DECLARE SECTION;

	fixed binary 'circle' circle_t my_circle;

EXEC SQL END DECLARE SECTION;

In this example, the circle.h header file contains the declaration for the circle_t structure (see Figure 10-1 on page 10-4), 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.

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:

• When you omit opaque type from the fixed binary declaration, the database server attempts to identify the appropriate support functions to use when it sends the host variable to the database server for storage in the opaque-type column.

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

• When you specify opaque type in the fixed binary declaration, the database server knows precisely which support functions to use to read and write to the opaque-type column.

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:

#include <shape.h>;



EXEC SQL BEGIN DECLARE SECTION;

	 fixed binary 'shape' shape_t square1, square2;

EXEC SQL END DECLARE SECTION;

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 on page 10-6).

/* Include declaration for circle_t structure */

#include <circle.h>;



EXEC SQL BEGIN DECLARE SECTION;

	fixed binary 'circle' circle_t fbin_circle;

EXEC SQL END DECLARE SECTION;



/* Assign data to members of internal data structure */

fbin_circle.center.x = 1.00;

fbin_circle.center.y = 17.00;

fbin_circle.radius = 15.25; 



/* Insert a new circle_tab row with a fixed binary host

  *  variable */

EXEC SQL insert into circle_tab values (:fbin_circle);



/* Select a circle_tab row from into a fixed binary 

  * host variable */

EXEC SQL select circle_col into :fbin_circle

	from circle_tab

	where radius(circle_col) = 15.25;

if ((fbin_circle.center.x == 1.00) &&

		(fbin_circle.center.y == 17.00))

	printf("coordinates = (%d, %d)\n",

		fbin_circle.center.x,	fbin_circle.center.y);

To insert the data that a fixed binary host variable contains into an opaque-type column, the code fragment in Figure 10-7 on page 10-21 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 on page 10-4 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:

#include <circle.h>;



EXEC SQL BEGIN DECLARE SECTION;

	fixed binary 'circle' circle_t fbin_circle;

	int circle_ind;

EXEC SQL END DECLARE SECTION;


circle_ind = -1;

EXEC SQL insert into circle_tab 

	values (:fbin_circle:circle_ind);

For more information on indicator variables, see Using Indicator Variables.

To select the data that an opaque-type column contains into a fixed binary host variable, the code fragment in Figure 10-7 on page 10-21 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 on page 10-5) 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.

The var binary data type allows you to access the internal format of either of the following opaque data types:

• A fixed-length opaque-type column for which you do not have access to the C-structure of the internal format
• A varying-length opaque-type column

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.

To declare a var binary host variable, use the following syntax.

Figure 10-8 shows declarations for three var binary variables.

#include <shape.h>;

#include <image.h>;



EXEC SQL BEGIN DECLARE SECTION;

	var binary polygon1;

	var binary 'shape' polygon2, a_circle;

	var binary 'image' an_image;

EXEC SQL END DECLARE SECTION;

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:

• When you omit opaque type from the var binary declaration, the database server attempts to identify the appropriate support functions to use when the application receives the internal data structure from the opaque-type column in a database.

The advantage of the omission of opaque type is that you can use the var binary host variable to hold data that was selected from several different opaque types (as long as the database server is able to find the appropriate support functions).

• When you specify opaque type in the var binary declaration, the database server knows precisely which support functions to use when it sends the internal data structure to the database server for storage in the opaque-type column.

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 on page 10-24 shows.

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.

Important: The ifx_varlena_t structure is an opaque structure to ESQL/C programs. That is, you should not access its internal structure directly. Informix does not guarantee that the internal structure of ifx_varlena_t will not change in future releases. Therefore, to create portable code, always use the ESQL/C accessor functions for this structure to obtain and store values in the ifx_varlena_t structure. For a list of these ESQL/C access functions, see The lvarchar pointer and var binary Library Functions.

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.

typedef struct 

	{

	int		img_len; 

	int		img_thresh;

	int		img_flags;

	union

		{

		ifx_lop_t		img_lobhandle;

		char		img_data[4];

		}

		

	} image_t;



typedef struct

	{

	point_t			center;

	double			radius;

	} circle_t;

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.

CREATE TABLE image_tab 

(

	image_id			integer not null primary key),

	image_col			image

);

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 on page 10-26).

#include <image.h>;



EXEC SQL BEGIN DECLARE SECTION;

	var binary 'image' vbin_image;

EXEC SQL END DECLARE SECTION;



struct image_t user_image, *image_ptr;

int imgsz;



/* Load data into members of internal data structure

load_image(&user_image);

imgsz = getsize(&user_image);



/* Allocate memory for var binary data buffer */

ifx_var_flag(&vbin_image, 0);

ifx_var_alloc(&vbin_image, imgsz);



/* Assign data to data buffer of var binary host 

  *  variable */

ifx_var_setdata(&vbin_image, &user_image, imgsz); 



/* Insert a new image_tab row with a var binary host

  *  variable */

EXEC SQL insert into image_tab values (1, :vbin_image);



/* Deallocate image data buffer */

ifx_var_dealloc(&vbin_image);



/* Select an image_tab row from into a var binary 

  * host variable */

ifx_var_flag(&vbin_image, 1);

EXEC SQL select image_col into :vbin_image

	from image_tab

	where image_id = 1;

image_ptr = (image_t *)ifx_var_getdata(&vbin_image);

unload_image(&user_image);

ifx_var_dealloc(&vbin_image);

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.

To insert the data that a var binary host variable contains into an opaque-type column, the code fragment in Figure 10-11 on page 10-27 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 on page 10-26 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:

• Set an indicator variable that is associated with a var binary host variable to -1.

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 Using Indicator Variables.

• Use the ifx_var_setnull() function to set the data buffer of the var binary host variable to a null value.

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 B-263.

To select the data that an opaque-type column contains into a var binary host variable, the code fragment in Figure 10-11 on page 10-27 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:

• Check an indicator variable that is associated with a var binary host variable for a value of -1.

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 Using Indicator Variables.

• Use the ifx_var_isnull() function to check the data buffer of the var binary host variable for a null value.

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 B-258.