PQstatus() detect change in connection...

/***************************************************************************

    DBDriverPostgres.cpp

    Date        Author  Description
    20001114    MP      Created
    20010529    OB      Mods to make this TRULY part of TSAUtils

 ***************************************************************************/

#include "DBDriverPostgres.h"

TSAUTILS_NAMESPACE_BEGIN

/*
 ***** Internal function declarations
 */

    // Trims trailing spaces from string
    char * _rTrim(char *in_string);

/*
 ***** Internal function definitions
 */

// _rTrim()
char * _rTrim(char *in_string)
{
    long lPos;

    // Exit if null
    if (!in_string)
        return "";

    // Find last char in string so we can loop backward
    lPos = strlen(in_string)-1;

    // Loop backward finding last non-space char
    while ((lPos >= 0) && (in_string[lPos] == ' '))
        lPos--;

    // Move forward one place (back on to the spaces) and set null
    lPos++;
    in_string[lPos] = 0;

    return in_string;
}

/*
 ***** DBDriverPostgres
 */

// DBDriverPostgres::_initResultSet()
void DBDriverPostgres::_initResultSet()
{
    // Clear the column names
    m_columnNames.clear();

    // Free the result set
    PQclear(m_resultSet);
    m_resultSet        = NULL;

    // Indicate we did not return rows
    m_blDidReturnRows  = false;

    // Reset the row number
    m_lCurrentRow      = -1;
}

// DBDriverPostgres::_validateFieldAccess()
int DBDriverPostgres::_validateFieldAccess(const string &in_sFldName)
{
    int iFieldIndex;

    // Check if we have rows
    if (!didReturnRows())
        throw TSAUTILS::xExceptionGeneric("No records returned");

    // Check if the field name is valid and return the index if it is
    iFieldIndex = PQfnumber(m_resultSet,in_sFldName.c_str());
    if (iFieldIndex == -1)
        throw TSAUTILS::xExceptionGeneric("Invalid field name %s",in_sFldName.c_str());

    return iFieldIndex;
}

// DBDriverPostgres::colNames
vector <string> DBDriverPostgres::colNames()
{
    // Check if we have rows
    if (!didReturnRows())
        throw TSAUTILS::xExceptionGeneric("Column names unavailable - query did not return rows");

    // Return the list of column names
    return m_columnNames;
}

// DBDriverPostgres::DBDriverPostgres
DBDriverPostgres::DBDriverPostgres(const char *pchHost, const char *pchDB, const char *pchUser, const char *pchPwd)
{
    // Pre-init
    m_resultSet = NULL;

    // Init
    _initResultSet();

    // Build connect string
    sprintf(m_pchTemp,"host=%s dbname=%s user=%s password=%s",pchHost,pchDB,pchUser,pchPwd);

    // Connect to DB
    m_connection = PQconnectdb(m_pchTemp);
    if (PQstatus(m_connection) == CONNECTION_BAD)
        throw TSAUTILS::xExceptionGeneric("Unable to connect to %s on %s. Error - %s",pchDB,pchHost,PQerrorMessage(m_connection));
}

// DBDriverPostgres::~DBDriverPostgres
DBDriverPostgres::~DBDriverPostgres()
{
    // Free result set
    _initResultSet();

    // Free connection
    PQfinish(m_connection);
}

// DBDriverPostgres::fldBool
bool DBDriverPostgres::fldBool(const string &in_sFldName)
{
    // Return whether int <> 0
    return fldInt(in_sFldName);
}

// DBDriverPostgres::fldDbl
double DBDriverPostgres::fldDbl(const string &in_sFldName)
{
    int iFieldIndex = _validateFieldAccess(in_sFldName);

    // Return field value
    return atof(PQgetvalue(m_resultSet,m_lCurrentRow,iFieldIndex));
}

// DBDriverPostgres::fldDT
Date DBDriverPostgres::fldDT(const string &in_sFldName)
{
    // Grab field index and data
    int iFieldIndex     = _validateFieldAccess(in_sFldName);
    string sDateTime    = PQgetvalue(m_resultSet,m_lCurrentRow,iFieldIndex);

    // Trim off the timezone
    size_t iTZstart = sDateTime.find('+');
    if (iTZstart < sDateTime.length())
        sDateTime = sDateTime.substr(0,iTZstart);

    // Return a new date object
    return Date(sDateTime);
}

// DBDriverPostgres::fldFlt
float DBDriverPostgres::fldFlt(const string &in_sFldName)
{
    int iFieldIndex = _validateFieldAccess(in_sFldName);

    // Return field value
    return atof(PQgetvalue(m_resultSet,m_lCurrentRow,iFieldIndex));
}

// DBDriverPostgres::fldInt
int DBDriverPostgres::fldInt(const string &in_sFldName)
{
    int iFieldIndex = _validateFieldAccess(in_sFldName);

    // Return field value
    return atoi(PQgetvalue(m_resultSet,m_lCurrentRow,iFieldIndex));
}

// DBDriverPostgres::fldLong
long DBDriverPostgres::fldLong(const string &in_sFldName)
{
    int iFieldIndex = _validateFieldAccess(in_sFldName);

    // Return field value
    return atol(PQgetvalue(m_resultSet,m_lCurrentRow,iFieldIndex));
}

// DBDriverPostgres::fldStr
string DBDriverPostgres::fldStr(const string &in_sFldName)
{
	string sRet = "";
    int iFieldIndex = _validateFieldAccess(in_sFldName);

    // Return field value
    char *pchTmp = PQgetvalue(m_resultSet,m_lCurrentRow,iFieldIndex);
    if (pchTmp)
    	sRet = _rTrim(pchTmp);
   
   	return sRet;
}

// DBDriverPostgres::didReturnRows
bool DBDriverPostgres::didReturnRows()
{
    return m_blDidReturnRows;
}

// DBDriverPostgres::recordNext
bool DBDriverPostgres::recordNext()
{
    // Return false if we didn't return any rows from the query or we are at end
    if ((!didReturnRows()) || (m_lCurrentRow >= PQntuples(m_resultSet)))
        return false;

    // Move to the next row
    m_lCurrentRow++;

    return (m_lCurrentRow < PQntuples(m_resultSet));
}

// DBDriverPostgres::sqlExec
bool DBDriverPostgres::sqlExec(const char *in_pchSQL)
{
    ExecStatusType execStatus;
    int iFieldCount;

    // Init
    _initResultSet();

    // Check if we have SQL
    if (!in_pchSQL)
        throw TSAUTILS::xExceptionGeneric("Unable to execute query - no SQL specified");

    // Attempt to execute it
    m_resultSet    = PQexec(m_connection,in_pchSQL);
    if (!m_resultSet)
    {
        // Query execution failed. Attempt to reconnect and re-execute
        PQreset(m_connection);
        m_resultSet = PQexec(m_connection,in_pchSQL);
    }

    // Check the result set status
    execStatus      = PQresultStatus(m_resultSet);
    if ((!m_resultSet) || ((execStatus != PGRES_COMMAND_OK) && (execStatus != PGRES_TUPLES_OK)))
        throw TSAUTILS::xExceptionGeneric("Error executing query [%s]. Error = %s",in_pchSQL,PQerrorMessage(m_connection));

    // Exit if we executed a query that doesn't return rows
    if (execStatus == PGRES_COMMAND_OK)
        return didReturnRows();

    // Exit if we executed a query that returns rows, but didn't in this case (select with no matching records)
    if (PQntuples(m_resultSet) == 0)
        return didReturnRows();

    // We have data
    m_blDidReturnRows = true;

    // Init row counter
    m_lCurrentRow = -1;

    // Fetch the list of column names
    iFieldCount = PQnfields(m_resultSet);
    for (int i = 0; i < iFieldCount; i++)
        m_columnNames.push_back(string(PQfname(m_resultSet,i)));

    // Indicate we returned rows
    return didReturnRows();
}

// DBDriverPostgres::transCommit
void DBDriverPostgres::transCommit()
{
    _initResultSet();

    // Start the transaction
    m_resultSet = PQexec(m_connection, "COMMIT");
    if ((!m_resultSet) || (PQresultStatus(m_resultSet) != PGRES_COMMAND_OK))
        throw TSAUTILS::xExceptionGeneric("Unable to start transaction. Error - %s",PQerrorMessage(m_connection));

    _initResultSet();
}

// DBDriverPostgres::transRollback
void DBDriverPostgres::transRollback()
{
    _initResultSet();

    // Start the transaction
    m_resultSet = PQexec(m_connection, "ROLLBACK");
    if ((!m_resultSet) || (PQresultStatus(m_resultSet) != PGRES_COMMAND_OK))
        throw TSAUTILS::xExceptionGeneric("Unable to rollback transaction. Error - %s",PQerrorMessage(m_connection));

    _initResultSet();
}

// DBDriverPostgres::transStart
void DBDriverPostgres::transStart()
{
    _initResultSet();

    // Start the transaction
    m_resultSet = PQexec(m_connection, "BEGIN");
    if ((!m_resultSet) || (PQresultStatus(m_resultSet) != PGRES_COMMAND_OK))
        throw TSAUTILS::xExceptionGeneric("Unable to start transaction. Error - %s",PQerrorMessage(m_connection));

    _initResultSet();
}

TSAUTILS_NAMESPACE_END

#ifndef __TSAUTILS_DB_DRIVER_POSTGRES_H__
#define __TSAUTILS_DB_DRIVER_POSTGRES_H__

/***************************************************************************

    DBDriverPostgres.h

    DBDriverPostgres implements Postgres functionality

    Date        Author  Description
    20001114    MP      Created

 ***************************************************************************/

#include <postgres/libpq-fe.h>

#include "DBDriverGeneric.h"

TSAUTILS_NAMESPACE_BEGIN

/*
 ***** DBDriverPostgres
 */

class DBDriverPostgres : public DBDriverGeneric
{
    private:
        // Whether the last query returned rows
        bool    m_blDidReturnRows;

        // Connection to Postgres database
        PGconn *m_connection;

        // Result set from Postgres
        PGresult *m_resultSet;

        // Column names in the result set
        vector <string> m_columnNames;

        // Current row number
        long m_lCurrentRow;

        // Initialises result set data
        void _initResultSet();

        // Validates OK to access fields
        int _validateFieldAccess(const string &in_sFldName);
    public:
        // Constructor/destructor
        DBDriverPostgres(const char *pchHost, const char *pchDB, const char *pchUser, const char *pchPwd);
        virtual ~DBDriverPostgres();

		// Returns column names
		vector <string> colNames();

        // Retrieves field data
        bool        fldBool(const string &in_sFldName);
        double      fldDbl(const string &in_sFldName);
        Date        fldDT(const string &in_sFldName);
        float       fldFlt(const string &in_sFldName);
        int         fldInt(const string &in_sFldName);
        long        fldLong(const string &in_sFldName);
        string      fldStr(const string &in_sFldName);

        // Record navigation
        bool    didReturnRows();
        bool    recordNext();

        // SQL manipulation
        bool    sqlExec(const char *in_pchSQL);

        // Transaction control
        void    transCommit();
        void    transRollback();
        void    transStart();
};

TSAUTILS_NAMESPACE_END

#endif