DbiResultSet.cxx

Go to the documentation of this file.

#include <sstream>

#include "DatabaseInterface/DbiFieldType.h"
#include "DatabaseInterface/DbiResultSet.h"
#include "DatabaseInterface/DbiString.h"
#include "DatabaseInterface/DbiStatement.h"
#include "DatabaseInterface/DbiTableMetaData.h"
#include "LeakChecker/Lea.h"
#include "MessageService/MsgService.h"
#include "Util/UtilString.h"
#include "Validity/VldTimeStamp.h"

ClassImp(DbiResultSet)


//   Definition of static data members
//   *********************************

CVSID("$Id: DbiResultSet.cxx,v 1.40 2007/07/05 11:36:05 west Exp $");

//    Definition of all member functions (static or otherwise)
//    *******************************************************
//
//    -  ordered: ctors, dtor, operators then in alphabetical order.


//.....................................................................

DbiResultSet::DbiResultSet(DbiStatement* stmtDb,
                           const DbiString& sql,
                           const DbiTableMetaData* metaData,
                           const DbiTableProxy* tableProxy,
                           UInt_t dbNo,
                           const string& fillOpts) :
DbiRowStream(metaData),
fCurRow(0),
fDbNo(dbNo),
fDbType(Dbi::kMySQL),
fStatement(stmtDb),
fTSQLStatement(0),
fExhausted(true),
fTableProxy(tableProxy),
fFillOpts(fillOpts)
{
//
//
//  Purpose:  Default constructor
//
//  Arguments:
//     stmtDb     in  DbiStatement to be used for query.  May be zero.
//     sql        in  The query to be applied to the statement.
//     metaData   in  Meta data for query.
//     tableProxy in  Source DbiTableProxy.
//     dbNo       in  Cascade no. of source.
//
//  Return:    n/a
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  o  Create ResultSet for query.


//  Program Notes:-
//  =============

//  None.

  LEA_CTOR    //Leak Checker

  MSG("Dbi", Msg::kVerbose) << "Creating DbiResultSet" << endl;

  if ( stmtDb ) { 
    fDbType = stmtDb->GetDBType();
    fTSQLStatement = stmtDb->ExecuteQuery(sql.c_str());
    if ( fTSQLStatement && fTSQLStatement->NextResultRow() ) fExhausted = false;
    stmtDb->PrintExceptions(Msg::kDebug);
  }
  
}


//.....................................................................

DbiResultSet::~DbiResultSet() {
//
//
//  Purpose: Destructor
//
//  Arguments: 
//    None.
//
//  Return:    n/a
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  o  Destroy ResultSet and owned DbiStatement if any.


//  Program Notes:-
//  =============

//  None.

  LEA_DTOR    //Leak Checker

  MSG("Dbi", Msg::kVerbose) << "Destroying DbiResultSet" << endl;
  delete fTSQLStatement;
  fTSQLStatement = 0;
  delete fStatement;
  fStatement = 0;

}

//.....................................................................


#define IN(t) istringstream in(AsString(t)); in  

// On first row use AsString to force type checking.  
// On subsequent rows use binary interface for speed.
// Caution: Column numbering in TSQLStatement starts at 0.                    
#define IN2(t,m)                            \
  int col = CurColNum()-1;                  \
  if ( CurRowNum() == 0 ) {                 \
    istringstream in(AsString(t));          \
    in >> dest;                             \
  }                                         \
  else {                                    \
    dest = fTSQLStatement->m(col);          \
    IncrementCurCol();                      \
  }                                         \

// Handling reading of unsigned application data stored as signed database data. 
// Both GetInt(int) and GetString(int) return the signed data correctly.
// So first read into signed equivalent, then copy and finally
// trim off leading extended sign bits beyond the capacity of
// the database column.
// For BIGINT (size 8) make an exception.  It's used only as
// an alternative to unsigned int and getUInt(int) (but not GetInt(int))
// returns it correctly so can load directly into destination
// Caution: Column numbering in TSQLStatement starts at 0.                    
#define IN3(t)                                                      \
int col = this->CurColNum()-1;                                      \
const DbiFieldType& fType = this->ColFieldType(col+1);              \
if ( fType.GetSize() == 8 ) {                                       \
  dest=fTSQLStatement->GetUInt(col);                                \
}                                                                   \
else {                                                              \
  t dest_signed;                                                    \
  *this >> dest_signed;                                             \
  dest = dest_signed;                                               \
  if ( fType.GetSize() == 1 ) dest &= 0xff;                         \
  if ( fType.GetSize() == 2 ) dest &= 0xffff;                       \
  if ( fType.GetSize() == 4 ) dest &= 0xffffffff;                   \
}\

DbiResultSet& DbiResultSet::operator>>(Bool_t& dest) {
                                 IN(Dbi::kBool) >> dest;  return *this;}
DbiResultSet& DbiResultSet::operator>>(Char_t& dest) {
                                 IN(Dbi::kChar) >> dest; return *this;}
DbiResultSet& DbiResultSet::operator>>(Short_t& dest) {
                                 IN2(Dbi::kInt,GetInt);    return *this;}
DbiResultSet& DbiResultSet::operator>>(UShort_t& dest) {
                                 IN3(Short_t); return *this;}
DbiResultSet& DbiResultSet::operator>>(Int_t& dest) {
                                 IN2(Dbi::kInt,GetInt);      return *this;}
DbiResultSet& DbiResultSet::operator>>(UInt_t& dest) {
                                 IN3(Int_t);  return *this;}
DbiResultSet& DbiResultSet::operator>>(Long_t& dest) {
                                 IN2(Dbi::kLong, GetLong);   return *this;}
DbiResultSet& DbiResultSet::operator>>(ULong_t& dest) {
                                 IN3(Long_t);  return *this;}
DbiResultSet& DbiResultSet::operator>>(Float_t& dest) {
                                 IN2(Dbi::kFloat,GetDouble);  return *this;}
DbiResultSet& DbiResultSet::operator>>(Double_t& dest) {
                                 IN2(Dbi::kDouble,GetDouble);return *this;}

// Also use AsString() for string and VldTimeStamp; conversion to string
// is needed in any case.
DbiResultSet& DbiResultSet::operator>>(string& dest) {
                          dest = AsString(Dbi::kString);  return *this;}
DbiResultSet& DbiResultSet::operator>>(VldTimeStamp& dest){
           dest=Dbi::MakeTimeStamp(AsString(Dbi::kDate)); return *this;}

//.....................................................................

string& DbiResultSet::AsString(Dbi::DataTypes type) {
//
//
//  Purpose: Return current column value as a modifiable string and 
//           move on. 
//
//  Arguments: 
//    type         in    Required data type (as defined in Dbi.h).
//
//  Return:   Current column value as a string (null if missing) 
//            Note: Caller must dispose of value before calling
//                  this member function again as the value is
//                  assembled into fValString.
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
// o Return the datum at current (row,column) as a string and
//   increment column number.
//
// o Check for compatibility between required data type and table
//   data type, report problems and return default if incompatible.

//  Program Notes:-
//  =============

//  None.

  DbiFieldType  reqdt(type);

//  Place table value string in value string buffer.

  Bool_t fail = ! LoadCurValue();
// Internally columns number from zero.
  UInt_t col = CurColNum();
  IncrementCurCol();

  if ( fail ) {
    string udef = reqdt.UndefinedValue();
    MAXMSG("Dbi",Msg::kError,20) 
       << "... value \"" << udef
       << "\" will be substitued." <<  endl;
    fValString = udef;
    return fValString;
  }
 
//  Check for compatibility with required data type.

  const DbiFieldType& actdt = MetaData()->ColFieldType(col);

  if ( reqdt.IsCompatible(actdt) ) {
    Bool_t smaller = reqdt.IsSmaller(actdt);
//  Allow one character String to be stored in Char
    if ( reqdt.GetConcept() == Dbi::kChar && fValString.size() == 1 
       ) smaller = kFALSE;
    if ( smaller  ) {
       MAXMSG("Dbi",Msg::kWarning,20) 
          << "In table " << TableNameTc() 
          << " row " << fCurRow 
          << " column "<< col 
          << " (" << MetaData()->ColName(col) << ")"
          << " value \"" << fValString
          << "\" of type " << actdt.AsString()
          << " may be truncated before storing in " << reqdt.AsString()
          <<  endl;
    }
  }
  else {
    string udef = reqdt.UndefinedValue();
    MAXMSG("Dbi",Msg::kError,20) 
        << "In table " << TableNameTc() 
         << " row " << fCurRow
        << " column "<< col 
        << " (" << MetaData()->ColName(col) << ")"
        << " value \"" << fValString
        << "\" of type " << actdt.AsString()
        << " is incompatible with user type " << reqdt.AsString()
        << ", value \"" << udef
        << "\" will be substituted." <<  endl;
    fValString = udef;
  }

  return fValString;
}


//.....................................................................

Bool_t DbiResultSet::CurColExists() const {
//
//
//  Purpose:  Test if current column exists.
//
//  Arguments: None.
//
//  Return:    kTRUE if column exists.
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  o  Test if current column exists.

//  Program Notes:-
//  =============

//  None.

  Int_t col = CurColNum();

  if ( IsExhausted() ) {
    MAXMSG("Dbi",Msg::kError,20) 
      << "In table " << TableNameTc() 
      << " attempting to access row " << fCurRow  
      << " column " << col 
      << " but only " << fCurRow << " rows in table."  << endl;
    return kFALSE;
  }

  int numCols = NumCols();
  if ( col > numCols ) {
    MAXMSG("Dbi",Msg::kError,20) 
      << "In table " << TableNameTc() 
      << " row " << fCurRow 
      << " attempting to access column "<< col 
      << " but only " << NumCols() << " in table ."  << endl;
    return kFALSE;
  }

  return kTRUE;

}
//.....................................................................

string DbiResultSet::CurColString() const {
//
//
//  Purpose:  Return current column as a string.
//
//  Arguments: None.
//
//  Return:   Current column as a string (or null string if non-existant).
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  o Return current column as a string.

//  Program Notes:-
//  =============

//  None.

  if ( ! CurColExists() ) return "";

  TString valStr = this->GetStringFromTSQL(CurColNum());
  return valStr.Data();

}

//.....................................................................

Bool_t DbiResultSet::FetchRow() {

//
//
//  Purpose: Fetch next row of result set.. 
//
//  Arguments: None.
//
//  Return:   kTRUE if row exists, kFALSE otherwise.
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  o Load next row with string lengths.

//  Program Notes:-
//  =============

//  None.

  ClearCurCol();
  if ( IsExhausted() ) return kFALSE;
  ++fCurRow;
  if ( ! fTSQLStatement->NextResultRow() ) fExhausted = true;
  return ! fExhausted;

}
//.....................................................................

TString DbiResultSet::GetStringFromTSQL(Int_t col) const {
//
//
//  Purpose: Get string from underlying TSQL interface 
//
//  N.B.  No check that col is valid - caller beware. 
//
//  Specification:-
//  =============
//
//  o Get string from underlying TSQL interface converting  supported 
//    binary characters for ORACLE text fields (ORACLE doesn't convert 
//    external representation to binary on input).
//

// Caution: Column numbering in TSQLStatement starts at 0.                    
  TString valStr = fTSQLStatement->GetString(col-1);
  if (    this->GetDBType() == Dbi::kOracle
       && this->ColFieldType(col).GetConcept() == Dbi::kString ) {
    MSG("Dbi",Msg::kDebug) << "ORACLE string conversion from: " << valStr << endl;
    valStr.ReplaceAll("\\n", "\n");
    valStr.ReplaceAll("\\t", "\t");
    valStr.ReplaceAll("\\\'","\'");
    valStr.ReplaceAll("\\\"","\"");
    valStr.ReplaceAll("\\\\","\\");
    MSG("Dbi",Msg::kDebug) << "                           to: " << valStr <<endl;
  }
  return valStr;
}

//.....................................................................

Bool_t DbiResultSet::LoadCurValue() const{
//
//
//  Purpose:  Load current value into buffer fValString
//
//  Arguments: None.
//
//  Return:    kTRUE if current column in range, otherwise kFALSE.
//
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  o Load current value into buffer fValString stripping off any
//    enclosing quotes.

  
  fValString.clear();

  if ( ! CurColExists() ) return kFALSE;

  Int_t col = CurColNum();
  TString valStr = this->GetStringFromTSQL(col);

  // For floating point, use binary interface to preserve precision 
  // e.g.-1.234567890123457e-100 as string is -0.000000
  if ( CurColFieldType().GetConcept() == Dbi::kFloat ) {
    ostringstream out;
    out << setprecision(8);
    if ( CurColFieldType().GetType() == Dbi::kDouble )  out << setprecision(16);
//  Caution: Column numbering in TSQLStatement starts at 0.                    
    out << fTSQLStatement->GetDouble(col-1);
    valStr = out.str().c_str();
  }
  int len = valStr.Length();



  const char* pVal = valStr.Data();
  // Remove leading and trailing quotes if dealing with a string.
  if (    len >= 2
       && ( *pVal == *(pVal+len-1) )  
       && ( *pVal == '\'' || *pVal == '"' ) ) {
    ++pVal;
    len -= 2;
  }
  fValString.assign(pVal,len);

  return kTRUE;

}
//.....................................................................

void DbiResultSet::RowAsCsv(string& row) const {
//
//
//  Purpose:  Append row as a Comma Separated Values string.
//
//  Arguments: 
//    row          in    String to append to.
//

  const DbiTableMetaData* md = this->MetaData();

  Int_t maxCol = this->NumCols();
  for (Int_t col = 1; col <= maxCol; ++col) {
    // Deal with NULL values.  Caution: Column numbering in TSQLStatement starts at 0.
    if ( fTSQLStatement->IsNull(col-1) ) {
      row += "NULL";
      if ( col < maxCol ) row += ',';
      continue;
    }
    Bool_t mustDelimit  = md->ColMustDelimit(col);
    UInt_t concept      = md->ColFieldConcept(col);
    if ( mustDelimit ) row += '\'';
    TString str = this->GetStringFromTSQL(col);
    const char* value = str.Data();

    // Make strings printable. 
    if ( concept == Dbi::kString ) UtilString::MakePrintable(value,row);

    // For floating point, use binary interface to preserve precision 
    // e.g.-1.234567890123457e-100 as string is -0.000000
    else if ( concept == Dbi::kFloat ) {
      ostringstream out;
      out << setprecision(8);
      if ( md->ColFieldType(col).GetType() == Dbi::kDouble ) out << setprecision(16);
      out << fTSQLStatement->GetDouble(col-1);
      row += out.str();
    }

    // Everything else (!) is O.K.
    else                        row += value;

    if ( mustDelimit ) row += '\'';
    if ( col < maxCol ) row += ',';
  }
}

---