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

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 SQLSetConnectOption 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.


Option 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 SQLGetInfo

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

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.