2 Working with TimesTen Databases

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 details about these functions.

Connecting to and disconnecting from a database

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

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

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

#ifdef WIN32
# include <windows.h>
#else
# include <sqlunix.h>
#endif
#include <sql.h>
#include <sqlext.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_1121;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 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;LockLevel=1", SQL_NTS,
    ConnStrOut, sizeof (ConnStrOut),
    &cbConnStrOut, SQL_DRIVER_NOPROMPT);

Access control for connections

Privilege to connect to the 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.

TimesTen #include files

In addition to standard C #include files, your application must include the following TimesTen #include files.

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 <sql.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 number of rows to fetch at one time, using SQLSetStmtOption options documented in "Option support for 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 SQLExecute to initiate the SELECT statement. See "SQLExecDirect and SQLExecute functions".

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

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.

#include <sql.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);
   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 is expected to be compiled by the SQL engine so that information about the SQL statement, such as column descriptions for the result set, is available to the application and accessible through calls such as SQLDescribeCol. To achieve this functionality, the SQLPrepare call must be sent to 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 allows applications to prefetch multiple rows of data into the ODBC driver buffer. This can increase the performance of applications that use the Read Committed or Serializable isolation level.

The TT_PREFETCH_COUNT connection option determines how many rows a SQLFetch call will prefetch. This option is available for both direct access and client/server applications.

TT_PREFETCH_COUNT can be set in a call to either SQLSetConnectOption or SQLSetStmtOption. 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 will prefetch 100 rows. Subsequent SQLFetch calls will fetch from the ODBC buffer instead of from the database, until the buffer is depleted. After it is depleted, the next SQLFetch call will fetch another 100 rows into the buffer, and so on.

To disable prefetch, set TT_PREFETCH_COUNT to 1.

When the prefetch count is set to 0, TimesTen uses a default value, depending on the isolation level you have set for the database. With Read Committed isolation level, the default prefetch value is 5. With Serializable isolation level, the default is 128. The default prefetch value is the optimum 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 set the isolation level as follows:

rc = SQLSetConnectOption(hdbc, SQL_TXN_ISOLATION, SQL_TXN_READ_COMMITTED);

Or:

rc = SQLSetConnectOption(hdbc, SQL_TXN_ISOLATION, SQL_TXN_SERIALIZABLE);

Binding parameters and executing statements

This sections 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 IN parameters

• Binding OUT parameters

• Binding IN OUT 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

{
  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 */

SQLBindParameter(..., 1, ...); /* both occurrences of :a */
SQLBindParameter(..., 2, ...); /* 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, where a REF CURSOR is a handle to a cursor over a SQL result set and can be passed between PL/SQL and an application. In TimesTen, the cursor can be opened in PL/SQL then the REF CURSOR can be passed to the application. 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, allowing 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 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 OUT parameters" for information about these and other SQLBindParameter arguments.

3. Call SQLExecute to execute the statement.

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

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.

refcursor_example(SQLHDBC hdbc)
{
  SQLCHAR*      stmt_text;
  SQLHSTMT      plsql_hstmt;
  SQLHSTMT      refcursor_hstmt;
  SQLINTEGER    deptid;
  SQLINTEGER    empid;
  SQLCHAR       lastname[30];
 
  /* 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);
 
  /* set the value for :deptid */
  deptid = 30;
 
  /* 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's statement handle and drop both handles */
  SQLFreeStmt(refcursor_hstmt, SQL_DROP);
  SQLFreeStmt(plsql_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 OUT parameters. Bind the OUT 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 specification" in Oracle TimesTen In-Memory Database SQL Reference for additional information about rowids and the ROWID data type, including usage and life.

Working with synonyms

TimesTen supports private and public synonyms (aliases) for database objects such as tables, views, sequences, and PL/SQL objects. Synonyms are often used for security to mask object names and object owners, or for convenience to simplify SQL statements.

To create a private synonym for table foo in your schema:

CREATE SYNONYM synfoo FOR foo;

To create a public synonym for foo:

CREATE PUBLIC SYNONYM pubfoo FOR foo;

A private synonym exists in the schema of a specific user and shares the same namespace as database objects such as tables, views, and sequences. A private synonym cannot have the same name as a table or other object in the same schema.

A public synonym does not belong to any particular schema, is accessible to all users, and can have the same name as any private object.

To create a synonym you must have the CREATE SYNONYM or CREATE PUBLIC SYNONYM privilege, as applicable. To use a synonym you must have appropriate privileges to access the underlying object.

For general information about synonyms, see "Understanding synonyms" in Oracle TimesTen In-Memory Database Operations Guide. For information about the CREATE SYNONYM and DROP SYNONYM statements, see "SQL Statements" in Oracle TimesTen In-Memory Database SQL Reference.

Making and committing changes to the database

By default in TimesTen, autocommit is enabled, so that any DML change you make (update, insert, or delete) is committed automatically. It is recommended, however, that you disable this feature and commit (or roll back) your changes explicitly. You can refer to "Transaction semantics" in Oracle TimesTen In-Memory Database Operations Guide for information about autocommit.

With autocommit disabled, you can commit or roll back a transaction using the SQLTransact ODBC function. Refer to ODBC API reference documentation for details about this function.

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. See  */
  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 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, applying to any SQLExecute, SQLExecDirect, or SQLFetch call.

• Setting a timeout value for SQL statements

• Setting a threshold value for SQL statements

For the former, if the timeout duration is reached, the statement stops executing and an error is thrown. For the latter, if the threshold is reached, an SNMP trap is thrown but execution continues.

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 IMDB cache

This section discusses features related to the use of IMDB Cache:

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

• Determining passthrough status

• Managing cache groups

See Oracle In-Memory Database Cache User's Guide for information about IMDB Cache.

rc = SQLExecDirect (hstmt, "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 #include 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

Setting up user-specified parallel replication

For applications that have very predictable transactional dependencies and do not require the commit order on the replica database to be the same as that on the originating database, TimesTen supports parallel replication. This feature allows replication of multiple user-specified tracks of transactions in parallel. See "Increasing replication throughput for other replication schemes" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide for general information about parallel replication.

User-specified parallel replication is enabled through the TimesTen data store attributes ReplicationParallelism and ReplicationApplyOrdering, as described in "Data store attributes" in Oracle TimesTen In-Memory Database Reference. The track number for transactions on a connection can be specified through the TimesTen general connection attribute ReplicationTrack, the ALTER SESSION parameter REPLICATION_TRACK, or in ODBC through the TT_REPLICATION_TRACK connection option, as noted in "Option support for SQLSetConnectOption and SQLGetConnectOption".

ODBC 3.0 data types

The data types used in ODBC 2.0 and prior have been renamed in ODBC 3.0 for ISO 92 standards compliance. The sample programs shipped with TimesTen have been written using SQL 3.0 data types. The following table maps 2.0 types to their 3.0 equivalents.

Either version of data types may be used with TimesTen without restriction.

Note also that the FAR modifier that is mentioned in ODBC 2.0 documentation is not required.

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 demo programs provided with TimesTen show examples of error checking.

Errors can be checked using either the TimesTen error code (error number) or error string, as defined in the install_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.

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

Error and warning levels

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, applications are required to disconnect from the invalidated database. Shared memory from the old TimesTen instance will not be freed until all active connections at the time of the error have disconnected.

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

If no checkpoint or transaction log files exist and the AutoCreate first connection attribute is set, TimesTen creates an empty database.

Features and functionality of automatic client failover

When a client failover occurs, no state other than the connection handle is preserved. All client statement handles are marked as invalid. API calls on these statement handles will generally return SQL_ERROR with a distinctive failover error code, defined in tt_errCode.h, such as:

SQLSTATE = S1000 "General Error", native error = tt_ErrFailoverInvalidation

The exception to this is for SQLError and SQLFreeStmt calls, which would behave normally.

In addition, note the following:

• The socket to the old server is closed. There is no attempt to call SQLDisconnect.

• In connecting to the alternate TimesTen node, the same connection string that was returned from the original connection request is used, other than resetting attributes as appropriate to indicate the new server DSN.

• 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 alternate server, the next failover request results in an attempt to reconnect to the original server. If that fails, alternating attempts are made to connect to the two servers until a timeout value specified by the TimesTen client connection attribute TTC_Timeout is reached. (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.

When failover occurs, TimesTen makes a callback to a user-defined function that you register. This function takes care of any custom actions you want to occur in a failover situation.

The following public connection options will be 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_WARN_POSSIBLE_TRUNC_BINDING 
TT_WARN_SQLCBIGINT_BINDING
TT_CONNECTION_CHARACTER_SET (ConnectionCharacterSet)
TT_REGISTER_FAILOVER_CALLBACK

The following options will be 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)

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

SQL_QUERY_TIMEOUT
TT_PREFETCH_COUNT

The following attributes for the logical server DSN in sys.ttconnect.ini are equivalent to TTC_Server, TTC_Server_DSN, and TCP_Port, but for the alternate server.

TTC_Server2
TTC_Server_DSN2
TCP_Port2

Setting any of TTC_Server2, TTC_Server_DSN2, or TCP_Port2 implies the following:

• You intend to use automatic client failover.

• You understand that a new thread will be created for your application to support the failover mechanism.

• You have linked your application with a thread library.

The following new connection attribute specifies a port range for the port where the failover thread will listen for failover notifications:

TTC_FAILOVERPORTRANGE

Set this as a lower and upper value separated by hyphen. TimesTen supports setting a port range to accommodate firewalls between the client and server. By default, a port chosen by the operating system will be used.

Failover callback functions

When failover occurs, TimesTen makes a callback to your user-defined function for any desired action. This function is called when the attempt to connect to the alternate server 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 (modeled on a corresponding TAF function):

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, as would be the case with TAF.

• foEvent indicates the event that has occurred, with supported values as for FAN and TAF:

  • TT_FO_BEGIN: Beginning failover.

  • TT_FO_ABORT: Failover failed. Retries were attempted for the interval specified by TTC_Timeout 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, which 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 will still happen, but the application will 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 will be 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