The term large object and the generic acronym LOB are used to refer to any type of large object. There are three LOB data types: Binary Large Object (BLOB), Character Large Object (CLOB), and Double-Byte Character Large Object (DBCLOB). These LOB data types are represented symbolically as SQL_BLOB, SQL_CLOB, SQL_DBCLOB respectively. The list in Table 2 contains entries for the three LOB data types, the corresponding symbolic name, and the default C symbolic name. The LOB symbolic constants can be specified or returned on any of the DB2 CLI functions that take in or return an SQL data type argument (such as SQLBindParameter(), SQLDescribeCol()).
Since LOB values can be very large, transfer of data using the piecewise sequential method provides by SQLGetData() and SQLPutData() can be quite time consuming. Applications dealing with such data will often do so in random access segments or via direct file input an output.
There are many cases where an application needs to select a large object value and operate on pieces of it, but does not need or want the entire value to be transferred from the database server into application memory. In these cases, the application can reference an individual LOB value via a large object locator (LOB locator).
A LOB locator is a mechanism that allows an application program to manipulate a large object value in an efficient, random access fashion. A LOB locator is a run time concept: it is not a persistent type and is not stored in the database; it is a mechanism used to refer to a LOB value during a transaction and does not persist beyond the transaction in which it was created. The three LOB locator types each 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.
A LOB locator is a simple token value that represents a single LOB value. A locator is not a reference to a column in a row, rather it is created to reference a large object value. 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. An application can retrieve a LOB locator into an application variable (using the SQLBindCol() or SQLGetData() functions) and can then apply the following DB2 CLI functions to the associated LOB value via the locator:
Locators are implicitly allocated by:
LOB locators also provide an efficient method of moving data from one column of a table at the server 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. For example, 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))
The locator can be explicitly freed before the end of a transaction by executing the FREE LOCATOR statement. The syntax is shown below.
>>-FREE LOCATOR--?---------------------------------------------><
Although this statement cannot be prepared dynamically, DB2 CLI will accept it as a valid statement on SQLPrepare() and SQLExecDirect(). The application uses SQLBindParameter() with the SQL data type argument set to the appropriate SQL and C symbolic data types from Table 2.
Alternatively, if the application does require the entire LOB column value, it can request direct file input and output for LOBs. Database queries, updates, and inserts may involve transfer of single LOB column values into and from files. The two DB2 CLI LOB file access functions are:
The file name is either the complete path name of the file (which is recommended), or a relative file name. If a relative file name is provided, it is appended to the current path (of the operating environment) of the client process. On execute or fetch, data transfer to and from the file would take place, similar to bound application variables. A file options argument associated with these 2 functions indicates how the files are to be handled at time of transfer.
Use of SQLBindFileToParam() is more efficient than the sequential input of data segments using SQLPutData() since SQLPutData() essentially puts the input segments into a temporary file and then uses the SQLBindFileToParam() technique to send the LOB data value to the server. Applications should take advantage of SQLBindFileToParam() instead of using SQLPutData().
Refer to Appendix C, DB2 CLI and ODBC for information on writing generic ODBC applications that use SQL_LONGVARCHAR and SQL_LONGVARBINARY to respectively reference character and binary large object data.
Not all DB2 servers currently have Large Object support, to determine if any of the LOB functions are supported for the currently server, call SQLGetFunctions() with the appropriate function name argument value.
Figure 16 shows the retrieval of a character LOB (CLOB).
A LOB locator is fetched, which is then used as an input parameter to search the CLOB for a substring, the substring is then retrieved.
The file is first bound to the CLOB column, and when the row is fetched, the entire CLOB value is transferred directly to a file.
|
The following example extracts the "Interests" section from the Resume CLOB column of the EMP_RESUME table. Only the substring is transferred to the application.
/* From CLI sample lookres.c */
/* ... */
SQLCHAR * stmt2 = "SELECT resume FROM emp_resume "
"WHERE empno = ? AND resume_format = 'ascii'" ;
/* ... */
/* Get CLOB locator to selected Resume */
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_OUTPUT,
emp_no.type,
SQL_CHAR,
emp_no.length,
0,
emp_no.s,
emp_no.length,
&emp_no.ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "\n>Enter an employee number:\n" ) ;
gets( ( char * ) emp_no.s ) ;
rc = SQLExecDirect( hstmt, stmt2, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol( hstmt,
1,
SQL_C_CLOB_LOCATOR,
&ClobLoc1,
0,
&pcbValue
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLFetch( hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/*
Search CLOB locator to find "Interests"
Get substring of resume ( from position of interests to end )
*/
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &lhstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Get total length */
rc = SQLGetLength( lhstmt,
SQL_C_CLOB_LOCATOR,
ClobLoc1,
&SLength,
&Ind ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
/* Get Starting postion */
rc = SQLGetPosition( lhstmt,
SQL_C_CLOB_LOCATOR,
ClobLoc1,
0,
( SQLCHAR * ) "Interests",
9,
1,
&Pos1,
&Ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
rc = SQLFreeStmt( lhstmt, SQL_CLOSE ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
buffer = ( SQLCHAR * ) malloc( SLength - Pos1 + 1 ) ;
/* Get just the "Interests" section of the Resume CLOB */
/* ( From Pos1 to end of CLOB ) */
rc = SQLGetSubString( lhstmt,
SQL_C_CLOB_LOCATOR,
ClobLoc1,
Pos1,
SLength - Pos1,
SQL_C_CHAR,
buffer,
SLength - Pos1 + 1,
&OutLength,
&Ind
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, lhstmt, rc ) ;
/* Print Interest section of Employee's resume */
printf( "\nEmployee #: %s\n %s\n", emp_no.s, buffer ) ;
Existing ODBC applications use SQL_LONGVARCHAR and SQL_LONGVARBINARY instead of the DB2 BLOB and CLOB data types. By setting the LONGDATACOMPAT keyword in the initialization file, or setting the SQL_ATTR_LONGDATA_COMPAT connection attribute using SQLSetConnectAttr(), DB2 CLI will map the ODBC long data types to the DB2 LOB data types.
When this mapping is in effect:
The default setting for SQL_ATTR_LONGDATA_COMPAT is SQL_LD_COMPAT_NO, mapping is not in effect.
For more information, refer to Configuration Keywords, and SQLSetConnectAttr - Set Connection Attributes.
With the mapping in effect, ODBC applications can retrieve LOB data by using the SQLGetData(), SQLPutData() and related functions. For more information about inserting and retrieving data in pieces, refer to Sending/Retrieving Long Data in Pieces.
| Note: | DB2 CLI uses a temporary file when inserting LOB data in pieces. If the data originates in a file, the use of a temporary file can be avoided by using SQLBindFileToParam(). Call SQLGetFunctions() to query if support is provided for SQLBindFileToParam(). |