Last active
June 27, 2016 21:13
-
-
Save brianmhess/a1be1dda327706fffaf9 to your computer and use it in GitHub Desktop.
ODBC Tables/Query Test
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## 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= |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
## 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment