DbiStatement Class Reference

#include <DbiStatement.h>

List of all members.

Public Member Functions
	DbiStatement (DbiConnection &conDb)
virtual	~DbiStatement ()
Dbi::DbTypes	GetDBType () const
Bool_t	PrintExceptions (Int_t level=3) const
const DbiExceptionLog &	GetExceptionLog () const
std::list< TString >	TestTranslateSQL (const TString &sql, Dbi::DbTypes type)
	Debugging of SQL translation.
TSQLStatement *	ExecuteQuery (const TString &sql="")
	Give caller a TSQLStatement of results (Process() and StoreResult() already performed.).
Bool_t	ExecuteUpdate (const TString &sql="")
	Apply an update and return success/fail.
Private Member Functions
void	AppendExceptionLog (DbiException *e)
void	AppendExceptionLog (TSQLStatement *s)
void	AppendExceptionLog (DbiConnection &c)
void	ClearExceptionLog ()
TSQLStatement *	CreateProcessedStatement (const TString &sql="")
std::list< TString >	TranslateSQL (const TString &sql)
Private Attributes
DbiConnection &	fConDb
	Connection associated with this statement.
Dbi::DbTypes	fDbType
	Type of database (MySQL, Oracle).
DbiExceptionLog	fExceptionLog

---

Detailed Description

Id

DbiStatement.h,v 1.14 2007/04/26 14:19:57 west Exp

Definition at line 44 of file DbiStatement.h.

---

Constructor & Destructor Documentation

DbiStatement::DbiStatement

(

DbiConnection &

conDb

)

Definition at line 35 of file DbiStatement.cxx.

References Msg::kVerbose, LEA_CTOR, and MSG.

                                               :
fConDb(conDb),
fDbType(conDb.GetDbType())
{
//
//
//  Purpose:  Constructor
//
//  Arguments:  None.
//
//  Return:   
//
//  conDb    in    The connection associated with the statement.
//

  LEA_CTOR    //Leak Checker

  MSG("Dbi", Msg::kVerbose) << "Creating DbiStatement" << endl;
  fConDb.ConnectStatement();

}

DbiStatement::~DbiStatement

(

)

[virtual]

Definition at line 59 of file DbiStatement.cxx.

References DbiConnection::DisConnectStatement(), fConDb, Msg::kVerbose, LEA_DTOR, and MSG.

                            {
//
//
//  Purpose: Destructor

  LEA_DTOR    //Leak Checker
  MSG("Dbi", Msg::kVerbose) << "Destroying DbiStatement" << endl;

  fConDb.DisConnectStatement();
}

---

Member Function Documentation

void DbiStatement::AppendExceptionLog

(

DbiConnection &

c

)

[inline, private]

Definition at line 83 of file DbiStatement.h.

References DbiExceptionLog::AddLog(), fExceptionLog, and DbiConnection::GetExceptionLog().

{          fExceptionLog.AddLog(c.GetExceptionLog()); }

void DbiStatement::AppendExceptionLog

(

TSQLStatement *

s

)

[inline, private]

Definition at line 82 of file DbiStatement.h.

References DbiExceptionLog::AddEntry(), and fExceptionLog.

{ if ( s ) fExceptionLog.AddEntry(*s); }

void DbiStatement::AppendExceptionLog

(

DbiException *

e

)

[inline, private]

Definition at line 81 of file DbiStatement.h.

References DbiExceptionLog::AddEntry(), MuELoss::e, and fExceptionLog.

Referenced by CreateProcessedStatement(), and ExecuteUpdate().

{ if ( e ) fExceptionLog.AddEntry(*e); }

void DbiStatement::ClearExceptionLog

(

)

[inline, private]

Definition at line 84 of file DbiStatement.h.

References DbiExceptionLog::Clear(), and fExceptionLog.

Referenced by ExecuteQuery(), and ExecuteUpdate().

{ fExceptionLog.Clear(); }

TSQLStatement * DbiStatement::CreateProcessedStatement

(

const TString &

sql = ""

)

[private]

Definition at line 72 of file DbiStatement.cxx.

References AppendExceptionLog(), DbiConnection::CreatePreparedStatement(), and fConDb.

Referenced by ExecuteQuery().

                                                                                  {

// Attempt to create a processed statement (caller must delete).  Return 0 if failure.

  TSQLStatement* stmt = fConDb.CreatePreparedStatement(sql.Data());
  if ( ! stmt ) {
    this->AppendExceptionLog(fConDb);
    return 0;
  }
  if ( stmt->Process() ) return stmt;
  this->AppendExceptionLog(stmt);
  delete stmt;
  stmt = 0;
  return 0;

}

TSQLStatement * DbiStatement::ExecuteQuery

(

const TString &

sql = ""

)

Give caller a TSQLStatement of results (Process() and StoreResult() already performed.).

Definition at line 92 of file DbiStatement.cxx.

References DbiExceptionLog::AddEntry(), ClearExceptionLog(), CreateProcessedStatement(), fConDb, fExceptionLog, DbiConnection::GetDbName(), DbiExceptionLog::IsEmpty(), Msg::kSynopsis, and MSG.

Referenced by do_command(), DbuDaqConfigFilesText::LookupSeqNo(), and DbmValidate::TestGlobaliseSeqNo().

                                                             {
//
//
//  Purpose:  Translate SQL if required and execute. 
//  Return:   TSQLStatement with Process() and StoreResult() already performed.

  this->ClearExceptionLog();

  std::list<TString> sqlList(this->TranslateSQL(sql));
  std::list<TString>::const_iterator itr(sqlList.begin()), itrEnd(sqlList.end());

  TSQLStatement* stmt = 0;
  while (itr != itrEnd) {
    const TString& sql = *itr++;
    MSG("Dbi",Msg::kSynopsis) << "SQL:" << fConDb.GetDbName() << ":" << sql << endl;
    delete stmt;
    stmt = this->CreateProcessedStatement(sql);
    if ( ! stmt ) return 0;
  }
  // Store results from last SQL command (when multiple commands are generated
  // the last will be the one that performs the query).
  if ( stmt && ! stmt->StoreResult() ) {
    this->AppendExceptionLog(stmt);
    delete stmt;
    stmt = 0;
  }

  // Final sanity check: If there is a statement then the exception log should still
  // be clear otherwise it should not be.
  if ( stmt ) {
    if ( ! fExceptionLog.IsEmpty() ) {
      delete stmt;
      stmt = 0;
    }
  }
  else if ( fExceptionLog.IsEmpty() ) {
    ostringstream oss;
    oss << "Unknown failure (no execption but no TSQLStatement either executing " << sql;
    fExceptionLog.AddEntry(oss.str().c_str());
  }
  return stmt;
  
}

Bool_t DbiStatement::ExecuteUpdate

(

const TString &

sql = ""

)

Apply an update and return success/fail.

Definition at line 138 of file DbiStatement.cxx.

References AppendExceptionLog(), ClearExceptionLog(), fConDb, fExceptionLog, DbiConnection::GetDbName(), DbiConnection::GetServer(), DbiExceptionLog::IsEmpty(), Msg::kSynopsis, MSG, and DbiConnection::RecordException().

Referenced by DbuRunSummary::Commit(), DbuDaqFileSummary::Commit(), do_command(), DbmValidate::PrepareDb(), DbiCascader::Lock::SetLock(), and DbuDaqMonitorModule::WriteSubRunSummaryEntry().

                                                      {
//
//
//  Purpose:  Translate SQL if required and Execute.
//
//  Return true if all updates successful.


  this->ClearExceptionLog();

  std::list<TString> sqlList(this->TranslateSQL(sql));
  std::list<TString>::const_iterator itr(sqlList.begin()), itrEnd(sqlList.end());

  while (itr != itrEnd) {
    const TString& sql = *itr++;
    MSG("Dbi",Msg::kSynopsis) << "SQL:" << fConDb.GetDbName() << ":" << sql << endl;
    bool ok = fConDb.GetServer()->Exec(sql.Data());
    if ( ! ok ) {
      fConDb.RecordException();
      this->AppendExceptionLog(fConDb);
      return false;
    }
  }
  return fExceptionLog.IsEmpty();
  
}

Dbi::DbTypes DbiStatement::GetDBType

(

)

const [inline]

Definition at line 55 of file DbiStatement.h.

References fDbType.

Referenced by DbiCascader::Lock::SetLock().

{ return fDbType; } 

const DbiExceptionLog& DbiStatement::GetExceptionLog

(

)

const [inline]

Definition at line 64 of file DbiStatement.h.

References fExceptionLog.

Referenced by DbiCascader::Lock::SetLock().

{ return fExceptionLog; }

Bool_t DbiStatement::PrintExceptions

(

Int_t

level = 3

)

const

Print accumulated exceptions at supplied Msg level, add them to the Global Exception Log if level >= kWarning and return true if there are any.

Definition at line 167 of file DbiStatement.cxx.

References DbiExceptionLog::AddLog(), DbiExceptionLog::GetGELog(), Msg::kWarning, and MSG.

Referenced by DbuRunSummary::Commit(), DbuDaqFileSummary::Commit(), DbiCascader::CreateStatement(), DbmValidate::PrepareDb(), DbiCascader::Lock::SetLock(), and DbmValidate::TestGlobaliseSeqNo().

                                                      {

//  Purpose:  Print accumulated exceptions at supplied Msg level,
//            add them to the Global Exception Log if level >= kWarning
//            and return true if there are any.

  const DbiExceptionLog& el(this->GetExceptionLog());
  if ( el.IsEmpty() ) return false;
  MSG("Dbi", level) << el;
  if ( level >= Msg::kWarning )  DbiExceptionLog::GetGELog().AddLog(el);
  return true;

}

std::list< TString > DbiStatement::TestTranslateSQL	(	const TString &	sql,
		Dbi::DbTypes	type
	)

Debugging of SQL translation.

Definition at line 183 of file DbiStatement.cxx.

References fDbType.

                                                                                    {
//
//
//  Purpose:  Debugging aid: Test translate from MySQL to other dialects of SQL.
//
//  Arguments: 
//    sql          in    The string to be translated
//    type         in    The database type to translate to.
//
//  Return:    A list of translated SQL
//             NB A single MySQL command can generate multiple ORACLE ones.

  Dbi::DbTypes fDbTypeSave = fDbType;
  fDbType = type;
  std::list<TString> sqlTrans(this->TranslateSQL(sql));
  fDbType = fDbTypeSave;
  return sqlTrans;

}

std::list< TString > DbiStatement::TranslateSQL

(

const TString &

sql

)

[private]

Definition at line 205 of file DbiStatement.cxx.

References fDbType, Dbi::kOracle, Msg::kSynopsis, MSG, DbiTableMetaData::SetFromSql(), DbiTableMetaData::Sql(), and UtilString::StringTok().

                                                               {
//
//
//  Purpose:  Translate from MySQL to other dialects of SQL.
//
//  Arguments: 
//    sql          in    The string to be translated
//
//
//  Return:    A list of translated SQL
//             NB A single MySQL command can generate multiple ORACLE ones.
// 
//  Contact:   N. West
//
//  Specification:-
//  =============
//
//  Translate  MySQL to other dialects of SQL.

//  This is NOT meant to be a general purpose SQL translator, but rather a very
//  simple translater of the SQL that the DBI employs which is dialect specific.

//  The only translations supported are as follows:-

//  1)  MySQL -> Oracle:-
//      Set date format to be compatible with MySQL.
//
//  2)  MySQL -> Oracle:-
//      Convert WHERE expressions of the form A & B to bitand(A,B) != 0
//
//  3)  MySQL -> Oracle:-
//      In CREATE TABLE 
//
//      Conversion is achieved by creating a DbiTableMetaData object
//      from the SQL and then asking it to generate the Oracle equivalent.
//      See DbiTableMetaData for details.
//      
//  4)  MySQL -> Oracle:-
//      SHOW TABLES        ->  SELECT TABLE_NAME FROM ALL_TABLES
//
//  5)  MySQL -> Oracle:-
//      \' -> '' (in general ORACLE doesn't respect escape sequences
//                except single quotes and only then as '' not \').
//
//  6)  MySQL -> Oracle:-
//      Convert now() into sysdate

  std::list<TString> sqlTransList;

  if ( fDbType != Dbi::kOracle ) {
    sqlTransList.push_back(sql);
    return sqlTransList;
}

  Bool_t translated = false;  //Once true,  sqlTransList contains the translation. No further translation possible.
  Bool_t modified  = false;   //sqlTrans has been modified, but  further translation possible.

  TString sqlTrans(sql);   
  sqlTrans.ToUpper();

// Set date format  to be compatible with MySQL.
  sqlTransList.push_back("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'");

// Translate NOW()
  if ( sqlTrans.Index("NOW()") != kNPOS ) {
    sqlTrans.ReplaceAll("NOW()","SYSDATE");
    modified = true;
  }

// Translate SHOW TABLES
  if (sqlTrans == "SHOW TABLES" ) {
    sqlTrans = "SELECT TABLE_NAME FROM ALL_TABLES";
    sqlTransList.push_back(sqlTrans);
    translated = true;
  }

   TString sqlIncant;

// Translate CREATE TABLE commands using DbiTableMetaData.
  Ssiz_t createTableIndex = sqlTrans.Index("CREATE TABLE",0,TString::kIgnoreCase );
  if ( ! translated && createTableIndex != kNPOS ) {
    DbiTableMetaData tmd;
    tmd.SetFromSql(sql.Data());
    std::vector<std::string> sql_list;
    UtilString::StringTok(sql_list,tmd.Sql(Dbi::kOracle),";");
    std::vector<std::string>::const_iterator itr(sql_list.begin()), itrEnd(sql_list.end());
    while ( itr != itrEnd ) { 
      sqlTransList.push_back(*itr); 
      ++itr;
    }
    translated = true;
  }

  
  // Translate DROP TABLE commands.
  Ssiz_t dropTableIndex = sqlTrans.Index("DROP TABLE",0,TString::kIgnoreCase );
  if ( ! translated && dropTableIndex != kNPOS ) {
    // Remove any "IF EXISTS"
    sqlTrans.ReplaceAll(" IF EXISTS ",    " ");

    //add the drop table command, then work on the synonym
    sqlTransList.push_back(sqlTrans);

    // Extract the table name
    Ssiz_t startIndex = dropTableIndex + 10;
    while ( isspace(sqlTrans[startIndex]) ) ++startIndex;
    Ssiz_t endIndex = startIndex + 1;
    Ssiz_t endIndexMax = sqlTrans.Length();
    while (     endIndex < endIndexMax
            && (isalnum(sqlTrans[endIndex]) || sqlTrans[endIndex] == '_') 
          ) ++endIndex;
    TString name(sqlTrans.Data()+startIndex,endIndex-startIndex);
    translated = true;
    
    // Add extra ORACLE incantations required when dropping a table with
    // a public synonym
    TString sqlIncant("DROP PUBLIC SYNONYM ");
    sqlIncant  += name ;
    sqlTransList.push_back(sqlIncant);
  }
  
// Translate commands with a WHERE keyword, but take care if this is
// an INSERT INTO command - it could include "where" as part of a character value!
  Ssiz_t whereStart = sqlTrans.Index("INSERT INTO",0,TString::kIgnoreCase );
  if ( whereStart == kNPOS ) whereStart = 0;
  else {
    // Skip to end of INSERT by looking for the trailing ")" at level 0
    // Yes, I know this can be defeated by mismatched "(" and ")" within
    // character values, but it's better than giving up on a possible WHERE clause 
    // altogether.
    Ssiz_t whereStartMax = sqlTrans.Length();
    whereStart = sqlTrans.Index("(",whereStart,TString::kIgnoreCase );
    if ( whereStart == kNPOS ) whereStart = whereStartMax;
    else ++whereStart;
    int level = 1;
    while ( whereStart < whereStartMax && level ) {
      char chr = sqlTrans[whereStart++];
      if ( chr == '(' ) ++level;
      if ( chr == ')' ) --level;
    }
  }
  // Look for space separated WHERE (so not fooled by e.g. FABWHERE!)
  Ssiz_t whereIndex = sqlTrans.Index(" WHERE ",whereStart,TString::kIgnoreCase );
  if ( ! translated && whereIndex != kNPOS ) {

    // Set limit of WHERE clause
    ++whereIndex; // Step over leading space
    Ssiz_t whereEnd = sqlTrans.Length();
    std::string whereDelim[] = { "GROUP BY", "HAVING", "ORDER BY", "LIMIT" };
    int numDelims = sizeof(whereDelim)/sizeof(string);
    for (int iDelim = 0; iDelim < numDelims; ++iDelim ) {
      const string& delimName = whereDelim[iDelim];
      Ssiz_t delimIndex = sqlTrans.Index(delimName.c_str(), delimName.size(), whereIndex + 5, TString::kIgnoreCase );
      if (delimIndex != kNPOS && delimIndex < whereEnd) whereEnd = delimIndex;
    }

    // Translate all bitwise and expressions within the WHERE clause.

    TString whereClause(sql.Data()+whereIndex,whereEnd-whereIndex);
    // Convert \n to space so that tokenising works.
    whereClause.ReplaceAll("\n"," ");
   
    std::vector<std::string> tokens;
    UtilString::StringTok(tokens,whereClause.Data()," ");
    int numTokens = tokens.size();
    for (int ithToken = 1; ithToken < numTokens-1; ++ithToken ) {
      if ( tokens[ithToken] == "&" ) {
        string tmp("bitand(");
        tmp += tokens[ithToken-1] + "," +  tokens[ithToken+1] + ") != 0";
        tokens[ithToken] = tmp;
        tokens[ithToken-1] = "";
        tokens[ithToken+1] = "";
      }
    }

    sqlTrans = sql(0,whereIndex);
    for (int ithToken = 0; ithToken < numTokens; ++ithToken ) {
      sqlTrans += " ";
      sqlTrans += tokens[ithToken].c_str();
    }
    sqlTrans += " " + sql(whereEnd,999999);
    modified = true;
  }

// Translate \' to ''
  if ( ! translated && sqlTrans.Index("\\\'") != kNPOS ) {
//  Bit of a kludge, if not yet modified, undo upper case conversion
//  as quoted data is likely to contain characters.
    if ( not modified ) sqlTrans = sql;
    sqlTrans.ReplaceAll("\\\'","\'\'");
    modified = true;
  }

  if ( modified && ! translated ) {
    sqlTransList.push_back(sqlTrans);
    translated = true;
  }

  if ( translated ) {
    MSG("Dbi",Msg::kSynopsis) << "sql: " << sql  << endl
                           << "translates to " << sqlTransList.size() 
                           << " statements:- \n";
    std::list<TString>::const_iterator itr(sqlTransList.begin()), itrEnd(sqlTransList.end());
    while (itr != itrEnd) { MSG("Dbi",Msg::kSynopsis) << "   " << *itr << endl; ++itr;}
  }
  else {
    sqlTransList.push_back(sql);
  }

  return sqlTransList;

}

---

Member Data Documentation

DbiConnection& DbiStatement::fConDb [private]

Connection associated with this statement.

Definition at line 92 of file DbiStatement.h.

Referenced by CreateProcessedStatement(), ExecuteQuery(), ExecuteUpdate(), and ~DbiStatement().

Dbi::DbTypes DbiStatement::fDbType [private]

Type of database (MySQL, Oracle).

Definition at line 95 of file DbiStatement.h.

Referenced by GetDBType(), TestTranslateSQL(), and TranslateSQL().

DbiExceptionLog DbiStatement::fExceptionLog [private]

A log of reported exceptions. Cleared by calling ExecuteQuery, ExecuteUpdate

Definition at line 99 of file DbiStatement.h.

Referenced by AppendExceptionLog(), ClearExceptionLog(), ExecuteQuery(), ExecuteUpdate(), and GetExceptionLog().

---

The documentation for this class was generated from the following files:

• DbiStatement.h
• DbiStatement.cxx

---