Fetching LOB data with LOB locators in CLI applications

There are many cases where an application needs to fetch a large object value by referencing a large object locator (LOB locator). An example is used to demonstrate how using a locator to retrieve CLOB data allows a character string to be extracted from the CLOB, without having to transfer the entire CLOB to an application buffer. The LOB locator is fetched and then used as an input parameter to search the CLOB for a substring. This substring is then retrieved.

Before you begin

Before fetching LOB data with LOB locators, ensure that you have initialized your CLI application.

Procedure

To fetch LOB data using LOB locators:

Retrieve a LOB locator into an application variable using the SQLBindCol() or SQLGetData() functions. For example:

    SQLINTEGER  clobLoc ;   
    SQLINTEGER  pcbValue ;

    /* ... */
    sqlrc = SQLBindCol( hstmtClobFetch, 1, SQL_C_CLOB_LOCATOR,
                        &clobLoc, 0, &pcbValue);

Fetch the locator using SQLFetch():
```
    sqlrc = SQLFetch( hstmtClobFetch );
```

Call SQLGetLength() to get the length of a string that is represented by a LOB locator. For example:

    sqlrc = SQLGetLength( hstmtLocUse, SQL_C_CLOB_LOCATOR,
                          clobLoc, &clobLen, &ind ) ;

Call SQLGetPosition() to get the position of a search string within a source string where the source string is represented by a LOB locator. The search string can also be represented by a LOB locator. For example:

    sqlrc = SQLGetPosition( hstmtLocUse,
                            SQL_C_CLOB_LOCATOR,
                            clobLoc,
                            0,
                            ( SQLCHAR * ) "Interests",
                            strlen( "Interests"),
                            1,
                            &clobPiecePos,
                            &ind ) ;

Call SQLGetSubString() to retrieve the substring. For example:

    sqlrc = SQLGetSubString( hstmtLocUse,
                             SQL_C_CLOB_LOCATOR,
                             clobLoc,
                             clobPiecePos,
                             clobLen - clobPiecePos,
                             SQL_C_CHAR,
                             buffer,
                             clobLen - clobPiecePos + 1,
                             &clobPieceLen,
                             &ind ) ;

Free the locator. All LOB locators are implicitly freed when a transaction ends. The locator can be explicitly freed before the end of a transaction by executing the FREE LOCATOR statement.
Although this statement cannot be prepared dynamically, 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. For example,
```
    sqlrc = SQLSetParam( hstmtLocFree,
                         1,
                         SQL_C_CLOB_LOCATOR,
                         SQL_CLOB_LOCATOR,
                         0,
                         0,
                         &clobLoc,
                         NULL ) ;

    /* ... */
    sqlrc = SQLExecDirect( hstmtLocFree, stmtLocFree, SQL_NTS ) ;
```