MySQLStatement.cxx

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

// $Id: MySQLStatement.cxx,v 1.1.1.1 2004/02/18 20:58:02 dave Exp $
//*-- Author : Valeriy Onuchin 24/02/2001 
//
// RDBC driver to MySQL database implemented with MySQL C API.
//

//
// The object used for executing a static SQL statement and 
// obtaining the results produced by it. 
// 
// Only one TSQLResultSet per TSQLStatement can be open at any point 
// in time. Therefore, if the reading of one TSQLResultSet is
// interleaved with the reading of another, each must have been 
// generated by different TSQLStatements. All statement execute 
// methods  implicitly close a statment's current TSQLResultSet if 
// an open  one exists. 
//
// See also: 
//    TSQLConnection::CreateStatement(), TSQLResultSet 
//    TSQLCallableStatement TSQLPreparedStatement
//Begin_Html
/*
<P>
   The <TT>TSQLStatement</TT> class encapsulates SQL queries to your database. 
Using several methods, these calls return objects that contain the 
results of your SQL query. When you execute an SQL query, the data 
that is returned to you is commonly called the result set. You can 
choose from several result sets, depending on your needs:
<UL>
<LI><TT>TSQLResultSet* ExecuteQuery( const TString& sqlStatement)<BR></TT>
This method sends the SQL query contained in <TT>sqlStatement</TT>
and returns a single set of results. This method is best used 
in sending  <TT>SELECT</TT> statements. These statements typically 
return a result set. This method implicitly deletes previous resultset.

<LI><TT>Int_t ExecuteUpdate( const TString& sqlStatement )<BR></TT>
This method sends the SQL query contained in <TT>sqlStatement</TT> 
and returns an integer. This method is useful when you send SQL 
<TT>INSERT</TT>s,  <TT>DELETE</TT>s, and <TT>UPDATE</TT>s.  These commands return 
a count of rows  that were affected  by your query. This statement 
should not be used for queries that  return result sets.

<LI><TT>Bool_t Execute( const TString& sqlStatement )<BR></TT>
This method sends the <TT>sqlStatement</TT> to the database and returns 
<TT>kTRUE</TT> if the statement returns a result set or <TT>kFALSE</TT> if the 
statement returns an integer. This method is best used when multiple 
result sets can be returned.
</UL>
<P>
Use the following methods to easily navigate the results a query returns:
<UL>
<LI><TT>Bool_t GetMoreResults()<BR> </TT>
This moves to the next result set in the <TT>TSQLStatement</TT>. This, 
like the <TT>Execute()</TT> method, returns <TT>kTRUE</TT> if the  next 
result is a result set or <TT>kFALSE</TT> if it is an  integer.  
If you have  already retrieved a <TT>TSQLResultSet</TT> from  the  
<TT>TSQLStatement</TT>, this method will close it before returning.

<LI><TT>TSQLResultSet* GetResultSet()<BR></TT>
This method returns to you a result set in a <TT>TSQLResultSet</TT> 
object. This result set is the current result set.

<LI><TT>Int_t GetUpdateCount()<BR></TT>
This method returns to you the integer result that an 
<TT>Execute()</TT> method returned.
</UL>
<P>
*/
//End_Html

#include <RDBC/TSQLStatement.h>
#include <RDBC/TSQLResultSet.h>
#include <RDBC/TSQLConnection.h>
#include <TList.h>
#include "MySQLStatementPrivate.h"


ClassImpQ(TSQLStatement)


//___________________________________________________________________
TSQLStatement::TSQLStatement( TSQLConnection* con, 
                              void* imp ):TSQL(imp)
{
   // ctor
 
   fBatches = new TList();
   fConnection = con;
   fCurrentResult = 0;
   fImp = new MySQLStatementPrivate();
}

//___________________________________________________________________
TSQLStatement::~TSQLStatement( )
{
   // Destructor.
   //
   //  Note: When a TSQLStatement is closed, its current 
   //       TSQLResultSet,  if one exists, is also closed.
   // 

   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   delete imp;  

   ClearBatch();
   SafeDelete(fBatches);

   fConnection->GetListOfStatements()->Remove(this);
   if(fCurrentResult) delete fCurrentResult;
   Destroyed();
}

//___________________________________________________________________
TSQLResultSet* TSQLStatement::ExecuteQuery( const TString& sql )
{
   // Executes a SQL statement that returns a single TSQLResultSet
   //
   // This method also implicitly closes current TSQLResultSet 
   //
   // Returns:
   //       a TSLResultSet that contains the data produced by the query; 
   //       NULL - in case of error
   //
   //   Throws:
   //       TSQLException - if a database access error occurs

   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   MySQLConnectionPrivate* con = (MySQLConnectionPrivate*)fConnection->fImp;

   imp->fQuery = sql;

   ClearWarnings();

   if(fCurrentResult)  fCurrentResult->Close();
   else fCurrentResult = new TSQLResultSet(this,new MySQLResultSetPrivate());

   MySQLResultSetPrivate* result = (MySQLResultSetPrivate*)fCurrentResultSet->fImp;

   TString oldCatalog;

   if( fConnection->GetCatalog() != imp->fCatalog ) {
      oldCatalog = fConnection->GetCatalog();
      fConnection->SetCatalog(imp->fCatalog);
   }

   if( imp->fMaxRows && imp->fMaxRows != (Ulong_t)~0L ) {
      if( !imp->fQuery.Contains("LIMIT",TString::kIgnoreCase) ) {
         Int_t pos = imp->fQuery.Index("select",0,TString::kIgnoreCase);
         imp->fQuery.Insert(pos+6,Form(" limit %lu",imp->fMaxRows));
      }
   }
 
   if( con->CheckIfServerIsAlive() || mysql_query(con->fMYSQL,imp->fQuery)) {
      Throw(new TSQLException(mysql_error(con->fMYSQL),"S1000",mysql_errno(con->fMYSQL)) );
      if( !oldCatalog.IsNull() ) fConnection->SetCatalog(oldCatalog);
      return 0;
   }

#ifdef NOT_USED
  if( imp->fCursorType == kCURSOR_FORWARD_ONLY &&
      !(con->fMYSQL->flag & FLAG_SAFE))
    result->fMYSQL_RES = mysql_use_result(con->fMYSQL);
  else
#endif

   result->fMYSQL_RES = mysql_store_result(con->fMYSQL);  
   
   if( !oldCatalog.IsNull() ) fConnection->SetCatalog(oldCatalog);

   if( !result->fMYSQL_RES ) {
      if( !mysql_field_count(con->fMYSQL) ) { 
         stmt->state = ST_EXECUTED;
         stmt->affected_rows = mysql_affected_rows(con->fMYSQL);
         result->fReallyResult = kFALSE;  // no result set
         return fCurrentResult;
      }
 
      Throw(new TSQLException(mysql_error(con->fMYSQL),"S1000",mysql_errno(con->fMYSQL)));
      return 0;
   }

   result->fCurrentRow = 0;
   result->fReallyResult = kTRUE;  
   imp->fLastInsertId = fCurrenResult->GetUpdateID();

   return fCurrentResult;
}

//___________________________________________________________________
Bool_t TSQLStatement::Execute( const TString& sql )
{
   // Executes a SQL statement that may return multiple results.
   // Under some (uncommon) situations a single SQL statement may 
   // return multiple result sets and/or update counts. Normally you 
   // can ignore this unless you are (1) executing a stored
   // procedure that you know may return multiple results or (2) you 
   // are dynamically executing an unknown SQL string. The methods 
   // execute, GetMoreResults(), GetResultSet(), and GetUpdateCount()
   // let you navigate through multiple results.
   //  The execute method executes a SQL statement and indicates the 
   // form of the first result. You can then use GetResultSet() or
   // GetUpdateCount() to retrieve the result, and GetMoreResults() 
   // to move to any subsequent result(s).
   //
   // Parameters:
   //          sql - any SQL statement
   // Returns:
   //          kTRUE if the next result is a TSQLResultSet; 
   //          kFALSE if it is an update count or there are no more
   //          results
   // Throws:
   //            TSQLException - if a database access error occurs
   // See Also: 
   //       GetResultSet(), GetUpdateCount(), GetMoreResults()
  

   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   MySQLConnectionPrivate* con = (MySQLConnectionPrivate*)fConnection->fImp;

   imp->fQuery = sql;
   ClearWarnings();

   if(fCurrentResult)  fCurrentResult->Close();
   else fCurrentResult = new TSQLResultSet(this,new MySQLResultSetPrivate());

   MySQLResultSetPrivate* result = (MySQLResultSetPrivate*)fCurrentResultSet->fImp;

   TString oldCatalog;

   if( fConnection->GetCatalog() != imp->fCatalog ) {
      oldCatalog = fConnection->GetCatalog();
      fConnection->SetCatalog(imp->fCatalog);
   }

   if( imp->fMaxRows && imp->fMaxRows != (Ulong_t)~0L ) {
      if( !imp->fQuery.Contains("LIMIT",TString::kIgnoreCase) ) {
         Int_t pos = imp->fQuery.Index("select",0,TString::kIgnoreCase);
         imp->fQuery.Insert(pos+6,Form(" limit %lu",imp->fMaxRows));
      }
   }
 
   if( con->CheckIfServerIsAlive() || mysql_query(con->fMYSQL,imp->fQuery)) {
      Throw(new TSQLException(mysql_error(con->fMYSQL),"S1000",mysql_errno(con->fMYSQL)) );
      if( !oldCatalog.IsNull() ) fConnection->SetCatalog(oldCatalog);
      return 0;
   }

#ifdef NOT_USED
  if( imp->fCursorType == kCURSOR_FORWARD_ONLY &&
      !(con->fMYSQL->flag & FLAG_SAFE))
    result->fMYSQL_RES = mysql_use_result(con->fMYSQL);
  else
#endif

   result->fMYSQL_RES = mysql_store_result(con->fMYSQL);  
   
   if( !oldCatalog.IsNull() ) fConnection->SetCatalog(oldCatalog);

   if( !result->fMYSQL_RES ) {
      if( !mysql_field_count(con->fMYSQL) ) { 
         stmt->state = ST_EXECUTED;
         stmt->affected_rows = mysql_affected_rows(con->fMYSQL);
         result->fReallyResult = kFALSE;  // no result set
         return kFALSE;
      }
 
      Throw(new TSQLException(mysql_error(con->fMYSQL),"S1000",mysql_errno(con->fMYSQL)));
      return kFALSE;
   }

   result->fCurrentRow = 0;
   result->fReallyResult = kTRUE;  
   imp->fLastInsertId = fCurrenResult->GetUpdateID();

   return kTRUE; 
}

//___________________________________________________________________
Int_t TSQLStatement::ExecuteUpdate( const TString& sql )
{
   // Executes an SQL INSERT, UPDATE or DELETE statement. 
   // In addition, SQL statements that return nothing, 
   // such as SQL DDL statements, can be executed.
   //
   //  Parameters:
   //      sql - a SQL INSERT, UPDATE or DELETE statement or 
   //            a SQL statement that  returns nothing
   //
   //  Returns:
   //      either the row count for INSERT, UPDATE or DELETE or 
   //      0 for SQL statements that return nothing
   //  Throws:
   //      TSQLException - if a database access error occurs
 
   return return_value;
}

//___________________________________________________________________
TSQLResultSet* TSQLStatement::GetResultSet()
{
   // Returns the current result as a TSQLResultSet object. 
   // This method should be called only once per result.
   //
   // This method also implicitly closes any current TSQLResultSet 
   //   
   // Returns:
   //       the current result as a TSQLResultSet; null if the result 
   //       is an update count or there are no more results
   // Throws:
   //       TSQLException - if a database access error occurs
   // See Also: 
   //       Execute(const TString&)

}   

//___________________________________________________________________
Int_t TSQLStatement::GetUpdateCount()
{
   // Returns the current result as an update count; 
   // if there are no more results, -1 is returned.
   // This method should be called only once per result.
   //
   // Returns:
   //       the current result as an update count; -1 if it is a 
   //       TSQLResultSet or there are no more results
   // Throws:
   //       TSQLException - if a database access error occurs
   // See Also: 
   //       Execute(const TString&)
 
   return return_value;
}

//___________________________________________________________________
Bool_t TSQLStatement::GetMoreResults()
{
   // Moves to a TSQLStatement's next result. It returns kTRUE if 
   // this result is a TSQLResultSet. 
   // 
   // There are no more results when 
   //       (!GetMoreResults() && (GetUpdateCount() == -1)
   //
   // Returns:
   //    kTRUE if the next result is a TSQLResultSet; 
   //    kFALSE if it is an update count or there are no more results
   //
   // Throws:
   //       TSQLException - if a database access error occurs
   // See Also: 
   //       Execute(const TString&)

   return return_value;
}

//___________________________________________________________________
Int_t TSQLStatement::GetMaxFieldSize()
{
   // Returns the maximum number of bytes allowed for any column 
   // value. This limit is the maximum number of bytes that can be
   // returned for any column value. The limit applies only to 
   // kBINARY, kVARBINARY, kLONGVARBINARY, kCHAR, kVARCHAR, and 
   // kLONGVARCHAR columns (see TSQLTypes.h). If the limit is exceeded, 
   // the excess data  is silently discarded.
   //
   // Returns:
   //    the current max column size limit; zero means unlimited
   // Throws:
   //    TSQLException - if a database access error occurs

   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   reutrn imp->fMaxFieldSize;
}

//___________________________________________________________________
void TSQLStatement::SetMaxFieldSize( Int_t max )
{
   // Sets the limit for the maximum number of bytes in a column to 
   // the given number of bytes. This is the maximum number of bytes 
   // that can be returned for any column value. This limit applies 
   // only to kBINARY, kVARBINARY, kLONGVARBINARY, kCHAR, kVARCHAR,
   // and kLONGVARCHAR fields (see TSQLTypes.h) . If the limit is exceeded, 
   // the excess  data is silently discarded. For maximum portability, 
   // use values greater than 256.
   //
   // Parameters:
   //       max - the new max column size limit; zero means unlimited
   // Throws:
   //       TSQLException - if a database access error occurs
   
   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;

   if( max > MysqlIO.MAXBUF )
            throw new java.sql.SQLException("Attempt to set max field size > " + MysqlIO.MAXBUF + " (compile time default)", "S1009");
   else
      imp->fMaxFieldSize = max; 
}

//___________________________________________________________________
Int_t TSQLStatement::GetMaxRows()
{
   // Retrieves the maximum number of rows that a TSQLResultSet can 
   // contain. If the limit is exceeded, the excess rows are silently 
   // dropped.
   //
   // Returns:
   //       the current max row limit; zero means unlimited
   // Throws:
   //       TSQLException - if a database access error occurs

   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   return imp->fMaxRows <=0 ? 0 : imp->fMaxRows;
}

//___________________________________________________________________
void TSQLStatement::SetMaxRows( Int_t max )
{
   // Sets the limit for the maximum number of rows that any 
   // TSQLResultSet can contain to the given number. If the limit is 
   // exceeded, the excess rows are silently dropped.
   //
   // Parameters:
   //       max - the new max rows limit; zero means unlimited
   // Throws:
   //       TSQLException - if a database access error occurs
   
   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;

       if (max > MysqlDefs.MAX_ROWS) {
            throw new java.sql.SQLException("setMaxRows() out of range. " + max + " > " + MysqlDefs.MAX_ROWS + ".", "S1009");
        }

        if (max == 0) {
            max = -1;
        }

        _max_rows = max;

        // Most people don't use setMaxRows()
        // so don't penalize them
        // with the extra query it takes
        // to do it efficiently unless we need
        // to.

        _Conn.maxRowsChanged();
}

//___________________________________________________________________
void TSQLStatement::SetEscapeProcessing( Bool_t enable )
{
   // Sets escape processing on or off. If escape scanning is on 
   // (the default), the driver will do escape substitution before 
   // sending the SQL to the database.
   // 
   // Note:
   //    Since prepared statements have usually been parsed prior to 
   //    making this call, disabling escape processing for prepared 
   //    statements will have no effect.
   //
   // Parameters:
   //       enable - kTRUE to enable; kFALSE to disable
   // Throws:
   //       TSQLException - if a database access error occurs
   
   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   imp->fEscapeProcessing = enable;
}

//___________________________________________________________________
Bool_t TSQLStatement::GetEscapeProcessing()
{
   //  Returns if escape processing is on or off. 
   // If escape scanning is on (the default), the driver will do escape 
   // substitution before  sending the SQL to the database.
   // 
   // Note:
   //    Since prepared statements have usually been parsed prior to 
   //    making this call, disabling escape processing for prepared 
   //    statements will have no effect.
   //
   // Parameters:
   //       enable - kTRUE to enable; kFALSE to disable
   // Throws:
   //       TSQLException - if a database access error occurs
   
   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   return imp->fEscapeProcessing;
}

//___________________________________________________________________
Int_t TSQLStatement::GetQueryTimeout()
{
   // Retrieves the number of seconds the driver will wait for a
   // TSQLStatement to execute. If the limit is exceeded, a 
   // TSQLException is thrown.
   //
   // Returns:
   //    the current query timeout limit in seconds; zero means
   //    unlimited
   // Throws:
   //    TSQLException - if a database access error occurs
   
   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   return imp->fQueryTimeout;
}

//___________________________________________________________________
void TSQLStatement::SetQueryTimeout( Int_t seconds )
{
   // Sets the number of seconds the driver will wait for a 
   // TSQLStatement to execute to the given number of seconds. 
   // If the limit is exceeded, a TSQLException is thrown.
   //
   // Parameters:
   //          seconds - the new query timeout limit in seconds; 
   //          zero means unlimited
   // Throws:
   //          TSQLException - if a database access error occurs 

   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   imp->fQueryTimeout = 0; // not imlemented  yet
}

//___________________________________________________________________
void TSQLStatement::Cancel() 
{
   // Cancels this statement object if both the DBMS and driver 
   // support aborting an SQL statement. This method can be used by 
   // one thread to cancel a statement that is being executed by 
   // another thread.
   //
   // Throws:
   //       TSQLException - if a database access error occurs
   
}

//___________________________________________________________________
void TSQLStatement::Close()
{
   // Avoid using this method. Use delete TSQLStatement instead.
   //
   //  Note: When a TSQLStatement is closed, its current 
   //       TSQLResultSet,  if one exists, is also closed.
   //
   //     Throws:
   //      TSQLException - if a database access error occurs

   fImp = 0;
   Destroyed();
}

//___________________________________________________________________
void TSQLStatement::SetCursorName( const TString& name )
{
   // Defines the SQL cursor name that will be used by subsequent
   // TSQLStatement execute methods. This name can then be used in 
   // SQL positioned update/delete statements to identify the 
   // current row in the TSQLResultSet generated by this statement. 
   // If the database doesn't support positioned update/delete, 
   // this method is a noop. To insure that a cursor has the proper 
   // isolation level to support updates, the cursor's SELECT 
   // statement should be of the form 'SELECT FOR UPDATE ...'. If
   // the 'FOR UPDATE' phrase is omitted, positioned updates may 
   // fail. 
   //
   // Note: By definition, positioned update/delete execution must 
   //    be done by a different TSQLStatement than the one which 
   //    generated the TSQLResultSet being used for positioning.
   //    Also, cursor names must be unique within a connection.
   //
   // Parameters:
   //       name - the new cursor name, which must be unique within
   //       a connection
   // Throws:
   //       TSQLException - if a database access error occurs
   //
   //  This MySQL driver does not support cursors.
}

//___________________________________________________________________
void TSQLStatement::SetFetchDirection( Int_t direction )
{
   // Gives the driver a hint as to the direction in which the
   // rows in a result set will be processed. The hint applies only 
   // to result sets created using this TSQLStatement object. 
   // The default value is TSQLResultSet::kTYPE_FORWARD_ONLY
   //
   // Note that this method sets the default fetch direction for 
   // result sets generated by this TSQLStatement object.
   //
   // Parameters:
   //    direction - the initial direction for processing rows
   // Throws:
   //    TSQLException - if a database access error occurs or the 
   //                   given direction is not one of 
   // 
}

//___________________________________________________________________
Int_t TSQLStatement::GetFetchDirection()
{
   // Retrieves the direction for fetching rows from database
   // tables that is the default for result sets generated from this 
   // TSQLStatement object. If this TSQLStatement object has not set 
   // a fetch direction by calling the method SetFetchDirection(), 
   // the return value is implementation-specific.
   //
   // Returns:
   //       the default fetch direction for result sets generated 
   //       from this TSQLStatement object
   // Throws:
   //       TSQLException - if a database access error occurs

   Int_t return_value = 0;
   return return_value;
}

//___________________________________________________________________
void TSQLStatement::SetFetchSize( Int_t rows )
{
   // Gives the driver a hint as to the number of rows that
   // should be fetched from the database when more rows are needed. 
   // The number of rows specified affects only result sets created 
   // using this statement. If the value specified is zero, then the
   // hint is ignored. The default value is zero.
   //
   // Parameters:
   //       rows - the number of rows to fetch
   // Throws:
   //       TSQLException - if a database access error occurs, or 
   //       the condition 0 <= rows <= GetMaxRows() is not satisfied.

}

//___________________________________________________________________
Int_t TSQLStatement::GetFetchSize()
{
   // Retrieves the number of result set rows that is the default 
   // fetch size for result sets generated from this TSQLStatement 
   // object. If this TSQLStatement object has not set a fetch size
   // by calling the method SetFetchSize(), the return value is
   // implementation-specific.
   //
   // Returns:
   //       the default fetch size for result sets generated from 
   //       this TSQLStatement object
   // Throws:
   //       TSQLException - if a database access error occurs
   
   Int_t return_value = 0;
   return return_value;
}

//___________________________________________________________________
Int_t TSQLStatement::GetResultSetConcurrency()
{
   // Retrieves the result set concurrency.
   //
   // enum EResultSetConcurrency{
   //       kCONCUR_READ_ONLY,
   //       kCONCUR_UPDATABLE
   // };

   return kCONCUR_UPDATABLE;  // MyODBC says "Anything goes" 
}

//___________________________________________________________________
Int_t TSQLStatement::GetResultSetType()
{
   // Determine the result set type.
   //
   // enum EResultSetType{
   //       kTYPE_FORWARD_ONLY,
   //       kTYPE_SCROLL_INSENSITIVE,
   //       kTYPE_SCROLL_SENSITIVE
   // };
   //
     
   MySQLStatementPrivate* imp = (MySQLStatementPrivate*)fImp;
   return imp->fCursorType;
}

//___________________________________________________________________
void TSQLStatement::AddBatch( const TString& sql )
{
   // Adds a SQL command to the current batch of commmands for
   // the statement. This method is optional.
   //
   // Parameters:
   //       sql - typically this is a static SQL INSERT or UPDATE 
   //       statement
   // Throws:
   //       TSQLException - if a database access error occurs, or 
   //       the  driver does not support batch statements

}

//___________________________________________________________________
void TSQLStatement::ClearBatch()
{
   // Makes the set of commands in the current batch empty. This
   // method is optional.
   //
   // Throws:
   //       TSQLException - if a database access error occurs or 
   //       the driver does not support batch statements

}

//___________________________________________________________________
Int_t* TSQLStatement::ExecuteBatch()
{
   // Submits a batch of commands to the database for execution.
   // This method is optional.
   //
   // Returns:
   //       an array of update counts containing one element for 
   //       each command in the batch. The array is ordered 
   //       according  to the order in which commands were inserted 
   //       into the  batch.
   //
   // Throws:
   //       TSQLException - if a database access error occurs or 
   //       the driver  does not support batch statements

   return 0;
}