2 Understanding and Using TTClasses

This chapter provides some general overview and best practices for TTClasses. It includes the following topics:

Overview of TTClasses

The TimesTen C++ Interface Classes library (TTClasses) provides wrappers around the most common ODBC functionality to allow database access. It was developed to meet the demand for an API that is easier to use than ODBC but does not sacrifice performance. Refer to ODBC API reference documentation for detailed information about ODBC.

In addition to providing a C++ interface to the TimesTen ODBC interface, TTClasses supplies an interface to the TimesTen Transaction Log API (XLA). XLA allows an application to monitor one or more tables in a database. When other applications change that table, the changes are reported through XLA to the monitoring application. TTClasses provides a convenient interface to the most commonly used aspects of XLA functionality. For general information about XLA, see "XLA and TimesTen Event Management" in Oracle TimesTen In-Memory Database C Developer's Guide.

TTClasses is also intended to promote best practices when writing application software that uses the TimesTen Data Manager. The library uses TimesTen in an optimal manner. For example, autocommit is disabled by default. Parameterized SQL is strongly encouraged and its use is greatly simplified in TTClasses compared to hand-coded ODBC.

Using TTCmd, TTConnection, and TTConnectionPool

While TTClasses can be used in several ways, the following general approach has been used successfully and can easily be adapted to a variety of applications.

To achieve optimal performance, real-time applications should use prepared SQL statements. Ideally, all SQL statements that will be used by an application are prepared when the application begins, using a separate TTCmd object for each statement. In ODBC, and thus in TTClasses, statements are bound to a particular connection, so a full set of the statements used by the application will often be associated with every connection to the database.

A convenient way to accomplish this is to develop an application-specific class that is derived from TTConnection. For an application named XYZ, you can create a class XYZConnection, for example. The XYZConnection class contains private TTCmd members representing the prepared SQL statements that can be used in the application, and provides new public methods to implement the application-specific database functionality through these private TTCmd members.

Before a TTCmd object can be used, a SQL statement (such as SELECT, INSERT, UPDATE, or DELETE) must be associated with it. The association is accomplished by using the Prepare() method, which also compiles and optimizes the SQL statement to ensure it will be executed in an efficient manner. Note that the Prepare() method only prepares and does not execute the statement.

With TimesTen, statements are typically parameterized for better performance. Consider the following SQL statements:

SELECT col1 FROM table1 WHERE C = 10;
SELECT col1 FROM table1 WHERE C = 11;

It is more efficient to prepare a single parameterized statement and execute it multiple times:

SELECT col1 FROM table1 WHERE C = ?;

The value for "?" is specified at runtime by using the TTCmd::setParam() method.

There is no need to explicitly bind columns or parameters to a SQL statement, as is necessary when you use ODBC directly. TTCmd automatically defines and binds all necessary columns at prepare time. Parameters are bound at execution time.

Note that preparing is a relatively expensive operation. When an application establishes a connection to TimesTen, using TTConnection::Connect(), the application should prepare all TTCmd objects associated with the connection.

In normal operations, where TTClasses and applications are compiled with the TTEXCEPT flag, a TTStatus object is thrown as an exception if an error or warning occurs during the operation. In general, anytime a TTClasses method encounters an error or warning, it throws an exception in this way, which the application should catch and handle appropriately. See "TTEXCEPT: Throw C++ exceptions" and "TTStatus" for additional information. Also, the TTClasses Quick Start demo applications show examples of how this is done. See "About the TimesTen TTClasses demos".

Note:

If TTConnection and TTCmd lack any getter or setter methods you need, you can access underlying ODBC connection and statement handles directly, through the TTConnection::getHdbc() and TTCmd::getHandle() methods. Similarly, there is a TTGlobal::sqlhenv() method to access the ODBC environment handle.

Example 2-1 Definition of a connection class

This is an example of a class that inherits from TTConnection.

class XYZConnection : public TTConnection {
private:
  TTCmd updateData;
  TTCmd insertData;
  TTCmd queryData;

public:
  XYZConnection();
  ~XYZConnection();
  virtual void Connect (const char* connStr, const char* user, const char* pwd);
  void updateUser ();
  void addUser (char* nameP);
  void queryUser (const char* nameP, int* valueP);
};

In this example, an XYZConnection object is a connection to TimesTen that can be used to perform three application-specific operations: addUser(), updateUser(), and queryUser(). These operations are specific to the XYZ application. The implementation of these three methods can use the updateData, insertData, and queryData TTCmd objects to implement the database operations of the application.

To prepare the SQL statements of the application, the XYZConnection class overloads the Connect() method provided by the TTConnection base class. The XYZConnection::Connect() method calls the Connect() method of the base class to establish the database connection and also calls the Prepare() method for each TTCmd object to prepare the SQL statements for later use.

Example 2-2 Definition of a Connect() method

This example shows an implementation of the XYZConnection::Connect() method.

void
XYZConnection::Connect(const char* connStr, const char* user, const char* pwd)
{
  try {
    TTConnection::Connect(connStr, user, pwd);
    updateData.Prepare(this, "update mydata v set foo = ? where bar = ?");
    insertData.Prepare(this, "insert into mydata values(?,0)");
    queryData.Prepare(this, "select i from mydata where name = ?");
  }
  catch (TTStatus st) {
    cerr << "Error in XYZConnection::Connect: " << st << endl;
  }
  return;
}

This Connect() method makes the XYZConnection object and its application-specific methods fully operational.

This approach also works well with the design of the TTConnectionPool class. The application can create numerous objects of type XYZConnection and add them to a TTConnectionPool object. By calling TTConnectionPool::ConnectAll(), the application connects all connections in the pool to the database and prepares all SQL statements. Refer to the usage discussion under "TTConnectionPool", which includes important information.

This application design allows database access to be easily separated from the application business logic. Only the XYZConnection class contains database-specific code.

Examples of this application design can be found in several of the TTClasses sample programs that are included with the TimesTen Quick Start. See "About the TimesTen TTClasses demos".

Note that other configurations are possible. Some customers have extended this scheme further, so that SQL statements to be used in an application are listed in a table in the database, rather than being hard-coded in the application itself. This allows changes to database functionality to be implemented by making database changes rather than application changes.

Example 2-3 Definition of a Disconnect() method

This example shows an implementation of the XYZConnection::Disconnect() method.

void
XYZConnection::Disconnect()
{
  updateData.Drop();
  insertData.Drop();
  queryData.Drop();
 
  TTConnection::Disconnect();
}

Considering TimesTen features for access control

TimesTen has features to control database access with object-level resolution for database objects such as tables, views, materialized views, sequences, and synonyms. You can refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for general information about these features. Also see "Considering TimesTen features for access control" in Oracle TimesTen In-Memory Database C Developer's Guide.

For any query, SQL DML statement, or SQL DDL statement discussed in this document or used in an example, it is assumed that the user has appropriate privileges to execute the statement. For example, a SELECT statement on a table requires ownership of the table, SELECT privilege granted on the table, or the SELECT ANY TABLE system privilege.

Refer to "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference for the privilege required for any given SQL statement.

Privileges are granted through the SQL statement GRANT and revoked through the SQL statement REVOKE. Some privileges are granted to all users through the PUBLIC role, of which each user is a member. See "The PUBLIC role" in Oracle TimesTen In-Memory Database SQL Reference for information about that role.

In addition, access control affects connecting to a database (as discussed in "Access control for connections"), setting connection attributes, using XLA (as discussed in "Access control impact on XLA"), and executing C utility functions.

Notes:

  • Access control cannot be disabled.

  • Access control privileges are checked both when SQL is prepared and when it is executed in the database, with most of the performance cost coming at prepare time.

Managing TimesTen connections

This section covers topics related to connecting to a database:

About DSNs

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 will connect directly to the database or will connect by a client.

If you intend to connect directly to the database, refer to "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide. There are sections on creating a DSN for a direct connection from UNIX or Windows.

If you intend to create a client connection to the database, refer to "Working with the TimesTen Client and Server" in Oracle TimesTen In-Memory Database Operations Guide. There are sections on creating a DSN for a client/server connection from UNIX or Windows.

Note:

A TimesTen connection cannot be inherited from a parent process. If a process opens a database connection before creating a child process, the child must not use the connection.

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.

Example 2-4 Connecting to and disconnecting from TimesTen

  ...

  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);
  }

Access control for connections

This section covers access control features related to how you connect to the database with TTClasses.

For a general access control overview, refer to "Considering TimesTen features for access control".

Connection method signatures for access control

The following method signatures are defined for the TTConnection, TTConnectionPool, and TTXlaPersistConnection classes. (Note that in all cases, signatures are also supported with a TTStatus object as the last parameter, but using the methods with TTStatus is not typical.)

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)

Notes:

  • The connection string (connStr value) can specify the user name and password, such as "DSN=testdb;uid=brian;pwd=welcome". But note that for signatures that take connection string, user name, and password arguments, the user name and password arguments take precedence over any user name or password specified in the connection string.

  • See "TTConnection" for information about DRIVER_COMPLETION_ENUM values.

CREATE SESSION privilege for access control

Privilege to connect to a database must be explicitly granted to every user other than the instance administrator, through the CREATE SESSION privilege. This is a system privilege. It must be granted by an administrator to the user, either directly or through the PUBLIC role. Refer to "Managing Access Control" in Oracle TimesTen In-Memory Database Operations Guide for additional information and examples.

XLA privilege for XLA connections

In addition to the CREATE SESSION privilege, a user must be granted the XLA privilege to create an XLA connection and execute XLA functionality, as noted in "Access control impact on XLA".

Managing TimesTen data

This section covers the following topics for working with data.

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. There is also functionality to support either possible TimesTen DuplicateBindMode setting if there are duplicate parameters.

These topics are covered in the following sections.

Binding IN parameters

For non-batch operations, use the TTCmd::setParam() method to bind IN parameters for SQL statements, specifying the parameter position and the value to be bound. For batch operations, use the TTCmd::BindParameter() method. (See Example 3-5, "Using the ExecuteBatch() method" for an example of batch operations.)

For non-batch operations, Example 2-5 shows snippets from a class SampleConnection, where parameters are bound to insert a row into a table. (This example is from the TimesTen Quick Start demo basics.cpp. See "About the TimesTen TTClasses demos".) Implementation of the Connect() method is omitted here, but see Example 2-2 for a Connect() implementation.

Assume a table basics, defined as follows:

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

Example 2-5 Binding parameters to insert a row (non-batch)

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 set as a connection, name as a character string.
  try {
    conn.insert(name); 
  }
  catch (TTStatus st) {
    cerr << "Error inserting row " << name << ":" << st << endl;
    conn.Rollback();
  }
}

Registering parameters

The TTCmd class provides the method registerParam(), which enables you to specify the SQL type, precision, and scale of a parameter (as applicable) and whether the parameter is IN, OUT, or IN OUT. A registerParam() call is required for an OUT or IN OUT parameter, which could be a REF CURSOR (OUT only) or a parameter from a PL/SQL RETURNING INTO clause (OUT only), procedure, or function.

For an IN parameter, TTClasses by default derives the SQL type from the bound C type for the setParam() or BindParameter() call according to the mappings shown in Table 2-1. It is not typical to need a registerParam() call for an IN parameter, but you can call it if you have reason to use a particular SQL type or precision or scale.

Table 2-1 TTClasses C type to SQL type mappings

C type SQL type

char*

SQL_VARCHAR

const char*

SQL_VARCHAR

const void*

SQL_VARBINARY

double

SQL_DOUBLE

DATE_STRUCT

SQL_DATE

float

SQL_REAL

int

SQL_INTEGER

SQLBIGINT

SQL_BIGINT

SQLCHAR*

SQL_VARCHAR

SQLINTEGER

SQL_INTEGER

SQLSMALLINT

SQL_SMALLINT

SQLTINYINT

SQL_TINYINT

SQLWCHAR*

SQL_WVARCHAR

TIME_STRUCT

SQL_TIME

TIMESTAMP_STRUCT

SQL_TIMESTAMP


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, 
                     int sqltype, 
                     int precision = 0, 
                     int 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, SQLINTEGER).

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

Note:

See the next section, "Binding OUT or IN OUT parameters", for an example. Also see "registerParam()" for additional reference information.

Binding OUT or IN OUT parameters

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

You must use the TTCmd::registerParam() method, described in the preceding section, to inform TTClasses if a parameter in a SQL statement is OUT or IN OUT. For the intputOutputParameter 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 OUT or IN OUT parameters as well as for IN parameters, in either case before the statement is executed. After statement execution, the data for an OUT value will be in the buffer specified in the BindParameter() call. BindParameter() has a signature for each data type. Note that for an IN OUT 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 OUT and IN OUT parameters.

Example 2-6 Using IN and OUT parameters (non-batch)

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

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

Example 2-7 Using IN and OUT parameters (batch operations)

This 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 };
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);
...

Example 2-8 Using IN OUT parameters

This example uses an IN OUT 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);
cmd.Execute();
SQLINTEGER outval;

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

Example 2-9 Using OUT and IN OUT parameters

This example uses OUT and IN OUT 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

begin
  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");
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;
...

Binding duplicate parameters

TimesTen supports two modes for binding duplicate parameters in a SQL statement. In the Oracle mode, where DuplicateBindMode=0 (the default), multiple occurrences of the same parameter name are considered to be distinct parameters. In the traditional TimesTen mode, where DuplicateBindMode=1, multiple occurrences of the same parameter name are considered to be the same parameter (as in earlier TimesTen releases).

Note:

Refer to "DuplicateBindMode" in Oracle TimesTen In-Memory Database Reference and "Binding duplicate parameters in SQL statements" in Oracle TimesTen In-Memory Database C Developer's Guide for additional information.

For illustration, consider the following query:

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

In the Oracle mode, 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.

In TimesTen mode, SQL statements containing duplicate parameters are parsed such that only distinct parameter names are considered as separate parameters. Binding is based on the position of the first occurrence of a parameter name. Subsequent occurrences of the parameter name are not given their own position numbers, and all occurrences of the same parameter name take on the same value.

Example 2-10 Duplicate parameters: Oracle mode

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

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.

Example 2-11 Duplicate parameters: TimesTen mode

For the SQL statement above, in TimesTen mode the two occurrences of a are considered to be a single parameter, so cannot be bound separately:

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

Note that in TimesTen mode, parameter b is considered to be in position 2, not position 3, and the number of parameters is considered to be two.

Working with REF CURSORs

REF CURSOR is a PL/SQL concept, where a REF CURSOR is a handle to a cursor over a SQL result set and 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 OUT parameter, it must be registered using the TTCmd::registerParam() method. (See "Registering parameters" and "Binding OUT or IN OUT 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.

Important:

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

  • As noted in "Considerations when using an ODBC driver manager (Windows)", REF CURSOR functionality does not work in TTClasses when you use an ODBC driver manager. (This restriction does not apply to the demo ttdm driver manager supplied with TimesTen Quick Start.)

Example 2-12 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 OUT 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.

Example 2-12 Using a 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);
cmdPLSQL.Execute();

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

cmdPLSQL.Drop();

Notes:

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

  • 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 will return NULL.

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

Example 2-13 Using a rowid

...
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 specification" in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and life.

Note:

Oracle TimesTen In-Memory Database does not support the PL/SQL type UROWID.

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. For the latter, if the threshold is reached, an SNMP trap is thrown but execution continues.

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. There is also a getQueryThreshold() method to read the current threshold setting.

In TTClasses, these features can be operated 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.

Using TTClasses logging

TTClasses has a logging facility that allows applications to capture debugging information. TTClasses logging is associated with processes. You can enable logging for a specific process and produce a single output log stream for the process.

TTClasses supports different levels of logging information. See Example 2-15 for more information about what is printed at each log level.

Log level WARN is very useful while developing a TTClasses application. It can also be appropriate for production applications because in this log level, database query plans are generated.

Note that at the more verbose log levels (INFO and DEBUG), so much log data is generated that application performance can be adversely affected. Do not use these log levels in a production environment.

Although TTClasses logging can print to either stdout or stderr, the best approach is to write directly to a TTClasses log file. Example 2-14 demonstrates how to print TTClasses log information at log level WARN into the /tmp/ttclasses.log output file.

Note:

TTClasses logging is disabled by default.

Example 2-14 Printing TTClasses log information

ofstream output;
output.open("/tmp/ttclasses.log");
TTGlobal::setLogStream(output);
TTGlobal::setLogLevel(TTLog::TTLOG_WARN);

First-time users of TTClasses should spend a little time experimenting with TTClasses logging to see how errors are printed at log level ERROR and how much information is generated at log levels INFO and DEBUG.

See "TTGlobal" for more information about using the TTGlobal class for logging.

Using TTClasses XLA

The Transaction Log API (XLA) is a set of functions that enable you to implement applications that monitor TimesTen for changes to specified database tables and receive real-time notification of these changes.

One of the purposes of XLA is to provide a high-performance, asynchronous alternative to triggers.

XLA returns notification of changes to specific tables in the database and information about the transaction boundaries for those database changes. This section shows how to acknowledge updates only at transaction boundaries (a common requirement for XLA applications), using one example that does not use and one example that does use transaction boundaries.

This section covers the following topics:

For additional information about XLA, see "XLA and TimesTen Event Management" in Oracle TimesTen In-Memory Database C Developer's Guide. In addition, the TTClasses Quick Start demos include XLA demos. See "About the TimesTen TTClasses demos".

Important:

As noted in "Considerations when using an ODBC driver manager (Windows)", XLA functionality does not work when you use an ODBC driver manager.

Acknowledging XLA updates without using transaction boundaries

Example 2-15 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-15 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 usually 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 will end with a completed transaction (perhaps multiple transactions in a single batch of XLA records).

Or:

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

Example 2-15 TTClasses XLA program

This example shows a typical main loop of a TTClasses XLA program.

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

loop {
  // 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(); 
  }
} // loop

Acknowledging XLA updates at transaction boundaries

XLA applications should verify whether the last record in a batch of XLA records has 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 will probably 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 need to balance overall system throughput with recovery time and disk 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 disk 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-15 above with Example 2-16, specifically the do_acknowledge setting and the &do_acknowledge parameter of the HandleChange() call.

Example 2-16 TTClasses XLA program using transaction boundaries

In this example, ackUpdates() is called only when the do_acknowledge flag indicates that this batch of XLA records is at a transaction boundary.

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;

// ...
loop {
  // 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();
  }
} // loop

In addition to this change to the XLA main loop, the HandleChange() method must be overloaded to have two parameters (ttXlaUpdateDesc_t*, void* pData). See "HandleChange()". Note that the Quick Start xlasubscriber1 demo shows the use of a pData parameter. (See "About the TimesTen TTClasses demos".)

Access control impact on XLA

"Considering TimesTen features for access control" provides a brief overview of how TimesTen access control affects operations in the database. Access control includes impact on XLA.

Any XLA functionality requires the system privilege XLA. This includes connecting to TimesTen 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.

Note:

A user with the XLA privilege can be notified of any DML statement that executes in the database. As a result, the user with XLA privilege can obtain information about database objects that he or she has not otherwise been granted access to. In practical terms, the XLA privilege is effectively the same as the SELECT ANY TABLE privilege.