2 Understanding and Using TTClasses

About DSNs

For TimesTen Scaleout, refer to Oracle TimesTen In-Memory Database Scaleout User's Guide for information about creating a database and connecting to a database, using either a direct connection or a client/server connection. See "Creating a database" and "Connecting to a database".

For TimesTen Classic, Oracle TimesTen In-Memory Database Operations Guide contains information about creating a DSN (data source name) for a database. The type of DSN you create depends on whether your application connects directly to the database or connects by a client. If you intend to connect directly to the database, refer to "Managing TimesTen Databases". If you intend to create a client connection to the database, refer to "Working with the TimesTen Client and Server".

Connecting and disconnecting

Based on the XYZConnection class discussed in "Using TTCmd, TTConnection, and TTConnectionPool", you could connect to and disconnect from TimesTen as shown in the following example.

  ...

  XYZConnection conn;
  char connStr[256];
  char user[30];
  char pwd[30];
 
  ...
 
  try {
    conn.Connect(connStr, user, pwd);
  }
  catch (TTWarning st) {
    cerr << "Warning connecting to TimesTen: " << st << endl;
  }
  catch (TTError st) {
    cerr << "Error connecting to TimesTen " << st << endl;
    exit(1);
  }

// ... Work with the database connection...

  try {
    conn.Disconnect();
  }
  catch (TTStatus st) {
    cerr << "Error disconnecting from TimesTen: " << st << endl;
    exit(1);
  }

Connection methods

The following method signatures are defined for the TTConnection, TTConnectionPool, and TTXlaPersistConnection classes.

virtual void 
TTConnection::Connect(const char* connStr)
 
virtual void
TTConnection::Connect(const char* connStr, const char* username, 
                      const char* password)
 
virtual void
TTConnection::Connect(const char* connStr, 
                      DRIVER_COMPLETION_ENUM driverCompletion)

void 
TTConnectionPool::ConnectAll(const char* connStr)

void
TTConnectionPool::ConnectAll(const char* connStr, const char* username, 
                             const char* password)
 
virtual void
TTXlaPersistConnection::Connect(const char* connStr, const char* username, 
                                const char* password, const char* bookmarkStr, 
                                bool createBookmarkFlag)
 
virtual void
TTXlaPersistConnection::Connect(const char* connStr, 
                                DRIVER_COMPLETION_ENUM driverCompletion, 
                                const char * bookmarkStr, bool createBookmarkFlag)
 
virtual void
TTXlaPersistConnection::Connect(const char* connStr, const char* username, 
                                const char* password, const char* bookmarkStr)

virtual void
TTXlaPersistConnection::Connect(const char* connStr, 
                                DRIVER_COMPLETION_ENUM driverCompletion, 
                                const char * bookmarkStr)

Binding parameters

This section discusses parameter binding for SQL statements. 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.

SELECT 'x' FROM DUAL WHERE ? = ?;

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

The following topics are covered:

• Binding input parameters

• Registering parameters

• Binding output or input/output parameters

• Binding duplicate parameters

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

class SampleConnection : public TTConnection 
{
  using TTConnection::Connect;
 
  private:
    TTCmd        insertData;
    ...
 
  protected:
 
  public:
    SampleConnection();
    ~SampleConnection();
    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)");
  ...
}

//----------------------------------------------------------------------
 

void
SampleConnection::insert(char* nameP)
{
  static long i = 0;
  insertData.setParam(1, nameP);
  insertData.setParam(2, i++);
  insertData.Execute();
}
 
//----------------------------------------------------------------------

...

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

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

inline void
TTCmd::registerParam(int pno,
                     int inputOutputType, 
                     int sqltype, 
                     int precision = 0, 
                     int scale = 0)

...
// 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);
cmd.Execute();
SQLINTEGER outval;

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

...
#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 };
int numrows;
 
cmd.PrepareBatch(&conn, "insert into t1 values (:a) returning c1 into :b",
                 BATCH_SIZE);
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);
...

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

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

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

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

...
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");
cmd.Execute();
SQLINTEGER outval1, outval2;

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

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

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

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

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.

Example 2-11 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.

...
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);
cmdPLSQL.Execute();

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

cmdPLSQL.Drop();

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:

  cmdPLSQL.Prepare(...);
  
  loop
     cmdPLSQL.registerParam(...);
     cmdPLSQL.Execute();
     cmdPLSQL.getParam(...);
     fetch loop
  end loop
  

  This is shown in Example 2-12 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.

...
TTCmd query;
TTCmd* ref_cur;
...

...
      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.Execute();
         query.getParam(1, &ref_cur);
 
         while(true) {
            fetch_next = ref_cur -> FetchNext();
            if (fetch_next == 1)
                break;
 
            ref_cur -> getColumn(1, &val);
            cerr << "val = " << val << endl;
         }
         ref_cur->Drop();
         delete ref_cur;
      }
 
      conn.Commit();
      query.Drop();
...

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 in Example 2-13.

...
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);
cmd.Execute();
...

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.

Working with LOBs

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

For an overview of LOBs and LOB programming interfaces for C and C++, see "Working with LOBs" in Oracle TimesTen In-Memory Database C Developer's Guide. Only the LOB simple data interface is applicable to TTClasses.

This section discusses the use of LOBs in TTClasses, covering the following topics:

• Differences between TimesTen LOBs and Oracle Database LOBs

• Using the LOB simple data interface in TTClasses

You can also refer to the following.

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

#ifdef _WIN32
#include <ttcommon.h>
#endif
#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;
   int b_len;
   int u_len;
 
   cerr << "Connecting to TimesTen <" << conn_str << ">" << endl;
 
   try {
      conn.Connect(conn_str);
      sprintf(query_stmt, "select * from %s", tbl_name);
      query.Prepare(&conn, query_stmt);
      query.Execute();
      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)
            break;
 
         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
                  }
                  break;
 
               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
                  }
                  break;
 
               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
                  }
                  free(alfanum_val);
                  break;
 
               default:
                  break;
            }
         }
 
         num_rows++;
         cerr << "row " << num_rows << " fetched" << endl;
      }
      cerr << num_rows << " rows returned" << endl;
   } catch (TTError err) {
      cerr << "\nError" << err << endl;
   }
   query.Drop();
   conn.Disconnect();
   return 0;
}

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. For the former, if the timeout duration is reached, the statement stops executing and an error is thrown. A value of 0 indicates no timeout. For the latter, 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.

For related information, see "Setting a timeout or threshold 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.

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 "Using automatic client failover in your application" 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 additional information. For TimesTen Classic, see "Using automatic client failover" in Oracle TimesTen In-Memory Database Operations Guide.

Acknowledging XLA updates without using transaction boundaries

Example 2-16 below shows basic usage of XLA, without using transaction boundaries.

Inside the HandleChange() method, depending on whether the record is an insert, update, or delete, the appropriate method from among the following is called: HandleInsert(), HandleUpdate(), or HandleDelete().

It is inside HandleChange() that you can access the flag that indicates whether the XLA record is the last record in a particular transaction. Thus there is no way in the Example 2-16 loop for the HandleChange() method to pass the information about the transaction boundary to the loop, so that this information can influence when to call conn.ackUpdates().

This is not an issue under typical circumstances of only a few records per transaction. Usually only a few records are returned when you ask XLA to return at most 1000 records with a fetchUpdatesWait() call. XLA returns records as quickly as it can, and even if huge numbers of transactions are occurring in the database, you usually can pull the XLA records out quickly, a few at a time, and XLA makes sure that the last record returned is on a transaction boundary. For example, if you ask for 1000 records from XLA but only 15 are returned, it is highly probable that the 15th record is at the end of a transaction.

XLA guarantees one of the following:

• A batch of records ends with a completed transaction (perhaps multiple transactions in a single batch of XLA records).

Or:

• A batch of records contains a partial transaction, with no completed transactions in the same batch, and subsequent batches of XLA records are returned for that single transaction until its transaction boundary has been reached.

TTXlaPersistConnection conn; // XLA connection
TTXlaTableList list(&conn); // tables being monitored
ttXlaUpdateDesc_t ** arry; // pointer to returned XLA records
int records_fetched;
// ...

while (!signal_received) {
  // fetch the updates
  conn.fetchUpdatesWait(&arry, MAX_RECS_TO_FETCH, &records_fetched, ...); 

  // Interpret the updates
  for(j=0;j < records_fetched;j++){
    ttXlaUpdateDesc_t *p;
    p = arry[j];
    list.HandleChange(p, NULL);
  } // end for each record fetched

  // periodically call ackUpdates()
  if (/* some condition is reached */) {
    conn.ackUpdates(); 
  }
}

Acknowledging XLA updates at transaction boundaries

XLA applications should verify whether the last record in a batch of XLA records is at a transaction boundary, and call ackUpdates() only on transaction boundaries. This way, when the application or system or database fails, the XLA bookmark is at the start of a transaction after the system recovers. This is especially important when operations involve a large number of rows. If a bulk insert, update, or delete operation has been performed on the database and the XLA application asks for 1000 records, it may or may not receive all 1000 records. The last record returned through XLA probably does not have the end-of-transaction flag. In fact, if the transaction has made changes to 10,000 records, then clearly a minimum of 10 blocks of 1000 XLA records must be fetched before reaching the transaction boundary.

Calling ackUpdates() for every transaction boundary is not recommended, however, because ackUpdates() is a relatively expensive operation. Users should balance overall system throughput with recovery time and file system space requirements. (Recall that a TimesTen transaction log file cannot be deleted by a checkpoint operation if XLA has a bookmark that references that log file. See "ttLogHolds" in Oracle TimesTen In-Memory Database Reference for related information.) Depending on system throughput, recovery time, and file system space requirements, some applications may find it appropriate to call ackUpdates() once or several times per minute, while other applications may need only call it once or several times per hour.

The HandleChange() method has a second parameter to allow passing information between HandleChange() and the main XLA loop. Compare Example 2-16 above with Example 2-17, specifically the do_acknowledge setting and the &do_acknowledge parameter of the HandleChange() call.

TTXlaPersistConnection conn; // XLA connection
TTXlaTableList list(&conn); // tables being monitored
ttXlaUpdateDesc_t ** arry; // ptr to returned XLA recs
int records_fetched;
int do_acknowledge;
int j;

// ...
while (!signal_received) {
  // fetch the updates
  conn.fetchUpdatesWait(&arry, MAX_RECS_TO_FETCH, &records_fetched, ...); 

  do_acknowledge = FALSE;

  // Interpret the updates
  for(j=0;j < records_fetched;j++){
    ttXlaUpdateDesc_t *p;
    p = arry[j];
    list.HandleChange(p, &do_acknowledge);
  } // end for each record fetched

  // periodically call ackUpdates()
  if (do_acknowledge == TRUE  /* and some other conditions ... */ ) {
    conn.ackUpdates();
  }
}

Access control impact on XLA

The system privilege XLA is required for any XLA functionality, such as connecting to TimesTen (which also requires the CREATE SESSION privilege) as an XLA reader, executing XLA-related TimesTen C functions, and executing XLA-related TimesTen built-in procedures.

You can refer to "Access control impact on XLA" in Oracle TimesTen In-Memory Database C Developer's Guide for additional details.