SQLGetPosition() - Find the starting position of a string

SQLGetPosition() 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.

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

ODBC specifications for SQLGetPosition()

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

Syntax

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

Function arguments

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

Table 2. SQLGetPosition() 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. This argument must specify 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.
SQLINTEGER SearchLocator input Specifies a LOB locator that refers to a search string.
This argument is ignored unless both the following conditions are met:
  • The SearchLiteral argument specifies a null pointer.
  • The SearchLiteralLength argument is set to 0.
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 length of the string in SearchLiteral (in bytes).1

If this argument value is 0, you specify the search string with a LOB locator. (The SearchLocator argument specifies the search string when it is represented by a LOB locator.)
SQLUINTEGER FromPosition input For BLOBs and CLOBs, this argument specifies the position of the byte within the source string at which the search is to start. For DBCLOBs, this argument specifies the character at which the search is to start. The start-byte or start-character is numbered 1.
SQLUINTEGER * LocatedAt output Specifies the position at which the search string was located. For BLOBs and CLOBs, this location is the byte position. For DBCLOBs, this location is the character position. If the search string is not located this argument returns zero.

If the length of the source string is zero, the value 1 is returned.
SQLINTEGER * IndicatorValue output Always set to zero.
Note:
  1. This is in bytes even for DBCLOB data.

Usage

Use SQLGetPosition() in conjunction with SQLGetSubString() to obtain a portion of a string in a random manner. To use SQLGetSubString(), you must know the location of the substring within the overall string in advance. In situations in which you want to use a search string to find the start of a substring, use SQLGetPosition().

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

The Locator and SearchLocator arguments must specify LOB locators of the same type.

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

Return codes

After you call SQLGetPosition(), 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.

Table 3. SQLGetPosition() SQLSTATEs
SQLSTATE Description Explanation
07006 Invalid conversion. The combination of the value that the LocatorCType argument specifies with either of the LOB locator values is not valid.
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. A value specified for the Locator or SearchLocator arguments is currently not a LOB locator.
42818 The operands of an operator or function are not compatible. The length of the search pattern is longer than 4000 bytes.
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.
HY009 Invalid use of a null pointer. This SQLSTATE is returned for one or more of the following reasons:
  • The pointer that the LocatedAt argument specifies is null.
  • The argument value for the FromPosition argument is not greater than 0.
  • The LocatorCType argument is not one of the following values:
    • 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.
HY090 Invalid string or buffer length. The value of SearchLiteralLength is less than 1, and not SQL_NTS.
HYC00 Driver not capable. The application is currently connected to a data source that does not support large objects.

Restrictions

This function is available only when you connect to a Db2 server that supports large objects. Call SQLGetFunctions() with the fFunction argument set to SQL_API_SQLGETPOSITION and check the fExists output argument to determine if the function is supported for the current connection.

Example

The following example shows an application that retrieves a substring from a large object. To find where in a large object this substring begins, the application calls SQLGetPosition().
Figure 1. An application that retrieves a substring from a large object
/* ... */
    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_INPUT, SQL_C_CHAR, SQL_CHAR,
                          7, 0, Empno.s, sizeof(Empno.s), &Empno.ind);                        
    printf("\n>Enter an employee number:\n");
    gets(Empno.s);
    rc = SQLExecDirect(hstmt, stmt2, SQL_NTS);
    rc = SQLBindCol(hstmt, 1, SQL_C_CLOB_LOCATOR, &ClobLoc1, 0,
                     &pcbValue);
    rc = SQLFetch(hstmt);
/******************************************************************
    Search CLOB locator to find "Interests"
    Get substring of resume (from position of interests to end)
*******************************************************************/
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &lhstmt);
    /* Get total length */
    rc = SQLGetLength(lhstmt, SQL_C_CLOB_LOCATOR, ClobLoc1, &SLength, &Ind);
    /* Get Starting position */
    rc = SQLGetPosition(lhstmt, SQL_C_CLOB_LOCATOR, ClobLoc1, 0,
                    "Interests", 9, 1, &Pos1, &Ind);
    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);
    /* Print Interest section of Employee's resume */
    printf("\nEmployee #: %s\n %s\n", Empno.s, buffer); 
/* ... */