Example: Connecting to GemFire XD with the ODBC Driver

This topic includes an example of how to connect to GemFire XD using the ODBC driver and how to execute ODBC function calls.

/*
 * example.cpp
 *
 * To build this example with unixODBC DM use
 *    g++ example.cpp -o example -lodbc
 * To build this example with iODBC DM use
 *    g++ example.cpp -o example -liodbc
 */
#include <stdio.h>
#include <sqlext.h>
#include <string.h>

//error information
SQLCHAR  sqlstate[6];
SQLCHAR message[SQL_MAX_MESSAGE_LENGTH];
SQLINTEGER  native_error;
SQLSMALLINT length;

#define HANDLE_SQLERROR(handletype, handle, retcode) \
  if(retcode == SQL_ERROR || retcode == SQL_INVALID_HANDLE) \
{ \
  SQLGetDiagRec(handletype, handle, 1, sqlstate, &native_error, message, SQL_MAX_MESSAGE_LENGTH - 1, &length);\
  printf("#\nERROR: [%6s] Message: %*s \n", sqlstate, length, message);\
  return -1;\
}\

int main() {
  SQLRETURN retcode = SQL_SUCCESS;
  SQLHENV henv = SQL_NULL_HANDLE;
  SQLHDBC hdbc = SQL_NULL_HANDLE;
  SQLHSTMT hstmt = SQL_NULL_HANDLE;

  //allocate environment, connection, statement handles
  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  HANDLE_SQLERROR(SQL_HANDLE_ENV, henv, retcode);

  //specify the ODBC version client app is using
  retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3,
      0);
  HANDLE_SQLERROR(SQL_HANDLE_ENV, henv, retcode);

  //create connection handle
  retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
  HANDLE_SQLERROR(SQL_HANDLE_DBC, hdbc, retcode);

  //connect to data source
  retcode = SQLConnect(hdbc, (SQLCHAR*) "testdsn", SQL_NTS, NULL, SQL_NTS,
      NULL, SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_DBC, hdbc, retcode);

  //create the statement handle
  retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //drop tables if exists
  retcode = SQLExecDirect(hstmt, (SQLCHAR*) "DROP TABLE IF EXISTS Employees;",
      SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //create employee table
  retcode =
    SQLExecDirect(hstmt,
        (SQLCHAR*) "CREATE TABLE Employees ("
        "empid INT PRIMARY KEY, birthdate DATE NOT NULL, "
        "firstname  VARCHAR(14) NOT NULL, lastname   VARCHAR(16) NOT NULL, "
        "hiredate   DATE  NOT NULL)", SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //insert data into employee table
  retcode =
    SQLExecDirect(hstmt,
        (SQLCHAR*) "INSERT INTO Employees VALUES (480001,'1955-09-23','Divine','Felder','1990-09-16')",
        SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  retcode =
    SQLExecDirect(hstmt,
        (SQLCHAR*) "INSERT INTO Employees VALUES (480002,'1953-09-26','Irena','Unno','1985-06-07')",
        SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  retcode =
    SQLExecDirect(hstmt,
        (SQLCHAR*) "INSERT INTO Employees VALUES (480003,'1953-04-07','Yishay','Cools','1987-05-14')",
        SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  retcode =
    SQLExecDirect(hstmt,
        (SQLCHAR*) "INSERT INTO Employees VALUES (480004,'1956-12-12','George','Denti','1986-01-18')",
        SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);


  //execute the select statement
  retcode =SQLExecDirect(hstmt, (SQLCHAR*) "SELECT empid, birthdate,firstname,lastname,hiredate FROM Employees order by empid", SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);


  //bind all the columns to fetch data
  SQLLEN empID;
  SQLCHAR bdate[100];
  SQLCHAR jdate[100];
  SQLCHAR fname[100];
  SQLCHAR lname[100];


  //binding empID
  retcode = SQLBindCol(hstmt, 1, SQL_C_LONG, &empID, 0, NULL);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //binding birth date and retrieving date as string
  retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, bdate, sizeof(bdate), NULL);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //binding first name
  retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, fname, sizeof(fname), NULL);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //binding last name
  retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, lname, sizeof(lname), NULL);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //binding hire date and retrieving date as string
  retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, jdate, sizeof(jdate), NULL);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);


  printf("\n-------------------------------------------------------------------\n");
  printf("\nempid\tbirthdate\tfirstname\tlastname\thiredate");
  printf("\n-------------------------------------------------------------------\n");
  do {
    empID = 0;
    memset(bdate,0,100);
    memset(fname,0,100);
    memset(jdate,0,100);
    memset(lname,0,100);
    retcode = SQLFetch(hstmt);
    HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);
    if(retcode == SQL_NO_DATA)
      break;
    printf("\n%d\t%s\t%s\t\t%s\t\t%s", empID, bdate, fname, lname, jdate);

  } while (true);

  printf("\n-------------------------------------------------------------------\n");

  //close the current cursor
  retcode = SQLCloseCursor(hstmt);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //drop tables if exists
  retcode = SQLExecDirect(hstmt, (SQLCHAR*) "DROP TABLE Employees;", SQL_NTS);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  //free environment, connection, statement handles
  retcode = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
  HANDLE_SQLERROR(SQL_HANDLE_STMT, hstmt, retcode);

  retcode = SQLDisconnect(hdbc);
  HANDLE_SQLERROR(SQL_HANDLE_DBC, hdbc, retcode);

  retcode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
  HANDLE_SQLERROR(SQL_HANDLE_DBC, hdbc, retcode);

  retcode = SQLFreeHandle(SQL_HANDLE_ENV, henv);
  HANDLE_SQLERROR(SQL_HANDLE_ENV, henv, retcode);

  return 0;
}