TimesTen Features and Operations in Your Application

TimesTen Include Files

This section lists files you must include from your code in order to use TimesTen features. They are located in the include directory of the TimesTen installation.

Set the include path appropriately to access any files that are to be included. See Compiling and Linking Applications.

Include File Description

Include File	Description
`timesten.h`	TimesTen ODBC features This file includes the appropriate version of `sql.h`: the TimesTen version on Linux or UNIX systems or the system version on Windows systems. This file also includes `sqltypes.h`, `sqlext.h`, and `sqlucode.h`. On Windows systems, it also includes `windows.h`.
`tt_errCode.h`	TimesTen error codes (optional—see notes) This file maps TimesTen error codes to defined constants.

timesten.h

TimesTen ODBC features

This file includes the appropriate version of sql.h: the TimesTen version on Linux or UNIX systems or the system version on Windows systems.

This file also includes sqltypes.h, sqlext.h, and sqlucode.h. On Windows systems, it also includes windows.h.

tt_errCode.h

TimesTen error codes (optional—see notes)

This file maps TimesTen error codes to defined constants.

Note:

If you include sql.h directly (instead of through timesten.h), on Windows you must include the system version of sql.h, not the TimesTen version.
Type definitions previously in sqlunix.h are now in sqltypes.h; however, sqlunix.h still exists (as an empty file) for backward compatibility.
There are alternatives to including tt_errCode.h. One is to move any desired constant definitions to timesten.h. Another is to reference the corresponding integer values directly in your code.

TimesTen Deferred Prepare

TimesTen has a deferred prepare feature to reduce round trips to the database.

In standard ODBC, a SQLPrepare call compiles a SQL statement so that information about the statement, such as column descriptions for the result set, is available to the application and accessible through calls such as SQLDescribeCol. To accomplish this, the SQLPrepare call must communicate with the server for processing.

This is in contrast, for example, to expected behavior under Oracle Call Interface (OCI), where a prepare call is expected to be a lightweight operation performed on the client to simply extract names and positions of parameters.

To avoid unwanted round trips between client and server, and also to make the behavior consistent with OCI expectations, the TimesTen client library implementation of SQLPrepare performs what is referred to as a "deferred prepare", where the request is not sent to the server until required. Examples of when the round trip would be required:

When there is a SQLExecute call. Note that if there is a deferred prepare call that has not yet been sent to the server, a SQLExecute call on the client is converted to a SQLExecDirect call.
When there is a request for information about the query that can only be supplied by the SQL engine, such as when there is a SQLDescribeCol call, for example. Many such calls in standard ODBC can access information previously returned by a SQLPrepare call, but with the deferred prepare functionality the SQLPrepare call is sent to the server and the information is returned to the application only as needed.

Note:

Deferred prepare functionality is not implemented (and not necessary) with the TimesTen direct driver.

The deferred prepare implementation requires no changes at the application or user level; however, be aware that calling any of the following functions may result in a round trip to the server if the required information from a previously prepared statement has not yet been retrieved:

SQLColAttributes
SQLDescribeCol
SQLDescribeParam
SQLNumResultCols
SQLNumParams
SQLGetStmtOption (for options that depend on the statement having been compiled by the SQL engine)

Also be aware that when calling any of these functions, any error from an earlier SQLPrepare call may be deferred until one of these calls is executed. In addition, these calls may return errors specific to SQLPrepare as well as errors specific to themselves.

Prefetching Multiple Rows of Data

A TimesTen extension to ODBC enables applications to prefetch multiple rows of data into the ODBC driver buffer. This can improve performance of client/server applications.

The TT_PREFETCH_COUNT ODBC statement option determines how many rows a SQLFetch call prefetches. Note that this option provides no benefit for an application using a direct connection to TimesTen.

You can set TT_PREFETCH_COUNT in a call to either SQLSetStmtOption or SQLSetConnectOption (which sets the option default value for all statements associated with the connection). The value can be any integer from 0 to 128, inclusive. Following is an example.

rc = SQLSetConnectOption(hdbc, TT_PREFETCH_COUNT, 100);

With this setting, the first SQLFetch call on the connection prefetches 100 rows. Subsequent SQLFetch calls fetch from the ODBC buffer instead of from the database, until the buffer is depleted. After it is depleted, the next SQLFetch call fetches another 100 rows into the buffer, and so on.

To disable prefetch, set TT_PREFETCH_COUNT to 1.

When you set the prefetch count to 0, TimesTen uses a default prefetch count according to the isolation level you have set for the database, and sets TT_PREFETCH_COUNT to that value. With Read Committed isolation level, the default prefetch value is 5. With Serializable isolation level, the default is 128. The default prefetch value is a good setting for most applications. Generally, a higher value may result in better performance for larger result sets, at the expense of slightly higher resource use.

You can also see Attribute Support for ODBC 3.5 SQLSetStmtAttr and SQLGetStmtAttr.

Optimizing Query Performance

A TimesTen extension to ODBC enables applications to optimize read-only query performance in client/server applications by using the TT_PREFETCH_CLOSE ODBC connection option.

Set TT_PREFETCH_CLOSE to TT_PREFETCH_CLOSE_ON using SQLSetConnectOption.

All transactions should be committed when executed, including read-only transactions. When TT_PREFETCH_CLOSE is set to TT_PREFETCH_CLOSE_ON, the server automatically closes the cursor and commits the transaction after the server has prefetched all rows of the result set for a read-only query. This enhances performance by reducing the number of network round-trips between client and server.

The client should still free the statement with SQLFreeStmt(SQL_CLOSE) and commit the transaction with SQLTransact(SQL_COMMIT), but those calls are executed in the client and do not require a network round trip between the client and server.

Note:

Do not use multiple statement handles for the same connection when TT_PREFETCH_CLOSE is set to TT_PREFETCH_CLOSE_ON. The server may fetch all of the result set, commit the transaction, and close the statement handle before the client is finished, resulting in the closing of all statement handles.
This option is ignored for TimesTen direct connections and for SELECT FOR UPDATE statements.

The following example shows how to use the TT_PREFETCH_CLOSE option.

SQLSetConnectOption (hdbc, TT_PREFETCH_CLOSE, TT_PREFETCH_CLOSE_ON);
SQLExecDirect (hstmt, "SELECT * FROM T", SQL_NTS);
while (SQLFetch (hstmt) != SQL_NO_DATA_FOUND)
{
// do the processing and error checking
}
SQLFreeStmt (hstmt, SQL_CLOSE);
SQLTransact(SQL_COMMIT);

Parameter Binding and Statement Execution

There are methods for how to bind input or output parameters for SQL statements.

The following topics are covered.

Note:

The term "bind parameter" as used in TimesTen developer guides (in keeping with ODBC terminology) is equivalent to the term "bind variable" as used in TimesTen PL/SQL documents (in keeping with Oracle Database PL/SQL terminology).

SQLBindParameter Function

The ODBC SQLBindParameter function is used to bind parameters for SQL statements. This could include input, output, or input/output parameters.

To bind an input parameter through ODBC, use the SQLBindParameter function with a setting of SQL_PARAM_INPUT for the fParamType argument. Refer to ODBC API reference documentation for details about the SQLBindParameter function. Table 2-1 provides a brief summary of its arguments.

To bind an output or input/output parameter through ODBC, use the SQLBindParameter function with a setting of SQL_PARAM_OUTPUT or SQL_PARAM_INPUT_OUTPUT, respectively, for the fParamType argument. As with input parameters, use the fSqlType, cbColDef, and ibScale arguments (as applicable) to specify data types.

Table 2-1 SQLBindParameter Arguments

Argument	Type	Description
`hstmt`	`SQLHSTMT`	Statement handle
`ipar`	`SQLUSMALLINT`	Parameter number, sequentially from left to right, starting with 1
`fParamType`	`SQLSMALLINT`	Indicating input or output: `SQL_PARAM_INPUT`, `SQL_PARAM_OUTPUT`, or `SQL_PARAM_INPUT_OUTPUT`
`fCType`	`SQLSMALLINT`	C data type of the parameter
`fSqlType`	`SQLSMALLINT`	SQL data type of the parameter
`cbColDef`	`SQLULEN`	The precision of the parameter, such as the maximum number of bytes for binary data, the maximum number of digits for a number, or the maximum number of characters for character data
`ibScale`	`SQLSMALLINT`	The scale of the parameter, referring to the maximum number of digits to the right of the decimal point, where applicable
`rgbValue`	`SQLPOINTER`	Pointer to a buffer for the data of the parameter
`cbValueMax`	`SQLLEN`	Maximum length of the `rgbValue` buffer, in bytes
`pcbValue`	`SQLLEN*`	Pointer to a buffer for the length of the parameter

Note:

Refer to Data Types in Oracle TimesTen In-Memory Database SQL Reference.

Parameter Type Assignments and Type Conversions

Bind parameter type assignments are decided by different entities depending on where they are executed. Type conversions are performed by the ODBC driver.

This section discusses bind parameter type assignments, which are determined as follows:

Parameter type assignments for statements that execute in TimesTen are determined by TimesTen. Specifically:
- For SQL statements that execute within TimesTen, the TimesTen query optimizer determines data types of SQL parameters.
Parameter type assignments for statements that execute in Oracle Database, or according to Oracle Database functionality, are determined by the application as follows.
- For SQL statements that execute within Oracle Database—that is, passthrough statements from cache—the application must specify data types through its calls to the ODBC SQLBindParameter function, according to the fSqlType, cbColDef, and ibScale arguments of that function, as applicable.
- For PL/SQL blocks or procedures that execute within TimesTen, where the PL/SQL execution engine has the same basic functionality as in Oracle Database, the application must specify data types through its calls to SQLBindParameter (the same as for SQL statements that execute within Oracle Database).
  
  So regarding host binds for PL/SQL (the variables, or parameters, that are preceded by a colon within a PL/SQL block), note that the type of a host bind is effectively declared by the call to SQLBindParameter, according to fSqlType and the other arguments as applicable, and is not declared within the PL/SQL block.

The ODBC driver performs any necessary type conversions between C values and SQL or PL/SQL types. For any C-to-SQL or C-to-PL/SQL combination that is not supported, an error occurs. These conversions can be from a C type to a SQL or PL/SQL type (input parameter), from a SQL or PL/SQL type to a C type (output parameter), or both (input/output parameter).

See the next section for information about type mappings between ODBC and TimesTen.

Note:

The TimesTen binding mechanism (early binding) differs from that of Oracle Database (late binding). TimesTen requires the data types before preparing queries. As a result, there will be an error if the data type of each bind parameter is not specified or cannot be inferred from the SQL statement. This would apply, for example, to the following statement:

SELECT 'x' FROM DUAL WHERE ? = ?;

You could address the issue as follows, for example:

SELECT 'x' from DUAL WHERE CAST(? as VARCHAR2(10)) = 
                           CAST(? as VARCHAR2(10));

ODBC SQL to TimesTen SQL or PL/SQL Type Mappings

There are mappings from ODBC SQL to TimesTen SQL or PL/SQL.

Table 2-2 documents the mapping between ODBC types and SQL or PL/SQL types.

Table 2-2 ODBC SQL to TimesTen SQL or PL/SQL Type Mappings

ODBC Type (fSqlType)	SQL or PL/SQL Type	TimesTen Support Notes
`SQL_BIGINT`	`NUMBER`	No notes
`SQL_BINARY`	`RAW(p)`	No notes
`SQL_BIT`	`PLS_INTEGER`	No notes
`SQL_CHAR`	`CHAR(p)`	No notes
`SQL_DATE`	`DATE`	No notes
`SQL_DECIMAL`	`NUMBER`	No notes
`SQL_DOUBLE`	`NUMBER`	No notes
`SQL_FLOAT`	`BINARY_DOUBLE`	No notes
`SQL_INTEGER`	`PLS_INTEGER`	No notes
`SQL_INTERVAL_DAY`	N/A	See notes after this table.
`SQL_INTERVAL_DAY_TO_HOUR`	N/A	See notes after this table.
`SQL_INTERVAL_DAY_TO_MINUTE`	N/A	See notes after this table.
`SQL_INTERVAL_DAY_TO_SECOND`	N/A	See notes after this table.
`SQL_INTERVAL_HOUR`	N/A	See notes after this table.
`SQL_INTERVAL_HOUR_TO_MINUTE`	N/A	See notes after this table.
`SQL_INTERVAL_HOUR_TO_SECOND`	N/A	See notes after this table.
`SQL_INTERVAL_MINUTE`	N/A	See notes after this table.
`SQL_INTERVAL_MINUTE_TO_SECOND`	N/A	See notes after this table.
`SQL_INTERVAL_MONTH`	N/A	See notes after this table.
`SQL_INTERVAL_YEAR`	N/A	See notes after this table.
`SQL_INTERVAL_YEAR_TO_MONTH`	N/A	See notes after this table.
`SQL_INTERVAL_SECOND`	N/A	See notes after this table.
`SQL_NUMERIC`	`NUMBER`	No notes
`SQL_REAL`	`BINARY_FLOAT`	No notes
`SQL_REFCURSOR`	`REF CURSOR`	No notes
`SQL_ROWID`	`ROWID`	No notes
`SQL_SMALLINT`	`PLS_INTEGER`	No notes
`SQL_TIME`	`TIME`	TimesTen does not support `TIMEZONE`. `TIME` data type values are stored without making any adjustment for time difference. Applications must assume one time zone and convert `TIME` values to that time zone before sending the values to the database.
`SQL_TIMESTAMP`	`TIMESTAMP(s)`	Same consideration as for `TIME`.
`SQL_TINYINT`	`PLS_INTEGER`	No notes
`SQL_VARBINARY`	`RAW(p)`	No notes
`SQL_VARCHAR`	`VARCHAR2(p)`	No notes
`SQL_WCHAR`	`NCHAR(p)`	No notes
`SQL_WVARCHAR`	`NVARCHAR2(p)`	No notes

Note:

The notation (p) indicates precision is according to the SQLBindParameter argument cbColDef.
The notation (s) indicates scale is according to the SQLBindParameter argument ibScale.
The SQL_INTERVAL_xxxx types are supported only for computing values, such as in SQL expressions, not as database column types.
Most applications should use SQL_VARCHAR rather than SQL_CHAR for binding character data. Use of SQL_CHAR may result in unwanted space padding to the full precision of the parameter type.
Regarding TIME and TIMESTAMP, for example, an application can assume its time zone to be Pacific Standard Time. If the application is using TIME and TIMESTAMP values from Pacific Daylight Time or Eastern Standard Time, for example, the application must convert TIME and TIMESTAMP to Pacific Standard Time.

Binding Input Parameters

To bind input parameters to PL/SQL in TimesTen, use the fSqlType, cbColDef, and ibScale arguments (as applicable) of the ODBC SQLBindParameter function to specify data types.

This is in contrast to how SQL input parameters are supported, as noted in Parameter Type Assignments and Type Conversions.

In addition, use the rgbValue, cbValueMax, and pcbValue arguments of SQLBindParameter as follows for input parameters:

rgbValue: Before statement execution, points to the buffer where the application places the parameter value to be passed to the application.
cbValueMax: For character and binary data, indicates the maximum length of the incoming value that rgbValue points to, in bytes. For all other data types, cbValueMax is ignored, and the length of the value that rgbValue points to is determined by the length of the C data type specified in the fCType argument of SQLBindParameter.
pcbValue: Points to a buffer that contains one of the following before statement execution:
- The actual length of the value that rgbValue points to
  
  Note: For input parameters, this would be valid only for character or binary data.
- SQL_NTS for a null-terminated string
- SQL_NULL_DATA for a null value

Binding Output Parameters

To bind output parameters from PL/SQL in TimesTen, as noted for input parameters previously, use the fSqlType, cbColDef, and ibScale arguments (as applicable) of the ODBC SQLBindParameter function to specify data types.

In addition, use the rgbValue, cbValueMax, and pcbValue arguments of SQLBindParameter as follows for output parameters:

rgbValue: During statement execution, points to the buffer where the value returned from the statement should be placed.
cbValueMax: For character and binary data, indicates the maximum length of the outgoing value that rgbValue points to, in bytes. For all other data types, cbValueMax is ignored, and the length of the value that rgbValue points to is determined by the length of the C data type specified in the fCType argument of SQLBindParameter.

Note that ODBC null-terminates all character data, even if the data is truncated. Therefore, when an output parameter has character data, cbValueMax must be large enough to accept the maximum data value plus a null terminator (one additional byte for CHAR and VARCHAR parameters, or two additional bytes for NCHAR and NVARCHAR parameters).
pcbValue: Points to a buffer that contains one of the following after statement execution:
- The actual length of the value that rgbValue points to (for all C types, not just character and binary data)
  
  Note: This is the length of the full parameter value, regardless of whether the value can fit in the buffer that rgbValue points to.
- SQL_NULL_DATA for a null value

The following example shows how to prepare, bind, and execute a PL/SQL anonymous block.

The anonymous block assigns bind parameter a the value 'abcde' and bind parameter b the value 123.
SQLPrepare prepares the anonymous block.
SQLBindParameter binds the first parameter (a) as an output parameter of type SQL_VARCHAR and binds the second parameter (b) as an output parameter of type SQL_INTEGER.
SQLExecute executes the anonymous block.

{
  SQLHSTMT      hstmt;
  char          aval[11];
  SQLLEN        aval_len;
  SQLINTEGER    bval;
  SQLLEN        bval_len;
 
  SQLAllocStmt(hdbc, &hstmt);
 
  SQLPrepare(hstmt,
        (SQLCHAR*)"begin :a := 'abcde'; :b := 123; end;",
        SQL_NTS);
 
  SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR,
         10, 0, (SQLPOINTER)aval, sizeof(aval), &aval_len);
 
  SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER,
         0, 0, (SQLPOINTER)&bval, sizeof(bval), &bval_len);
 
  SQLExecute(hstmt);
  printf("aval = [%s] (length = %d), bval = %d\n", aval, (int)aval_len, bval);
}

Binding Input/Output Parameters

To bind input/output parameters to and from PL/SQL in TimesTen, as noted for input and output parameters previously, use the fSqlType, cbColDef, and ibScale arguments (as applicable) of the ODBC SQLBindParameter function to specify data types.

In addition, use the rgbValue, cbValueMax, and pcbValue arguments of SQLBindParameter as follows for input/output parameters:

rgbValue: This is first used before statement execution as described in Binding Input Parameters. Then it is used during statement execution as described in the preceding section, Binding Output Parameters. Note that for an input/output parameter, the outgoing value from a statement execution is the incoming value to the statement execution that immediately follows, unless that is overridden by the application. Also, for input/output values bound when you are using data-at-execution, the value of rgbValue serves as both the token that would be returned by the ODBC SQLParamData function and as the pointer to the buffer where the outgoing value is placed.
cbValueMax: For character and binary data, this is first used as described in Binding Input Parameters. Then it is used as described in the preceding section, Binding Output Parameters. For all other data types, cbValueMax is ignored, and the length of the value that rgbValue points to is determined by the length of the C data type specified in the fCType argument of SQLBindParameter.

Note that ODBC null-terminates all character data, even if the data is truncated. Therefore, when an input/output parameter has character data, cbValueMax must be large enough to accept the maximum data value plus a null terminator (one additional byte for CHAR and VARCHAR parameters, or two additional bytes for NCHAR and NVARCHAR parameters).
pcbValue: This is first used before statement execution as described in Binding Input Parameters. Then it is used after statement execution as described in the preceding section, Binding Output Parameters.

Tip:

For character and binary data, carefully consider the value you use for cbValueMax. A value that is smaller than the actual buffer size may result in spurious truncation warnings. A value that is greater than the actual buffer size may cause the ODBC driver to overwrite the rgbValue buffer, resulting in memory corruption.

Binding of Duplicate Parameters in SQL Statements

TimesTen handles duplicate parameters in SQL. In TimesTen, multiple occurrences of the same parameter name in a SQL statement are considered to be distinct parameters. (This is consistent with Oracle Database support for binding duplicate parameters.)

Note:

This discussion applies only to SQL statements issued directly from ODBC, not through PL/SQL, for example. (Regarding PL/SQL statements, see the next section Binding of Duplicate Parameters in PL/SQL Statements.)
"TimesTen mode" for binding duplicate parameters, and the DuplicateBindMode connection attribute, are deprecated.
The use of "?" for parameters, not supported in Oracle Database, is supported by TimesTen.

Consider this query:

SELECT * FROM employees
  WHERE employee_id < :a AND manager_id > :a AND salary < :b;

When parameter position numbers are assigned, a number is given to each parameter occurrence without regard to name duplication. The application must, at a minimum, bind a value for the first occurrence of each parameter name. For any subsequent occurrence of a given parameter name, the application has the following choices.

It can bind a different value for the occurrence.
It can leave the parameter occurrence unbound, in which case it takes the same value as the first occurrence.

In either case, each occurrence has a distinct parameter position number.

To use a different value for the second occurrence of a in the SQL statement above:

SQLBindParameter(..., 1, ...); /* first occurrence of :a */
SQLBindParameter(..., 2, ...); /* second occurrence of :a */
SQLBindParameter(..., 3, ...); /* occurrence of :b */

To use the same value for both occurrences of a:

SQLBindParameter(..., 1, ...); /* both occurrences of :a */
SQLBindParameter(..., 3, ...); /* occurrence of :b */

Parameter b is considered to be in position 3 regardless.

The SQLNumParams ODBC function returns 3 for the number of parameters in the example.

Binding of Duplicate Parameters in PL/SQL Statements

TimesTen handles duplicate parameters in PL/SQL. In PL/SQL, you bind a value for each unique parameter name. An application executing the following block, for example, would bind only one parameter, corresponding to :a.

Discussion in the preceding section, Binding of Duplicate Parameters in SQL Statements, does not apply to PL/SQL, which has its own semantics.

DECLARE
   x NUMBER;
   y NUMBER;
BEGIN
   x:=:a;
   y:=:a;
END;

An application executing the following block would also bind only one parameter:

BEGIN
   INSERT INTO tab1 VALUES(:a, :a);
END

And the same for the following CALL statement:

...CALL proc(:a, :a)...

An application executing the following block would bind two parameters, with :a as the first parameter and :b as the second parameter. The second parameter in each INSERT statement would take the same value as the first parameter in the first INSERT statement:

BEGIN
   INSERT INTO tab1 VALUES(:a, :a);
   INSERT INTO tab1 VALUES(:b, :a);
END

Considerations for Floating Point Data

There are considerations for floating point data.

The BINARY_DOUBLE and BINARY_FLOAT data types store and retrieve the IEEE floating point values Inf, -Inf, and NaN. If an application uses a C language facility such as printf, scanf, or strtod that requires conversion to character data, the floating point values are returned as "INF", "-INF", and "NAN". These character strings cannot be converted back to floating point values.

Using SQL_WCHAR and SQL_WVARCHAR With a Driver Manager

This section discusses how to avoid possible error conditions when using SQL_WCHAR or SQL_WVARCHAR with a driver manager.

Applications using the Windows driver manager may encounter errors from SQLBindParameter with SQL state S1004 (SQL data type out of range) when passing an fSqlType value of SQL_WCHAR or SQL_WVARCHAR. This problem can be avoided by passing one of the following values for fSqlType instead.

SQL_WCHAR_DM_SQLBINDPARAMETER_BYPASS instead of SQL_WCHAR
SQL_WVARCHAR_DM_SQLBINDPARAMETER_BYPASS instead of SQL_WVARCHAR

These type codes are semantically identical to SQL_WCHAR and SQL_WVARCHAR but avoid the error from the Windows driver manager. They can be used in applications that link with the driver manager or link directly with the TimesTen ODBC direct driver or ODBC client driver.

See SQLBindParameter Function.

Working With REF CURSORs

REF CURSOR is a PL/SQL concept, a handle to a cursor over a SQL result set that can be passed between PL/SQL and an application. In TimesTen, the cursor can be opened in PL/SQL then the REF CURSOR can be passed to the application. The results can be processed in the application using ODBC calls.

This is an OUT REF CURSOR (an OUT parameter with respect to PL/SQL). The REF CURSOR is attached to a statement handle, enabling applications to describe and fetch result sets using the same APIs as for any result set.

Take the following steps to use a REF CURSOR. Assume a PL/SQL statement that returns a cursor through a REF CURSOR OUT parameter. Note that REF CURSORs use the same basic steps of prepare, bind, execute, and fetch as in the cursor example in Steps to Prepare and Execute Queries and Work With Cursors in ODBC.

Prepare the PL/SQL statement, using SQLPrepare, to be associated with the first statement handle.
Bind each parameter of the statement, using SQLBindParameter. When binding the REF CURSOR output parameter, use an allocated second statement handle as rgbValue, the pointer to the data buffer.

The pcbValue, ibScale, cbValueMax, and pcbValue arguments are ignored for REF CURSORs.

See SQLBindParameter Function and Binding Output Parameters.
Call SQLBindCol to bind result columns to local variable storage.
Call SQLExecute to execute the statement.
Call SQLFetch to fetch the results. After a REF CURSOR is passed from PL/SQL to an application, the application can describe and fetch the results as it would for any result set.
Use SQLFreeStmt to free the statement handle.

These steps are demonstrated in the example that follows. Refer to ODBC API reference documentation for details on these functions. See PL/SQL REF CURSORs in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.

Note:

For passing REF CURSORs between PL/SQL and an application, TimesTen supports only OUT REF CURSORs, from PL/SQL to the application.

This example, using a REF CURSOR in a loop, demonstrates the basic steps of preparing a query, binding parameters, executing the query, binding results to local variable storage, and fetching the results. Error handling is omitted for simplicity. In addition to the ODBC functions summarized earlier, this example uses SQLAllocStmt to allocate memory for a statement handle.

refcursor_example(SQLHDBC hdbc)
{
  SQLCHAR*      stmt_text;
  SQLHSTMT      plsql_hstmt;
  SQLHSTMT      refcursor_hstmt;
  SQLINTEGER    deptid;
  SQLINTEGER    depts[3] = {10,30,40};
  SQLINTEGER    empid;
  SQLCHAR       lastname[30];
  SQLINTEGER    i;
 
  /* allocate 2 statement handles: one for the plsql statement and
   * one for the ref cursor */
  SQLAllocStmt(hdbc, &plsql_hstmt);
  SQLAllocStmt(hdbc, &refcursor_hstmt);
 
  /* prepare the plsql statement */
  stmt_text = (SQLCHAR*)
    "begin "
      "open :refc for "
        "select employee_id, last_name "
        "from employees "
        "where department_id = :dept; "
    "end;";
  SQLPrepare(plsql_hstmt, stmt_text, SQL_NTS);
 
  /* bind parameter 1 (:refc) to refcursor_hstmt */
  SQLBindParameter(plsql_hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_REFCURSOR,
                   SQL_REFCURSOR, 0, 0, refcursor_hstmt, 0, 0);
 
  /* bind parameter 2 (:deptid) to local variable deptid */
  SQLBindParameter(plsql_hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
                   SQL_INTEGER, 0, 0, &deptid, 0, 0);
 
  /* loop through values for :deptid */
  for (i=0; i<3; i++)
  {
     deptid = depts[i];
 
     /* execute the plsql statement */
     SQLExecute(plsql_hstmt);
     /*
      * The result set is now attached to refcursor_hstmt.
      * Bind the result columns and fetch the result set.
      */
 
     /* bind result column 1 to local variable empid */
     SQLBindCol(refcursor_hstmt, 1, SQL_C_SLONG,
                (SQLPOINTER)&empid, 0, 0);
 
     /* bind result column 2 to local variable lastname */
     SQLBindCol(refcursor_hstmt, 2, SQL_C_CHAR,
                (SQLPOINTER)lastname, sizeof(lastname), 0);
 
     /* fetch the result set */
     while(SQLFetch(refcursor_hstmt) != SQL_NO_DATA_FOUND){
       printf("%d, %s\n", empid, lastname);
     }
 
     /* close the ref cursor statement handle */
     SQLFreeStmt(refcursor_hstmt, SQL_CLOSE);
  }
   
  /* drop both handles */
  SQLFreeStmt(plsql_hstmt, SQL_DROP);
  SQLFreeStmt(refcursor_hstmt, SQL_DROP);
}

Working With DML Returning (RETURNING INTO Clause)

You can use a RETURNING INTO clause, referred to as DML returning, with an INSERT, UPDATE, or DELETE statement to return specified items from a row that was affected by the action.

This eliminates the need for a subsequent SELECT statement and separate round trip in case, for example, you want to confirm what was affected by the action.

With ODBC, DML returning is limited to returning items from a single-row operation. The clause returns the items into a list of output parameters. Bind the output parameters as discussed in Parameter Binding and Statement Execution.

SQL syntax and restrictions for the RETURNING INTO clause in TimesTen are documented as part of INSERT, UPDATE, and DELETE in Oracle TimesTen In-Memory Database SQL Reference.

Refer to RETURNING INTO Clause in Oracle Database PL/SQL Language Reference for details about DML returning.

This example is adapted from the example in Committing Changes to the Database in ODBC, with bold text highlighting key portions.

void
update_example(SQLHDBC hdbc)
{
   SQLCHAR*      stmt_text;
   SQLHSTMT      hstmt;
   SQLINTEGER    raise_pct;
   char          hiredate_str[30];
   char          last_name[30];
   SQLLEN        hiredate_len;
   SQLLEN        numrows;

   /* allocate a statement handle */
   SQLAllocStmt(hdbc, &hstmt);

   /* prepare an update statement to give a raise to one employee hired
      before a given date and return that employee's last name */
   stmt_text = (SQLCHAR*)
     "update employees "
     "set salary = salary * ((100 + :raise_pct) / 100.0) "
     "where hire_date < :hiredate and rownum = 1 returning last_name into "
                       ":last_name";
   SQLPrepare(hstmt, stmt_text, SQL_NTS);

   /* bind parameter 1 (:raise_pct) to variable raise_pct */
   SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                    SQL_DECIMAL, 0, 0, (SQLPOINTER)&raise_pct, 0, 0);

   /* bind parameter 2 (:hiredate) to variable hiredate_str */
   SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                    SQL_TIMESTAMP, 0, 0, (SQLPOINTER)hiredate_str,
                    sizeof(hiredate_str), &hiredate_len);
   /* bind parameter 3 (:last_name) to variable last_name */
   SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR,
                    SQL_VARCHAR, 30, 0, (SQLPOINTER)last_name,
                    sizeof(last_name), NULL);
   /* set parameter values to give a 10% raise to an employee hired before
    * January 1, 1996. */
   raise_pct = 10;
   strcpy(hiredate_str, "1996-01-01");
   hiredate_len = SQL_NTS;

   /* execute the update statement */
   SQLExecute(hstmt);

   /* tell us who the lucky person is */
   printf("Gave raise to %s.\n", last_name );

   /* drop the statement handle */
   SQLFreeStmt(hstmt, SQL_DROP);

   /* commit the changes */
   SQLTransact(henv, hdbc, SQL_COMMIT);

}

This returns "King" as the recipient of the raise.

Working With rowids

Each row in a database table has a unique identifier known as its rowid. An application can retrieve the rowid of a row from the ROWID pseudocolumn. Rowids can be represented in either binary or character format.

An application can specify literal rowid values in SQL statements, such as in WHERE clauses, as CHAR constants enclosed in single quotes.

As noted in Table 2-2, the ODBC SQL type SQL_ROWID corresponds to the SQL type ROWID.

For parameters and result set columns, rowids are convertible to and from the C types SQL_C_BINARY, SQL_C_WCHAR, and SQL_C_CHAR. SQL_C_CHAR is the default C type for rowids. The size of a rowid would be 12 bytes as SQL_C_BINARY, 18 bytes as SQL_C_CHAR, and 36 bytes as SQL_C_WCHAR.

Refer to ROWID Data Type and ROWID Pseudocolumn in Oracle TimesTen In-Memory Database SQL Reference.

Note:

TimesTen does not support the PL/SQL type UROWID.

Large Objects (LOBs)

TimesTen Classic supports LOBs (large objects). This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

These sections provide an overview of LOBs and discuss their use in ODBC, covering these topics:

You can also refer to the following:

LOBs in TimesTen OCI and LOBs in TimesTen Pro*C/C++ for information specific to those APIs
LOB Data Types in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen
Oracle Database SecureFiles and Large Objects Developer's Guide for general information about programming with LOBs (but not specific to TimesTen functionality)

About LOBs

A LOB is a large binary object (BLOB) or character object (CLOB or NCLOB). In TimesTen, a BLOB can be up to 16 MB and a CLOB or NCLOB up to 4 MB. LOBs in TimesTen have essentially the same functionality as in Oracle Database, except as noted otherwise.

See Differences Between TimesTen LOBs and Oracle Database LOBs.

LOBs may be either persistent or temporary. A persistent LOB exists in a LOB column in the database. A temporary LOB exists only within an application. There are circumstances where a temporary LOB is created implicitly. For example, if a SELECT statement selects a LOB concatenated with an additional string of characters, TimesTen creates a temporary LOB to contain the concatenated data. In TimesTen ODBC, any temporary LOBs are managed implicitly.

Temporary LOBs are stored in the TimesTen temporary data region.

Differences Between TimesTen LOBs and Oracle Database LOBs

There are key differences between TimesTen and Oracle Database LOB functionality.

In TimesTen, a LOB used in an application does not remain valid past the end of the transaction. All such LOBs are invalidated after a commit or rollback, whether explicit or implicit. This includes after any DDL statement.
TimesTen does not support BFILEs, SecureFiles, array reads and writes for LOBs, or callback functions for LOBs.
TimesTen does not support binding arrays of LOBs.
TimesTen does not support batch processing of LOBs.
Relevant to BLOBs, there are differences in the usage of hexadecimal literals in TimesTen. see the description of HexadecimalLiteral in Constants in Oracle TimesTen In-Memory Database SQL Reference.

LOB Programmatic Approaches and Programming Interfaces

There are three programmatic approaches for accessing LOBs from TimesTen in a C or C++ program.

Simple data interface (ODBC, OCI, Pro*C/C++, TTClasses): Use binds and defines, as with other scalar types, to transfer LOB data in a single chunk.
Piecewise data interface (ODBC): Use advanced forms of binds and defines to transfer LOB data in multiple pieces. This is sometimes referred to as streaming or using data-at-exec (at program execution time). TimesTen supports the piecewise data interface through polling loops to go piece-by-piece through the LOB data. (Another piecewise approach, using callback functions, is supported by Oracle Database but not by TimesTen.)

The piecewise interface enables applications to access LOB data in portions, piece by piece. An application binds parameters or defines results similarly to how those actions are performed for the simple data interface, but indicates that the data is to be provided or retrieved at program execution time ("at exec"). In TimesTen, you can implement the piecewise data interface through a polling loop that is repeated until all the LOB data has been read or written.
LOB locator interface (OCI, Pro*C/C++): Select LOB locators using SQL then access LOB data through APIs that are similar conceptually to those used in accessing a file system. Using the LOB locator interface, you can work with LOB data in pieces or in single chunks. See LOBs in TimesTen OCI and LOBs in TimesTen Pro*C/C++.

The LOB locator interface offers the most utility if it is feasible for you to use it.

Using the LOB Simple Data Interface in ODBC

For the simple data interface in ODBC, use SQLBindParameter to bind parameters and SQLBindCol to define result columns.

The application can bind or define using a SQL type that is compatible with the corresponding variable type, as follows.

For BLOB data, use SQL type SQL_LONGVARBINARY and C type SQL_C_BINARY.
For CLOB data, use SQL type SQL_LONGVARCHAR and C type SQL_C_CHAR.
For NCLOB data, use SQL type SQL_WLONGVARCHAR and C type SQL_C_WCHAR.

SQLBindParameter and SQLBindCol calls for LOB data would be very similar to such calls for other data types, discussed earlier in this chapter.

Note:

Binding a CLOB or NCLOB with a C type of SQL_C_BINARY is prohibited.

Using the LOB Piecewise Data Interface in ODBC

For the piecewise data interface in ODBC, use SQLParamData with SQLPutData in a polling loop to bind parameters and SQLGetData in a polling loop to retrieve results.

See the preceding section, Using the LOB Simple Data Interface in ODBC, for information about supported SQL and C data types for BLOBs, CLOBs, and NCLOBs.

Note:

Similar piecewise data access has already been supported for the various APIs in previous releases of TimesTen, for var data types.

This program excerpt uses SQLPutData with SQLParamData in a polling loop to insert LOB data piece-by-piece into the database. The CLOB column contains the value "123ABC" when the code is executed.

...
  /* create a table */
  create_stmt = "create table clobtable ( c clob )";
  rc = SQLExecDirect(hstmt, (SQLCHAR *)create_stmt, SQL_NTS);
  if(rc != SQL_SUCCESS){/* ...error handling... */}
 
  /* initialize an insert statement */
  insert_stmt = "insert into clobtable values(?)";
  rc = SQLPrepare(hstmt, (SQLCHAR *)insert_stmt, SQL_NTS);
  if(rc != SQL_SUCCESS){/* ...error handling... */}
 
  /* bind the parameter and specify that we will be using
   * SQLParamData/SQLPutData */
  rc = SQLBindParameter(
    hstmt,            /* statement handle */
    1,                /* colnum number */
    SQL_PARAM_INPUT,  /* param type */
    SQL_C_CHAR,       /* C type */
    SQL_LONGVARCHAR,  /* SQL type (ignored) */
    2,                /* precision (ignored) */
    0,                /* scale (ignored) */
    0,                /* putdata token */
    0,                /* ignored */
    &pcbvalue);       /* indicates use of SQLPutData */
  if(rc != SQL_SUCCESS){/* ...error handling... */}
 
  pcbvalue = SQL_DATA_AT_EXEC;
 
  /* execute the statement -- this should return SQL_NEED_DATA */
  rc = SQLExecute(hstmt);
  if(rc != SQL_NEED_DATA){/* ...error handling... */}
 
  /* while we still have parameters that need data... */
  while((rc = SQLParamData(hstmt, &unused)) == SQL_NEED_DATA){
 
    memcpy(char_buf, "123", 3);
    rc = SQLPutData(hstmt, char_buf, 3);
    if(rc !=  SQL_SUCCESS){/* ...error handling... */}
 
    memcpy(char_buf, "ABC", 3);
    rc = SQLPutData(hstmt, char_buf, 3);
    if(rc !=  SQL_SUCCESS){/* ...error handling... */}
 
  }
...

Passthrough LOBs in ODBC

Passthrough LOBs, which are LOBs in Oracle Database accessed through TimesTen, are exposed as TimesTen LOBs and are supported by TimesTen in much the same way that any TimesTen LOB is supported.

Note the following:

TimesTen LOB size limitations do not apply to storage of LOBs in the Oracle database through passthrough.
As with TimesTen local LOBs, a passthrough LOB used in an application does not remain valid past the end of the transaction.

Using CALL to Execute Procedures and Functions

TimesTen Classic supports each of these syntax formats from any of its programming interfaces to call PL/SQL procedures (procname) or PL/SQL functions (funcname) that are standalone or part of a package, or to call TimesTen built-in procedures (procname).

CALL procname[(argumentlist)]

CALL funcname[(argumentlist)] INTO :returnparam

CALL funcname[(argumentlist)] INTO ?

TimesTen ODBC also supports each of the following syntax formats:

{ CALL procname[(argumentlist)] }

{ ? = [CALL] funcname[(argumentlist)] }

{ :returnparam = [CALL] funcname[(argumentlist)] }

The following ODBC example calls the TimesTen ttCkpt built-in procedure.

rc = SQLExecDirect (hstmt, (SQLCHAR*) "call ttCkpt",SQL_NTS);

These examples call a PL/SQL procedure myproc with two parameters:

rc = SQLExecDirect(hstmt, (SQLCHAR*) "{ call myproc(:param1, :param2) }",SQL_NTS);

rc = SQLExecDirect(hstmt, (SQLCHAR*) "{ call myproc(?, ?) }",SQL_NTS);

The following shows several ways to call a PL/SQL function myfunc:

rc = SQLExecDirect (hstmt, (SQLCHAR*) "CALL myfunc() INTO :retparam",SQL_NTS);

rc = SQLExecDirect (hstmt, (SQLCHAR*) "CALL myfunc() INTO ?",SQL_NTS);

rc = SQLExecDirect (hstmt, (SQLCHAR*) "{ :retparam = myfunc() }",SQL_NTS);

rc = SQLExecDirect (hstmt, (SQLCHAR*) "{ ? = myfunc() }",SQL_NTS);

See CALL in Oracle TimesTen In-Memory Database SQL Reference.

Note:

A user's own procedure takes precedence over a TimesTen built-in procedure with the same name, but it is best to avoid such naming conflicts.
TimesTen does not support using SQL_DEFAULT_PARAM with SQLBindParameter for a CALL statement.

Timeouts and Thresholds for Executing SQL Statements

TimesTen offers two ways to limit the time for SQL statements or procedure calls to execute, by either setting a timeout duration or setting a threshold duration. This applies to any SQLExecute, SQLExecDirect, or SQLFetch call.

This section covers these topics:

Setting a Timeout Duration for SQL Statements

To control how long SQL statements should execute before timing out, you can set the SQL_QUERY_TIMEOUT option using a SQLSetStmtOption or SQLSetConnectOption call to specify a timeout value, in seconds. A value of 0 indicates no timeout. If a timeout duration is reached, the statement stops executing and an error is thrown.

Note:

Despite the name, this timeout value applies to any executable SQL statement, not just queries.

In TimesTen, you can specify this timeout value for a connection, and therefore any statement on the connection, by using either the SQLQueryTimeout general connection attribute (in seconds) or the SQLQueryTimeoutMsec general connection attribute (in milliseconds). The default value of each is 0, for no timeout. (Also see SQLQueryTimeout and SQLQueryTimeoutMsec in Oracle TimesTen In-Memory Database Reference.)

Despite the names, these timeout values apply to any executable SQL statement, not just queries.

A call to SQLSetConnectOption with the SQL_QUERY_TIMEOUT option overrides any previous query timeout setting. A call to SQLSetStmtOption with the SQL_QUERY_TIMEOUT option overrides the connection setting for the particular statement.

The query timeout limit has effect only when a SQL statement is actively executing. A timeout does not occur during commit or rollback. For transactions that update, insert, or delete a large number of rows, the commit or rollback phases may take a long time to complete. During that time the timeout value is ignored.

See Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide.

Note:

If both a lock timeout value and a SQL query timeout value are specified, the lesser of the two values causes a timeout first. Regarding lock timeouts, you can refer to ttLockWait (built-in procedure) or LockWait (general connection attribute) in Oracle TimesTen In-Memory Database Reference, or to Check for Deadlocks and Timeouts in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.

Setting a Threshold Duration for SQL Statements

You can configure TimesTen to write a warning to the support log when the execution of a SQL statement exceeds a specified time duration, in seconds. Execution continues and is not affected by the threshold.

By default, the application obtains the threshold from the QueryThreshold general connection attribute setting (refer to QueryThreshold in Oracle TimesTen In-Memory Database Reference). The default value is 0, for no warnings. Setting the TT_QUERY_THRESHOLD option in a SQLSetConnectOption call overrides the connection attribute setting for the current connection. Despite the name, the threshold applies to any executable SQL statement.

To set the threshold with SQLSetConnectOption:

RETCODE SQLSetConnectOption(hdbc, TT_QUERY_THRESHOLD, seconds);

Setting the TT_QUERY_THRESHOLD option in a SQLSetStmtOption call overrides the connection attribute setting, and any setting through SQLSetConnectOption, for the statement. It applies to SQL statements executed using the ODBC statement handle.

To set the threshold with SQLSetStmtOption:

RETCODE SQLSetStmtOption(hstmt, TT_QUERY_THRESHOLD, seconds);

You can retrieve the current value of TT_QUERY_THRESHOLD by using the SQLGetConnectOption or SQLGetStmtOption ODBC function:

RETCODE SQLGetConnectOption(hdbc, TT_QUERY_THRESHOLD, paramvalue);

RETCODE SQLGetStmtOption(hstmt, TT_QUERY_THRESHOLD, paramvalue);

Features for Cache

There are features related to the use of cache in TimesTen Classic.

See Oracle TimesTen In-Memory Database Cache Guide for information about cache.

See PassThrough and Setting a Passthrough Level in Oracle TimesTen In-Memory Database Cache Guide.

Setting Temporary Passthrough Level With the ttOptSetFlag Built-In Procedure

TimesTen provides the ttOptSetFlag built-in procedure for setting various flags, including the PassThrough flag to temporarily set the passthrough level.

You can use ttOptSetFlag to set PassThrough in a C application as in the following example that sets the passthrough level to 1. The setting affects all statements that are prepared until the end of the transaction.

rc = SQLExecDirect (hstmt, "call ttOptSetFlag ('PassThrough', 1)",SQL_NTS);

See ttOptSetFlag in Oracle TimesTen In-Memory Database Reference.

Determining Passthrough Status

You can call the SQLGetStmtOption ODBC function with the TT_STMT_PASSTHROUGH_TYPE statement option to determine whether a SQL statement is to be executed in the TimesTen database or passed through to the Oracle database for execution.

This is shown in the following example.

rc = SQLGetStmtOption(hStmt, TT_STMT_PASSTHROUGH_TYPE, &passThroughType);

You can make this call after preparing the SQL statement. It is useful with PassThrough settings of 1 or 2, where the determination of whether a statement is actually passed through is not made until compilation time. If TT_STMT_PASSTHROUGH_NONE is returned, the statement is to be executed in TimesTen. If TT_STMT_PASSTHROUGH_ORACLE is returned, the statement is to be passed through to Oracle Database for execution.

See Setting a Passthrough Level in Oracle TimesTen In-Memory Database Cache Guide.

Note:

TT_STMT_PASSTHROUGH_TYPE is supported with SQLGetStmtOption only, not with SQLSetStmtOption.

Retrieving Information About Cache Groups

When using cache, following the execution of a FLUSH CACHE GROUP, LOAD CACHE GROUP, REFRESH CACHE GROUP, or UNLOAD CACHE GROUP statement, the ODBC function SQLRowCount returns the number of cache instances that were flushed, loaded, refreshed, or unloaded.

For related information, see Determining the Number of Cache Instances Affected by an Operation in Oracle TimesTen In-Memory Database Cache Guide.

Refer to ODBC API reference documentation for general information about SQLRowCount.

TimesTen Include Files

TimesTen Deferred Prepare

Prefetching Multiple Rows of Data

Optimizing Query Performance

Parameter Binding and Statement Execution

SQLBindParameter Function

Parameter Type Assignments and Type Conversions

ODBC SQL to TimesTen SQL or PL/SQL Type Mappings

Binding Input Parameters

Binding Output Parameters

Binding Input/Output Parameters

Binding of Duplicate Parameters in SQL Statements

Binding of Duplicate Parameters in PL/SQL Statements

Considerations for Floating Point Data

Using SQL_WCHAR and SQL_WVARCHAR With a Driver Manager

Working With REF CURSORs

Working With DML Returning (RETURNING INTO Clause)

Working With rowids

Large Objects (LOBs)

About LOBs

Differences Between TimesTen LOBs and Oracle Database LOBs

LOB Programmatic Approaches and Programming Interfaces

Using the LOB Simple Data Interface in ODBC

Using the LOB Piecewise Data Interface in ODBC

Passthrough LOBs in ODBC

Using CALL to Execute Procedures and Functions

Timeouts and Thresholds for Executing SQL Statements

Setting a Timeout Duration for SQL Statements

Setting a Threshold Duration for SQL Statements

Configuring the Result Set Buffer Size in Client/Server Using ODBC

Features for Cache

Setting Temporary Passthrough Level With the ttOptSetFlag Built-In Procedure

Determining Passthrough Status

Retrieving Information About Cache Groups