1. C Developer's Guide
  2. Working With TimesTen Databases in ODBC
  3. Management of TimesTen Database Connections

Management of TimesTen Database Connections

There are methods to manage TimesTen database connections.

Overview of TimesTen Connections

ODBC applications can connect to a database by referencing either its attributes (host, port number, and so on) or its data source name (DSN). In TimesTen Classic, users can create DSNs directly. In TimesTen Scaleout, a DSN is created for each connectable you define in the grid.

This section covers some basics regarding TimesTen connections and provides references for details.

For TimesTen Scaleout, refer to Oracle TimesTen In-Memory Database Scaleout User's Guide for information about creating a database and connecting to a database, using either a direct connection or a client/server connection. See Creating a Database and Connecting to a Database.

For TimesTen Classic, Oracle TimesTen In-Memory Database Operations Guide contains information about creating a DSN for the database. The type of DSN you create depends on whether your application connects directly to the database or connects through a client:

  • If you intend to connect directly to the database, refer to Managing TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide. There are sections on creating a DSN for a direct connection from Linux or UNIX or from Windows.

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

Note:

  • 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 (forking) a child process, the child must not use the connection.

SQLConnect, SQLDriverConnect, SQLAllocConnect, SQLDisconnect Functions

There are ODBC functions that are available for connecting to a database, allocating memory for the connection, and disconnecting from the database.

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

    Here is the SQLConnect calling sequence: 

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

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

    Here is the SQLDriverConnect calling sequence: 

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

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

    Here is the SQLAllocConnect calling sequence: 

    SQLRETURN SQLAllocConnect(
           SQLHENV        EnvironmentHandle,
           SQLHDBC        PointerToConnectionHandle);

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

    Here is the SQLDisconnect calling sequence: 

    SQLRETURN SQLDisconnect(
          SQLHDBC        ConnectionHandle);

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

Use of the Default DSN

This lists circumstances when a default DSN is used.

In TimesTen Classic, a default DSN, simply named default, can be defined in the odbc.ini or sys.odbc.ini file. See Setting Up a Default DSN in TimesTen Classic in Oracle TimesTen In-Memory Database Operations Guide.

For SQLConnect, if a default DSN has been defined, it is used in these circumstances:

  • If ServerName specifies a data source that cannot be found.

  • If ServerName is a null pointer.

  • If default is specified as the server name. The user name and authentication values are used as is.

For SQLDriverConnect, if a default DSN has been defined, it is used in these circumstances:

  • If the connection string does not include the DSN keyword.

  • If the data source cannot be found.

  • If default is specified as the DSN keyword. The user name and password are used as is.

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

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

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

Connecting To and Disconnecting From a Database

There are methods for connecting to and disconnecting from a database.

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

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

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

The following is a complete program that creates, connects to, and disconnects from a database. 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.) 

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

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

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

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

   /* Your application code here */

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

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

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

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

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

Setting Connection Attributes Programmatically

This shows how to set or override connection attributes programmatically by specifying a connection string when you connect to a database.

This code fragment connects to a database named mydsn and indicates in the SQLDriverConnect call that the application should use a passthrough setting of 3. Note that PassThrough is a general connection attribute. 

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

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

Note:

  • Each direct connection to a database opens several files. An application with many threads, each with a separate connection, has several files open for each thread. Such an application can exceed the maximum allowed (or configured 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.

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