ibmdb2query.cpp

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

   //   res = FT_BLOB;
   //   break;      
   
   switch(type)
   {      
      case SQL_BIGINT:
         res = FT_LONG;
         break;
      case SQL_BLOB:
         res = FT_BLOB;
         break;
      case SQL_CHAR:
         res = FT_STRING;
         break;
      case SQL_TINYINT:
         res = FT_LONG;
         break;
      case SQL_BINARY:
         res = FT_BLOB;
         break;
      case SQL_CLOB:
         res = FT_STRING;
         break;
      case SQL_TYPE_DATE:
         res = FT_DATETIME;
         break;
      case SQL_DECIMAL:
         res = FT_DOUBLE;
         break;
      case SQL_DOUBLE:
         res = FT_DOUBLE;
         break;
      case SQL_FLOAT:
         res = FT_DOUBLE;
         break;
      case SQL_INTEGER:
         res = FT_LONG;
         break;
      case SQL_NUMERIC:
         res = FT_DOUBLE;
         break;
      case SQL_REAL:
         res = FT_DOUBLE;
         break;
      case SQL_SMALLINT:
         res = FT_SHORT;
         break;
      case SQL_TYPE_TIME:
         res = FT_DATETIME;
         break;
      case SQL_TYPE_TIMESTAMP:
         res = FT_DATETIME;
         break;
      case SQL_VARCHAR:
         res = FT_STRING;
         break;
      case SQL_GRAPHIC:
         res = FT_STRING;
         break;      
      case SQL_VARGRAPHIC:
         res = FT_STRING;
         break;      
      default:
         res = FT_UNKNOWN;
         break;      
   }
   
   return res;
}  // DB2Query::_db2ResolveFieldType


//------------------------------------------------------------------------------
// DB2Query::_db2ParseBindParameters
//------------------------------------------------------------------------------
string
DB2Query::_db2ParseBindParameters(
      const string& originalSqlStatement,
      vector<DB2BindParam*> &bindParameters)
{
   // Replace the ":name" parameters with a ? from left to right building the bindParameter vector to point to the correct values
   // Try and substitute the parameters checking to make sure they exist.
   string res = originalSqlStatement;    
   int pos, endPos;
   char paramName[1024];
   
   pos = res.find(":");
   while (pos != string::npos)
   {
      memset(paramName, 0, sizeof(paramName));
      
      // Get the position at the end of the parameter name and determine the parameter name
      endPos = pos +1;
      char tstChar = res.c_str()[endPos];
      while ((tstChar >= '0' && tstChar <= '9') ||    // 0 - 9
             (tstChar >= 'A' && tstChar <= 'Z') ||    // A - Z
             (tstChar >= 'a' && tstChar <= 'z')||     // a - z
             (tstChar == '_') )                       // _              
      {
         endPos++;         
         if (endPos > res.length())
            throw BindParameterError("_db2ParseBindParameters(): Unable to properly parse the bind parameters");
         
         tstChar = res.c_str()[endPos];
      }
            
      char *paramNameStart = (char*)((int)res.c_str() + pos+1);   // Jump the : in the param name
      
      strncpy(paramName, paramNameStart, endPos - pos-1);
      // Replace the parameter with a ?      
      res.replace(pos, endPos-pos, "?");

      // Make sure the parameter name exists and add the value to the parameter vector
      bool found = false;
      for (int i =0; i<_numParameters; i++)
      {
         if (strcasecmp(_parameters[i]->name().c_str(), paramName) == 0)
         {
            found = true;
            bindParameters.push_back(_parameters[i]);
            break;
         }
      }
      
      if (!found)
      {
         string err = "_db2ParseBindParameters(): The bind parameter, ";
         err += paramName;
         err += ", in the SQL statement: ";
         err += originalSqlStatement;
         err += " has not been set.";
         throw BindParameterNotSet(err);
      }

      // Find the next parameter and continue
      pos = res.find(":");
   }
   
   return res;      
}  // DB2Query::_db2ParseBindParameters
   

//------------------------------------------------------------------------------
// DB2Query::_db2BindParameters
//------------------------------------------------------------------------------
void
DB2Query::_db2BindParameters(
      const string& resolvedSqlStatement,
      vector<DB2BindParam*> &bindParameters)
{
   SQLRETURN cliRC = SQL_SUCCESS;
   
   // Iterate through the pointer list and bind the parameter values according to type from left to right
   for (int i=0; i<bindParameters.size(); i++)
   {
      // Get the required parameter information      
      SQLSMALLINT  paramSQLType;
      SQLUINTEGER  columnSize;
      SQLSMALLINT  decDigits;
      SQLSMALLINT  nullableInfo;
      
      cliRC = SQLDescribeParam(_hstmt, i+1, &paramSQLType, &columnSize, &decDigits, &nullableInfo);
      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 = "_db2BindParameters(): ";
         err += (const char*)message;              
         throw BindParameterError(err);
      }
      
      
      cliRC = SQLBindParameter(_hstmt, i+1, SQL_PARAM_INPUT, bindParameters[i]->getDB2CType(),
                               paramSQLType, columnSize, decDigits, bindParameters[i]->getDB2DataPointer(),
                               bindParameters[i]->getDB2DataPointerLength(), bindParameters[i]->getDB2StrLenIndPtr() );        
      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 = "_db2BindParameters(): ";
         err += (const char*)message;              
         throw BindParameterError(err);
      }
   }
}  // DB2Query::_db2BindParameters


//------------------------------------------------------------------------------
// DB2Query::_freeCollection
//------------------------------------------------------------------------------
void 
DB2Query::_freeCollection(
   CollectionType type)
{
   int i;
   switch (type)
   {
      case FIELD_INFORMATION:
         if (_fieldInformation)
         {
            for (i=0; i<_numFieldInformation; i++)
            {
               if (_fieldInformation[i])
               {
                  delete _fieldInformation[i];
                  _fieldInformation[i] = NULL;         
               }
            }
            free(_fieldInformation);
            _fieldInformation = NULL;
            _numFieldInformation = 0;
            _fieldCount = 0;
         }
      
         break;      
      
      case FIELD_VALUES:
         if (_recordValues)
         {
            for (i=0; i<_numRecordValues; i++)
            {
               if (_recordValues[i])
               {
                  delete _recordValues[i];
                  _recordValues[i] = NULL;         
               }
            }
            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;
   }
}  // DB2Query::_freeCollection


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


//------------------------------------------------------------------------------
// DB2Query::DB2Query
//------------------------------------------------------------------------------
DB2Query::DB2Query(
   DB2Connection* parentConnection,
   int                   index)
: 
   _parentConnection(parentConnection),
   _index(index),
   _isTransaction(false),
   _hstmt(SQL_NULL_HSTMT),
   _db2CurrentRow(0),
   _recordValues(NULL),
   _numRecordValues(0),
   _fieldInformation(NULL),
   _numFieldInformation(0),
   _parameters(NULL),
   _numParameters(0)
{

}  // DB2Query::DB2Query


//------------------------------------------------------------------------------
// DB2Query::~DB2Query
//------------------------------------------------------------------------------
DB2Query::~DB2Query()
{   
   // If there is still a transaction, rollback
   if (_isTransaction)
      rollback();
   
   //Make sure all DB2 resources are released
   if (_hstmt != SQL_NULL_HSTMT)
   {
      SQLFreeHandle(SQL_HANDLE_STMT, _hstmt);
      _hstmt = SQL_NULL_HSTMT;
   }

   // 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);
}  // DB2Query::~DB2Query


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


//------------------------------------------------------------------------------
// DB2Query::command
//------------------------------------------------------------------------------
void
DB2Query::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);
   string newSqlStatement = sqlStatement;
   // TODO: Add procedure calling "CALL XYZ(?,?)"