There isn't a self-test suite provided for this component.

Useful resources for how to test this can be found at:

  https://compscinotes.wordpress.com/2010/04/18/unixodbc-mysql-sample-program/
  http://www.unixodbc.org/unixODBCsetup.html
  http://www.easysoft.com/developer/languages/c/odbc_tutorial.html

From the later, a couple of simple commands to run are:

$ odbcinst --version

and

$ odbcinst -q -s

just to make sure you have the correct new version of the package installed.

The Oracle Instant Client is the only package in Solaris known to use
unixODBC, and this can be used to provide some more testing.

Install all the Instant Client Solaris packages with:

$ sudo pkg install \*instantclient\*

Create a file called .odbc.ini in your home directory containing the
following:

$ cat ~/.odbc.ini

[plus1]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = /usr/oracle/instantclient/12.1/lib/64/libsqora.so.12.1
DSN = TestDBDSN
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName =
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID =
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
SQLTranslateErrors=F
MaxTokenSize=8192
AggregateSQLType=FLOAT

Create a file called tnsnames.ora containing the following:

$ cat tnsnames.ora
plus1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = adc2171609.us.oracle.com)
          (PORT = 1521)
        )
    )
    (CONNECT_DATA =
      (SID = plus1)
    )
  )

Create a file called odbcconnect.c containing the following:

$ cat odbcconnect.c
/*  Copyright (c) 2004, 2012, Oracle and/or its affiliates.
 *  All rights reserved.
 */

/*
   NAME
     odbcconnect.c - Simple ODBC Connect demo

   DESCRIPTION
     Basic ODBC Connect demo
*/

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

#define STR_LEN 50

int main (int argc, char *argv[])
{
  HENV          henv;               /* environment handle */
  HDBC          hdbc;               /* connection handle */
  HSTMT         hstmt;              /* statement handle */
  SDWORD        retcode;            /* return code */
  UCHAR         info[STR_LEN];      /* info string for SQLGetInfo */
  SQLSMALLINT   cbInfoValue;
  SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
  SQLINTEGER    NativeError;
  SQLSMALLINT   MsgLen;

  retcode = SQLAllocEnv (&henv);
  retcode = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (void *)
SQL_OV_ODBC3,
              SQL_IS_INTEGER);
  retcode = SQLAllocConnect (henv, &hdbc);
  retcode = SQLConnect (hdbc, "plus1", SQL_NTS, "scott", SQL_NTS, "tiger",
              SQL_NTS);

  if (retcode != SQL_SUCCESS)
  {
    if ((retcode = SQLGetDiagRec (SQL_HANDLE_DBC, hdbc, 1, SqlState,
                   &NativeError, Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA)
      printf ("SqlState = %s\n Message = %s\n", SqlState, Msg);
    goto EXIT;
  }

  retcode = SQLGetInfo (hdbc, SQL_DBMS_VER, &info, STR_LEN, &cbInfoValue);

  if (retcode != SQL_SUCCESS)
  {
    if ((retcode = SQLGetDiagRec (SQL_HANDLE_DBC, hdbc, 1, SqlState,
                   &NativeError, Msg, sizeof(Msg), &MsgLen)) != SQL_NO_DATA)
      printf ("SqlState = %s\n Message = %s\n", SqlState, Msg);
    goto EXIT;
  }

  printf ("Current DBMS version is %s\n", info);

EXIT:
  SQLDisconnect (hdbc);
  SQLFreeConnect (hdbc);
  SQLFreeEnv (henv);
  return 0;
}

Compile this with:

$ gcc -I/usr/include/odbc -m64 -o odbcconnect odbcconnect.c -lodbc

In order to run this, you will need to:

  1/ set LD_LIBRARY_PATH to the directory containing the Instant Client
driver.
  2/ Set TNS_ADMIN to the directory containing the tnsnames.ora file.
  3/ Set TWO_TASK to the name of the database (plus1 in the above example).

Running the odbcconnect program should give you something like:

$ ./do_run.sh
+ ./odbcconnect
+ TNS_ADMIN=/export/home/richb/userland/bugs/23210080/tianfang
+ TWO_TASK=plus1
+ LD_LIBRARY_PATH_32=/usr/oracle/instantclient/12.1/lib
+ LD_LIBRARY_PATH_64=/usr/oracle/instantclient/12.1/lib/64
Current DBMS version is 12.02.0020

----

Another test using the previous database and setup...

Create a file called odbcselect.c containing the following:

$ cat odbcselect.c
/* Copyright (c) 2004, 2010, Oracle and/or its affiliates.
All rights reserved. */

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#define  STR_LEN 50

int main (int argc, char *argv[])
{
  HENV          henv;          /* environment handle */
  HDBC          hdbc;          /* connection handle  */
  SQLHSTMT      hstmt;         /* statement handle   */
  SDWORD        retcode;       /* return code        */
  SQLCHAR *     stmt = "select ename, empno from emp where empno orderby
empno";
  SQLCHAR       empname[STR_LEN] = "", job[STR_LEN];
  SQLLEN        enind, jind;
  SQLUINTEGER   eno = 0;
  SQLCHAR       SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
  SQLINTEGER    NativeError;
  SQLSMALLINT   MsgLen;

  retcode = SQLAllocEnv (&henv);
  retcode = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (void *)
SQL_OV_ODBC3,
              SQL_IS_INTEGER);
  retcode = SQLAllocConnect (henv, &hdbc);
  retcode = SQLConnect (hdbc, "plus1", SQL_NTS, "scott", SQL_NTS, "tiger",
              SQL_NTS);
  if (retcode != SQL_SUCCESS)
    {
      if ((retcode = SQLGetDiagRec (SQL_HANDLE_DBC, hdbc, 1, SqlState,
                       &NativeError, Msg, sizeof (Msg),
                       &MsgLen)) != SQL_NO_DATA)
      printf ("SqlState = %s\n Message = %s\n", SqlState, Msg);
      goto EXIT;
    }
  retcode = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
  SQLBindCol (hstmt, 1, SQL_C_CHAR, empname, sizeof (empname), &enind);
  SQLBindCol (hstmt, 2, SQL_C_ULONG, &eno, 0, &jind);

  SQLExecDirect (hstmt, "select ename, empno from emp", SQL_NTS);

  while ((retcode = SQLFetch (hstmt)) != SQL_NO_DATA)
    {
      if (enind == SQL_NULL_DATA)
        printf ("NULL  \n");
      else
        printf ("EMPNAME=%s ", empname);

      if (jind == SQL_NULL_DATA)
        printf ("NULL  \n");
      else
        printf ("EMPNO=%d\n", eno);
    }

EXIT:
  SQLFreeStmt (hstmt, SQL_CLOSE);
  SQLDisconnect (hdbc);
  SQLFreeConnect (hdbc);
  SQLFreeEnv (henv);
  return 0;
}

Compile this with:

$ gcc -I/usr/include/odbc -m64 -o odbcselect odbcselect.c -lodbc

Set the same environment variables.

Running the odbcselect program should give you something like:

$ ./do_run.sh
+ ./odbcselect
+ TNS_ADMIN=/export/home/richb/userland/bugs/23210080/tianfang
+ TWO_TASK=plus1
+ LD_LIBRARY_PATH_32=/usr/oracle/instantclient/12.1/lib
+ LD_LIBRARY_PATH_64=/usr/oracle/instantclient/12.1/lib/64
EMPNAME=SMITH EMPNO=7369
EMPNAME=ALLEN EMPNO=7499
EMPNAME=WARD EMPNO=7521
EMPNAME=JONES EMPNO=7566
EMPNAME=MARTIN EMPNO=7654
EMPNAME=BLAKE EMPNO=7698
EMPNAME=CLARK EMPNO=7782
EMPNAME=SCOTT EMPNO=7788
EMPNAME=KING EMPNO=7839
EMPNAME=TURNER EMPNO=7844
EMPNAME=ADAMS EMPNO=7876
EMPNAME=JAMES EMPNO=7900
EMPNAME=FORD EMPNO=7902
EMPNAME=MILLER EMPNO=7934

----

One more test using the same database and setup...

Create a file called stmt_cache.c containing the following:

$ cat stmt_cache.c

/* Copyright (c) 2005, 2006, Oracle. All rights reserved.  */

/*
   NAME
     stmt_cache.c - Statement caching demo

   DESCRIPTION
     Run this demo in following modes and compare the result
     1. Statement caching enabled
        Make sure following options are added in odbc.ini file
            StatementCache=T
            CacheBufferSize=20
     2. Statement caching disabled
        To disable statement caching adjust 'StatementCache' to false
              StatementCache=F
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <sql.h>
#include <sqlext.h>

#define QUERYLEN  500
#define NUM_STMTS 500

SQLHSTMT hstmt[NUM_STMTS];
char *sqlquery[NUM_STMTS];
SQLHENV m_henv;               /* environment handle */
SQLHDBC m_hdbc = NULL;        /* connection handle */
int retCode;
SQLCHAR SqlState[6], Msg[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER NativeError;
SQLSMALLINT MsgLen;

static void call_perf_test();
static void execute_query();
static void printSQLError (long , SQLHANDLE );

int
main()
{
    struct timeval tim;
    double tot_time = 0.0, start_time = 0.0, end_time = 0.0;

    if (!gettimeofday (&tim, NULL)) {
        start_time = (double)((tim.tv_sec * 1000000 ) + tim.tv_usec);
    } else {
        return -1;
    }
    call_perf_test();
    if (!gettimeofday (&tim, NULL)) {
        end_time = (double) ((tim.tv_sec * 1000000 ) + tim.tv_usec);
    } else {
        return -1;
    }
  tot_time = (double) (end_time - start_time) / 100000;
  (void) printf ("\nTotal time taken is = %lf\n", tot_time);

  return 0;
}

void
call_perf_test()
{
    int count = 0;
    char *buf1 = (char *) malloc(sizeof(char) * QUERYLEN);
    char *buf2 = (char *) malloc(sizeof(char) * QUERYLEN);
    sqlquery[0] =(char *) malloc(sizeof(char) * NUM_STMTS);

    for (count=0; count < NUM_STMTS; count++) {
        sqlquery[count]=(char *)malloc(sizeof(char)*QUERYLEN);
    }

    retCode = SQLAllocEnv (&m_henv);
    retCode = SQLSetEnvAttr (m_henv, SQL_ATTR_ODBC_VERSION,
                             (void *) SQL_OV_ODBC3, SQL_IS_INTEGER);
    retCode = SQLAllocConnect (m_henv, &m_hdbc);
    retCode = SQLConnect (m_hdbc, "plus1", SQL_NTS,
                          "scott", SQL_NTS, "tiger", SQL_NTS);

    for (count = 0; count < NUM_STMTS-5; count++) {
        sprintf(buf1, "SELECT empno, ename, job, mgr, hiredate, sal, comm,
deptno ");
        sprintf(buf2, " FROM emp");
        (void) strcat(buf1, buf2);
        sprintf(sqlquery[count], "%s", buf1);
    }

    execute_query();

    retCode = SQLDisconnect (m_hdbc);
    retCode = SQLFreeConnect (m_hdbc);
    retCode = SQLFreeEnv (m_henv);
}

void
execute_query()
{
    int count = 0;
    for (count=0; count < NUM_STMTS - 5; count++) {
        retCode = SQLAllocHandle (SQL_HANDLE_STMT, m_hdbc,
                                  (SQLHANDLE) &hstmt[count]);
        if (retCode != SQL_SUCCESS) {
            printSQLError(3, hstmt[count]);
        }
        retCode = SQLPrepare(hstmt[count], (SQLCHAR *) sqlquery[count],
SQL_NTS);
        if (retCode != SQL_SUCCESS) {
            printSQLError(3, hstmt[count]);
        }
        retCode=SQLExecute(hstmt[count]);
        if (retCode != SQL_SUCCESS) {
            printSQLError(3, hstmt[count]);
        }
        /*
         * 1. Driver keeps the statement handle back to cache if statement
         *    caching is enabled
         * 2. Driver frees the statement handle if statement caching is
disabled
         */
        retCode = SQLFreeStmt (hstmt[count], SQL_DROP);
        if (retCode != SQL_SUCCESS) {
            printSQLError(3,hstmt[count]);
        }
    }
}

void printSQLError (long handletype, SQLHANDLE handle)
{
    SQLSMALLINT errRecNo = 1;
    SQLSMALLINT arg_handleType;
    SQLCHAR errSQLStateBuf[256];
    SQLINTEGER errCode = 0;
    SQLCHAR errMsgBuf[256];
    SQLSMALLINT errMsgBufLen = 256;
    SQLSMALLINT errInfoSize;
    long retcode;

    (void) memset (errSQLStateBuf, 0x00, sizeof (errSQLStateBuf));
    (void) memset (errMsgBuf, 0x00, sizeof (errMsgBuf));

    if (handletype == 1) {
        arg_handleType = SQL_HANDLE_ENV;
    } else if (handletype == 2) {
        arg_handleType = SQL_HANDLE_DBC;
    } else {
        arg_handleType = SQL_HANDLE_STMT;
    }
    retcode = SQLGetDiagRec (arg_handleType, handle, errRecNo,
                             errSQLStateBuf, &errCode, errMsgBuf,
errMsgBufLen,
                             &errInfoSize);

    if (SQL_NO_DATA_FOUND != retcode)
        (void) printf ("errCode:%d errSQLStateBuf:%s\n errMsg:%s\n", errCode,
                        errSQLStateBuf, errMsgBuf);
    errRecNo++;
}


Compile this with:

$ gcc -I/usr/include/odbc -m64 -o stmt_cache stmt_cache.c -lodbc

There are two test runs.

The first one is with the following line in your ~/.odbc.ini file:

StatementCache=F

Running this gives:

$ ./do_run.sh
+ ./stmt_cache
+ TNS_ADMIN=/export/home/richb/userland/bugs/23210080/tianfang
+ TWO_TASK=plus1
+ LD_LIBRARY_PATH_32=/usr/oracle/instantclient/12.1/lib
+ LD_LIBRARY_PATH_64=/usr/oracle/instantclient/12.1/lib/64

Total time taken is = 390.039130


Now change that line in the ~/.odbc.ini file to:

StatementCache=T


This test run gives:

$ ./do_run.sh
+ ./stmt_cache
+ TNS_ADMIN=/export/home/richb/userland/bugs/23210080/tianfang
+ TWO_TASK=plus1
+ LD_LIBRARY_PATH_32=/usr/oracle/instantclient/12.1/lib
+ LD_LIBRARY_PATH_64=/usr/oracle/instantclient/12.1/lib/64

Total time taken is = 383.461800

----