TSQLStatement.cxx

Go to the documentation of this file.

// @(#)root/net:$Id$
// Author: Sergey Linev   6/02/2006
 
/*************************************************************************
 * Copyright (C) 1995-2006, Rene Brun and Fons Rademakers.               *
 * All rights reserved.                                                  *
 *                                                                       *
 * For the licensing terms see $ROOTSYS/LICENSE.                         *
 * For the list of contributors see $ROOTSYS/README/CREDITS.             *
 *************************************************************************/
 
////////////////////////////////////////////////////////////////////////////////
///
/// Abstract base class defining SQL statements, which can be submitted
/// in bulk to DB server.
///
/// This is alternative to TSQLServer::Query() method, which allows only pure
/// text queries and pure text result in TSQLResult classes.
/// TSQLStatement is designed to support following features:
///   - usage of basic data types (like int or double) as parameters
///     in SQL statements
///   - bulk operation when inserting/updating/selecting data in database
///   - uasge of basic data types when accessing result set of executed query
///
///
/// 1. Creation of statement
/// ======================================
/// To create an instance of the TSQLStatement class, the TSQLServer::Statement() method
/// should be used. Depending on the driver used for an ODBC connection,
/// the appropriate object instance will be created. For the moment there are
/// six different implementations of the TSQLStatement class: for MySQL,
/// Oracle, PostgreSQL, SQLite3 and ODBC. Hopefully, support of ODBC will allow usage of
/// statements for most existing RDBMS.
///
///   // first, connect to the database
///   TSQLServer* serv = TSQLServer::Connect("mysql://hostname.domain:3306/test",
///                                          "user", "pass");
///   // check if connection is ok
///   if ((serv!=0) && serv->IsConnected()) {
///       // create instance of sql-statement
///       TSQLStatement* stmt = serv->Statement("CREATE TABLE TESTTABLE (ID1 INT, ID2 INT, FFIELD VARCHAR(255), FVALUE VARCHAR(255))";
///       // process statement
///       stmt->Process();
///       // destroy object
///       delete stmt;
///   }
///   delete serv;
///
///
/// 2. Insert data to data base
/// ===============================================
/// There is a special syntax of SQL queries which allows to use values
/// provided as parameters. For instance, to insert one row into the TESTTABLE created
/// with the previous example, one can simply execute a query like:
///
///    serv->Query("INSERT INTO TESTTABLE VALUES (1, 2, \"name1\", \"value1\"");
///
/// However, when many (100-1000) rows should be inserted, each call of
/// TSQLServer::Query() method will cause communication loop with database
/// server, and the statement has to be evaluated each time instead of using a prepared statement.
/// As a result, insertion of data takes too much time.
///
/// TSQLStatement provides a mechanism to insert many rows at once.
/// First of all, an appropriate statement should be created:
///
///    TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (?, ?, ?, ?)", 100);
///
/// Here question marks "?" indicate where statement parameters can be inserted.
/// To specify values of parameters, SetInt(), SetDouble(), SetString() and other
/// methods of the TSQLStatement class should be used. Before parameters values
/// can be specified, the NextIteration() method of statement class should be called.
/// For each new row,  NextIteration() has to be called first, then parameters values are
/// specified. There is one limitation for most type-aware DBMS - once a parameter is set as integer via
/// SetInt(), all other rows should be specified as integer. At the end,
/// TSQLStatement::Process() should be called. Here a small example:
///
///    // first, create statement
///    TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (?, ?, ?, ?)", 100);
///
///    for (int n=0;n<357;n++)
///       if (stmt->NextIteration()) {
///          stmt->SetInt(0, 123);
///          stmt->SetUInt(1, n+10);
///          stmt->SetString(2, Form("name %d",n), 200);
///          stmt->SetString(3, Form("value %d", n+10), 200);
///      }
///
///     stmt->Process();
///     delete stmt;
///
/// The second argument in the TSQLServer::Statement() method specifies the depth of
/// of buffers which are used to keep parameter values (100 in the example). It is not
/// a limitation of the number of rows which can be inserted with the statement.
/// When buffers are filled, they will be submitted to database and can be
/// reused again. This happens transparent to the user in the NextIteration()
/// method.
///
/// Oracle and some ODBC drivers support buffering of parameter values and,
/// as a result, bulk insert (update) operation. MySQL (native driver and
/// MyODBC 3) does not support such a mode of operation, therefore adding
/// new rows will result in communication loop to database.
///
/// Local databases (SQLite3) do not use any buffering at all in the TSQLStatement
/// implementation (but inside the library). They still profit from the
/// usage of prepared statements. When inserting many rows into a SQLite3 database,
/// consider using a transaction via the methods StartTransaction() and Commit()
/// of the TSQLServer, as autocommit is active by default and causes a sync to disk
/// after each single insert.
///
/// One should also mention differences between Oracle and ODBC SQL syntax for
/// parameters. ODBC (and MySQL) use question marks to specify the position
/// where parameters should be inserted (as shown in the example). Oracle uses
/// :1, :2 and so on as marks to specify the position of parameter 0, 1, and so on.
/// Therefore, similar to the example, a query will look like:
///
///    TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (:1, :2, :3, :4)", 100);
///
/// SQLite3 supports both these syntaxes and some more.
///
/// There is a possibility to set a parameter value to NULL with the SetNull() method.
/// If this method is to be called for the first iteration, one should first call another Set...
/// method to identify the actual type which will be used for the parameter later.
///
///
/// 3. Getting data from database
/// =============================
/// To request data from a database, the SELECT statement should be used.
/// After a SELECT statement is created, it must be processed
/// with the TSQLStatement::Process() method and the result of statement
/// should be stored in internal buffers with the method TSQLStatement::StoreResult().
/// Information about selected fields (columns)
/// can be obtained with GetNumFields() and GetFieldName() methods.
/// To receive data for the next result row, NextResultRow() method should be called.
/// Value from each column can be retrieved with the GetInt(), GetDouble(),
/// GetString() and other methods.
///
/// There are no strict limitations on which method should be used
/// to get column values. GetString() can be used as a generic method,
/// which should always return correct result, but also conversions between most
/// basic data types are supported. For instance, if a column contains integer
/// values, GetInt(), GetLong64(), GetDouble() and GetString() methods can be used.
/// If column has floating point format, GetDouble() and GetString() methods can
/// be used without loss of precision while GetInt() or GetLong64() will return
/// only the integer part of the value. One also can test whether
/// a value is NULL with the IsNull() method.
///
/// The buffer length specified for a statement in the TSQLServer::Statement() call
/// will also be used to allocate buffers for column values. Usage of these
/// buffers is transparent for users and does not limit the number of rows
/// which can be accessed with one statement. Again, local databases do not work
/// with buffers inside TSQLStatement at all and ignore this value.
/// Example of select query:
///
///    stmt = serv->Statement("SELECT * FROM TESTTABLE", 100);
///    // process statement
///    if (stmt->Process()) {
///       // store result of statement in buffer
///       stmt->StoreResult();
///
///       // display info about selected field
///       std::cout << "NumFields = " << stmt->GetNumFields() << std::endl;
///       for (int n=0;n<stmt->GetNumFields();n++)
///          std::cout << "Field " << n << "  = " << stmt->GetFieldName(n) << std::endl;
///
///       // extract rows one after another
///       while (stmt->NextResultRow()) {
///          Double_t id1 = stmt->GetDouble(0);
///          UInt_t id2 = stmt->GetUInt(1);
///          const char* name1 = stmt->GetString(2);
///          const char* name2 = stmt->GetString(3);
///          std::cout << id1 << " - " << id2 << "  " << name1 << "  " << name2 << std::endl;
///       }
///    }
///
/// 4. Working with date/time parameters
/// ====================================
/// The current implementation supports date, time, date&time and timestamp
/// data (all time intervals are not supported yet). To set or get date/time values,
/// the following methods should be used:
///   SetTime()/GetTime() - only time (hour:min:sec),
///   SetDate()/GetDate() - only date (year-month-day),
///   SetDatime()/GetDatime() - date and time
///   SetTimestamp()/GetTimestamp() - timestamp with seconds fraction
/// For some of these methods TDatime type can be used as parameter / return value.
/// Be aware that TDatime supports only dates after 1995-01-01.
/// There are also methods to get year, month, day, hour, minutes and seconds separately.
///
/// Note that different SQL databases treat date/time types differently.
/// For instance, MySQL has all correspondent types (TIME, DATE, DATETIME and TIMESTAMP),
/// Oracle native driver supports only DATE (which is actually date and time) and TIMESTAMP
/// ODBC interface provides access for time, date and timestamps,
/// for PostgreSQL, TIMESTAMP is available and can be retrieved via all methods,
/// the implementation for SQLite interprets the column content as
/// a timestamp with second fraction.
/// Due to these differences, one should use correct methods to access such data.
/// For instance, in MySQL SQL type 'DATE' is only date (one should use GetDate() to
/// access such data), while in Oracle it is date and time. Therefore,
/// to get complete data from a 'DATE' column in Oracle, one should use the GetDatime() method.
///
/// The only difference between timestamp and date/time is that timestamp has a fractional
/// seconds part. Be aware that the fractional part has different meanings
/// (actual value) in different SQL plugins.
/// For PostgreSQL, it is given back as microseconds, while for SQLite3,
/// milliseconds correspond to the fraction (similar to the DATETIME-functions
/// implemented in the SQLite3 language).
///
/// 5. Binary data
/// ==============
/// Most modern data bases support just binary data, which is
/// typically has SQL type name 'BLOB'. To access data in such
/// columns, GetBinary()/SetBinary() methods should be used.
/// The current implementation implies that the complete content of the
/// column must be retrieved at once. Therefore, very big data of
/// gigabytes size may cause a problem.
///
/// In addition, for PostgresSQL, the methods GetLargeObject()/SetLargeObject()
/// are implemented with similar syntax. They retrieve a large object for the OID
/// given in the column of the statement. For non-PostgreSQL databases,
/// calling GetLargeObject()/SetLargeObject() is redirected to GetBinary()/SetBinary().
///
/// Since ROOT 6.36, the GetLargeObject/GetBinary(Int_t col, void* &mem, Long_t& size) API
/// is defined strictly to return new memory `mem` that must be released with
/// `delete [] (unsigned char *) mem` by the caller.
/// Older uses of this API (such as those of oracle, mysql, odbc) that relied on
/// internal buffer management (owning pointers) are deprecated/no longer supported.
///
////////////////////////////////////////////////////////////////////////////////
 
#include "TSQLStatement.h"
 
ClassImp(TSQLStatement);
 
////////////////////////////////////////////////////////////////////////////////
/// returns error code of last operation
/// if res==0, no error
/// Each specific implementation of TSQLStatement provides its own error coding
 
Int_t TSQLStatement::GetErrorCode() const
{
   return fErrorCode;
}
 
////////////////////////////////////////////////////////////////////////////////
///  returns error message of last operation
/// if no errors, return 0
/// Each specific implementation of TSQLStatement provides its own error messages
 
const char* TSQLStatement::GetErrorMsg() const
{
   return GetErrorCode()==0 ? nullptr : fErrorMsg.Data();
}
 
////////////////////////////////////////////////////////////////////////////////
/// reset error fields
 
void TSQLStatement::ClearError()
{
   fErrorCode = 0;
   fErrorMsg = "";
}
 
////////////////////////////////////////////////////////////////////////////////
/// set new values for error fields
/// if method specified, displays error message
 
void TSQLStatement::SetError(Int_t code, const char* msg, const char* method)
{
   fErrorCode = code;
   fErrorMsg = msg;
   if (method && fErrorOut)
      Error(method,"Code: %d  Msg: %s", code, (msg ? msg : "No message"));
}
 
////////////////////////////////////////////////////////////////////////////////
/// set only date value for specified parameter from TDatime object
 
Bool_t TSQLStatement::SetDate(Int_t npar, const TDatime& tm)
{
   return SetDate(npar, tm.GetYear(), tm.GetMonth(), tm.GetDay());
}
 
////////////////////////////////////////////////////////////////////////////////
/// set only time value for specified parameter from TDatime object
 
Bool_t TSQLStatement::SetTime(Int_t npar, const TDatime& tm)
{
   return SetTime(npar, tm.GetHour(), tm.GetMinute(), tm.GetSecond());
}
 
////////////////////////////////////////////////////////////////////////////////
/// set date & time value for specified parameter from TDatime object
 
Bool_t TSQLStatement::SetDatime(Int_t npar, const TDatime& tm)
{
   return SetDatime(npar, tm.GetYear(), tm.GetMonth(), tm.GetDay(),
                          tm.GetHour(), tm.GetMinute(), tm.GetSecond());
}
 
////////////////////////////////////////////////////////////////////////////////
/// set timestamp value for specified parameter from TDatime object
 
Bool_t TSQLStatement::SetTimestamp(Int_t npar, const TDatime& tm)
{
   return SetTimestamp(npar, tm.GetYear(), tm.GetMonth(), tm.GetDay(),
                             tm.GetHour(), tm.GetMinute(), tm.GetSecond(), 0);
}
 
////////////////////////////////////////////////////////////////////////////////
/// return value of parameter in form of TDatime
/// Be aware, that TDatime does not allow dates before 1995-01-01
 
TDatime TSQLStatement::GetDatime(Int_t npar)
{
   Int_t year, month, day, hour, min, sec;
 
   if (!GetDatime(npar, year, month, day, hour, min, sec))
     return TDatime();
 
   if (year<1995) {
      SetError(-1, "Date before year 1995 does not supported by TDatime type", "GetDatime");
      return TDatime();
   }
 
   return TDatime(year, month, day, hour, min, sec);
}
 
////////////////////////////////////////////////////////////////////////////////
/// return year value for parameter (if applicable)
 
Int_t TSQLStatement::GetYear(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetDate(npar, year, month, day)) return year;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return year;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return month value for parameter (if applicable)
 
Int_t TSQLStatement::GetMonth(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetDate(npar, year, month, day)) return month;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return month;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return day value for parameter (if applicable)
 
Int_t TSQLStatement::GetDay(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetDate(npar, year, month, day)) return day;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return day;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return hours value for parameter (if applicable)
 
Int_t TSQLStatement::GetHour(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetTime(npar, hour, min, sec)) return hour;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return hour;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return minutes value for parameter (if applicable)
 
Int_t TSQLStatement::GetMinute(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetTime(npar, hour, min, sec)) return min;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return min;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return seconds value for parameter (if applicable)
 
Int_t TSQLStatement::GetSecond(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetTime(npar, hour, min, sec)) return sec;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return sec;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return seconds fraction value for parameter (if applicable)
 
Int_t TSQLStatement::GetSecondsFraction(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
   if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return frac;
   return 0;
}
 
////////////////////////////////////////////////////////////////////////////////
/// return value of parameter in form of TDatime
/// Be aware, that TDatime does not allow dates before 1995-01-01
 
TDatime TSQLStatement::GetTimestamp(Int_t npar)
{
   Int_t year, month, day, hour, min, sec, frac;
 
   if (!GetTimestamp(npar, year, month, day, hour, min, sec, frac))
     return TDatime();
 
   if (year<1995) {
      SetError(-1, "Date before year 1995 does not supported by TDatime type", "GetTimestamp");
      return TDatime();
   }
 
   return TDatime(year, month, day, hour, min, sec);
}
 
////////////////////////////////////////////////////////////////////////////////
/// Set parameter as timestamp
 
Bool_t TSQLStatement::SetTimestamp(Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t)
{
   return kFALSE;
}
 
////////////////////////////////////////////////////////////////////////////////
/// Set parameter as TTimeStamp
 
Bool_t TSQLStatement::SetTimestamp(Int_t, const TTimeStamp&)
{
   Warning("SetTimestamp", "Not implemented for TTimeStamp");
   return kFALSE;
}
 
////////////////////////////////////////////////////////////////////////////////
/// Get parameter as timestamp
 
Bool_t TSQLStatement::GetTimestamp(Int_t, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&)
{
   return kFALSE;
}
 
////////////////////////////////////////////////////////////////////////////////
/// Get parameter as TTimeStamp
 
Bool_t TSQLStatement::GetTimestamp(Int_t, TTimeStamp&)
{
   Warning("GetTimestamp", "Not implemented for TTimeStamp");
   return kFALSE;
}