LOB locators in CLI applications

CLI applications can use a LOB locator to perform SQL operations on a large object (LOB) value of a result-set, without transferring entire LOB value from the database server into application memory.

A LOB locator is a token value, defined as type SQLINTEGER, that allows for efficient random access of a large object. When a LOB locator is used, the server performs the query and instead of placing the value of the LOB column in the result set, it updates the LOB locator with an integer that corresponds to the value of the LOB. When the application later requests the result, the application then passes the locator to the server and the server returns the LOB result.

A LOB locator is not stored in the database. It refers to a LOB value during a transaction, and does not persist beyond the transaction in which it was created. It is a simple token value created to reference a single large object value, and not a column in a row. There is no operation that could be performed on a locator that would have an effect on the original LOB value stored in the row.

Each of the three LOB locator types has its own C data type (SQL_C_BLOB_LOCATOR, SQL_C_CLOB_LOCATOR, SQL_C_DBCLOB_LOCATOR). These types are used to enable transfer of LOB locator values to and from the database server.

Locators are implicitly allocated by:
  • Fetching a bound LOB column to the appropriate C locator type.
  • Calling SQLGetSubString() and specifying that the substring be retrieved as a locator.
  • Calling SQLGetData() on an unbound LOB column and specifying the appropriate C locator type. The C locator type must match the LOB column type or an error will occur.

In a CLI application, for a statement that retrieves LOB data, by default the row data is returned with LOB locators to reference the LOB values. In cases where a buffer of an adequate size has been bound to a LOB column, the LOB value will be returned in the buffer and not as a LOB locator.

Differences between regular data types and LOB locators

LOB locators can in general be treated as any other data type, but there are some important differences:
  • Locators are generated at the server when a row is fetched and a LOB locator C data type is specified on SQLBindCol(), or when SQLGetSubString() is called to define a locator on a portion of another LOB. Only the locator is transferred to the application.
  • The value of the locator is only valid within the current transaction. You cannot store a locator value and use it beyond the current transaction, even if the cursor used to fetch the LOB locator has the WITH HOLD attribute.
  • A locator can also be freed before the end of the transaction with the FREE LOCATOR statement.
  • Once a locator is received, the application can use SQLGetSubString(), to either receive a portion of the LOB value, or to generate another locator representing the sub-string. The locator value can also be used as input for a parameter marker (using SQLBindParameter()).

    A LOB locator is not a pointer to a database position, but rather it is a reference to a LOB value: a snapshot of that LOB value. There is no association between the current position of the cursor and the row from which the LOB value was extracted. This means that even after the cursor has moved to a different row, the LOB locator (and thus the value that it represents) can still be referenced.

  • SQLGetPosition() and SQLGetLength() can be used with SQLGetSubString() to define the sub-string.
For a given LOB column in the result set, the binding can be to a:
  • storage buffer for holding the entire LOB data value,
  • LOB locator, or
  • LOB file reference (using SQLBindFileToCol()).

Examples of using LOB locators

LOB locators also provide an efficient method of moving data from one column of a table in a database to another column (of the same or different table) without having to pull the data first into application memory and then sending it back to the server. The following INSERT statement inserts a LOB value that is a concatenation of 2 LOB values as represented by their locators: 
INSERT INTO lobtable values (CAST ? AS CLOB(4k) || CAST ? AS CLOB(5k))
CLI applications may also obtain LOB values in pieces using the following VALUES statement:
VALUES (SUBSTR(:locator, :offset, :length))