Logo ROOT  
Reference Guide
TSQLStatement.cxx
Go to the documentation of this file.
1// @(#)root/net:$Id$
2// Author: Sergey Linev 6/02/2006
3
4/*************************************************************************
5 * Copyright (C) 1995-2006, Rene Brun and Fons Rademakers. *
6 * All rights reserved. *
7 * *
8 * For the licensing terms see $ROOTSYS/LICENSE. *
9 * For the list of contributors see $ROOTSYS/README/CREDITS. *
10 *************************************************************************/
11
12////////////////////////////////////////////////////////////////////////////////
13//
14// TSQLStatement
15//
16// Abstract base class defining SQL statements, which can be submitted
17// in bulk to DB server.
18//
19// This is alternative to TSQLServer::Query() method, which allows only pure
20// text queries and pure text result in TSQLResult classes.
21// TSQLStatement is designed to support following features:
22// - usage of basic data types (like int or double) as parameters
23// in SQL statements
24// - bulk operation when inserting/updating/selecting data in database
25// - uasge of basic data types when accessing result set of executed query
26//
27//
28// 1. Creation of statement
29// ======================================
30// To create an instance of the TSQLStatement class, the TSQLServer::Statement() method
31// should be used. Depending on the driver used for an ODBC connection,
32// the appropriate object instance will be created. For the moment there are
33// six different implementations of the TSQLStatement class: for MySQL,
34// Oracle, PostgreSQL, SQLite3 and ODBC. Hopefully, support of ODBC will allow usage of
35// statements for most existing RDBMS.
36//
37// // first, connect to the database
38// TSQLServer* serv = TSQLServer::Connect("mysql://hostname.domain:3306/test",
39// "user", "pass");
40// // check if connection is ok
41// if ((serv!=0) && serv->IsConnected()) {
42// // create instance of sql-statement
43// TSQLStatement* stmt = serv->Statement("CREATE TABLE TESTTABLE (ID1 INT, ID2 INT, FFIELD VARCHAR(255), FVALUE VARCHAR(255))";
44// // process statement
45// stmt->Process();
46// // destroy object
47// delete stmt;
48// }
49// delete serv;
50//
51//
52// 2. Insert data to data base
53// ===============================================
54// There is a special syntax of SQL queries which allows to use values
55// provided as parameters. For instance, to insert one row into the TESTTABLE created
56// with the previous example, one can simply execute a query like:
57//
58// serv->Query("INSERT INTO TESTTABLE VALUES (1, 2, \"name1\", \"value1\"");
59//
60// However, when many (100-1000) rows should be inserted, each call of
61// TSQLServer::Query() method will cause communication loop with database
62// server, and the statement has to be evaluated each time instead of using a prepared statement.
63// As a result, insertion of data takes too much time.
64//
65// TSQLStatement provides a mechanism to insert many rows at once.
66// First of all, an appropriate statement should be created:
67//
68// TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (?, ?, ?, ?)", 100);
69//
70// Here question marks "?" indicate where statement parameters can be inserted.
71// To specify values of parameters, SetInt(), SetDouble(), SetString() and other
72// methods of the TSQLStatement class should be used. Before parameters values
73// can be specified, the NextIteration() method of statement class should be called.
74// For each new row, NextIteration() has to be called first, then parameters values are
75// specified. There is one limitation for most type-aware DBMS - once a parameter is set as integer via
76// SetInt(), all other rows should be specified as integer. At the end,
77// TSQLStatement::Process() should be called. Here a small example:
78//
79// // first, create statement
80// TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (?, ?, ?, ?)", 100);
81//
82// for (int n=0;n<357;n++)
83// if (stmt->NextIteration()) {
84// stmt->SetInt(0, 123);
85// stmt->SetUInt(1, n+10);
86// stmt->SetString(2, Form("name %d",n), 200);
87// stmt->SetString(3, Form("value %d", n+10), 200);
88// }
89//
90// stmt->Process();
91// delete stmt;
92//
93// The second argument in the TSQLServer::Statement() method specifies the depth of
94// of buffers which are used to keep parameter values (100 in the example). It is not
95// a limitation of the number of rows which can be inserted with the statement.
96// When buffers are filled, they will be submitted to database and can be
97// reused again. This happens transparent to the user in the NextIteration()
98// method.
99//
100// Oracle and some ODBC drivers support buffering of parameter values and,
101// as a result, bulk insert (update) operation. MySQL (native driver and
102// MyODBC 3) does not support such a mode of operation, therefore adding
103// new rows will result in communication loop to database.
105// Local databases (SQLite3) do not use any buffering at all in the TSQLStatement
106// implementation (but inside the library). They still profit from the
107// usage of prepared statements. When inserting many rows into a SQLite3 database,
108// consider using a transaction via the methods StartTransaction() and Commit()
109// of the TSQLServer, as autocommit is active by default and causes a sync to disk
110// after each single insert.
111//
112// One should also mention differences between Oracle and ODBC SQL syntax for
113// parameters. ODBC (and MySQL) use question marks to specify the position
114// where parameters should be inserted (as shown in the example). Oracle uses
115// :1, :2 and so on as marks to specify the position of parameter 0, 1, and so on.
116// Therefore, similar to the example, a query will look like:
117//
118// TSQLStatement* stmt = serv->Statement("INSERT INTO TESTTABLE (ID1, ID2, FFIELD, FVALUE) VALUES (:1, :2, :3, :4)", 100);
119//
120// SQLite3 supports both these syntaxes and some more.
121//
122// There is a possibility to set a parameter value to NULL with the SetNull() method.
123// If this method is to be called for the first iteration, one should first call another Set...
124// method to identify the actual type which will be used for the parameter later.
125//
126//
127// 3. Getting data from database
128// =============================
129// To request data from a database, the SELECT statement should be used.
130// After a SELECT statement is created, it must be processed
131// with the TSQLStatement::Process() method and the result of statement
132// should be stored in internal buffers with the method TSQLStatement::StoreResult().
133// Information about selected fields (columns)
134// can be obtained with GetNumFields() and GetFieldName() methods.
135// To receive data for the next result row, NextResultRow() method should be called.
136// Value from each column can be retrieved with the GetInt(), GetDouble(),
137// GetString() and other methods.
138//
139// There are no strict limitations on which method should be used
140// to get column values. GetString() can be used as a generic method,
141// which should always return correct result, but also conversions between most
142// basic data types are supported. For instance, if a column contains integer
143// values, GetInt(), GetLong64(), GetDouble() and GetString() methods can be used.
144// If column has floating point format, GetDouble() and GetString() methods can
145// be used without loss of precision while GetInt() or GetLong64() will return
146// only the integer part of the value. One also can test whether
147// a value is NULL with the IsNull() method.
148//
149// The buffer length specified for a statement in the TSQLServer::Statement() call
150// will also be used to allocate buffers for column values. Usage of these
151// buffers is transparent for users and does not limit the number of rows
152// which can be accessed with one statement. Again, local databases do not work
153// with buffers inside TSQLStatement at all and ignore this value.
154// Example of select query:
155//
156// stmt = serv->Statement("SELECT * FROM TESTTABLE", 100);
157// // process statement
158// if (stmt->Process()) {
159// // store result of statement in buffer
160// stmt->StoreResult();
161//
162// // display info about selected field
163// std::cout << "NumFields = " << stmt->GetNumFields() << std::endl;
164// for (int n=0;n<stmt->GetNumFields();n++)
165// std::cout << "Field " << n << " = " << stmt->GetFieldName(n) << std::endl;
166//
167// // extract rows one after another
168// while (stmt->NextResultRow()) {
169// Double_t id1 = stmt->GetDouble(0);
170// UInt_t id2 = stmt->GetUInt(1);
171// const char* name1 = stmt->GetString(2);
172// const char* name2 = stmt->GetString(3);
173// std::cout << id1 << " - " << id2 << " " << name1 << " " << name2 << std::endl;
174// }
175// }
176//
177// 4. Working with date/time parameters
178// ====================================
179// The current implementation supports date, time, date&time and timestamp
180// data (all time intervals are not supported yet). To set or get date/time values,
181// the following methods should be used:
182// SetTime()/GetTime() - only time (hour:min:sec),
183// SetDate()/GetDate() - only date (year-month-day),
184// SetDatime()/GetDatime() - date and time
185// SetTimestamp()/GetTimestamp() - timestamp with seconds fraction
186// For some of these methods TDatime type can be used as parameter / return value.
187// Be aware that TDatime supports only dates after 1995-01-01.
188// There are also methods to get year, month, day, hour, minutes and seconds separately.
189//
190// Note that different SQL databases treat date/time types differently.
191// For instance, MySQL has all correspondent types (TIME, DATE, DATETIME and TIMESTAMP),
192// Oracle native driver supports only DATE (which is actually date and time) and TIMESTAMP
193// ODBC interface provides access for time, date and timestamps,
194// for PostgreSQL, TIMESTAMP is available and can be retrieved via all methods,
195// the implementation for SQLite interprets the column content as
196// a timestamp with second fraction.
197// Due to these differences, one should use correct methods to access such data.
198// For instance, in MySQL SQL type 'DATE' is only date (one should use GetDate() to
199// access such data), while in Oracle it is date and time. Therefore,
200// to get complete data from a 'DATE' column in Oracle, one should use the GetDatime() method.
201//
202// The only difference between timestamp and date/time is that timestamp has a fractional
203// seconds part. Be aware that the fractional part has different meanings
204// (actual value) in different SQL plugins.
205// For PostgreSQL, it is given back as microseconds, while for SQLite3,
206// milliseconds correspond to the fraction (similar to the DATETIME-functions
207// implemented in the SQLite3 language).
208//
209// 5. Binary data
210// ==============
211// Most modern data bases support just binary data, which is
212// typically has SQL type name 'BLOB'. To access data in such
213// columns, GetBinary()/SetBinary() methods should be used.
214// The current implementation implies that the complete content of the
215// column must be retrieved at once. Therefore, very big data of
216// gigabytes size may cause a problem.
217//
218// In addition, for PostgresSQL, the methods GetLargeObject()/SetLargeObject()
219// are implemented with similar syntax. They retrieve a large object for the OID
220// given in the column of the statement. For non-PostgreSQL databases,
221// calling GetLargeObject()/SetLargeObject() is redirected to GetBinary()/SetBinary().
222//
223////////////////////////////////////////////////////////////////////////////////
224
225#include "TSQLStatement.h"
226
228
229////////////////////////////////////////////////////////////////////////////////
230/// returns error code of last operation
231/// if res==0, no error
232/// Each specific implementation of TSQLStatement provides its own error coding
233
235{
236 return fErrorCode;
237}
238
239////////////////////////////////////////////////////////////////////////////////
240/// returns error message of last operation
241/// if no errors, return 0
242/// Each specific implementation of TSQLStatement provides its own error messages
243
244const char* TSQLStatement::GetErrorMsg() const
245{
246 return GetErrorCode()==0 ? nullptr : fErrorMsg.Data();
247}
248
249////////////////////////////////////////////////////////////////////////////////
250/// reset error fields
251
253{
254 fErrorCode = 0;
255 fErrorMsg = "";
256}
257
258////////////////////////////////////////////////////////////////////////////////
259/// set new values for error fields
260/// if method specified, displays error message
261
262void TSQLStatement::SetError(Int_t code, const char* msg, const char* method)
263{
264 fErrorCode = code;
265 fErrorMsg = msg;
266 if (method && fErrorOut)
267 Error(method,"Code: %d Msg: %s", code, (msg ? msg : "No message"));
268}
269
270////////////////////////////////////////////////////////////////////////////////
271/// set only date value for specified parameter from TDatime object
272
274{
275 return SetDate(npar, tm.GetYear(), tm.GetMonth(), tm.GetDay());
276}
277
278////////////////////////////////////////////////////////////////////////////////
279/// set only time value for specified parameter from TDatime object
280
282{
283 return SetTime(npar, tm.GetHour(), tm.GetMinute(), tm.GetSecond());
284}
285
286////////////////////////////////////////////////////////////////////////////////
287/// set date & time value for specified parameter from TDatime object
288
290{
291 return SetDatime(npar, tm.GetYear(), tm.GetMonth(), tm.GetDay(),
292 tm.GetHour(), tm.GetMinute(), tm.GetSecond());
293}
294
295////////////////////////////////////////////////////////////////////////////////
296/// set timestamp value for specified parameter from TDatime object
297
299{
300 return SetTimestamp(npar, tm.GetYear(), tm.GetMonth(), tm.GetDay(),
301 tm.GetHour(), tm.GetMinute(), tm.GetSecond(), 0);
302}
303
304////////////////////////////////////////////////////////////////////////////////
305/// return value of parameter in form of TDatime
306/// Be aware, that TDatime does not allow dates before 1995-01-01
307
309{
310 Int_t year, month, day, hour, min, sec;
311
312 if (!GetDatime(npar, year, month, day, hour, min, sec))
313 return TDatime();
314
315 if (year<1995) {
316 SetError(-1, "Date before year 1995 does not supported by TDatime type", "GetDatime");
317 return TDatime();
318 }
319
320 return TDatime(year, month, day, hour, min, sec);
321}
322
323////////////////////////////////////////////////////////////////////////////////
324/// return year value for parameter (if applicable)
325
327{
328 Int_t year, month, day, hour, min, sec, frac;
329 if (GetDate(npar, year, month, day)) return year;
330 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return year;
331 return 0;
332}
333
334////////////////////////////////////////////////////////////////////////////////
335/// return month value for parameter (if applicable)
336
338{
339 Int_t year, month, day, hour, min, sec, frac;
340 if (GetDate(npar, year, month, day)) return month;
341 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return month;
342 return 0;
343}
344
345////////////////////////////////////////////////////////////////////////////////
346/// return day value for parameter (if applicable)
347
349{
350 Int_t year, month, day, hour, min, sec, frac;
351 if (GetDate(npar, year, month, day)) return day;
352 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return day;
353 return 0;
354}
355
356////////////////////////////////////////////////////////////////////////////////
357/// return hours value for parameter (if applicable)
358
360{
361 Int_t year, month, day, hour, min, sec, frac;
362 if (GetTime(npar, hour, min, sec)) return hour;
363 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return hour;
364 return 0;
365}
366
367////////////////////////////////////////////////////////////////////////////////
368/// return minutes value for parameter (if applicable)
369
371{
372 Int_t year, month, day, hour, min, sec, frac;
373 if (GetTime(npar, hour, min, sec)) return min;
374 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return min;
375 return 0;
376}
377
378////////////////////////////////////////////////////////////////////////////////
379/// return seconds value for parameter (if applicable)
380
382{
383 Int_t year, month, day, hour, min, sec, frac;
384 if (GetTime(npar, hour, min, sec)) return sec;
385 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return sec;
386 return 0;
387}
388
389////////////////////////////////////////////////////////////////////////////////
390/// return seconds fraction value for parameter (if applicable)
391
393{
394 Int_t year, month, day, hour, min, sec, frac;
395 if (GetTimestamp(npar, year, month, day, hour, min, sec, frac)) return frac;
396 return 0;
397}
398
399////////////////////////////////////////////////////////////////////////////////
400/// return value of parameter in form of TDatime
401/// Be aware, that TDatime does not allow dates before 1995-01-01
402
404{
405 Int_t year, month, day, hour, min, sec, frac;
406
407 if (!GetTimestamp(npar, year, month, day, hour, min, sec, frac))
408 return TDatime();
409
410 if (year<1995) {
411 SetError(-1, "Date before year 1995 does not supported by TDatime type", "GetTimestamp");
412 return TDatime();
413 }
414
415 return TDatime(year, month, day, hour, min, sec);
416}
417
418////////////////////////////////////////////////////////////////////////////////
419/// Set parameter as timestamp
420
422{
423 return kFALSE;
424}
425
426////////////////////////////////////////////////////////////////////////////////
427/// Set parameter as TTimeStamp
428
430{
431 Warning("SetTimestamp", "Not implemented for TTimeStamp");
432 return kFALSE;
433}
434
435////////////////////////////////////////////////////////////////////////////////
436/// Get parameter as timestamp
437
439{
440 return kFALSE;
441}
442
443////////////////////////////////////////////////////////////////////////////////
444/// Get parameter as TTimeStamp
445
447{
448 Warning("GetTimestamp", "Not implemented for TTimeStamp");
449 return kFALSE;
450}
451
const Bool_t kFALSE
Definition: RtypesCore.h:90
#define ClassImp(name)
Definition: Rtypes.h:361
This class stores the date and time with a precision of one second in an unsigned 32 bit word (950130...
Definition: TDatime.h:37
Int_t GetMonth() const
Definition: TDatime.h:66
Int_t GetDay() const
Definition: TDatime.h:67
Int_t GetHour() const
Definition: TDatime.h:69
Int_t GetSecond() const
Definition: TDatime.h:71
Int_t GetYear() const
Definition: TDatime.h:65
Int_t GetMinute() const
Definition: TDatime.h:70
virtual void Warning(const char *method, const char *msgfmt,...) const
Issue warning message.
Definition: TObject.cxx:877
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition: TObject.cxx:891
Bool_t fErrorOut
Definition: TSQLStatement.h:28
virtual Int_t GetErrorCode() const
returns error code of last operation if res==0, no error Each specific implementation of TSQLStatemen...
TString fErrorMsg
Definition: TSQLStatement.h:27
virtual Bool_t GetDatime(Int_t, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &)
Definition: TSQLStatement.h:93
void SetError(Int_t code, const char *msg, const char *method=nullptr)
set new values for error fields if method specified, displays error message
Int_t GetDay(Int_t)
return day value for parameter (if applicable)
virtual Bool_t SetTimestamp(Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t=0)
Set parameter as timestamp.
virtual Bool_t SetDatime(Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t)
Definition: TSQLStatement.h:55
virtual Bool_t GetDate(Int_t, Int_t &, Int_t &, Int_t &)
Definition: TSQLStatement.h:91
void ClearError()
reset error fields
Int_t GetMonth(Int_t)
return month value for parameter (if applicable)
Int_t GetSecondsFraction(Int_t)
return seconds fraction value for parameter (if applicable)
virtual Bool_t SetTime(Int_t, Int_t, Int_t, Int_t)
Definition: TSQLStatement.h:53
virtual Bool_t GetTime(Int_t, Int_t &, Int_t &, Int_t &)
Definition: TSQLStatement.h:92
Int_t GetSecond(Int_t)
return seconds value for parameter (if applicable)
Int_t GetMinute(Int_t)
return minutes value for parameter (if applicable)
virtual const char * GetErrorMsg() const
returns error message of last operation if no errors, return 0 Each specific implementation of TSQLSt...
Int_t GetYear(Int_t)
return year value for parameter (if applicable)
virtual Bool_t SetDate(Int_t, Int_t, Int_t, Int_t)
Definition: TSQLStatement.h:51
virtual Bool_t GetTimestamp(Int_t, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &)
Get parameter as timestamp.
Int_t GetHour(Int_t)
return hours value for parameter (if applicable)
const char * Data() const
Definition: TString.h:364
The TTimeStamp encapsulates seconds and ns since EPOCH.
Definition: TTimeStamp.h:71