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