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

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