ODBC Tables/Query Test

.odbc.ini

## If you did not install in the default directory, replace '/opt/datastax/cassandraodbc' 
## with the correct location.

[ODBC]
Trace=no

[ODBC Data Sources]
spark64=Simba Spark ODBC Driver 64-bit

[spark64]

# Description: DSN Description.
# This key is not necessary and is only to give a description of the data source.
Description=Simba Spark ODBC Driver (64-bit) DSN

# Driver: The location where the ODBC driver is installed to.
Driver=/opt/simba/sparkodbc/lib/64/libsparkodbc_sb64.so

# The host name or IP of the Thrift server.
HOST=192.168.159.157

# The TCP port Thrift server is listening.
PORT=10000

# The name of the database schema to use when a schema is not explicitly specified in a query.
Schema=test

# The Spark Server Type
# 1 - Shark Server 1 for Shark 0.8.1 and earlier
# 2 - Shark Server 2 for Shark 0.9.*
# 3 - Spark Thrift Server for Shark 1.1 and later 
SparkServerType=3

# The authentication mechanism to use for the connection.
#   Set to 0 for No Authentication
#   Set to 1 for Kerberos
#   Set to 2 for User Name
#   Set to 3 for User Name and Password
# Note only No Authentication is supported when connecting to Shark Server 1.
AuthMech=2

# The Thrift transport to use for the connection.
#	Set to 0 for Binary
#	Set to 1 for SASL
#	Set to 2 for HTTP
# Note for Shark Server 1 only Binary can be used.
ThriftTransport=1

# When this option is enabled (1), the driver does not transform the queries emitted by an 
# application, so the native query is used.
# When this option is disabled (0), the driver transforms the queries emitted by an application and 
# converts them into an equivalent from in Spark SQL.
UseNativeQuery=0

# Set the UID with the user name to use to access Spark when using AuthMech 2 or 3.
UID=datastax

# The following is settings used when using Kerberos authentication (AuthMech 1)

# The fully qualified host name part of the of the Spark Thrift Server Kerberos service principal.
# For example if the service principal name of you Spark Thrift Server is:
#   spark/myhs2.mydomain.com@EXAMPLE.COM
# Then set KrbHostFQDN to myhs2.mydomain.com
KrbHostFQDN=[Spark Thrift Server Host FQDN]

# The service name part of the of the Spark Thrift Server Kerberos service principal.
# For example if the service principal name of you Spark Thrift Server is:
#   spark/myhs2.mydomain.com@EXAMPLE.COM
# Then set KrbServiceName to spark
KrbServiceName=[Spark Thrift Server Kerberos service name]

# The realm part of the of the Spark Thrift Server Kerberos service principal.
# For example if the service principal name of you Spark Thrift Server is:
#   spark/myhs2.mydomain.com@EXAMPLE.COM
# Then set KrbRealm to EXAMPLE.COM
KrbRealm=[Spark Thrift Server Kerberos realm]

# Set to 1 to enable SSL. Set to 0 to disable.
SSL=0

# Set to 1 to enable two-way SSL. Set to 0 to disable. You must enable SSL in order to
# use two-way SSL.
TwoWaySSL=0

# The file containing the client certificate in PEM format. This is required when using two-way SSL.
ClientCert=

# The client private key. This is used for two-way SSL authentication.
ClientPrivateKey=

# The password for the client private key. Password is only required for password protected
# client private key.
ClientPrivateKeyPassword=

.odbcinst.ini

## If you did not install in the default directory, replace '/opt/datastax/cassandraodbc' 
## with the correct location.

[ODBC Drivers]
Simba Spark ODBC Driver 64-bit=Installed

[Simba Spark ODBC Driver 64-bit]
Description=Simba Spark ODBC Driver (64-bit)
Driver=/opt/simba/sparkodbc/lib/64/libsparkodbc_sb64.so

odbcsql.c

// gcc -o odbctables odbctables.c -lodbc
// $ ./odbctables DSN=spark64 "SELECT MAX(Length) FROM test.fischeriris"

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

/*******************************************/
/* Macro to call ODBC functions and        */
/* report an error on failure.             */
/* Takes handle, handle type, and stmt     */
/*******************************************/

#define TRYODBC(h, ht, x)   {   RETCODE rc = x;		\
    if (rc != SQL_SUCCESS)				\
      {							\
	HandleDiagnosticRecord (h, ht, rc);		\
      }							\
    if (rc == SQL_ERROR)				\
      {							\
	fprintf(stderr, "Error in " #x "\n");	        \
	goto Exit;					\
      }							\
  }

/******************************************/
/* Forward references                     */
/******************************************/

void HandleDiagnosticRecord (SQLHANDLE      hHandle,    
			     SQLSMALLINT    hType,  
			     RETCODE        RetCode);

void DisplayResults(HSTMT       hStmt,
                    SQLSMALLINT cCols,
		    bool        silent);

/*****************************************/
/* Some constants                        */
/*****************************************/
#define MAXCOLS (100)
#define BUFFERLEN (1024)


int main(int argc, char **argv)
{
  SQLHENV     hEnv = NULL;
  SQLHDBC     hDbc = NULL;
  SQLHSTMT    hStmt = NULL;
  char*       pConnStr;
  char*       pQuery;
  bool        silent = false;

  if ((argc != 3) && (argc != 4)) {
    fprintf(stderr, "Usage: %s <ConnString> <Query> [silent>\n", argv[0]);
    return 1;
  }
  pConnStr = argv[1];
  pQuery = argv[2];
  if (4 == argc) {
    if (0 != strncmp("silent", argv[3], 6)) {
      fprintf(stderr, "Usage: %s <ConnString> <Query> [silent>\n", argv[0]);
      return 1;
    }
    else {
      silent = true;
    }
  }
  

  // Allocate an environment
  if (!silent)
    fprintf(stderr, "Allocating Handle Enviroment\n");
  if (SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv) == SQL_ERROR)
    {
      fprintf(stderr, "Unable to allocate an environment handle\n");
      exit(-1);
    }

  // Register this as an application that expects 3.x behavior,
  // you must register something if you use AllocHandle
  if (!silent)
    fprintf(stderr, "Setting to ODBC3\n");
  TRYODBC(hEnv,
	  SQL_HANDLE_ENV,
	  SQLSetEnvAttr(hEnv,
			SQL_ATTR_ODBC_VERSION,
			(SQLPOINTER)SQL_OV_ODBC3,
			0));

  // Allocate a connection
  if (!silent)
    fprintf(stderr, "Allocating Handle\n");
  TRYODBC(hEnv,
	  SQL_HANDLE_ENV,
	  SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc));

  // Connect to the driver.  Use the connection string if supplied
  // on the input, otherwise let the driver manager prompt for input.
  if (!silent)
    fprintf(stderr, "Connecting to driver\n");
  TRYODBC(hDbc,
	  SQL_HANDLE_DBC,
	  SQLDriverConnect(hDbc,
			   NULL,
			   pConnStr,
			   SQL_NTS,
			   NULL,
			   0,
			   NULL,
			   SQL_DRIVER_COMPLETE));

  fprintf(stderr, "Connected!\n");

  if (!silent)
    fprintf(stderr, "Allocating statement\n");
  TRYODBC(hDbc,
	  SQL_HANDLE_DBC,
	  SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt));

  RETCODE     RetCode;
  SQLSMALLINT sNumResults;

  // Execute the query
  if (!silent)
    fprintf(stderr, "Executing query\n");
  RetCode = SQLExecDirect(hStmt, pQuery, SQL_NTS);

  switch(RetCode)
    {
    case SQL_SUCCESS_WITH_INFO:
      {
	HandleDiagnosticRecord(hStmt, SQL_HANDLE_STMT, RetCode);
	// fall through
      }
    case SQL_SUCCESS:
      {
	// If this is a row-returning query, display
	// results
	TRYODBC(hStmt,
		SQL_HANDLE_STMT,
		SQLNumResultCols(hStmt,&sNumResults));

	if (sNumResults > 0)
	  {
	    DisplayResults(hStmt,sNumResults, silent);
	  } 
	else
	  {
	    SQLLEN cRowCount;

	    TRYODBC(hStmt,
		    SQL_HANDLE_STMT,
		    SQLRowCount(hStmt,&cRowCount));

	    if (cRowCount >= 0)
	      {
		printf("%d %s affected\n",
		       (int)cRowCount,
		       (cRowCount == 1) ? "row" : "rows");
	      }
	  }
	break;
      }

    case SQL_ERROR:
      {
	HandleDiagnosticRecord(hStmt, SQL_HANDLE_STMT, RetCode);
	break;
      }

    default:
      fprintf(stderr, "Unexpected return code %hd!\n", RetCode);

    }
  TRYODBC(hStmt,
	  SQL_HANDLE_STMT,
	  SQLFreeStmt(hStmt, SQL_CLOSE));

 Exit:

  // Free ODBC handles and exit

  if (hStmt)
    {
      SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
    }

  if (hDbc)
    {
      SQLDisconnect(hDbc);
      SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    }

  if (hEnv)
    {
      SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
    }

  return 0;

}

/************************************************************************
/* DisplayResults: display results of a select query
/*
/* Parameters:
/*      hStmt      ODBC statement handle
/*      cCols      Count of columns
/************************************************************************/

void DisplayResults(HSTMT       hStmt,
                    SQLSMALLINT cCols,
		    bool        silent)
{
  SQLSMALLINT     cDisplaySize;
  RETCODE         RetCode = SQL_SUCCESS;
  int             iCount = 0;
  long long numReceived = 0;

  // Allocate memory for each column 
  SQLCHAR buffer[MAXCOLS][BUFFERLEN];
  SQLLEN indPtr[MAXCOLS];
  int iCol;

  for (iCol = 0; iCol < cCols; iCol++) {
    TRYODBC(hStmt,
	    SQL_HANDLE_STMT,
	    SQLBindCol(hStmt,
		       iCol+1,
		       SQL_C_CHAR,
		       (SQLPOINTER) buffer[iCol],
		       (BUFFERLEN) * sizeof(char),
		       &indPtr[iCol]));    
  }

  // Fetch and display the data

  bool fNoData = false;

  do {
    // Fetch a row

    TRYODBC(hStmt, SQL_HANDLE_STMT, RetCode = SQLFetch(hStmt));

    if (RetCode == SQL_NO_DATA_FOUND)
      {
	fNoData = true;
      }
    else
      {
	if (!silent) {
	  // Display the data.   Ignore truncations	
	  printf("%s", buffer[0]);
	  for (iCol = 1; iCol < cCols; iCol++) {
	    printf(",%s", buffer[iCol]);
	  }
	  printf("\n");
	}

	numReceived++;
      }
  } while (!fNoData);

 Exit:
  printf("numRecieved = %lld\n", numReceived);
}

/************************************************************************
/* HandleDiagnosticRecord : display error/warning information
/*
/* Parameters:
/*      hHandle     ODBC handle
/*      hType       Type of handle (HANDLE_STMT, HANDLE_ENV, HANDLE_DBC)
/*      RetCode     Return code of failing command
/************************************************************************/

void HandleDiagnosticRecord (SQLHANDLE      hHandle,    
                             SQLSMALLINT    hType,  
                             RETCODE        RetCode)
{
  SQLSMALLINT iRec = 0;
  SQLINTEGER  iError;
  char        message[1000];
  char        state[SQL_SQLSTATE_SIZE+1];


  if (RetCode == SQL_INVALID_HANDLE)
    {
      fprintf(stderr, "Invalid handle!\n");
      return;
    }

  while (SQLGetDiagRec(hType,
		       hHandle,
		       ++iRec,
		       state,
		       &iError,
		       message,
		       (SQLSMALLINT)(sizeof(message) / sizeof(WCHAR)),
		       (SQLSMALLINT *)NULL) == SQL_SUCCESS)
    {
      // Hide data truncated..
      if (strncmp(state, "01004", 5))
        {
	  fprintf(stderr, "[%5.5s] %s (%d)\n", state, message, iError);
        }
    }

}

odbctables.c

// gcc -o odbctables odbctables.c -lodbc
// $ ./odbctables DSN=spark64
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <stdbool.h>

#define TRYODBC(h, ht, x)   {   RETCODE rc = x;         \
    if (rc != SQL_SUCCESS)                              \
      {                                                 \
        HandleDiagnosticRecord (h, ht, rc);             \
      }                                                 \
    if (rc == SQL_ERROR)                                \
      {                                                 \
        fprintf(stderr, "Error in " #x "\n");           \
        goto Exit;                                      \
      }                                                 \
  }

#define MAXCOLS (100)
#define BUFFERLEN (1024)

// simple helper functions
int MySQLSuccess(SQLRETURN rc) {
  int myrc;
  myrc = (rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO) ? 1 : 0;
  return myrc;
}

struct DataBinding {
  SQLSMALLINT TargetType;
  SQLPOINTER TargetValuePtr;
  SQLINTEGER BufferLength;
  SQLLEN StrLen_or_Ind;
};


void HandleDiagnosticRecord (SQLHANDLE      hHandle,
                             SQLSMALLINT    hType,
                             RETCODE        RetCode)
{
  SQLSMALLINT iRec = 0;
  SQLINTEGER  iError;
  char        message[1000];
  char        state[SQL_SQLSTATE_SIZE+1];


  if (RetCode == SQL_INVALID_HANDLE)
    {
      fprintf(stderr, "Invalid handle!\n");
      return;
    }

  while (SQLGetDiagRec(hType,
                       hHandle,
                       ++iRec,
                       state,
                       &iError,
                       message,
                       (SQLSMALLINT)(sizeof(message) / sizeof(WCHAR)),
                       (SQLSMALLINT *)NULL) == SQL_SUCCESS)
    {
      // Hide data truncated..
      if (strncmp(state, "01004", 5))
        {
          fprintf(stderr, "[%5.5s] %s (%d)\n", state, message, iError);
        }
    }

}

void printCatalog(const struct DataBinding* catalogResult) {
  if (catalogResult[0].StrLen_or_Ind != SQL_NULL_DATA)
    printf("Catalog Name = %s\n", (char *)catalogResult[0].TargetValuePtr);
}

void DisplayResults(HSTMT       hStmt,
                    SQLSMALLINT cCols)
{
  SQLSMALLINT     cDisplaySize;
  RETCODE         RetCode = SQL_SUCCESS;
  int             iCount = 0;
  long long numReceived = 0;

  // Allocate memory for each column
  SQLCHAR buffer[MAXCOLS][BUFFERLEN];
  SQLLEN indPtr[MAXCOLS];
  int iCol;

  for (iCol = 0; iCol < cCols; iCol++) {
    TRYODBC(hStmt,
            SQL_HANDLE_STMT,
            SQLBindCol(hStmt,
                       iCol+1,
                       SQL_C_CHAR,
                       (SQLPOINTER) buffer[iCol],
                       (BUFFERLEN) * sizeof(char),
                       &indPtr[iCol]));
  }

  // Fetch and display the data

  bool fNoData = false;

  do {
    // Fetch a row

    TRYODBC(hStmt, SQL_HANDLE_STMT, RetCode = SQLFetch(hStmt));

    if (RetCode == SQL_NO_DATA_FOUND)
      {
        fNoData = true;
      }
    else
      {
        // Display the data.   Ignore truncations
        printf("%s", buffer[0]);
        for (iCol = 1; iCol < cCols; iCol++) {
          printf(",%s", buffer[iCol]);
        }
        printf("\n");

        numReceived++;
      }
  } while (!fNoData);

 Exit:

  printf("numRecieved = %lld\n", numReceived);

}

void extract_error(
                   char *fn,
                   SQLHANDLE handle,
                   SQLSMALLINT type)
{
  SQLINTEGER i = 0;
  SQLINTEGER native;
  SQLCHAR state[ 7 ];
  SQLCHAR text[256];
  SQLSMALLINT len;
  SQLRETURN ret;

  fprintf(stderr,
            "\n"
            "The driver reported the following diagnostics whilst running "
          "%s\n\n",
          fn);

    do
      {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
          fprintf(stderr, "%s:%ld:%ld:%s\n", state, (long)i, (long)native, text);
      }
    while( ret == SQL_SUCCESS );
    fprintf(stderr, "DONE\n");
}

// remember to disconnect and free memory, and free statements and handles
int main(int argc, char **argv) {
  if (2 != argc) {
    fprintf(stderr, "Usage: %s <DSN>\n", argv[0]);
    return -1;
  }
  char *dsn = argv[1];
  int bufferSize = 1024, i, numCols = 5;
  struct DataBinding* catalogResult = (struct DataBinding*) malloc( numCols * sizeof(struct DataBinding) );
  char* dbName = (char*)malloc( sizeof(char)*bufferSize );
  char* userName = (char*)malloc( sizeof(char)*bufferSize );

  // declare and initialize the environment, connection, statement handles
  SQLHENV henv = NULL;   // Environment
  SQLHDBC hdbc = NULL;   // Connection handle
  SQLHSTMT hstmt = NULL;   // Statement handle

  SQLRETURN retCode;
  SQLWCHAR connStrbuffer[1024];
  SQLSMALLINT connStrBufferLen;

  retCode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  TRYODBC(henv, SQL_HANDLE_ENV, SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, -1));
  TRYODBC(henv, SQL_HANDLE_ENV, SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc));
  retCode = SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)10, 0);

  TRYODBC(hdbc, SQL_HANDLE_DBC, SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE));

  TRYODBC(hdbc, SQL_HANDLE_DBC, SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt));

  fprintf(stderr, "hstmt: %p\n", hstmt);
  // all catalogs query
  retCode = SQLTables( hstmt,
                       NULL, 0,
                       NULL, 0,
                       NULL, 0,
                       NULL, 0);
  fprintf(stderr, "hstmt: %p\n", hstmt);
  SQLSMALLINT sNumResults;
  switch(retCode)
    {
    case SQL_SUCCESS_WITH_INFO:
      {
        HandleDiagnosticRecord(hstmt, SQL_HANDLE_STMT, retCode);
        // fall through
      }
    case SQL_SUCCESS:
      {
        // If this is a row-returning query, display
        // results
        TRYODBC(hstmt,
                SQL_HANDLE_STMT,
                SQLNumResultCols(hstmt,&sNumResults));

        if (sNumResults > 0)
          {
            printf("Catalog,Schema,Table,Type\n");
            DisplayResults(hstmt,sNumResults);
          }
        else
          {
            SQLLEN cRowCount;

            TRYODBC(hstmt,
                    SQL_HANDLE_STMT,
                    SQLRowCount(hstmt,&cRowCount));

            if (cRowCount >= 0)
              {
                printf("%d %s affected\n",
                       (int)cRowCount,
                       (cRowCount == 1) ? "row" : "rows");
              }
          }
        break;
      }

    case SQL_ERROR:
      {
        HandleDiagnosticRecord(hstmt, SQL_HANDLE_STMT, retCode);
        break;
      }

    default:
      fprintf(stderr, "Unexpected return code %hd!\n", retCode);

    }

 Exit:

  if (hstmt)
    {
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    }

  if (hdbc)
    {
      SQLDisconnect(hdbc);
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    }

  if (henv)
    {
      SQLFreeHandle(SQL_HANDLE_ENV, henv);
    }

  return 0;

}