ODBC Smart trigger

Smart Triggers (also known as Pushdata) in ODBC are a set of classes/interfaces that provide an ease of use capability to the Push data feature.

A smart trigger is a set of commands issued to the database that sets up a push notification when certain changes happen to data in a table. These changes are detected by a SQL query that is run after INSERT, UPDATE, or DELETE commands are executed. It is available across all CSDK/ODBC supported platforms.

It uses ODBC’s standard APIs SQLSetStmtAttr()/SQLSetStmtAttrA()/SQLSetStmtAttrW() and SQLGetStmtAttr()/SQLGetStmtAttrA()/SQLGetStmtAttrW() with following Informix extensions, defined in infxcli.h file.

 SQL_INFX_ATTR_OPEN_SMART_TRIGGER
SQL_INFX_ATTR_JOIN_SMART_TRIGGER
SQL_INFX_ATTR_GET_LO_FILE_DESC_SMART_TRIGGER
SQL_INFX_ATTR_GET_SESSION_ID_SMART_TRIGGER
SQL_INFX_ATTR_REGISTER_SMART_TRIGGER
SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_LOOP
SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP
SQL_INFX_ATTR_DELETE_SMART_TRIGGER

ODBC API

Using the following ODBC API to use Smart Trigger:
  1. Allocate Environment handle
  2. Allocate Connection Handle
  3. Connect to “sysadmin? database
  4. Allocate statement handle
  5. Call SQLSetStmtAttr(SQL_INFX_ATTR_OPEN_SMART_TRIGGER) and using structure IFMX_OPEN_SMART_TRIGGER
  6. Call SQLGetStmtAttr(SQL_INFX_ATTR_GET_LO_FILE_DESC_SMART_TRIGGER) and get the File Descriptor ID (to be used for registering the event/queries, the same File Descriptor to be used for multiple event/queries)
  7. Following steps could be in thread loop for each event/query to be registered.
    1. SQLAllocHandle(STMT)
    2. Fill/Populate the SQL_INFX_ATTR_REGISTER_SMART_TRIGGER structure
    3. Call SQLSetStmtAttr(SQL_INFX_ATTR_REGISTER_SMART_TRIGGER)
    4. Call SQLGetStmtAttr(SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_LOOP / SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP)
Once you register the event or join the already registered session, the call becomes blocking (ODBC waits for IDS server to return the message/data), once the data/response or timeout message is received from IDS server, ODBC invokes the registered callback function with output buffer.
Note: It is read only buffer and application should not tamper the buffer.

Once the control comes back to application (callback function), application may decide to come out of the loop by setting IFMX_JOIN_SMART_TRIGGER->ControlBackToApplication OR SQL_INFX_ATTR_REGISTER_SMART_TRIGGER->isDeregister to TRUE. If you decide to continue, then there is no action needed by you in the callback function (other than consuming the received output).

Register the smart trigger events

File Descriptor is required to “register the events". After successful call to SQLSetStmtAttr(SQL_INFX_ATTR_OPEN_SMART_TRIGGER), application should call following SQLGetStmtAttr() API to get the “File Descriptor?. The same “File Descriptor? should be used to “Register the smart trigger/pushdata events?.
SQLGetStmtAttr(hstmt, SQL_INFX_ATTR_GET_LO_FILE_DESC_SMART_TRIGGER, (int *)&FileDesc, SQL_NTS, NULL);
Application should use “File Descriptor? received from above SQLGetStmtAttr() call and other inputs like table, database, user, query etc to populate/fill the structure (mentioned above) IFMX_REGISTER_SMART_TRIGGER. Application should make call to SQLSetStmtAttr() as follows. Application can register as many as events/queries they want in each thread (application example below). It is advised to use separate statement handle for each registration.
    SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &tmpHstmt );
    SQLSetStmtAttr(tmpHstmt, SQL_INFX_ATTR_REGISTER_SMART_TRIGGER, (void *)&SmartTriggerInstance, SQL_NTS);
If the session was opened with detachable option then session ID will be created. Application can save the same session ID and could use the same later to attach/join to the session. Below API should be used to attach/join the session using structure IFMX_JOIN_SMART_TRIGGER. All the registered events/queries with the attached/joined session will be in effect.
SQLSetStmtAttr(hstmt, SQL_INFX_ATTR_JOIN_SMART_TRIGGER, (void *)&gJoinSmartTrigger, SQL_NTS);

Delete the registered session

If the session was registered as detachable session, there should be “session ID?. The same session ID should be used to delete the session by making below call. 
SQLSetStmtAttr(hstmt, SQL_INFX_ATTR_DELETE_SMART_TRIGGER, (int *)&sesID, SQL_NTS);

API Structure

  • IFMX_OPEN_SMART_TRIGGER structure: This is input structure to be used in SQLSetStmtAttr() call while establishing the smart trigger/pushdata session. The allocation & deallocation of this structure and it’s members is application’s responsibility. Each member of the structure is explained below:

    BOOL *isDetachable -> If the session is expected to be reused at later time, even after finishing the application, one should set this flag to TRUE. Otherwise set to FALSE.

    int *timeOut-> Time in seconds, as per registered event/query, if no event happens, IDS sever will send “timeout? message to ODBC based on the value set for this member. Valid range is 0 to 12000 seconds. If it’s beyond these value, internally it will be set to 300 seconds.

    short *maxRecsPerRead->This is number of records, returned (in callback function output buffer). Default is 1. Valid allowed range is 1 to 200. The max buffer size is 8KB, if this number is set high, it will return data only max of 8KB at a time, which may not match the number of records.

    int *maxPendingOperations->Maximum pending operation. Default is 0. Allowed range is 0 to 200.

    SQLWCHAR reserved[16]->Reserved for future usage.

  • IFMX_JOIN_SMART_TRIGGER structure: This structure is used to attach/join already opened session. Each member of the structure is explained below:

    void(*callback) (char const *jsonOutBuf)-> This is user defined callback function. This will be called when response from IDS server is received on registered event/query(events/queries) for a session to be attached/joined. The data returned is read only for the user.

    int *joinSessionID-> This is to be assigned from the value received from the SQLGetStmtAttr() call. This session ID will be used to connect to prior registered session.

    BOOL *ControlBackToApplication-> Once control comes back to application (as part of callback call), if user decides to come out of the blocking call (event registration is blocking call), user can set this flag to TRUE.

    SQLWCHAR reserved[16] -> Reserved for future usage.

  • IFMX_REGISTER_SMART_TRIGGER structure: This structure is used to register the event/query on already opened smart trigger/pushdata session. There could be as many as trigger/event user wants to register. Each trigger/event should be invoked with separate thread. Each member of the structure is explained below.

    void(*callback) (char const *jsonOutBuf)-> This is user defined callback function. This will be called when response from IDS server is received on registered event/query. The data returned is read only for the user.

    int *loFileDescriptor -> This is input, which is received from SQLGetStmtAttr(SQL_INFX_ATTR_GET_LO_FILE_DESC_SMART_TRIGGER) call. For each event/query registration for a given session, this value will remain same.

    SQLWCHAR *tableName -> This is table name to be registered. The input to be provided in SQLWCHAR type. Max length as per IDS supported table length.

    SQLWCHAR *ownerName -> This is user/owner of table to be registered. The input to be provided in SQLWCHAR type. Max length as per IDS supported user/owner length.

    SQLWCHAR *dbName -> This is database name where the table belongs. The input to be provided in SQLWCHAR type. Max length as per IDS supported database length.

    SQLWCHAR *sqlQuery -> This is the SELECT query which is on registered table. The input to be provided in SQLWCHAR type. Max length of query 4KB.

    SQLWCHAR *label -> If NULL, internally, ODBC will create the label using table name, owner name database name & internal counter i.e. "%s_%s_%s_%d". The input to be provided in SQLWCHAR type.

    BOOL *isDeregister -> Smart Trigger/Pushdata is blocking call, the control is back to application when ODBC calls user specified callback function. If user wants to come out of the blocking call, they can set this flag to TRUE.

    BOOL *ControlBackToApplication-> Once control comes back to application (as part of callback call), if user decides to come out of the blocking call without non-registering the event (event registration is blocking call), user can set this flag to TRUE.

    SQLWCHAR reserved[16] -> Reserved for future usage.

Compiling sample application on Linux

Use below compilation/linking steps, assuming C file name is SmartTrigger.c and application links directly to Informix driver (no Driver Manager).
gcc -g -c -fsigned-char -DNO_WIN32 -O -I$ONEDB_HOME/incl/cli -I$ONEDB_HOME/incl/esql -I$ONEDB_HOME/incl/dmi SmartTrigger.c
gcc -g -o SmartTrigger SmartTrigger.o -L$ONEDB_HOME/lib/cli -L$ONEDB_HOME/lib/esql -lthcli -lifdmr -L$ONEDB_HOME/lib/esql -lifgls -lifglx -lm -lnsl

Compiling sample application on Windows

Use below steps to compile the Smart Trigger application on Windows.
cl /Zi /DEBUG /MD /D_CRT_SECURE_NO_DEPRECATE /D_CRT_NON_CONFORMING_SWPRINTFS  /D
_CRT_NONSTDC_NO_DEPRECATE /I%ONEDB_HOME%\incl\cli %ONEDB_HOME%\lib\iclit09b.lib odbc32.lib odbccp32.lib SmartTrigger.c
The below example, supports two(2) trigger/event registration, hence two threads (NUM_OF_INSTANCE) has been used. There are following 3 functions which needs to be changed to provide appropriate input for your environment.
  1. SetConnectionString() => This is for database connection, you can provide “DSN=<value>? as well, depending on your choice. In this function, you need to provide your own connection string.
  2. AssignOpenParams() => In this function, you may need to change timeout, number of records(1 is recommended) etc parameters for smart trigger/pushdata session opening.
  3. AssignRegisterParams() => In this function, you need to change, callback function, table, owner, database and query values which suits your environment. This function uses two such events/inputs to be registered.

ODBC sample application



#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        hdbc;
SQLHENV        henv;
SQLHSTMT       hstmt;
SQLINTEGER     sesID = 0;
SQLWCHAR       connStrInW[NAMELEN];
SQLCHAR       connStrIn[NAMELEN];
BOOL           delete = 0;
int            gFileDesc = 0;
int            fileDesc[NUM_OF_INSTANCE];
int            timeOut;
short          maxRec;
int            maxPend;
int            detachable;
BOOL           dregister[NUM_OF_INSTANCE];
BOOL           ControlBack[NUM_OF_INSTANCE];

IFMX_REGISTER_SMART_TRIGGER  gSmartTriggerRegister[NUM_OF_INSTANCE];
IFMX_OPEN_SMART_TRIGGER      gopenSmartTrigger;
IFMX_JOIN_SMART_TRIGGER      gJoinSmartTrigger;


SQLINTEGER checkError (SQLRETURN       rc,
                       SQLSMALLINT     handleType,
                       SQLHANDLE       handle,
                       SQLCHAR*        errmsg)
{
    SQLRETURN      retcode = SQL_SUCCESS;
    SQLSMALLINT    errNum = 1;
    SQLWCHAR             sqlStateW[6];
    SQLCHAR                 *sqlState;
    SQLINTEGER     nativeError;
    SQLWCHAR             errMsgW[ERRMSG_LEN];
    SQLCHAR                 *errMsg;
    SQLSMALLINT    textLengthPtr;


    if ((rc != SQL_SUCCESS) && (rc != SQL_SUCCESS_WITH_INFO))
    {
        while (retcode != SQL_NO_DATA)
        {
            retcode = SQLGetDiagRecW (handleType, handle, errNum, sqlStateW, &nativeError, errMsgW, 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))
            {
                sqlState = (SQLCHAR *) malloc (wcslen(sqlStateW) + sizeof(char));
                wcstombs( (char *) sqlState, sqlStateW, wcslen(sqlStateW)
                          + sizeof(char));

                errMsg = (SQLCHAR *) malloc (wcslen(errMsgW) + sizeof(char));
                wcstombs( (char *) errMsg, errMsgW, wcslen(errMsgW)
                          + sizeof(char));

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

void TriggerCallback1(char const *outBuf)
{
  dregister[0] = FALSE; //TRUE;
  ControlBack[0] = FALSE;
  printf("\nCallback #1");
  if(outBuf != NULL)
    printf("\nData received : %s\n", outBuf);
  else
    printf("\nReturned NULL data!!");

  gSmartTriggerRegister[0].isDeregister = &dregister[0];
  gSmartTriggerRegister[0].ControlBackToApplication = &ControlBack[0];
  gJoinSmartTrigger.ControlBackToApplication = &ControlBack[0];
  return;
}

void TriggerCallback2(char const *outBuf)
{
  dregister[1] = FALSE;
  ControlBack[1] = FALSE; //TRUE;
  printf("\nCallback #2");
  if(outBuf != NULL)
    printf("\nData received : %s\n", outBuf);
  else
    printf("\nReturned NULL data!!");

  gSmartTriggerRegister[1].isDeregister = &dregister[1];
  gSmartTriggerRegister[1].ControlBackToApplication = &ControlBack[1];
  gJoinSmartTrigger.ControlBackToApplication = &ControlBack[1];
  return;
}

DWORD ThreadRegisterPushDataQuery(void *lpParam)
{
    SQLRETURN rc = 0;
    SQLHSTMT  tmpHstmt;
    SQLINTEGER      dummy = 0;

    IFMX_REGISTER_SMART_TRIGGER  temp;
    IFMX_REGISTER_SMART_TRIGGER  SmartTriggerInstance;

    SmartTriggerInstance = *((IFMX_REGISTER_SMART_TRIGGER *)lpParam);
    
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &tmpHstmt );
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error(Thread) in Step 1 -- Statement Handle Allocation failed\nExiting!!"))
        exit(-1);

    rc = SQLSetStmtAttrW(tmpHstmt, SQL_INFX_ATTR_REGISTER_SMART_TRIGGER,(IFMX_REGISTER_SMART_TRIGGER *)&SmartTriggerInstance, SQL_IS_POINTER);
    if (checkError(rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error(Thread) in Step 2 -- SQLSetStmtAttr failed\nExiting!!"))
        exit(-1);
    rc = SQLGetStmtAttrW(tmpHstmt, SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_LOOP,(void *)&dummy, SQL_NTS, NULL);
    //rc = SQLGetStmtAttrW(tmpHstmt, SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP,(void *)&dummy, SQL_NTS, NULL);
    if (checkError(rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error(Thread) in Step 2 -- SQLGetStmtAttr failed\nExiting!!"))
        exit(-1);

    SQLFreeHandle(SQL_HANDLE_STMT, tmpHstmt);
    printf("\nFinished thread execution\n");
}

void SetConnectionString()
{
#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=onedb;PWD=xxx");
   wsprintf((SQLWCHAR *) connStrInW, "DSN=SmartTrigger");
#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=onedb;PWD=xxx");
   swprintf((SQLWCHAR *) connStrInW, L"DSN=SmartTrigger");
#endif
   return;
}

void AssignOpenParams()
{
    timeOut = 5; //In seconds
    maxRec = 1; //Maximum number of records to get, (8192 Bytes limit)
    maxPend = 0;
    detachable = FALSE; //TRUE, If want to retain session for later usage
    delete = FALSE; //TRUE, if want to delete the detachable session

    gopenSmartTrigger.timeOut = &timeOut;
    gopenSmartTrigger.isDetachable = &detachable;
    gopenSmartTrigger.maxRecsPerRead = &maxRec;
    gopenSmartTrigger.maxPendingOperations = &maxPend;

    return;
}

void AssignRegisterParams(IFMX_REGISTER_SMART_TRIGGER *SmartTriggerInstance, int FileDesc, int i)
{
    fileDesc[i] = FileDesc;
    dregister[i] = FALSE; // Just initialize, should be changed in callback
    ControlBack[i] = FALSE; // Just initialize, should be changed in callback
    SmartTriggerInstance->loFileDescriptor = &fileDesc[i];
    SmartTriggerInstance->tableName = (SQLWCHAR *)malloc(50 * sizeof(SQLWCHAR));
    SmartTriggerInstance->ownerName = (SQLWCHAR *)malloc(50 * sizeof(SQLWCHAR));
    SmartTriggerInstance->dbName = (SQLWCHAR *)malloc(50 * sizeof(SQLWCHAR));
    SmartTriggerInstance->sqlQuery = (SQLWCHAR *)malloc(500 * sizeof(SQLWCHAR));
    //SmartTriggerInstance->label = (SQLWCHAR *)malloc(50 * sizeof(SQLWCHAR));
    SmartTriggerInstance->label = NULL;
    SmartTriggerInstance->isDeregister = &dregister[i];
    SmartTriggerInstance->ControlBackToApplication = &ControlBack[i];

    //wcscpy((SQLWCHAR *)SmartTriggerInstance->ownerName, L"shesh");
    //wcscpy((SQLWCHAR *)SmartTriggerInstance->dbName, L"sheshdb");
    wsprintf((SQLWCHAR *)SmartTriggerInstance->ownerName, "shesh");
    wsprintf((SQLWCHAR *)SmartTriggerInstance->dbName, "sheshdb");

    if (i==0)
    {
       SmartTriggerInstance->callback = TriggerCallback1;
       //wcscpy((SQLWCHAR *)SmartTriggerInstance->label, L"label1");
       //wcscpy((SQLWCHAR *)SmartTriggerInstance->tableName, L"tab1");
       //wcscpy((SQLWCHAR *)SmartTriggerInstance->sqlQuery, L"select * from tab1;");
       //wsprintf((SQLWCHAR *)SmartTriggerInstance->label, "label1");
       wsprintf((SQLWCHAR *)SmartTriggerInstance->tableName, "tab1");
       wsprintf((SQLWCHAR *)SmartTriggerInstance->sqlQuery, "select * from tab1;");
    }
    else
    {
       SmartTriggerInstance->callback = TriggerCallback2;
       //wcscpy((SQLWCHAR *)SmartTriggerInstance->label, L"label2");
       //wcscpy((SQLWCHAR *)SmartTriggerInstance->tableName, L"tab2");
       //wcscpy((SQLWCHAR *)SmartTriggerInstance->sqlQuery, L"select * from tab2;");
       //wsprintf((SQLWCHAR *)SmartTriggerInstance->label, "label2");
       wsprintf((SQLWCHAR *)SmartTriggerInstance->tableName, "tab2");
       wsprintf((SQLWCHAR *)SmartTriggerInstance->sqlQuery, "select * from tab2;");
    }
    return;
}

void FreeMemory(IFMX_REGISTER_SMART_TRIGGER *SmartTriggerInstance)
{
    free(SmartTriggerInstance->tableName);
    free(SmartTriggerInstance->ownerName);
    free(SmartTriggerInstance->dbName);
    free(SmartTriggerInstance->sqlQuery);
    if(SmartTriggerInstance->label != NULL)
       free(SmartTriggerInstance->label);
    return;
}

int main (long         argc,
          char*        argv[])
{
    /* Miscellaneous variables */
    SQLRETURN       rc = 0;
    SQLINTEGER      i = 0;
    SQLINTEGER      getSesID = 0;
    SQLWCHAR         connStrOutW[NAMELEN];
    SQLSMALLINT     connStrOutLen;
    SQLINTEGER      stackSize = 40 * 1024;
    HANDLE          hThread_[NUM_OF_INSTANCE];
    DWORD           threadID_[NUM_OF_INSTANCE];
#ifdef NO_WIN32
    pthread_t       cpid[NUM_OF_INSTANCE];
#endif
    DWORD           dwThreadID=10;

    printf("\nApplication : sizeof(SQLWCHAR) = %d", sizeof(SQLWCHAR));
    /* 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);

    /* Allocate the connection handle */
    rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
    if (checkError (rc, SQL_HANDLE_ENV, henv, (SQLCHAR *) "Error(main) in Step 2 -- Connection Handle Allocation failed\nExiting!!"))
        exit (-1);

    /* Establish the database connection */
    SetConnectionString();
    rc = SQLDriverConnectW(hdbc, NULL, connStrInW, SQL_NTS, connStrOutW, NAMELEN, &connStrOutLen, SQL_DRIVER_NOPROMPT);
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error(main) in Step 3 -- SQLDriverConnect failed\nExiting!!"))
        exit (-1);
    printf("\nApplication : Database connection successful");

    /* Allocate the statement handle */
    rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt );
    if (checkError (rc, SQL_HANDLE_DBC, hdbc, (SQLCHAR *) "Error(main) in Step 4 -- Statement Handle Allocation failed\nExiting!!"))
        exit (-1);

   sesID = 0;
   gJoinSmartTrigger.callback = TriggerCallback1;
   gJoinSmartTrigger.joinSessionID = &sesID;

    if(sesID != 0)
    {
       rc = SQLSetStmtAttrW(hstmt, SQL_INFX_ATTR_JOIN_SMART_TRIGGER,(void *)&gJoinSmartTrigger, SQL_NTS);
       if (checkError(rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error(main) in Step 5 --  SQLSetStmtAttr(SQL_INFX_ATTR_JOIN_SMART_TRIGGER) failed\nExiting!!"))
          exit(-1);

       printf("\nJoin session was executed successfully... Exiting.\n");
       goto Exit; // Exit gracefully
    }

    AssignOpenParams();

    rc = SQLSetStmtAttrW(hstmt, SQL_INFX_ATTR_OPEN_SMART_TRIGGER,&gopenSmartTrigger, SQL_NTS);
    if (checkError(rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error(main) in Step 5 --  SQLSetStmtAttr(SQL_INFX_ATTR_OPEN_SMART_TRIGGER) failed\nExiting!!"))
       exit(-1);

    rc = SQLGetStmtAttrW(hstmt, SQL_INFX_ATTR_GET_LO_FILE_DESC_SMART_TRIGGER, (int *)&gFileDesc, SQL_NTS, NULL);
    if (checkError(rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error(main) in Step 6 --  SQLGetStmtAttr(SQL_INFX_ATTR_GET_LO_FILE_DESC_SMART_TRIGGER) failed\nExiting!!"))
        exit(-1);

    for (i = 0; i < NUM_OF_INSTANCE; i++)
    {
           printf("\nStart Thread %d", i + 1);
           AssignRegisterParams(&gSmartTriggerRegister[i], gFileDesc, i);
#ifndef NO_WIN32
           hThread_[i] = CreateThread(
                        0, // Security Attributes (no security restrictions)
                        stackSize, // Stack Size
                        ThreadRegisterPushDataQuery, // Start address
                        (void *)&gSmartTriggerRegister[i], //&inputValues[i],
                        0, // Creation Flags (create running)
                        &(threadID_[i]) // Thread Id
                );
#else
           rc = pthread_create(&cpid[i],NULL,(void *)ThreadRegisterPushDataQuery,&gSmartTriggerRegister[i]);
#endif
        }

#ifndef NO_WIN32
        if(NUM_OF_INSTANCE > 0)
           WaitForMultipleObjects(NUM_OF_INSTANCE, hThread_, TRUE, INFINITE);
#else
        for (i = 0; i < NUM_OF_INSTANCE; ++i)
           pthread_join(cpid[i], NULL);
#endif

    rc = SQLGetStmtAttrW(hstmt, SQL_INFX_ATTR_GET_SESSION_ID_SMART_TRIGGER, (int *)&getSesID, SQL_NTS, NULL);
    if (checkError(rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error(main) in Step 7 --  SQLGetStmtAttr(SQL_INFX_ATTR_GET_SESSION_ID_SMART_TRIGGER) failed\nExiting!!"))
        exit(-1);
    printf("\nSession ID received = %d\n", getSesID);

    if( getSesID > 0 && delete == 1 )
    {
       rc = SQLSetStmtAttrW(hstmt, SQL_INFX_ATTR_DELETE_SMART_TRIGGER, (int *)&getSesID, SQL_NTS);
       if (checkError(rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error(main) in Step 8 --  SQLSetStmtAttr(SQL_INFX_ATTR_DELETE_SMART_TRIGGER) failed\nExiting!!"))
        exit(-1);
       printf("\nSession ID deleted = %d\n",getSesID);
    }

    for (i = 0; i < NUM_OF_INSTANCE; ++i)
    {
#ifndef NO_WIN32
       printf("Close Thread Handle : %d\n", i);
       CloseHandle(hThread_[i]);
#endif
       FreeMemory(&gSmartTriggerRegister[i]);
    }

Exit:

    /* CLEANUP: Close the statement handle
    **          Free the statement handle
    **          Disconnect from the datasource
    **          Free the connection and environment handles
    **          Exit
    */

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

    /* Free the statement handle */
    SQLFreeHandle (SQL_HANDLE_STMT, hstmt);

    /* Disconnect from the data source */
    SQLDisconnect (hdbc);

    /* Free the environment handle and the database connection handle */
    SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
    SQLFreeHandle (SQL_HANDLE_ENV, henv);

    return (rc);
}

Best Practices/Trouble shooting

You can link Smart Trigger ODBC application with ODBC Driver Manager(DM). Smart Trigger feature has been tested with Windows Driver Manager and unixODBC Driver Manager. Due to synchronization behaviour of certain Driver Manager, it may not allow more than one SQLSetStmtAttr(with Smart trigger) / SQLGetStmtAttr to be called until previous call is completed, Smart Trigger is blocking call (due to internal call to ifx_lo_read()). In multi-threaded Smart Trigger application, this could cause unexpected/hang behaviour, to avoid the same one of the two below available options could be used:

  • For unixODBC DM configuration in .odbcinst.ini file, use “Threading = 0" (disables synchronization/mutex of DM)
    Note: Synchronization/mutex continues to work from Informix ODBC driver
  • You can use SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP interface in application and manage the WHILE loop in application (example given below). If you use this option, you may not need to set “Threading = 0? in DM.

SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_LOOP and SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP interfaces

These two interfaces are provided to choose the appropriate one depending on the usage of DM.

In SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_LOOP interface, ODBC internally uses WHILE loop to wait for data for Smart Trigger from server until user deregister or wants control back to application (example below), this is suitable when application uses unixODBC DM and wants to register many events/queries from application by setting “Threading = 0? in DM’s .odbcinst.ini file.

In SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP interface, the responsibility of looping to get next Smart Trigger from server lies with application, this way you don not really have to set “Threading = 0? in unixODBC DM.

Example
Thread Start
SQLSetStmtAttr(SQL_INFX_ATTR_REGISTER_SMART_TRIGGER)
SQLGetStmtAttr(SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_LOOP)  While loop inside the ODBC code.
OR
While(TRUE)
{
    SQLGetStmtAttr(SQL_INFX_ATTR_GET_DATA_SMART_TRIGGER_NO_LOOP)
    //Break with some business condition
}
Thread End
Note: On Unix/Linux user libthcli.so and on Windows use iclit09b.lib, you can directly link Smart Trigger application with Informix ODBC driver

If you have multiple events/queries to be registered from single database connection, then application should be linked with multi-threaded ODBC library “libthcli.so". Multiple events/queries could be registered only with multi-threaded application, where each event/query could be registered from each thread.

In addition to above extensions, there are following structures provided in infxcli.h file. The allocation & deallocation of memory for these structures and its members is responsibility of application.

typedef struct tagIfmxJoinTrigger
{
        void(*callback)
                (char const *jsonOutBuf);
        int      *joinSessionID;
        BOOL     *ControlBackToApplication;
        SQLWCHAR     reserved[16];
} IFMX_JOIN_SMART_TRIGGER;

typedef struct tagIfmxOpenTrigger
{
        BOOL     *isDetachable;
        int      *timeOut;
        short    *maxRecsPerRead;
        int      *maxPendingOperations;
        SQLWCHAR     reserved[16];
} IFMX_OPEN_SMART_TRIGGER;

typedef struct tagIfmxRegisterTrigger
{
        void(*callback)
                (char const *jsonOutBuf);
        int      *loFileDescriptor;
        SQLWCHAR     *tableName;
        SQLWCHAR     *ownerName;
        SQLWCHAR     *dbName;
        SQLWCHAR     *sqlQuery;
        SQLWCHAR     *label;
        BOOL     *isDeregister;
        BOOL     *ControlBackToApplication;
        SQLWCHAR     reserved[16];
} IFMX_REGISTER_SMART_TRIGGER;
Structure IFMX_OPEN_SMART_TRIGGER should be allocated/filled with respective values and must call following SQLSetStmtAttr() API. If “isDetachable? member is TRUE, it will retain the session even after closing the connection / finishing the application execution.
SQLSetStmtAttr(hstmt, SQL_INFX_ATTR_OPEN_SMART_TRIGGER, &gopenSmartTrigger, SQL_NTS);
If session is opened with “isDetachable? member TRUE, then application can call following SQLGetStmtAttr() to get the session ID, which could be used later to JOIN (more info below) the session. If “isDetachable? member was FALSE while opening the session, if so calling SQLGetStmtAttr() may return 0 (zero) or negative number, which are invalid session ID.
SQLGetStmtAttr(hstmt, SQL_INFX_ATTR_GET_SESSION_ID_SMART_TRIGGER, (int *)&getSesID, SQL_NTS, NULL);