DataBlade API Programmer's Manual

DataBlade API Programmer's Manual
Chapter 3: Handling Smart Large Objects

Home Contents Index Master Index New Book

Creating a Smart Large Object

To create a smart large object and save its LO handle in the database, take the following steps.


Step	Task	Smart-Large-Object Function	For More Information
1.	Obtain an `LO`-specification structure to hold the storage characteristics for the new smart large object.	mi_lo_spec_init(), mi_lo_stat_cspec()	page 3-33
2.	Ensure that the `LO`-specification structure contains the desired storage characteristics for the new smart large object.	System-specified storage characteristics: mi_lo_spec_init() Column-level storage characteristics: mi_lo_colinfo_by_name(), mi_lo_colinfo_by_ids() User-specified storage characteristics: Figure 3-13, Figure 3-14	page 3-37
3.	Create an `LO` handle for the new smart large object and open the smart large object.	mi_lo_create(), mi_lo_expand(), mi_lo_copy(), mi_lo_from_file()	page 3-50
4.	Write a specified number of bytes from a user-defined buffer to the open smart large object.	mi_lo_write(), mi_lo_writewithseek()	page 3-53
5.	Pass the `LO` handle as the column value for an `INSERT` or `UPDATE` statement.	C Casting	page 3-55
6.	Execute an `INSERT` or `UPDATE` statement to save the `LO` handle of the smart large object in a database column.	mi_exec(), mi_exec_prepared_statement(), mi_value()	page 3-55
7.	Close the smart large object.	mi_lo_close()	page 3-70
8.	Deallocate resources.	mi_lo_spec_free(), mi_lo_release()	page 3-55

Figure 3-7 shows the first six of these steps that a DataBlade routine uses to insert the smart-large-object data into the emp_picture column of the employee table (Figure 3-3).

Figure 3-7
Inserting Into a BLOB Column

#include "milo.h"

...

MI_LO_SPEC *myspec;

MI_CONNECTION *conn;

...
/* allocate a new LO-specification structure */

myspec = NULL;

if ( mi_lo_spec_init(conn, &myspec) != MI_OK )

	handle_error();
/* perform tasks with LO-specification structure */

...

/* once finished with LO-specification structure, free it */
if ( mi_lo_spec_free(conn, myspec)!= MI_OK )

	handle_error();

After the execution of mi_lo_spec_init(), the myspec variable points to the newly allocated LO-specification structure. For more information on how to use an LO-specification structure to create a new smart large object, see "Choosing Storage Characteristics".

If you provide a second argument that does not point to NULL, the mi_lo_spec_init() function assumes that this pointer references an existing LO-specification structure that a previous call to mi_lo_spec_init() has allocated. An LO-specification pointer that is not NULL allows a DataBlade routine to reuse an LO-specification structure. The following code fragment reuses the LO-specification structure that the LO_spec pointer references when the first_time flag is false:

#include "milo.h"

...
MI_CONNECTION *conn;

MI_LO_SPEC *LO_spec = NULL;

mi_integer first_time;
...
if (first_time)

	{
	...
	LO_spec = NULL; /* tell interface to allocate memory */
	first_time = 0; /* set "first_time" flag to false */
	...

	}
if ( mi_lo_spec_init(conn, &LO_spec) != MI_OK )

	{

	/* error */

	}

Important: Before you use an LO-specification structure, make sure that you either call mi_lo_spec_init() with the LO-specification pointer set to NULL, or that you have initialized this pointer with a previous call to mi_lo_spec_init().

Once you have a valid LO-specification structure, you can use the accessor functions to obtain the storage characteristics from this LO-specification structure. For more information, see "Defining User-Specified Storage Characteristics". For the syntax of the mi_lo_spec_init() function, see its description in Chapter 12, "DataBlade API Function Descriptions."

Initializing an LO-Specification Structure

The mi_lo_spec_init() function initializes the LO-specification structure with values that obtain the system-specified storage characteristics. The system-specified storage characteristics are the defaults that the database server uses. They are the storage characteristics at the bottom of the storage-
characteristics hierarchy.

After this initialization, you can change the values in the LO-specification structure so that:

the new smart large object inherits column-level storage characteristics of a CLOB or BLOB column.
you provide custom user-specified storage characteristics for the new smart large object.

For more information on storage characteristics and the storage-
characteristics hierarchy, see "Choosing Storage Characteristics".

Storage Characteristics from an Existing Smart Large Object

The mi_lo_stat_cspec() function copies the storage characteristics from an existing smart large object to an LO-specification structure. This function performs the following tasks:

1. It allocates a new LO-specification structure to hold the storage characteristics.

2. It initializes all fields of this LO-specification structure (disk-storage information and attributes flag) to the values of the storage characteristics of the smart large object whose status information is in the LO-status structure that you pass as an argument.

3. It returns the address of the newly allocated LO-specification structure.

The LO-status structure holds status information for an existing smart large object. You initialize an LO-status structure with the mi_lo_stat() function. For more information on an LO-status structure, see "Obtaining the Status of a Smart Large Object".

The following code fragment assumes that the old_LOfd variable was already initialized as the LO file descriptor of an existing smart large object. This code fragment uses the storage characteristics of the existing smart large object (which the mi_lo_stat() function puts into the LO-specification structure that LO_spec specifies) as the storage characteristics for the new smart large object (which the mi_lo_create() function creates).

MI_LO_HANDLE *LO_hdl = NULL;

MI_LO_STAT *LO_stat = NULL;

MI_LO_SPEC *LO_spec;

MI_LO_FD new_LOfd, old_LOfd;
...
if ( mi_lo_stat(conn, old_LOfd, &LO_spec) != MI_OK )

	{

	/* handle error and exit */

	}
LO_spec = mi_lo_stat_cspec(LO_stat);

new_LOfd = mi_lo_create(conn, LO_spec, flags, &LO_hdl);

Choosing Storage Characteristics

Once you initialize an LO-specification structure, you must ensure that this structure contains the desired storage characteristics. You want the LO-
specification structure to contain the appropriate values for the storage characteristics you want the new smart large object to have. You then pass this LO-specification structure to one of the smart-large-object creation functions (Figure 3-5) so that the smart-large-object optimizer can obtain the storage characteristics to use for the new smart large object.

You can choose the following storage characteristics for a new smart large object:

Use the system-specified storage characteristics as a basis for obtaining the storage characteristics of a smart large object.

system-specified storage characteristics

Customize the storage characteristics.

You can override the system-specified storage characteristics with one of the following levels of the storage-characteristics hierarchy:

Storage characteristics defined for a particular CLOB or BLOB column in which you want to store the smart large object

CLOB

BLOB

column-level storage characteristics

User-specified storage characteristics

user-specified storage characteristics

Important: For most applications, Informix recommends that you use the system-specified values for the disk-storage information. Most DataBlade routines only need to ensure correct storage characteristics for an sbspace name (the location of the smart large object) and for the smart-large-object attributes.

Obtaining Storage Characteristics

For more smart large objects, you can use the following steps to obtain storage characteristics:

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

When a storage characteristic in the LO-specification structure has a null value, the smart-large-object optimizer uses the system-specified value for the storage characteristic. The smart-large-object optimizer calculates the system-specified values for disk-storage storage characteristics. Most applications can use these system-specified values. For more information, see

"Using System-Specified Storage Characteristics"

2. Specify the location of the smart large object to override the default location. You can specify the location in one of the following ways:

Specify the name of the sbspace associated with the CLOB or BLOB column in which you want to store the smart large object.

To store a new smart large object in a CLOB or BLOB column, use the mi_lo_colinfo_by_name() or mi_lo_colinfo_by_ids() function. These functions obtain the column-level storage characteristics for this column. One of the storage characteristics they obtain is the sbspace name for the column. For more information, see

"Obtaining Column-Level Storage Characteristics"

Specify the name of some other sbspace.

You might want to specify an sbspace name for a new smart large object that is embedded in an opaque data type. The mi_lo_specset_sbspace() accessor function sets the name of the sbspace in the LO-specification structure. For more information, see

"Defining User-Specified Storage Characteristics"

3. Optionally, override any attributes for the smart large object with the mi_lo_specset_flags() accessor function.

The system-specified attributes have both logging and last-access time disabled. You might want to enable one or more attributes for the new smart large object. The mi_lo_specset_flags() function sets the attributes flag in the LO-specification structure. For more information, see

"Defining User-Specified Storage Characteristics"

4. Pass this LO-specification structure to one of the smart-large-object creation functions (mi_lo_create(), mi_lo_copy(), mi_lo_expand(), or mi_lo_from_file()) to create the new smart large object.

The smart-large-object creation function creates a new smart large object that has storage characteristics that the LO-specification structure indicates. For more information, see

"Initializing an LO Handle and LO File Descriptor"

You can modify the storage characteristics that these steps obtain if:

your application needs to obtain extra performance.

You can use other LO-specification accessor functions to change the disk-storage information of a new smart large object. For more information, see

"Defining User-Specified Storage Characteristics"

you want to use the storage characteristics of an existing smart large object.

The mi_lo_stat_cspec() can obtain the storage characteristics of an open smart large object from its LO-status structure. For more information, see

"Storage Characteristics from an Existing Smart Large Object"

Using the Storage-Characteristics Hierarchy

Universal Server uses the storage-characteristics hierarchy, which Figure 3-8 shows, to obtain the storage characteristics for a new smart large object.


	Figure 3-8 Storage-Characteristics Hierarchy

For a given storage characteristic, any value defined at the column level overrides the system-specified value, and any user-level value overrides the column-level value. Figure 3-9 summarizes the ways to specify disk-storage information for a smart large object.

Figure 3-9
Specifying Disk-Storage Information

Disk-Storage Information System-Specified Storage Characteristics Column-Level Storage Characteristics User-Specified Storage Characteristics
System Default Value Specified by onspaces Utility Specified by
PUT clause of CREATE TABLE Specified by a DataBlade API Function
Size of extent
Calculated by smart-large-object optimizer
EXTENT_SIZE
EXTENT SIZE
Yes

Size of next extent
Calculated by smart-large-object optimizer
NEXT_SIZE
No
No

Minimum extent size
4 kilobytes
MIN_EXT_SIZE
No
No

Size of smart large object
Calculated by smart-large-object optimizer
Average size of all smart large objects in sbspace:
AVG_LO_SIZE
No
Estimated size of a particular smart large object
Maximum size of a particular smart large object

Maximum size of I/O block
Calculated by smart-large-object optimizer
MAX_IO_SIZE
No
No

Name of sbspace
SBSPACENAME
-S option
Name of an existing sbspace specified in the IN clause
Yes

For more information, see "Disk-Storage Information".

Figure 3-10 summarizes the ways to specify attribute information for a smart large object.

Figure 3-10
Specifying Attribute Information

Attribute Information System-Specified Storage Characteristics Column-Level Storage Characteristics User-Specified Storage Characteristics
System Default Value Specified by onspaces Utility Specified by PUT clause of CREATE TABLE Specified by a DataBlade API Function
Logging
OFF
LOGGING
LOG, NO LOG
Yes

Last-access time
OFF
ACCESSTIME
KEEP ACCESS TIME,
NO KEEP ACCESS TIME
Yes

For more information on any of the attributes in Figure 3-10, see "Attribute Information".

Using System-Specified Storage Characteristics

The Database Administrator (DBA) establishes system-specified storage characteristics when he or she initializes the database server and creates an sbspace with the onspaces utility, as follows:

If the onspaces utility specifies a value for a particular storage characteristic, the smart-large-object optimizer uses the onspaces value as the system-specified storage characteristic.
If the onspaces utility does not specify a value for a particular storage characteristic, the smart-large-object optimizer uses the system default as the system-specified storage characteristic.

The system-specified storage characteristics apply to all smart large objects that are stored in the sbspace, unless a smart large object specifically overrides them with column-level or user-specified storage characteristics.

The onspaces utility establishes storage characteristics for a sbspace. For the storage characteristics that onspaces can set as well as the system defaults, see Figure 3-9 and Figure 3-10. For example, the following call to the onspaces utility creates an sbspace called sb1 in the /dev/sbspace1 partition:

onspaces -c -S sb1 -g 5 -p /dev/sbspace1 -o 500 -s 2000

	-Df "AVG_LO_SIZE=32"

Figure 3-11 shows the system-specified storage characteristics for all smart large objects in the sb1 sbspace.

Figure 3-11
System-Specified Storage Characteristics for the sb1 Sbspace

Storage Characteristic System-Specified Value Specified by onspaces Utility
Disk-storage information:

Size of extent
Calculated by smart-large-object optimizer
system default

Size of next extent
Calculated by smart-large-object optimizer
system default

Minimum extent size
Calculated by smart-large-object optimizer
system default

Size of smart large object
32 kilobytes
(smart-large-object optimizer uses as size estimate)
AVG_LO_SIZE

Maximum size of I/O block
Calculated by smart-large-object optimizer
system default

Name of sbspace
sb1
-S option

Attribute information:

Logging
OFF
system default

Last-access time
OFF
system default

For a smart large object that has system-specified storage characteristics, the smart-large-object optimizer calculates values for all disk-storage information except the sbspace name. The DBA can specify a default sbspace name with the SBSPACENAME configuration parameter in the ONCONFIG file. However, you must ensure that the location (the name of the sbspace) is correct for the smart large object that you create. If you do not specify an sbspace name for a new smart large object, the database server stores it in this default sbspace. This arrangement can quickly lead to space constraints.

Important: For new smart large objects, Informix recommends that you use the system-specified values of all disk-storage information except the sbspace name. The smart-large-object optimizer can best determine most of the values of the storage characteristics. Most applications only need to specify an sbspace name for their disk-storage information.

Obtaining Column-Level Storage Characteristics

The DBA can establish column-level storage characteristics when he or she creates a database table with the CREATE TABLE statement. If the table contains a CLOB or BLOB column, the PUT clause of CREATE TABLE can specify the storage characteristics that Figure 3-9 and Figure 3-10 show. This statement stores column-level storage characteristics in the syscolattribs system catalog table.

The column-level storage characteristics apply to all smart large objects whose LO handles are stored in the column, unless a smart large object specifically overrides them with user-specified storage characteristics. Column-level storage characteristics override any corresponding system-specified storage characteristics.

For example, if the sb1 sbspace is defined as Figure 3-11 shows, the following CREATE TABLE statement specifies column-level storage characteristics of a location and last-access time for the cat_descr column:

CREATE TABLE catalog2

(

          catalog_num INTEGER,

          cat_descr CLOB

) PUT cat_descr IN (sb1) (KEEP ACCESS TIME)

Figure 3-12 shows the storage characteristics for all smart large objects in the cat_descr column.

Figure 3-12
Storage Characteristics for the cat_descr Column

Storage Characteristic Column-Level Value Specified by PUT Clause of CREATE TABLE
Disk-storage information:

Size of extent
Calculated by smart-large-object optimizer
system-specified

Size of next extent
Calculated by smart-large-object optimizer
system-specified

Minimum extent size
Calculated by smart-large-object optimizer
system-specified

Size of smart large object
32 kilobytes
(smart-large-object optimizer uses as size estimate)
system-specified

Maximum size of I/O block
Calculated by smart-large-object optimizer
system-specified

Name of sbspace
sb1
IN (sb1)

Attribute information:

Logging
OFF
system-specified

Last-access time
ON
KEEP LAST ACCESS

For more information on the syntax of the CREATE TABLE statement, see its description in the Informix Guide to SQL: Syntax.

The following DataBlade API functions obtain column-level storage characteristics for a specified CLOB or BLOB column:

The mi_lo_colinfo_by_name() function allows you to identify the column by the table and column name.
The mi_lo_colinfo_by_ids() function allows you to identify the column by an MI_ROW structure and the relative column identifier.

Both these functions store the column-level storage characteristics for the specified column in an existing LO-specification structure. When a smart-large-object creation function receives this LO-specification structure, it creates a new smart-large-object instance that has these column-level storage characteristics.

Tip: When you use the column-level storage characteristics, you usually do not need to override the name of the sbspace for the smart large object. The sbspace name is specified in the PUT clause of the CREATE TABLE statement.

For example, the following code fragment obtains the column-level storage characteristics for the emp_picture column of the employee table (Figure 3-3) and puts them in the LO-specification structure that LO_spec references:

MI_LO_SPEC *LO_spec = NULL;
MI_CONNECTION *conn;

...
mi_lo_spec_init(conn, &LO_spec);
mi_lo_colinfo_by_name(conn, "employee.emp_picture",

	LO_spec);

The call to mi_lo_colinfo_by_name() overwrites the system-specified storage characteristics that the call to mi_lo_spec_init() put in the LO-
specification structure. The LO-specification structure that LO_spec references now contains the column-level storage characteristics for the emp_picture column.

Defining User-Specified Storage Characteristics

You can establish user-specified storage characteristics when you create a new smart large object. DataBlade API functions can specify the storage characteristics that Figure 3-9 and Figure 3-10 show. The user-specified storage characteristics apply only to the particular smart-large-object instance that is being created. They override any corresponding column-level or system-specified storage characteristics.

Once you have an allocated LO-specification structure, you can use the appropriate LO-specification accessor functions to set fields of this structure. When a smart-large-object creation function receives this LO-specification structure, it creates a new smart-large-object instance that has these user-specified storage characteristics.

Important: The LO-specification structure, MI_LO_SPEC, is an opaque structure to DataBlade routines. Do not access its internal structure directly. Informix does not guarantee that the internal structure of MI_LO_SPEC will not change in future releases. Therefore, to create portable code, always use the LO-specification accessor functions to obtain and store values in this structure.

The following sections describe how to access each group of storage characteristics in the LO-specification structure.

How to Access Disk-Storage Information

Figure 3-13 shows the disk-storage information along with the corresponding LO-specification accessor functions.

Figure 3-13
Disk-Storage Information in the LO-Specification Structure

(1 of 2)

Disk-Storage Information Description LO-Specification
Accessor Function
Estimated number
of bytes
An estimate of the final size, in bytes, of the smart large object. The smart-large-object optimizer uses this value to determine the extents in which to store the smart large object. This value provides optimization information. If the value is grossly incorrect, it does not cause incorrect behavior. However, it does mean that the optimizer might not necessarily choose optimal extent sizes for the smart large object.
By default, this value is -1, which tells the smart-large-object optimizer to obtain the extent size from the storage-characteristics hierarchy.
mi_lo_specget_estbytes(),
mi_lo_specset_estbytes()

Maximum number
of bytes
The maximum size, in bytes, for the smart large object. The smart-large-object optimizer does not allow the smart large object to grow beyond this size.
By default, this value is -1, which tells the smart-large-object optimizer that there is no preset maximum size.
mi_lo_specget_maxbytes(),
mi_lo_specset_maxbytes()

Allocation extent size
The allocation extent size is specified in kilobytes. It is the size of the page extents for the smart large object.
By default, this value is -1, which tells the smart-large-object optimizer to obtain the allocation extent size from the storage-characteristics hierarchy.
mi_lo_specget_extsz(),
mi_lo_specset_extsz()

Name of the sbspace
The name of the sbspace that contains the smart large object. The sbspace name can be at most 18 characters long and must be null terminated.
By default, this value is null, which tells the smart-large-object optimizer to obtain the sbspace name from the storage-characteristics hierarchy.
mi_lo_specget_sbspace(),
mi_lo_specset_sbspace()

For most applications, Informix recommends that you use the values for the disk-storage information that the smart-large-object optimizer determines. For more information, see "Disk-Storage Information". You can also refer to the description of the appropriate LO-specification accessor function in Chapter 12, "DataBlade API Function Descriptions."

How to Access Attributes

The LO-specification structure uses a bitmask flag, called an attributes flag, to specify the attributes of a smart large object. Figure 3-14 shows the attribute constants of an LO-specification structure.

Figure 3-14
Attribute Constants in the LO-Specification Structure

Attribute Attribute Constant Description
Logging indicators:
MI_LO_ATTR_LOG
Log changes to the smart large object in the system log file.

MI_LO_ATTR_NO_LOG
Turn off logging for all operations that involve the associated smart large object.

Consider carefully whether to use the MI_LO_ATTR_LOG flag value. The database server incurs considerable overhead to log smart large objects. For more information, see "Logging".

Last-access-time indicators:
MI_LO_ATTR_KEEP_LASTACCESS_TIME
Save the last access time for the smart large object.

MI_LO_ATTR_NOKEEP_LASTACCESS_TIME
Do not maintain the last-access time for the smart large object.

Consider carefully whether to use the MI_LO_ATTR_KEEP_LASTACCESS_TIME flag value. The database server incurs considerable overhead in logging and concurrency to maintain last-access times for smart large objects. For more information, see "Last-Access Time".

Use the mi_lo_specset_flags() function to override system-specified or column-level attributes in the LO-specification structure and the mi_lo_specget_flags() function to retrieve the attributes flag from the LO-specification structure.

The milo.h header file defines the attribute constants: MI_LO_ATTR_LOG, MI_LO_ATTR_NO_LOG, MI_LO_ATTR_KEEP_LASTACCESS_TIME, and MI_LO_ATTR_NOKEEP_LASTACCESS_TIME.

To set an attributes flag

1. If you need to set more than one attribute, use the C-language bitwise OR operator (|) to mask attribute constants together.

2. Use the mi_lo_specset_flags() accessor function to store the attributes flag in the LO-specification structure.

Masking mutually exclusive flags results in an error. If you do not specify a value for a particular attribute, the database server uses the storage-
characteristics hierarchy to determine this information.

For example, the following code fragment specifies the constants to enable logging the last-access time for the attributes flag in the LO-specification structure that LO_spec identifies:

#include "milo.h"

...

MI_CONNECTION *conn;

MI_LO_SPEC *LO_spec = NULL;

mi_integer create_flgs;

...


if ( mi_lo_spec_init(conn, &LO_spec) != MI_OK )

	/* handle error and exit */

create_flgs = MI_LO_ATTR_LOG | MI_LO_ATTR_KEEP_LASTACCESS_TIME;

if ( mi_lo_specset_flags(LO_spec, create_flgs) != MI_OK )

	/* handle error and exit */

For more information on the attributes of a smart large object, see "Attribute Information". You can also refer to descriptions of the mi_lo_specset_flags() and mi_lo_specget_flags() functions in Chapter 12, "DataBlade API Function Descriptions."

Initializing an LO Handle and LO File Descriptor

Once you have an LO-specification structure that describes the storage characteristics for the new smart large object, you can create the smart large object with one of the smart-large-object creation functions: mi_lo_copy(), mi_lo_create(), mi_lo_expand(), or mi_lo_from_file(). These smart-large-object creation functions perform the following tasks to create a new smart large object:

1. Initialize the LO handle for the new smart large object.

2. Store the storage characteristics in a user-supplied LO-specification structure for the new smart large object in the metadata area of the sbspace.

You provide a pointer to an LO-specification structure as an argument to these functions. For more information, see

"Obtaining the LO-Specification Structure"

3. Open the new smart large object in the specified access mode.

You provide the open mode as an argument to the mi_lo_create(), mi_lo_copy(), and mi_lo_expand() functions. The mi_lo_from_file() function opens a smart large object in read/write mode. For more information, see

"Opening a Smart Large Object"

4. Write any associated data to the new smart large object.

mi_lo_copy()

mi_lo_expand()

mi_lo_from_file()

5. Return an LO file descriptor that identifies the open smart large object.

These smart-large-object creation functions initialize the following data structures for a smart large object:

An LO handle identifies the location of the smart large object and can be stored in a CLOB, BLOB, or opaque-type column.
An LO file descriptor identifies the open smart large object.

Obtaining an LO Handle

A DataBlade routine can allocate an LO handle with any of the following methods:

Any of the smart-large-object creation functions can allocate an LO handle.
A DataBlade routine can explicitly allocate an LO handle.
A SELECT statement can return an LO handle from a CLOB or BLOB column in the database.

For more information, see

"Selecting the LO Handle"

Implicitly Allocating an LO Handle

Any of the smart-large-object creation functions (Figure 3-5) can allocate memory for an LO handle when you specify a pointer to NULL for the last argument. For example, the code fragment declares a pointer to an LO handle, called LO_hdl, initializes it to NULL, and then calls the mi_lo_create() function to allocate memory for this LO handle:

#include "milo.h"
...
MI_CONNECTION *conn;

MI_LO_SPEC *LO_spec;

MI_LO_HANDLE *LO_hdl = NULL; /* request allocation */

MI_LO_FD LO_fd;
mi_integer flags;
...
LO_fd = mi_lo_create(conn, &LO_spec, flags, &LO_hdl);

After the execution of mi_lo_create(), the LO_hdl variable is a pointer to the new LO handle, which identifies the location of the new smart large object. This new LO handle has a default memory duration of PER_ROUTINE. If you switch the memory duration, the creation function uses the current memory duration for the LO handle that it allocates.

If you provide an LO-handle pointer that does not point to NULL, the smart-large-object creation function assumes that memory has already been allocated for the LO handle and it uses the LO handle that you provide. An existing LO handle has a memory duration of the original allocation.

Explicitly Allocating an LO Handle

You can explicitly allocate an LO handle in either of the following two ways:

Dynamically, with one of the DataBlade memory-management functions such as mi_alloc(): MI_LO_HANDLE *LO_hdl = mi_alloc(sizeof(MI_LO_HANDLE));
On the stack, with a direct declaration: MI_LO_HANDLE my_LOhndl;
MI_LO_HANDLE *LO_hdl2 &my_LOhndl;

However, this LO handle is still opaque; that is, it is declared as a flat array of undifferentiated bytes and its fields are not available to the DataBlade routine.

Important: The LO handle structure is the only smart-large-object structure that a DataBlade routine can allocate directly. You must allocate other smart-large-object data structures, such as the LO-specification structure and the LO-status structure, with DataBlade API constructor functions.

Obtaining an LO File Descriptor

The smart-large-object creation functions (Figure 3-5) return an LO file descriptor for a smart large object. The LO file descriptor is needed for most subsequent operations on the smart large object. However, this LO file descriptor is only valid within the current database connection.

The following code fragment uses the mi_lo_create() function to generate an LO file descriptor for a new smart large object:

MI_LO_FD LO_fd;
MI_LO_HANDLE *LO_ptr;
MI_LO_SPEC *LO_spec;
MI_CONNECTION *conn;
...
LO_fd = mi_lo_create(conn, LO_spec, MI_LO_RDONLY, &LO_ptr);

Tip: A return value of zero from a smart-large-object creation function does not indicate an error. The value zero (0) is a valid LO file descriptor.

Writing Data to a Smart Large Object

To write data to the sbspace of a smart large object, use one of the following smart-large-object functions:

The mi_lo_write() function begins the write operation at the current LO seek position.

mi_lo_tell()

mi_lo_seek()

The mi_lo_writewithseek() function performs the seek and write operations with a single function call.

mi_lo_writewithseek()

Both of these functions write a specified number of bytes from a user-defined character buffer to the open smart large object that an LO file descriptor identifies. The smart-large-object optimizer determines the default extent size for the smart large object based on the amount of data that you write. Therefore, try to maximize the amount of data you write in a single call to mi_lo_write() or mi_lo_writewithseek().

Important: An attempt to write data to an sbspace that does not exist results in an error.

For more information on the syntax of the mi_lo_write() and mi_lo_writewithseek() functions, see their descriptions in Chapter 12, "DataBlade API Function Descriptions."

In addition to a write operation, you might also need to perform the following operations on the open smart large object.

Task Smart-Large-Object Function For More Information
Read data from the sbspace
mi_lo_read(), mi_lo_readwithseek()
page 3-69

Obtain the LO seek position
mi_lo_tell()
page 3-69

Obtain status information
mi_lo_stat()
page 3-74

Obtain storage characteristics
mi_lo_stat_cspec()
page 3-74

Storing the LO Handle

The INSERT or UPDATE statement can store the LO handle of a smart large object into the CLOB, BLOB, or opaque-type column. To store a smart large object in the database, follow these steps:

1. Provide the LO handle to the INSERT or UPDATE statement, as follows:

For a CLOB or BLOB column, provide the LO handle as data for the column.
For an opaque-type column, store the LO handle in the internal structure of the opaque data type and pass this internal structure as data for the column.

2. Execute the INSERT or UPDATE statement with a DataBlade API function such as mi_exec() or mi_exec_prepared_statement().

Tip: The data of the smart large object is stored when you write it to the sbspace of the smart large object.

When you save the LO handle in the CLOB or BLOB column, the smart-large-object optimizer increments the reference count of the smart large object by one. When you save the LO handle in an opaque-type column, the assign() support function for the opaque type must increment the reference count.

If you create a new smart large object but do not store it in a database column, the smart large object is a temporary smart large object. The database server does not guarantee that temporary smart large objects remain valid once they are closed. When all references to the smart large objects are deleted, the database server deletes the smart large object. For more information, see "Deleting a Smart Large Object".

Deallocating Resources

Once you store the new smart large object in the database, make sure that you deallocate any resources you no longer need. When you create a new smart large object, you might need to deallocate resources of the following data structures:

The LO-specification structure
The LO handle

If any of the smart-large-object data structures have a memory duration that of PER_ROUTINE, the database server automatically deallocates the structure when the user-defined routine completes.

Deallocating an LO-Specification Structure

The LO-specification structure has the memory duration that is current when the mi_lo_spec_init() function allocates it. Therefore, if an LO-specification structure has a memory duration of PER_ROUTINE, the database server automatically deallocates it when the user-defined routine completes.

To explicitly deallocate the resources assigned to an LO-specification structure, use the mi_lo_spec_free() function. When these resources are freed, they can be reallocated to other structures that your program needs. For more information on the syntax of the mi_lo_spec_free() function, see its description in Chapter 12, "DataBlade API Function Descriptions."

Deallocating an LO Handle

The LO handle structure has the memory duration that is current when the structure is allocated. Therefore, if it has a memory duration of PER_ROUTINE, the database server automatically deallocates it when the user-defined routine completes.

To explicitly deallocate the resources assigned to an LO handle, you can use the following functions:

The mi_lo_release() function releases resources of a temporary smart large object.

A temporary smart large object is one that was created in a DataBlade routine but was never inserted in a table. The mi_lo_release() function deallocates an LO handle that the DataBlade API allocated. For more information, see the description of the mi_lo_release() function in

Chapter 12, "DataBlade API Function Descriptions."

The mi_free() function releases an LO handle that you allocated.

mi_alloc()

mi_dalloc()

mi_free()

When these resources are freed, they can be reallocated to other structures that your program needs.

Sample Code to Create a New Smart Large Object

Suppose you want to create a new smart large object for the cat_descr column in the catalog2 table that contains the following data:

The rain in Spain stays mainly in the plain. In Hartford, 
Hereford, and Hampshire, hurricanes hardly happen.

The following code fragment creates a new smart large object, which assumes storage characteristics of its column, cat_descr, and then modifies the logging behavior:

#include "int8.h"

#include "milo.h"

#define BUFSZ 10000

{
	MI_CONNECTION *conn;
	MI_LO_SPEC *create_spec = NULL;
	MI_LO_HANDLE *descrip = NULL;
	MI_LO_FD lofd;
	char buf[BUFSZ];

	mi_integer buflen = BUFSZ;

	mi_int8 offset, est_size;

	mi_integer numbytes;
...
/* Allocate and initialize the LO-specification structure 

  */

	if ( mi_lo_spec_init(conn, &create_spec) == MI_ERROR )

		handle_lo_error("mi_lo_spec_init()");

/* Obtain the following column-level storage characteristics

  * for the cat_desc column:

  *           sbspace name = sb1 (this sbspace must already exist)

  *            keep last access time is ON

  */
	if ( mi_lo_colinfo_by_name(conn, "catalog2.cat_descr",

			create_spec) == MI_ERROR )
		handle_lo_error("mi_lo_colinfo_by_name()");

/* Provide user-specified storage characteristics:

  *          logging behavior is ON

  *          size estimate of 2 kilobytes 

  */

	mi_lo_specset_flags(create_spec, MI_LO_ATTR_LOG);

	ifx_int8cvint(2000, &est_size);
	mi_lo_specset_estbytes(create_spec, &est_size)

/* Create an LO handle and LO file descriptor for the new

  * smart large object */

	if ( lofd = mi_lo_create(conn, create_spec, MI_LO_RDWR, 

			&descrip) == MI_ERROR )

		handle_lo_error("mi_lo_create()");

/* Copy data into the character buffer 'buf' */

	sprintf(buf, "%s %s %s"

		"The rain in Spain stays mainly in the plain. ",

		"In Hartford, Hereford, and Hampshire, hurricanes",

		" hardly happen.");

/* Write contents of character buffer to the open smart

  * large object to which lofd points.

  */

	ifx_int8cvint(0, &offset);
	if ( numbytes = mi_lo_writewithseek(conn, lofd, buf,

			buflen, &offset, MI_LO_SEEK_SET) == MI_ERROR )
		handle_lo_error("mi_lo_writewithseek()");

/* Close the LO file descriptor */
	mi_lo_close(lofd);

/* Deallocate LO-specification structure */
	mi_lo_spec_free(conn, create_spec);

After the mi_lo_create() function executes:

the create_spec LO handle has been allocated and identifies the new smart large object.
the lofd LO file descriptor identifies the open smart large object.
the new smart large object has user-specified storage characteristics for logging behavior and estimated size.

The smart large object inherits the other storage characteristics.

Figure 3-15

Figure 3-15
Storage Characteristics for the New Smart Large Object

Storage Characteristic Value Specified By
Disk-storage information:

Size of extent
Calculated by smart-large-object optimizer
system-specified

Size of next extent
Calculated by smart-large-object optimizer
system-specified

Minimum extent size
Calculated by smart-large-object optimizer
system-specified

Size of smart large object
2 kilobytes
(smart-large-object optimizer uses as size estimate)
mi_lo_specset_estbytes()

Maximum size of I/O block
Calculated by smart-large-object optimizer
system-specified

Name of sbspace
sb1
column-level

Attribute information:

Logging
ON
mi_lo_specset_flags() with MI_LO_ATTR_LOG

Last-access time
ON
column-level

Figure 3-11 shows the system-specified storage characteristics for the sb1 sbspace, and Figure 3-12 shows the column-level storage characteristics for the cat_descr column.

The mi_lo_writewithseek() function writes the buf data to the smart large object that lofd identifies. When the write operation is successful, the descrip LO handle is ready to be stored in the CLOB column with the INSERT statement.

DataBlade API Programmer's ManualChapter 3: Handling Smart Large Objects Home Contents Index Master Index New Book

Creating a Smart Large Object

New Storage Characteristics

Choosing Storage Characteristics

Obtaining an LO Handle

DataBlade API Programmer's Manual
Chapter 3: Handling Smart Large Objects

Home Contents Index Master Index New Book