DB Connector

/*
 ============================================================================
 Name        : DBConnector.h
 Author      : Stephen Cannon
 Version     : 0.1
 Copyright   : Copyright 2011 Stephen Cannon
 Description :
 ============================================================================
 *
 * This file is part of LikelihoodWeighting.
 *
 * LikelihoodWeighting is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or (at your
 * option) any later version.
 *
 * LikelihoodWeighting is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public
 * License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with LikelihoodWeighting.  If not, see <http://www.gnu.org/licenses/>.
 *
 */
 
#ifndef DBCONNECTOR_H_
#define DBCONNECTOR_H_
 
#include <windows.h>
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>
 
#ifdef __cplusplus
extern "C" {
#endif
 
 
enum { BUFFER_SIZE = 1024, LARGE_BUFFER_SIZE = 10240 };
enum {	DBCONNECTOR_STATE_NOT_CONNECTED = 0,
		DBCONNECTOR_STATE_CONNECTED = 1,
		DBCONNECTOR_STATE_STMT_PREPARED = 2,
        DBCONNECTOR_STATE_STMT_EXECUTED = 3};
enum {	ERR_GENERAL_ERROR = -1,
		ERR_ALREADY_CONNECTED = -9,
		ERR_NOT_CONNECTED = -10,
		ERR_NO_PREPARED_STMT = -11,
		ERR_STMT_NOT_EXECUTED = -12};
 
/**
 * Defines a parameter binding in a prepared statement.  A parameter binding
 * defines information about the parameter being bound to a prepared statement
 * as well as holds the value to be passed into the prepared statement when it
 * is used in a DB query.  There is a parameter binding for each "?" in a
 * prepared statement.
 */
typedef struct _ParamBinding_
{
	SQLSMALLINT		ioType;
	SQLSMALLINT		valueType;
	SQLSMALLINT		paramType;
	SQLULEN			columnSize;
	SQLSMALLINT		decimalDigits;
	SQLPOINTER		paramValuePtr;
	SQLLEN			bufferLength;
	SQLLEN			*indPtr;
} ParamBinding;
 
/**
 * Defines a column binding in a prepared statement.  A column binding defines
 * a column's type and holds the value returned for that column after the DB
 * has been queried.
 */
typedef struct _ColBinding_
{
	SQLSMALLINT		type;
	SQLPOINTER		valuePtr;
	SQLLEN			bufferLength;
	SQLLEN			*indPtr;
} ColBinding;
 
/**
 * Defines a DB connection and allows one to query that DB
 */
typedef struct _DBConnector_
{
	SQLHENV h_env;
	SQLHDBC h_dbc;
	SQLHSTMT h_stmt;
	SQLCHAR *stmt;
	const char *connectionString;
	size_t state;
} DBConnector;
 
char DBConnector__init(DBConnector *self, const char *connectionString);
char DBConnector__connect(DBConnector *self);
char DBConnector__prepareStatement(DBConnector *self, const char *stmt, ParamBinding *paramBinding, size_t numParams, ColBinding *colBinding, size_t numCols);
char DBConnector__executePreparedStatement(DBConnector *self);
char DBConnector__fetchExecutedStatementResult(DBConnector *self, char *error);
char DBConnector__disconnect(DBConnector *self);
char DBConnector__del(DBConnector *self);
void __DBConnector__extractError(const char *fn, SQLHANDLE handle, SQLSMALLINT type);
 
#ifdef __cplusplus
}
#endif
 
#endif /* DBCONNECTOR_H_ */

/*
 ============================================================================
 Name        : DBConnector.c
 Author      : Stephen Cannon
 Version     : 0.1
 Copyright   : Copyright 2011 Stephen Cannon
 Description :
 ============================================================================
 *
 * This file is part of LikelihoodWeighting.
 *
 * LikelihoodWeighting is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Lesser General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or (at your
 * option) any later version.
 *
 * LikelihoodWeighting is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public
 * License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with LikelihoodWeighting.  If not, see <http://www.gnu.org/licenses/>.
 *
 */
 
#include "DBConnector.h"
 
 
/**
 * Initializes the DBConnector
 * @param connectionString The connection string to use for this DB connection
 * @return Returns true on error, false on success.
 */
char DBConnector__init(DBConnector *self, const char *connectionString)
{
	self->connectionString = connectionString;
 
	if(!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &self->h_env)))
		return ERR_GENERAL_ERROR;
	if(!SQL_SUCCEEDED(SQLSetEnvAttr(self->h_env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0)))
		return ERR_GENERAL_ERROR;
	if(!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, self->h_env, &self->h_dbc)))
		return ERR_GENERAL_ERROR;
 
	self->state = DBCONNECTOR_STATE_NOT_CONNECTED;
	return 0;
}
 
/**
 * Connects to the DB.  This method must be called after DBConnector__init()
 * @return Returns true on error, false on success.
 */
char DBConnector__connect(DBConnector *self)
{
	SQLRETURN retVal;
	SQLSMALLINT dwLength;
 
	// Check that DBConnector is not already connected
	if(self->state > DBCONNECTOR_STATE_NOT_CONNECTED)
		return ERR_ALREADY_CONNECTED;
 
	if(!SQL_SUCCEEDED(retVal = SQLDriverConnect(self->h_dbc,
			0,
			(SQLCHAR *)self->connectionString,
			SQL_NTS,
			0,
			0,
			&dwLength,
			SQL_DRIVER_NOPROMPT)))
	{
		__DBConnector__extractError("SQLDriverConnect", self->h_dbc, SQL_HANDLE_DBC);
		return (char)retVal;
	}
	if(!SQL_SUCCEEDED(retVal = SQLAllocHandle(SQL_HANDLE_STMT, self->h_dbc, &self->h_stmt)))
	{
		__DBConnector__extractError("SQLDriverConnect", self->h_dbc, SQL_HANDLE_DBC);
		return (char)retVal;
	}
 
	self->state = DBCONNECTOR_STATE_CONNECTED;
	return 0;
}
 
/**
 * Prepares a statement to query the DB with.  This method must be called after
 * DBConnector__connect()
 * @param stmt The prepared statement with "?" to represent bound parameters to
 * be filled in later
 * @param paramBinding An array of ParamBindings describing each parameter
 * described with a "?" in the given statement
 * @param numParams The number of ParamBindings in the paramBinding parameter
 * @param colBinding An array of ColBindings describing each column in the
 * result set generated after a query is made with the given statement
 * @param numCols The number of ColBindings in the colBinding parameter
 * @return Returns true on error, false on success.
 */
char DBConnector__prepareStatement(DBConnector *self, const char *stmt, ParamBinding *paramBinding, size_t numParams, ColBinding *colBinding, size_t numCols)
{
	SQLRETURN retVal;
	size_t i = 0;
 
	// Check that DBConnector is connected
	if(self->state < DBCONNECTOR_STATE_CONNECTED)
		return ERR_NOT_CONNECTED;
 
	// Prepare statement handle
	self->stmt = (SQLCHAR *)stmt;
	if(!SQL_SUCCEEDED(retVal = SQLPrepare(self->h_stmt, self->stmt, SQL_NTS)))
	{
		__DBConnector__extractError("SQLPrepare", self->h_stmt, SQL_HANDLE_STMT);
		return (char)retVal;
	}
 
	// Bind parameters to buffers
	for(i = 0; i < numParams && i < (size_t)-1; i++)
	{
		//SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, EMPLOYEE_ID_LEN, 0, szEmployeeID, 0, &cbEmployeeID);
		if(!SQL_SUCCEEDED(retVal = SQLBindParameter(self->h_stmt,
				i + 1,
				paramBinding[i].ioType,
				paramBinding[i].valueType,
				paramBinding[i].paramType,
				paramBinding[i].columnSize,
				paramBinding[i].decimalDigits,
				paramBinding[i].paramValuePtr,
				paramBinding[i].bufferLength,
				paramBinding[i].indPtr)))
		{
			__DBConnector__extractError("SQLBindParameter", self->h_stmt, SQL_HANDLE_STMT);
			return (char)retVal;
		}
	}
 
	// Bind columns to buffers to collect results
	for(i = 0; i < numCols && i < (size_t)-1; i++)
	{
		if(!SQL_SUCCEEDED(retVal = SQLBindCol(self->h_stmt,
				i + 1,
				colBinding[i].type,
				colBinding[i].valuePtr,
				colBinding[i].bufferLength,
				colBinding[i].indPtr)))
		{
			__DBConnector__extractError("SQLBindCol", self->h_stmt, SQL_HANDLE_STMT);
			return (char)retVal;
		}
	}
 
	self->state = DBCONNECTOR_STATE_STMT_PREPARED;
	return 0;
}
 
/**
 * Executes the prepared statement.  This method must be called after
 * DBConnector__prepareStatement()
 * @return Returns true on error, false on success.
 */
char DBConnector__executePreparedStatement(DBConnector *self)
{
	SQLRETURN retVal;
 
	// Check that there is a prepared statement
	if(self->state < DBCONNECTOR_STATE_STMT_PREPARED)
		return ERR_NO_PREPARED_STMT;
 
	if(!SQL_SUCCEEDED(retVal = SQLExecute(self->h_stmt)))
	{
		__DBConnector__extractError("SQLExecute", self->h_stmt, SQL_HANDLE_STMT);
		return (char)retVal;
	}
 
	self->state = DBCONNECTOR_STATE_STMT_EXECUTED;
	return 0;
}
 
/**
 * Returns a single result from the result set generated when the DB is
 * queried.  This method must be called after
 * DBConnector__executePreparedStatement()
 * @param error [OUT] If an error occors, the error value is returned here.
 * Otherwise, the value returned here is 0
 * @return Returns true if there are more results to retrieve in the result set
 * and no error has occured, false otherwise.
 */
char DBConnector__fetchExecutedStatementResult(DBConnector *self, char *error)
{
	SQLRETURN retVal;
 
	// Check that there is a prepared statement that was executed
	if(self->state < DBCONNECTOR_STATE_STMT_EXECUTED)
		return ERR_STMT_NOT_EXECUTED;
 
	// Fetch the next line of results from the result set
	if(!SQL_SUCCEEDED(retVal = SQLFetch(self->h_stmt)))
	{
		// Lack of success might just mean at end of result set
		if(retVal == SQL_NO_DATA || retVal == SQL_STILL_EXECUTING)
			*error = 0;
		else
		{
			__DBConnector__extractError("SQLFetch", self->h_stmt, SQL_HANDLE_STMT);
			*error = (char)retVal;
		}
 
		return 0;
	}
 
	// No errors so set error to 0
	*error = 0;
 
	return retVal == SQL_SUCCESS || SQL_SUCCESS_WITH_INFO;
}
 
/**
 * Disconnects from the DB.  This method must be called after
 * DBConnector__connect()
 * @return Returns true on error, false on success.
 */
char DBConnector__disconnect(DBConnector *self)
{
	SQLRETURN retVal;
 
	// Check that there is a prepared statement
	if(self->state < 1)
		return ERR_NOT_CONNECTED;
 
	if(!SQL_SUCCEEDED(retVal = SQLFreeHandle(SQL_HANDLE_STMT, self->h_stmt)))
		return (char)retVal;
	if(!SQL_SUCCEEDED(retVal = SQLDisconnect(self->h_dbc)))
		return (char)retVal;
 
	self->state = DBCONNECTOR_STATE_NOT_CONNECTED;
	return 0;
}
 
/**
 * Destroys the DBConnector.  This method must be called after
 * DBConnector__init()
 * @return Returns true on error, false on success.
 */
char DBConnector__del(DBConnector *self)
{
	SQLRETURN retVal;
 
	if(!SQL_SUCCEEDED(retVal = SQLFreeHandle(SQL_HANDLE_DBC, self->h_dbc)))
		return (char)retVal;
	if(!SQL_SUCCEEDED(retVal = SQLFreeHandle(SQL_HANDLE_ENV, self->h_env)))
		return (char)retVal;
 
	return 0;
}
 
void __DBConnector__extractError(const char *fn, SQLHANDLE handle, SQLSMALLINT type)
{
    SQLSMALLINT i = 0;
    SQLINTEGER native;
    SQLCHAR state[7];
    SQLCHAR text[1024];
    SQLSMALLINT len;
    SQLRETURN ret;
 
    fprintf(stderr,
            "\n"
            "Error while running "
            "%s\n\n",
            fn);
 
    printf("index : state : native : error message\n");
    do
    {
        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text,
                            sizeof(text), &len );
        if (SQL_SUCCEEDED(ret))
            printf("%ld:%s:%ld:%s\n", i, state, native, text);
		else
			printf("Error while running SQLGetDiagRec");
    }
    while( ret == SQL_SUCCESS );
}