TSQLResultSet.cxx

Go to the documentation of this file. Or view the newest version in sPHENIX GitHub for file TSQLResultSet.cxx

// $Id: TSQLResultSet.cxx,v 1.1.1.1 2004/02/18 20:58:02 dave Exp $
//*-- Author : Valeriy Onuchin 14/02/2000 
//

//
// A TSQLResultSet provides access to a table of data. A 
// TSQLResultSet object is usually generated by executing 
// TSQLStatement. 
//
// A TSQLResultSet maintains a cursor pointing to its current row 
// of data. Initially the cursor is positioned before the first
// row. The Next() method moves the cursor to the next row. 
//
// The GetXXX methods retrieve column values for the current row.
// You can retrieve values using either the index number of the 
// column or the name of the column. In general,using the column 
// index will be more efficient. Columns are numbered from 1. 
//
// For maximum portability, TSQLResultSet columns within each row
// should be read in left-to-right order and each column should be 
// read only once. 
//
// For the GetXXX methods, the driver attempts to convert the
// underlying data to the specified ROOT type and returns a suitable 
// value. See the specification for allowable mappings 
// from SQL types to ROOT types with the TSQLResultSet::GetXXX 
// methods. 
//
// Column names used as input to GetXXX methods are case
// insensitive. When performing a GetXXX using a column name, 
// if several columns have the same name, then the value of the 
// first matching column will be returned. The column name option 
// is designed to be used when column names are used in the SQL 
// query. For columns that are NOT explicitly named in the query, 
// it is best to use column numbers. If column names are used, there 
// is no way for the programmer to guarantee that they actually refer 
// to the intended columns. 
//
// A TSQLResultSet is automatically closed by the TSQLStatement that 
// generated it when that TSQLStatement is closed, re-executed, 
// or used  to retrieve the next result from a sequence of multiple 
// results. 
//
// The number, types and properties of a TSQLResultSet's columns are
// provided by the TSQLResulSetMetaData object returned by the 
// GetMetaData() method. 
//
// See also: 
//      TSQLStatement::ExecuteQuery(const TString&),
//      TSQLStatement::GetResultSet(), TSQLResultSetMetaData
//
//___________________________________________________________________
//
//       kCONCUR_READ_ONLY 
//
// The concurrency mode for a TSQLResultSet object that may 
// NOT be updated.
//___________________________________________________________________
//
//       kCONCUR_UPDATABLE 
//
//  The concurrency mode for a TSQLResultSet
//  object that may be updated.
//___________________________________________________________________
//
//       kFETCH_FORWARD 
//
// The rows in a result set will be
// processed in a forward direction; first-to-last.
//___________________________________________________________________
//
//       kFETCH_REVERSE 
//
// The rows in a result set will be
// processed in a reverse direction; last-to-first.
//___________________________________________________________________
//
//       kFETCH_UNKNOWN 
//
// The order in which rows in a result set
// will be processed is unknown.
//___________________________________________________________________
//
//       kTYPE_FORWARD_ONLY 
//
// The type for a TSQLResultSet object whose
// cursor may move only forward.
//___________________________________________________________________
//
//       kTYPE_SCROLL_INSENSITIVE 
//
// The type for a TSQLResultSet object that is
// scrollable but generally not sensitive to changes made by
// others.
//___________________________________________________________________
//
//       kTYPE_SCROLL_SENSITIVE 
// 
// The type for a TSQLResultSet object that is
// scrollable and generally sensitive to changes made by
// others.
//
 
#include <RDBC/TSQLResultSet.h>
#include <RDBC/TSQLResultSetMetaData.h>
#include <RDBC/TSQLStatement.h>
#include <TList.h>
#include <TTree.h>
#include <TBranch.h>
#include <ctype.h>
#include <time.h>
#include <iostream>
using std::cout;
using std::endl;
#include <stdlib.h>

ClassImpQ(TSQLResultSet)


//___________________________________________________________________
TSQLResultSet::TSQLResultSet(TSQLStatement* stmt,void* imp):TSQL(imp)
{
   // ctor
   
   fStatement = stmt;
   fRow = 0;
   fMetaData = 0;
}                             

//___________________________________________________________________
TSQLResultSet::~TSQLResultSet()
{
   // dtor

   fRow = 0;
   fMetaData = 0;
}

//___________________________________________________________________
Int_t TSQLResultSet::GetFieldCount()
{
   // Get number of fields(aka columns) in result.

   return fMetaData->GetColumnCount();
}

//___________________________________________________________________
const char* TSQLResultSet::GetFieldName(Int_t field)
{
   // Get name of specified field( aka column ).

   return (const char*)fMetaData->GetColumnName(field);
}

//___________________________________________________________________
void TSQLResultSet::Close(const Option_t * /* option */)
{
   // Releases this TSQLResultSet object's database and resources
   // immediately instead of waiting for this to happen when it is 
   // automatically closed. 
   //
   // Note: A TSQLResultSet is automatically closed by the 
   // statement that generated it when that statement 
   // is closed, re-executed, or is used to retrieve the next
   // result from a sequence of multiple results. A TSQLResultSet 
   // is also automatically closed when it is garbage collected.
   //
   //  Throws:
   //       TSQLException - if a database access error occurs
 
   fMetaData = 0;   
   fRow = 0;
   fImp = 0;
   Destroyed();
}

//___________________________________________________________________
TString GetValueFromOption(const TString& pattern,const TString& option)
{
   // internal use func.

   Ssiz_t t1,t2;    
   const char* tmp;

   TString stropt = option; 
   stropt.ReplaceAll(" ",""); // strip blanks
   stropt.ToLower();

   t1 = stropt.Index(pattern.Data());
   t1 = t2 = stropt.Index("=",t1) + 1;
   TString str = stropt(t1,1024); // 
   tmp = str.Data();
 
   for(int i=0;isdigit(tmp[i])!=0;i++) t2=i+1;
   str = stropt(t1,t2);
   return str; 
}

//___________________________________________________________________
void SaveAsCSV(TSQLResultSet* rs,const TString& filename)
{
   // Auxilary function for saving content of the result set 
   // in ASCI file in CSV format

   TString colname;
   TString tmpstr;
   TString str;
   FILE* fd;
   
   if( (fd=fopen(filename.Data(),"w")) == NULL ) {
      return;
   }

   TSQLResultSetMetaData* md = rs->GetMetaData();
   Int_t ncollumns = md->GetColumnCount();
   Int_t type;

   for( int i=1; i <= ncollumns; ++i ) {
      type = md->GetColumnType(i);
      colname = md->GetColumnName(i);
      
      switch( type ) { 
      case kCHAR: 
      case kVARCHAR:
        break;
      case kINTEGER:
         break;
      case kDATE:
      case kTIME:
      case kTIMESTAMP:
         break;      
      case kBIT:       
      case kTINYINT:
      case kSMALLINT:
         break;
      case kREAL:
         break;
      case kLONGVARCHAR:
      case kLONGVARBINARY:
      case kVARBINARY:
         break;  
      case kBIGINT:
      case kDECIMAL:
      case kNUMERIC:
      case kDOUBLE:
      case kFLOAT:
      default:
         break;      
      }  
   }

   // write data in CSV format

   while ( rs->Next() ) {
      str = "";   // clear

      for(int i=1; i<ncollumns+1; i++) {
         type = md->GetColumnType(i);  // slowdown?

         switch(type) {
            case kVARCHAR:
            case kLONGVARCHAR:
               tmpstr = rs->GetString(i);
//               if(!tmpstr.IsNull()) tmpstr = '\"' + tmpstr + '\"'; // put quotas around field
               break;
            case kLONGVARBINARY:
            case kVARBINARY:
               tmpstr="";
               break;
            default:
               tmpstr = rs->GetString(i);
               break;
         }

         str += tmpstr;
         if(i<ncollumns) str += ",";
      }
      str += "\n";
      fputs(str.Data(),fd);
   }
   fclose(fd);
}

//___________________________________________________________________
void TSQLResultSet::Print(Option_t *option) const
{
   // Print a resultset contents
   //
   // The following options are valid:
   //
   //    begin=nnn - start print from #nnn'th row, for example Print("begin=100")
   //    end=nnn   - end print at  #nnn'th row, for example Print("begin=100 end=1000")
   //    nrows=nnn - print #nnn rows, for example Print("begin=100 nrows=1000")
   //    file=filename - save content as CSV file, for example Print("file=result.csv")

   TSQLResultSet* th = (TSQLResultSet*)this;

   if(!fImp) {
      Warning("Print()","TSQLResultSet is destroyed\n");
      th->Throw(new TSQLException("TSQLResultSet is destroyed"));
      th->Destroyed();
      return;
   }
   
   TString str;
   TString stropt = option;

   if(stropt.Contains("file")) {
      str = GetValueFromOption("file",stropt);  // extract filename
      SaveAsCSV(th,str.Data());
      return;
   }

   TString colname;
   Int_t ds;
   Int_t dl;
   Int_t* nn;
   Int_t* pd1;
   Int_t pd2;
   Int_t save_row = th->GetRow(); 
   Int_t cur_row;
   Int_t nr = 0;

   Int_t srow = save_row;  // start from srow, default is current 
   Int_t nrows = -1; // -1 - stand for "all rows" 
   Int_t erow = -1;  // -1 - stand for "to the last row" 

   stropt.ToLower();

   if(stropt.Contains("begin")) {
      str = GetValueFromOption("begin",stropt);
      srow = atoi(str.Data());
   }

   if (stropt.Contains("end")) {
      str = GetValueFromOption("end",stropt);
      erow = atoi(str.Data());
   } 

   if (stropt.Contains("nrows")) {
      str = GetValueFromOption("nrows",stropt);
      nrows = atoi(str.Data());
   }

   Int_t ncols = fMetaData->GetColumnCount();

   nn  = new Int_t[ncols+1];
   pd1 = new Int_t[ncols+1];

   for(int i=1; i<ncols+1; i++) {
      colname = fMetaData->GetColumnName(i);
      ds = fMetaData->GetColumnDisplaySize(i)+2;
      dl = colname.Length() + 2;
      nn[i] = dl > ds ? dl : ds;
      pd1[i] = nn[i]-dl;
   }   

   //  
   for(int i=1; i<ncols+1; i++) {
      cout << "+"; cout.fill('-'); cout.width(nn[i]+1); cout << "+";
   }  cout << endl;

   for(int i=1; i<ncols+1; i++) { 
      colname = fMetaData->GetColumnName(i); 
      cout << "| " << colname << " "; 
      cout.fill(' '); cout.width(pd1[i]+1); cout << "|";
   } cout << endl;

   //  
   for(int i=1; i<ncols+1; i++) {
      cout << "+"; cout.fill('-'); cout.width(nn[i]+1); cout << "+";
   }  cout << endl;

   cur_row = th->GetRow();

   if(fStatement) {
      if(fStatement->GetResultSetType() != kTYPE_FORWARD_ONLY ) {
         th->Absolute(srow-1);
      } else {
         if(srow>cur_row) {
            while ( th->Next() && cur_row+2 < srow) cur_row = th->GetRow();
         }
      }
   }

   nr = 0;
   cur_row = th->GetRow();

   while ( th->Next() ) {
 
      if(nrows > 0 && nr >= nrows) break;      
      if(erow > 0 && cur_row >= erow ) break;
      nr++;
      cur_row = th->GetRow();

      for(int i=1; i<ncols+1; i++) {
         str = th->GetString(i);
         cout << "| " << str;
         pd2 = nn[i] - str.Length();
         cout.fill(' '); cout.width(pd2); cout << "|"; 
      }  cout << endl;
   }

   for(int i=1; i<ncols+1; i++) {
      cout << "+"; cout.fill('-'); cout.width(nn[i]+1); cout << "+";
   }  cout << endl;

   if(fStatement) { 
      if(fStatement->GetResultSetType() != kTYPE_FORWARD_ONLY ) {
         th->Absolute(save_row);
      } else {
         Warning("Print","To set cursor to initial position -> re-execute Query.");
      }
   }
   delete [] nn;
   delete [] pd1;
}

//___________________________________________________________________
TTree* TSQLResultSet::Tree(Int_t begin,Int_t end)
{
   // Writes resultset content to ROOT tree
   //
   // This method creates "new TTree". 
   // To avoid memory leakage it should be deleted if not used.
   //
   // See also TTree
   //
   // Comment: this method is experimental nad buggy
   
   TString leafList;    // leaf description string
   TString clmname;     // column name

   Int_t siz = 0;
   Int_t ncollumns = 0;
   char* buf = 0;
   Int_t type,offset,prec;
   TString str;
   char tmpstr[40];

   Int_t  intg = 0;
   Short_t shrt = 0;
   Float_t flt = 0;
   Double_t dbl = 0;
   Int_t yy, mm, dd, hh, mi, ss;
   UInt_t t =0;
   struct tm tp;
   Int_t save_row = GetRow(); 
   Int_t cur_row =0;

   Int_t srow = begin > 0 ? begin : save_row; 
   Int_t erow = end > 0 ? end : -1;
   Int_t tmp = 0;

   if(srow>erow) { 
      tmp = erow; 
      erow = srow;
      srow = tmp; 
   }

   // calculate "leaf buffer" size
   ncollumns = fMetaData->GetColumnCount();

   for( int i=1; i <= ncollumns; ++i ) {
      type = fMetaData->GetColumnType(i);
            
      switch( type ) { 
      case kCHAR: 
      case kVARCHAR:
        siz += fMetaData->GetPrecision(i)+1; // length + zero
        break;
      case kINTEGER:
         siz += sizeof(Int_t);
         break;
      case kDATE:
      case kTIME:
      case kTIMESTAMP:
         siz += sizeof(UInt_t);
         break;      
      case kBIT:       
      case kTINYINT:
      case kSMALLINT:
         siz += sizeof(Short_t);
         break;
      case kREAL:
         siz += sizeof(Float_t);
         break;
      case kLONGVARCHAR: // not resolved yet how to handle   
      case kLONGVARBINARY:
      case kVARBINARY:
         break;  
      case kBIGINT:     // convert all these types to Double_t
      case kDECIMAL:
      case kNUMERIC:
      case kDOUBLE:
      case kFLOAT:
      default:
         siz += sizeof(Double_t);
         break;      
      }  
   }

   // determine leaf description string
   for( int i=1; i <= ncollumns; ++i ) {
      type = fMetaData->GetColumnType(i);
      clmname = fMetaData->GetColumnName(i);
      
      switch( type ) {
      case kCHAR: 
      case kVARCHAR:
         prec = fMetaData->GetPrecision(i)+1;
         sprintf(tmpstr,"[%d]",prec);
         leafList += clmname + tmpstr + "/C:"; // 
         break;
      case kINTEGER:
         leafList += clmname + "/I:"; // signed integer 
         break;
      case kDATE:
      case kTIME:
      case kTIMESTAMP:
         leafList += clmname + "/i:"; // unsigned integer ( time_t format )
         break;
      case kBIT:        
      case kTINYINT:      
      case kSMALLINT:
         leafList += clmname + "/S:"; //  signed short        
         break;
      case kREAL:
          leafList += clmname + "/F:"; // floating point
         break;
      case kLONGVARCHAR: // not resolved yet how to handle   
      case kLONGVARBINARY:
      case kVARBINARY: 
         break;         
      case kBIGINT:     // convert all these types to Double_t
      case kDECIMAL:
      case kNUMERIC:          
      case kDOUBLE:
      case kFLOAT:
      default:
         leafList += clmname + "/D:"; // double
         break;
      }
   }  
   if(!leafList.IsNull()) leafList.Resize(leafList.Length()-1);   // cut off last ":"

   //  Dinamically allocate  "leaf buffer"  
   buf = new char[siz]; // buffer

   TString tblname =  fMetaData->GetTableName(1);

   if(tblname.IsNull()) { // if table name unknown => generate "random name" 
      tblname = "table"; 
      sprintf(tmpstr,"%d",rand()%1000);
      tblname += tmpstr;
   }

   // Create a ROOT Tree
   //
   TTree* tree = new TTree(tblname.Data(),"Created by TSQLResultSet:Tree() method");

   tree->Branch(tblname.Data(),(void*)buf,leafList.Data());

   // skip to start 
   cur_row = GetRow();

   if(fStatement) { 
      if(fStatement->GetResultSetType() != kTYPE_FORWARD_ONLY ) {
         Absolute(srow-1);
      } else {
         if(srow>cur_row) {
            while ( Next() && cur_row+2 < srow) cur_row = GetRow();
         }
      }
   }

   // tree filling 
   while( Next() ) {   // iterate rows
      offset = 0;
   
      if(erow>0 && cur_row >= erow) break;
      cur_row = GetRow();

      for( int i=1; i <= ncollumns; ++i ) {
         type = fMetaData->GetColumnType(i);
         
         switch( type ) {      
         case kCHAR:  
         case kVARCHAR:
            siz = fMetaData->GetPrecision(i)+1; 
            str = GetString(i);
            memcpy(&buf[offset],str.Data(),siz); 
            break;  
         case kINTEGER:
            siz = sizeof(Int_t);
            intg = GetInt(i);
            memcpy(&buf[offset],&intg,siz); 
            break;
         case kBIT:
         case kTINYINT:  
         case kSMALLINT:
            siz = sizeof(Short_t);
            shrt = GetShort(i);
            memcpy(&buf[offset],&shrt,siz); 
            break;
         case kREAL:
            siz = sizeof(Float_t);
            dbl = GetFloat(i);
            memcpy(&buf[offset],&flt,siz); 
            break;
         case kDATE: // convert all date-times into time_t
         case kTIME:       // probably not working for kTIME  
         case kTIMESTAMP: 
            siz = sizeof(UInt_t);
            str = GetString(i);
            sscanf(str.Data(), "%d-%d-%d %d:%d:%d", 
                                &yy, &mm, &dd, &hh, &mi, &ss);
            tp.tm_year  = yy-1900;
            tp.tm_mon   = mm;
            tp.tm_mday  = dd;
            tp.tm_hour  = hh;
            tp.tm_min   = mi;
            tp.tm_sec   = ss;
            tp.tm_isdst = -1;
            t = (UInt_t)mktime(&tp);
            memcpy(&buf[offset],&t,siz);
            break;            
         case kLONGVARCHAR: // not resolved  how to handle   
         case kLONGVARBINARY:
         case kVARBINARY:
            break;
         case kBIGINT:     // convert all these types to Double_t
         case kDECIMAL:
         case kNUMERIC:
         case kDOUBLE:
         case kFLOAT:                        
         default:
            siz = sizeof(Double_t);
            dbl = GetDouble(i);
            memcpy(&buf[offset],&dbl,siz); 
            break;      
         }  
         offset += siz;
      }
      tree->Fill();
   } 

   delete [] buf;

   if(fStatement) { 
      if(fStatement->GetResultSetType() != kTYPE_FORWARD_ONLY ) {
         Absolute(save_row);
      } else {
         Warning("Print","To set cursor to initial position -> re-execute Query.");
      }
   }

   return tree;  
}