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 type (like int or double) as parameters
     in SQL statements
   - bulk operation when inserting/updating/selecting data in data base
   - uasge of basic data types when accessing result set of executed query


 1. Creation of statement

 To create instance of TSQLStatement class, TSQLServer::Statement() method
 should be used. Depending of the driver, used for connection to ODBC,
 appropriate object instance will be created. For the moment there are
 three different implementation of TSQLStatement class: for MySQL,
 Oracle and ODBC. Hopefully, support of ODBC will allows usage of
 statements for most existing RDBMS.

   // first connect to data base
   TSQLServer* serv = TSQLServer::Connect("mysql://hostname.domain:3306/test",
                                          "user", "pass");
   // check if connection is ok
   if ((serv!=0) && serv->IsConnected()) {
       // create statement instance
       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 allow to use values,
 provided as parameters. For instance, insert one row in TESTTABLE, created
 with previous example, one can simply execute query like:

    serv->Query("INSERT INTO TESTTABLE VALUES (1, 2, \"name1\", \"value1\"");

 But when many (100-1000) rows should be inserted, each call of
 TSQLServer::Query() method will cause communication loop with database
 server. As a result, insertion of data will takes too much time.

 TSQLStatement provides a mechanism to insert many rows at once. First of all,
 appropriate statement should be created:

    TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (?, ?, ?, ?)", 100);

 Here question marks "?" indicates where statement parameters can be inserted.
 To specify values of parameters, SetInt(), SetDouble(), SetString() and other
 methods of TSQLStatement class should be used. Before parameters values
 can be specified, NextIteration() method of statement class should be called.
 For each new row first, NextIteration() called, that parameters values are
 specified. There is one limitation - once parameter set as integer via
 SetInt(), for 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;

 Second argument in TSQLServer::Statement() method specifies depth of
 of buffers, used to keep parameter values (100 in example). It is not
 a limitation of rows number, 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 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 mode of operation, therefore adding
 new rows will result in communication loop to database.

 One should also mention difference between Oracle and ODBC SQL syntax for
 parameters. ODBC (and MySQL) uses question marks to specify position,
 where parameters should be inserted (as shown in the example). Oracle uses
 :1, :2 and so on marks for specify position of parameter 0, 1, and so on.
 Therefore, similar to example query will look like:

    TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (:1, :2, :3, :4)", 100);

 There is a possibility to set parameter value to NULL with SetNull() method.
 If this method called for first iteration, before one should call other Set...
 to identify actual parameter type, which will be used for parameter later.


 3. Getting data from database

 To request data from data base, SELECT statement should be used.
 After SELECT statement is created, it must be processed
 (with TSQLStatement::Process()) method and result of statement
 should be stored in internal buffers with TSQLStatement::StoreResult()
 method. Information about selected fields (columns)
 can be obtained with GetNumFields() and GetFieldName() methods.
 To recieve data for next result row, NextResultRow() method should be called.
 Value from each column can be taken with the GetInt(), GetDouble(),
 GetString() and other methods.

 There are no strict limitation which method should be used
 to get column values. GetString() can be used as generic method,
 which should always return correct result, but also convertion between most
 basic data types are supported. For instance, if column contains integer
 values, GetInt(), GetLong64(), GetDouble() and GetString() methods can be used.
 If column has float point format, GetDouble() and GetString() methods can
 be used without loss of precision while GetInt() or GetLong64() will return
 integer part of the value. One also can test, if value is NULL with IsNull()
 method.

 Buffer length, specified for statement in 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 number of rows,
 which can be accessed with  one statement. 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

 Current implementation supports date, time, date&time and timestamp
 data (all time intervals not supported yet). To set or get date/time values,
 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 separately year, month, day, hour, minutes and seconds.

 Different SQL databases has different treatement of date/time types.
 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.
 Therefore, 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 'DATE' column in Oracle, one should use GetDatime() method.

 The only difference of timestamp from date/time, that it has fractional
 seconds part. Be aware, that fractional part can has different meaning
 (actual value) in different SQL plugins.

 5. Binary data

 Most of 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.
 Current implementation supposed, that complete content of the
 column must be retrieved at once. Therefore very big data of
 gigabytes size may cause a problem.


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 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()
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(basic_ostream<char,char_traits<char> >& 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_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& insp, char* parent)
virtual Bool_tStoreResult()
virtual voidStreamer(TBuffer& b)
voidStreamerNVirtual(TBuffer& 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 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 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; }