Using LOB locators

LOB locators enable you to identify and manipulate LOB values at the database server. They also enable you to retrieve pieces of a LOB value into application memory.

Locators are a run time concept: they are not a persistent type, nor are they stored in the database. Conceptually, LOB locators are simple token values (much like a pointer) that you use to refer to much larger LOB values in the database. LOB locator values do not persist beyond the transaction in which they are created (unless you specify otherwise).

A locator references a LOB value, not the physical location (or address) at which a LOB value resides. The LOB value that a locator references does not change if the original LOB value in the table is altered. When you perform operations on a locator, these operations similarly do not alter the original LOB value that the table contains. To materialize operations that you perform on LOB locators, you must store the result of these operations in a location on the database server, or in a variable within your application.

In Db2 ODBC functions, you specify LOB locators with one of the following symbolic C data types:
  • SQL_C_BLOB_LOCATOR for BLOB data
  • SQL_C_CLOB_LOCATOR for CLOB data
  • SQL_C_DBCLOB_LOCATOR for DBCLOB data
Choose a C type that corresponds to the LOB data to which you refer with the locator. Through these C data types, you can transfer a small token value to and from the database server instead of an entire LOB value.

Call SQLBindCol() and SQLFetch(), or SQLGetData() to retrieve a LOB locator that is associated with a LOB value into an application variable. You can then apply the following Db2 ODBC functions to that locator:

  • SQLGetLength(), which returns the length of the string that a LOB locator represents.
  • SQLGetPosition(), which returns the position of a search string within a source string that a LOB locator represents. LOB locators can represent both search strings and source strings.
The following actions implicitly allocate LOB locators:
  • 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; otherwise an error occurs.

You can also use LOB locators to move LOB data at the server without pulling data into application memory and then sending it back to the server.

Example: The following INSERT SQL statement concatenates two LOB values with LOB locators (which are represented by the parameter markers) and inserts the result into a table: 
INSERT INTO TABLE4A
VALUES(1,CAST(? AS CLOB(2K)) CONCAT CAST(? AS CLOB(3K)))

You can explicitly free a locator before the end of a transaction with the FREE LOCATOR statement. You can explicitly retain a locator beyond a unit of work with the HOLD LOCATOR statement. You execute these statements with the following syntax:

Read syntax diagramSkip visual syntax diagramFREE LOCATOR,host_variable
Read syntax diagramSkip visual syntax diagramHOLD LOCATOR,host_variable

Although you cannot prepare the FREE LOCATOR SQL statement or the HOLD LOCATOR SQL statement dynamically, Db2 ODBC accepts these statements in SQLPrepare() and SQLExecDirect(). Use parameter markers in these statements so that you can convert application variables that contain LOB locator values to host variables that these SQL statements can access. Before you call SQLPrepare() or SQLExecDirect(), call SQLBindParameter() with the data type arguments set to the appropriate SQL and C symbolic data types. This calls to SQLBindParameter() passes an application variable that contains the locator value into the parameter markers as a host variable.

LOB locators and functions that are associated with locators (such as the SQLGetSubString(), SQLGetPosition(), and SQLGetLength() functions) are not available when you connect to a Db2 server that does not support large objects. To determine if a connection supports LOBs, call SQLGetFunctions() with the function type set to SQL_API_SQLGETSUBSTRING. If the pfExists output argument returns SQL_TRUE, the current connection supports LOBs. If the pfExists output argument returns SQL_FALSE, the current connection does not support LOBs.

For applications that use locators when retrieving LOB data from a result set, set the LIMITEDBLOCKFETCH initialization keyword to 0. Otherwise, if you attempt to use the SQLGetData() function to retrieve a LOB locator into an application variable after the data has been fetched, the function call fails.