SQLGetSubString() - Retrieve a portion of a string value

SQLGetSubString() retrieves a portion of a large object value that is referenced by a LOB locator.

ODBC specifications for SQLGetSubString()

Table 1. SQLGetSubString() specifications
ODBC specification level In X/Open CLI CAE specification? In ISO CLI specification?
No No No

Syntax

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

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. SQLGetSubString() arguments
Data type Argument Use Description
SQLHSTMT hstmt input Specifies a statement handle. This can be any statement handle that is allocated but does not currently have a prepared statement assigned to it.
SQLSMALLINT LocatorCType input Specifies the C type of the source LOB locator with one of the following values:
  • SQL_C_BLOB_LOCATOR for BLOB data
  • SQL_C_CLOB_LOCATOR for CLOB data
  • SQL_C_DBCLOB_LOCATOR for DBCLOB data
SQLINTEGER Locator input Specifies the source LOB locator value.
SQLUINTEGER FromPosition input Specifies the position at which the string that is retrieved begins. For BLOBs and CLOBs, this is the position of the first byte the function returns. For DBCLOBs, this is the first character. The start-byte or start-character is numbered 1.
SQLUINTEGER ForLength input Specifies the length of the string that SQLGetSubString() retrieves. For BLOBs and CLOBs, this is the length in bytes. For DBCLOBs, this is the length in characters.

If the value that the FromPosition argument specifies is less than the length of the source string, but FromPosition + ForLength -1 extends beyond the position of 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 Specifies the target C data type for the string that is retrieved into the buffer to which the rgbValue argument points. This target can be a LOB locator C buffer of one of the following types:
  • SQL_C_CLOB_LOCATOR
  • SQL_C_BLOB_LOCATOR
  • SQL_C_DBCLOB_LOCATOR
Or, the target can be a C string variable of one of the following types:
  • SQL_C_CHAR for CLOB data
  • SQL_C_BINARY for BLOB data
  • SQL_C_DBCHAR for DBCLOB data
SQLPOINTER rgbValue output Pointer to the buffer where the retrieved string value or a LOB locator is stored.
SQLINTEGER cbValueMax input Specifies the maximum size (in bytes) of the buffer to which the rgbValue argument points.
SQLINTEGER * StringLength output If the target C buffer type is intended for a binary or character string variable, not a locator value, this argument points to the length (in bytes1) of the substring that is retrieved.

If a null pointer is specified, no value is returned.
SQLINTEGER * IndicatorValue output Always returns zero.
Note:
  1. This is in bytes even for DBCLOB data.

Usage

Use SQLGetSubString() to obtain any portion of the string that a LOB locator represents. The target for this substring can be one of the following objects:
  • An appropriate C string variable.
  • A new LOB value that is created on the server. The LOB locator for this value can be assigned to a target application variable on the client.

You can use SQLGetSubString() as an alternative to SQLGetData() for retrieving data in pieces. To use SQLGetSubString() to retrieve data in pieces, you first bind a column to a LOB locator. You then use this LOB locator to fetch the LOB value as a whole or in pieces.

The Locator argument can contain any valid LOB locator that was returned by a fetch or a previous SQLGetSubString() call during the current transaction. Do not free the LOB locator through a FREE LOCATOR statement, or execute SQLGetSubString() in a different transaction from the one in which the locator is created.

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

Return codes

After you call SQLGetSubString(), it returns one of the following values:
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO
  • SQL_ERROR
  • SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

40
Table 3. SQLGetSubString() SQLSTATEs
SQLSTATE Description Explanation
01004 Data truncated. The amount of returned data is longer than cbValueMax. Actual length, in bytes, that is available for return is stored in StringLength.
07006 Invalid conversion. This SQLSTATE is returned for one or more of the following reasons:
  • The value specified for TargetCType is 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).
08S01 Communication link failure. The communication link between the application and data source fails before the function completes.
0F001 The LOB token variable does not currently represent any value. The value specified for Locator or SearchLocator is not currently a LOB locator.
22011 A substring error occurred. FromPosition is greater than the length of the source string.
58004 Unexpected system failure. Unrecoverable system error.
HY001 Memory allocation failure. Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY003 Program type out of range. LocatorCType is not one of the following:
  • SQL_C_CLOB_LOCATOR
  • SQL_C_BLOB_LOCATOR
  • SQL_C_DBCLOB_LOCATOR
HY013 Unexpected memory handling error. Db2 ODBC is not able to access the memory that is required to support execution or completion of the function.
HY024 Invalid argument value. The value specified for FromPosition or for ForLength is not a positive integer.
HY090 Invalid string or buffer length. The value of cbValueMax is less than 0.
HYC00 Driver not capable. The application is currently connected to a data source that does not support large objects.

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.

Example

Refer to the function SQLGetPosition() for a related example.