ibmdb2query.cpp

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

/*
 * DB2Query object defines the needed query functions for the dbConnect IBM DB2 driver
 * Copyright (C) 2003 Johnathan Ingram, [e-mail]
 *
 * This library 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 2.1 of the License, or (at your option) any later version.
 *
 *   This library 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 this library; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  US
 *
 */


#include "ibmDB2Query.h"

// -----------------------------------------------------------------------------
// PRIVATE:
// -----------------------------------------------------------------------------


//------------------------------------------------------------------------------
// DB2Query::_db2GetFieldsInformation
//------------------------------------------------------------------------------
void 
DB2Query::_db2GetFieldsInformation()
{
   // Clear any previouse field information just incase.
   _freeCollection(FIELD_INFORMATION);

   // Internal method used to build the field information.
   
   // Allocate the information pointers
   _numFieldInformation = _fieldCount;
   _fieldInformation = (DB2FieldDescription**)malloc(_numFieldInformation * sizeof(DB2FieldDescription*));
   
   // Assign the data to the corresponding fields.
   for (int i=0; i<_numFieldInformation; i++)
   {
      // Get the field information
      SQLRETURN cliRC;
      SQLCHAR colName[256];
      SQLSMALLINT colNameLen;
      SQLSMALLINT colType;
      SQLUINTEGER colSize;
      SQLSMALLINT colScale;
      SQLSMALLINT colPrecision;
      SQLSMALLINT colNullable;
      SQLSMALLINT colAutoUnique;
               
      // Obtain the required information
      cliRC = SQLDescribeCol(_hstmt, (SQLSMALLINT)i+1,
                             colName, sizeof(colName)-1, &colNameLen,
                             &colType, &colSize, &colScale, &colNullable);

      if (cliRC == SQL_SUCCESS)
         cliRC = SQLColAttribute(_hstmt, (SQLSMALLINT)i+1, SQL_DESC_PRECISION, NULL, 0, NULL, &colPrecision);

      if (cliRC == SQL_SUCCESS)
         cliRC = SQLColAttribute(_hstmt, (SQLSMALLINT)i+1, SQL_DESC_AUTO_UNIQUE_VALUE, NULL, 0, NULL, &colAutoUnique);
            
      // Finally bail on an error      
      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 = "_db2GetFieldsInformation(): ";      
         err += (const char*)message;
           
         throw ErrorQuerying(err);
      }
                          
      // Work out the field type.
      FieldType _type = _db2ResolveFieldType(colType);
      
      // Field Properties.      
      string fName = (const char*)colName;
      
      long int  _scale = colScale;
      
      long int  _precision = 0;
      if (colType == SQL_DECIMAL || colType == SQL_NUMERIC || colType == SQL_DOUBLE ||
          colType == SQL_FLOAT || colType == SQL_INTEGER || colType == SQL_REAL || colType == SQL_SMALLINT)
         _precision = colPrecision;

      bool     _isIncrement = (colAutoUnique == SQL_FALSE ? false : true);  // Will always return false. Future versions of CLI will support this.
      bool     _isPriKey    = false;    // Not supported 
      bool     _isUnique    = false;    // Not supported 
      bool     _isNotNull   = (colNullable == SQL_NULLABLE ? false : true);
     
      // Add the field properties to the vector.
      _fieldInformation[i] = new DB2FieldDescription(fName, i, _type, _isIncrement, _isPriKey, _isUnique, 
                                                     _isNotNull, _precision, _scale, colType, colSize);
   }
}  // DB2Query::_db2GetFieldsInformation


//------------------------------------------------------------------------------
// DB2Query::_db2GetResultSetRow
//------------------------------------------------------------------------------
void
DB2Query::_db2GetResultSetRow()
{
   // NOTE: This function always assumes fetchNext has been called before the start   
   // Clear any previouse field information just incase.
   _freeCollection(FIELD_VALUES);
   

   // Allocate the value pointers
   _numRecordValues = _fieldCount;
   _recordValues = (DB2Value**)malloc(_numRecordValues * sizeof(DB2Value*));

   
   // Get the data :)  
   SQLRETURN cliRC; 
   for (int i=0; i<_fieldCount; i++)
   {
      // Allocate the field
      _recordValues[i] = new DB2Value(_fieldInformation[i]->name().c_str());
      
      // Get the field value according to the field type
      SQLINTEGER strlen_indptr, tmp_strlen_indptr;
      void *buffer = NULL;
      long  bufferSize = 0;
      void *bufferOffset = NULL;

      JDate dVal(0.0);   // Epoch
      
      switch(_fieldInformation[i]->db2FieldType())
      {         
         // Obtain the data as an 64bit integer value            
         case SQL_BIGINT:
         case SQL_TINYINT:
         case SQL_INTEGER:
         case SQL_SMALLINT:
            bufferSize = sizeof(DBLONG);
            buffer = malloc(bufferSize);
         
            cliRC = SQLGetData(_hstmt, i+1, SQL_C_SBIGINT, buffer, bufferSize, &strlen_indptr); 
            if (cliRC == SQL_SUCCESS)
            {
               if (strlen_indptr == SQL_NULL_DATA)
                  _recordValues[i]->setNULL();
               else               
                  _recordValues[i]->setLong(*(DBLONG*)buffer);
            }
            break;       
         
            
         // Obtain the data as an double floating point value            
         case SQL_DECIMAL:
         case SQL_DOUBLE:
         case SQL_FLOAT:
         case SQL_NUMERIC:
         case SQL_REAL:
            bufferSize = sizeof(double);
            buffer = malloc(bufferSize);
         
            cliRC = SQLGetData(_hstmt, i+1, SQL_C_DOUBLE, buffer, bufferSize, &strlen_indptr); 
            if (cliRC == SQL_SUCCESS)
            {
               if (strlen_indptr == SQL_NULL_DATA)
                  _recordValues[i]->setNULL();
               else               
                  _recordValues[i]->setFloat(*(double*)buffer);
            }
            break;       
         
            
         // Obtain the data as character data (Allocate enough for field size)
         case SQL_CHAR:
         case SQL_CLOB:
         case SQL_VARCHAR:
         case SQL_GRAPHIC:
         case SQL_VARGRAPHIC:
            bufferSize = _fieldInformation[i]->db2FieldSize() + 1;  // +1 for NULL terminating char
            buffer = malloc(bufferSize);
            memset(buffer, 0, bufferSize);
         
            cliRC = SQLGetData(_hstmt, i+1, SQL_C_CHAR, buffer, bufferSize, &strlen_indptr); 
            if (cliRC == SQL_SUCCESS)
            {
               if (strlen_indptr == SQL_NULL_DATA)
                  _recordValues[i]->setNULL();
               else               
                  _recordValues[i]->setString((char*)buffer);
            }
            break;                
           
            
         // Obtain the data as timstamp value
         case SQL_TYPE_DATE:
         case SQL_TYPE_TIME:
         case SQL_TYPE_TIMESTAMP:
            bufferSize = sizeof(TIMESTAMP_STRUCT);
            buffer = malloc(bufferSize);
         
            cliRC = SQLGetData(_hstmt, i+1, SQL_C_TYPE_TIMESTAMP, buffer, bufferSize, &strlen_indptr); 
            if (cliRC == SQL_SUCCESS)
            {
               if (strlen_indptr == SQL_NULL_DATA)
                  _recordValues[i]->setNULL();
               else               
               {
                  // Build a JDate from the components
                  dVal.setDate(((TIMESTAMP_STRUCT*)buffer)->year,
                               ((TIMESTAMP_STRUCT*)buffer)->month,
                               ((TIMESTAMP_STRUCT*)buffer)->day,
                               ((TIMESTAMP_STRUCT*)buffer)->hour,
                               ((TIMESTAMP_STRUCT*)buffer)->minute,
                               ((TIMESTAMP_STRUCT*)buffer)->second );
                  _recordValues[i]->setDateTime(dVal);
               }
            }
            break;
         

         // Obtain the data as binary value (Obtain the data block by block)
         case SQL_BLOB:
         case SQL_BINARY:
            bufferSize = FETCH_BLOCK_SIZE;
            buffer = malloc(bufferSize);
         
            cliRC = SQLGetData(_hstmt, i+1, SQL_C_BINARY, buffer, bufferSize, &strlen_indptr); 
            if (cliRC == SQL_SUCCESS || cliRC == SQL_SUCCESS_WITH_INFO)
            {
               if (strlen_indptr == SQL_NULL_DATA)
                  _recordValues[i]->setNULL();
               else
               {
                  // Get any left over data in a single query. Result is set to SQL_SUCCESS_WITH_INFO 
                  //   if there is still data left to be read
                  if (cliRC == SQL_SUCCESS_WITH_INFO)
                  {
                     // strlen_indptr now has the total size of the data in the column
                     tmp_strlen_indptr = strlen_indptr;
                     
                     buffer = realloc(buffer, tmp_strlen_indptr);
                     bufferOffset = (void*)((unsigned int)buffer + bufferSize);
                     
                     cliRC = SQLGetData(_hstmt, i+1, SQL_C_BINARY, bufferOffset, tmp_strlen_indptr - bufferSize, &strlen_indptr); 
                     if (cliRC != SQL_SUCCESS)
                        break;
                     
                     // Now finally set the buffer size
                     bufferSize = tmp_strlen_indptr;
                  }
                  else
                  {
                     // The buffer size is the number of bytes read
                     bufferSize = strlen_indptr;
                  }
                  if (cliRC == SQL_SUCCESS)
                     _recordValues[i]->setBinary(buffer, bufferSize);
               }
            }
            break;       
                     
            
         default:
            char buf[20];
            memset(buf, 0, sizeof(buf));
            snprintf(buf, sizeof(buf)-1, "%d", _fieldInformation[i]->db2FieldType());
            
            string err = "_db2GetResultSetRow(): Unknown DB2 SQL field type ";         
            err += buf;
            throw ResultSetError(err);
            break;
      }

      // Free the buffer
      if (buffer)
      {
         free(buffer);
         buffer = NULL;         
      }
      
      // Make sure fetching the field data was ok.      
      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 = "_db2GetResultSetRow(): Unable to obtain field data, ";
         err += (const char*)message;
           
         throw ResultSetError(err);
      }
   }
   
   
   // Pre-fetch the next row
   cliRC = SQLFetch(_hstmt);
   
   // Check if the previouse fetchNext fetched the last row   
   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 = "_db2GetResultSetRow(): ";      
      err += (const char*)message;
        
      throw ResultSetError(err);
   }   
}  // DB2Query::_db2GetResultSetRow


//------------------------------------------------------------------------------
// DB2Query::_db2ResolveFieldType
//------------------------------------------------------------------------------
FieldType
DB2Query::_db2ResolveFieldType(
   SQLSMALLINT type)
{
   FieldType res;
   
   // LONG types are not supported as they should be replaced by LOB types according to the DB2 documentation
   
   // TODO: Research and support the following types
   //       SQL_BIT, SQL_DATALINK
   //       SQL_DB_CLOB, SQL_DB_LOCATOR, SQL_WCHAR
   // TODO: Support VARBINARY
   //case SQL_VARBINARY: