Logo ROOT  
Reference Guide
 
Loading...
Searching...
No Matches
TPgSQLStatement.cxx
Go to the documentation of this file.
1// @(#)root/pgsql:$Id$
2// Author: Dennis Box (dbox@fnal.gov) 3/12/2007
3
4/*************************************************************************
5 * Copyright (C) 1995-2007, 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 PgSQL //
15// //
16// See TSQLStatement class documentation for more details. //
17// //
18//////////////////////////////////////////////////////////////////////////
19
20#include "TPgSQLStatement.h"
21#include "TDataType.h"
22#include "TDatime.h"
23#include "TTimeStamp.h"
24#include "TMath.h"
25#include "strlcpy.h"
26#include "snprintf.h"
27
28#include <cstdlib>
29
30#include <pg_config.h> // to get PG_VERSION_NUM
31
32#define pgsql_success(x) (((x) == PGRES_EMPTY_QUERY) \
33 || ((x) == PGRES_COMMAND_OK) \
34 || ((x) == PGRES_TUPLES_OK))
35
36#include <libpq-fe.h>
37
39
40#ifdef PG_VERSION_NUM
41
42#include "libpq/libpq-fs.h"
43
44static const Int_t kBindStringSize = 30; // big enough to handle text rep. of 64 bit number and timestamp (e.g. "1970-01-01 01:01:01.111111+00")
45
46////////////////////////////////////////////////////////////////////////////////
47/// Normal constructor.
48/// Checks if statement contains parameters tags.
49
51 TSQLStatement(errout),
52 fStmt(stmt),
53 fNumBuffers(0),
54 fBind(nullptr),
55 fFieldName(nullptr),
56 fWorkingMode(0),
57 fIterationCount(0),
58 fParamLengths(nullptr),
59 fParamFormats(nullptr),
60 fNumResultRows(0),
61 fNumResultCols(0)
62{
63 // Given fRes not used, we retrieve the statement using the connection.
64 if (fStmt->fRes != nullptr) {
65 PQclear(fStmt->fRes);
66 }
67
68 fStmt->fRes = PQdescribePrepared(fStmt->fConn,"preparedstmt");
69 unsigned long paramcount = PQnparams(fStmt->fRes);
70 fNumResultCols = PQnfields(fStmt->fRes);
71 fIterationCount = -1;
72
73 if (paramcount>0) {
74 fWorkingMode = 1;
75 SetBuffersNumber(paramcount);
76 } else {
77 fWorkingMode = 2;
78 SetBuffersNumber(fNumResultCols);
79 }
80}
81
82////////////////////////////////////////////////////////////////////////////////
83/// Destructor.
84
86{
87 Close();
88}
89
90////////////////////////////////////////////////////////////////////////////////
91/// Close statement.
92
94{
95 if (fStmt->fRes)
96 PQclear(fStmt->fRes);
97
98 fStmt->fRes = nullptr;
99
100 PGresult *res=PQexec(fStmt->fConn,"DEALLOCATE preparedstmt;");
101 PQclear(res);
102
103 FreeBuffers();
104 //TPgSQLServers responsibility to free connection
105 fStmt->fConn = nullptr;
106 delete fStmt;
107}
108
109
110// Reset error and check that statement exists
111#define CheckStmt(method, res) \
112 { \
113 ClearError(); \
114 if (!fStmt) { \
115 SetError(-1,"Statement handle is 0",method); \
116 return res; \
117 } \
118 }
119
120#define CheckErrNo(method, force, wtf) \
121 { \
122 int stmterrno = PQresultStatus(fStmt->fRes); \
123 if ((stmterrno!=0) || force) { \
124 const char* stmterrmsg = PQresultErrorMessage(fStmt->fRes); \
125 if (stmterrno==0) { stmterrno = -1; stmterrmsg = "PgSQL statement error"; } \
126 SetError(stmterrno, stmterrmsg, method); \
127 return wtf; \
128 } \
129 }
130
131#define CheckErrResult(method, pqresult, retVal) \
132 { \
133 ExecStatusType stmterrno=PQresultStatus(pqresult); \
134 if (!pgsql_success(stmterrno)) { \
135 const char* stmterrmsg = PQresultErrorMessage(fStmt->fRes); \
136 SetError(stmterrno, stmterrmsg, method); \
137 PQclear(res); \
138 return retVal; \
139 } \
140 }
141
142#define RollBackTransaction(method) \
143 { \
144 PGresult *resnum=PQexec(fStmt->fConn,"COMMIT"); \
145 CheckErrResult("RollBackTransaction", resnum, kFALSE); \
146 PQclear(res); \
147 }
148
149// check last pgsql statement error code
150#define CheckGetField(method, res) \
151 { \
152 ClearError(); \
153 if (!IsResultSetMode()) { \
154 SetError(-1,"Cannot get statement parameters",method); \
155 return res; \
156 } \
157 if ((npar<0) || (npar>=fNumBuffers)) { \
158 SetError(-1,Form("Invalid parameter number %d", npar),method); \
159 return res; \
160 } \
161 }
162
163////////////////////////////////////////////////////////////////////////////////
164/// Process statement.
165
167{
168 CheckStmt("Process",kFALSE);
169
170 // We create the prepared statement below, MUST delete the old one
171 // from our constructor first!
172 if (fStmt->fRes != NULL) {
173 PQclear(fStmt->fRes);
174 }
175
176 if (IsSetParsMode()) {
177 fStmt->fRes= PQexecPrepared(fStmt->fConn,"preparedstmt",fNumBuffers,
178 (const char* const*)fBind,
181 0);
182
183 } else { //result set mode
184 fStmt->fRes= PQexecPrepared(fStmt->fConn,"preparedstmt",0,(const char* const*) nullptr, nullptr, nullptr,0);
185 }
186 ExecStatusType stat = PQresultStatus(fStmt->fRes);
187 if (!pgsql_success(stat))
188 CheckErrNo("Process",kTRUE, kFALSE);
189 return kTRUE;
190}
191
192////////////////////////////////////////////////////////////////////////////////
193/// Return number of affected rows after statement is processed.
194
196{
197 CheckStmt("GetNumAffectedRows", -1);
198
199 return (Int_t) atoi(PQcmdTuples(fStmt->fRes));
200}
201
202////////////////////////////////////////////////////////////////////////////////
203/// Return number of statement parameters.
204
206{
207 CheckStmt("GetNumParameters", -1);
208
209 if (IsSetParsMode()) {
210 return fNumBuffers;
211 } else {
212 return 0;
213 }
214}
215
216////////////////////////////////////////////////////////////////////////////////
217/// Store result of statement processing to access them
218/// via GetInt(), GetDouble() and so on methods.
219
221{
222 int i;
223 for (i=0;i<fNumResultCols;i++){
224 fFieldName[i] = PQfname(fStmt->fRes,i);
225 fParamFormats[i]=PQftype(fStmt->fRes,i);
226 fParamLengths[i]=PQfsize(fStmt->fRes,i);
227
228 }
229 fNumResultRows=PQntuples(fStmt->fRes);
230 ExecStatusType stat = PQresultStatus(fStmt->fRes);
231 fWorkingMode = 2;
232 if (!pgsql_success(stat))
233 CheckErrNo("StoreResult",kTRUE, kFALSE);
234 return kTRUE;
235}
236
237////////////////////////////////////////////////////////////////////////////////
238/// Return number of fields in result set.
239
241{
242 if (fWorkingMode==1)
243 return fNumBuffers;
244 if (fWorkingMode==2)
245 return fNumResultCols;
246 return -1;
247}
248
249////////////////////////////////////////////////////////////////////////////////
250/// Returns field name in result set.
251
252const char* TPgSQLStatement::GetFieldName(Int_t nfield)
253{
254 if (!IsResultSetMode() || (nfield < 0) || (nfield >= fNumBuffers)) return nullptr;
255
256 return fFieldName[nfield];
257}
258
259////////////////////////////////////////////////////////////////////////////////
260/// Shift cursor to nect row in result set.
261
263{
264 if (!fStmt || !IsResultSetMode()) return kFALSE;
265
266 Bool_t res = kTRUE;
267
270 res = kFALSE;
271 return res;
272}
273
274////////////////////////////////////////////////////////////////////////////////
275/// Increment iteration counter for statement, where parameter can be set.
276/// Statement with parameters of previous iteration
277/// automatically will be applied to database.
278
280{
281 ClearError();
282
283 if (!IsSetParsMode() || !fBind) {
284 SetError(-1,"Cannot call for that statement","NextIteration");
285 return kFALSE;
286 }
287
289
290 if (fIterationCount==0) return kTRUE;
291
292 fStmt->fRes= PQexecPrepared(fStmt->fConn,"preparedstmt",fNumBuffers,
293 (const char* const*)fBind,
296 0);
297 ExecStatusType stat = PQresultStatus(fStmt->fRes);
298 if (!pgsql_success(stat) ){
299 CheckErrNo("NextIteration", kTRUE, kFALSE) ;
300 return kFALSE;
301 }
302 return kTRUE;
303}
304
305////////////////////////////////////////////////////////////////////////////////
306/// Release all buffers, used by statement.
307
309{
310 //individual field names free()'ed by PQclear of fStmt->fRes
311 if (fFieldName)
312 delete[] fFieldName;
313
314 if (fBind){
315 for (Int_t i=0;i<fNumBuffers;i++)
316 delete [] fBind[i];
317 delete[] fBind;
318 }
319
320 if (fParamLengths)
321 delete [] fParamLengths;
322
323 if (fParamFormats)
324 delete [] fParamFormats;
325
326 fFieldName = nullptr;
327 fBind = nullptr;
328 fNumBuffers = 0;
329 fParamLengths = nullptr;
330 fParamFormats = nullptr;
331}
332
333////////////////////////////////////////////////////////////////////////////////
334/// Allocate buffers for statement parameters/ result fields.
335
337{
338 FreeBuffers();
339 if (numpars<=0) return;
340
341 fNumBuffers = numpars;
342
343 fBind = new char*[fNumBuffers];
344 for(int i=0; i<fNumBuffers; ++i){
345 fBind[i] = new char[kBindStringSize];
346 }
347 fFieldName = new char*[fNumBuffers];
348
349 fParamLengths = new int[fNumBuffers];
350 memset(fParamLengths, 0, sizeof(int)*fNumBuffers);
351
352 fParamFormats = new int[fNumBuffers];
353 memset(fParamFormats, 0, sizeof(int)*fNumBuffers);
354}
355
356////////////////////////////////////////////////////////////////////////////////
357/// Convert field value to string.
358
360{
361 const char *buf = PQgetvalue(fStmt->fRes, fIterationCount, npar);
362 return buf;
363}
364
365////////////////////////////////////////////////////////////////////////////////
366/// Convert field to numeric.
367
369{
370 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
371 return (long double)0;
372
373 return (long double) atof(PQgetvalue(fStmt->fRes,fIterationCount,npar));
374}
375
376////////////////////////////////////////////////////////////////////////////////
377/// Checks if field value is null.
378
380{
381 CheckGetField("IsNull", kTRUE);
382
383 return PQgetisnull(fStmt->fRes,fIterationCount,npar);
384}
385
386////////////////////////////////////////////////////////////////////////////////
387/// Get integer.
388
390{
391 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
392 return (Int_t)0;
393
394 return (Int_t) atoi(PQgetvalue(fStmt->fRes,fIterationCount,npar));
395}
396
397////////////////////////////////////////////////////////////////////////////////
398/// Get unsigned integer.
399
401{
402 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
403 return (UInt_t)0;
404
405 return (UInt_t) atoi(PQgetvalue(fStmt->fRes,fIterationCount,npar));
406}
407
408////////////////////////////////////////////////////////////////////////////////
409/// Get long.
410
412{
413 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
414 return (Long_t)0;
415
416 return (Long_t) atol(PQgetvalue(fStmt->fRes,fIterationCount,npar));
417}
418
419////////////////////////////////////////////////////////////////////////////////
420/// Get long64.
421
423{
424 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
425 return (Long64_t)0;
426
427#ifndef R__WIN32
428 return (Long64_t) atoll(PQgetvalue(fStmt->fRes,fIterationCount,npar));
429#else
430 return (Long64_t) _atoi64(PQgetvalue(fStmt->fRes,fIterationCount,npar));
431#endif
432}
433
434////////////////////////////////////////////////////////////////////////////////
435/// Return field value as unsigned 64-bit integer
436
438{
439 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
440 return (ULong64_t)0;
441
442#ifndef R__WIN32
443 return (ULong64_t) atoll(PQgetvalue(fStmt->fRes,fIterationCount,npar));
444#else
445 return (ULong64_t) _atoi64(PQgetvalue(fStmt->fRes,fIterationCount,npar));
446#endif
447}
448
449////////////////////////////////////////////////////////////////////////////////
450/// Return field value as double.
451
453{
454 if (PQgetisnull(fStmt->fRes,fIterationCount,npar))
455 return (Double_t)0;
456 return (Double_t) atof(PQgetvalue(fStmt->fRes,fIterationCount,npar));
457}
458
459////////////////////////////////////////////////////////////////////////////////
460/// Return field value as string.
461
462const char *TPgSQLStatement::GetString(Int_t npar)
463{
464 return PQgetvalue(fStmt->fRes,fIterationCount,npar);
465}
466
467////////////////////////////////////////////////////////////////////////////////
468/// Return field value as binary array.
469/// Note PQgetvalue mallocs/frees and ROOT classes expect new/delete.
470
472{
473 size_t sz;
474 char *cptr = PQgetvalue(fStmt->fRes,fIterationCount,npar);
475 unsigned char * mptr = PQunescapeBytea((const unsigned char*)cptr,&sz);
476 if ((Long_t)sz>size) {
477 delete [] (unsigned char*) mem;
478 mem = (void*) new unsigned char[sz];
479 }
480 size=sz;
481 memcpy(mem,mptr,sz);
482 PQfreemem(mptr);
483 return kTRUE;
484}
485
486////////////////////////////////////////////////////////////////////////////////
487/// Return large object whose oid is in the given field.
488
490{
491 Int_t objID = atoi(PQgetvalue(fStmt->fRes,fIterationCount,npar));
492
493 // All this needs to happen inside a transaction, or it will NOT work.
494 PGresult *res=PQexec(fStmt->fConn,"BEGIN");
495
496 CheckErrResult("GetLargeObject", res, kFALSE);
497 PQclear(res);
498
499 Int_t lObjFD = lo_open(fStmt->fConn, objID, INV_READ);
500
501 if (lObjFD<0) {
502 Error("GetLargeObject", "SQL Error on lo_open: %s", PQerrorMessage(fStmt->fConn));
503 RollBackTransaction("GetLargeObject");
504 return kFALSE;
505 }
506 // Object size is not known beforehand.
507 // Possible fast ways to get it are:
508 // (1) Create a function that does fopen, fseek, ftell on server
509 // (2) Query large object table with size()
510 // Both can not be expected to work in general,
511 // as (1) needs permissions and changes DB,
512 // and (2) needs permission.
513 // So we use
514 // (3) fopen, fseek and ftell locally.
515
516 lo_lseek(fStmt->fConn, lObjFD, 0, SEEK_END);
517 Long_t sz = lo_tell(fStmt->fConn, lObjFD);
518 lo_lseek(fStmt->fConn, lObjFD, 0, SEEK_SET);
519
520 if ((Long_t)sz>size) {
521 delete [] (unsigned char*) mem;
522 mem = (void*) new unsigned char[sz];
523 size=sz;
524 }
525
526 Int_t readBytes = lo_read(fStmt->fConn, lObjFD, (char*)mem, size);
527
528 if (readBytes != sz) {
529 Error("GetLargeObject", "SQL Error on lo_read: %s", PQerrorMessage(fStmt->fConn));
530 RollBackTransaction("GetLargeObject");
531 return kFALSE;
532 }
533
534 if (lo_close(fStmt->fConn, lObjFD) != 0) {
535 Error("GetLargeObject", "SQL Error on lo_close: %s", PQerrorMessage(fStmt->fConn));
536 RollBackTransaction("GetLargeObject");
537 return kFALSE;
538 }
539
540 res=PQexec(fStmt->fConn,"COMMIT");
541
542 ExecStatusType stat = PQresultStatus(res);
543 if (!pgsql_success(stat)) {
544 Error("GetLargeObject", "SQL Error on COMMIT: %s", PQerrorMessage(fStmt->fConn));
545 RollBackTransaction("GetLargeObject");
546 return kFALSE;
547 }
548 PQclear(res);
549
550 return kTRUE;
551}
552
553////////////////////////////////////////////////////////////////////////////////
554/// Return field value as date, in UTC.
555
556Bool_t TPgSQLStatement::GetDate(Int_t npar, Int_t& year, Int_t& month, Int_t& day)
557{
558 TString val=PQgetvalue(fStmt->fRes,fIterationCount,npar);
559 TDatime d = TDatime(val.Data());
560 year = d.GetYear();
561 month = d.GetMonth();
562 day= d.GetDay();
563 Int_t hour = d.GetHour();
564 Int_t min = d.GetMinute();
565 Int_t sec = d.GetSecond();
566 ConvertTimeToUTC(val, year, month, day, hour, min, sec);
567 return kTRUE;
568}
569
570////////////////////////////////////////////////////////////////////////////////
571/// Return field as time, in UTC.
572
573Bool_t TPgSQLStatement::GetTime(Int_t npar, Int_t& hour, Int_t& min, Int_t& sec)
574{
575 TString val=PQgetvalue(fStmt->fRes,fIterationCount,npar);
576 TDatime d = TDatime(val.Data());
577 hour = d.GetHour();
578 min = d.GetMinute();
579 sec= d.GetSecond();
580 Int_t year = d.GetYear();
581 Int_t month = d.GetMonth();
582 Int_t day = d.GetDay();
583 ConvertTimeToUTC(val, year, month, day, hour, min, sec);
584 return kTRUE;
585}
586
587////////////////////////////////////////////////////////////////////////////////
588/// Return field value as date & time, in UTC.
589
590Bool_t TPgSQLStatement::GetDatime(Int_t npar, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
591{
592 TString val=PQgetvalue(fStmt->fRes,fIterationCount,npar);
593 TDatime d = TDatime(val.Data());
594 year = d.GetYear();
595 month = d.GetMonth();
596 day= d.GetDay();
597 hour = d.GetHour();
598 min = d.GetMinute();
599 sec= d.GetSecond();
600 ConvertTimeToUTC(val, year, month, day, hour, min, sec);
601 return kTRUE;
602}
603
604////////////////////////////////////////////////////////////////////////////////
605/// Convert timestamp value to UTC if a zone is included.
606
607void TPgSQLStatement::ConvertTimeToUTC(const TString &PQvalue, Int_t& year, Int_t& month, Int_t& day, Int_t& hour, Int_t& min, Int_t& sec)
608{
609 Ssiz_t p = PQvalue.Last(':');
610 // Check if timestamp has timezone
611 TSubString *s_zone = nullptr;
612 Bool_t hasZone = kFALSE;
613 Ssiz_t tzP = PQvalue.Last('+');
614 if ((tzP != kNPOS) && (tzP > p) ) {
615 s_zone = new TSubString(PQvalue(tzP+1,PQvalue.Length()-tzP));
616 hasZone=kTRUE;
617 } else {
618 Ssiz_t tzM = PQvalue.Last('-');
619 if ((tzM != kNPOS) && (tzM > p) ) {
620 s_zone = new TSubString(PQvalue(tzM+1,PQvalue.Length()-tzM));
621 hasZone = kTRUE;
622 }
623 }
624 if (hasZone == kTRUE) {
625 // Parse timezone, might look like e.g. +00 or -00:00
626 Int_t hourOffset, minuteOffset = 0;
627 Int_t conversions=sscanf(s_zone->Data(), "%2d:%2d", &hourOffset, &minuteOffset);
628 Int_t secondOffset = hourOffset*3600;
629 if (conversions>1) {
630 // Use sign from hour also for minute
631 secondOffset += (TMath::Sign(minuteOffset, hourOffset))*60;
632 }
633 // Use TTimeStamp so we do not have to take care of over-/underflows
634 TTimeStamp ts(year, month, day, hour, min, sec, 0, kTRUE, -secondOffset);
635 UInt_t uyear, umonth, uday, uhour, umin, usec;
636 ts.GetDate(kTRUE, 0, &uyear, &umonth, &uday);
637 ts.GetTime(kTRUE, 0, &uhour, &umin, &usec);
638 year=uyear;
639 month=umonth;
640 day=uday;
641 hour=uhour;
642 min=umin;
643 sec=usec;
644 delete s_zone;
645 }
646}
647
648////////////////////////////////////////////////////////////////////////////////
649/// Return field as timestamp, in UTC.
650/// Second fraction is to be interpreted as in the following example:
651/// 2013-01-12 12:10:23.093854+02
652/// Fraction is '93854', precision is fixed in this method to 6 decimal places.
653/// This means the returned frac-value is always in microseconds.
654
655Bool_t TPgSQLStatement::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)
656{
657 TString val=PQgetvalue(fStmt->fRes,fIterationCount,npar);
658 TDatime d(val.Data());
659 year = d.GetYear();
660 month = d.GetMonth();
661 day= d.GetDay();
662 hour = d.GetHour();
663 min = d.GetMinute();
664 sec= d.GetSecond();
665
666 ConvertTimeToUTC(val, year, month, day, hour, min, sec);
667
668 Ssiz_t p = val.Last('.');
669 TSubString s_frac = val(p,val.Length()-p+1);
670
671 // atoi ignores timezone part.
672 // We MUST use atof here to correctly convert the fraction of
673 // "12:23:01.093854" and put a limitation on precision,
674 // as we can only return an Int_t.
675 frac=(Int_t) (atof(s_frac.Data())*1.E6);
676
677 return kTRUE;
678}
679
680////////////////////////////////////////////////////////////////////////////////
681/// Return value of parameter in form of TTimeStamp
682/// Be aware, that TTimeStamp does not allow dates before 1970-01-01
683
685{
686 Int_t year, month, day, hour, min, sec, microsec;
687 GetTimestamp(npar, year, month, day, hour, min, sec, microsec);
688
689 if (year < 1970) {
690 SetError(-1, "Date before year 1970 does not supported by TTimeStamp type", "GetTimestamp");
691 return kFALSE;
692 }
693
694 tm.Set(year, month, day, hour, min, sec, microsec*1000, kTRUE, 0);
695
696 return kTRUE;
697}
698
699////////////////////////////////////////////////////////////////////////////////
700/// Set parameter type to be used as buffer.
701/// Also verifies parameter index and memory allocation
702
703Bool_t TPgSQLStatement::SetSQLParamType(Int_t npar, Bool_t isbinary, Int_t param_len, Int_t maxsize)
704{
705 if ((npar < 0) || (npar >= fNumBuffers)) return kFALSE;
706
707 if (maxsize < 0) {
708 if (fBind[npar]) delete [] fBind[npar];
709 fBind[npar] = nullptr;
710 } else if (maxsize > kBindStringSize) {
711 if (fBind[npar]) delete [] fBind[npar];
712 fBind[npar] = new char[maxsize];
713 } else if (!fBind[npar]) {
714 fBind[npar] = new char[kBindStringSize];
715 }
716 fParamFormats[npar] = isbinary ? 1 : 0;
717 fParamLengths[npar] = isbinary ? param_len : 0;
718
719 return kTRUE;
720}
721
722////////////////////////////////////////////////////////////////////////////////
723/// Set NULL as parameter value.
724
726{
727 if (!SetSQLParamType(npar, kFALSE, 0, -1)) return kFALSE;
728
729 return kTRUE;
730}
731
732////////////////////////////////////////////////////////////////////////////////
733/// Set parameter value as integer.
734
736{
737 if (!SetSQLParamType(npar)) return kFALSE;
738
739 snprintf(fBind[npar],kBindStringSize,"%d",value);
740
741 return kTRUE;
742}
743
744////////////////////////////////////////////////////////////////////////////////
745/// Set parameter value as unsinged integer.
746
748{
749 if (!SetSQLParamType(npar)) return kFALSE;
750
751 snprintf(fBind[npar],kBindStringSize,"%u",value);
752
753 return kTRUE;
754}
755
756////////////////////////////////////////////////////////////////////////////////
757/// Set parameter value as long.
758
760{
761 if (!SetSQLParamType(npar)) return kFALSE;
762
763 snprintf(fBind[npar],kBindStringSize,"%ld",value);
764
765 return kTRUE;
766}
767
768////////////////////////////////////////////////////////////////////////////////
769/// Set parameter value as 64-bit integer.
770
772{
773 if (!SetSQLParamType(npar)) return kFALSE;
774
775 snprintf(fBind[npar],kBindStringSize,"%lld",(Long64_t)value);
776
777 return kTRUE;
778}
779
780////////////////////////////////////////////////////////////////////////////////
781/// Set parameter value as unsinged 64-bit integer.
782
784{
785 if (!SetSQLParamType(npar)) return kFALSE;
786
787 snprintf(fBind[npar],kBindStringSize,"%llu",(ULong64_t)value);
788
789 return kTRUE;
790}
791
792////////////////////////////////////////////////////////////////////////////////
793/// Set parameter value as double value.
794
796{
797 if (!SetSQLParamType(npar)) return kFALSE;
798
799 snprintf(fBind[npar],kBindStringSize,"%lf",value);
800
801 return kTRUE;
802}
803
804////////////////////////////////////////////////////////////////////////////////
805/// Set parameter value as string.
806
807Bool_t TPgSQLStatement::SetString(Int_t npar, const char* value, Int_t maxsize)
808{
809 if (!SetSQLParamType(npar, kFALSE, 0, maxsize)) return kFALSE;
810
811 if (fBind[npar] && value)
812 strlcpy(fBind[npar], value, (maxsize > kBindStringSize) ? maxsize : kBindStringSize);
813
814 return kTRUE;
815}
816
817////////////////////////////////////////////////////////////////////////////////
818/// Set parameter value as binary data.
819
820Bool_t TPgSQLStatement::SetBinary(Int_t npar, void *mem, Long_t size, Long_t maxsize)
821{
822 if (size > maxsize) maxsize = size;
823
824 if (!SetSQLParamType(npar, kTRUE, size, maxsize)) return kFALSE;
825
826 if (fBind[npar] && mem)
827 memcpy(fBind[npar], mem, size);
828
829 return kTRUE;
830}
831
832////////////////////////////////////////////////////////////////////////////////
833/// Set parameter value to large object and immediately insert the large object into DB.
834
835Bool_t TPgSQLStatement::SetLargeObject(Int_t npar, void* mem, Long_t size, Long_t /*maxsize*/)
836{
837 // All this needs to happen inside a transaction, or it will NOT work.
838 PGresult *res=PQexec(fStmt->fConn,"BEGIN");
839
840 CheckErrResult("GetLargeObject", res, kFALSE);
841 PQclear(res);
842
843 Int_t lObjID = lo_creat(fStmt->fConn, INV_READ | INV_WRITE);
844 if (lObjID<0) {
845 Error("SetLargeObject", "Error in SetLargeObject: %s", PQerrorMessage(fStmt->fConn));
846 RollBackTransaction("GetLargeObject");
847 return kFALSE;
848 }
849
850 Int_t lObjFD = lo_open(fStmt->fConn, lObjID, INV_READ | INV_WRITE);
851 if (lObjFD<0) {
852 Error("SetLargeObject", "Error in SetLargeObject: %s", PQerrorMessage(fStmt->fConn));
853 RollBackTransaction("GetLargeObject");
854 return kFALSE;
855 }
856
857 Int_t writtenBytes = lo_write(fStmt->fConn, lObjFD, (char*)mem, size);
858
859 if (writtenBytes != size) {
860 Error("SetLargeObject", "SQL Error on lo_write: %s", PQerrorMessage(fStmt->fConn));
861 RollBackTransaction("GetLargeObject");
862 return kFALSE;
863 }
864
865 if (lo_close(fStmt->fConn, lObjFD) != 0) {
866 Error("SetLargeObject", "SQL Error on lo_close: %s", PQerrorMessage(fStmt->fConn));
867 RollBackTransaction("GetLargeObject");
868 return kFALSE;
869 }
870
871 res=PQexec(fStmt->fConn,"COMMIT");
872 ExecStatusType stat = PQresultStatus(res);
873 if (!pgsql_success(stat)) {
874 Error("SetLargeObject", "SQL Error on COMMIT: %s", PQerrorMessage(fStmt->fConn));
875 PQclear(res);
876 return kFALSE;
877 }
878 PQclear(res);
879
880 snprintf(fBind[npar],kBindStringSize,"%d",lObjID);
881
882 return kTRUE;
883}
884
885////////////////////////////////////////////////////////////////////////////////
886/// Set parameter value as date.
887
889{
890 if (!SetSQLParamType(npar)) return kFALSE;
891
892 TDatime d(year,month,day,0,0,0);
893 snprintf(fBind[npar],kBindStringSize,"%s",(char*)d.AsSQLString());
894
895 return kTRUE;
896}
897
898////////////////////////////////////////////////////////////////////////////////
899/// Set parameter value as time.
900
902{
903 if (!SetSQLParamType(npar)) return kFALSE;
904
905 TDatime d(2000,1,1,hour,min,sec);
906 snprintf(fBind[npar],kBindStringSize,"%s",(char*)d.AsSQLString());
907 return kTRUE;
908}
909
910////////////////////////////////////////////////////////////////////////////////
911/// Set parameter value as date & time, in UTC.
912
913Bool_t TPgSQLStatement::SetDatime(Int_t npar, Int_t year, Int_t month, Int_t day, Int_t hour, Int_t min, Int_t sec)
914{
915 if (!SetSQLParamType(npar)) return kFALSE;
916
917 TDatime d(year,month,day,hour,min,sec);
918 snprintf(fBind[npar],kBindStringSize,"%s+00",(char*)d.AsSQLString());
919 return kTRUE;
920}
921
922////////////////////////////////////////////////////////////////////////////////
923/// Set parameter value as timestamp, in UTC.
924/// Second fraction is assumed as value in microseconds,
925/// i.e. as a fraction with six decimal places.
926/// See GetTimestamp() for an example.
927
928Bool_t TPgSQLStatement::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)
929{
930 if (!SetSQLParamType(npar)) return kFALSE;
931
932 TDatime d(year,month,day,hour,min,sec);
933 snprintf(fBind[npar],kBindStringSize,"%s.%06d+00",(char*)d.AsSQLString(),frac);
934 return kTRUE;
935}
936
937////////////////////////////////////////////////////////////////////////////////
938/// Set parameter value as timestamp from TTimeStamp object
939
941{
942 if (!SetSQLParamType(npar)) return kFALSE;
943
944 snprintf(fBind[npar], kBindStringSize, "%s.%06d+00", (char*)tm.AsString("s"), TMath::Nint(tm.GetNanoSec() / 1000.0));
945 return kTRUE;
946}
947
948#else
949
950////////////////////////////////////////////////////////////////////////////////
951/// Normal constructor.
952/// For PgSQL version < 8.2 no statement is supported.
953
955{
956}
957
958////////////////////////////////////////////////////////////////////////////////
959/// Destructor.
960
962{
963}
964
965////////////////////////////////////////////////////////////////////////////////
966/// Close statement.
967
969{
970}
971
972////////////////////////////////////////////////////////////////////////////////
973/// Process statement.
974
976{
977 return kFALSE;
978}
979
980////////////////////////////////////////////////////////////////////////////////
981/// Return number of affected rows after statement is processed.
982
984{
985 return 0;
986}
987
988////////////////////////////////////////////////////////////////////////////////
989/// Return number of statement parameters.
990
992{
993 return 0;
994}
995
996////////////////////////////////////////////////////////////////////////////////
997/// Store result of statement processing to access them
998/// via GetInt(), GetDouble() and so on methods.
999
1001{
1002 return kFALSE;
1003}
1004
1005////////////////////////////////////////////////////////////////////////////////
1006/// Return number of fields in result set.
1007
1009{
1010 return 0;
1011}
1012
1013////////////////////////////////////////////////////////////////////////////////
1014/// Returns field name in result set.
1015
1017{
1018 return 0;
1019}
1020
1021////////////////////////////////////////////////////////////////////////////////
1022/// Shift cursor to nect row in result set.
1023
1025{
1026 return kFALSE;
1027}
1028
1029
1030////////////////////////////////////////////////////////////////////////////////
1031/// Increment iteration counter for statement, where parameter can be set.
1032/// Statement with parameters of previous iteration
1033/// automatically will be applied to database.
1034
1036{
1037 return kFALSE;
1038}
1039
1040////////////////////////////////////////////////////////////////////////////////
1041/// Release all buffers, used by statement.
1042
1044{
1045}
1046
1047////////////////////////////////////////////////////////////////////////////////
1048/// Allocate buffers for statement parameters/ result fields.
1049
1051{
1052}
1053
1054////////////////////////////////////////////////////////////////////////////////
1055/// Convert field value to string.
1056
1058{
1059 return 0;
1060}
1061
1062////////////////////////////////////////////////////////////////////////////////
1063/// Convert field to numeric value.
1064
1066{
1067 return 0;
1068}
1069
1070////////////////////////////////////////////////////////////////////////////////
1071/// Checks if field value is null.
1072
1074{
1075 return kTRUE;
1076}
1077
1078////////////////////////////////////////////////////////////////////////////////
1079/// Return field value as integer.
1080
1082{
1083 return 0;
1084}
1085
1086////////////////////////////////////////////////////////////////////////////////
1087/// Return field value as unsigned integer.
1088
1090{
1091 return 0;
1092}
1093
1094////////////////////////////////////////////////////////////////////////////////
1095/// Return field value as long integer.
1096
1098{
1099 return 0;
1100}
1101
1102////////////////////////////////////////////////////////////////////////////////
1103/// Return field value as 64-bit integer.
1104
1106{
1107 return 0;
1108}
1109
1110////////////////////////////////////////////////////////////////////////////////
1111/// Return field value as unsigned 64-bit integer.
1112
1114{
1115 return 0;
1116}
1117
1118////////////////////////////////////////////////////////////////////////////////
1119/// Return field value as double.
1120
1122{
1123 return 0.;
1124}
1125
1126////////////////////////////////////////////////////////////////////////////////
1127/// Return field value as string.
1128
1130{
1131 return 0;
1132}
1133
1134////////////////////////////////////////////////////////////////////////////////
1135/// Return field value as binary array.
1136
1138{
1139 return kFALSE;
1140}
1141
1142////////////////////////////////////////////////////////////////////////////////
1143/// Return large object whose oid is in the given field.
1144
1146{
1147 return kFALSE;
1148}
1149
1150////////////////////////////////////////////////////////////////////////////////
1151/// Return field value as date.
1152
1154{
1155 return kFALSE;
1156}
1157
1158////////////////////////////////////////////////////////////////////////////////
1159/// Return field value as time.
1160
1162{
1163 return kFALSE;
1164}
1165
1166////////////////////////////////////////////////////////////////////////////////
1167/// Return field value as date & time.
1168
1170{
1171 return kFALSE;
1172}
1173
1174////////////////////////////////////////////////////////////////////////////////
1175/// Return field value as time stamp.
1176
1178{
1179 return kFALSE;
1180}
1181
1182////////////////////////////////////////////////////////////////////////////////
1183/// Return value of parameter in form of TTimeStamp
1184/// Be aware, that TTimeStamp does not allow dates before 1970-01-01
1185
1187{
1188 return kFALSE;
1189}
1190
1191////////////////////////////////////////////////////////////////////////////////
1192/// Set parameter type to be used as buffer.
1193
1195{
1196 return kFALSE;
1197}
1198
1199////////////////////////////////////////////////////////////////////////////////
1200/// Set NULL as parameter value.
1201
1203{
1204 return kFALSE;
1205}
1206
1207////////////////////////////////////////////////////////////////////////////////
1208/// Set parameter value as integer.
1209
1211{
1212 return kFALSE;
1213}
1214
1215////////////////////////////////////////////////////////////////////////////////
1216/// Set parameter value as unsigned integer.
1217
1219{
1220 return kFALSE;
1221}
1222
1223////////////////////////////////////////////////////////////////////////////////
1224/// Set parameter value as long integer.
1225
1227{
1228 return kFALSE;
1229}
1230
1231////////////////////////////////////////////////////////////////////////////////
1232/// Set parameter value as 64-bit integer.
1233
1235{
1236 return kFALSE;
1237}
1238
1239////////////////////////////////////////////////////////////////////////////////
1240/// Set parameter value as unsigned 64-bit integer.
1241
1243{
1244 return kFALSE;
1245}
1246
1247////////////////////////////////////////////////////////////////////////////////
1248/// Set parameter value as double.
1249
1251{
1252 return kFALSE;
1253}
1254
1255////////////////////////////////////////////////////////////////////////////////
1256/// Set parameter value as string.
1257
1259{
1260 return kFALSE;
1261}
1262
1263////////////////////////////////////////////////////////////////////////////////
1264/// Set parameter value as binary data.
1265
1267{
1268 return kFALSE;
1269}
1270
1271////////////////////////////////////////////////////////////////////////////////
1272/// Set parameter value to large object and immediately insert the large object into DB.
1273
1275{
1276 return kFALSE;
1277}
1278
1279////////////////////////////////////////////////////////////////////////////////
1280/// Set parameter value as date.
1281
1283{
1284 return kFALSE;
1285}
1286
1287////////////////////////////////////////////////////////////////////////////////
1288/// Set parameter value as time.
1289
1291{
1292 return kFALSE;
1293}
1294
1295////////////////////////////////////////////////////////////////////////////////
1296/// Set parameter value as date & time.
1297
1299{
1300 return kFALSE;
1301}
1302
1303////////////////////////////////////////////////////////////////////////////////
1304/// Set parameter value as timestamp.
1305
1307{
1308 return kFALSE;
1309}
1310
1311////////////////////////////////////////////////////////////////////////////////
1312/// Set parameter value as timestamp from TTimeStamp object
1313
1315{
1316 return kFALSE;
1317}
1318
1319#endif //PG_VERSION_NUM
#define d(i)
Definition RSha256.hxx:102
size_t size(const MatrixT &matrix)
retrieve the size of a square matrix
bool Bool_t
Definition RtypesCore.h:63
int Int_t
Definition RtypesCore.h:45
int Ssiz_t
Definition RtypesCore.h:67
long Long_t
Definition RtypesCore.h:54
unsigned int UInt_t
Definition RtypesCore.h:46
constexpr Bool_t kFALSE
Definition RtypesCore.h:94
double Double_t
Definition RtypesCore.h:59
constexpr Ssiz_t kNPOS
Definition RtypesCore.h:117
long long Long64_t
Definition RtypesCore.h:69
unsigned long long ULong64_t
Definition RtypesCore.h:70
constexpr Bool_t kTRUE
Definition RtypesCore.h:93
const char Option_t
Definition RtypesCore.h:66
#define ClassImp(name)
Definition Rtypes.h:382
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)
struct pg_result PGresult
#define pgsql_success(x)
#define CheckGetField(method, res)
#define CheckStmt(method, res)
#define snprintf
Definition civetweb.c:1540
This class stores the date and time with a precision of one second in an unsigned 32 bit word (950130...
Definition TDatime.h:37
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition TObject.cxx:993
PgSQL_Stmt_t * fStmt
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.
void ConvertTimeToUTC(const TString &PQvalue, Int_t &year, Int_t &month, Int_t &day, Int_t &hour, Int_t &min, Int_t &sec)
void SetBuffersNumber(Int_t n)
Allocate buffers for statement parameters/ result fields.
Int_t fNumResultRows
data type (OID)
UInt_t GetUInt(Int_t npar) final
Return field value as unsigned integer.
Bool_t SetTime(Int_t npar, Int_t hour, Int_t min, Int_t sec) final
Set parameter value as time.
long double ConvertToNumeric(Int_t npar)
Convert field to numeric value.
Bool_t GetTime(Int_t npar, Int_t &hour, Int_t &min, Int_t &sec) final
Return field value as time.
Bool_t SetLargeObject(Int_t npar, void *mem, Long_t size, Long_t maxsize=0x1000) final
Set parameter value to large object and immediately insert the large object into DB.
Bool_t IsResultSetMode() const
TPgSQLStatement(PgSQL_Stmt_t *stmt, Bool_t errout=kTRUE)
Normal constructor.
int * fParamLengths
number of iteration
Bool_t GetDate(Int_t npar, Int_t &year, Int_t &month, Int_t &day) final
Return field value as date.
Int_t GetInt(Int_t npar) final
Return field value as integer.
Int_t GetNumParameters() final
Return number of statement parameters.
const char * GetString(Int_t npar) final
Return field value as string.
Bool_t SetDate(Int_t npar, Int_t year, Int_t month, Int_t day) final
Set parameter value as date.
Bool_t GetLargeObject(Int_t npar, void *&mem, Long_t &size) final
Return large object whose oid is in the given field.
Bool_t SetInt(Int_t npar, Int_t value) final
Set parameter value as integer.
ULong64_t GetULong64(Int_t npar) final
Return field value as unsigned 64-bit integer.
Long_t GetLong(Int_t npar) final
Return field value as long integer.
Bool_t SetSQLParamType(Int_t npar, Bool_t isbinary=kFALSE, Int_t param_len=0, Int_t maxsize=0)
Set parameter type to be used as buffer.
Int_t fIterationCount
1 - setting parameters, 2 - retrieving results
Int_t fNumBuffers
executed statement
Int_t fWorkingMode
array of column names
Bool_t GetBinary(Int_t npar, void *&mem, Long_t &size) final
Return field value as binary array.
Bool_t IsSetParsMode() const
Bool_t IsNull(Int_t npar) final
Checks if field value is null.
Int_t GetNumFields() final
Return number of fields in result set.
Double_t GetDouble(Int_t npar) final
Return field value as double.
Bool_t SetDouble(Int_t npar, Double_t value) final
Set parameter value as double.
Bool_t NextResultRow() final
Shift cursor to nect row in result set.
Long64_t GetLong64(Int_t npar) final
Return field value as 64-bit integer.
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.
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.
Int_t GetNumAffectedRows() final
Return number of affected rows after statement is processed.
Bool_t SetLong(Int_t npar, Long_t value) final
Set parameter value as long integer.
Bool_t StoreResult() final
Store result of statement processing to access them via GetInt(), GetDouble() and so on methods.
Bool_t SetUInt(Int_t npar, UInt_t value) final
Set parameter value as unsigned integer.
const char * GetFieldName(Int_t nfield) final
Returns field name in result set.
Bool_t SetNull(Int_t npar) final
Set NULL as parameter value.
const char * ConvertToString(Int_t npar)
Convert field value to string.
Bool_t SetULong64(Int_t npar, ULong64_t value) final
Set parameter value as unsigned 64-bit integer.
char ** fBind
number of statement parameters
void FreeBuffers()
Release all buffers, used by statement.
Bool_t SetLong64(Int_t npar, Long64_t value) final
Set parameter value as 64-bit integer.
virtual ~TPgSQLStatement()
Destructor.
Bool_t SetString(Int_t npar, const char *value, Int_t maxsize=256) final
Set parameter value as string.
Bool_t SetBinary(Int_t npar, void *mem, Long_t size, Long_t maxsize=0x1000) final
Set parameter value as binary data.
Bool_t Process() final
Process statement.
int * fParamFormats
length of column
char ** fFieldName
array of data for input
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 value as time stamp.
Bool_t NextIteration() final
Increment iteration counter for statement, where parameter can be set.
void Close(Option_t *="") final
Close statement.
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
Basic string class.
Definition TString.h:139
Ssiz_t Length() const
Definition TString.h:417
const char * Data() const
Definition TString.h:376
Ssiz_t Last(char c) const
Find last occurrence of a character c.
Definition TString.cxx:931
A zero length substring is legal.
Definition TString.h:85
const char * Data() const
Definition TString.h:736
The TTimeStamp encapsulates seconds and ns since EPOCH.
Definition TTimeStamp.h:45
void Set()
Set Date/Time to current time as reported by the system.
Int_t GetNanoSec() const
Definition TTimeStamp.h:110
const char * AsString(const Option_t *option="") const
Return the date & time as a string.
Int_t Nint(T x)
Round to nearest integer. Rounds half integers to the nearest even integer.
Definition TMath.h:693
T1 Sign(T1 a, T2 b)
Returns a value with the magnitude of a and the sign of b.
Definition TMathBase.h:175
PGresult * fRes