Using TimesTen Features in your Application

This section covers the following topics for working with data.

Binding Parameters

This section discusses parameter binding for SQL statements.


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

About Parameter Binding

The TTCmd class supplies the methods setParam() and BindParameter() (for batch operations) to bind parameters. It also supplies the method registerParam() to support output and input/output parameters or to override default bind types.

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:


You could address the issue as follows, for example:

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

Binding Input Parameters

For non-batch operations, use the TTCmd::setParam() method to bind input parameters for SQL statements, specifying the parameter position and the value to be bound. For batch operations, use the TTCmd::BindParameter() method.

See setParam() and ExecuteBatch() for an example of batch operations.

For non-batch operations, the example below shows snippets from a class SampleConnection, where parameters are bound to insert a row into a table. (This example is from the basics.cpp sample application provided with the TimesTen Classic Quick Start. See About TimesTen Quick Start and Sample Applications.)

Assume a table basics, defined as follows:

create table basics (name char(10) not null primary key, i tt_integer);

Implementation of the Connect() method is omitted here, but see the example of a Connect() implementation in Using TTCmd, TTConnection, and TTConnectionPool.

class SampleConnection : public TTConnection 
  using TTConnection::Connect;
    TTCmd        insertData;
    virtual void Connect(const char* connStr, 
                         DRIVER_COMPLETION_ENUM driverCompletion);
    void insert(char* nameP);
  // Assume a Connect() method implemented with the following:
  // insertData.Prepare(this, "insert into basics values(:name, :value)");


SampleConnection::insert(char* nameP)
  static long i = 0;
  insertData.setParam(1, nameP);
  insertData.setParam(2, i++);


main(int argc, char** argv)
  char name[10];
  SampleConnection conn;

// Assume conn is an open connection.
  sprintf(name, "Robert");
  try {
  catch (TTStatus st) {
    cerr << "Error inserting row " << name << ":" << st << endl;

Registering Parameters

The TTCmd class provides the registerParam() method, which enables you to specify the SQL type, precision, and scale of a parameter (as applicable) and whether the parameter is input, output, or input/output.

A registerParam() call is required for an output or input/output parameter, which could be a REF CURSOR (output only) or a parameter from a PL/SQL RETURNING INTO clause (output only), procedure, or function.

A registerParam() call can be either before or after the related setParam() or BindParameter() call and takes precedence regarding SQL type, precision, and scale (as applicable).

The method signature is as follows:

inline void
TTCmd::registerParam(int   pno,
                     int   inputOutputType, 
                     short sqltype, 
                     int   precision = 0, 
                     short scale = 0)
  • pno is the parameter position in the statement.

  • inputOutputType can be TTCmd::PARAM_IN, TTCmd::PARAM_OUT, or TTCmd::PARAM_INOUT.

  • sqltype is the SQL type of the data (for example, SQL_INTEGER).

  • precision and scale (both optional) are used the same way as in an ODBC SQLBindParameter call. For primitive types (such as int), precision and scale settings are ignored.


See Binding Output or Input/Output Parameters, for an example. Also see registerParam() for additional reference information.

Parameter C Type to SQL Type Mappings

For an input parameter, TTClasses by default derives the SQL type from the bound C type for the setParam() or BindParameter() call according to the mappings.

It is not typical to need a registerParam() call for an input parameter, but you can call it if you must use a particular SQL type or precision or scale.

Table 2-1 TTClasses C Type to SQL Type Mappings

C Type SQL Type
































Not all C types shown in the preceding table are supported if you are using a driver manager. Refer to Considerations when Using an ODBC Driver Manager (Windows).

Binding Output or Input/Output Parameters

TTClasses supports output and input/output parameters such as REF CURSORs (output only), parameters from a PL/SQL procedure or function that has OUT or IN OUT parameters, or a parameter from a RETURNING INTO clause (output only).

You must use the TTCmd::registerParam() method, described in the preceding section, to inform TTClasses if a parameter in a SQL statement is output or input/output. For the intputOutputType setting in the method call, use TTCmd::PARAM_OUT or TTCmd::PARAM_INOUT as appropriate.

For non-batch operations, after the SQL statement has been executed, use the appropriate TTCmd::getParam() method to retrieve the output value, specifying the parameter position and the variable into which the value is placed. There is a signature for each data type.

For batch operations, TTCmd::BindParameter() is used for output or input/output parameters as well as for input parameters. It is called before the statement is executed. After statement execution, the data for an output value is in the buffer specified in the BindParameter() call. BindParameter() has a signature for each data type. For an input/output parameter in batch operations, BindParameter() is called only once, before statement execution. Before execution the specified buffer contains the input, and after statement execution it contains the output.

The following examples provide code fragments showing the use of output and input/output parameters.

The first example uses input and output parameters. The setParam() call binds the value of the input parameter :a. The getParam() call retrieves the value of the output parameter :b. The output parameter is also registered as required.

// t1 has a single TT_INTEGER column
cmd.Prepare(&conn, "insert into t1 values (:a) returning c1 into :b");
cmd.setParam(1, 99);
cmd.registerParam(2, TTCmd::PARAM_OUT, SQL_INTEGER);

if (cmd.getParam(2, &outval))
  cerr << "The output value is null." << endl;
  cerr << "The output value is " << outval << endl;

The next example uses input and output parameters in a batch operation. The first BindParameter() call provides the input data for the first parameter :a. The second BindParameter() call provides a buffer for output data for the second parameter :b.

#define BATCH_SIZE  5
int input_int_array[BATCH_SIZE] = { 91, 92, 93, 94, 95 };
int output_int_array[BATCH_SIZE] = { -1, -1, -1, -1, -1 };
SQLULEN numrows;
cmd.PrepareBatch(&conn, "insert into t1 values (:a) returning c1 into :b",
cmd.BindParameter(1, BATCH_SIZE, input_int_array);
cmd.BindParameter(2, BATCH_SIZE, output_int_array);
cmd.registerParam(2, TTCmd::PARAM_OUT, SQL_INTEGER);
numrows = cmd.ExecuteBatch(BATCH_SIZE);

The following example uses an input/output parameter. It is registered as required. The setParam() call binds its input value and the getParam() call retrieves its output value.

cmd.Prepare(&conn, "begin :x := :x + 1; end;");
cmd.registerParam(1, TTCmd::PARAM_INOUT, SQL_INTEGER);
cmd.setParam(1, 99);

if (cmd.getParam(1, &outval))
  cerr << "The output value is null." << endl;
  cerr << "The output value is " << outval << endl;

This final example uses output and input/output parameters. Assume a PL/SQL procedure as follows:

create or replace procedure my_proc (
  a in number,
  b in number,
  c out number,
  d in out number ) as

  c := a + b; 
  d := a + b - d; 
end my_proc;

The input parameters for the procedure are taken as constants in this example rather than as bound parameters, so only the OUT parameter and IN OUT parameter are bound. Both are registered as required. The setParam() call provides the input value for the IN OUT parameter :var1. The first getParam() call retrieves the value for the OUT parameter :sum. The second getParam() call retrieves the output value for the IN OUT parameter :var1.

cmd.Prepare(&conn, "begin my_proc (10, 5, :sum, :var1); end;");
cmd.registerParam (1, TTCmd::PARAM_OUT, SQL_DECIMAL, 38);
cmd.registerParam (2, TTCmd::PARAM_INOUT, SQL_DECIMAL, 38);
cmd.setParam(2, "99");
SQLINTEGER outval1, outval2;

if (cmd.getParam(1, &outval1))
  cerr << "The first output value is null." << endl;
  cerr << "The first output value is " << outval << endl;
if (cmd.getParam(2, &outval2))
  cerr << "The second output value is null." << endl;
  cerr << "The second output value is " << outval << endl;

Binding Duplicate Parameters

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


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 can bind a different value for the occurrence or it can leave the parameter occurrence unbound. In the latter case, the subsequent occurrence takes the same value as the first occurrence. In either case, each occurrence still has a distinct parameter position number.

This example uses a different value for the second occurrence of a in the SQL statement above:

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

To use the same value for both occurrences of a:

mycmd.setParam(1, ...); // both occurrences of :a
mycmd.setParam(3, ...); // occurrence of :b

Parameter b is considered to be in position 3 regardless, and the number of parameters is considered to be three.

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 for processing. This usage is an OUT REF CURSOR, an OUT parameter with respect to PL/SQL. As with any output parameter, it must be registered using the TTCmd::registerParam() method.

See Registering Parameters and Binding Output or Input/Output Parameters.

In the TimesTen implementation, the REF CURSOR is attached to a separate statement handle. The application prepares a SQL statement that has a REF CURSOR parameter on one statement handle, then, before executing the statement, binds a second statement handle as the value of the REF CURSOR. After the statement is executed, the application can describe, bind, and fetch the results using the same APIs as for any result set.

In TTClasses, because a TTCmd object encapsulates a single SQL statement, two TTCmd objects are used to support this REF CURSOR model.

See PL/SQL REF CURSORs in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for additional information about REF CURSORs.


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

The example below demonstrates the following steps for using a REF CURSOR in TTClasses.

  1. Declare a TTCmd object for the PL/SQL statement that returns a REF CURSOR (cmdPLSQL in the example).

  2. Declare a TTCmd* pointer to point to a second TTCmd object for the REF CURSOR (cmdRefCursor in the example).

  3. Use the first TTCmd object (cmdPLSQL) to prepare the PL/SQL statement.

  4. Use the TTCmd::registerParam() method of the first TTCmd object to register the REF CURSOR as an output parameter.

  5. Use the first TTCmd object to execute the statement.

  6. Use the TTCmd::getParam() method of the first TTCmd object to retrieve the REF CURSOR into the second TTCmd object (using &cmdRefCursor). There is a getParam(int paramNo, TTCmd** rcCmd) signature for REF CURSORs.

  7. Fetch the results from the TTCmd object for the REF CURSOR and process as desired.

  8. Drop the first TTCmd object.

  9. Drop the pointer to the TTCmd object for the REF CURSOR.

  10. Issue a delete statement to delete the TTCmd object for the REF CURSOR.

This example retrieves and processes a REF CURSOR from a PL/SQL anonymous block. See the preceding steps for an explanation.

TTCmd  cmdPLSQL;
TTCmd* cmdRefCur;
TTConnection conn;
// c1 is a TT_INTEGER column.
cmdPLSQL.Prepare(&conn, "begin open :rc for select c1 from t; end;")
cmdPLSQL.registerParam(1, TTCmd::PARAM_OUT, SQL_REFCURSOR);

if (cmdPLSQL.getParam(1, &cmdRefCur) == false)
  SQLINTEGER fetchval;
  while (!cmdRefCursor->FetchNext()) {
    cmdRefCur->getColumn(1, &fetchval);
  delete cmdRefCursor;


Be aware of the following usage notes when using REF CURSORs in TTClasses:

  • For passing REF CURSORs between PL/SQL and an application, TimesTen supports only OUT REF CURSORs, from PL/SQL to the application, and supports a statement returning only a single REF CURSOR.

  • Unlike TTCmd::getParam() calls for other data types, a getParam() call with a TTCmd** parameter for a REF CURSOR can only be called once. Subsequent calls return NULL. If you must retrieve a REF CURSOR a second time, you must reexecute the statement.

  • If the statement is executed multiple times, the REF CURSOR parameter must be reregistered each time. For example, if you are executing the statement, getting the REF CURSOR parameter, and fetching from the REF CURSOR within a loop, then the parameter registration must also be in the loop, such as follows:

       fetch loop
    end loop

    This is shown the example below.

  • Any TTCmd object, including one for a REF CURSOR, has an ODBC statement handle allocated for it. The REF CURSOR statement handle is dropped at the time of the Drop() statement and the resource is freed after the delete statement.

The following example uses a REF CURSOR in a loop. Assume the following declarations and a TTConnection instance conn.

TTCmd query;
TTCmd* ref_cur;

Here is the loop:

      cerr << "Selecting values using cursor" << endl;
      query.Prepare(&conn, "begin open :rc for select c1 from t1; end;");
      for (int round = 0; round < ROUNDS; round++) {
         cerr << "executing ref cursor round# " << (round+1) << endl;
         query.registerParam(1, TTCmd::PARAM_OUT, SQL_REFCURSOR);
         query.getParam(1, &ref_cur);
         while(true) {
            fetch_next = ref_cur -> FetchNext();
            if (fetch_next == 1)
            ref_cur -> getColumn(1, &val);
            cerr << "val = " << val << endl;
         delete ref_cur;

Working with ROWIDs

Each row in a 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.

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 is 12 bytes as SQL_C_BINARY, 18 bytes as SQL_C_CHAR, and 36 bytes as SQL_C_WCHAR.

Note that TTClasses has always supported rowids as character strings; however, a TTClasses application can now pass a rowid to a PL/SQL anonymous block as a ROWID type instead of as a string. This involves using the TTCmd::registerParam() method to register the rowid input parameter as SQL_ROWID type, as shown this example:

TTConnection conn;
TTCmd cmd;
cmd.Prepare(&conn, "begin delete from t1 where rowid = :x; end;");
cmd.registerParam(1, TTCmd::PARAM_IN, SQL_ROWID);
cmd.setParam(1, rowid_string);

Refer to ROWID Data Type and ROWID pseudocolumn in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and life.


TimesTen does not support the PL/SQL type UROWID.

Working with LOBs

This section discusses the use of LOBs (large objects) in TTClasses. This includes CLOBs (character LOBs), NCLOBs (national character LOBs), and BLOBs (binary LOBs).

The following topics are discussed:

You can also refer to the following.

  • Large objects (LOBs) in Oracle TimesTen In-Memory Database C Developer's Guide for an overview of LOBs and LOB programming interfaces for C and C++. Only the LOB simple data interface is applicable to TTClasses.

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

Differences Between TimesTen LOBs and Oracle Database LOBs

This section notes differences in LOB support between TimesTen and Oracle Database.

  • A key difference between the TimesTen LOB implementation and the Oracle Database implementation is that 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.

Using the LOB Simple Data Interface in TTClasses

The simple data interface enables applications to access LOB data by binding and defining, just as with other scalar types.

For the simple data interface in TTClasses, use getParam() and setParam() to bind parameters and use getColumn() or getColumnNullable() 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.


  • TTClasses does not support batch mode for LOBs.

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

The following example shows use of the LOB simple data interface in TTClasses. Assume a table with NCLOB, BLOB, and CLOB columns has been created and populated. The methods executed on these LOB types are the same as for NCHAR, BINARY, and CHAR, respectively.

#ifdef _WIN32
#include <ttcommon.h>
#include "TTInclude.h"
#define LOB_COL_SIZE 4194304

int main(int argc, char** argv) {

   TTConnection conn;
   TTCmd query;
   char conn_str[100] = "... your connection string ...";
   char tbl_name[20] = "... test table name ...";

   int num_rows = 0;
   char query_stmt[1000];
   int fetch_next;
   int value_is_null = 0;
   int column_type;
   SQLWCHAR * unicode_val;
   u_char * binary_val;
   char * alfanum_val;
   SQLLEN b_len;
   SQLLEN u_len;
   cerr << "Connecting to TimesTen <" << conn_str << ">" << endl;
   try {
      sprintf(query_stmt, "select * from %s", tbl_name);
      query.Prepare(&conn, query_stmt);
      const int num_result_cols = query.getNColumns();
      while (true) {
         // loop until no rows found
         // fetch a row; if no more rows, break out of loop
         // FetchNext returns 0 for success, 1 for SQL_NO_DATA_FOUND
         fetch_next = query.FetchNext();
         if (fetch_next == 1)
         for (int col = 1; col <= num_result_cols; col++) {
            value_is_null = 0;
            column_type = query.getColumnType(col);
            switch (column_type) {
               case SQL_WLONGVARCHAR:
                  value_is_null = query.getColumnNullable(col,
                          (SQLWCHAR**) & unicode_val, &u_len);
                  if (value_is_null) {
                     cerr << "NCLOB value is NULL";
                  } else {
                     cerr << "NCLOB value length = " << u_len << endl;
                     // do something with NCLOB value
               case SQL_LONGVARBINARY:
                  value_is_null = query.getColumnNullable(col,
                          (void**) & binary_val, &b_len);
                  if (value_is_null) {
                     cerr << "BLOB value is NULL";
                  } else {
                     cerr << "BLOB value length = " << b_len << endl;
                     // do something with BLOB value
               case SQL_LONGVARCHAR:
                  alfanum_val = (char*) malloc(LOB_COL_SIZE + 1);
                  value_is_null = query.getColumnNullable(col, alfanum_val);
                  if (value_is_null) {
                     cerr << "CLOB value is NULL";
                  } else {
                    cerr << "CLOB value length = " << strlen(alfanum_val) << endl;
                     // do something with BLOB value
         cerr << "row " << num_rows << " fetched" << endl;
      cerr << num_rows << " rows returned" << endl;
   } catch (TTError err) {
      cerr << "\nError" << err << endl;
   return 0;

Passthrough LOBs in TTClasses

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.

However, 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.

Setting a Timeout or Threshold for Executing SQL Statements

TimesTen offers two ways for you to limit the time for SQL statements or procedure calls to execute, by setting either a timeout value or a threshold value.

When you set a timeout value, if the timeout duration is reached, the statement stops executing and an error is thrown. A value of 0 indicates no timeout. When you set a threshold value, if the threshold is reached, a warning is written to the support log but execution continues. A value of 0 means no warnings.

The query timeout limit has effect only when a SQL statement is actively executing. A timeout does not occur during commit or rollback.

Use the TTCmd methods setQueryTimeout() and setQueryThreshold() to specify these settings for the TTCmd object. Note that these methods override the settings of the TimesTen connection attributes SQLQueryTimeout (or SQLQueryTimeoutMsec) and QueryThreshold, respectively. Each of these connection attributes has a default value of 0, for no timeout or no threshold.

There is also a getQueryThreshold() method to read the current threshold setting.

In TTClasses, these features can be used only at the statement level, not the connection level.

See Timeouts and Thresholds for Executing SQL Statements in Oracle TimesTen In-Memory Database C Developer's Guide. For information about the relationship between timeout values, see Choose SQL and PL/SQL Timeout Values in Oracle TimesTen In-Memory Database Operations Guide.


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.

Using Automatic Client Failover in a TTClasses Application

TTClasses does not have its own functionality for automatic client failover, but a TTClasses application can configure TimesTen automatic client failover in the same way that an ODBC application can.

This is discussed in ODBC Support for Automatic Client Failover in Oracle TimesTen In-Memory Database C Developer's Guide. For TimesTen Scaleout, also see Client Connection Failover in Oracle TimesTen In-Memory Database Scaleout User's Guide. For TimesTen Classic, see Using Automatic Client Failover in Oracle TimesTen In-Memory Database Operations Guide.