Call Level Interface Guide and Reference

SQLGetSubString - Retrieve Portion of A String Value

Purpose

Specification: DB2 CLI 2.1

SQLGetSubString() is used to retrieve a portion of a large object value, referenced by a large object locator that has been returned from the server (returned by a fetch or a previous SQLGetSubString() call) during the current transaction.

Syntax

SQLRETURN  SQLGetSubString   (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLSMALLINT       LocatorCType,
                SQLINTEGER        SourceLocator,
                SQLUINTEGER       FromPosition,
                SQLUINTEGER       ForLength,
                SQLSMALLINT       TargetCType,
                SQLPOINTER        DataPtr,           /* rgbValue */
                SQLINTEGER        BufferLength,      /* cbValueMax */
                SQLINTEGER   FAR  *StringLength,
                SQLINTEGER   FAR  *IndicatorValue);

Function Arguments

Table 120. SQLGetSubString Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle input Statement handle. This can be any statement handle which has been allocated but which does not currently have a prepared statement assigned to it.
SQLSMALLINT LocatorCType input The C type of the source LOB locator. This may be:

SQL_C_BLOB_LOCATOR
SQL_C_CLOB_LOCATOR
SQL_C_DBCLOB_LOCATOR

SQLINTEGER Locator input Locator must be set to the source LOB locator value.
SQLUINTEGER FromPosition input For BLOBs and CLOBs, this is the position of the first byte to be returned by the function. For DBCLOBs, this is the first character. The start byte or character is numbered 1.
SQLUINTEGER ForLength input This is the length of the string to be returned by the function. For BLOBs and CLOBs, this is the length in bytes. For DBCLOBs, this is the length in characters.
If FromPosition is less than the length of the source string but FromPosition + ForLength - 1 extends beyond the end of the source string, the result is padded on the right with the necessary number of characters (X'00' for BLOBs, single byte blank character for CLOBs, and double byte blank character for DBCLOBs).
SQLSMALLINT TargetCType input The C data type of the DataPtr. The target must always be either a LOB locator C buffer type (SQL_C_CLOB_LOCATOR, (SQL_C_BLOB_LOCATOR, (SQL_C_DBCLOB_LOCATOR) or a C string variable (SQL_C_CHAR for CLOB, SQL_C_BINARY for BLOB, and SQL_C_DBCHAR for DBCLOB).
SQLPOINTER DataPtr output Pointer to the buffer where the retrieved string value or a LOB locator is to be stored.
SQLINTEGER BufferLength input Maximum size of the buffer pointed to by DataPtr in bytes.
SQLINTEGER * StringLength output The length of the returned information in DataPtr in bytes^a if the target C buffer type is intended for a binary or character string variable and not a locator value.
If the pointer is set to NULL, nothing is returned.
SQLINTEGER * IndicatorValue output Always set to zero.

Note:

a
This is in bytes even for DBCLOB data.

Usage

SQLGetSubString() is used to obtain any portion of the string that is represented by the LOB locator. There are two choices for the target:

The target can be an appropriate C string variable.
A new LOB value can be created on the server and the LOB locator for that value can be assigned to a target application variable on the client.

SQLGetSubString() can be used as an alternative to SQLGetData for getting data in pieces. In this case a column is first bound to a LOB locator, which is then used to fetch the LOB as a whole or in pieces.

The Locator argument can contain any valid LOB locator which has not been explicitly freed using a FREE LOCATOR statement nor implicitly freed because the transaction during which it was created has terminated.

The statement handle must not have been associated with any prepared statements or catalog function calls.

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 121. SQLGetSubString SQLSTATEs

SQLSTATE Description Explanation
01004 Data truncated. The amount of data to be returned is longer than BufferLength. Actual length available for return is stored in StringLength.
07006 Invalid conversion. The value specified for TargetCType was not SQL_C_CHAR, SQL_C_BINARY, SQL_C_DBCHAR or a LOB locator.
The value specified for TargetCType is inappropriate for the source (for example SQL_C_DBCHAR for a BLOB column).
22011 A substring error occurred. FromPosition is greater than the of length of the source string.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY003 Program type out of range. LocatorCType is not one of SQL_C_CLOB_LOCATOR, SQL_C_BLOB_LOCATOR, or SQL_C_DBCLOB_LOCATOR.
HY009 Invalid argument value. The value specified for FromPosition or for ForLength was not a positive integer.
HY010 Function sequence error. The specified StatementHandle is not in an allocated state.
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY013 Unexpected memory handling error. DB2 CLI was unable to access memory required to support execution or completion of the function.
HY090 Invalid string or buffer length. The value of BufferLength was less than 0.
HYC00 Driver not capable. The application is currently connected to a data source that does not support large objects.
0F001 No locator currently assigned The value specified for Locator is not currently a LOB locator.

SQLSTATE	Description	Explanation
01004	Data truncated.	The amount of data to be returned is longer than `BufferLength`. Actual length available for return is stored in `StringLength`.
07006	Invalid conversion.	The value specified for `TargetCType` was not SQL_C_CHAR, SQL_C_BINARY, SQL_C_DBCHAR or a LOB locator. The value specified for `TargetCType` is inappropriate for the source (for example SQL_C_DBCHAR for a BLOB column).
22011	A substring error occurred.	`FromPosition` is greater than the of length of the source string.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
58004	Unexpected system failure.	Unrecoverable system error.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY003	Program type out of range.	`LocatorCType` is not one of SQL_C_CLOB_LOCATOR, SQL_C_BLOB_LOCATOR, or SQL_C_DBCLOB_LOCATOR.
HY009	Invalid argument value.	The value specified for `FromPosition` or for `ForLength` was not a positive integer.
HY010	Function sequence error.	The specified `StatementHandle` is not in an `allocated` state. The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY013	Unexpected memory handling error.	DB2 CLI was unable to access memory required to support execution or completion of the function.
HY090	Invalid string or buffer length.	The value of `BufferLength` was less than 0.
HYC00	Driver not capable.	The application is currently connected to a data source that does not support large objects.
0F001	No locator currently assigned	The value specified for `Locator` is not currently a LOB locator.

Restrictions

This function is not available when connected to a DB2 server that does not support Large Objects. Call SQLGetFunctions() with the function type set to SQL_API_SQLGETSUBSTRING and check the fExists output argument to determine if the function is supported for the current connection.

CLI Sample dtlob.c

(The complete sample dtlob.c is also available here .)

 
/* From the CLI sample dtlob.c */
/* ... */
 
    /* read the piece of CLOB data in buffer */
    sqlrc = SQLGetSubString( hstmtLocUse,
                             SQL_C_CLOB_LOCATOR,
                             clobLoc,
                             clobPiecePos,
                             clobLen - clobPiecePos,
                             SQL_C_CHAR,
                             buffer,
                             clobLen - clobPiecePos + 1,
                             &clobPieceLen,
                             &ind ) ;
    STMT_HANDLE_CHECK( hstmtLocUse, sqlrc);

References

[ Top of Page | Previous Page | Next Page ]