Connection pooling

• On Unix/Linux, user could use following attributes in odbc.ini file:
  • Set 0 to disable, 1 to enable. By default its disabled(0).

    InformixPooling=0

  • Timeout for the connection in seconds range 5 to 60000. Default 60. Beyond range will be reset to default value without any error/warning.

    InformixCPTimeout=10

  • Set 0 for strict match and 1 for relaxed match. By default, it is strict match(0). In strict match, more number of parameters are compared to find out the match from the available connection from the pool. It is recommended to use “strict match?.

    InformixCPMatch=0

• On Unix/Linux or Windows, user can also set/get above values programatically using SQLSetEnvAttr/SQLGetEnvAttr APIs.

SQLGetEnvAttr(SQL_INFX_ATTR_CP_TOTAL_CONNECTIONS) => This will return total number of connections. 
                                                     At any point of time, this will be equal to "Active + Idle" number of connections.
SQLGetEnvAttr(SQL_INFX_ATTR_CP_TOTAL_ACTIVE) => This will return connection count which are currently in use.
SQLGetEnvAttr(SQL_INFX_ATTR_CP_TOTAL_IDLE) => This will return connection count which are currently idle (not in use)and
                                              ready to be assigned for matching incoming connection requests.The idle number of connections
                                              are actually connected to the database server.

Total connections = -1, active = -1, idle = -1
*** Connection pooling enabled ***
*** Connection pooling set to STRICT mode ***
*** Connection pooling timeout set to 10 seconds
Connected, label = connStrIn
Total connections = 1, active = 1, idle = 0
Disconnected, label = connStrIn
Total connections = 1, active = 0, idle = 1
Connected, label = connStrIn
Total connections = 1, active = 1, idle = 0
Disconnected, label = connStrIn
Total connections = 1, active = 0, idle = 1
Connected, label = connStrIn1
Total connections = 2, active = 1, idle = 1
Connected, label = connStrIn
Total connections = 2, active = 2, idle = 0
Disconnected, label = connStrIn
Total connections = 2, active = 1, idle = 1
Connected, label = connStrIn1
Total connections = 3, active = 2, idle = 1
Disconnected, label = connStrIn1
Total connections = 3, active = 1, idle = 2
Press enter to Exit, you may run 'userid informix onstat -g ses' to 
see number of connections still opened due to connection pooling effect :

Statement handle freed successfully
Disconnected, label = connStrIn1
Total connections = 3, active = 0, idle = 3
Environment handle freed successfully
Press enter to Exit, you may run 'userid informix onstat -g ses' to see
there should be no connection, all connections must have closed by now :
sh-3.2$


/***************************************************************************
*       Licensed Materials - Property of HCL Technologies
*
*       "Restricted Materials of HCL"
*
*       HCL OneDB ODBC Application
*
*       Copyright HCL 2019 All rights reserved.
*
*  Title:          ConnectionPooling.c
*
*  Description:    Connection Pooling Sample ODBC Program
*
*  Author  : User 1
*
* Compile/link options on Linux/Unix :
* gcc -g -c -fsigned-char -DNO_WIN32 -O -I$ONEDB_HOME/incl/cli ConnectionPooling.c
* gcc -g -o ConnectionPooling ConnectionPooling.o -L$ONEDB_HOME/lib/cli -L$ONEDB_HOME/lib/esql -lthcli -lifdmr -lifgls -lifglx -lm -lnsl
***************************************************************************
*/

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

#ifndef NO_WIN32
#include <io.h>
#include <windows.h>
#include <conio.h>
#endif /*NO_WIN32*/

#define __REENTRANT
#include <signal.h>
#ifdef NO_WIN32
#include <sys/wait.h>
#include <pthread.h>
#endif
#include <time.h>
#include <infxcli.h>

#define ERRMSG_LEN         200
#define NAMELEN            300
#define NUM_OF_INSTANCE    2

SQLHDBC        hdbc0;
SQLHDBC        hdbc1;
SQLHDBC        hdbc2;
SQLHDBC        hdbc3;
SQLHDBC        hdbc4;
SQLHENV        henv;
SQLHSTMT       hstmt;
SQLCHAR        connStrIn[NAMELEN];
SQLCHAR        connStrIn1[NAMELEN];
short          totalConn=0, totalActive=0, totalIdle=0;
SQLCHAR        connStrOut[NAMELEN];
SQLSMALLINT    connStrOutLen;

SQLINTEGER checkError (SQLRETURN       rc,
                       SQLSMALLINT     handleType,
                       SQLHANDLE       handle,
                       SQLCHAR*        errmsg)
{
    SQLRETURN       retcode = SQL_SUCCESS;

    SQLSMALLINT     errNum = 1;
    SQLCHAR         sqlState[6];
    SQLINTEGER      nativeError;
    SQLCHAR	    errMsg[ERRMSG_LEN];
    SQLSMALLINT     textLengthPtr;


    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
    {
        while (retcode != SQL_NO_DATA)
        {
            retcode = SQLGetDiagRec (handleType, handle, errNum, sqlState, &nativeError, errMsg, ERRMSG_LEN, &textLengthPtr);

            if (retcode == SQL_INVALID_HANDLE)
            {
                fprintf (stderr, "CheckError function was called with an invalid handle!!\n");
                return 1;
            }

            if ((retcode == SQL_SUCCESS) || (retcode == SQL_SUCCESS_WITH_INFO))
                fprintf (stderr, "ERROR: %d:  %s : %s \n", nativeError, sqlState, errMsg);

            errNum++;
        }

        fprintf (stderr, "%s\n", errmsg);
        return 1;   /* all errors on this handle have been reported */
    }
    else
        return 0;	/* no errors to report */
}

int GetConnectionPoolingAttributes()
{
    SQLRETURN rc = SQL_SUCCESS;

    rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TOTAL_CONNECTIONS , (void *) &totalConn, SQL_NTS, NULL);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling Total Connections failed \nExiting!!");
        exit (-1);
    }
    rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TOTAL_ACTIVE , (void *) &totalActive, SQL_NTS, NULL);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling Total Active failed \nExiting!!");
        exit (-1);
    }
    rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TOTAL_IDLE , (void *) &totalIdle, SQL_NTS, NULL);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling Total Idle failed \nExiting!!");
        exit (-1);
    }

    printf("\nTotal connections = %d, active = %d, idle = %d",totalConn,totalActive, totalIdle );

return 0;
}

void SetConnectionString()
{
    memset(connStrIn, 0, sizeof(connStrIn));
    memset(connStrIn1, 0, sizeof(connStrIn1));
#ifdef NO_WIN32
   //sprintf((char *) connStrIn, "DRIVER={IBM INFORMIX ODBC DRIVER};
    HOST=x.x.x.x;SERVER=ol_informix1210_2;SERVICE=8573;PROTOCOL=onsoctcp;DATABASE=sysadmin;UID=informix;PWD=xxxxx");
   sprintf((char *) connStrIn, "DSN=SmartTrigger");
   sprintf((char *) connStrIn1, "DSN=odbc_demo");
#else
   //sprintf((char *) connStrIn, "DRIVER={IBM INFORMIX ODBC DRIVER};HOST=x.x.x.x;SERVER=ol_informix1210_1;
    SERVICE=20195;PROTOCOL=onsoctcp;DATABASE=sysadmin;UID=informix;PWD=xxxxx");
   sprintf((char *) connStrIn, "DSN=SmartTrigger");
   sprintf((char *)connStrIn1, "DSN=odbc_demo");
#endif
   return;
}

int Connect(SQLHDBC *hdbc, SQLCHAR connStrIn[], SQLCHAR label[])
{
    SQLRETURN rc = SQL_SUCCESS;
    SQLHDBC tmpHdbc = NULL;
    /* Allocate the connection handle */
    rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &tmpHdbc);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 2 -- Connection Handle Allocation failed\nExiting!!"))
        exit (-1);

    //printf("\nConnection handle BEFORE connection = %p", tmpHdbc);
    /* Establish the database connection */
    rc = SQLDriverConnect (tmpHdbc, NULL, connStrIn, SQL_NTS, connStrOut, NAMELEN, &connStrOutLen, SQL_DRIVER_NOPROMPT);
    if (checkError (rc, SQL_HANDLE_DBC, tmpHdbc, (SQLCHAR *) "Error(main) in Step 3 -- SQLDriverConnect failed\nExiting!!"))
        exit (-1);
    printf("\nConnected, label = %s", label);
    //printf("\nConnection handle AFTER connection = %p", tmpHdbc);

    *hdbc = tmpHdbc;

   return rc;
}

int DisconnectAndFree(SQLHDBC *hdbc, SQLCHAR label[])
{
    SQLRETURN rc = SQL_SUCCESS;
    SQLHDBC tmphdbc = *hdbc;
    /* Disconnect from the data source */
    rc = SQLDisconnect (tmphdbc);
    printf("\nDisconnected, label = %s", label);

    /* Free the environment handle and the database connection handle */
    rc = SQLFreeHandle (SQL_HANDLE_DBC, tmphdbc);
    //printf("\nDatabase handle freed successfully");

    return rc;
}

int main (long         argc,
          char*        argv[])
{
    /* Miscellaneous variables */
    SQLRETURN       rc = 0;
    BOOL            poolEnabled = 0;
    BOOL            cpMode = 0;
    SQLINTEGER      timeOut = -1;

    /* Allocate the Environment handle */
    rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Environment Handle Allocation failed\nExiting!!");
        exit (-1);
    }

    /* Set the ODBC version to 3.0 */
    rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, 0);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
        exit (-1);

    GetConnectionPoolingAttributes();

    //rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CONNECTION_POOLING , (SQLPOINTER) SQL_INFX_CP_OFF, 0);
    rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CONNECTION_POOLING , (SQLPOINTER) SQL_INFX_CP_ON, 0);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling call failed \nExiting!!");
        exit (-1);
    }

    rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CONNECTION_POOLING , (void *) &poolEnabled, SQL_NTS, NULL);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling call failed \nExiting!!");
        exit (-1);
    }
    if(SQL_INFX_CP_OFF == poolEnabled)
      printf("\n*** Connection pooling disabled ***");
    else if(SQL_INFX_CP_ON == poolEnabled)
      printf("\n*** Connection pooling enabled ***");
    else
      printf("\n*** What's going with Connection pooling!!!");

/*
    rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CP_MATCH, (SQLPOINTER)SQL_INFX_CP_RELAXED_MATCH, 0);
    //rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CP_MATCH, (SQLPOINTER)SQL_INFX_CP_STRICT_MATCH, 0);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
        exit (-1);
*/
    rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_MATCH , (void *) &cpMode, SQL_NTS, NULL);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling call failed \nExiting!!");
        exit (-1);
    }
    if(SQL_INFX_CP_RELAXED_MATCH == cpMode)
      printf("\n*** Connection pooling set to RELAX mode ***");
    else if(SQL_INFX_CP_STRICT_MATCH == cpMode)
      printf("\n*** Connection pooling set to STRICT mode ***");
/*
    rc = SQLSetEnvAttr (henv, SQL_INFX_ATTR_CP_TIMEOUT, (SQLPOINTER)5, 0);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 1 -- SQLSetEnvAttr failed\nExiting!!"))
        exit (-1);
*/
    rc = SQLGetEnvAttr (henv, SQL_INFX_ATTR_CP_TIMEOUT , (void *) &timeOut, SQL_NTS, NULL);
    if (rc != SQL_SUCCESS)
    {
        fprintf (stdout, "Connection pooling call failed \nExiting!!");
        exit (-1);
    }
    printf("\n*** Connection pooling timeout set to %d seconds", timeOut);

    SetConnectionString();
    Connect(&hdbc0, connStrIn, "connStrIn");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc0, "connStrIn");
    GetConnectionPoolingAttributes();

    SetConnectionString();
    Connect(&hdbc1, connStrIn, "connStrIn");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc1, "connStrIn");
    GetConnectionPoolingAttributes();

    SetConnectionString();
    Connect(&hdbc2, connStrIn1, "connStrIn1");
    GetConnectionPoolingAttributes();
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc2, &hstmt );
    if (checkError (rc, SQL_HANDLE_DBC, hdbc2, (SQLCHAR *) "Error(main) in Step 4 -- Statement Handle Allocation failed\nExiting!!"))
        exit (-1);


    SetConnectionString();
    Connect(&hdbc3, connStrIn, "connStrIn");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc3, "connStrIn");
    GetConnectionPoolingAttributes();

    SetConnectionString();
    Connect(&hdbc4, connStrIn1, "connStrIn1");
    GetConnectionPoolingAttributes();
    DisconnectAndFree(&hdbc4, "connStrIn1");
    GetConnectionPoolingAttributes();

    printf("\nPress enter to Exit, you may run 'userid informix onstat -g ses' to see 
    number of connections still opened due to connection pooling effect : ");
    char c = getchar();

Exit:
    /* Close the statement handle */
    SQLFreeStmt (hstmt, SQL_CLOSE);

    /* Free the statement handle */
    SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
    printf("\nStatement handle freed successfully");

    DisconnectAndFree(&hdbc2, "connStrIn1");
    GetConnectionPoolingAttributes();

    SQLFreeHandle (SQL_HANDLE_ENV, henv);
    printf("\nEnvironment handle freed successfully");

    printf("\nPress enter to Exit, you may run 'userid informix onstat -g ses' to see there
 should be no connection, all connections must have closed by now : ");
    c = getchar();

    return (rc);
}