Informix-ESQL/C Programmer's Manual

Informix-ESQL/C Programmer's Manual
Chapter 7: Working with INFORMIX-Universal Server Smart Large

Home Contents Index Master Index New Book

Data Structures for Smart Large Objects

ESQL/C supports the SQL data types CLOB and BLOB with the ifx_lo_t data type. Because of the potentially huge size of smart-large-object data, the ESQL/C program does not store the data directly in a host variable. Instead, the data is accessed by the client application as a file-like structure. To use smart-large-object variables in an ESQL/C program, take the following actions:

Declare a host variable with the ifx_lo_t data type.

For more information, see

"Declaring a Host Variable"

Access the smart large object with a combination of the following three data structures:
- The LO-specification structure, ifx_lo_create_spec_t

For more information, see

"The LO-Specification Structure"

"Obtaining Storage Characteristics"

The LO-pointer structure, ifx_lo_t

For more information, see

"Deallocating the LO-Specification Structure"

An integer LO file descriptor

For more information, see

"Opening a Smart Large Object"

Important: The structures that ESQL/C uses to access smart large objects begin with the LO prefix. This prefix is an acronym for large object. Currently, Informix uses large object to refer to both smart large objects and simple large objects. However, use of this prefix in the ESQL/C structures that access smart large objects is retained for legacy purposes.

Declaring a Host Variable

Declare ESQL/C host variables for database columns of type CLOB or BLOB as a fixed binary host variable with the ifx_lo_t structure (called an ifx_lo_t data type) as follows:

EXEC SQL include locator;
.

.

.


EXEC SQL BEGIN DECLARE SECTION;

	fixed binary 'clob' ifx_lo_t  clob_loptr;

	fixed binary 'blob' ifx_lo_t blob_loptr;

EXEC SQL END DECLARE SECTION;
.

.

.


EXEC SQL select blobcol into :blob_loptr from tab1;

The ifx_lo_t data type stores an LO-pointer structure. For more information, see "The ifx_lo_t Structure".

Tip: For more information on the fixed binary ESQL/C data type, see "Accessing a Fixed-Length Opaque Type".

To access smart large objects, you must include the locator.h header file in your ESQL/C program. This header file contains definitions of data structures and constants that your program needs to work with smart large objects.

(1 of 2)
ESQL/C Library Function	Purpose	For More Information
ifx_lo_col_info()	Updates the `LO`-specification structure with the column-level storage characteristics	page 7-39
ifx_lo_create()	Reads an `LO`-specification structure to obtain storage characteristics for a new smart large object that it creates	page 7-46
ifx_lo_def_create_spec()	Allocates and initializes an `LO`-specification structure	page 7-49
ifx_lo_spec_free()	Frees the resources of the `LO-`specification structure	page 7-63
ifx_lo_specget_estbytes()	Gets the estimated number of bytes from the `LO`-specification structure	page 7-64
ifx_lo_specget_extsz()	Gets the allocation extent size from the `LO`-specification structure	page 7-66
ifx_lo_specget_flags()	Gets the create-time flags from the `LO`-specification structure	page 7-67
ifx_lo_specget_maxbytes()	Gets the maximum number of bytes from the `LO`-specification structure	page 7-69
ifx_lo_specget_sbspace()	Gets the name of the sbspace from the `LO`-specification structure	page 7-71
ifx_lo_specset_estbytes()	Sets the estimated number of bytes from the `LO`-specification structure	page 7-73
ifx_lo_specset_extsz()	Sets the allocation extent size in the `LO`-specification structure	page 7-75
ifx_lo_specset_flags()	Sets the create-time flags in the `LO`-specification structure	page 7-77
ifx_lo_specset_maxbytes()	Sets the maximum number of bytes in the `LO`-specification structure	page 7-79
ifx_lo_specset_sbspace()	Sets the name of the sbspace in the `LO`-specification structure	page 7-80
ifx_lo_stat_cspec()	Returns the storage characteristics into the `LO`-specification structure for a specified smart large object	page 7-84

Obtaining Storage Characteristics

Once you have allocated an LO-specification structure with the ifx_lo_def_create_spec() function, you must ensure that this structure contains the appropriate storage characteristics when you create a smart large object.

Universal Server uses an inheritance hierarchy to obtain storage characteristics. Figure 7-3 shows the inheritance hierarchy for smart-large-object storage characteristics.

Figure 7-3
Inheritance Hierarchy for Storage Characteristics

The System-Specified Storage Characteristics

Universal Server uses one of the following sets of storage characteristics as the system-specified storage characteristics:

If the sbspace in which the smart large object is stored has specified a value for a particular storage characteristic, the database server uses the sbspace value as the system-specified storage characteristic.

DBA

onspaces

If the sbspace in which the smart large object is stored has not specified a value for a particular storage characteristic, the database server uses the system default as the system-specified storage characteristic.

SBSPACENAME

ONCONFIG

Informix recommends that you use the values for the system-specified disk-storage information. Most applications do not need to change these system-specified storage characteristics. For more information on database server and sbspace storage characteristics, see the description of the onspaces utility in the Administrator's Guide.

To use the system-specified storage characteristics for a new smart large object, follow these steps:

1. Use the ifx_lo_def_create_spec() function to allocate an LO-specification structure and to initialize this structure to null values.

2. Pass this LO-specification structure to the ifx_lo_create() function to create the instance of the smart large object.

The ifx_lo_create() function creates a smart-large-object instance with the storage characteristics in the LO-specification structure that it receives as an argument. Because the previous call to ifx_lo_def_create_spec() stored null values in this structure, the database server assigns the system-specified characteristics to the new instance of the smart large object.

The Column-Level Storage Characteristics

The database administrator (DBA) assigns column-level storage characteristics with the CREATE TABLE statement. The PUT clause of CREATE TABLE specifies storage characteristics for a particular smart-large-object (CLOB or BLOB) column. (For more information, see the description of the CREATE TABLE statement in the Informix Guide to SQL: Syntax.) The syscolattribs system catalog table stores column-level storage characteristics.

The ifx_lo_col_info() function obtains column-level storage characteristics for a smart-large-object column. To use the column-level storage characteristics for a new smart-large-object instance, follow these steps:

1. Use the ifx_lo_def_create_spec() function to allocate an LO-specification structure and initialize this structure to null values.

2. Pass this LO-specification structure to the ifx_lo_col_info() function and specify the desired column and table name as arguments.

3. Pass this same LO-specification structure to the ifx_lo_create() function to create the instance of the smart large object.

When the ifx_lo_create() function receives the LO-specification structure as an argument, this structure contains the column-level storage characteristics that the previous call to ifx_lo_col_info() stored. Therefore, the database server assigns these column-level characteristics to the new instance of the smart large object.

When you use the column-level storage characteristics, you do not usually need to provide the name of the sbspace for the smart large object. The sbspace name is specified in the PUT clause of the CREATE TABLE statement or by the SBSPACENAME parameter in the ONCONFIG file.

The User-Defined Storage Characteristics

An ESQL/C application program can define a unique set of storage characteristics for a new smart large object, as follows:

For smart large objects that are to be stored in a column, you can override some storage characteristics for the column when it creates an instance of a smart large object.

If the application does not override some or all of these characteristics, the smart large object uses the column-level storage characteristics. For information on how to obtain column-level storage characteristics, see

"The Column-Level Storage Characteristics"

You can specify a wider set of characteristics for each smart large object since the smart large object is not constrained by table column properties.

If the application programmer does not override some or all of these characteristics, the smart large object inherits the system-specified storage characteristics. For more information, see

"The System-Specified Storage Characteristics"

To specify user-defined storage characteristics, use the appropriate ESQL/C accessor functions for the LO-specification structure. For more information on these accessor functions, see "The LO-Specification Structure".

Deallocating the LO-Specification Structure

Once you are finished with an LO-specification structure, deallocate the resources assigned to it with the ifx_lo_spec_free() function. When the resources are freed, they can be reallocated to other structures that your program needs. For more information on the ifx_lo_spec_free() function, see page 7-63.

The LO-Pointer Structure

To open a smart large object for read and write operations, an ESQL/C program must have an LO-pointer structure for the smart large object. This structure contains the disk address and a unique hexadecimal identifier for a smart large object. For more information about the unique identifier, see the description of the ifx_lo_copy_to_file() function on page 7-41.

To create an LO-pointer structure for a new smart large object, use the ifx_lo_create() function. The ifx_lo_create() function performs the following tasks:

1. It initializes an LO-pointer structure, whose pointer you provide as an argument, for the new smart large object.

This new smart large object has the storage characteristics that the LO-specification structure you provide specifies. For more information on the LO-specification structure, see

page 7-8

2. It opens the new smart large object in the specified access mode and returns an LO file descriptor that is needed for subsequent operations on the smart large object.

For more information on an LO file descriptor, see

page 7-27

You must call ifx_lo_def_create_spec() before you call the ifx_lo_create() function to create a new smart large object. For more information on the ifx_lo_def_create_spec() function, see page 7-49.

Storing a Smart Large Object

An ESQL/C program accesses a smart large object through an LO-pointer structure. The ESQL/C library functions in the table on page 7-19 accept an LO-pointer structure as an argument. Through the LO-pointer structure, these functions allow you to create and manipulate a smart large object without binding it to a database row.

An INSERT or UPDATE statement does not perform the actual input of the smart-large-object data. It does, however, provide a means for the application program to identify which smart-large-object data to associate with the column. A CLOB or BLOB column in a database table stores the LO-pointer structure for a smart large object. Therefore, when you store a CLOB or BLOB column, you provide an LO-pointer structure for the column in an ifx_lo_t host variable to the INSERT or UPDATE statement. For this reason, you declare host variables for CLOB and BLOB values as LO-pointer structures. (For more information, see "Declaring a Host Variable".)

Figure 7-4 shows how the ESQL/C client application transfers the data of a smart large object to the database server.

Figure 7-4
Transferring Smart- Large-Object Data from Client Application to Database Server

The smart large object that an LO-pointer structure identifies exists as long as its LO-pointer structure exists. When you store an LO-pointer structure in the database, the database server can ensure that the smart large objects are deallocated when appropriate.

When you retrieve a row and then update a smart large object which that row contains, the database server exclusively locks the row for the time that it updates the smart large object. Moreover, long updates for smart large objects (whether or not logging is enabled and whether or not they are associated with a table row) create the potential for a long transaction condition if the smart large object takes a long time to update or create.

For a sample of code that stores a new smart large object into a database column, see "The create_clob Program". For information on how to select a smart large object from the database, see page 7-22.

The ifx_lo_t Structure

The LO-pointer structure, ifx_lo_t, serves as a reference to a smart large object. It provides security-related information and holds information about the actual disk location of the smart large object. The locator.h header file defines the LO-pointer structure so you must include the locator.h file in your ESQL/C programs that access this structure.

Important: The LO-pointer structure, ifx_lo_t, 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_lo_t will not change. Therefore, to create portable code, use the appropriate ESQL/C library function (see page 7-19) to use this structure.

The LO-pointer structure, not the CLOB or BLOB data itself, is stored in a CLOB or BLOB column in the database. Therefore, SQL statements such as INSERT and SELECT accept an LO-pointer structure as the column value for a smart-large-object column. You declare an ESQL/C host variable to hold the value of a smart large object as an ifx_lo_t structure. For more information about how to declare a host variable for a smart large object, see page 7-7.

ESQL/C Functions That Use the LO-Pointer Structure

The following table shows the ESQL/C library functions that access the LO-pointer structure and how they access it.


ESQL/C Library Function	Purpose	For More Information
ifx_lo_copy_to_file()	Copies the smart large object that the `LO`-pointer structure identifies to an operating-system file.	page 7-41
ifx_lo_create()	Initializes an `LO`-pointer structure for a new smart large object that it creates and returns an `LO` file descriptor for this smart large object.	page 7-46
ifx_lo_filename()	Returns the name of the file where the ifx_lo_copy_to_file() function would store the smart large object that the `LO`-pointer structure identifies.	page 7-50
ifx_lo_from_buffer()	Copies a specified number of bytes from a user-defined buffer into the smart large object that the LO_pointer structure references.	page 7-52
ifx_lo_release()	Tells the database server to release the resources associated with the temporary smart large object that the LO-pointer structure references.	page 7-59
ifx_lo_to_buffer()	Copies a specified number of bytes from the smart large object referenced by the LO_pointer structure into a user-defined buffer.	page 7-93

The LO File Descriptor

The LO file descriptor is an integer value that identifies an open smart large object. An LO file descriptor is very similar to the file descriptors for operating-system files. It serves as an I/O handle to the data of the smart large object in the server. LO file descriptors start with a seek position of zero (0). Use the LO file descriptor in one of the ESQL/C library functions that accepts LO file descriptors.

ESQL/C Library Functions That Use an LO File Descriptor

The following table shows the ESQL/C library functions that access the LO file descriptor.

(1 of 2)
ESQL/C Library Function	Purpose	For More Information
ifx_lo_close()	Closes the smart large object that the `LO` file descriptor identifies and deallocates the `LO` file descriptor	page 7-38
ifx_lo_copy_to_lo()	Copies an operating-system file to an open smart large object that the `LO` file descriptor identifies	page 7-44
ifx_lo_create()	Creates and opens a new smart large object and returns an `LO` file descriptor	page 7-46
ifx_lo_open()	Opens a smart large object and returns an `LO` file descriptor	page 7-53
ifx_lo_read()	Reads data from the open smart large object that the `LO` file descriptor identifies	page 7-55
ifx_lo_readwithseek()	Seeks a specified file position in the open smart large object that the `LO` file descriptor identifies and then reads data from this position	page 7-57
ifx_lo_seek()	Moves the file position in the open smart large object that the `LO` file descriptor identifies	page 7-61
ifx_lo_stat()	Obtains status information for the open smart large object that the `LO` file descriptor identifies	page 7-81
ifx_lo_tell()	Determines the current file position in the open smart large object that the `LO` file descriptor identifies	page 7-92
ifx_lo_truncate()	Truncates at a specified offset the open smart large object that the `LO` file descriptor identifies	page 7-95
ifx_lo_write()	Writes data to the open smart large object that the `LO` file descriptor identifies	page 7-96
ifx_lo_writewithseek()	Seeks a specified file position in the open smart large object that the `LO` file descriptor identifies and then writes data to this position	page 7-98

Informix-ESQL/C Programmer's ManualChapter 7: Working with INFORMIX-Universal Server Smart Large Home Contents Index Master Index New Book

Data Structures for Smart Large Objects

Disk-Storage Information

Informix-ESQL/C Programmer's Manual
Chapter 7: Working with INFORMIX-Universal Server Smart Large

Home Contents Index Master Index New Book