2 Working with TimesTen Databases in ODBC

SQLConnect, SQLDriverConnect, SQLAllocConnect, SQLDisconnect functions

The following ODBC functions are available for connecting to a database and related functionality:

• SQLConnect: Loads a driver and connects to the database. The connection handle points to where information about the connection is stored, including status, transaction state, results, and error information.

• SQLDriverConnect: This is an alternative to SQLConnect when more information is required than what is supported by SQLConnect, which is just data source (the database), user name, and password.

• SQLAllocConnect: Allocates memory for a connection handle within the specified environment.

• SQLDisconnect: Disconnect from the database. Takes the existing connection handle as its only argument.

Refer to ODBC API reference documentation for additional details about these functions.

Connecting to and disconnecting from a database

This section provides examples of connecting to and disconnecting from the database.

#include <timesten.h>
SQLRETURN retcode;
SQLHDBC hdbc;

...
retcode = SQLConnect(hdbc,
                     (SQLCHAR*)"FixedDs", SQL_NTS,
                     (SQLCHAR*)"johndoe", SQL_NTS,
                     (SQLCHAR*)"opensesame", SQL_NTS);
...
retcode = SQLDisconnect(hdbc);
...

#include <timesten.h>
#include <stdio.h>
#include <string.h>
#include <stdlib.h>

static void chkReturnCode(SQLRETURN rc, SQLHENV henv,
                          SQLHDBC hdbc, SQLHSTMT hstmt,
                          char* msg, char* filename,
                          int lineno, BOOL err_is_fatal);
#define DEFAULT_CONNSTR "DSN=sampledb;PermSize=32"

int
main(int ac, char** av)
{
   SQLRETURN rc = SQL_SUCCESS;
                  /* General return code for the API */
   SQLHENV henv = SQL_NULL_HENV;
                  /* Environment handle */
   SQLHDBC hdbc = SQL_NULL_HDBC;
                  /* Connection handle */
   SQLHSTMT hstmt = SQL_NULL_HSTMT;
                  /* Statement handle */
   SQLCHAR connOut[255];
                  /* Buffer for completed connection string */
   SQLSMALLINT connOutLen;
                  /* Number of bytes returned in ConnOut */
   SQLCHAR *connStr = (SQLCHAR*)DEFAULT_CONNSTR;
                  /* Connection string */
   rc = SQLAllocEnv(&henv);
   if (rc != SQL_SUCCESS) {
      fprintf(stderr, "Unable to allocate an "
             "environment handle\n");
    exit(1);
   }
   rc = SQLAllocConnect(henv, &hdbc);
   chkReturnCode(rc, henv, SQL_NULL_HDBC,
              SQL_NULL_HSTMT,
              "Unable to allocate a "
              "connection handle\n",
              __FILE__, __LINE__, 1);

   rc = SQLDriverConnect(hdbc, NULL,
                         connStr, SQL_NTS,
                         connOut, sizeof(connOut),
                         &connOutLen,
                         SQL_DRIVER_NOPROMPT);
   chkReturnCode(rc, henv, hdbc, SQL_NULL_HSTMT,
                 "Error in connecting to the"
                 " database\n",
                 __FILE__, __LINE__, 1);
   rc = SQLAllocStmt(hdbc, &hstmt);
   chkReturnCode(rc, henv, hdbc, SQL_NULL_HSTMT,
                 "Unable to allocate a "
                 "statement handle\n",
                 __FILE__, __LINE__, 1);

   /* Your application code here */

   if (hstmt != SQL_NULL_HSTMT) {
     rc = SQLFreeStmt(hstmt, SQL_DROP);
     chkReturnCode(rc, henv, hdbc, hstmt,
                   "Unable to free the "
                   "statement handle\n",
                    __FILE__, __LINE__, 0);
   }

   rc = SQLDisconnect(hdbc);
   chkReturnCode(rc, henv, hdbc,
                 SQL_NULL_HSTMT,
                 "Unable to close the "
                 "connection\n",
                 __FILE__, __LINE__, 0);

   rc = SQLFreeConnect(hdbc);
   chkReturnCode(rc, henv, hdbc,
                 SQL_NULL_HSTMT,
                 "Unable to free the "
                 "connection handle\n",
                 __FILE__, __LINE__, 0);

   rc = SQLFreeEnv(henv);
   chkReturnCode(rc, henv, SQL_NULL_HDBC,
                 SQL_NULL_HSTMT,
                 "Unable to free the "
                 "environment handle\n",
                 __FILE__, __LINE__, 0);
     return 0;
   }
}

static void
chkReturnCode(SQLRETURN rc, SQLHENV henv,
              SQLHDBC hdbc, SQLHSTMT hstmt,
              char* msg, char* filename,
              int lineno, BOOL err_is_fatal)
{
   #define MSG_LNG 512
   SQLCHAR sqlState[MSG_LNG];
   /* SQL state string */
   SQLINTEGER nativeErr;
   /* Native error code */
   SQLCHAR errMsg[MSG_LNG];
   /* Error msg text buffer pointer */
   SQLSMALLINT errMsgLen;
   /* Error msg text Available bytes */
   SQLRETURN ret = SQL_SUCCESS;
   if (rc != SQL_SUCCESS &&
       rc != SQL_NO_DATA_FOUND ) {
      if (rc != SQL_SUCCESS_WITH_INFO) {
       /*
        * It's not just a warning
        */
      fprintf(stderr, "*** ERROR in %s, line %d:"
               " %s\n",
               filename, lineno, msg);
  }
  /*
   * Now see why the error/warning occurred
   */
  while (ret == SQL_SUCCESS ||
         ret == SQL_SUCCESS_WITH_INFO) {
    ret = SQLError(henv, hdbc, hstmt,
                   sqlState, &nativeErr,
                   errMsg, MSG_LNG,
                   &errMsgLen);
    switch (ret) {
      case SQL_SUCCESS:
        fprintf(stderr, "*** %s\n"
                 "*** ODBC Error/Warning = %s, "
                 "TimesTen Error/Warning "
                 " = %d\n",
                 errMsg, sqlState,
                 nativeErr);
      break;
    case SQL_SUCCESS_WITH_INFO:
      fprintf(stderr, "*** Call to SQLError"
              " failed with return code of "
              "SQL_SUCCESS_WITH_INFO.\n "
              "*** Need to increase size of"
              " message buffer.\n");
      break;
    case SQL_INVALID_HANDLE:
      fprintf(stderr, "*** Call to SQLError"
              " failed with return code of "
              "SQL_INVALID_HANDLE.\n");
      break;
    case SQL_ERROR:
      fprintf(stderr, "*** Call to SQLError"
              " failed with return code of "
              "SQL_ERROR.\n");
      break;
    case SQL_NO_DATA_FOUND:
      break;
     } /* switch */
   } /* while */
   if (rc != SQL_SUCCESS_WITH_INFO && err_is_fatal) {
     fprintf(stderr, "Exiting.\n");
     exit(-1);
   }
}

Setting connection attributes programmatically

You can set or override connection attributes programmatically by specifying a connection string when you connect to a database.

Refer to "Managing TimesTen Databases" in Oracle TimesTen In-Memory Database Operations Guide for general information about connection attributes. General connection attributes require no special privilege. First connection attributes are set when the database is first loaded, and persist for all connections. Only the instance administrator can load a database with changes to first connection attribute settings. Refer to "Connection Attributes" in Oracle TimesTen In-Memory Database Reference for additional information, including specific information about any particular connection attribute.

SQLHDBC hdbc;
SQLCHAR ConnStrOut[512];
SQLSMALLINT cbConnStrOut;
SQLRETURN rc;

rc = SQLDriverConnect(hdbc, NULL,
    "DSN=mydsn;PassThrough=3", SQL_NTS,
    ConnStrOut, sizeof (ConnStrOut),
    &cbConnStrOut, SQL_DRIVER_NOPROMPT);

Using a default DSN

In TimesTen Classic, a default DSN, simply named default, can be defined in the odbc.ini or sys.odbc.ini file. See "Setting up a default DSN in TimesTen Classic" in Oracle TimesTen In-Memory Database Operations Guide for information about defining a default DSN.

The associated data source would be connected to in the following circumstances when SQLConnect or SQLDriverConnect is called.

For SQLConnect, if a default DSN has been defined, it is used if ServerName specifies a data source that cannot be found, is a null pointer, or is specifically set to a value of default. For reference, here is the SQLConnect calling sequence:

SQLRETURN SQLConnect( 
          SQLHDBC        ConnectionHandle, 
          SQLCHAR *      ServerName, 
          SQLSMALLINT    NameLength1, 
          SQLCHAR *      UserName, 
          SQLSMALLINT    NameLength2, 
          SQLCHAR *      Authentication, 
          SQLSMALLINT    NameLength3); 

Use default as the server name. The user name and authentication values are used as is.

For SQLDriverConnect, if a default DSN has been defined, it is used if the connection string does not include the DSN keyword or if the data source cannot be found. For reference, here is the SQLDriverConnect calling sequence:

SQLRETURN SQLDriverConnect( 
          SQLHDBC         ConnectionHandle, 
          SQLHWND         WindowHandle, 
          SQLCHAR *       InConnectionString, 
          SQLSMALLINT     StringLength1, 
          SQLCHAR *       OutConnectionString, 
          SQLSMALLINT     BufferLength, 
          SQLSMALLINT *   StringLength2Ptr, 
          SQLUSMALLINT    DriverCompletion); 

Use default as the DSN keyword. The user name and password are used as is.

Be aware of the following usage notes when in direct mode versus client/server mode with a driver manager:

• When you are not using a driver manager, TimesTen manages this functionality. The default DSN must be a TimesTen database.

• When you are using a driver manager, the driver manager manages this functionality. The default DSN need not be a TimesTen database.

TimesTen include files

To use TimesTen features, include the TimesTen files shown in the following table, as applicable. They are located in the include directory of the TimesTen installation.

Set the include path appropriately to access any files that are to be included. See "Compiling and linking applications" for related information.

SQL statement execution within C applications

"Working with Data in a TimesTen Database" in Oracle TimesTen In-Memory Database Operations Guide describes how to use SQL to manage data. This section describes general formats used to execute a SQL statement within a C application. The following topics are covered:

• SQLExecDirect and SQLExecute functions

• Executing a SQL statement

#include <timesten.h>
SQLRETURN rc;
SQLHSTMT hstmt;
...
rc = SQLExecDirect(hstmt, (SQLCHAR*)
     "CREATE TABLE NameID (CustID INTEGER, CustName VARCHAR(50))",
     SQL_NTS);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
     ... /* handle error */

Preparing and executing queries and working with cursors

This section shows the basic steps of preparing and executing a query and working with cursors. Applications use cursors to scroll through the results of a query, examining one result row at a time.

In the ODBC setting, a cursor is always associated with a result set. This association is made by the ODBC driver. The application can control cursor characteristics, such as the number of rows to fetch at one time, using SQLSetStmtOption options documented in "Option support for ODBC 2.5 SQLSetStmtOption and SQLGetStmtOption". The steps involved in executing a query typically include the following.

1. Use SQLPrepare to prepare the SELECT statement for execution.

2. Use SQLBindParameter, if the statement has parameters, to bind each parameter to an application address. See "SQLBindParameter function". (Note that Example 2-5 below does not bind parameters.)

3. Call SQLBindCol to assign the storage and data type for a column of results, binding column results to local variable storage in your application.

4. Call SQLExecute to execute the SELECT statement. See "SQLExecDirect and SQLExecute functions".

5. Call SQLFetch to fetch the results. Specify the statement handle.

6. Call SQLFreeStmt to free the statement handle. Specify the statement handle and either SQL_CLOSE, SQL_DROP, SQL_UNBIND, or SQL_RESET_PARAMS.

Refer to ODBC API reference documentation for details on these ODBC functions. Examples are shown throughout this chapter and in the TimesTen sample applications. See "TimesTen Quick Start and sample applications".

#include <timesten.h>

SQLHSTMT hstmt;
SQLRETURN rc;
int i;
SQLSMALLINT numCols;
SQLCHAR colname[32];
SQLSMALLINT colnamelen, coltype, scale, nullable;
SQLULEN collen [MAXCOLS];
SQLLEN outlen [MAXCOLS];
SQLCHAR* data [MAXCOLS];

/* other declarations and program set-up here */

/* Prepare the SELECT statement */
rc = SQLPrepare(hstmt,
(SQLCHAR*) "SELECT * FROM EMP WHERE AGE>20",
SQL_NTS);
/* ... */

/* Determine number of columns in result rows */
rc = SQLNumResultCols(hstmt, &numCols);

/* ... */

/* Describe and bind the columns */
for (i = 0; i < numCols; i++) {
    rc = SQLDescribeCol(hstmt,
         (SQLSMALLINT) (i + 1),
         colname,(SQLSMALLINT)sizeof(colname), &colnamelen, &coltype, &collen[i],
         &scale, &nullable);

    /* ... */

   data[i] = (SQLCHAR*) malloc (collen[i] +1);  //Allocate space for column data.
   rc = SQLBindCol(hstmt, (SQLSMALLINT) (i + 1),
                   SQL_C_CHAR, data[i],
                   COL_LEN_MAX, &outlen[i]);

   /* ... */

}
/* Execute the SELECT statement */
rc = SQLExecute(hstmt);

/* ... */

/* Fetch the rows */
if (numCols > 0) {
  while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS ||
          rc == SQL_SUCCESS_WITH_INFO) {
    /* ... "Process" the result row */
  } /* end of for-loop */
  if (rc != SQL_NO_DATA_FOUND)
    fprintf(stderr,
            "Unable to fetch the next row\n");

/* Close the cursor associated with the SELECT statement */
  rc = SQLFreeStmt(hstmt, SQL_CLOSE);
}

TimesTen deferred prepare

In standard ODBC, a SQLPrepare call compiles a SQL statement so that information about the statement, such as column descriptions for the result set, is available to the application and accessible through calls such as SQLDescribeCol. To accomplish this, the SQLPrepare call must communicate with the server for processing.

This is in contrast, for example, to expected behavior under Oracle Call Interface (OCI), where a prepare call is expected to be a lightweight operation performed on the client to simply extract names and positions of parameters.

To avoid unwanted round trips between client and server, and also to make the behavior consistent with OCI expectations, the TimesTen client library implementation of SQLPrepare performs what is referred to as a "deferred prepare", where the request is not sent to the server until required. Examples of when the round trip would be required:

• When there is a SQLExecute call. Note that if there is a deferred prepare call that has not yet been sent to the server, a SQLExecute call on the client is converted to a SQLExecDirect call.

• When there is a request for information about the query that can only be supplied by the SQL engine, such as when there is a SQLDescribeCol call, for example. Many such calls in standard ODBC can access information previously returned by a SQLPrepare call, but with the deferred prepare functionality the SQLPrepare call is sent to the server and the information is returned to the application only as needed.

The deferred prepare implementation requires no changes at the application or user level; however, be aware that calling any of the following functions may result in a round trip to the server if the required information from a previously prepared statement has not yet been retrieved:

• SQLColAttributes

• SQLDescribeCol

• SQLDescribeParam

• SQLNumResultCols

• SQLNumParams

• SQLGetStmtOption (for options that depend on the statement having been compiled by the SQL engine)

Also be aware that when calling any of these functions, any error from an earlier SQLPrepare call may be deferred until one of these calls is executed. In addition, these calls may return errors specific to SQLPrepare as well as errors specific to themselves.

Prefetching multiple rows of data

A TimesTen extension to ODBC enables applications to prefetch multiple rows of data into the ODBC driver buffer. This can improve performance of client/server applications.

The TT_PREFETCH_COUNT ODBC statement option determines how many rows a SQLFetch call prefetches. Note that this option provides no benefit for an application using a direct connection to TimesTen.

You can set TT_PREFETCH_COUNT in a call to either SQLSetStmtOption or SQLSetConnectOption (which sets the option default value for all statements associated with the connection). The value can be any integer from 0 to 128, inclusive. Following is an example.

rc = SQLSetConnectOption(hdbc, TT_PREFETCH_COUNT, 100);

With this setting, the first SQLFetch call on the connection prefetches 100 rows. Subsequent SQLFetch calls fetch from the ODBC buffer instead of from the database, until the buffer is depleted. After it is depleted, the next SQLFetch call fetches another 100 rows into the buffer, and so on.

To disable prefetch, set TT_PREFETCH_COUNT to 1.

When you set the prefetch count to 0, TimesTen uses a default prefetch count according to the isolation level you have set for the database, and sets TT_PREFETCH_COUNT to that value. With Read Committed isolation level, the default prefetch value is 5. With Serializable isolation level, the default is 128. The default prefetch value is a good setting for most applications. Generally, a higher value may result in better performance for larger result sets, at the expense of slightly higher resource use.

You can also see "Option support for ODBC 2.5 SQLSetStmtOption and SQLGetStmtOption" for information about statement options, including TT_PREFETCH_COUNT and SQL_TXN_ISOLATION.

Optimizing query performance

A TimesTen extension to ODBC enables applications to optimize read-only query performance in client/server applications by using the TT_PREFETCH_CLOSE ODBC connection option. Set TT_PREFETCH_CLOSE to TT_PREFETCH_CLOSE_ON using SQLSetConnectOption.

All transactions should be committed when executed, including read-only transactions. When TT_PREFETCH_CLOSE is set to TT_PREFETCH_CLOSE_ON, the server automatically closes the cursor and commits the transaction after the server has prefetched all rows of the result set for a read-only query. This enhances performance by reducing the number of network round-trips between client and server.

The client should still free the statement with SQLFreeStmt(SQL_CLOSE) and commit the transaction with SQLTransact(SQL_COMMIT), but those calls are executed in the client and do not require a network round trip between the client and server.

The following example shows how to use the TT_PREFETCH_CLOSE option.

SQLSetConnectOption (hdbc, TT_PREFETCH_CLOSE, TT_PREFETCH_CLOSE_ON);
SQLExecDirect (hstmt, "SELECT * FROM T", SQL_NTS);
while (SQLFetch (hstmt) != SQL_NO_DATA_FOUND)
{
// do the processing and error checking
}
SQLFreeStmt (hstmt, SQL_CLOSE);
SQLTransact(SQL_COMMIT);

Binding parameters and executing statements

This section discusses how to bind input or output parameters for SQL statements. The following topics are covered.

• SQLBindParameter function

• Determination of parameter type assignments and type conversions

• Binding input parameters

• Binding output parameters

• Binding input/output parameters

• Binding duplicate parameters in SQL statements

• Binding duplicate parameters in PL/SQL

• Considerations for floating point data

• Using SQL_WCHAR and SQL_WVARCHAR with a driver manager

SELECT 'x' FROM DUAL WHERE ? = ?;

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

{
  SQLHSTMT      hstmt;
  char          aval[11];
  SQLLEN        aval_len;
  SQLINTEGER    bval;
  SQLLEN        bval_len;
 
  SQLAllocStmt(hdbc, &hstmt);
 
  SQLPrepare(hstmt,
        (SQLCHAR*)"begin :a := 'abcde'; :b := 123; end;",
        SQL_NTS);
 
  SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR,
         10, 0, (SQLPOINTER)aval, sizeof(aval), &aval_len);
 
  SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_SLONG, SQL_INTEGER,
         0, 0, (SQLPOINTER)&bval, sizeof(bval), &bval_len);
 
  SQLExecute(hstmt);
  printf("aval = [%s] (length = %d), bval = %d\n", aval, (int)aval_len, bval);
}

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

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

SQLBindParameter(..., 1, ...); /* both occurrences of :a */
SQLBindParameter(..., 3, ...); /* occurrence of :b */

DECLARE
   x NUMBER;
   y NUMBER;
BEGIN
   x:=:a;
   y:=:a;
END;

BEGIN
   INSERT INTO tab1 VALUES(:a, :a);
END

...CALL proc(:a, :a)...

BEGIN
   INSERT INTO tab1 VALUES(:a, :a);
   INSERT INTO tab1 VALUES(:b, :a);
END

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. The results can be processed in the application using ODBC calls. This is an OUT REF CURSOR (an OUT parameter with respect to PL/SQL). The REF CURSOR is attached to a statement handle, enabling applications to describe and fetch result sets using the same APIs as for any result set.

Take the following steps to use a REF CURSOR. Assume a PL/SQL statement that returns a cursor through a REF CURSOR OUT parameter. Note that REF CURSORs use the same basic steps of prepare, bind, execute, and fetch as in the cursor example in "Preparing and executing queries and working with cursors".

1. Prepare the PL/SQL statement, using SQLPrepare, to be associated with the first statement handle.

2. Bind each parameter of the statement, using SQLBindParameter. When binding the REF CURSOR output parameter, use an allocated second statement handle as rgbValue, the pointer to the data buffer.

   The pcbValue, ibScale, cbValueMax, and pcbValue arguments are ignored for REF CURSORs.

   See "SQLBindParameter function" and "Binding output parameters" for information about these and other SQLBindParameter arguments.

3. Call SQLBindCol to bind result columns to local variable storage.

4. Call SQLExecute to execute the statement.

5. Call SQLFetch to fetch the results. After a REF CURSOR is passed from PL/SQL to an application, the application can describe and fetch the results as it would for any result set.

6. Use SQLFreeStmt to free the statement handle.

These steps are demonstrated in the example that follows. Refer to ODBC API reference documentation for details on these functions. See "PL/SQL REF CURSORs" in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide for additional information about REF CURSORs.

refcursor_example(SQLHDBC hdbc)
{
  SQLCHAR*      stmt_text;
  SQLHSTMT      plsql_hstmt;
  SQLHSTMT      refcursor_hstmt;
  SQLINTEGER    deptid;
  SQLINTEGER    depts[3] = {10,30,40};
  SQLINTEGER    empid;
  SQLCHAR       lastname[30];
  SQLINTEGER    i;
 
  /* allocate 2 statement handles: one for the plsql statement and
   * one for the ref cursor */
  SQLAllocStmt(hdbc, &plsql_hstmt);
  SQLAllocStmt(hdbc, &refcursor_hstmt);
 
  /* prepare the plsql statement */
  stmt_text = (SQLCHAR*)
    "begin "
      "open :refc for "
        "select employee_id, last_name "
        "from employees "
        "where department_id = :dept; "
    "end;";
  SQLPrepare(plsql_hstmt, stmt_text, SQL_NTS);
 
  /* bind parameter 1 (:refc) to refcursor_hstmt */
  SQLBindParameter(plsql_hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_REFCURSOR,
                   SQL_REFCURSOR, 0, 0, refcursor_hstmt, 0, 0);
 
  /* bind parameter 2 (:deptid) to local variable deptid */
  SQLBindParameter(plsql_hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
                   SQL_INTEGER, 0, 0, &deptid, 0, 0);
 
  /* loop through values for :deptid */
  for (i=0; i<3; i++)
  {
     deptid = depts[i];
 
     /* execute the plsql statement */
     SQLExecute(plsql_hstmt);
     /*
      * The result set is now attached to refcursor_hstmt.
      * Bind the result columns and fetch the result set.
      */
 
     /* bind result column 1 to local variable empid */
     SQLBindCol(refcursor_hstmt, 1, SQL_C_SLONG,
                (SQLPOINTER)&empid, 0, 0);
 
     /* bind result column 2 to local variable lastname */
     SQLBindCol(refcursor_hstmt, 2, SQL_C_CHAR,
                (SQLPOINTER)lastname, sizeof(lastname), 0);
 
     /* fetch the result set */
     while(SQLFetch(refcursor_hstmt) != SQL_NO_DATA_FOUND){
       printf("%d, %s\n", empid, lastname);
     }
 
     /* close the ref cursor statement handle */
     SQLFreeStmt(refcursor_hstmt, SQL_CLOSE);
  }
   
  /* drop both handles */
  SQLFreeStmt(plsql_hstmt, SQL_DROP);
  SQLFreeStmt(refcursor_hstmt, SQL_DROP);
}

Working with DML returning (RETURNING INTO clause)

You can use a RETURNING INTO clause, referred to as DML returning, with an INSERT, UPDATE, or DELETE statement to return specified items from a row that was affected by the action. This eliminates the need for a subsequent SELECT statement and separate round trip in case, for example, you want to confirm what was affected by the action.

With ODBC, DML returning is limited to returning items from a single-row operation. The clause returns the items into a list of output parameters. Bind the output parameters as discussed in "Binding parameters and executing statements".

SQL syntax and restrictions for the RETURNING INTO clause in TimesTen are documented as part of "INSERT", "UPDATE", and "DELETE" in Oracle TimesTen In-Memory Database SQL Reference.

Refer to "RETURNING INTO Clause" in Oracle Database PL/SQL Language Reference for details about DML returning.

void
update_example(SQLHDBC hdbc)
{
   SQLCHAR*      stmt_text;
   SQLHSTMT      hstmt;
   SQLINTEGER    raise_pct;
   char          hiredate_str[30];
   char          last_name[30];
   SQLLEN        hiredate_len;
   SQLLEN        numrows;

   /* allocate a statement handle */
   SQLAllocStmt(hdbc, &hstmt);

   /* prepare an update statement to give a raise to one employee hired
      before a given date and return that employee's last name */
   stmt_text = (SQLCHAR*)
     "update employees "
     "set salary = salary * ((100 + :raise_pct) / 100.0) "
     "where hire_date < :hiredate and rownum = 1 returning last_name into "
                       ":last_name";
   SQLPrepare(hstmt, stmt_text, SQL_NTS);

   /* bind parameter 1 (:raise_pct) to variable raise_pct */
   SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                    SQL_DECIMAL, 0, 0, (SQLPOINTER)&raise_pct, 0, 0);

   /* bind parameter 2 (:hiredate) to variable hiredate_str */
   SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                    SQL_TIMESTAMP, 0, 0, (SQLPOINTER)hiredate_str,
                    sizeof(hiredate_str), &hiredate_len);
   /* bind parameter 3 (:last_name) to variable last_name */
   SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR,
                    SQL_VARCHAR, 30, 0, (SQLPOINTER)last_name,
                    sizeof(last_name), NULL);
   /* set parameter values to give a 10% raise to an employee hired before
    * January 1, 1996. */
   raise_pct = 10;
   strcpy(hiredate_str, "1996-01-01");
   hiredate_len = SQL_NTS;

   /* execute the update statement */
   SQLExecute(hstmt);

   /* tell us who the lucky person is */
   printf("Gave raise to %s.\n", last_name );

   /* drop the statement handle */
   SQLFreeStmt(hstmt, SQL_DROP);

   /* commit the changes */
   SQLTransact(henv, hdbc, SQL_COMMIT);

}

Working with rowids

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

As noted in Table 2-2, 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 would be 12 bytes as SQL_C_BINARY, 18 bytes as SQL_C_CHAR, and 36 bytes as SQL_C_WCHAR.

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

Working with LOBs

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

This section provides a brief overview of LOBs and discusses their use in ODBC, covering the following topics:

• About LOBs

• Differences between TimesTen LOBs and Oracle Database LOBs

• LOB programming interfaces

• Using the LOB simple data interface in ODBC

• Using the LOB piecewise data interface in ODBC

• Passthrough LOBs in ODBC

You can also refer to the following:

• "LOBs in TimesTen OCI" and "LOBs in TimesTen Pro*C/C++" for information specific to those APIs

• "LOB data types" in Oracle TimesTen In-Memory Database SQL Reference for additional information about LOBs in TimesTen

• Oracle Database SecureFiles and Large Objects Developer's Guide for general information about programming with LOBs (but not specific to TimesTen functionality)

...
  /* create a table */
  create_stmt = "create table clobtable ( c clob )";
  rc = SQLExecDirect(hstmt, (SQLCHAR *)create_stmt, SQL_NTS);
  if(rc != SQL_SUCCESS){/* ...error handling... */}
 
  /* initialize an insert statement */
  insert_stmt = "insert into clobtable values(?)";
  rc = SQLPrepare(hstmt, (SQLCHAR *)insert_stmt, SQL_NTS);
  if(rc != SQL_SUCCESS){/* ...error handling... */}
 
  /* bind the parameter and specify that we will be using
   * SQLParamData/SQLPutData */
  rc = SQLBindParameter(
    hstmt,            /* statement handle */
    1,                /* colnum number */
    SQL_PARAM_INPUT,  /* param type */
    SQL_C_CHAR,       /* C type */
    SQL_LONGVARCHAR,  /* SQL type (ignored) */
    2,                /* precision (ignored) */
    0,                /* scale (ignored) */
    0,                /* putdata token */
    0,                /* ignored */
    &pcbvalue);       /* indicates use of SQLPutData */
  if(rc != SQL_SUCCESS){/* ...error handling... */}
 
  pcbvalue = SQL_DATA_AT_EXEC;
 
  /* execute the statement -- this should return SQL_NEED_DATA */
  rc = SQLExecute(hstmt);
  if(rc != SQL_NEED_DATA){/* ...error handling... */}
 
  /* while we still have parameters that need data... */
  while((rc = SQLParamData(hstmt, &unused)) == SQL_NEED_DATA){
 
    memcpy(char_buf, "123", 3);
    rc = SQLPutData(hstmt, char_buf, 3);
    if(rc !=  SQL_SUCCESS){/* ...error handling... */}
 
    memcpy(char_buf, "ABC", 3);
    rc = SQLPutData(hstmt, char_buf, 3);
    if(rc !=  SQL_SUCCESS){/* ...error handling... */}
 
  }
...

Making and committing changes to the database

Autocommit is enabled by default (according to the ODBC specification), so that any DML change you make, such as an update, insert, or delete, is committed automatically. It is recommended, however, that you disable this feature and commit (or roll back) your changes explicitly. Use the SQL_AUTOCOMMIT option in a SQLSetConnectOption call to accomplish this:

rc = SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);

With autocommit disabled, you can commit or roll back a transaction using the SQLTransact ODBC function, such as in the following example to commit:

rc = SQLTransact(henv, hdbc, SQL_COMMIT);

Refer to ODBC API reference documentation for details about these functions.

You can refer to "Transaction overview" in Oracle TimesTen In-Memory Database Operations Guide for additional information about transactions.

update_example(SQLHDBC hdbc)
{
  SQLCHAR*      stmt_text;
  SQLHSTMT      hstmt;
  SQLINTEGER    raise_pct;
  char          hiredate_str[30];
  SQLLEN        hiredate_len;
  SQLLEN        numrows;
 
  /* allocate a statement handle */
  SQLAllocStmt(hdbc, &hstmt);
 
  /* prepare an update statement to give raises to employees hired before a
   * given date */
  stmt_text = (SQLCHAR*)
    "update employees "
    "set salary = salary * ((100 + :raise_pct) / 100.0) "
    "where hire_date < :hiredate";
  SQLPrepare(hstmt, stmt_text, SQL_NTS);
 
  /* bind parameter 1 (:raise_pct) to variable raise_pct */
  SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
                   SQL_DECIMAL, 0, 0, (SQLPOINTER)&raise_pct, 0, 0);
 
  /* bind parameter 2 (:hiredate) to variable hiredate_str */
  SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
                   SQL_TIMESTAMP, 0, 0, (SQLPOINTER)hiredate_str,
                   sizeof(hiredate_str), &hiredate_len);
 
  /* set parameter values to give a 10% raise to employees hired before
   * January 1, 1996. */
  raise_pct = 10;
  strcpy(hiredate_str, "1996-01-01");
  hiredate_len = SQL_NTS;
 
  /* execute the update statement */
  SQLExecute(hstmt);
 
  /* print the number of employees who got raises  */
  SQLRowCount(hstmt, &numrows);
  printf("Gave raises to %d employees.\n", numrows);
 
  /* drop the statement handle */
  SQLFreeStmt(hstmt, SQL_DROP);

  /* commit the changes */
  SQLTransact(henv, hdbc, SQL_COMMIT);

}

Using CALL to execute procedures and functions

TimesTen Classic supports each of the following syntax formats from any of its programming interfaces to call PL/SQL procedures (procname) or PL/SQL functions (funcname) that are standalone or part of a package, or to call TimesTen built-in procedures (procname).

CALL procname[(argumentlist)]

CALL funcname[(argumentlist)] INTO :returnparam

CALL funcname[(argumentlist)] INTO ?

TimesTen ODBC also supports each of the following syntax formats:

{ CALL procname[(argumentlist)] }

{ ? = [CALL] funcname[(argumentlist)] }

{ :returnparam = [CALL] funcname[(argumentlist)] }

The following ODBC example calls the TimesTen ttCkpt built-in procedure.

rc = SQLExecDirect (hstmt, (SQLCHAR*) "call ttCkpt",SQL_NTS);

These examples call a PL/SQL procedure myproc with two parameters:

rc = SQLExecDirect(hstmt, (SQLCHAR*) "{ call myproc(:param1, :param2) }",SQL_NTS);

rc = SQLExecDirect(hstmt, (SQLCHAR*) "{ call myproc(?, ?) }",SQL_NTS);

The following shows several ways to call a PL/SQL function myfunc:

rc = SQLExecDirect (hstmt, (SQLCHAR*) "CALL myfunc() INTO :retparam",SQL_NTS);

rc = SQLExecDirect (hstmt, (SQLCHAR*) "CALL myfunc() INTO ?",SQL_NTS);

rc = SQLExecDirect (hstmt, (SQLCHAR*) "{ :retparam = myfunc() }",SQL_NTS);

rc = SQLExecDirect (hstmt, (SQLCHAR*) "{ ? = myfunc() }",SQL_NTS);

See "CALL" in Oracle TimesTen In-Memory Database SQL Reference for details about CALL syntax.

Setting a timeout or threshold for executing SQL statements

TimesTen offers two ways to limit the time for SQL statements or procedure calls to execute, by either setting a timeout duration or setting a threshold duration. This applies to any SQLExecute, SQLExecDirect, or SQLFetch call.

If a timeout duration is reached, the statement stops executing and an error is thrown. If a threshold duration is reached, a warning is written to the support log but execution continues.

This section covers the following topics:

• Setting a timeout duration for SQL statements

• Setting a threshold duration for SQL statements

RETCODE SQLSetConnectOption(hdbc, TT_QUERY_THRESHOLD, seconds);

RETCODE SQLSetStmtOption(hstmt, TT_QUERY_THRESHOLD, seconds);

RETCODE SQLGetConnectOption(hdbc, TT_QUERY_THRESHOLD, paramvalue);

RETCODE SQLGetStmtOption(hstmt, TT_QUERY_THRESHOLD, paramvalue);

Features for use with TimesTen Cache

This section discusses features related to the use of TimesTen Cache in TimesTen Classic:

• Setting temporary passthrough level with the ttOptSetFlag built-in procedure

• Determining passthrough status

• Managing cache groups

See Oracle TimesTen Application-Tier Database Cache User's Guide for information about TimesTen Cache.

See "PassThrough" in Oracle TimesTen In-Memory Database Reference for information about that general connection attribute. See "Setting a passthrough level" in Oracle TimesTen Application-Tier Database Cache User's Guide for information about passthrough settings.

rc = SQLExecDirect (hstmt, "call ttOptSetFlag ('PassThrough', 1)",SQL_NTS);

rc = SQLGetStmtOption(hStmt, TT_STMT_PASSTHROUGH_TYPE, &passThroughType);

Setting globalization options

TimesTen extensions to ODBC enable an application to set options for linguistic sorts, length semantics for character columns, and error reporting during character set conversion. These options can be used in a call to SQLSetConnectOption. The options are defined in the timesten.h file (noted in "TimesTen include files").

For more information about linguistic sorts, length semantics, and character sets, see "Globalization Support" in Oracle TimesTen In-Memory Database Operations Guide.

This section includes the following TimesTen ODBC globalization options.

• TT_NLS_SORT

• TT_NLS_LENGTH_SEMANTICS

• TT_NLS_NCHAR_CONV_EXCP

Features for use with replication

For applications that employ replication, you can improve performance by using parallel replication, which uses multiple threads acting in parallel to replicate and apply transactional changes to databases in a replication scheme. TimesTen Classic supports the following types of parallel replication:

• Automatic parallel replication (ReplicationApplyOrdering=0): Parallel replication over multiple threads that automatically enforces transactional dependencies and all changes applied in commit order. This is the default.

• Automatic parallel replication with disabled commit dependencies (ReplicationApplyOrdering=2): Parallel replication over multiple threads that automatically enforces transactional dependencies, but does not enforce transactions to be committed in the same order on the subscriber database as on the master database. In this mode, you can optionally specify replication tracks.

See "Configuring parallel replication" in Oracle TimesTen In-Memory Database Replication Guide for additional information and usage scenarios.

In an ODBC application that uses parallel replication and specifies replication tracks, you can specify the track number for transactions on a connection through the TT_REPLICATION_TRACK connection option, as noted in "Option support for ODBC 2.5 SQLSetConnectOption and SQLGetConnectOption". (Alternatively, use the general connection attribute ReplicationTrack or the ALTER SESSION parameter REPLICATION_TRACK.)

Checking for errors

An application should check for errors and warnings on every call. This saves considerable time and effort during development and debugging. The sample applications provided with TimesTen show examples of error checking. See "TimesTen Quick Start and sample applications".

Errors can be checked using either the TimesTen error code (error number) or error string, as defined in the installation_dir/include/tt_errCode.h file. Entries are in the following format:

#define tt_ErrMemoryLock             712

For a description of each message, see "List of errors and warnings" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

After calling an ODBC function, check the return code. If the return code is not SQL_SUCCESS, use an error-handling routine that calls the ODBC function SQLError to retrieve the errors on the relevant ODBC handle. A single ODBC call may return multiple errors. The application should be written to return all errors by repeatedly calling the SQLError function until all errors are read from the error stack. Continue calling SQLError until the return code is SQL_NO_DATA_FOUND. (SQL_NO_DATA_FOUND is defined in sqlext.h, which is included by timesten.h.)

Refer to ODBC API reference documentation for details about the SQLError function and its arguments.

For more information about writing a function to handle standard ODBC errors, see "Retrieving errors and warnings" in Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

rc = SQLAllocConnect(henv, &hdbc);

if (rc != SQL_SUCCESS) {
  handleError(rc, henv, hdbc, hstmt, err_buf, &native_error);
  fprintf(stderr,
          "Unable to allocate a connection handle:\n%s\n",
          err_buf);
  exit(-1);
}

Error and warning levels

When operations are not completely successful, TimesTen can return fatal errors, non-fatal errors, or warnings.

Recovering after fatal errors

When fatal errors occur, TimesTen performs a full cleanup and recovery procedure:

• Every connection to the database is invalidated. To avoid out-of-memory conditions in the server, applications are required to disconnect from the invalidated database. Shared memory from the old TimesTen instance is not freed until all active connections at the time of the error have disconnected. Inactive applications still connected to the old TimesTen instance may have to be manually terminated.

• The database is recovered from the checkpoint and transaction log files upon the first subsequent initial connection.

• The recovered database reflects the state of all durably committed transactions and possibly some transactions that were committed non-durably.

• No uncommitted or rolled back transactions are reflected.

Retrying after transient errors (ODBC)

TimesTen automatically resolves most transient errors (which is particularly important for TimesTen Scaleout), but if your application detects the following SQLSTATE value, it is suggested to retry the current transaction:

• TT005: Transient transaction failure due to unavailability of resource. Roll back the transaction and try it again.

In ODBC 3.5, SQLSTATE is returned by the SQLGetDiagRec function or indicated in the SQL_DIAG_SQLSTATE field of the SQLGetDiagField function. In ODBC 2.5, SQLSTATE is returned by the SQLError function. This SQLSTATE may be encountered by any of the following functions. Unless indicated otherwise, these functions apply to either ODBC 2.5 or ODBC 3.5.

• Catalog functions (such as SQLTables and SQLColumns)

• SQLCancel

• SQLCloseCursor (ODBC 3.5)

• SQLDisconnect

• SQLExecDirect

• SQLExecute

• SQLFetch

• SQLFetchScroll (ODBC 3.5)

• SQLFreeStmt (ODBC 2.5)

• SQLGetData

• SQLGetInfo

• SQLPrepare

• SQLPutData

• SQLEndTran (ODBC 3.5)

• SQLTransact (ODBC 2.5)

Functionality of automatic client failover

If a database or database element to which a client is connected fails, failover to an alternate database or database element occurs. When failover occurs, be aware of the following:

• The client has a new connection but using the same ODBC connection handle. No state from the original connection is preserved, however, other than the handle itself. The application must open new ODBC statement handles and descriptor handles.

• If you register a failover callback function (see "Failover callback functions".), a failover listener thread will be created within the client process to listen for failover event and invoke the callback function.

All client statement handles from the original connection are marked as invalid. API calls on these statement handles generally return SQL_ERROR with distinctive failover error codes defined in tt_errCode.h:

• Native error 30105 with SQL state 08006

• Native error 47137

The exception to this is for SQLError, SQLFreeStmt, SQLGetDiagRec, and SQLGetDiagField calls (depending on your version of ODBC), which behave normally.

In addition, note the following:

• The socket to the original database or database element is closed. There is no need to call SQLDisconnect. TimesTen performs the equivalent, cleaning up the connection handle and confirming resources are freed.

• In connecting to the new TimesTen database or database element, the same connection string and DSN definition from the original connection request are used, with the appropriate server name.

• It is up to the application to open new statement handles and reexecute necessary SQLPrepare calls.

• If a failover has already occurred and the client is already connected to the new database or database element:

  • For TimesTen Scaleout, the next failover request results in an attempt to connect to the next element in the list that was returned by TimesTen at the time of the original connection.

  • For TimesTen Classic with active standby replication, the next failover request results in an attempt to reconnect to the original active database. If that fails, alternating attempts are made to connect to the two servers until there is a timeout, and the connection is blocked during this period.

  • For TimesTen Classic using generic automatic client failover, the next failover request results in an attempt to connect to the next database in the list that is configured in the client odbc.ini file. This could be the next database sequentially or one chosen at random from the list, according to the setting of the TTC_Random_Selection connection attribute, which is described in "Configuration of automatic client failover".

  The timeout value is according to the TimesTen client connection attribute TTC_Timeout (default 60 seconds). (Refer to "TTC_Timeout" in Oracle TimesTen In-Memory Database Reference for information about that attribute.)

• Failover connections are created only as needed, not in advance.

During failover, TimesTen can optionally make callbacks to a user-defined function that you register. This function takes care of any custom actions you want to occur in a failover situation. (See "Failover callback functions".)

The following public connection options are propagated to the new connection. The corresponding general connection attribute is shown in parentheses where applicable. The TT_REGISTER_FAILOVER_CALLBACK option is used to register your callback function.

SQL_ACCESS_MODE
SQL_AUTOCOMMIT
SQL_TXN_ISOLATION (Isolation)
SQL_OPT_TRACE
SQL_QUIET_MODE
TT_PREFETCH_CLOSE
TT_CLIENT_TIMEOUT (TTC_TIMEOUT)
TT_REGISTER_FAILOVER_CALLBACK

The following options are propagated to the new connection if they were set through connection attributes or SQLSetConnectOption calls, but not if set through TimesTen built-in procedures or ALTER SESSION.

TT_NLS_SORT (NLS_SORT)
TT_NLS_LENGTH_SEMANTICS (NLS_LENGTH_SEMANTICS)
TT_NLS_NCHAR_CONV_EXCP (NLS_NCHAR_CONV_EXCP)
TT_DYNAMIC_LOAD_ENABLE (DynamicLoadEnable)
TT_DYNAMIC_LOAD_ERROR_MODE (DynamicLoadErrorMode)
TT_NO_RECONNECT_ON_FAILOVER (TTC_NoReconnectOnFailover)

The following options are propagated to the new connection if they were set on the connection handle.

SQL_QUERY_TIMEOUT
TT_PREFETCH_COUNT

See "Connection Attributes" in Oracle TimesTen In-Memory Database Reference for information about TimesTen connection attributes.

Configuration of automatic client failover

Refer to "Configuring automatic client failover for TimesTen Classic" in Oracle TimesTen In-Memory Database Operations Guide or "Client connection failover" in the Oracle TimesTen In-Memory Database Scaleout User's Guide for complete details on managing client connection failover in TimesTen.

In TimesTen Classic, failover DSNs must be specifically configured through TTC_Server2 and TTC_Servern connection attributes.

Be aware of these TimesTen connection attributes:

• TTC_NoReconnectOnFailover: If this is set to 1 (enabled), TimesTen is instructed to do all the usual client failover processing except for the automatic reconnect. (For example, statement and connection handles are marked as invalid.) This is useful if the application does its own connection pooling or manages its own reconnection to the database after failover. The default value is 0 (reconnect). Also see "TTC_NoReconnectOnFailover" in Oracle TimesTen In-Memory Database Reference.

• TTC_REDIRECT: If this is set to 0 and the initial connection attempt to the desired database or database element fails, then an error is returned and there are no further connection attempts. This does not affect subsequent failovers on that connection. Also see "TTC_REDIRECT" in Oracle TimesTen In-Memory Database Reference.

• TTC_Random_Selection: For TimesTen Classic using generic automatic client failover, the default setting of 1 (enabled) specifies that when failover occurs, the client randomly selects an alternative server from the list provided in TTC_Servern attribute settings. If the client cannot connect to the selected server, it keeps redirecting until it successfully connects to one of the listed servers. With a setting of 0, TimesTen goes through the list of TTC_Servern servers sequentially. Also see "TTC_Random_Selection" in Oracle TimesTen In-Memory Database Reference.

Failover callback functions

If there are custom actions you would like to have occur when there is a failover, you can have TimesTen make a callback to a user-defined function for such actions. This function is called when the attempt to connect to the new database or database element begins, and again after the attempt to connect is complete. This function could be used, for example, to cleanly restore statement handles.

The function API is defined as follows.

typedef SQLRETURN (*ttFailoverCallbackFcn_t)
  (SQLHDBC,      /* hdbc    */
   SQLPOINTER,   /* foCtx   */
   SQLUINTEGER,  /* foType  */
   SQLUINTEGER); /* foEvent */

Where:

• hdbc is the ODBC connection handle for the connection that failed.

• foCtx is a pointer to an application-defined data structure, for use as needed.

• foType is the type of failover. In TimesTen, the only supported value for this is TT_FO_SESSION, which results in the session being reestablished. This does not result in statements being re-prepared.

• foEvent indicates the event that has occurred, with the following supported values:

  • TT_FO_BEGIN: Beginning failover.

  • TT_FO_ABORT: Failover failed. Retries were attempted for the interval specified by TTC_Timeout (minimum value 60 seconds for active standby failover) without success.

  • TT_FO_END: Successful end of failover.

  • TT_FO_ERROR: A failover connection failed but will be retried.

  Note that TT_FO_REAUTH is not supported by TimesTen client failover.

Use a SQLSetConnectOption call to set the TimesTen TT_REGISTER_FAILOVER_CALLBACK option to register the callback function, specifying an option value that is a pointer to a structure of C type ttFailoverCallback_t that is defined as follows in the timesten.h file and refers to the callback function.

typedef struct{
  SQLHDBC                 appHdbc;
  ttFailoverCallbackFcn_t callbackFcn;
  SQLPOINTER              foCtx;
} ttFailoverCallback_t;

Where:

• appHdbc is the ODBC connection handle, and should have the same value as hdbc in the SQLSetConnectOption calling sequence. (It is required in the data structure due to driver manager implementation details, in case you are using a driver manager.)

• callbackFcn specifies the callback function. (You can set this to NULL to cancel callbacks for the given connection. The failover would still happen, but the application would not be notified.)

• foCtx is a pointer to an application-defined data structure, as in the function description earlier.

Set TT_REGISTER_FAILOVER_CALLBACK for each connection for which a callback is desired. The values in the ttFailoverCallback_t structure are copied when the SQLSetConnectOption call is made. The structure need not be kept by the application. If TT_REGISTER_FAILOVER_CALLBACK is set multiple times for a connection, the last setting takes precedence.

/* user defined structure */
struct FOINFO
{
   int callCount;
   SQLUINTEGER lastFoEvent;
};
/* global variable passed into the callback function */
struct FOINFO foStatus;
 
/* the callback function */
SQLRETURN FailoverCallback (SQLHDBC hdbc,
                           SQLPOINTER pCtx,
                           SQLUINTEGER FOType,
                           SQLUINTEGER FOEvent)
{
   struct FOINFO* pFoInfo = (struct FOINFO*) pCtx;
 
   /* update the user defined data */
   if (pFoInfo != NULL)
   {
      pFoInfo->callCount ++;
      pFoInfo->lastFoEvent = FOEvent;
 
      printf ("Failover Call #%d\n", pFoInfo->callCount);
   }
 
   /* the ODBC connection handle */
   printf ("Failover HDBC : %p\n", hdbc);
 
   /* pointer to user data */
   printf ("Failover Data : %p\n", pCtx);
 
   /* the type */
   switch (FOType)
   {
       case TT_FO_SESSION:
       printf ("Failover Type : TT_FO_SESSION\n");
       break;
 
     default:
       printf ("Failover Type : (unknown)\n");
   }
 
   /* the event */
   switch (FOEvent)
   {
     case TT_FO_BEGIN:
       printf ("Failover Event: TT_FO_BEGIN\n");
       break;
 
     case TT_FO_END:
       printf ("Failover Event: TT_FO_END\n");
       break;
 
     case TT_FO_ABORT:
       printf ("Failover Event: TT_FO_ABORT\n");
       break;
 
     case TT_FO_REAUTH:
       printf ("Failover Event: TT_FO_REAUTH\n");
       break;
 
     case TT_FO_ERROR:
       printf ("Failover Event: TT_FO_ERROR\n");
       break;
 
     default:
       printf ("Failover Event: (unknown)\n");
   }
 
 return SQL_SUCCESS;
}
 
/* function to register the callback with the failover connection */
SQLRETURN RegisterCallback (SQLHDBC hdbc)
{
   SQLRETURN rc;
   ttFailoverCallback_t failoverCallback;
 
   /* initialize the global user defined structure */
   foStatus.callCount = 0;
   foStatus.lastFoEvent = -1;
 
   /* register the connection handle, callback and the user defined structure */
   failoverCallback.appHdbc = hdbc;
   failoverCallback.foCtx = &foStatus;
   failoverCallback.callbackFcn = FailoverCallback;
 
   rc = SQLSetConnectOption (hdbc, TT_REGISTER_FAILOVER_CALLBACK,
     (SQLULEN)&failoverCallback);
 
   return rc;
}

When a failover occurs, the callback function would produce output such as the following:

Failover Call #1
Failover HDBC : 0x8198f50
Failover Data : 0x818f8ac
Failover Type : TT_FO_SESSION
Failover Event: TT_FO_BEGIN

Application action in the event of failover

This section discusses these topics:

• Application steps for failover

• Failover delay and retry settings

/*
 * The following code snippet is a simple illustration of how you might handle 
 * the retrying of transient and connection failover errors in a C/ODBC 
 * application. In the interests of simplicity code that is not directly 
 * relevant to the example has been omitted (...). A real application
 * would of course be more complex.
 *
 * This example uses the ODBC 3.5 API.
 */
 
// define maximum retry counts and failover retry delay
#define  MAX_TE_RETRIES    30
#define  MAX_FO_RETRIES    100
#define  FO_RETRY_DELAY    100  // milliseconds   
 
// function return values
#define  SUCCESS           0
#define  FAILURE         (-1)
 
// constants for categorising errors
#define  ERR_OTHER         1
#define  ERR_TRANSIENT     2
#define  ERR_FAILOVER      3
 
// SQLSTATES and native errors
#define    SQLSTATE_TRANSIENT   "TT005"
#define    SQLSTATE_FAILOVER    "08006"
#define    NATIVE_FAILOVER1     47137
#define    NATIVE_FAILOVER2     30105
 
// SQL statements
SQLCHAR * sqlQuery = (SQLCHAR *)"SELECT ...";
SQLCHAR * sqlUpdate = (SQLCHAR *)"UPDATE ...";
 
// Database connection handle
SQLHDBC        dbConn = SQL_NULL_HDBC;
 
// Statement handles
SQLHSTMT      stmtQuery = SQL_NULL_HSTMT;
SQLHSTMT      stmtUpdate = SQL_NULL_HSTMT;
 
// ODBC return code
SQLRETURN rc;
 
// Retry counters
int teRetries; // transient errors
int foRetries; // failover errors
int foDelay = FO_RETRY_DELAY; // failover retry delay in ms
 
// Function to sleep for a specified number of milliseconds
void 
sleepMs( unsigned int ms)
{
    struct timespec rqtm, rmtm;
 
    rqtm.tv_sec = (time_t)(ms / 1000);
    rqtm.tv_nsec = (long)(ms % 1000000);
 
    while (  nanosleep( &rqtm, &rmtm )  )
        rqtm = rmtm;
} // sleepMs
 
// Function to check error stack for transient or failover errors.
// In a real application lots of other kinds of checking would also
// go in here to identify other errors of interest. We'd probably also
// log the errors to an error log.
int 
errorCategory( SQLHANDLE handle, SQLSMALLINT handleType )
{
    SQLRETURN rc;
    SQLSMALLINT i = 1;
    SQLINTEGER native_error;
    SQLCHAR sqlstate[LEN_SQLSTATE+1];
    SQLCHAR msgbuff[1024];
    SQLSMALLINT msglen;
 
    native_error = 0;
    sqlstate[0] = '\0';
    rc = SQLGetDiagRec( handleType, handle, i, sqlstate, &native_error,
                        msgbuff, sizeof(msgbuff), &msglen );
    while (   rc == SQL_SUCCESS  )
    {
        if (  strcmp( sqlstate, SQLSTATE_TRANSIENT ) == 0  )
            return ERR_TRANSIENT;
        else
        if (  native_error == NATIVE_FAILOVER1  )
            return ERR_FAILOVER;
        else
        if (  ( strcmp( sqlstate, SQLSTATE_FAILOVER ) == 0 ) &&
              (native_error == NATIVE_FAILOVER2)  )
            return ERR_FAILOVER;
        rc = SQLGetDiagRec( handleType, handle, ++i, sqlstate,
                            &native_error, msgbuff, sizeof(msgbuff),
                            &msglen );
    }
 
    return ERR_OTHER;
} // errorCategory
 
// Function to perform a rollback
void 
rollBack( SQLHDBC hDbc )
{
    SQLRETURN rc;
 
    rc = SQLEndTran( SQL_HANDLE_DBC, hDbc, SQL_ROLLBACK );
    // Report/log errors (a rollback failure is very, very bad).
    ...
} // rollBack
 
// Function to prepare all statements, bind parameters and bind
// columns.
int 
prepareAll( void )
{
    SQLRETURN rc;
 
    // Prepare the SQL statements and check for errors.
    rc = SQLPrepare( stmtQuery, sqlQuery, SQL_NTS );
    if (  rc != SQL_SUCCESS  )
    {
        rollBack( dbConn );
        return errorCategory( stmtQuery, SQL_HANDLE_STMT );
    }
    rc = SQLPrepare( stmtUpdate, sqlUpdate, SQL_NTS );
...
    // Bind parameters and colums
...
 
    return SUCCESS; // indicate success
} // prepareAll
 
// Function to execute a specific application transaction handling
// retries.
int 
txnSomeTransaction( ... )
{
    SQLRETURN rc;
    SQLLEN    rowcount = 0;
    int needReprepare = 0;
    int result;
 
    // Initialize retry counters
    teRetries = MAX_TE_RETRIES;
    foRetries = MAX_FO_RETRIES;
 
    // main retry loop
    while (  ( teRetries > 0 ) && ( foRetries > 0 )  )
    {
 
        // Do we need to re-prepare?
        while ( needReprepare && ( foRetries > 0 ) )
        {
            msSleep( retryDelay ); // delay before proceeding
            result = prepareAll();
            if (  result == SUCCESS  )
                needReprepare = 0;
            else
            if (  result != ERR_FAILOVER  )
                goto err;
            else
                foRetries--;
        }
 
        // First execute the query
 
        // Set input values for query
        ...
 
        // Execute query
        rc = SQLExecute( stmtQuery );
        if (  rc != SQL_SUCCESS  )
        {
            result = errorCategory( stmtQuery, SQL_HANDLE_STMT );
            rollBack( dbConn );
            switch (  result  )
            {
                case ERR_OTHER:
                    goto err;
                    break;
                case ERR_TRANSIENT:
                    teRetries--;
                    continue; // retry loop
                    break;
                case ERR_FAILOVER:
                    foRetries--;
                    needReprepare = 1;
                    continue; // retry loop
                    break;
            }
        }
 
        // Process results
        while (  (rc = SQLFetch( stmtQuery )) == SQL_SUCCESS  )
        {
            // process next row
            ...
        }
        if (  (rc != SQL_SUCCESS) && (rc != SQL_NO_DATA)  )
        {
            result = errorCategory( stmtQuery, SQL_HANDLE_STMT );
            rollBack( dbConn );
            switch (  result  )
            {
                case ERR_OTHER:
                    goto err;
                    break;
                case ERR_TRANSIENT:
                    teRetries--;
                    continue; // retry loop
                    break;
                case ERR_FAILOVER:
                    foRetries--;
                    needReprepare = 1;
                    continue; // retry loop
                    break;
            }
        }
 
        // Now execute the update
 
        // Set input values for update
        ...
 
        // Execute update
        rc = SQLExecute( stmtUpdate );
        if (  rc != SQL_SUCCESS  )
        {
            ...
        }
 
        // Check number of rows affected
        rc = SQLRowCount( stmtUpdate, &rowcount );
        if (  rc != SQL_SUCCESS  )
        {
            ...
        }
        // Check rowcount and handle unexpected cases
        if (  rowcount != 1  )
        {
            ...
        }
 
        // Finally, commit
        rc = SQLEndTran( SQL_HANDLE_DBC, dbConn, SQL_COMMIT );
        if (  rc != SQL_SUCCESS  )
        {
            ...
        }
 
        return SUCCESS; // all good
    } // retry loop
 
err:
    // if we get here, we ran out of retries or had some other non-retryable
    // error. Report/log it etc. then return failure
    ...
 
    return FAILURE;
} // txnSomeTransaction
 
// Main code
int
main ( int argc, char * argv[] )
{
    int status = 0; // final exit code
 
    ....
 
    // Open the connection  to the database and allocate statement handles
    ...
 
    // Disable auto-commit (this is essential)
    rc = SQLSetConnectAttr( dbConn,
                            SQL_ATTR_AUTOCOMMIT,
                            SQL_AUTOCOMMIT_OFF,
                            0 ); 
    ...
 
    // Prepare all statements, bind etc.
    if (  prepareAll() != SUCCESS  )
    {
        ...
    }
 
    // Do stuff until we are finished
    while (  ...  )
    {
        ...
        if (  txnSomeTransaction( ... ) != SUCCESS  )
        {
            ...
            goto fini;
        }
        ...
    }
 
fini:  // cleanup etc.
    // Release all resources (ODBC and non-ODBC)
    ...
    // Disconnect from database
    ...
 
    // Return final exit code
    return status;
} //main

Creating a grid map and distribution

TimesTen Scaleout includes two new objects for client routing in the timesten.h file:

• TTGRIDMAP: A grid map is a lookup table that maps the topology of a grid. You create a grid map by calling the ttGridMapCreate function with a valid ODBC connection. The function returns a handle to a TTGRIDMAP object.

  Note:

  • A TTGRIDMAP object is not strongly associated with the HDBC connection. Freeing either object does not free the other.

  • A grid map can be shared among many grid distributions and across application threads. Only one grid map is required per application process per database.

  Use the ttGridMapFree function to free a grid map.

• TTGRIDDIST: A grid distribution is an ordered set of types and values that represent the distribution key columns of a table or tables. For distribution keys composed of multiple columns, the order of the types and values must be the same as for the distribution key columns of the table.

  You create a grid distribution by calling the ttGridDistCreate function with the C type, SQL type, length, scale, and precision of the distribution key columns of a table. The function returns a handle to a TTGRIDDIST object. Table 2-3 provides a brief summary of the arguments of the ttGridDistCreate function.

  Note:

  • A TTGRIDDIST object is not associated with a given table. You can use the same TTGRIDDIST object for any table that uses the same types and values in their distribution key columns.

  • A grid distribution cannot be shared across threads. However, multiple grid distributions in different threads can be created using the same grid map.

  Table 2-3 ttGridDistCreate arguments

  Argument	Type	Description
  hdbc	SQLHDBC	Connection handle
  map	TTGRIDMAP	Grid map handle
  cTypes[]	SQLSMALLINT	Array of C bind types in the same order as the distribution key columns
  sqlTypes[]	SQLSMALLINT	Array of SQL bind types in the same order as the distribution key columns
  precisions[]	SQLULEN	Array of precision values in the same order as the distribution key columns
  scales[]	SQLSMALLINT	Array of scale values in the same order as the distribution key columns
  maxSizes[]	SQLLEN	Array of maximum column size values in the same order as the distribution key columns
  nCols	SQLUSMALLINT	Number of columns in the distribution key
  *dist	TTGRIDDIST	Grid distribution handle (OUT)

  
  

  Note:

  The parameters for ttGridDistCreate are similar to those used in a subsequent SQLBindParameter ODBC call.

Use the ttGridDistFree function to free a grid distribution.

TTGRIDMAP map;
TTGRIDDIST dist;

ttGridMapCreate(hdbc, &map);

SQLSMALLINT cTypes[] = { SQL_C_LONG, SQL_C_CHAR };
SQLSMALLINT sqlTypes[] = { SQL_INTEGER, SQL_CHAR };
SQLLEN maxSizes[] = { 4, 20 };

ttGridDistCreate(hdbc, map, cTypes, sqlTypes, NULL, NULL, maxSizes, 2, &dist);

...

ttGridDistFree(hdbc, dist);
ttGridMapFree(hdbc, map);

Setting the distribution key values

With the grid map and distribution defined, set the key values in order to determine the elements in which they are allocated. Call the ttGridDistValueSet function to set the key value for one of the columns in the distribution key. For distribution keys composed of multiple columns, call this function once for every column in the distribution key. Table 2-4 provides a brief summary of the arguments of the ttGridDistValueSet function.

ttGridDistClear(hdbc, dist);

ttGridDistValueSet(hdbc, dist, 1, empId, sizeof(empId));
ttGridDistValueSet(hdbc, dist, 2, "SALES", SQL_NTS);

Getting the element location given a set of key values

Once you set the distribution key values, you can either call for the location of the key values by element IDs or replica set ID:

• Get the element IDs

• Get the replica set ID

SQLSMALLINT elementIds[2];

ttGridDistElementGet(hdbc, dist, elementIds, 2);

#include <timesten.h>

...

TTGRIDMAP map;
TTGRIDDIST dist;

/* Create a grid map using any existing connection. */
ttGridMapCreate(hdbc, &map);

/* The distribution key has two columns: one with TT_INTEGER as data type and
 * one with CHAR(20), in that order. Precision and scale are not necessary. */
SQLSMALLINT cTypes[] = { SQL_C_LONG, SQL_C_CHAR };
SQLSMALLINT sqlTypes[] = { SQL_INTEGER, SQL_CHAR };
SQLLEN maxSizes[] = { 4, 20 };

/* Create grid distribution from the grip map and the specified distribution
 * key column paremeters. */
ttGridDistCreate(hdbc, map, cTypes, sqlTypes, NULL, NULL, maxSizes, 2, &dist);

/* Execution loop. */
while ( ... ) 
{
      SQLSMALLINT elementIds[2];

      /* Clear the existing key values from the distribution map */
      ttGridDistClear(hdbc, dist);

      /* Set the key values for the grid distribution. */
      ttGridDistValueSet(hdbc, dist, 1, key1, sizeof(key1));
      ttGridDistValueSet(hdbc, dist, 2, key2, SQL_NTS);

      /* Get the corresponding element IDs for current key values*/
      ttGridDistElementGet(hdbc, dist, elementIds, 2);

      /* The application uses the element IDs to select a connection to 
       * one of the elements, prepare a statement, bind values, and execute 
       * the statement. */
      ...
}

/* Free the grid distribuion and map. */
ttGridDistFree(hdbc, dist);
ttGridMapFree(hdbc, map);

select 'TTC_REDIRECT=0;
TTC_SERVER='||hostexternaladdress||'/'||serverport,mappedelementid
 from SYS.V$DISTRIBUTION_CURRENT;
< TTC_REDIRECT=0;TTC_SERVER=ext-host3.example.com/6625, 1 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host4.example.com/6625, 2 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host5.example.com/6625, 3 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host6.example.com/6625, 4 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host7.example.com/6625, 5 >
< TTC_REDIRECT=0;TTC_SERVER=ext-host8.example.com/6625, 6 >
6 rows found.

SQLSMALLINT replicaSetId;

ttGridDistReplicaGet(hdbc, dist, replicaSetId);

Supported data types

The TTGRIDDIST object is created using the C types and SQL types available from ODBC. Table 2-7 shows the supported C types and SQL types with their corresponding Database SQL types.

The TTGRIDDIST object supports all signed and unsigned data type variants. For example, it supports both SQL_C_SLONG and SQL_C_ULONG.

You can set NULL values by specifying SQL_NULL_DATA for the valueLen parameter of the ttGridDistValueSet function. The NULL value will always map to the same replica set or element IDs.

Restrictions

Client routing has these restrictions:

• It does not have implicit connection or statement management.

• It does not support date, time, or timestamp data types.

• It does not support explicit type conversion. Applications must specify key values in canonical byte format.

• It does not support character set conversion. It ignores the connection character set.

• Changes in the topology of the grid require that applications free and recreate the grid map.

Failure modes

The client routing API may return an error in these scenarios:

• Incorrect types and values to describe the distribution key columns of the table. In this case, the API will still compute an array of element IDs, but these may not correspond to the real location of the desired key values.

• Unrecognized type codes. If you call the ttGridDistCreate function with unrecognized type codes, the function returns an error.

• Not enough values set for the grid distribution. If you do not provide enough values for the distribution key through the ttGridDistValueSet function, then the ttGridDistElementGet or ttGridDistReplicaGet function would return an error.

• Invalid size of the element IDs array. If you do not provide an array of at least the size of the value of K-safety, the ttGridDistElementGet function would return an error.