pgsqlquery.cpp

C++连接一写常用数据库的接口

            free(_recordValues);
            _recordValues = NULL;
            _numRecordValues = 0;
         }
       
         break;
         
      case BIND_PARAMETERS:
         if (_parameters)
         {
            for (i=0; i<_numParameters; i++)
            {
               if (_parameters[i])
               {
                  delete _parameters[i];
                  _parameters[i] = NULL;         
               }
            }
            free(_parameters);
            _parameters = NULL;
            _numParameters = 0;
         }
               
         break;      
   }
}  // PostgresqlQuery::_freeCollection



// -----------------------------------------------------------------------------
// PUBLIC:
// -----------------------------------------------------------------------------


//------------------------------------------------------------------------------
// PostgresqlQuery::PostgresqlQuery
//------------------------------------------------------------------------------
PostgresqlQuery::PostgresqlQuery(
   PostgresqlConnection* parentConnection,
   int                   index)
: 
   _parentConnection(parentConnection),
   _index(index),
   _isTransaction(false),
   __pgsql_res__(NULL),
   _pgsqlNumRows(0),
   _pgsqlCurrentRow(0),
   _recordValues(NULL),
   _numRecordValues(0),
   _fieldInformation(NULL),
   _numFieldInformation(0),
   _parameters(NULL),
   _numParameters(0)
{

}  // PostgresqlQuery::PostgresqlQuery



//------------------------------------------------------------------------------
// PostgresqlQuery::~PostgresqlQuery
//------------------------------------------------------------------------------
PostgresqlQuery::~PostgresqlQuery()
{   
   // If there is still a transaction, rollback   
   if (_isTransaction)
      rollback();   
   
   //Make sure any stored query results are freed.
   if (__pgsql_res__ != NULL)
   {
      PQclear(__pgsql_res__);
      __pgsql_res__ = NULL;
   }

   // Free any parameters
   _freeCollection(BIND_PARAMETERS);
   
   // Free the current field values
   _freeCollection(FIELD_VALUES);
  
   // Clear any previouse field information.
   _freeCollection(FIELD_INFORMATION);

   // Release this connection from the parent connection object.
   _parentConnection->releaseQueryConnection(this);
}  // PostgresqlQuery::~PostgresqlQuery



//------------------------------------------------------------------------------
// PostgresqlQuery::clearBindParams
//------------------------------------------------------------------------------
void 
PostgresqlQuery::clearBindParams()
{
   // Free all the bind parameters.
   _freeCollection(BIND_PARAMETERS);   
}  // PostgresqlQuery::clearBindParams


//------------------------------------------------------------------------------
// PostgresqlQuery::command
//------------------------------------------------------------------------------
void
PostgresqlQuery::command(
   const string& sqlStatement)
{   
   // Clear any bind parameters as we now have a new query
   clearBindParams();   
   
   // Check if this is a function been called and build the correct sql if it is
   string newSqlStatement = _pgsqlParseFunctionCall(sqlStatement);
   
   // Call the base query to store a copy of the query.
   BaseQuery::command(newSqlStatement);   
} // PostgresqlQuery::command


//------------------------------------------------------------------------------
// PostgresqlQuery::bindParam
//------------------------------------------------------------------------------
BaseValue*
PostgresqlQuery::bindParam(
   const string& paramName)
{   
   // Make sure the name has not already been added. If it has, return the instance to it
   for (int i=0; i<_numParameters; i++)
      if (strcasecmp(_parameters[i]->bindName.c_str(), paramName.c_str()) == 0)
         return _parameters[i];

   // Make sure the bind param is not reserved      
   if (strcasecmp(paramName.c_str(), "result") == 0)
   {
      string err = "bindParam(): The specified bind parameter, ";
      err += paramName;
      err += ", is a reserved name";
      throw BindParameterError(err);
   }     
      
   // Make sure the parameter is present in the quey
   if (!_isBindParameterPresent(paramName))
   {
      string err = "bindParam(): The specified bind parameter, ";
      err += paramName;
      err += ", is not present in the SQL statement: ";
      err += _sqlStatement;      
      throw BindParameterNotPresent(err);
   }
      
   // Add the value to the parameters array.
   _numParameters++;
   _parameters = (PostgresqlBindParam**)realloc((void*)_parameters, _numParameters * sizeof(PostgresqlBindParam*));
   _parameters[_numParameters-1] = new PostgresqlBindParam(paramName);        
   return _parameters[_numParameters-1];
}  // PostgresqlQuery::bindParam


//------------------------------------------------------------------------------
// PostgresqlQuery::execute
//------------------------------------------------------------------------------
void
PostgresqlQuery::execute()
{
   // Make sure any stored query results are freed.
   if (__pgsql_res__ != NULL)
   {
      PQclear(__pgsql_res__);
      __pgsql_res__ = NULL;
   }

   // Number of rows in the result set is now 0
   _pgsqlNumRows = 0;
   _pgsqlCurrentRow = 0;
   
   // Clear any previouse field information.
   _freeCollection(FIELD_INFORMATION);
 
   // Free the current field values
   _freeCollection(FIELD_VALUES);

   // Always make sure there is a transaction that the query is occuring in
   if (!_isTransaction)
      transBegin();
        
   // Resolve the bind parameters
   string resolvedSqlStatement = _pgsqlParseBindParameters(_sqlStatement);   
   
   // Ping the connection to make sure it is still valid.
   _parentConnection->_pgsqlPing(_index);

   // Execute the query
   __pgsql_res__ = _parentConnection->_pgsqlQuery(_index, resolvedSqlStatement);
   
   // Setup the result with the number of records and field names if any
   if (PQresultStatus(__pgsql_res__) == PGRES_COMMAND_OK)
   {
      // Executed query, but no possible data can be returned (Ie DDL statement)
      _fieldCount = 0;
      _eof = true;      
   }
   else
   {
      // We have data
      _fieldCount = PQnfields(__pgsql_res__);
      
      // Get the field information for this query.
      _pgsqlGetFieldsInformation();
            
      // Store the number of rows in the result set.
      _pgsqlNumRows = PQntuples(__pgsql_res__);
      
      if (!_pgsqlNumRows)
         _eof = true;     // No data in result set
      else
         _eof = false;
   }
   
   // If this was a function call, get the result and populate the result param
   if (_isFunctionQuery)
   {
      // The function result is in the special field _fnc_reslt_,
      //    which is the first and only field in the single row result set
      fetchNext();

      // Add the value to the parameters array.
      _numParameters++;
      _parameters = (PostgresqlBindParam**)realloc((void*)_parameters, _numParameters * sizeof(PostgresqlBindParam*));
      _parameters[_numParameters-1] = (PostgresqlBindParam*)_recordValues[0];      
      _recordValues[0] = NULL;      
      
      _eof = true;      

      // Clear the field information
      _freeCollection(FIELD_INFORMATION);
    
      // Free the field values
      _freeCollection(FIELD_VALUES);      
   }
}  // PostgresqlQuery::execute



//------------------------------------------------------------------------------
// PostgresqlQuery::next
//------------------------------------------------------------------------------
void
PostgresqlQuery::fetchNext()
{
   // Do nothing if the we have reached the end of file.
   if (_eof)
      return;
   
   // Get the data for the row
   _pgsqlGetResultSetRow();
   _pgsqlCurrentRow++;
   
   // Check if we are at the end of the result set.
   if (_pgsqlCurrentRow >= _pgsqlNumRows)
      _eof = true;   
}  // PostgresqlQuery::next


//------------------------------------------------------------------------------
// PostgresqlQuery::transBegin
//------------------------------------------------------------------------------
void
PostgresqlQuery::transBegin()
{
   // Make sure a transaction is not already active
   if (_isTransaction)
      throw TransactionError("transBegin(): "
         "A transaction is already active. Commit or rollback the transaction before creating a new transaction.");

   // Ping the connection to make sure it is still valid.
   _parentConnection->_pgsqlPing(_index);
   
   // Start the transaction
   PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, "BEGIN");      
   if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR)
   {         
      string err = "transBegin(): Unable to start transaction";
      if (r)
      {
         err += ", ";
         err += PQresultErrorMessage(r);
         PQclear(r);
      }
      throw TransactionError(err);
   }

   // The transaction has active
   PQclear(r);
   
	_isTransaction = true;
}  // PostgresqlQuery::transBegin
   
   
//------------------------------------------------------------------------------
// PostgresqlQuery::commit
//------------------------------------------------------------------------------
void
PostgresqlQuery::commit()
{
   // Make sure a transaction is active
   if (!_isTransaction)
      throw TransactionError("commit(): "
         "A transaction is not active. Create a transaction before calling commit.");

   // Ping the connection to make sure it is still valid.
   _parentConnection->_pgsqlPing(_index);
   
   // Commit the transaction
   PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, "COMMIT");      
   if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR)
   {         
      string err = "commit(): Unable to commit the transaction";
      if (r)
      {
         err += ", ";
         err += PQresultErrorMessage(r);
         PQclear(r);
      }
      throw TransactionError(err);
   }

   // The transaction has been commited
   PQclear(r);
   
	_isTransaction = false;
}  // PostgresqlQuery::commit
   
  
//------------------------------------------------------------------------------
// PostgresqlQuery::rollback
//------------------------------------------------------------------------------
void
PostgresqlQuery::rollback()
{
   // Make sure a transaction is active
   if (!_isTransaction)
      throw TransactionError("rollback(): "
         "A transaction is not active. Create a transaction before calling commit.");

   // Ping the connection to make sure it is still valid.
   _parentConnection->_pgsqlPing(_index);
   
   // Commit the transaction
   PGresult *r = PQexec(_parentConnection->_handles[_index]->__conn, "ROLLBACK");      
   if (!r || PQresultStatus(r) == PGRES_BAD_RESPONSE || PQresultStatus(r) == PGRES_FATAL_ERROR)
   {
      string err = "rollback(): Unable to rollback the transaction";
      if (r)
      {
         err += ", ";
         err += PQresultErrorMessage(r);
         PQclear(r);
      }
      throw TransactionError(err);
   }

   // The transaction has been rolled back
   PQclear(r);
   
	_isTransaction = false;   
}  // PostgresqlQuery::rollback



//------------------------------------------------------------------------------
// PostgresqlQuery::getFieldInfoByColumn
//------------------------------------------------------------------------------
BaseFieldDescription*
PostgresqlQuery::getFieldInfoByColumn(
   int index)
{
   // Make sure the index is in range.
   if (index < 0 || index >= _numFieldInformation)
      throw IndexOutOfRange("getFieldInfoByColumn(): The field index is out of range for the current result set");

   return _fieldInformation[index];   
}  // PostgresqlQuery::getFieldInfoByColumn


//------------------------------------------------------------------------------
// PostgresqlQuery::getFieldInfoByName
//------------------------------------------------------------------------------
BaseFieldDescription*
PostgresqlQuery::getFieldInfoByName(
   const string& fieldName)
{
   // Try and find the field name
   for (int i=0; i<_numFieldInformation; i++)
      if (strcasecmp(_fieldInformation[i]->name().c_str(), fieldName.c_str()) == 0)
         return _fieldInformation[i];

   throw NameNotFound("getFieldInfoByName(): The field name was not found for the current result set.");   
}  // PostgresqlQuery::getFieldInfoByName


//------------------------------------------------------------------------------
// PostgresqlQuery::getFieldByColumn
//------------------------------------------------------------------------------
BaseValue*
PostgresqlQuery::getFieldByColumn(
   int index)
{
   // Make sure the index is in range.
   if (index < 0 || index >= _numRecordValues)
   {
      throw IndexOutOfRange("getFieldByColumn(): The field index is out of range for the current result set");
   }

   return _recordValues[index];   
}  // PostgresqlQuery::getFieldByColumn
   

//------------------------------------------------------------------------------
// PostgresqlQuery::getFieldByName
//------------------------------------------------------------------------------
BaseValue*
PostgresqlQuery::getFieldByName(
   const string& fieldName)
{
   // Try and find the field name
   for (int i=0; i<_numRecordValues; i++)
      if (strcasecmp(_recordValues[i]->name().c_str(), fieldName.c_str()) == 0)
         return _recordValues[i];

   throw NameNotFound("getFieldByName(): The field name was not found for the current result set.");      
}  // PostgresqlQuery::getFieldByName