A Smart Large Object in the Database
To save a smart large object in the database, you must save its LO handle in a column. This section provides the following information about a smart large object that is in a database:
Valid Data Types
In the database, you can use either of the following ways to store a smart large object in a column:
The CLOB and BLOB Data Types
You can store a smart large object directly in a column that has one of the following data types:
The CLOB or BLOB column holds an LO handle for the smart large object. Therefore, if you select a CLOB or BLOB column, you do not obtain the actual data of the smart large object, but the LO handle that identifies this data. The BLOB and CLOB data types have identical internal representation. Externally, an LO handle is represented as a flat array of bytes with a length of MI_LO_SIZE.
Suppose an employee table has a BLOB column called emp_picture to hold the picture of an employee. Figure 3-3 shows that in a row of the employee table, the emp_picture column contains an LO handle. This LO handle contains information about the location of the actual employee picture in the sbspace1_100 sbspace.
The CLOB and BLOB data types are often referred to collectively as smart-large-object data types. For more information on these data types, see the Informix Guide to SQL: Reference.
The Opaque Data Type
An opaque data type is a user-defined atomic data type. You can define a field of an opaque data type to be a smart large object. The support functions of the opaque type must perform the conversion between the LO handle in the opaque type and the smart-large-object data in the sbspace. For more information, see "Managing the Reference Count".
In Figure 3-3, the emp_picture column could be an opaque data type called picture instead of a BLOB data type. The picture data type could hold the image in a smart large object in one field of its internal structure and other information about the picture in other fields.
For more information on opaque data types, see the Informix Guide to SQL: Reference and Extending INFORMIX-Universal Server: Data Types.
Access to a Smart Large Object
The DataBlade API provides the following interfaces to access the data of the smart large object:
The DataBlade API supports a subset of the Illustra large-object interface for compatibility with earlier Illustra products.
Both of these interfaces provide access to the smart large object through its LO handle, as follows:
Selecting a Smart Large Object
A SELECT statement on a CLOB, BLOB, or opaque-type column retrieves an LO handle for a smart large object. It does not retrieve the actual data for the smart large object because this data resides in an sbspace. To select a smart large object, follow these steps:
1. Use a SELECT statement to retrieve the LO handle from the CLOB, BLOB, or opaque-type column.
The LO handle identifies the location of the smart large object on disk.
2. Read the smart-large-object data from the sbspace of the smart large object.
The LO handle identifies the smart large object to open. Once you open the smart large object, you obtain an LO file descriptor, which you can use to read data from the sbspace of the smart large object.
For more information on how to select smart large objects with the Universal Server smart-large-object interface, see "Accessing an Existing Smart Large Object".
Storing a Smart Large Object
Because a smart large object can be quite large, it is not practical to store it directly in the database table. Instead, the INSERT and UPDATE statements store the LO handle of the smart large object in the CLOB, BLOB, or opaque-type column. The data of the smart large object resides in an sbspace.
To save a smart large object in a CLOB, BLOB, or opaque-type column, follow these steps:
1. For a new smart large object, ensure that the smart large object has an sbspace specified for its data.
For most smart large objects, the sbspace name is the only storage characteristic that you need to specify. The smart-large-object optimizer can calculate values for all other storage characteristics. You can set particular storage characteristics to override these calculated values. However, most applications do not need to set storage characteristics at this level of detail. For more information, see "Obtaining Storage Characteristics".
2. Create a new LO handle for the smart large object and open the smart large object.
When you create a smart large object, you obtain an LO handle and an LO file descriptor for the new smart large object.
3. Write the smart-large-object data to the sbspace of the smart large object.
Use the LO file descriptor to identify the smart large object whose data you want to write to the sbspace.
4. Use the INSERT or UPDATE statement to store the LO handle into the CLOB, BLOB, or opaque-type column.
The LO handle for the smart large object identifies the location of the smart large object on disk. Once you have written the data to the smart large object, provide its LO handle to the INSERT or UPDATE statement to save it in the database. The smart-large-object data remains in the sbspace.
Important: The sbspace for the smart large object must exist before the INSERT statement executes.
When you store an LO handle in the database, the database server can ensure that the smart large objects are only deallocated when no more database columns reference them. For more information, see "Deleting a Smart Large Object". For information on how to insert a smart large object from within a DataBlade routine, see "Creating a Smart Large Object".
|
