10 TimesTen ODBC Functions and Options

This chapter covers the topics noted below, listing ODBC functions supported by TimesTen and options supported by TimesTen for set and get functions for statements and connections. For complete function definitions, refer to ODBC API reference documentation.

TimesTen supports ODBC 2.5, Extension Level 1, as well as Extension Level 2 features that are documented in this chapter.

Supported ODBC functions
Option support for ODBC connection and statement functions
Information type support for SQLGetInfo
Column descriptor support for SQLColAttributes

Supported ODBC functions

This section lists ODBC function supported by TimesTen, with special notes as applicable.

Table 10-1 Supported ODBC functions

Function	Notes for TimesTen
`SQLAllocConnect`
`SQLAllocEnv`
`SQLAllocStmt`
`SQLBindCol`
`SQLBindParameter`	See "SQLBindParameter function".
`SQLCancel`	`SQLCancel` can cancel the following: An operation running on an `hstmt` on another thread An operation running on an `hstmt` that needs data `SQLCancel` cannot cancel the following: A global query `SQLCancel` can cancel only on the local node. TimesTen Cache or cache grid administrative operations Do not call `SQLCancel` directly from a signal handler. Such code may not be portable.
`SQLColAttributes` and `SQLColAttributesW`	See "Column descriptor support for SQLColAttributes".
`SQLColumns` and `SQLColumnsW`
`SQLConnect`
`SQLDataSources` and `SQLDataSourcesW`	Available only to programs using a driver manager.
`SQLDescribeCol` and `SQLDescribeColW`
`SQLDescribeParam`
`SQLDisconnect`
`SQLDriverConnect` and `SQLDriverConnectW`
`SQLDrivers` and `SQLDriversW`	Available only to programs using a driver manager.
`SQLError` and `SQLErrorW`	Native error codes are TimesTen errors. You may receive generic errors such as, "Execution at Oracle failed. Oracle error code `nnn`."
`SQLExecDirect`	See the note for `SQLExecute`.
`SQLExecute`	TimesTen does not support asynchronous statement execution. (TimesTen does not support the `SQL_ASYNC_ENABLE` statement option, as noted later in this chapter.)
`SQLFetch`
`SQLForeignKeys` and `SQLForeignKeysW`
`SQLFreeConnect`
`SQLFreeEnv`
`SQLFreeStmt`
`SQLGetConnectOption` and `SQLGetConnectOptionW`	See "Option support for SQLSetConnectOption and SQLGetConnectOption".
`SQLGetCursorName` and `SQLGetCursorNameW`	You can set or get a cursor name but not reference it, such as in a `WHERE CURRENT OF` clause for a positioned update or delete. TimesTen does not support positioned update or delete statements.
`SQLGetData`	See "Avoid SQLGetData".
`SQLGetFunctions`
`SQLGetInfo` and `SQLGetInfoW`	See "Information type support for SQLGetInfo".
`SQLGetStmtOption`	See "Option support for SQLSetStmtOption and SQLGetStmtOption".
`SQLGetTypeInfo` and `SQLGetTypeInfoW`
`SQLNativeSql` and `SQLNativeSqlW`
`SQLNumParams`
`SQLNumResultCols`
`SQLParamData`
`SQLParamOptions`
`SQLPrepare`
`SQLPrimaryKeys` and `SQLPrimaryKeysW`
`SQLProcedureColumns` and `SQLProcedureColumnsW`
`SQLProcedures` and `SQLProceduresW`
`SQLPutData`
`SQLRowCount`	In addition to its standard functionality, this has special usage with cache groups. See "Managing cache groups".
`SQLSetConnectOption` and `SQLSetConnectOptionW`	See "Option support for SQLSetConnectOption and SQLGetConnectOption" under the next section.
`SQLSetCursorName` and `SQLSetCursorNameW`	You can set or get a cursor name but not reference it, such as in a `WHERE CURRENT OF` clause for a positioned update or delete.
`SQLSetStmtOption`	See "Option support for SQLSetStmtOption and SQLGetStmtOption".
`SQLSetParam`	This is an ODBC 1.0 function, replaced by `SQLBindParameter` in ODBC 2.0. Retained for backward compatibility.
`SQLSpecialColumns` and `SQLSpecialColumnsW`
`SQLStatistics` and `SQLStatisticsW`
`SQLTables` and `SQLTablesW`
`SQLTransact`

Note:

TimesTen supports only UTF-16 as a national character set.

Option support for ODBC connection and statement functions

This section discusses TimesTen option support for the ODBC functions SQLSetConnectOption, SQLGetConnectOption, SQLSetStmtOption, and SQLGetStmtOption.

Refer to ODBC API reference documentation for general information about these functions.

Option support for SQLSet ConnectOption and SQLGetConnectOption

Table 10-2 and Table 10-3 document TimesTen support for standard and TimesTen-specific options for the ODBC SQLSetConnectOption and SQLGetConnectOption functions. These functions let you set connection options after the initial connection or retrieve those settings. Some of these correspond to connection attributes you can set during the connection process, as noted.

Also see "Option support for SQLSetStmtOption and SQLGetStmtOption". Those options can also be set using SQLSetConnectOption, in which case the value serves as a default for all statements on the connection.

Notes:

An option setting through SQLSetConnectOption or SQLSetStmtOption overrides the setting of the corresponding connection attribute (as applicable).
The documentation here also applies to SQLSetConnectOptionW and SQLGetConnectOptionW.

Table 10-2 Standard options: SQLSetConnectOption, SQLGetConnectOption

Option	Support
`SQL_ACCESS_MODE`	No
`SQL_AUTOCOMMIT`	Yes
`SQL_CURRENT_QUALIFIER`	No
`SQL_LOGIN_TIMEOUT`	No
`SQL_MAX_ROWS`	Yes
`SQL_NOSCAN`	Yes
`SQL_ODBC_CURSORS`	Yes, for programs using a driver manager
`SQL_OPT_TRACE`	Yes, for programs using a driver manager
`SQL_OPT_TRACEFILE`	Yes, for programs using a driver manager
`SQL_PACKET_SIZE`	No
`SQL_QUIET_MODE`	No
`SQL_TRANSLATE_DLL`	No
`SQL_TRANSLATE_OPTION`	No
`SQL_TXN_ISOLATION`	Yes, if `vParam` is `SQL_TXN_READ_COMMITTED` or `SQL_TXN_SERIALIZABLE` See "Prefetching multiple rows of data". Also see "Concurrency control through isolation and locking" in Oracle TimesTen In-Memory Database Operations Guide. Same functionality as the `Isolation` general connection attribute, as described in "Isolation" in Oracle TimesTen In-Memory Database Reference.

Table 10-3 TimesTen options: SQLSetConnectOption, SQLGetConnectOption

Option	Comments
`TT_CLIENT_TIMEOUT`	This is for client/server only and has the same functionality as the `TTC_Timeout` TimesTen client connection attribute, as described in "TTC_Timeout" in Oracle TimesTen In-Memory Database Reference.
`TT_DYNAMIC_LOAD_ENABLE`	See "Dynamic load configuration" in Oracle TimesTen Application-Tier Database Cache User's Guide. This has the same functionality as the `DynamicLoadEnable` TimesTen Cache general connection attribute described in "DynamicLoadEnable" in Oracle TimesTen In-Memory Database Reference.
`TT_DYNAMIC_LOAD_ERROR_MODE`	See "Return dynamic load errors" in Oracle TimesTen Application-Tier Database Cache User's Guide. This has the same functionality as the `DynamicLoadErrorMode` TimesTen Cache connection attribute described in "DynamicLoadErrorMode" in Oracle TimesTen In-Memory Database Reference.
`TT_NLS_LENGTH_SEMANTICS`	See "Setting globalization options". This has the same functionality as the `NLS_LENGTH_SEMANTICS` general connection attribute described in "NLS_LENGTH_SEMANTICS" in Oracle TimesTen In-Memory Database Reference. There is also related information about the functionality in "Additional globalization features".
`TT_NLS_NCHAR_CONV_EXCP`	See "Setting globalization options". This has the same functionality as the `NLS_NCHAR_CONV_EXCP` general connection attribute described in "NLS_NCHAR_CONV_EXCP" in Oracle TimesTen In-Memory Database Reference. There is also related information about the functionality in "Additional globalization features".
`TT_NLS_SORT`	See "Setting globalization options". This has the same functionality as the `NLS_SORT` general connection attribute described in "NLS_SORT" in Oracle TimesTen In-Memory Database Reference. There is also related information about the functionality in "Additional globalization features".
`TT_PREFETCH_CLOSE`	See "Enable TT_PREFETCH_CLOSE for Serializable transactions" in Oracle TimesTen In-Memory Database Operations Guide.
`TT_REGISTER_FAILOVER_CALLBACK`	See "Using automatic client failover in your application".
`TT_REPLICATION_TRACK`	See "Features for use with replication". For ODBC applications that use parallel replication and specify replication tracks, this has the same functionality as the `ReplicationTrack` general connection attribute, to specify a track number for the connection.

Opti on support for SQLSetStmtOption and SQLGetStmtOption

Table 10-4 and Table 10-5 document TimesTen support for standard and TimesTen-specific options for the ODBC SQLSetStmtOption and SQLGetStmtOption functions, which let you set or retrieve statement option settings.

To set an option default value for all statements associated with a connection, use SQLSetConnectOption.

Notes:

An option setting through SQLSetConnectOption or SQLSetStmtOption overrides the setting of the corresponding connection attribute (as applicable).

Table 10-4 Standard options: SQLSetStmtOption, SQLGetStmtOption

Option	Support
`SQL_ASYNC_ENABLE`	No
`SQL_BIND_TYPE`	No
`SQL_CONCURRENCY`	No
`SQL_CURSOR_TYPE`	No
`SQL_KEYSET_SIZE`	No
`SQL_MAX_LENGTH`	No `SQL_MAX_LENGTH` can be set, but any specified value is overridden with 0 (return all available data).
`SQL_MAX_ROWS`	Yes
`SQL_NOSCAN`	Yes
`SQL_QUERY_TIMEOUT`	Yes See "Setting a timeout or threshold for executing SQL statements".
`SQL_RETRIEVE_DATA`	No
`SQL_ROWSET_SIZE`	No
`SQL_SIMULATE_CURSOR`	No
`SQL_USE_BOOKMARKS`	No

Table 10-5 TimesTen options: SQLSetStmtOption, SQLGetStmtOption

Option	Comment
`TT_PREFETCH_COUNT`	See "Prefetching multiple rows of data".
`TT_QUERY_THRESHOLD`	See "Setting a threshold duration for SQL statements". This is to specify a time threshold for SQL statements, in seconds, after which TimesTen writes a warning to the support log and throws an SNMP trap.
`TT_PRIVATE_COMMANDS`	Commands are not shared with any other connection. See "PrivateCommands" in Oracle TimesTen In-Memory Database Reference.
`TT_STMT_PASSTHROUGH_TYPE`	Determines whether a specific prepared statement is passed through to Oracle Database by the passthrough feature of TimesTen Cache. The value returned by `SQLGetStmtOption` can be either `TT_STMT_PASSTHROUGH_NONE` or `TT_STMT_PASSTHROUGH_ORACLE`. Note: In TimesTen, this option is supported only with `SQLGetStmtOption`. See "Determining passthrough status". Also see "Setting a passthrough level" in Oracle TimesTen Application-Tier Database Cache User's Guide.

Information type support for SQL GetInfo

This section covers standard and TimesTen-specific information types supported by TimesTen for the ODBC function SQLGetInfo.

Refer to ODBC API reference documentation for general information about this function and standard information types.

TimesTen supports the following standard ODBC 2.x information types (in alphabetical order):

 SQL_ACCESSIBLE_PROCEDURES, SQL_ACCESSIBLE_TABLES, SQL_ACTIVE_CONNECTIONS,
 SQL_ACTIVE_STATEMENTS, SQL_ALTER_TABLE, SQL_BOOKMARK_PERSISTENCE,
 SQL_COLUMN_ALIAS, SQL_CONCAT_NULL_BEHAVIOR, SQL_CONVERT_BIGINT,
 SQL_CONVERT_BINARY, SQL_CONVERT_BIT, SQL_CONVERT_CHAR, SQL_CONVERT_DATE,
 SQL_CONVERT_DECIMAL, SQL_CONVERT_DOUBLE, SQL_CONVERT_FLOAT,
 SQL_CONVERT_FUNCTIONS, SQL_CONVERT_INTEGER, SQL_CONVERT_LONGVARBINARY,
 SQL_CONVERT_LONGVARCHAR, SQL_CONVERT_NUMERIC, SQL_CONVERT_REAL,
 SQL_CONVERT_SMALLINT, SQL_CONVERT_TIME, SQL_CONVERT_TIMESTAMP,
 SQL_CONVERT_TINYINT, SQL_CONVERT_VARBINARY, SQL_CONVERT_VARCHAR,
 SQL_CONVERT_WVARCHAR, SQL_CORRELATION_NAME, SQL_CURSOR_COMMIT_BEHAVIOR,
 SQL_CURSOR_ROLLBACK_BEHAVIOR, SQL_DATA_SOURCE_NAME, SQL_DATA_SOURCE_READ_ONLY,
 SQL_DATABASE_NAME, SQL_DBMS_NAME, SQL_DBMS_VER, SQL_DEFAULT_TXN_ISOLATION,
 SQL_DRIVER_HDBC, SQL_DRIVER_HENV, SQL_DRIVER_HLIB, SQL_DRIVER_HSTMT,
 SQL_DRIVER_NAME, SQL_DRIVER_ODBC_VER, SQL_DRIVER_VER, SQL_EXPRESSIONS_IN_ORDERBY,
 SQL_FETCH_DIRECTION, SQL_FILE_USAGE, SQL_GETDATA_EXTENSIONS, SQL_GROUP_BY,
 SQL_IDENTIFIER_CASE, SQL_IDENTIFIER_QUOTE_CHAR, SQL_KEYWORDS,
 SQL_LIKE_ESCAPE_CLAUSE, SQL_LOCK_TYPES, SQL_MAX_BINARY_LITERAL_LEN,
 SQL_MAX_CHAR_LITERAL_LEN, SQL_MAX_COLUMN_NAME_LEN, SQL_MAX_COLUMNS_IN_GROUP_BY,
 SQL_MAX_COLUMNS_IN_INDEX, SQL_MAX_COLUMNS_IN_ORDER_BY, SQL_MAX_COLUMNS_IN_SELECT,
 SQL_MAX_COLUMNS_IN_TABLE, SQL_MAX_CURSOR_NAME_LEN, SQL_MAX_INDEX_SIZE,
 SQL_MAX_OWNER_NAME_LEN, SQL_MAX_PROCEDURE_NAME_LEN, SQL_MAX_QUALIFIER_NAME_LEN,
 SQL_MAX_ROW_SIZE, SQL_MAX_ROW_SIZE_INCLUDES_LONG, SQL_MAX_STATEMENT_LEN,
 SQL_MAX_TABLE_NAME_LEN, SQL_MAX_TABLES_IN_SELECT, SQL_MAX_USER_NAME_LEN,
 SQL_MULT_RESULT_SETS, SQL_MULTIPLE_ACTIVE_TXN, SQL_NEED_LONG_DATA_LEN,
 SQL_NON_NULLABLE_COLUMNS, SQL_NULL_COLLATION, SQL_NUMERIC_FUNCTIONS,
 SQL_ODBC_API_CONFORMANCE, SQL_ODBC_SAG_CLI_CONFORMANCE, SQL_ODBC_SQL_CONFORMANCE,
 SQL_ODBC_SQL_OPT_IEF, SQL_ODBC_VER, SQL_OJ_CAPABILITIES,
 SQL_ORDER_BY_COLUMNS_IN_SELECT, SQL_OUTER_JOINS, SQL_OWNER_TERM, SQL_OWNER_USAGE,
 SQL_POS_OPERATIONS, SQL_POSITIONED_STATEMENTS, SQL_PROCEDURE_TERM,
 SQL_PROCEDURES, SQL_QUALIFIER_LOCATION, SQL_QUALIFIER_NAME_SEPARATOR,
 SQL_QUALIFIER_TERM, SQL_QUALIFIER_USAGE, SQL_QUOTED_IDENTIFIER_CASE,
 SQL_ROW_UPDATES, SQL_SCROLL_CONCURRENCY, SQL_SCROLL_OPTIONS,
 SQL_SEARCH_PATTERN_ESCAPE, SQL_SERVER_NAME, SQL_SPECIAL_CHARACTERS,
 SQL_STATIC_SENSITIVITY, SQL_STRING_FUNCTIONS, SQL_SUBQUERIES,
 SQL_SYSTEM_FUNCTIONS, SQL_TABLE_TERM, SQL_TIMEDATE_ADD_INTERVALS,
 SQL_TIMEDATE_DIFF_INTERVALS, SQL_TIMEDATE_FUNCTIONS, SQL_TXN_CAPABLE,
 SQL_TXN_ISOLATION_OPTION, SQL_UNION, SQL_USER_NAME

Note:

SQL_DRIVER_HLIB is supported with a driver manager only.

TimesTen supports the following standard ODBC 3.x information types:

 SQL_AGGREGATE_FUNCTIONS, SQL_CONVERT_WCHAR, SQL_CONVERT_WLONGVARCHAR,
 SQL_CREATE_VIEW, SQL_DATETIME_LITERALS, SQL_DROP_VIEW,
 SQL_SQL92_RELATIONAL_JOIN_OPERATORS, SQL_SQL92_VALUE_EXPRESSIONS

Table 10-6 describes TimesTen-specific information types.

Table 10-6 TimesTen information items: SQLGetInfo

Information type	Data type	Description
`TT_DATA_STORE_INVALID`	`SQLINTEGER`	Returns 1 if the database is in invalid state, such as due to a system or application failure, or 0 if not. Note: Fatal errors, such as error 846 or 994, invalidate a TimesTen database, causing this item to be set to 1.
`TT_DATABASE_CHARACTER_SET`	`SQLCHAR`	Returns the name of the database character set.
`TT_DATABASE_CHARACTER_SET_SIZE`	`SQLINTEGER`	Returns the maximum size of a character in the database character set, in bytes.
`TT_DATABASE_TYPE_MODE`	`SQLINTEGER`	Returns 0 for Oracle type mode (typical and default setting), or 1 for TimesTen type mode (legacy setting). Note: The type mode can be specified through the `TypeMode` connection attribute.
`TT_PLATFORM_INFO`	Bit mask	Returns a bit mask indicating platform information. Bit 0 has the value 1 for a 64-bit platform, or the value 0 for a 32-bit platform. Bit 1 has the value 1 for big-endian, or the value 0 for little-endian.
`TT_REPLICATION_INVALID`	`SQLINTEGER`	Returns 1 if replication is in a failed state, or 0 if not. Note: If `TT_REPLICATION_INVALID=1` on a subscriber or standby database, the replication agent shuts down due to the fact that the subscriber or standby is no longer receiving updates. In a bidirectional configuration, because the replication agent is not running, the `FAILTHRESHOLD` is not honored. To resolve this situation, destroy the subscriber or standby database and recreate it using the `ttRepAdmin -duplicate` option. For additional information, see "Subscriber failures" in Oracle TimesTen In-Memory Database Replication Guide.

Column descriptor support for SQLColAttributes

This section covers TimesTen-specific column descriptor information supported for the ODBC function SQLColAttributes.

Refer to ODBC API reference documentation for general information about this function and standard information types.

Table 10-7 describes TimesTen-specific column descriptors.

Table 10-7 TimesTen column descriptors: SQLColAttributes

Descriptor Comment/description

Descriptor	Comment/description
`TT_COLUMN_INLINE`	Returns `TRUE` for columns with inline data, or `FALSE` otherwise. This is returned in the `SQLColAttributes` `pfDesc` parameter.
`TT_COLUMN_LENGTH_SEMANTICS`	For character-type columns, this returns "BYTE" for columns with byte length semantics and "CHAR" for columns with character length semantics. For non-character columns, it returns "". The information is returned in the `SQLColAttributes` `rgbDesc` parameter. This information refers to whether data length is measured in bytes or characters. Length semantics in TimesTen are the same as in Oracle Database. See "Length Semantics" in Oracle Database Globalization Support Guide for additional information.

TT_COLUMN_INLINE

Returns TRUE for columns with inline data, or FALSE otherwise. This is returned in the SQLColAttributes pfDesc parameter.

TT_COLUMN_LENGTH_SEMANTICS

For character-type columns, this returns "BYTE" for columns with byte length semantics and "CHAR" for columns with character length semantics. For non-character columns, it returns "". The information is returned in the SQLColAttributes rgbDesc parameter.

This information refers to whether data length is measured in bytes or characters. Length semantics in TimesTen are the same as in Oracle Database. See "Length Semantics" in Oracle Database Globalization Support Guide for additional information.