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

Accessing an Existing Smart Large Object

 To access an existing smart large object in the database, take the following steps.

Step Task Smart-Large-Object Function For More Information

1.

Execute a SELECT statement to obtain the LO handle of the smart large object from the CLOB or BLOB column.

mi_exec(), mi_exec_prepared_statement(), mi_value()

page 3-61

2.

Convert the returned column value into an LO handle.

C Cast

page 3-61

3.

Open the smart large object that the LO handle identifies and return a valid LO file descriptor.

mi_lo_open()

page 3-63

4.

Read a specified number of bytes and store them in a user-defined buffer.

mi_lo_read(), mi_lo_readwithseek()

page 3-69

5.

Close the smart large object.

mi_lo_close()

page 3-70

6.

Deallocate resources.

mi_lo_release()

page 3-70

Figure 3-16 shows the first four of these steps that a DataBlade routine uses to access the smart-large-object data from the emp_picture column of the employee table (Figure 3-3).

Figure 3-16
Selecting a BLOB Column

Selecting the LO Handle

The SELECT statement can select an LO handle of a smart large object from a CLOB, BLOB, or opaque-type column. Because the desired result of a query is usually the contents of an object, not just its LO handle, the DataBlade routine must then use the LO handle that the mi_value() function returns to access the smart-large-object data in its sbspace.

To select a smart large object from the database

    1. Execute the SELECT statement with a DataBlade API function such as mi_exec() or mi_exec_prepared_statement().

    2. Convert to an LO handle the MI_DATUM value that the mi_value() function returns.

    3. Optionally, convert the LO handle to its binary representation.

    4. Optionally, ensure that the LO handle is valid.

When a DataBlade API application selects columns of SQL type BLOB or CLOB, the retrieved value must be bound to a pointer variable of type MI_LO_HANDLE*. Similarly, a server routine should use the C type MI_LO_HANDLE* in the following situations:

Binary and Text Representations of an LO Handle

 The MI_LO_HANDLE data type is an opaque structure with a length of MI_LO_SIZE. The binary representation of the LO handle is the flat array of MI_LO_SIZE bytes. This binary representation of an LO handle can be:

To store the LO handle in a text context, you can convert it to its text representation with the mi_lo_from_string() function. The text representation is the text hexadecimal dump of the flat binary array. To represent hexadecimal format, each binary byte requires 2 bytes of characters. The text representation of an LO handle can be:

You can convert an LO handle back to its binary representation with the mi_lo_from_string() function.

Validating an LO Handle

An LO handle is valid when it correctly identifies the location of a smart large object in an sbspace. An LO handle might be invalid for any of the following reasons:

Use the mi_lo_validate() function to check whether an LO handle is valid. If mi_lo_validate() returns a positive integer, the LO handle is invalid. You can mark this LO handle as invalid with the mi_lo_invalidate() function. The following code fragment checks whether the LO handle that LO_hdl references is valid:

You can use the mi_lo_validate() function in the support function of an opaque data type that contains smart large objects.

Opening a Smart Large Object

You can open a smart large object with one of the following functions:

These functions open the smart large object in a particular open mode, which in turn determines the lock mode of the smart large object. They all return an LO file descriptor through which you can access the data of a smart large object as if it were in an operating-system file.

Open Mode

When you open a smart large object with the mi_lo_copy(), mi_lo_create(), mi_lo_expand(), or mi_lo_open() function, you specify as an argument the open mode for the data. The open mode describes the context in which the I/O operations on the smart large object are performed. The open mode is a bitmask flag that determines the following access capabilities:

Tip: The mi_lo_from_file() function does not require an open mode for the smart large object it creates. It always opens a smart large object in read/write mode and in buffered mode. The smart-large-object optimizer determines which method of access is most efficient.

To set an open-mode flag, use the C-language bitwise OR operator (|) to mask the desired open-mode constants together. The milo.h header file defines these open-mode constants.

Access-Mode Flags
The access mode determines which read and write operations are valid on the open smart large object. Figure 3-17 shows the access modes and the corresponding access-mode constants that the mi_lo_open(), mi_lo_copy(), mi_lo_create(), and mi_lo_expand() functions support.

Figure 3-17
Access-Mode Flags for Smart Large Objects

Access Mode Purpose Access-Mode Constant

Read-only mode

Only read operations are valid on the data.

MI_LO_RDONLY

Write-only mode

Only write operations are valid on the data.

MI_LO_WRONLY

Write/append mode

Any data you write is appended to the end of the smart large object. By itself, it is equivalent to write-only mode followed by a seek to the end of the smart large object. Read operations fail.

MI_LO_APPEND

Read/write mode

Both read and write operations are valid on the data.

MI_LO_RDWR

Truncate

Delete any existing data in the smart large object and move the LO seek position to the start of the smart large object (byte 0). If the smart large object does not contain data, this access mode has no effect.

MI_LO_TRUNC

If you do not specify an access mode, the default is read-only mode (MI_LO_RDONLY).

Tip: These access-mode flags for a smart large object are patterned after the UNIX System V file-access modes.

When you open a smart large object with LO_APPEND only, the smart large object is opened in write-only mode. Seek operations move the seek position, but read operations to the smart large object fail, and the LO seek position remains unchanged from its position just before the write. Write operations occur at the LO seek position, and then the seek position is moved.

You can mask the MI_LO_APPEND constant with another access-mode constant. In any of these OR combinations, the seek operation remains unaffected. The following table shows the effect that each of the OR combinations has on the read and write operations.

OR Operation Read Operations Write Operations

MI_LO_RDONLY | MI_LO_APPEND

Occur at the LO seek position and then move the seek position to the end of the data that has been read

Fail and do not move the LO seek position

MI_LO_WRONLY | MI_LO_APPEND

Fail and do not move the LO seek position

Move the LO seek position to the end of the smart large object and then write the data; LO seek position is at the end of the data after the write

MI_LO_RDWR | MI_LO_APPEND

Occur at the LO seek position and then move the seek position to the end of the data that has been read

Move the LO seek position to the end of the smart large object and then write the data; LO seek position is at the end of the data after the write

Access Flags
The access flags determine whether to access the smart-large-object data sequentially or with random access. Figure 3-18 shows the methods of access for smart large objects and the corresponding access constants that the mi_lo_open(), mi_lo_copy(), mi_lo_create(), and mi_lo_expand() functions support.

Figure 3-18
Access Flags for Smart Large Objects

Method of Access Purpose Access Constant

Random access

Indicates that I/O is random. When you plan to read in nonsequential locations in the smart large object, the smart-large-object optimizer should not read ahead a few pages.

MI_LO_RANDOM

Sequential access

Indicates that reads are sequential. When you read a smart large object sequentially, the smart-large-object optimizer can read ahead a few pages.

MI_LO_SEQUENTIAL

If you do not specify an access mode, the default is random, though the smart-large-object optimizer might change this default based on a particular read pattern. You can include one of these access-mode flags in the open mode to override the choice of the optimizer.

Buffering-Mode Flags
Figure 3-19 shows the buffering modes and the corresponding buffering-mode constants that the mi_lo_open(), mi_lo_copy(), mi_lo_create(), and mi_lo_expand() functions support.

Figure 3-19
Buffering-Mode Flags for Smart Large Objects

Buffering Mode Purpose Buffer-Mode Constant

Buffered access

Indicates that I/O of the smart-large-object data goes through the buffer pool. It tells the optimizer that someone might plan to reread the same LO page.

MI_LO_BUFFER

Unbuffered access

Indicates that I/O of the smart-large-object data does not use the buffer pool. Instead, it uses user-defined buffers.

MI_LO_NOBUFFER

If you do not specify a buffering mode, the default is buffered.

Smart-Large-Object Locks

To prevent simultaneous access to smart-large-object data, the smart-large-object optimizer obtains a lock on this data when you open the smart large object. This smart-large-object lock is distinct from the following kinds of locks:

    A lock on a smart large object does not lock the row in which the smart large object resides. However, if you retrieve a smart large object from a row and the row is still current, the database server might hold a row lock as well as a smart-large-object lock. Locks are held on the smart large object instead of on the row because many columns could be accessing the same smart-large-object data.

    A lock on one smart large object does not affect other smart large objects in the row.

When the smart-large-object optimizer opens a smart large object, it uses the following information to determine the lock mode of the smart large object:

    The database server obtains a lock as follows:

      When a write operation (or some other update) is actually performed on the smart large object, the database server upgrades this lock to an exclusive lock.

    If you select an isolation mode of repeatable read, the smart-large-object optimizer does not release any locks that it obtains on a smart large object until the end of the transaction.

The smart-large-object optimizer retains the lock as follows:

Important: You lose the lock at the end of a transaction even if the smart large object remains open. When the smart-large-object optimizer detects that a smart large object has no active lock, it automatically obtains a new lock when the first access occurs to the smart large object. The lock that it obtains is based on the original access mode of the smart large object.

The smart-large-object optimizer releases the lock when the current transaction terminates. However, the optimizer obtains the lock again when the next function that needs a lock executes. If this behavior is undesirable, use BEGIN WORK transaction blocks and place a COMMIT WORK or ROLLBACK WORK statement after the last statement that needs to use the lock.

Reading Data from a Smart Large Object

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

    You can obtain the current LO seek position with the mi_lo_tell() function, or you can specify the LO seek position with the mi_lo_seek() function.

    You specify the seek position at which to begin the read operation as arguments to mi_lo_readwithseek().

These functions both read a specified number of bytes from the open smart large object to a user-defined character buffer. For more information on the syntax of the mi_lo_read() and mi_lo_readwithseek() functions, see the descriptions in Chapter 12, "DataBlade API Function Descriptions."

You might also need to perform the following other operations on the open smart large object.

Task Smart-Large-Object Function For More Information

Write data to the sbspace

mi_lo_write(), mi_lo_writewithseek()

page 3-53

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

Closing a Smart Large Object

A smart large object remains open until it is closed in either of the following ways:

Once you finish the operations on the smart large object, you can close it explicitly with the mi_lo_close() function. This function deallocates the resources associated with the LO handle so that they can be reallocated to other structures that your program needs. In addition, the LO file descriptor can be reassigned to another smart large object.

When you close a smart large object, you release any share-mode or update-mode locks on that object. However, you do not release exclusive locks until the end of the transaction. For more information, see "Smart-Large-Object Locks".

Important: The end of a transaction does not close any smart large objects that are open. However, it does release any locks on the smart large objects.

For more information on the syntax of the mi_lo_close() function, see Chapter 12, "DataBlade API Function Descriptions." For more information on when the database server deletes a smart large object, see "Deleting a Smart Large Object".

Sample Code to Select an Existing Smart Large Object

Suppose you want to select the smart large object that was inserted into a CLOB column called cat_descr, of the catalog2 table. The following code fragment selects the following data from the cat_descr column:

The code fragment assumes that the descrip LO handle identifies the smart large object that is selected from the CLOB column. This LO handle is obtained with the SELECT statement on the cat_descr column.

The mi_lo_readwithseek() function reads 1000 bytes of data from the smart large object that lofd identifies to the buf user-defined buffer.



DataBlade API Programmer's Manual, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.