Using TTCmd, TTConnection, and TTConnectionPool

This section describes a general approach to using TTClasses that has been employed 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 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 are often 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 is 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.

Anytime a TTClasses method encounters an error or warning, it throws a TTStatus object as an exception, which the application should catch and handle appropriately. The TimesTen Classic Quick Start sample applications show examples of how this is done. See About TimesTen Quick Start and Sample Applications.

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.

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.

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

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 applications provided with TimesTen Classic Quick Start. See About TimesTen Quick Start and Sample Applications.

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.

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

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

Also see TTCmd Usage, TTConnection Usage, and TTConnectionPool Usage.