ibmdb2query.cpp

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

   // Call the base query to store a copy of the query.
   BaseQuery::command(newSqlStatement);
} // DB2Query::command


//------------------------------------------------------------------------------
// DB2Query::bindParam
//------------------------------------------------------------------------------
BaseValue*
DB2Query::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 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 = (DB2BindParam**)realloc((void*)_parameters, _numParameters * sizeof(DB2BindParam*));
   _parameters[_numParameters-1] = new DB2BindParam(paramName);
   return _parameters[_numParameters-1];
}  // DB2Query::bindParam


//------------------------------------------------------------------------------
// DB2Query::execute
//------------------------------------------------------------------------------
void
DB2Query::execute()
{
   // Holds pointers for the bind values (left to right alinged to the ?'s that will be substituted)
   vector<DB2BindParam*> bindParameters;   
   
   SQLRETURN cliRC = SQL_SUCCESS;
   
   //Make sure all DB2 resources are released
   if (_hstmt != SQL_NULL_HSTMT)
   {
      SQLFreeHandle(SQL_HANDLE_STMT, _hstmt);
      _hstmt = SQL_NULL_HSTMT;
   }
   _queryParameterNames.clear();
   
   // Number of rows in the result set is now 0
   _db2CurrentRow = 0;
   
   // Clear any previouse field information.
   _freeCollection(FIELD_INFORMATION);
 
   // Free the current field values
   _freeCollection(FIELD_VALUES);

   
   // Replace all the bind parameters in the SQL with '?' characters and build name list for correct binding
   string resolvedSqlStatement = _db2ParseBindParameters(_sqlStatement, bindParameters);
   
   // Ping the connection to make sure it is still valid.
   _parentConnection->_db2Ping(_index);
      
   // Allocate the statement handle
   cliRC = SQLAllocHandle(SQL_HANDLE_STMT, _parentConnection->_handles[_index]->_hdbc, &_hstmt);
   if (cliRC != SQL_SUCCESS)
      throw ErrorQuerying("execute(): Unable to allocate the statement handle.");
   
   // Prepare the statement 
   cliRC = SQLPrepare(_hstmt, (SQLCHAR*)resolvedSqlStatement.c_str(), SQL_NTS);
   if (cliRC != SQL_SUCCESS)
   {
      SQLCHAR sqlState[10];
      SQLINTEGER sqlCode;
      SQLCHAR message[255];
      SQLSMALLINT length;
      
      SQLGetDiagRec(SQL_HANDLE_STMT, _hstmt, 1, sqlState, &sqlCode, message, 250, &length);
      
      string err = "execute(): ";
      err += (const char*)message;           
      throw ErrorQuerying(err);
   }
     
   // Always make sure there is a transaction that the query is occuring in
   // Call this after the SQLPrepare as SQLPrepare will create a transaction
   if (!_isTransaction)
      transBegin();

   // Bind the parameters
   _db2BindParameters(resolvedSqlStatement, bindParameters);
   
   // Execute the query
   cliRC = SQLExecute(_hstmt);
   // Make sure it executed correctly
   if (cliRC != SQL_SUCCESS)
   {
      SQLCHAR sqlState[10];
      SQLINTEGER sqlCode;
      SQLCHAR message[255];
      SQLSMALLINT length;
      
      SQLGetDiagRec(SQL_HANDLE_STMT, _hstmt, 1, sqlState, &sqlCode, message, 250, &length);
      
      string err = "execute(): ";      
      err += (const char*)message;
           
      throw ErrorQuerying(err);
   }

   // Check if there is a result set
   SQLSMALLINT nResultCols;
   cliRC = SQLNumResultCols(_hstmt, &nResultCols);
   if (cliRC != SQL_SUCCESS)
   {
      SQLCHAR sqlState[10];
      SQLINTEGER sqlCode;
      SQLCHAR message[255];
      SQLSMALLINT length;
      
      SQLGetDiagRec(SQL_HANDLE_STMT, _hstmt, 1, sqlState, &sqlCode, message, 250, &length);
      
      string err = "execute(): ";      
      err += (const char*)message;
           
      throw ErrorQuerying(err);
   }


   // Handle the result set comming back
   if (nResultCols)
   {
      // We have a result set with data      
      _fieldCount = nResultCols;

      // Get the field information for this query.
      _db2GetFieldsInformation();

      _eof = false;
      
      // Pre-fetch the first row making sure there is data in the result set
      cliRC = SQLFetch(_hstmt);
      if (cliRC == SQL_NO_DATA_FOUND)
      {
         _eof = true;
      }
      else if (cliRC != SQL_SUCCESS)
      {
         SQLCHAR sqlState[10];
         SQLINTEGER sqlCode;
         SQLCHAR message[255];
         SQLSMALLINT length;
      
         SQLGetDiagRec(SQL_HANDLE_STMT, _hstmt, 1, sqlState, &sqlCode, message, 250, &length);
      
         string err = "execute(): ";      
         err += (const char*)message;
           
         throw ResultSetError(err);
      }            
   }
   else
   {
      // We have no records, could be an update or something
      _fieldCount = 0;
      _eof = true;            
   }
}  // DB2Query::execute


//------------------------------------------------------------------------------
// DB2Query::next
//------------------------------------------------------------------------------
void
DB2Query::fetchNext()
{
   // Do nothing if the we have reached the end of the record set.
   if (_eof)
      return;
   
   // Get the data. _db2GetResultSetRow will set the _eof parameter 
   _db2GetResultSetRow();
   _db2CurrentRow++;   
}  // DB2Query::next


//------------------------------------------------------------------------------
// DB2Query::transBegin
//------------------------------------------------------------------------------
void
DB2Query::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.");

	_isTransaction = true;
   
   /*
    * A DB2 transaction is automatically started when a SQLPrepare, SQLExecDirect or SQLGetTypeInfo call is made.
    * Hence a transaction is implied, and does not need to be explicilty created.      
    * This function should remain as a token function with basic error checking, Johnathan Ingram
    *
    */      
}  // DB2Query::transBegin
   
   
//------------------------------------------------------------------------------
// DB2Query::commit
//------------------------------------------------------------------------------
void
DB2Query::commit()
{
   // Make sure a transaction is active
   if (!_isTransaction)
      throw TransactionError("commit(): "
         "A transaction is not active. Create a transaction before calling commit.");
   
   // Commit the transaction
   SQLRETURN cliRC = SQL_SUCCESS;
   cliRC = SQLEndTran(SQL_HANDLE_DBC, _parentConnection->_handles[_index]->_hdbc, SQL_COMMIT);
   
   // Check if an error occured while commiting
   if (cliRC != SQL_SUCCESS)
   {
      SQLCHAR sqlState[10];
      SQLINTEGER sqlCode;
      SQLCHAR message[255];
      SQLSMALLINT length;
      
      SQLGetDiagRec(SQL_HANDLE_DBC, _parentConnection->_handles[_index]->_hdbc, 1, sqlState, &sqlCode, message, 250, &length);
      
      string err = "commit(): ";      
      err += (const char*)message;
           
      throw TransactionError(err);
   }
   
	_isTransaction = false;   
}  // DB2Query::commit
   
  
//------------------------------------------------------------------------------
// DB2Query::rollback
//------------------------------------------------------------------------------
void
DB2Query::rollback()
{
   // Make sure a transaction is active
   if (!_isTransaction)
      throw TransactionError("rollback(): "
         "A transaction is not active. Create a transaction before calling rollback.");

   // Roolback the transaction
   SQLRETURN cliRC;
   cliRC = SQLEndTran(SQL_HANDLE_DBC, _parentConnection->_handles[_index]->_hdbc, SQL_ROLLBACK);
   
   // Check if an error occured while rolling back
   if (cliRC != SQL_SUCCESS)
   {
      SQLCHAR sqlState[10];
      SQLINTEGER sqlCode;
      SQLCHAR message[255];
      SQLSMALLINT length;
      
      SQLGetDiagRec(SQL_HANDLE_DBC, _parentConnection->_handles[_index]->_hdbc, 1, sqlState, &sqlCode, message, 250, &length);
      
      string err = "rollback(): ";      
      err += (const char*)message;
           
      throw TransactionError(err);
   }
   
	_isTransaction = false;      
}  // DB2Query::rollback


//------------------------------------------------------------------------------
// DB2Query::getFieldInfoByColumn
//------------------------------------------------------------------------------
BaseFieldDescription*
DB2Query::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];      
}  // DB2Query::getFieldInfoByColumn


//------------------------------------------------------------------------------
// DB2Query::getFieldInfoByName
//------------------------------------------------------------------------------
BaseFieldDescription*
DB2Query::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.");   
}  // DB2Query::getFieldInfoByName


//------------------------------------------------------------------------------
// DB2Query::getFieldByColumn
//------------------------------------------------------------------------------
BaseValue*
DB2Query::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];      
}  // DB2Query::getFieldByColumn
   

//------------------------------------------------------------------------------
// DB2Query::getFieldByName
//------------------------------------------------------------------------------
BaseValue*
DB2Query::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.");   
}  // DB2Query::getFieldByName