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))
applications
may also obtain LOB values in pieces using the following VALUES statement:
VALUES (SUBSTR(:locator, :offset, :length))
DB2 10.5 for Linux, UNIX, and Windows