Skip Headers
Oracle® TimesTen In-Memory Database C Developer's Guide
Release 11.2.1

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

Go to previous page
Previous
Go to next page
Next
View PDF

1 Working with TimesTen Data Stores

This chapter describes how to use ODBC to connect to and use an Oracle TimesTen In-Memory Database data store. It includes the following topics:

Managing TimesTen data store connections

The Oracle TimesTen In-Memory Database Operations Guide contains information about creating a DSN for a TimesTen data store. The type of DSN you create depends on whether your application will connect directly to the data store or will connect by a client.

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

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

Notes:

  • In TimesTen, the user name and password must be for a valid user who has been granted CREATE SESSION privilege to connect to the database.

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

The rest of this section covers the following topics:

SQLConnect, SQLDriverConnect, SQLAllocConnect, SQLDisconnect functions

The following ODBC functions are available for connecting to a TimesTen data store and related functionality:

  • SQLConnect: Loads a driver and connects to the data store. 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 TimesTen data store), user name, and password.

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

  • SQLDisconnect: Disconnect from the data store. 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 data store

This section provides examples of connecting to and disconnecting from a TimesTen data store.

Example 1-1 Connect and disconnect (excerpt)

This code fragment invokes SQLConnect and SQLDisconnect to connect to and disconnect from the data store named FixedDs. The first invocation of SQLConnect by any application causes the creation of the FixedDs data store. Subsequent invocations of SQLConnect would connect to the existing data store.

#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);
...

Example 1-2 Connect and disconnect (complete program)

This example contains a complete program that creates, connects to, and disconnects from a data store. The example uses SQLDriverConnect instead of SQLConnect to set up the connection, and uses SQLAllocConnect to allocate memory. It also shows how to get error messages. (In addition, you can refer to "Error handling".)

#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"
              " data store\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 data store.

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 Oracle TimesTen In-Memory Database Reference for specific information about any particular connection attribute.

Example 1-3 Connect and use store-level locking

This code fragment connects to a data store called mydsn and indicates in the SQLDriverConnect call that the application should use data store-level locking rather than the default row-level locking. Note that LockLevel is a general 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);

Note:

Each connection to a TimesTen data store opens several files. This means that an application with many threads, each with a separate connection, has several files open for each thread. Such an application can exceed the maximum number of file descriptors that may be simultaneously open on the operating system. In this case, configure your system to allow a larger number of open files. See "Limits on number of open files" in Oracle TimesTen In-Memory Database Reference.

Access control for connections

Privilege to connect to a TimesTen data store 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.

Managing TimesTen data

This section provides detailed information on working with data in a TimesTen data store. It includes the following topics:

TimesTen #include files

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

Include file Description
timesten.h TimesTen ODBC #include file
tt_errCode.h TimesTen native error codes

SQL statement execution within C applications

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

Note:

Access control privileges are checked both when SQL is prepared and when it is executed in the database. Refer to "Considering TimesTen features for access control" for related information.

SQLExecDirect and SQLExecute functions

There are two ODBC functions to execute SQL statements:

  • SQLExecute: Executes a statement that has already been prepared. This is used in conjunction with SQLPrepare. After the application is done with the results, they can be discarded and SQLExecute can be run again using different parameter values.

    This is typically used for DML statements with bind parameters, or statements that are being executed a relatively large number of times.

  • SQLExecDirect: Prepares and executes a statement.

    This is typically used for DDL statements or for DML statements that would execute only a relatively small number of times and without bind parameters.

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

Executing a SQL statement

You can use the SQLExecDirect function as shown in Example 1-4.

The next section, "Preparing and executing queries and working with cursors", shows usage of the SQLExecute and SQLPrepare functions.

Example 1-4 Executing a SQL statement with SQLExecDirect

This code sample creates a table, called NameID, with two columns: CustID and CustName. The table maps character names to integer identifiers.

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

Note:

Access control privileges are checked both when SQL is prepared and when it is executed in the database. Refer to "Considering TimesTen features for access control" for related information.

Example 1-5 Executing a query and working with the cursor

This example illustrates how to prepare and execute a query using ODBC calls. Error checking has been omitted to simplify the example. In addition to ODBC functions mentioned previously, this example uses SQLNumResultCols to return the number of columns in the result set, SQLDescribeCol to return a description of one column of the result set (column name, type, precision, scale, and nullability), and SQLBindCol to assign the storage and data type for a column in the result set. These are all described in detail in ODBC API reference documentation.

#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, as well as making 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.

Note:

Deferred prepare functionality is not implemented, and not relevant, with the TimesTen direct driver.

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.

Binding parameters

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

ODBC to SQL or PL/SQL type mappings

Table 1-1 documents the mapping between ODBC SQL types and Oracle SQL or PL/SQL types.

Table 1-1 ODBC SQL to TimesTen SQL or PL/SQL type mappings

ODBC type SQL or PL/SQL type

SQL_BIGINT

NUMBER

SQL_BINARY

RAW(p)

SQL_BIT

PLS_INTEGER

SQL_CHAR

CHAR(p)

SQL_DATE

DATE

SQL_DECIMAL

NUMBER

SQL_DOUBLE

NUMBER

SQL_FLOAT

BINARY_DOUBLE

SQL_INTEGER

PLS_INTEGER

SQL_NUMERIC

NUMBER

SQL_REAL

BINARY_FLOAT

SQL_REFCURSOR

REF CURSOR

SQL_ROWID

ROWID

SQL_SMALLINT

PLS_INTEGER

SQL_TIMESTAMP

TIMESTAMP(s)

SQL_TINYINT

PLS_INTEGER

SQL_VARBINARY

RAW(p)

SQL_VARCHAR

VARCHAR2(p)

SQL_WCHAR

NCHAR(p)

SQL_WVARCHAR

NVARCHAR2(p)


Notes:

  • The notation (p) indicates precision is according to the SQLBindParameter argument cbColDef.

  • The notation (s) indicates scale is according to the SQLBindParameter argument ibScale.

  • For SQL types, the table applies only to passthrough parameters for Oracle In-Memory Database Cache (IMDB Cache). Otherwise, refer to "Considerations for SQL parameter type assignments".

  • For ODBC types that map to the NUMBER type, TimesTen ignores any precision and scale specified in the SQLBindParameter call. Thus TimesTen avoids the overhead of checking constraints on the data.

SQLBindParameter function

The ODBC SQLBindParameter function is used to bind parameters for SQL statements. This could include IN, OUT, or IN OUT parameters.

To bind an input parameter through ODBC, use the SQLBindParameter function with a setting of SQL_PARAM_INPUT for the fParamType argument. Refer to ODBC API reference documentation for details about the SQLBindParameter function. Table 1-2 provides a brief summary of its arguments.

To bind an output or input-output parameter through ODBC, use the SQLBindParameter function with a setting of SQL_PARAM_OUTPUT or SQL_PARAM_INPUT_OUTPUT, respectively, for the fParamType argument. As with input parameters, use the fSqlType, cbColDef, and ibScale arguments (as applicable) of the ODBC SQLBindParameter function to specify data types. In addition, use the rgbValue, cbValueMax, and pcbValue arguments of SQLBindParameter.

Table 1-2 SQLBindParameter arguments

Argument Type Description

hstmt

SQLHSTMT

Statement handle.

ipar

SQLUSMALLINT

Parameter number, sequentially from left to right, starting with 1.

fParamType

SQLSMALLINT

Indicating input or output: SQL_PARAM_INPUT, SQL_PARAM_OUTPUT, or SQL_PARAM_INPUT_OUTPUT.

fCType

SQLSMALLINT

C data type of the parameter.

fSqlType

SQLSMALLINT

SQL data type of the parameter.

cbColDef

SQLULEN

The precision of the column or expression of the parameter marker.

ibScale

SQLSMALLINT

The scale of the column or expression of the parameter marker.

rgbValue

SQLPOINTER

Pointer to a buffer for the data of the parameter.

cbValueMax

SQLLEN

Maximum length of the rgbValue buffer, in bytes.

pcbValue

SQLLEN*

Pointer to a buffer for the length of the parameter.


Considerations for SQL parameter type assignments

There is a notable difference between TimesTen and Oracle Database functionality regarding SQL parameter type assignments. For statements that execute within TimesTen, the TimesTen query optimizer determines data types of SQL parameters. Oracle, by contrast, requires the application to specify data types through the fSqlType, cbColDef, and ibScale arguments (as applicable) of the SQLBindParameter function. TimesTen ignores these arguments. An application cannot change or influence type assignments.

Note that the TimesTen behavior does not apply to passthrough statements, when TimesTen is used as IMDB Cache. When statements are passed through to Oracle for execution, the data types must be specified using fSqlType, cbColDef, and ibScale, as applicable.

The TimesTen behavior also does not apply to PL/SQL statements.

Considerations for C parameters used in PL/SQL

When a C parameter is used by a PL/SQL block or procedure, a type conversion may occur, depending on the C type of the parameter and the PL/SQL type or types that are acceptable in that context. If the combination is not supported, an error will occur. These conversions can be from a C type to a SQL or PL/SQL type (IN parameter), from a SQL or PL/SQL type to a C type (OUT parameter), or both (IN OUT parameter).

Binding IN parameters

For IN parameters for use with PL/SQL in TimesTen, use the fSqlType, cbColDef, and ibScale arguments (as applicable) of the ODBC SQLBindParameter function to specify data types. This is in contrast to how SQL input parameters are supported, as noted in "Considerations for SQL parameter type assignments" above.

In addition, the rgbValue, cbValueMax, and pcbValue arguments of SQLBindParameter are used as follows for IN parameters:

  • rgbValue: Before statement execution, points to the buffer where the application places the parameter value to be passed to the application.

  • cbValueMax: For character and binary data, indicates the maximum length of the incoming value that rgbValue points to, in bytes. For all other data types, cbValueMax is ignored, and the length of the value that rgbValue points to is determined by the length of the C data type specified in the fCType argument of SQLBindParameter.

  • pcbValue: Points to a buffer that contains one of the following before statement execution:

    • The actual length of the value that rgbValue points to. For IN parameters, this would be valid for only character or binary data.

    • SQL_NTS for a null-terminated string.

    • SQL_NULL_DATA for a null value.

Binding OUT parameters

The rgbValue, cbValueMax, and pcbValue arguments of the ODBC SQLBindParameter function are used as follows for OUT parameters:

  • rgbValue: During statement execution, points to the buffer where the value returned from the statement should be placed.

  • cbValueMax: For character and binary data, indicates the maximum length of the outgoing value that rgbValue points to, in bytes. For all other data types, cbValueMax is ignored, and the length of the value that rgbValue points to is determined by the length of the C data type specified in the fCType argument of SQLBindParameter. Note that ODBC null-terminates all character data, even if the data is truncated. Therefore, in the case of an OUT parameter that has character data, cbValueMax is greater by 1 than the longest value that can be accepted. And in the case of an OUT parameter that has C NCHAR data, which uses a two-byte null terminator, cbValueMax is greater by 2 than the longest value that can be accepted.

  • pcbValue: Points to a buffer that contains one of the following after statement execution:

    • The actual length of the value that rgbValue points to (for all C types, not just character and binary data). This is the length of the full parameter value, regardless of whether the value can fit in the buffer that rgbValue points to.

    • SQL_NULL_DATA for a null value.

Example 1-6 Binding output parameters

This example shows how to prepare, bind, and execute a PL/SQL anonymous block. The anonymous block assigns bind variable a the value 'abcde' and bind variable b the value 123.

SQLPrepare prepares the anonymous block. SQLBindParameter binds the first parameter (a) as an output parameter of type SQL_VARCHAR and binds the second parameter (b) as an output parameter of type SQL_INTEGER. SQLExecute executes the anonymous block.

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

Binding IN OUT parameters

The rgbValue, cbValueMax, and pcbValue arguments of the ODBC SQLBindParameter function are used as follows for IN OUT parameters:

  • rgbValue: This is first used before statement execution as described in "Binding IN parameters". Then it is used during statement execution as described in the preceding section, "Binding OUT parameters". Note that for an IN OUT parameter, the outgoing value from a statement execution will be the incoming value to the statement execution that immediately follows, unless that is overridden by the application. Also, for IN OUT values bound when you are using data-at-execution, the value of rgbValue serves as both the token that would be returned by the ODBC SQLParamData function and as the pointer to the buffer where the outgoing value will be placed.

  • cbValueMax: For character and binary data, this is first used as described in "Binding IN parameters". Then it is used as described in the preceding section, "Binding OUT parameters". For all other data types, cbValueMax is ignored, and the length of the value that rgbValue points to is determined by the length of the C data type specified in the fCType argument of SQLBindParameter. Note that ODBC null-terminates all character data, even if the data is truncated. Therefore, in the case of an IN OUT parameter that has character data, cbValueMax is greater by 1 than the longest value that can be accepted. And in the case of an IN OUT parameter that has C NCHAR data (which uses a two-byte null terminator), cbValueMax is greater by 2 than the longest value that can be accepted.

  • pcbValue: This is first used before statement execution as described in "Binding IN parameters". Then it is used after statement execution as described in the preceding section, "Binding OUT parameters".

Important:

  • For character and binary data, carefully consider the value you use for cbValueMax. A value that is smaller than the actual buffer size may result in spurious truncation warnings. A value that is greater than the actual buffer size may cause the ODBC driver to overwrite the rgbValue buffer, resulting in memory corruption.

  • TimesTen will return SQL_SUCCESS_WITH_INFO if there are errors in converting OUT or IN OUT parameters. If SQLExecute, SQLExecDirect, or SQLParamData returns SQL_SUCCESS_WITH_INFO, then the values of all OUT and IN OUT parameters are undefined.

Binding duplicate parameters in SQL statements

TimesTen supports either of two modes for binding duplicate parameters in a SQL statement. (Regarding PL/SQL statements, see "Binding duplicate parameters in PL/SQL".)

  • Oracle mode, where multiple instances of the same parameter name are considered to be distinct parameters.

  • Traditional TimesTen mode, as in earlier releases, where multiple instances of the same parameter name are considered to be the same parameter.

You can choose the desired mode through the DuplicateBindMode general connection attribute. DuplicateBindMode=0 (the default) is for Oracle mode, and DuplicateBindMode=1 is for TimesTen mode. Because this is a general connection attribute, different concurrent connections to the same database can use different values. Refer to "DuplicateBindMode" in Oracle TimesTen In-Memory Database Reference for additional information about this attribute.

The rest of this section provides details for each mode, considering the following query:

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

Notes:

  • This discussion applies only to SQL statements issued directly from ODBC (not through PL/SQL, for example).

  • The use of "?" for parameters, not supported in Oracle Database, is supported by TimesTen in either mode.

Oracle mode for duplicate parameters

In Oracle mode, where DuplicateBindMode=0, multiple instances of the same parameter name in a SQL statement are considered to be different parameters. When parameter position numbers are assigned, a number is given to each parameter occurrence without regard to name duplication. The application must, at a minimum, bind a value for the first occurrence of each parameter name. For any subsequent occurrence of a given parameter name, the application has the following choices:

  • It can bind a different value for the occurrence.

  • It can leave the parameter occurrence unbound, in which case it takes the same value as the first occurrence.

In either case, each occurrence still has a distinct parameter position number.

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

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

To use the same value for both occurrences of a:

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

Parameter b is considered to be in position 3 regardless.

In Oracle mode, the SQLNumParams ODBC function returns 3 for the number of parameters in the example.

TimesTen mode for duplicate parameters

In TimesTen mode, where DuplicateBindMode=1, SQL statements containing duplicate parameters are parsed such that only distinct parameter names are considered as separate parameters.

Binding is based on the position of the first occurrence of a parameter name. Subsequent occurrences of the parameter name are not given their own position numbers. All occurrences of the same parameter name take on the same value.

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

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

Note that in TimesTen mode, parameter b is considered to be in position 2, not position 3.

In TimesTen mode, the SQLNumParams ODBC function returns 2 for the number of parameters in the example.

Binding duplicate parameters in PL/SQL

The preceding discussion does not apply within PL/SQL. Instead, PL/SQL semantics apply, whereby you bind a value for each unique parameter. An application calling the following block, for example, would bind only one parameter, corresponding to :a.

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

An application calling the following block would also bind only one parameter:

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

An application calling the following block would bind two parameters, with :a as parameter #1 and :b as parameter #2. The second parameter in each INSERT statement would take the same value as the first parameter in the first INSERT statement:

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

Considerations for floating point data

The BINARY_DOUBLE and BINARY_FLOAT data types store and retrieve the IEEE floating point values Inf, -Inf, and NaN. If an application uses a C language facility such as printf, scanf, or strtod that requires conversion to character data, the floating point values are returned as "INF", "-INF", and "NAN". These character strings cannot be converted back to floating point values.

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.

Important:

For passing REF CURSORs between PL/SQL and an application, TimesTen supports only OUT REF CURSORs, from PL/SQL to the application, and supports a statement returning only a single REF CURSOR.

Example 1-7 Executing a query and working with a REF CURSOR

This example uses a REF CURSOR and demonstrates the basic steps of preparing a query, binding parameters, executing the query, binding results to local variable storage, and fetching the results. Error handling omitted for simplicity. In addition to ODBC functions summarized earlier, this example uses SQLAllocStmt to allocate memory for a statement handle.

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 rowids

Each row in a TimesTen 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 1-1, 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.

Note:

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

Prefetching multiple rows of data

A TimesTen extension to ODBC allows applications to prefetch multiple rows of data from a TimesTen data store 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. For 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 data store. In read-committed isolation mode, the default prefetch value is 5. In serializable isolation mode, 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);

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 manually. You can refer to "Transaction semantics" in Oracle TimesTen In-Memory Database Operations Guide for information about autocommit.

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

Notes:

  • Autocommit mode applies only to the top-level statement executed by SQLExecute or SQLExecDirect. There is no awareness of what occurs inside the statement, and therefore no capability for intermediate autocommits of nested operations.

  • All open cursors are closed upon transaction commit or rollback in TimesTen.

  • The SQLRowCount function can be used to return information about SQL operations. For UPDATE, INSERT, and DELETE statements, the output argument returns the number of rows affected. For other operations, the driver can define the usage of this argument. See "Managing cache groups" regarding special TimesTen functionality. Refer to ODBC API reference documentation for general information about SQLRowCount and its arguments.

Example 1-8 Updating the database and committing the change

This example prepares and executes a statement to give raises to selected employees, then manually commits the changes. Assume autocommit has been previously disabled.

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

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.

Example 1-9 DML returning

This example is adapted from Example 1-8 in the previous section.

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_CHAR, 0, 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);
 
}

This returns "King" as the recipient of the raise.

Calling TimesTen built-in procedures within C applications

"Built-In Procedures" in Oracle TimesTen In-Memory Database Reference describes TimesTen built-in procedures that extend standard ODBC functionality. You can invoke these procedures using ODBC call syntax. Use the following format:

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

This ODBC example calls the ttCkpt procedure to initiate a fuzzy checkpoint.

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

Note:

The Oracle TimesTen In-Memory Database Reference documents any required privileges for TimesTen built-in procedures. For example, ttCkpt requires ADMIN privilege.

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.

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.

Setting a timeout value for SQL statements

To control how long SQL statements should execute before timing out, you can set the SQL_QUERY_TIMEOUT option using a SQLSetStmtOption or SQLSetConnectOption call to specify a timeout value, in seconds. Despite the name, this timeout value applies to any executable SQL statement, not just queries.

In TimesTen you can specify this timeout value for any connection, and hence for any statement, by using the SqlQueryTimeout DSN attribute. If you set SqlQueryTimeout in the DSN specification, its value becomes the default value for all subsequent connections to the data store. A call to SQLSetConnectOption with the SQL_QUERY_TIMEOUT option overrides any default value that a connection may have inherited and applies to any statement from that connection. A call to SQLSetStmtOption with the SQL_QUERY_TIMEOUT option overrides any default value inherited from the connection as well as any value set using SQLSetConnectOption, but applies only to the statement.

The query timeout limit has effect only when a SQL statement is actively executing. A timeout does not occur during commit or rollback. For transactions that execute a large number of UPDATE, DELETE or INSERT statements, the commit or rollback phases may take a long time to complete. During that time the timeout value is ignored.

Note:

If both a lock timeout and a SqlQueryTimeout value are specified, the lesser of the two values causes a timeout first.

Regarding lock timeouts, you can refer to "ttLockWait" (built-in procedure) or "LockWait" (connection attribute) in Oracle TimesTen In-Memory Database Reference, or to "Check for deadlocks and timeouts" in Oracle TimesTen In-Memory Database Troubleshooting Procedures Guide.

Setting a threshold value for SQL statements

You can configure TimesTen to write a warning to the support log and throw an SNMP trap when the execution of a SQL statement exceeds a specified time duration, in seconds. Execution continues and is not affected by the threshold.

The name of the SNMP trap is ttQueryThresholdWarnTrap. See Oracle TimesTen In-Memory Database Error Messages and SNMP Traps for information about configuring SNMP traps. Despite the name, this threshold applies to any executable SQL statement.

By default, the application obtains the threshold from the QueryThreshold connection attribute setting. Setting the TT_QUERY_THRESHOLD option in a SQLSetConnectOption call overrides the connection attribute setting for the current connection.

To set the threshold with SQLSetConnectOption:

RETCODE SQLSetConnectOption(hdbc, TT_QUERY_THRESHOLD, seconds);

Setting the TT_QUERY_THRESHOLD option in a SQLSetStmtOption call overrides the connection attribute setting as well as any setting through SQLSetConnectOption. It applies to SQL statements executed using the ODBC statement handle.

To set the threshold with SQLSetStmtOption:

RETCODE SQLSetStmtOption(hstmt, TT_QUERY_THRESHOLD, seconds);

You can retrieve the current value of TT_QUERY_THRESHOLD by using the SQLGetConnectOption or SQLGetStmtOption ODBC function:

RETCODE SQLGetConnectOption(hdbc, TT_QUERY_THRESHOLD, paramvalue);

RETCODE SQLGetStmtOption(hstmt, TT_QUERY_THRESHOLD, paramvalue);

Managing cache groups

In IMDB Cache, following the execution of a FLUSH CACHE GROUP, LOAD CACHE GROUP, REFRESH CACHE GROUP, or UNLOAD CACHE GROUP statement, the ODBC function SQLRowCount returns the number of cache instances that were flushed, loaded, refreshed, or unloaded.

For related information, see "Determining the number of cache instances affected by an operation" in Oracle In-Memory Database Cache User's Guide.

Refer to ODBC API reference documentation for general information about SQLRowCount.

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

This option specifies the collating sequence used for linguistic comparisons. See "Monolingual linguistic sorts" and "Multilingual linguistic sorts" in Oracle TimesTen In-Memory Database Operations Guide for supported linguistic sorts.

It takes a string value. The default is "BINARY".

Also see the description of the NLS_SORT connection attribute, which has the same functionality, in "NLS_SORT" in Oracle TimesTen In-Memory Database Reference. Note that TT_NLS_SORT, being a runtime option, takes precedence over the NLS_SORT connection attribute.

TT_NLS_LENGTH_SEMANTICS

This option specifies whether byte or character semantics is used. The possible values are:

  • TT_NLS_LENGTH_SEMANTICS_BYTE (default)

  • TT_NLS_LENGTH_SEMANTICS_CHAR

Also see the description of the NLS_LENGTH_SEMANTICS connection attribute, which has the same functionality, in "NLS_LENGTH_SEMANTICS" in Oracle TimesTen In-Memory Database Reference. Note that TT_NLS_LENGTH_SEMANTICS, being a runtime option, takes precedence over the NLS_LENGTH_SEMANTICS connection attribute.

TT_NLS_NCHAR_CONV_EXCP

This option specifies whether an error is reported when there is data loss during an implicit or explicit character type conversion between NCHAR or NVARCHAR2 data and CHAR or VARCHAR2 data during SQL operations. The option does not apply to conversions done by ODBC as a result of binding.

The possible values are:

  • TRUE: Errors during conversion are reported.

  • FALSE: Errors during conversion are not reported (default).

Also see the description of the NLS_NCHAR_CONV_EXCP connection attribute, which has the same functionality, in "NLS_NCHAR_CONV_EXCP" in Oracle TimesTen In-Memory Database Reference. Note that TT_NLS_NCHAR_CONV_EXCP, being a runtime option, takes precedence over the NLS_NCHAR_CONV_EXCP connection attribute.

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.

ODBC 2.0 data type ODBC 3.0 data type
HDBC SQLHDBC
HENV SQLHENV
HSTMT SQLHSTMT
HWND SQLHWND
LDOUBLE SQLDOUBLE
RETCODE SQLRETURN
SCHAR SQLSCHAR
SDOUBLE SQLFLOATS
SDWORD SQLINTEGER
SFLOAT SQLREAL
SWORD SQLSMALLINT
UCHAR SQLCHAR
UDWORD SQLUINTEGER
UWORD SQLUSMALLINT

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.

Considering TimesTen features for access control

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

This section introduces access control as it relates to SQL operations, data store connections, XLA, and C utility functions.

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

For DDL statements, CREATE TABLE requires the CREATE TABLE privilege in the user's schema, or CREATE ANY TABLE in any other schema. ALTER TABLE requires ownership or the ALTER ANY TABLE system privilege. DROP TABLE requires ownership or the DROP ANY TABLE system privilege. There are no object-level ALTER or DROP privileges.

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

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

In addition, access control affects the following topics covered in this document:

Notes:

  • Access control cannot be disabled.

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

Managing error and failure conditions

This section discusses the following topics:

Error handling

This section includes the following topics:

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.

Example 1-10 Checking an ODBC function call for errors

This example shows that after a call to SQLAllocConnect, you can check for an error condition. If one is found, an error message is displayed and program execution is terminated.

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.

Fatal errors

Fatal errors are those that make the data store inaccessible until after error recovery. When a fatal error occurs, all data store connections are required to disconnect. No further operations may complete. Fatal errors are indicated by TimesTen error codes 846 and 994. Error handling for these errors should be different from standard error handling. In particular, the application error-handling code should include a disconnect from the data store.

Non-fatal errors

Non-fatal errors include simple errors such as an INSERT statement that violates unique constraints. This category also includes some classes of application and process failures.

TimesTen returns non-fatal errors through the normal error-handling process and requires the application to check for and identify them.

When a data store is affected by a non-fatal error, an error may be returned and the application should take appropriate action. In some cases, such as process failure, no error is returned, but TimesTen automatically rolls back the transactions of the failed process.

An application can handle non-fatal errors by modifying its actions or, in some cases, rolling back one or more offending transactions.

Warnings

TimesTen returns warnings when something unexpected occurs that you may want to know about. Some examples of events that cause TimesTen to issue a warning include:

  • Checkpoint failure

  • Use of a deprecated TimesTen feature

  • Truncation of some data

  • Execution of a recovery process upon connect

We strongly encourage application developers to include code that checks for warnings, as they can indicate application problems.

Recovering after fatal errors

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

  • Every connection to the data store is invalidated and applications are required to disconnect from the invalidated data store.

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

  • The recovered data store 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 DSN attribute is set, TimesTen creates an empty data store.

Automatic client failover

Automatic client failover, used in High Availability scenarios when failure of a TimesTen node results in failover (transfer) to an alternate node, automatically reconnects applications to the new node. The standby node becomes the active node due to failure of the previously active node. TimesTen provides features that allow applications to be alerted when this happens, so they can take any appropriate action.

This section discusses the TimesTen implementation of automatic client failover, covering the following topics:

Automatic client failover is complementary to Oracle Clusterware, though the two features are not dependent on each other. You can also refer to "Using Oracle Clusterware to Manage Active Standby Pairs" in Oracle TimesTen In-Memory Database TimesTen to TimesTen Replication Guide.

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 DSN attribute TTC_TIMEOUT is reached.

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

Notes:

  • The features described here apply only in client/server mode, not for direct connections.

  • TimesTen supports automatic client failover only in the active standby pair replication configuration, where the clients are to be connected to the node currently in the active role. When a failover connection is attempted, the server will reject it if it is not active.

  • Functionality is similar to that of Oracle TAF (Transparent Application Failover) and FAN (Fast Application Notification), but TimesTen does not use the FAN or TAF libraries.

The following public connection options will be propagated to the new connection. The corresponding connection attribute 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

Notes:

  • Like other DSN attributes, TTC_Server2, TTC_Server_DSN2, and TCP_Port2 can be specified in the connection string, overriding any settings in the DSN.

  • If TTC_Server2 is specified but TTC_Server_DSN2 and TCP_Port2 are not, then TTC_Server_DSN2 is set to the TTC_Server_DSN value and TCP_Port2 is set to the TCP_Port value.

  • TTC_Server and TTC_Server2 can have the same setting if it is a virtual IP address.

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

Notes:

  • If the client library cannot connect to TTC_Server_DSN, it will try the failover alternative, as if it had received an explicit failover request.

  • If the client library loses the connection to the server, it will fail over and attempt to switch to the alternate node.

  • If the active node fails before the client registration is successfully propagated by replication to the standby, the client will not receive a failover message and the registration will be lost. However, the client library will eventually notice (through TCP) that its connection to the former active server has been lost, and it can then initiate a failover attempt.

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

Notes:

  • Because the callback function executes asynchronously to the main thread of your application, it should generally perform only simple tasks, such as setting flags that are polled by the application. However, there is no such restriction if the application is designed for multithreading. In that case, the function could even make ODBC calls, for example, but it is only safe to do so if the foEvent value TT_FO_END has been received.

  • It is up to the application to manage the data pointed to by the foCtx setting.

Example 1-11 Failover callback function and registration

This example shows the following:

  • A globally defined user structure type, FOINFO, and the structure variable foStatus of type FOINFO.

  • A callback function, FailoverCallback(), that updates the foStatus structure whenever there is a failover.

  • A registration function, RegisterCallback(), that does the following:

    • Declares a structure, failoverCallback, of type ttFailoverCallback_t.

    • Initializes foStatus values.

    • Sets the failoverCallback data values, consisting of the connection handle, a pointer to foStatus, and the callback function (FailoverCallback).

    • Registers the callback function with a SQLSetConnectOption call that sets TT_REGISTER_FAILOVER_CALLBACK as a pointer to failoverCallback.

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