Logo ROOT   6.18/05
Reference Guide
TSQLiteStatement.cxx
Go to the documentation of this file.
1// @(#)root/sqlite:$Id$
2// Author: o.freyermuth <o.f@cern.ch>, 01/06/2013
3
4/*************************************************************************
5 * Copyright (C) 1995-2013, 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// SQL statement class for SQLite. //
15// //
16// See TSQLStatement class documentation for more details. //
17// //
18//////////////////////////////////////////////////////////////////////////
19
20#include "TSQLiteStatement.h"
21#include "TSQLiteResult.h"
22#include "TDataType.h"
23#include "TDatime.h"
24#include "TTimeStamp.h"
25
26#include <stdlib.h>
27
29
30////////////////////////////////////////////////////////////////////////////////
31/// Normal constructor.
32/// Checks if statement contains parameters tags.
33
35 TSQLStatement(errout),
36 fStmt(stmt),
37 fWorkingMode(0),
38 fNumPars(0),
39 fIterationCount(0)
40{
41 unsigned long bindParamcount = sqlite3_bind_parameter_count(fStmt->fRes);
42
43 if (bindParamcount > 0) {
44 fWorkingMode = 1;
45 fNumPars = bindParamcount;
46 } else {
47 fWorkingMode = 2;
48 fNumPars = sqlite3_column_count(fStmt->fRes);
49 }
50}
51
52////////////////////////////////////////////////////////////////////////////////
53/// Destructor.
54
56{
57 Close();
58}
59
60////////////////////////////////////////////////////////////////////////////////
61/// Close statement.
62
64{
65 if (fStmt->fRes) {
66 sqlite3_finalize(fStmt->fRes);
67 }
68
69 fStmt->fRes = 0;
70 fStmt->fConn = 0;
71 delete fStmt;
72}
73
74
75// Reset error and check that statement exists
76#define CheckStmt(method, res) \
77 { \
78 ClearError(); \
79 if (fStmt==0) { \
80 SetError(-1,"Statement handle is 0",method); \
81 return res; \
82 } \
83 }
84
85#define CheckErrNo(method, force, res) \
86 { \
87 int stmterrno = sqlite3_errcode(fStmt->fConn); \
88 if ((stmterrno!=0) || force) { \
89 const char* stmterrmsg = sqlite3_errmsg(fStmt->fConn); \
90 if (stmterrno==0) { stmterrno = -1; stmterrmsg = "SQLite statement error"; } \
91 SetError(stmterrno, stmterrmsg, method); \
92 return res; \
93 } \
94 }
95
96#define CheckGetField(method, res) \
97 { \
98 ClearError(); \
99 if (!IsResultSetMode()) { \
100 SetError(-1,"Cannot get statement parameters",method); \
101 return res; \
102 } \
103 if ((npar<0) || (npar>=fNumPars)) { \
104 SetError(-1,Form("Invalid parameter number %d", npar),method); \
105 return res; \
106 } \
107 }
108
109
110Bool_t TSQLiteStatement::CheckBindError(const char *method, int res)
111{
112 if (res == SQLITE_RANGE) {
113 SetError(-1, Form("SQLite parameter out of bounds, error: %d %s", res, sqlite3_errmsg(fStmt->fConn)), method);
114 return kFALSE;
115 }
116 if (res != SQLITE_OK) {
117 SetError(-1, Form("SQLite error code during parameter binding, error: %d %s", res, sqlite3_errmsg(fStmt->fConn)), method);
118 return kFALSE;
119 }
120 return kTRUE;
121}
122
123////////////////////////////////////////////////////////////////////////////////
124/// Process statement.
125
127{
128 CheckStmt("Process", kFALSE);
129
130 int res = sqlite3_step(fStmt->fRes);
131 if ((res != SQLITE_DONE) && (res != SQLITE_ROW)) {
132 SetError(-1, Form("SQLite error code during statement-stepping: %d %s", res, sqlite3_errmsg(fStmt->fConn)), "Process");
133 return kFALSE;
134 }
135
136 // After a DONE-step, we have to reset, note this still KEEPS the parameters bound in SQLite,
137 // real reset happens in finalize, but user can still reuse the query!
138 if (res == SQLITE_DONE) {
139 sqlite3_reset(fStmt->fRes);
140
141 // If IsResultSetMode then this means we are done and should return kFALSE:
142 if (IsResultSetMode()) {
143 return kFALSE;
144 }
145
146 // If IsSetParsMode then this means we just stepped and should return kTRUE:
147 if (IsSetParsMode()) {
148 return kTRUE;
149 }
150 }
151
152 if (res == SQLITE_ROW) {
153 // Next row data retrieved, return kTRUE.
154 return kTRUE;
155 }
156
157 return kFALSE;
158}
159
160////////////////////////////////////////////////////////////////////////////////
161/// Return number of affected rows after statement is processed.
162/// Indirect changes e.g. by triggers are not counted, only direct changes
163/// from last completed statement are taken into account.
164
166{
167 CheckStmt("GetNumAffectedRows", kFALSE);
168
169 return (Int_t) sqlite3_changes(fStmt->fConn);
170}
171
172////////////////////////////////////////////////////////////////////////////////
173/// Return number of statement parameters.
174
176{
177 CheckStmt("GetNumParameters", -1);
178
179 Int_t res = sqlite3_bind_parameter_count(fStmt->fRes);
180
181 CheckErrNo("GetNumParameters", kFALSE, -1);
182
183 return res;
184}
185
186////////////////////////////////////////////////////////////////////////////////
187/// Store result of statement processing to access them
188/// via GetInt(), GetDouble() and so on methods.
189/// For SQLite, this is a NO-OP.
190
192{
193 fWorkingMode = 2;
194
195 CheckStmt("StoreResult", kFALSE);
196
197 return kTRUE;
198}
199
200////////////////////////////////////////////////////////////////////////////////
201/// Return number of fields in result set.
202
204{
205 return fNumPars;
206}
207
208////////////////////////////////////////////////////////////////////////////////
209/// Returns field name in result set.
210
212{
213 if (!IsResultSetMode() || (nfield < 0) || (nfield >= sqlite3_column_count(fStmt->fRes))) {
214 return 0;
215 }
216
217 return sqlite3_column_name(fStmt->fRes, nfield);
218}
219
220////////////////////////////////////////////////////////////////////////////////
221/// Shift cursor to next row in result set.
222
224{
225 ClearError();
226
227 if ((fStmt == 0) || !IsResultSetMode()) return kFALSE;
228
229 if (fIterationCount == 0) {
230 // The interface says user should call NextResultRow() before getting any data,
231 // this makes no sense at least for SQLite.
232 // We just return kTRUE here and only do something on second request.
234 return kTRUE;
235 }
236
237 return Process();
238}
239
240////////////////////////////////////////////////////////////////////////////////
241/// Increment iteration counter for statement, where parameter can be set.
242/// Statement with parameters of previous iteration
243/// automatically will be applied to database.
244/// Actually a NO-OP for SQLite, as parameters stay bound when step-ping.
245
247{
248 ClearError();
249
250 if (!IsSetParsMode()) {
251 SetError(-1, "Cannot call for that statement", "NextIteration");
252 return kFALSE;
253 }
254
255 if (fIterationCount == 0) {
256 // The interface says user should call NextIteration() before binding any parameters,
257 // this makes no sense at least for SQLite.
258 // We just return kTRUE here and wait for data to really do something.
260 return kTRUE;
261 }
262
264
265 return Process();
266}
267
268////////////////////////////////////////////////////////////////////////////////
269/// Convert field value to string.
270
272{
273 CheckGetField("ConvertToString", "");
274
275 return reinterpret_cast<const char *>(sqlite3_column_text(fStmt->fRes, npar));
276}
277
278////////////////////////////////////////////////////////////////////////////////
279/// Convert field to numeric.
280
282{
283 CheckGetField("ConvertToNumeric", -1);
284
285 return (long double) sqlite3_column_double(fStmt->fRes, npar);
286}
287
288////////////////////////////////////////////////////////////////////////////////
289/// Checks if field value is null.
290
292{
293 CheckGetField("IsNull", kFALSE);
294
295 return (sqlite3_column_type(fStmt->fRes, npar) == SQLITE_NULL);
296}
297
298////////////////////////////////////////////////////////////////////////////////
299/// Get integer.
300
302{
303 CheckGetField("GetInt", -1);
304
305 return (Int_t) sqlite3_column_int(fStmt->fRes, npar);
306}
307
308////////////////////////////////////////////////////////////////////////////////
309/// Get unsigned integer.
310
312{
313 CheckGetField("GetUInt", 0);
314
315 return (UInt_t) sqlite3_column_int(fStmt->fRes, npar);
316}
317
318////////////////////////////////////////////////////////////////////////////////
319/// Get long.
320
322{
323 CheckGetField("GetLong", -1);
324
325 return (Long_t) sqlite3_column_int64(fStmt->fRes, npar);
326}
327
328////////////////////////////////////////////////////////////////////////////////
329/// Get long64.
330
332{
333 CheckGetField("GetLong64", -1);
334
335 return (Long64_t) sqlite3_column_int64(fStmt->fRes, npar);
336}
337
338////////////////////////////////////////////////////////////////////////////////
339/// Return field value as unsigned 64-bit integer
340
342{
343 CheckGetField("GetULong64", 0);
344
345 return (ULong64_t) sqlite3_column_int64(fStmt->fRes, npar);
346}
347
348////////////////////////////////////////////////////////////////////////////////
349/// Return field value as double.
350
352{
353 CheckGetField("GetDouble", -1);
354
355 return (Double_t) sqlite3_column_double(fStmt->fRes, npar);
356}
357
358////////////////////////////////////////////////////////////////////////////////
359/// Return field value as string.
360
362{
363 CheckGetField("GetString", "");
364
365 return reinterpret_cast<const char *>(sqlite3_column_text(fStmt->fRes, npar));
366}
367
368////////////////////////////////////////////////////////////////////////////////
369/// Return field value as binary array.
370/// Memory at 'mem' will be reallocated and size updated
371/// to fit the data if not large enough.
372
374{
375 CheckGetField("GetBinary", kFALSE);
376
377 // As we retrieve "as blob", we do NOT call sqlite3_column_text() before
378 // sqlite3_column_bytes(), which might leave us with a non-zero terminated
379 // data struture, but this should be okay for BLOB.
380 size_t sz = sqlite3_column_bytes(fStmt->fRes, npar);
381 if ((Long_t)sz > size) {
382 delete [](unsigned char*) mem;
383 mem = (void*) new unsigned char[sz];
384 }
385 size = sz;
386
387 memcpy(mem, sqlite3_column_blob(fStmt->fRes, npar), sz);
388
389 return kTRUE;
390}
391
392////////////////////////////////////////////////////////////////////////////////
393/// Return field value as date.
394
396{
397 CheckGetField("GetDate", kFALSE);
398
399 TString val = reinterpret_cast<const char*>(sqlite3_column_text(fStmt->fRes, npar));
400 TDatime d = TDatime(val.Data());
401 year = d.GetYear();
402 month = d.GetMonth();
403 day = d.GetDay();
404
405 return kTRUE;
406}
407
408////////////////////////////////////////////////////////////////////////////////
409/// Return field as time.
410
412{
413 CheckGetField("GetTime", kFALSE);
414
415 TString val = reinterpret_cast<const char*>(sqlite3_column_text(fStmt->fRes, npar));
416 TDatime d = TDatime(val.Data());
417 hour = d.GetHour();
418 min = d.GetMinute();
419 sec = d.GetSecond();
420
421 return kTRUE;
422}
423
424////////////////////////////////////////////////////////////////////////////////
425/// Return field value as date & time.
426
427Bool_t TSQLiteStatement::GetDatime(Int_t npar, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
428{
429 CheckGetField("GetDatime", kFALSE);
430
431 TString val = reinterpret_cast<const char*>(sqlite3_column_text(fStmt->fRes, npar));
432 TDatime d = TDatime(val.Data());
433 year = d.GetYear();
434 month = d.GetMonth();
435 day = d.GetDay();
436 hour = d.GetHour();
437 min = d.GetMinute();
438 sec = d.GetSecond();
439
440 return kTRUE;
441}
442
443////////////////////////////////////////////////////////////////////////////////
444/// Return field as timestamp.
445/// Second fraction is in milliseconds, which is also the precision all date and time functions of sqlite use.
446
447Bool_t TSQLiteStatement::GetTimestamp(Int_t npar, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec, Int_t& frac)
448{
449 CheckGetField("GetTimestamp", kFALSE);
450
451 TString val = reinterpret_cast<const char*>(sqlite3_column_text(fStmt->fRes, npar));
452
453 Ssiz_t p = val.Last('.');
454 TSubString ts_part = val(0, p);
455
456 TDatime d(ts_part.Data());
457 year = d.GetYear();
458 month = d.GetMonth();
459 day = d.GetDay();
460 hour = d.GetHour();
461 min = d.GetMinute();
462 sec = d.GetSecond();
463
464 TSubString s_frac = val(p, val.Length() - p+1);
465 frac=(Int_t) (atof(s_frac.Data())*1.E3);
466
467 return kTRUE;
468}
469
470////////////////////////////////////////////////////////////////////////////////
471/// Set NULL as parameter value.
472
474{
475 int res = sqlite3_bind_null(fStmt->fRes, npar + 1);
476
477 return CheckBindError("SetNull", res);
478}
479
480////////////////////////////////////////////////////////////////////////////////
481/// Set parameter value as integer.
482
484{
485 int res = sqlite3_bind_int(fStmt->fRes, npar + 1, value);
486
487 return CheckBindError("SetInt", res);
488}
489
490////////////////////////////////////////////////////////////////////////////////
491/// Set parameter value as unsigned integer.
492/// Actually casted to signed integer, has to be re-casted upon read!
493
495{
496 int res = sqlite3_bind_int(fStmt->fRes, npar + 1, (Int_t)value);
497
498 return CheckBindError("SetUInt", res);
499}
500
501////////////////////////////////////////////////////////////////////////////////
502/// Set parameter value as long.
503
505{
506 int res = sqlite3_bind_int64(fStmt->fRes, npar + 1, value);
507
508 return CheckBindError("SetLong", res);
509}
510
511////////////////////////////////////////////////////////////////////////////////
512/// Set parameter value as 64-bit integer.
513
515{
516 int res = sqlite3_bind_int64(fStmt->fRes, npar + 1, value);
517
518 return CheckBindError("SetLong64", res);
519}
520
521////////////////////////////////////////////////////////////////////////////////
522/// Set parameter value as unsigned 64-bit integer.
523/// Actually casted to signed integer, has to be re-casted upon read!
524
526{
527 int res = sqlite3_bind_int64(fStmt->fRes, npar + 1, (Long64_t)value);
528
529 return CheckBindError("SetULong64", res);
530}
531
532////////////////////////////////////////////////////////////////////////////////
533/// Set parameter value as double value.
534
536{
537 int res = sqlite3_bind_double(fStmt->fRes, npar + 1, value);
538
539 return CheckBindError("SetDouble", res);
540}
541
542////////////////////////////////////////////////////////////////////////////////
543/// Set parameter value as string.
544
545Bool_t TSQLiteStatement::SetString(Int_t npar, const char* value, Int_t maxsize)
546{
547 int res = sqlite3_bind_text(fStmt->fRes, npar + 1, value, maxsize, SQLITE_TRANSIENT);
548
549 return CheckBindError("SetString", res);
550}
551
552////////////////////////////////////////////////////////////////////////////////
553/// Set parameter value as binary data.
554/// Maxsize is ignored for SQLite, we directly insert BLOB of size 'size'.
555/// Negative size would cause undefined behaviour, so we refuse that.
556
557Bool_t TSQLiteStatement::SetBinary(Int_t npar, void* mem, Long_t size, Long_t /*maxsize*/)
558{
559 if (size < 0) {
560 SetError(-1, "Passing negative value to size for BLOB to SQLite would cause undefined behaviour, refusing it!", "SetBinary");
561 return kFALSE;
562 }
563
564 int res = sqlite3_bind_blob(fStmt->fRes, npar + 1, mem, (size_t)size, SQLITE_TRANSIENT);
565
566 return CheckBindError("SetBinary", res);
567}
568
569////////////////////////////////////////////////////////////////////////////////
570/// Set parameter value as date.
571
573{
574 TDatime d = TDatime(year, month, day, 0, 0, 0);
575 int res = sqlite3_bind_text(fStmt->fRes, npar + 1, (char*)d.AsSQLString(), -1, SQLITE_TRANSIENT);
576
577 return CheckBindError("SetDate", res);
578}
579
580////////////////////////////////////////////////////////////////////////////////
581/// Set parameter value as time.
582
584{
585 TDatime d = TDatime(2000, 1, 1, hour, min, sec);
586
587 int res = sqlite3_bind_text(fStmt->fRes, npar + 1, (char*)d.AsSQLString(), -1, SQLITE_TRANSIENT);
588
589 return CheckBindError("SetTime", res);
590}
591
592////////////////////////////////////////////////////////////////////////////////
593/// Set parameter value as date & time.
594
595Bool_t TSQLiteStatement::SetDatime(Int_t npar, Int_t year, Int_t month, Int_t day, Int_t hour, Int_t min, Int_t sec)
596{
597 TDatime d = TDatime(year, month, day, hour, min, sec);
598
599 int res = sqlite3_bind_text(fStmt->fRes, npar + 1, (char*)d.AsSQLString(), -1, SQLITE_TRANSIENT);
600
601 return CheckBindError("SetDatime", res);
602}
603
604////////////////////////////////////////////////////////////////////////////////
605/// Set parameter value as timestamp.
606/// The second fraction has to be in milliseconds,
607/// as all SQLite functions for date and time assume 3 significant digits.
608
609Bool_t TSQLiteStatement::SetTimestamp(Int_t npar, Int_t year, Int_t month, Int_t day, Int_t hour, Int_t min, Int_t sec, Int_t frac)
610{
611 TDatime d(year,month,day,hour,min,sec);
612 TString value;
613 value.Form("%s.%03d", (char*)d.AsSQLString(), frac);
614
615 int res = sqlite3_bind_text(fStmt->fRes, npar + 1, value.Data(), -1, SQLITE_TRANSIENT);
616
617 return CheckBindError("SetTimestamp", res);
618}
#define d(i)
Definition: RSha256.hxx:102
int Int_t
Definition: RtypesCore.h:41
int Ssiz_t
Definition: RtypesCore.h:63
unsigned int UInt_t
Definition: RtypesCore.h:42
const Bool_t kFALSE
Definition: RtypesCore.h:88
long Long_t
Definition: RtypesCore.h:50
bool Bool_t
Definition: RtypesCore.h:59
double Double_t
Definition: RtypesCore.h:55
long long Long64_t
Definition: RtypesCore.h:69
unsigned long long ULong64_t
Definition: RtypesCore.h:70
const Bool_t kTRUE
Definition: RtypesCore.h:87
const char Option_t
Definition: RtypesCore.h:62
#define ClassImp(name)
Definition: Rtypes.h:365
#define CheckErrNo(method, force, res)
#define CheckGetField(method, res)
#define CheckStmt(method, res)
char * Form(const char *fmt,...)
This class stores the date and time with a precision of one second in an unsigned 32 bit word (950130...
Definition: TDatime.h:37
void SetError(Int_t code, const char *msg, const char *method=0)
set new values for error fields if method specified, displays error message
void ClearError()
reset error fields
virtual Bool_t SetNull(Int_t npar)
Set NULL as parameter value.
virtual Long_t GetLong(Int_t npar)
Get long.
virtual Bool_t GetTimestamp(Int_t npar, Int_t &year, Int_t &month, Int_t &day, Int_t &hour, Int_t &min, Int_t &sec, Int_t &)
Return field as timestamp.
virtual Double_t GetDouble(Int_t npar)
Return field value as double.
virtual Bool_t SetULong64(Int_t npar, ULong64_t value)
Set parameter value as unsigned 64-bit integer.
virtual Bool_t SetString(Int_t npar, const char *value, Int_t maxsize=256)
Set parameter value as string.
virtual UInt_t GetUInt(Int_t npar)
Get unsigned integer.
virtual Bool_t SetDate(Int_t npar, Int_t year, Int_t month, Int_t day)
Set parameter value as date.
virtual Bool_t IsNull(Int_t npar)
Checks if field value is null.
virtual Bool_t NextIteration()
Increment iteration counter for statement, where parameter can be set.
virtual ~TSQLiteStatement()
Destructor.
SQLite3_Stmt_t * fStmt
virtual Bool_t SetDatime(Int_t npar, Int_t year, Int_t month, Int_t day, Int_t hour, Int_t min, Int_t sec)
Set parameter value as date & time.
virtual const char * GetFieldName(Int_t nfield)
Returns field name in result set.
virtual Bool_t GetDatime(Int_t npar, Int_t &year, Int_t &month, Int_t &day, Int_t &hour, Int_t &min, Int_t &sec)
Return field value as date & time.
Int_t fIterationCount
Number of bindable / gettable parameters.
virtual Bool_t SetUInt(Int_t npar, UInt_t value)
Set parameter value as unsigned integer.
virtual Bool_t NextResultRow()
Shift cursor to next row in result set.
virtual Long64_t GetLong64(Int_t npar)
Get long64.
Bool_t CheckBindError(const char *method, int res)
virtual Bool_t SetLong(Int_t npar, Long_t value)
Set parameter value as long.
virtual Bool_t SetLong64(Int_t npar, Long64_t value)
Set parameter value as 64-bit integer.
virtual Int_t GetNumAffectedRows()
Return number of affected rows after statement is processed.
virtual Int_t GetNumFields()
Return number of fields in result set.
const char * ConvertToString(Int_t npar)
Convert field value to string.
Int_t fWorkingMode
executed statement
virtual Bool_t SetBinary(Int_t npar, void *mem, Long_t size, Long_t maxsize=0x1000)
Set parameter value as binary data.
Bool_t IsSetParsMode() const
Iteration count.
virtual void Close(Option_t *="")
Close statement.
virtual Bool_t GetBinary(Int_t npar, void *&mem, Long_t &size)
Return field value as binary array.
virtual Bool_t Process()
Process statement.
virtual Bool_t SetTimestamp(Int_t npar, Int_t year, Int_t month, Int_t day, Int_t hour, Int_t min, Int_t sec, Int_t frac=0)
Set parameter value as timestamp.
virtual ULong64_t GetULong64(Int_t npar)
Return field value as unsigned 64-bit integer.
Int_t fNumPars
1 - setting parameters, 2 - retrieving results
Bool_t IsResultSetMode() const
virtual Bool_t GetTime(Int_t npar, Int_t &hour, Int_t &min, Int_t &sec)
Return field as time.
virtual Bool_t GetDate(Int_t npar, Int_t &year, Int_t &month, Int_t &day)
Return field value as date.
virtual const char * GetString(Int_t npar)
Return field value as string.
virtual Bool_t StoreResult()
Store result of statement processing to access them via GetInt(), GetDouble() and so on methods.
virtual Int_t GetInt(Int_t npar)
Get integer.
virtual Bool_t SetDouble(Int_t npar, Double_t value)
Set parameter value as double value.
TSQLiteStatement(SQLite3_Stmt_t *stmt, Bool_t errout=kTRUE)
Normal constructor.
long double ConvertToNumeric(Int_t npar)
Convert field to numeric.
virtual Bool_t SetTime(Int_t npar, Int_t hour, Int_t min, Int_t sec)
Set parameter value as time.
virtual Int_t GetNumParameters()
Return number of statement parameters.
virtual Bool_t SetInt(Int_t npar, Int_t value)
Set parameter value as integer.
Basic string class.
Definition: TString.h:131
Ssiz_t Length() const
Definition: TString.h:405
const char * Data() const
Definition: TString.h:364
Ssiz_t Last(char c) const
Find last occurrence of a character c.
Definition: TString.cxx:892
void Form(const char *fmt,...)
Formats a string using a printf style format descriptor.
Definition: TString.cxx:2289
A zero length substring is legal.
Definition: TString.h:77
const char * Data() const
Definition: TString.h:721
sqlite3_stmt * fRes