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 );
}