Example odbc client

odbc.c

/**
 * Author: Matt Fuller
 *
 * This is a basic test program I wrote while learning ODBC. It connects to a MySQL database I have running. 
 * I mostly followed the tutorial here:
 *             http://www.easysoft.com/developer/languages/c/odbc_tutorial.html
 */
#ifdef _WIN64
    #include <windows.h>
#endif

#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

void list_drivers();
void list_data_sources();
void list_tables();
void list_driver_information();

void presto_connect();
void presto_disconnect();
void play_with_show_tables_and_columns();

void print_results(SQLHSTMT stm);
void execute_query(char *query);

SQLHENV env;
SQLHDBC dbc;

int main(int argc, char** argv) 
{
	if (argc != 3) {
		printf("Usage: %s <ODBC URL> <Presto server port> <query>\n", argv[0]);
		exit(1);
	}
    presto_connect(argv[1]); 

    //list_drivers();
    //list_data_sources();
    //list_driver_information();
    //list_tables();
    
    execute_query(argv[2]);
    //execute_query("select * from tpch.tiny.nation");
    //execute_query("show catalogs");

    //play_with_show_tables_and_columns();

    presto_disconnect();
}

/*ac
 * see Retrieving ODBC Diagnostics
 * for a definition of extract_error().
 */
static 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))
            printf("%s:%ld:%ld:%s\n", state, (long int)i, (long int)native, text);
    }
    while( ret == SQL_SUCCESS );
} 

void list_drivers() 
{
    char driver[256];
    char attr[256];
    SQLSMALLINT driver_ret;
    SQLSMALLINT attr_ret;
    SQLUSMALLINT direction;
    SQLRETURN ret;

    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

    printf("Drivers:\n");
    direction = SQL_FETCH_FIRST;
    while(SQL_SUCCEEDED(ret = SQLDrivers(env, direction,
                                         driver, sizeof(driver), &driver_ret,
                                         attr, sizeof(attr), &attr_ret))) {
        direction = SQL_FETCH_NEXT;
        printf("        %s - %s\n", driver, attr);
        if (ret == SQL_SUCCESS_WITH_INFO) printf("\tdata truncation\n");
    }
}

void list_data_sources()
{
    char dsn[256];
    char desc[256];
    SQLSMALLINT dsn_ret;
    SQLSMALLINT desc_ret;
    SQLUSMALLINT direction;
    SQLRETURN ret;

    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);

    printf("Data Sources:\n");
    direction = SQL_FETCH_FIRST;
    while(SQL_SUCCEEDED(ret = SQLDataSources(env, direction,
                                             dsn, sizeof(dsn), &dsn_ret,
                                             desc, sizeof(desc), &desc_ret))) {
        direction = SQL_FETCH_NEXT;
        printf("        %s - %s\n", dsn, desc);
        if (ret == SQL_SUCCESS_WITH_INFO) printf("\tdata truncation\n");
    }
    printf("\n");
}

void list_driver_information()
{
    SQLCHAR dbms_name[256], dbms_ver[256];
    SQLUINTEGER getdata_support;
    SQLUSMALLINT max_concur_act;
    SQLSMALLINT string_len;

    /*
     *  Find something out about the driver.
     */
    SQLGetInfo(dbc, SQL_DBMS_NAME, (SQLPOINTER)dbms_name,
	       sizeof(dbms_name), NULL);
    SQLGetInfo(dbc, SQL_DBMS_VER, (SQLPOINTER)dbms_ver,
	       sizeof(dbms_ver), NULL);
    SQLGetInfo(dbc, SQL_GETDATA_EXTENSIONS, (SQLPOINTER)&getdata_support,
	       0, 0);
    SQLGetInfo(dbc, SQL_MAX_CONCURRENT_ACTIVITIES, &max_concur_act, 0, 0);
    
    printf("Driver Information:\n");
    printf("    DBMS Name: %s\n", dbms_name);
    printf("    DBMS Version: %s\n", dbms_ver);
    if (max_concur_act == 0) {
        printf("    SQL_MAX_CONCURRENT_ACTIVITIES - no limit or undefined\n");
    } else {
        printf("    SQL_MAX_CONCURRENT_ACTIVITIES = %u\n", max_concur_act);
    }
    if (getdata_support & SQL_GD_ANY_ORDER)
        printf("    SQLGetData - columns can be retrieved in any order\n");
    else
        printf("    SQLGetData - columns must be retrieved in order\n");
    if (getdata_support & SQL_GD_ANY_COLUMN)
        printf("    SQLGetData - can retrieve columns before last bound one\n");
    else
        printf("    SQLGetData - columns must be retrieved after last bound one\n");
    printf("\n");
}

void list_tables()
{
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */

    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    /* Retrieve a list of tables */
    SQLTables(stmt, "tpch", SQL_NTS, "%", SQL_NTS, "%", SQL_NTS, "TABLE", SQL_NTS);
    
    if (SQL_SUCCEEDED(ret)) {
        printf("Tables:\n");
        print_results(stmt);
    } else {
        extract_error("SQLTables", dbc, SQL_HANDLE_DBC);
    }

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    printf("\n");
}

void presto_connect(char* url)
{
    SQLRETURN ret; /* ODBC API return status */
    SQLCHAR outstr[1024];
    SQLSMALLINT outstrlen;

    /* Allocate an environment handle */
    SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    /* We want ODBC 3 support */
    SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
    /* Allocate a connection handle */
    SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    /* Connect to the DSN mydsn */
    
    /*
    ret = SQLSetConnectAttr(dbc, SQL_ATTR_METADATA_ID, SQL_TRUE, 0);
    if (!SQL_SUCCEEDED(ret)) {
        extract_error("SQLSetConnectAttr", dbc, SQL_HANDLE_DBC);
        exit(1);
    }
    */
    printf("Connecting to: %s\n", url);
    //ret = SQLDriverConnect(dbc, NULL, "driver=Teradata Presto ODBC Driver;host=18.217.99.42;port=8080", SQL_NTS,
    ret = SQLDriverConnect(dbc, NULL, url, SQL_NTS,
                           outstr, sizeof(outstr), &outstrlen,
                           SQL_DRIVER_COMPLETE);
    if (SQL_SUCCEEDED(ret)) {
        printf("Connected\n");
        printf("        Returned connection string was:\n\t%s\n", outstr);
        if (ret == SQL_SUCCESS_WITH_INFO) {
            printf("    Driver reported the following diagnostics\n");
            extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
        }
 
    } else {
        fprintf(stderr, "Failed to connect\n");
        extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
        exit(1);
    }

    printf("\n");
}

void presto_disconnect()
{
    /* disconnect from driver */
    SQLDisconnect(dbc);

    /* free up allocated handles */
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);
}

void print_results(SQLHSTMT stmt)
{
    SQLRETURN ret; /* ODBC API return status */
    SQLSMALLINT columns; /* number of columns in result-set */
    int row = 0;

    /* How many columns are there */
    SQLNumResultCols(stmt, &columns);

    /* Loop through the rows in the result-set */
    while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
        SQLUSMALLINT i;
        /* Loop through the columns */
        for (i = 1; i <= columns; i++) {
            SQLLEN indicator;
            char buf[512];
            /* retrieve column data as a string */
            ret = SQLGetData(stmt, i, SQL_C_CHAR,
                             buf, sizeof(buf), &indicator);
            if (SQL_SUCCEEDED(ret)) {
                /* Handle null columns */
                if (indicator == SQL_NULL_DATA) strcpy(buf, "NULL");
                printf(" %16s :", buf);
            }
        }
        printf("\n");
    }

    printf("\n");
}

void execute_query(char *query) 
{
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */

    printf("Query: %s\n", query);

    /* Allocate a statement handle */
    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
   
    /* execute query*/
    ret = SQLExecDirect(stmt, query, SQL_NTS);
    if (SQL_SUCCEEDED(ret)) {
        print_results(stmt);
    } 
    else {
        extract_error("SQLExecDirect", dbc, SQL_HANDLE_DBC);
    }

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}

void play_with_show_tables_and_columns()
{
    SQLHSTMT stmt;
    SQLRETURN ret; /* ODBC API return status */

    execute_query("create schema memory.schema_name");
    execute_query("create schema memory.schemaxname");
    execute_query("create table memory.schema_name.table_name (c varchar, _ varchar)");
    execute_query("create table memory.schemaxname.table_name (c varchar, _ varchar)");
    execute_query("create table memory.schema_name.tablexname (c varchar, _ varchar)");
    execute_query("create table memory.schemaxname.tablexname (c varchar, _ varchar)");

    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    SQLTables(stmt, "memory", SQL_NTS, "schema_name", SQL_NTS, "table_name", SQL_NTS, "TABLE", SQL_NTS);
    if (SQL_SUCCEEDED(ret)) {
        printf("Tables:\n");
        print_results(stmt);
    } else {
        extract_error("SQLTables", dbc, SQL_HANDLE_DBC);
    }
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    execute_query("SELECT 1");

    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    SQLColumns(stmt, "memory", SQL_NTS, "schema_name", SQL_NTS, "table_name", SQL_NTS, "_", SQL_NTS);
    if (SQL_SUCCEEDED(ret)) {
        printf("Columns:\n");
        print_results(stmt);
    } else {
        extract_error("SQLTables", dbc, SQL_HANDLE_DBC);
    }
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);

    execute_query("SELECT 1");
}