Skip Headers
Oracle® TimesTen In-Memory Database TTClasses Guide
11g Release 2 (11.2.2)

Part Number E21640-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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. TimesTen supports ODBC 2.5, Extension Level 1, with additional features for Extension Level 2 as documented in "TimesTen ODBC Functions and Options" in Oracle TimesTen In-Memory Database C Developer's Guide.

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.

Be aware that preparing is a relatively expensive operation. When an application establishes a connection to TimesTen, using TTConnection::Connect(), it should prepare all TTCmd objects associated with the connection. Prepare all SQL statements prior to the main execution loop of the application.

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 or TTCmd lacks the specific get or set method 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. Use TTConnectionPool::DisconnectAll() to disconnect. Refer to the usage discussion under "TTConnectionPool", which provides 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 come 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 or SQL DML or 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 (forking) 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.

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". Be aware 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 the DuplicateBindMode TimesTen connection attribute if there are duplicate parameters (to determine TimesTen mode or Oracle Database mode).

These topics are covered in the following sections.

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 PL/SQL terminology).

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 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 is an open connection.
  sprintf(name, "Robert");
  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 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.

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 shown in Table 2-1. 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

char*

SQL_CHAR, SQL_VARCHAR

void*

SQL_BINARY, SQL_VARBINARY

double

SQL_FLOAT, SQL_DOUBLE

DATE_STRUCT

SQL_DATE

float

SQL_REAL, SQL_DECIMAL

int

SQL_INTEGER

SQLBIGINT

SQL_BIGINT

SQLCHAR*

SQL_VARCHAR

SQLINTEGER

SQL_INTEGER

SQLSMALLINT

SQL_SMALLINT

SQLTINYINT

SQL_TINYINT

SQLWCHAR*

SQL_WCHAR, SQL_WVARCHAR

TIME_STRUCT

SQL_TIME

TIMESTAMP_STRUCT

SQL_TIMESTAMP

SQLHSTMT

SQL_REFCURSOR


Important:

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

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

Note:

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

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

Example 2-6 Using input and input/output 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, 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;
...

Example 2-7 Using input and output 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 input/output parameters

This 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);
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 output and input/output parameters

This 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

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 distinct modes for binding duplicate parameters in a SQL statement. In the Oracle mode, with the TimesTen connection attribute setting 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

This example uses 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, 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.

Important:

  • In addition to supporting only OUT REF CURSORs (from PL/SQL to the application), TimesTen supports only one REFCURSOR per statement.

  • As noted in "Considerations when using an ODBC driver manager (Windows)", REF CURSOR functionality is not supported 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 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.

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

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

Example 2-13 Using a REF CURSOR in a loop

This example uses a REF CURSOR in a loop. Assume the following declarations.

...
TTStatus stat;
TTCmd query;
TTCmd* ref_cur;
...

Here is the loop:

...
      cerr << "Selecting values using cursor" << endl;
      sprintf(query_stmt, "begin open :rc for select c1 from %s; end;", tbl_name);
      query.Prepare(&conn, query_stmt, stat);
      
      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(stat);
 
         query.getParam(1, &ref_cur);
 
         while(true) {
            fetch_next = ref_cur -> FetchNext(stat);
            if (fetch_next == 1)
                break;
 
            ref_cur -> getColumn(1, &val);
            cerr << "val = " << val << endl;
         }
      }
 
      conn.Commit(stat);
      ref_cur->Drop(stat);
      query.Drop(stat);
...

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

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

Note:

TimesTen does not support the PL/SQL type UROWID.

Working with LOBs

TimesTen 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:

Important:

As indicated in Oracle TimesTen In-Memory Database C Developer's Guide, in TimesTen a LOB used in an application does not remain valid past the end of the transaction.

Note:

TimesTen does not support CLOBs if the database character set is TIMESTEN8.

You can also refer to the following:

Differences between TimesTen LOBs and Oracle Database LOBs

Be aware of the following:

  • 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 if TimesTen DDLCommitBehavior is set to 0 (the default), for Oracle Database behavior.

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

Notes:

  • TTClasses does not support batch mode for LOBs.

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

Example 2-15 shows use of the LOB simple data interface in TTClasses.

Example 2-15 Using LOBs in TTClasses

This example assumes 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>
#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;
}

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, but note the following:

  • TimesTen LOB size limitations do not apply to storage of passthrough LOBs, but do apply to binding.

  • 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. 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, 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 for the TTCmd object. Note that these methods override the settings specified by the TimesTen connection attributes SqlQueryTimeout and QueryThreshold, respectively.

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.

Notes:

  • If you are using TimesTen Client/Server, the SQL query timeout should be significantly less than TTC_Timeout, and cannot be 0 (for no timeout) if TTC_Timeout is greater than 0. For details, see "TTC_Timeout" in Oracle TimesTen In-Memory Database Reference.

  • 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 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 "Using automatic client failover in your application" in Oracle TimesTen In-Memory Database C Developer's Guide. Also see "Using automatic client failover" in Oracle TimesTen In-Memory Database Operations Guide for more general information about this feature.

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-17 for more information about what is printed at each log level.

Log level TTLOG_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.

At the more verbose log levels (TTLOG_INFO and TTLOG_DEBUG), so much log data is generated that application performance will 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-16 demonstrates how to print TTClasses log information at log level TTLOG_WARN into the /tmp/ttclasses.log output file.

Note:

TTClasses logging is disabled by default.

Example 2-16 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 TTLOG_ERROR and how much information is generated at log levels TTLOG_INFO and TTLOG_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.

The primary purpose of XLA is as 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 provide XLA demos. See "About the TimesTen TTClasses demos".

Important:

  • As noted in "Considerations when using an ODBC driver manager (Windows)", XLA functionality cannot be used in an application connected to an ODBC driver manager.

  • If an XLA bookmark becomes stuck, which can occur if an XLA application terminates unexpectedly or disconnects without first deleting its bookmark or disabling change tracking, there may be an excessive accumulation of transaction log files. This accumulation may result in disk space being filled. For information about monitoring and addressing this situation, see "Monitoring accumulation of transaction log files" in Oracle TimesTen In-Memory Database Operations Guide.

Notes:

  • You can subscribe to tables containing LOB columns, but information about the LOB value itself is unavailable.

  • Columns containing LOBs are reported as empty (zero length) or null (if the value is actually NULL). In this way, you can tell the difference between a null column and a non-null column.

  • An XLA reader cannot subscribe to a table that uses in-memory columnar compression.

Acknowledging XLA updates without using transaction boundaries

Example 2-17 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-17 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 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-17 TTClasses XLA program

This example shows a typical main loop of a TTClasses XLA program. (It also assumes a signal handler is in place.)

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 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 should 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-17 above with Example 2-18, specifically the do_acknowledge setting and the &do_acknowledge parameter of the HandleChange() call.

Example 2-18 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. (The example also assumes a signal handler is in place.)

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

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()". 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 impacts 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.

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, SELECT ANY VIEW, and SELECT ANY SEQUENCE privileges.