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.
104 //
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 
244 const 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 
262 void 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 
TSQLStatement::GetTime
virtual Bool_t GetTime(Int_t, Int_t &, Int_t &, Int_t &)
Definition: TSQLStatement.h:92
TDatime::GetHour
Int_t GetHour() const
Definition: TDatime.h:69
TSQLStatement::ClearError
void ClearError()
reset error fields
Definition: TSQLStatement.cxx:252
TSQLStatement::SetTimestamp
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.
Definition: TSQLStatement.cxx:421
TSQLStatement::GetTimestamp
virtual Bool_t GetTimestamp(Int_t, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &)
Get parameter as timestamp.
Definition: TSQLStatement.cxx:438
TString::Data
const char * Data() const
Definition: TString.h:369
TSQLStatement::GetErrorMsg
virtual const char * GetErrorMsg() const
returns error message of last operation if no errors, return 0 Each specific implementation of TSQLSt...
Definition: TSQLStatement.cxx:244
ClassImp
#define ClassImp(name)
Definition: Rtypes.h:364
TObject::Error
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition: TObject.cxx:890
TDatime::GetSecond
Int_t GetSecond() const
Definition: TDatime.h:71
TSQLStatement.h
TSQLStatement::GetDay
Int_t GetDay(Int_t)
return day value for parameter (if applicable)
Definition: TSQLStatement.cxx:348
TSQLStatement::GetMonth
Int_t GetMonth(Int_t)
return month value for parameter (if applicable)
Definition: TSQLStatement.cxx:337
TSQLStatement::fErrorMsg
TString fErrorMsg
Definition: TSQLStatement.h:27
bool
TDatime::GetMinute
Int_t GetMinute() const
Definition: TDatime.h:70
TSQLStatement::GetDatime
virtual Bool_t GetDatime(Int_t, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &, Int_t &)
Definition: TSQLStatement.h:93
TSQLStatement::GetSecond
Int_t GetSecond(Int_t)
return seconds value for parameter (if applicable)
Definition: TSQLStatement.cxx:381
TSQLStatement::GetYear
Int_t GetYear(Int_t)
return year value for parameter (if applicable)
Definition: TSQLStatement.cxx:326
TSQLStatement::GetSecondsFraction
Int_t GetSecondsFraction(Int_t)
return seconds fraction value for parameter (if applicable)
Definition: TSQLStatement.cxx:392
kFALSE
const Bool_t kFALSE
Definition: RtypesCore.h:92
TSQLStatement::SetError
void SetError(Int_t code, const char *msg, const char *method=nullptr)
set new values for error fields if method specified, displays error message
Definition: TSQLStatement.cxx:262
TObject::Warning
virtual void Warning(const char *method, const char *msgfmt,...) const
Issue warning message.
Definition: TObject.cxx:876
TSQLStatement::fErrorCode
Int_t fErrorCode
Definition: TSQLStatement.h:26
TSQLStatement::GetMinute
Int_t GetMinute(Int_t)
return minutes value for parameter (if applicable)
Definition: TSQLStatement.cxx:370
TSQLStatement::GetHour
Int_t GetHour(Int_t)
return hours value for parameter (if applicable)
Definition: TSQLStatement.cxx:359
TSQLStatement::SetTime
virtual Bool_t SetTime(Int_t, Int_t, Int_t, Int_t)
Definition: TSQLStatement.h:53
TSQLStatement::SetDatime
virtual Bool_t SetDatime(Int_t, Int_t, Int_t, Int_t, Int_t, Int_t, Int_t)
Definition: TSQLStatement.h:55
TSQLStatement
Definition: TSQLStatement.h:21
TDatime::GetDay
Int_t GetDay() const
Definition: TDatime.h:67
TDatime
This class stores the date and time with a precision of one second in an unsigned 32 bit word (950130...
Definition: TDatime.h:37
TDatime::GetMonth
Int_t GetMonth() const
Definition: TDatime.h:66
TSQLStatement::SetDate
virtual Bool_t SetDate(Int_t, Int_t, Int_t, Int_t)
Definition: TSQLStatement.h:51
TTimeStamp
The TTimeStamp encapsulates seconds and ns since EPOCH.
Definition: TTimeStamp.h:71
TSQLStatement::GetDate
virtual Bool_t GetDate(Int_t, Int_t &, Int_t &, Int_t &)
Definition: TSQLStatement.h:91
TSQLStatement::fErrorOut
Bool_t fErrorOut
Definition: TSQLStatement.h:28
TSQLStatement::GetErrorCode
virtual Int_t GetErrorCode() const
returns error code of last operation if res==0, no error Each specific implementation of TSQLStatemen...
Definition: TSQLStatement.cxx:234
int
TDatime::GetYear
Int_t GetYear() const
Definition: TDatime.h:65