ROOT logo
ROOT » NET » NET » TSQLStatement

class TSQLStatement: public TObject


 TSQLStatement

 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, SAPDB, 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
       cout << "NumFields = " << stmt->GetNumFields() << endl;
       for (int n=0;n<stmt->GetNumFields();n++)
          cout << "Field " << n << "  = " << stmt->GetFieldName(n) << 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);
          cout << id1 << " - " << id2 << "  " << name1 << "  " << name2 << 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().


Function Members (Methods)

 
    This is an abstract class, constructors will not be documented.
    Look at the header to check for available constructors.

public:
virtual~TSQLStatement()
voidTObject::AbstractMethod(const char* method) const
virtual voidTObject::AppendPad(Option_t* option = "")
virtual voidTObject::Browse(TBrowser* b)
static TClass*Class()
virtual const char*TObject::ClassName() const
virtual voidTObject::Clear(Option_t* = "")
virtual TObject*TObject::Clone(const char* newname = "") const
virtual Int_tTObject::Compare(const TObject* obj) const
virtual voidTObject::Copy(TObject& object) const
virtual voidTObject::Delete(Option_t* option = "")MENU
virtual Int_tTObject::DistancetoPrimitive(Int_t px, Int_t py)
virtual voidTObject::Draw(Option_t* option = "")
virtual voidTObject::DrawClass() constMENU
virtual TObject*TObject::DrawClone(Option_t* option = "") constMENU
virtual voidTObject::Dump() constMENU
virtual voidEnableErrorOutput(Bool_t on = kTRUE)
virtual voidTObject::Error(const char* method, const char* msgfmt) const
virtual voidTObject::Execute(const char* method, const char* params, Int_t* error = 0)
virtual voidTObject::Execute(TMethod* method, TObjArray* params, Int_t* error = 0)
virtual voidTObject::ExecuteEvent(Int_t event, Int_t px, Int_t py)
virtual voidTObject::Fatal(const char* method, const char* msgfmt) const
virtual TObject*TObject::FindObject(const char* name) const
virtual TObject*TObject::FindObject(const TObject* obj) const
virtual Bool_tGetBinary(Int_t, void*&, Long_t&)
virtual Int_tGetBufferLength() const
virtual Bool_tGetDate(Int_t, Int_t&, Int_t&, Int_t&)
TDatimeGetDatime(Int_t)
virtual Bool_tGetDatime(Int_t, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&)
Int_tGetDay(Int_t)
virtual Double_tGetDouble(Int_t)
virtual Option_t*TObject::GetDrawOption() const
static Long_tTObject::GetDtorOnly()
virtual Int_tGetErrorCode() const
virtual const char*GetErrorMsg() const
virtual const char*GetFieldName(Int_t)
Int_tGetHour(Int_t)
virtual const char*TObject::GetIconName() const
virtual Int_tGetInt(Int_t)
virtual Bool_tGetLargeObject(Int_t col, void*& mem, Long_t& size)
virtual Long_tGetLong(Int_t)
virtual Long64_tGetLong64(Int_t)
Int_tGetMinute(Int_t)
Int_tGetMonth(Int_t)
virtual const char*TObject::GetName() const
virtual Int_tGetNumAffectedRows()
virtual Int_tGetNumFields()
virtual Int_tGetNumParameters()
virtual char*TObject::GetObjectInfo(Int_t px, Int_t py) const
static Bool_tTObject::GetObjectStat()
virtual Option_t*TObject::GetOption() const
Int_tGetSecond(Int_t)
virtual const char*GetString(Int_t)
virtual Bool_tGetTime(Int_t, Int_t&, Int_t&, Int_t&)
TDatimeGetTimestamp(Int_t)
virtual Bool_tGetTimestamp(Int_t, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&, Int_t&)
virtual const char*TObject::GetTitle() const
virtual UInt_tGetUInt(Int_t)
virtual ULong64_tGetULong64(Int_t)
virtual UInt_tTObject::GetUniqueID() const
Int_tGetYear(Int_t)
virtual Bool_tTObject::HandleTimer(TTimer* timer)
virtual ULong_tTObject::Hash() const
virtual voidTObject::Info(const char* method, const char* msgfmt) const
virtual Bool_tTObject::InheritsFrom(const char* classname) const
virtual Bool_tTObject::InheritsFrom(const TClass* cl) const
virtual voidTObject::Inspect() constMENU
voidTObject::InvertBit(UInt_t f)
virtual TClass*IsA() const
virtual Bool_tTObject::IsEqual(const TObject* obj) const
virtual Bool_tIsError() const
virtual Bool_tTObject::IsFolder() const
virtual Bool_tIsNull(Int_t)
Bool_tTObject::IsOnHeap() const
virtual Bool_tTObject::IsSortable() const
Bool_tTObject::IsZombie() const
virtual voidTObject::ls(Option_t* option = "") const
voidTObject::MayNotUse(const char* method) const
virtual Bool_tNextIteration()
virtual Bool_tNextResultRow()
virtual Bool_tTObject::Notify()
voidTObject::Obsolete(const char* method, const char* asOfVers, const char* removedFromVers) const
static voidTObject::operator delete(void* ptr)
static voidTObject::operator delete(void* ptr, void* vp)
static voidTObject::operator delete[](void* ptr)
static voidTObject::operator delete[](void* ptr, void* vp)
void*TObject::operator new(size_t sz)
void*TObject::operator new(size_t sz, void* vp)
void*TObject::operator new[](size_t sz)
void*TObject::operator new[](size_t sz, void* vp)
TSQLStatement&operator=(const TSQLStatement&)
virtual voidTObject::Paint(Option_t* option = "")
virtual voidTObject::Pop()
virtual voidTObject::Print(Option_t* option = "") const
virtual Bool_tProcess()
virtual Int_tTObject::Read(const char* name)
virtual voidTObject::RecursiveRemove(TObject* obj)
voidTObject::ResetBit(UInt_t f)
virtual voidTObject::SaveAs(const char* filename = "", Option_t* option = "") constMENU
virtual voidTObject::SavePrimitive(ostream& out, Option_t* option = "")
virtual Bool_tSetBinary(Int_t, void*, Long_t, Long_t = 0x1000)
voidTObject::SetBit(UInt_t f)
voidTObject::SetBit(UInt_t f, Bool_t set)
Bool_tSetDate(Int_t, const TDatime&)
virtual Bool_tSetDate(Int_t, Int_t, Int_t, Int_t)
Bool_tSetDatime(Int_t, const TDatime&)
virtual Bool_tSetDatime(Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t)
virtual Bool_tSetDouble(Int_t, Double_t)
virtual voidTObject::SetDrawOption(Option_t* option = "")MENU
static voidTObject::SetDtorOnly(void* obj)
virtual Bool_tSetInt(Int_t, Int_t)
virtual Bool_tSetLargeObject(Int_t col, void* mem, Long_t size, Long_t maxsize = 0x1000)
virtual Bool_tSetLong(Int_t, Long_t)
virtual Bool_tSetLong64(Int_t, Long64_t)
virtual Bool_tSetMaxFieldSize(Int_t, Long_t)
virtual Bool_tSetNull(Int_t)
static voidTObject::SetObjectStat(Bool_t stat)
virtual Bool_tSetString(Int_t, const char*, Int_t = 256)
Bool_tSetTime(Int_t, const TDatime&)
virtual Bool_tSetTime(Int_t, Int_t, Int_t, Int_t)
virtual voidSetTimeFormating(const char*)
Bool_tSetTimestamp(Int_t, const TDatime&)
virtual Bool_tSetTimestamp(Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t = 0)
virtual Bool_tSetUInt(Int_t, UInt_t)
virtual Bool_tSetULong64(Int_t, ULong64_t)
virtual voidTObject::SetUniqueID(UInt_t uid)
virtual voidShowMembers(TMemberInspector&)
virtual Bool_tStoreResult()
virtual voidStreamer(TBuffer&)
voidStreamerNVirtual(TBuffer& ClassDef_StreamerNVirtual_b)
virtual voidTObject::SysError(const char* method, const char* msgfmt) const
Bool_tTObject::TestBit(UInt_t f) const
Int_tTObject::TestBits(UInt_t f) const
virtual voidTObject::UseCurrentStyle()
virtual voidTObject::Warning(const char* method, const char* msgfmt) const
virtual Int_tTObject::Write(const char* name = 0, Int_t option = 0, Int_t bufsize = 0)
virtual Int_tTObject::Write(const char* name = 0, Int_t option = 0, Int_t bufsize = 0) const
protected:
voidClearError()
virtual voidTObject::DoError(int level, const char* location, const char* fmt, va_list va) const
voidTObject::MakeZombie()
voidSetError(Int_t code, const char* msg, const char* method = 0)

Data Members

protected:
Int_tfErrorCodeerror code of last operation
TStringfErrorMsgerror message of last operation
Bool_tfErrorOutenable error output

Class Charts

Inheritance Inherited Members Includes Libraries
Class Charts

Function documentation

Int_t GetErrorCode() const
 returns error code of last operation
 if res==0, no error
 Each specific implementation of TSQLStatement provides its own error coding
const char* GetErrorMsg() const
  returns error message of last operation
 if no errors, return 0
 Each specific implementation of TSQLStatement provides its own error messages
void ClearError()
 reset error fields
void SetError(Int_t code, const char* msg, const char* method = 0)
 set new values for error fields
 if method specified, displays error message
Bool_t SetDate(Int_t , const TDatime& )
 set only date value for specified parameter from TDatime object
Bool_t SetTime(Int_t , const TDatime& )
 set only time value for specified parameter from TDatime object
Bool_t SetDatime(Int_t , const TDatime& )
 set date & time value for specified parameter from TDatime object
Bool_t SetTimestamp(Int_t , const TDatime& )
 set timestamp value for specified parameter from TDatime object
TDatime GetDatime(Int_t )
 return value of parameter in form of TDatime
 Be aware, that TDatime does not allow dates before 1995-01-01
Int_t GetYear(Int_t )
 return year value for parameter (if applicable)
Int_t GetMonth(Int_t )
 return month value for parameter (if applicable)
Int_t GetDay(Int_t )
 return day value for parameter (if applicable)
Int_t GetHour(Int_t )
 return hours value for parameter (if applicable)
Int_t GetMinute(Int_t )
 return minutes value for parameter (if applicable)
Int_t GetSecond(Int_t )
 return seconds value for parameter (if applicable)
TDatime GetTimestamp(Int_t )
 return value of parameter in form of TDatime
 Be aware, that TDatime does not allow dates before 1995-01-01
virtual ~TSQLStatement()
{}
Int_t GetBufferLength() const
Int_t GetNumParameters()
Bool_t NextIteration()
Bool_t SetNull(Int_t )
{ return kFALSE; }
Bool_t SetInt(Int_t , Int_t )
{ return kFALSE; }
Bool_t SetUInt(Int_t , UInt_t )
{ return kFALSE; }
Bool_t SetLong(Int_t , Long_t )
{ return kFALSE; }
Bool_t SetLong64(Int_t , Long64_t )
{ return kFALSE; }
Bool_t SetULong64(Int_t , ULong64_t )
{ return kFALSE; }
Bool_t SetDouble(Int_t , Double_t )
{ return kFALSE; }
Bool_t SetString(Int_t , const char* , Int_t = 256)
{ return kFALSE; }
Bool_t SetDate(Int_t , Int_t , Int_t , Int_t )
{ return kFALSE; }
Bool_t SetTime(Int_t , Int_t , Int_t , Int_t )
{ return kFALSE; }
Bool_t SetDatime(Int_t , Int_t , Int_t , Int_t , Int_t , Int_t , Int_t )
{ return kFALSE; }
Bool_t SetTimestamp(Int_t , Int_t , Int_t , Int_t , Int_t , Int_t , Int_t , Int_t = 0)
{ return kFALSE; }
void SetTimeFormating(const char* )
{}
Bool_t SetBinary(Int_t , void* , Long_t , Long_t = 0x1000)
{ return kFALSE; }
Bool_t SetLargeObject(Int_t col, void* mem, Long_t size, Long_t maxsize = 0x1000)
{ return SetBinary(col, mem, size, maxsize); }
Bool_t Process()
Int_t GetNumAffectedRows()
{ return 0; }
Bool_t StoreResult()
Int_t GetNumFields()
const char * GetFieldName(Int_t )
Bool_t SetMaxFieldSize(Int_t , Long_t )
{ return kFALSE; }
Bool_t NextResultRow()
Bool_t IsNull(Int_t )
{ return kTRUE; }
Int_t GetInt(Int_t )
{ return 0; }
UInt_t GetUInt(Int_t )
{ return 0; }
Long_t GetLong(Int_t )
{ return 0; }
Long64_t GetLong64(Int_t )
{ return 0; }
ULong64_t GetULong64(Int_t )
{ return 0; }
Double_t GetDouble(Int_t )
{ return 0.; }
const char * GetString(Int_t )
{ return 0; }
Bool_t GetBinary(Int_t , void*& , Long_t& )
{ return kFALSE; }
Bool_t GetLargeObject(Int_t col, void*& mem, Long_t& size)
{ return GetBinary(col, mem, size); }
Bool_t GetDate(Int_t , Int_t& , Int_t& , Int_t& )
{ return kFALSE; }
Bool_t GetTime(Int_t , Int_t& , Int_t& , Int_t& )
{ return kFALSE; }
Bool_t GetDatime(Int_t , Int_t& , Int_t& , Int_t& , Int_t& , Int_t& , Int_t& )
{ return kFALSE; }
Bool_t GetTimestamp(Int_t , Int_t& , Int_t& , Int_t& , Int_t& , Int_t& , Int_t& , Int_t& )
{ return kFALSE; }
Bool_t IsError() const
{ return GetErrorCode()!=0; }
void EnableErrorOutput(Bool_t on = kTRUE)
{ fErrorOut = on; }