SQLGetPosition function (CLI) - Return starting position of string

Returns the starting position of one string within a LOB value (the source). The source value must be a LOB locator, the search string can be a LOB locator or a literal string.

Specification:

CLI 2.1

The source and search LOB locators can be any that have been returned from the database from a fetch or a SQLGetSubString() call during the current transaction.

Unicode equivalent: This function can also be used with the Unicode character set. The corresponding Unicode function is SQLGetPositionW(). For information about ANSI to Unicode function mappings, refer to Unicode functions (CLI).

Syntax

SQLRETURN   SQLGetPosition   (SQLHSTMT          StatementHandle,   /* hstmt */
                              SQLSMALLINT       LocatorCType,
                              SQLINTEGER        SourceLocator,
                              SQLINTEGER        SearchLocator,
                              SQLCHAR           *SearchLiteral,
                              SQLINTEGER        SearchLiteralLength,
                              SQLUINTEGER       FromPosition,
                              SQLUINTEGER       *LocatedAt,
                              SQLINTEGER        *IndicatorValue);

Function arguments

Table 1. SQLGetPosition 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 can 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.
SQLINTEGER	SearchLocator	input	If the SearchLiteral pointer is NULL and if SearchLiteralLength is set to 0, then SearchLocator must be set to the LOB locator associated with the search string; otherwise, this argument is ignored.
SQLCHAR *	SearchLiteral	input	This argument points to the area of storage that contains the search string literal. If SearchLiteralLength is 0, this pointer must be NULL.
SQLINTEGER	SearchLiteralLength	input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) needed to store SearchLiteral(in bytes). ^a If this argument value is 0, then the argument SearchLocator is meaningful.
SQLUINTEGER	FromPosition	input	For BLOBs and CLOBs, this is the position of the first byte within the source string at which the search is to start. For DBCLOBs, this is the first character. The start byte or character is numbered 1.
SQLUINTEGER *	LocatedAt	output	For BLOBs and CLOBs, this is the byte position at which the string was located or, if not located, the value zero. For DBCLOBs, this is the character position. If the length of the source string is zero, the value 1 is returned.
SQLINTEGER *	IndicatorValue	output	Always set to zero.
Note: a This is in bytes even for DBCLOB data.

Usage

SQLGetPosition() is used in conjunction with SQLGetSubString() in order to obtain any portion of a LOB in a random manner. In order to use SQLGetSubString(), the location of the substring within the overall string must be known in advance. In situations where the start of that substring can be found by a search string, SQLGetPosition() can be used to obtain the starting position of that substring.

The Locator and SearchLocator (if used) arguments can contain any valid LOB locator which has not been explicitly freed using a FREE LOCATOR statement or implicitly freed because the transaction during which it was created has ended.

The Locator and SearchLocator must have the same LOB locator type.

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 2. SQLGetPosition SQLSTATEs
SQLSTATE	Description	Explanation
07006	Invalid conversion.	The combination of LocatorCType and either of the LOB locator values is not valid.
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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY009	Invalid argument value.	The pointer to the LocatedAt argument was NULL. The argument value for FromPosition was not greater than 0. LocatorCType is not one of SQL_C_CLOB_LOCATOR, SQL_C_BLOB_LOCATOR, or SQL_C_DBCLOB_LOCATOR.
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 SearchLiteralLength was less than 1, and not SQL_NTS. The length of the pattern is longer than the maximum data length of the associated variable SQL data type (for Db2 for z/OS® servers, the pattern length is a maximum of 4000 bytes regardless of the data type or the LocatorCType). For LocatorCType of SQL_C_CLOB_LOCATOR, the literal maximum size is that of an SQLCLOB; for LocatorCType of SQL_C_BLOB_LOCATOR, the literal maximum size is that of an SQLVARBINARY; for LocatorCType of SQL_C_DBCLOB_LOCATOR, the literal maximum size is that of an SQLVARGRAPHIC.
HYC00	Driver not capable.	The application is currently connected to a data source that does not support large objects.
0F001	The LOB token variable does not currently represent any value.	The value specified for Locator or SearchLocator 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_SQLGETPOSITION and check the fExists output argument to determine if the function is supported for the current connection.

The SQLGetPosition() function is intended to handle graphic data and not WCHAR data. As a result, the data passed to this function should be considered big endian.

Example

  /* get the starting position of the CLOB piece of data */
  cliRC = SQLGetPosition(hstmtLocUse,
                         SQL_C_CLOB_LOCATOR,
                         clobLoc,
                         0,
                         (SQLCHAR *)"Interests",
                         strlen("Interests"),
                         1,
                         &clobPiecePos,
                         &ind);