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, with additional features for Extension Level 2 as indicated 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

SQLAllocConnect

  

SQLAllocEnv

  

SQLAllocStmt

  

SQLBindCol

  

SQLBindParameter

See "SQLBindParameter function".

SQLCancel

In TimesTen, SQLCancel cannot cancel a function running asynchronously on the hstmt or one running on the hstmt on another thread.

SQLColAttributes

See "Column descriptor support for SQLColAttributes".

SQLColumns

  

SQLConnect

  

SQLDataSources

Available only to programs using a driver manager.

SQLDescribeCol

  

SQLDescribeParam

  

SQLDisconnect

  

SQLDriverConnect

  

SQLDrivers

Available only to programs using a driver manager.

SQLError

  

SQLExecDirect

  

SQLExecute

  

SQLFetch

  

SQLForeignKeys

  

SQLFreeConnect

  

SQLFreeEnv

  

SQLFreeStmt

  

SQLGetConnectOption

See "Option support for SQLSetConnectOption and SQLGetConnectOption".

SQLGetCursorName

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.

SQLGetData

  

SQLGetFunctions

  

SQLGetInfo

See "Information type support for SQLGetInfo".

SQLGetStmtOption

See "Option support for SQLSetStmtOption and SQLGetStmtOption".

SQLGetTypeInfo

  

SQLNativeSql

  

SQLNumParams

  

SQLNumResultCols

  

SQLParamData

  

SQLParamOptions

  

SQLPrepare

  

SQLPrimaryKeys

  

SQLProcedureColumns

  

SQLProcedures

  

SQLPutData

  

SQLRowCount

In addition to its standard functionality, this has special usage with cache groups. See "Managing cache groups".

SQLSetConnectOption

See "Option support for SQLSetConnectOption and SQLGetConnectOption".

SQLSetCursorName

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

ODBC 1.0 function, replaced by SQLBindParameter in ODBC 2.0. Retained for backward compatibility.

SQLSpecialColumns

  

SQLStatistics

  

SQLTables

  

SQLTransact

  

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.

Note:

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

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

Available only to programs using a driver manager.

SQL_OPT_TRACE

Available only to programs using a driver manager.

SQL_OPT_TRACEFILE

Available only to programs using a driver manager.

SQL_PACKET_SIZE

No

SQL_QUIET_MODE

No

SQL_TRANSLATE_DLL

No

SQL_TRANSLATE_OPTION

No

SQL_TXN_ISOLATION

Supported only 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 Comment/description

TT_CLIENT_TIMEOUT

For client/server only. 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 In-Memory Database Cache User's Guide. This has the same functionality as the DynamicLoadEnable IMDB 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 In-Memory Database Cache User's Guide. Same functionality as the DynamicLoadErrorMode IMDB Cache connection attribute described in "DynamicLoadErrorMode" in Oracle TimesTen In-Memory Database Reference.

TT_NLS_LENGTH_SEMANTICS

See "Setting globalization options". 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". 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". 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".

TT_REPLICATION_TRACK

See "Setting up user-specified parallel replication". Same functionality as the ReplicationTrack general connection attribute, to specify a track number for use with parallel replication for the connection, as described in "ReplicationTrack" in Oracle TimesTen In-Memory Database Reference.

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.

Note:

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 will be 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/description

TT_PREFETCH_COUNT

See "Prefetching multiple rows of data".

TT_QUERY_THRESHOLD

See "Setting a threshold value for SQL statements". This is to specify a time threshold for SQL statements, in seconds, after which TimesTen will write a warning to the support log and throw 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 will be passed through to Oracle by the passthrough feature of IMDB 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 In-Memory 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.

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.