Chapter 50 – SQL/CLI: Locator Functions¶

Note

You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.

For more information specific to CrateDB, check out the CrateDB Reference.

In this chapter, we’ll describe the CLI locator functions: SQLGetLength, SQLGetPosition and SQLGetSubstring. These functions are used with locators of BLOBs and CLOBs. They are new in SQL3; as far as we know, no DBMS supports them. Our description is therefore fairly brief.

Table of Contents

What is a Locator?
- FREE LOCATOR Statement
- HOLD LOCATOR Statement
SQLGetLength
SQLGetPosition
SQLGetSubstring

What is a Locator?¶

When you assign the value of a BLOB, CLOB, NCLOB, UDT or ARRAY to an embedded host language variable or a host language parameter, your DBMS generates and assigns a locator to the target, to uniquely identify a value of the corresponding type. The locator is a 4-octet, non-zero integer (that is, locators are 32-bit INTs), and exists only until the current transaction ends (unless it is held).

Locators have certain properties.

A locator may be either valid or invalid.
A locator may be a holdable locator.

When a locator is initially created, it is marked valid and (if applicable) not holdable. You have to execute a HOLD LOCATOR statement before the end of the transaction in which a locator is created if you want that locator to be holdable. A non-holdable locator remains valid until the end of the transaction in which it was generated, unless it is explicitly made invalid by a FREE LOCATOR statement or a ROLLBACK WITH SAVEPOINT statement. A holdable locator may remain valid beyond the end of the transaction in which it was generated; it becomes invalid when you execute a FREE LOCATOR statement, a ROLLBACK WITH SAVEPOINT statement with a SAVEPOINT clause or if the transaction in which it is generated (or any subsequent transaction) is rolled back. All locators are made invalid when the current SQL-session ends.

The following items can have the “data type” LOCATOR: a host variable, a host parameter, an SQL parameter in an external routine and a value returned by external function. To specify an item as a locator, add the <keyword>s AS LOCATOR to the specification. According to the SQL Standard, this then allows the passing of very large data values “without transferring the entire value to and from the SQL-agent”. That is – if you’re dealing with an image, why do this:

DBMS reads into DBMS memory
DBMS transfers to host language memory
host language writes copy

The procedure could do it without transfers, with a locator.

Standard SQL provides two statements for use with locators. Brief descriptions of each follow.

FREE LOCATOR Statement¶

The FREE LOCATOR statement removes the association between a locator variable or parameter and the value represented by that locator. The required syntax for the FREE LOCATOR statement is:

FREE LOCATOR :host_parameter_name> [ {,:host_parameter_name}... ]

The FREE LOCATOR statement frees one or more locators – that is, it marks the locators identified by the <host parameter name>s as invalid.

If you want to restrict your code to Core SQL, don’t use the FREE LOCATOR statement.

HOLD LOCATOR Statement¶

The HOLD LOCATOR statement marks a locator variable or parameter as a holdable locator. The required syntax for the HOLD LOCATOR statement is:

HOLD LOCATOR :host_parameter_name> [ {,:host_parameter_name}... ]

The HOLD LOCATOR statement lets you change the status of one or more locators from non-holdable to holdable. The difference between the two status has to do with when a locator becomes invalid: a non-holdable locator remains valid until the end of the transaction in which it was generated (unless it is explicitly made invalid), while a holdable locator may remain valid beyond the end of the transaction in which it was generated. All locators are made invalid when the current SQL-session ends.

If you want to restrict your code to Core SQL, don’t use the HOLD LOCATOR statement.

The rest of this chapter describes the three CLI locator functions.

SQLGetLength ¶

Function Prototype:

SQLRETURN  SQLGetLength(
  SQLHSTMT hstmt,                 /* 32-bit input */
  SQLSMALLINT LocatorType,        /* 16-bit input */
  SQLINTEGER Locator,             /* 32-bit input */
  SQLINTEGER *StringLength,       /* pointer to 32-bit output */
  SQLINTEGER *IndicatorValue      /* pointer to 32-bit output */
  );

Job: Return the length of the value represented by a BLOB, CLOB, NCLOB, UDT or ARRAY locator.

Algorithm:

If (there is a prepared statement associated with hstmt)
  return error: HY010 CLI-specific condition-function sequence error
If (LocatorType is not SQL_BLOB_LOCATOR or SQL_CLOB_LOCATOR or SQL_UDT_LOCATOR
or SQL_ARRAY_LOCATOR)
  return error: CLI-specific condition-invalid argument value
If (Locator does not refer to a Locator)
  return error: 0F001 locator exception-invalid specification
If (LocatorType==SQL_BLOB_LOCATOR and Locator doesn't refer to a BLOB)
Or (LocatorType==SQL_CLOB_LOCATOR and Locator doesn't refer to a CLOB)
Or (LocatorType==SQL_UDT_LOCATOR and Locator doesn't refer to a UDT)
Or (LocatorType==SQL_ARRAY_LOCATOR and Locator doesn't refer to an ARRAY)
  return error: dynamic SQL error-restricted data type attribute violation

Case:
If (the Large Object's value is NULL)
  If (IndicatorValue is a null pointer)
    return error: data exception-null value, no indicator parameter
    Set *IndicatorValue = SQL_NULL_DATA i.e. -1
Else
  If (IndicatorValue is not a null pointer)
    Set *IndicatorValue = 0
  If (LocatorType == SQL_BLOB_LOCATOR)
    Set *StringLength = length of BLOB, in octets
  If (LocatorType==SQL_CLOB_LOCATOR)
    Set *StringLength = length of CLOB, in characters
  If (LocatorType==SQL_UDT_LOCATOR)
    Set *StringLength = length of UDT, in octets
  If (LocatorType==SQL_ARRAY_LOCATOR)
    Set *StringLength = length of ARRAY, in octets

Notes:

The octet length and the character length will be the same value only if 8-bit Character sets are in use.

Example:

#include "sqlcli.h"
...
SQLINTEGER  lob;              /* large object locator */
SQLINTEGER  len;              /* length */
SQLINTEGER  ind;              /* indicator */
...
SQLGetLength(hstmt,SQL_CLOB_LOCATOR,lob,&len,&ind);

ODBC: Since this is an SQL3 function, it is not in ODBC 3.0.

SQLGetPosition ¶

Function Prototype:

SQLRETURN SQLGetPosition(
  SQLHSTMT StatementHandle,         /* 32-bit input */
  SQLSMALLINT LocatorType,          /* 16-bit input */
  SQLINTEGER SourceLocator,         /* 32-bit input */
  SQLINTEGER SearchLocator,         /* 32-bit input */
  SQLCHAR *SearchLiteral,           /* pointer to *ANY */
  SQLINTEGER SearchLiteralLength,    /* 32-bit input */
  SQLINTEGER FromPosition,          /* 32-bit input */
  SQLINTEGER *LocatedAt,            /* pointer to 32-bit integer */
  SQLINTEGER *IndicatorValue        /* pointer to 32-bit integer */
  );

Job: Return the position of a passed string within a BLOB, CLOB, NCLOB, UDT or ARRAY.

Algorithm:

  if (stmt is associated with a prepared or executed statement)
    return error: HY010 CLI-specific condition-function sequence error
      If (LocatorType is not SQL_BLOB_LOCATOR or SQL_CLOB_LOCATOR or
SQL_UDT_LOCATOR or SQL_ARRAY_LOCATOR)
    return error: CLI-specific condition-invalid argument value
  If (Locator does not refer to a Locator)
    return error: 0F001 locator exception-invalid specification
  If (LocatorType==SQL_BLOB_LOCATOR and Locator doesn't refer to a BLOB)
  Or (LocatorType==SQL_CLOB_LOCATOR and Locator doesn't refer to a CLOB)
  Or (LocatorType==SQL_UDT_LOCATOR and Locator doesn't refer to a UDT)
  Or (LocatorType==SQL_ARRAY_LOCATOR and Locator doesn't refer to an ARRAY)
    return error: dynamic SQL error-restricted data type attribute violation
  If (the Large Object's value is NULL)
    If (IndicatorValue is a null pointer)
      return error: data exception-null value, no indicator parameter
    Set *IndicatorValue = SQL_NULL_DATA i.e. -1
  Else
    If (IndicatorValue is not a null pointer)
      Set *IndicatorValue = 0;
  Set *LocatedAt = position of string within the BLOB or CLOB or NCLOB or UDT
or ARRAY.

ODBC: Since this is an SQL3 function, it is not in ODBC 3.0.

SQLGetSubstring ¶

Function Prototype:

SQLRETURN  SQLGetSubString(
  SQLHSTMT StatementHandle,             /* 32-bit input */
  SQLSMALLINT LocatorType,              /* 16-bit input */
  SQLINTEGER SourceLocator,             /* 32-bit input */
  SQLINTEGER FromPosition,              /* 32-bit input */
  SQLINTEGER ForLength,                 /* 32-bit input */
  SQLSMALLINT TargetType,               /* 16-bit input */
  SQLPOINTER TargetValue                /* pointer to output */
  SQLINTEGER BufferLength,              /* 32-bit input */
  SQLINTEGER *StringLength,             /* pointer to integer output */
  SQLINTEGER *IndicatorValue);          /* pointer to integer output */

Job: Extract a portion of a BLOB, CLOB, NCLOB, UDT or ARRAY, returning the result as a string or, alternatively, as a new BLOB, CLOB, NCLOB, UDT or ARRAY.

Algorithm:

  if (stmt is associated with a prepared or executed statement)
    return error: HY010 CLI-specific condition-function sequence error
  If (LocatorType is not SQL_BLOB_LOCATOR or SQL_CLOB_LOCATOR or
SQL_UDT_LOCATOR or SQL_ARRAY_LOCATOR)
    return error: CLI-specific condition-invalid argument value
  If (Locator does not refer to a Locator)
    return error: 0F001 locator exception-invalid specification
  If (LocatorType==SQL_BLOB_LOCATOR and Locator doesn't refer to a BLOB)
  Or (LocatorType==SQL_CLOB_LOCATOR and Locator doesn't refer to a CLOB)
  Or (LocatorType==SQL_UDT_LOCATOR and Locator doesn't refer to a UDT)
  Or (LocatorType==SQL_ARRAY_LOCATOR and Locator doesn't refer to an ARRAY)
    return error: dynamic SQL error-restricted data type attribute violation
  If (the Large Object's value is NULL)
    If (IndicatorValue is a null pointer)
      return error: data exception-null value, no indicator parameter
    Set *IndicatorValue = SQL_NULL_DATA i.e. -1
  Else
    If (IndicatorValue is not a null pointer)
      Set *IndicatorValue = 0;
  Transfer the substring from the BLOB, CLOB, NCLOB, UDT or ARRAY to
TargetValue, using a procedure analogous to Character Retrieval Procedure.

ODBC: Since this is an SQL3 function, it is not in ODBC 3.0.

And that’s it for the CLI locator functions. In the next (and final) chapter on the CLI, we’ll take a look at the Catalog functions.

Chapter 50 – SQL/CLI: Locator Functions¶

What is a Locator?¶

FREE LOCATOR Statement¶

HOLD LOCATOR Statement¶

SQLGetLength¶

SQLGetPosition¶

SQLGetSubstring¶

Subscribe to the CrateDB Newsletter now

SQLGetLength ¶

SQLGetPosition ¶

SQLGetSubstring ¶