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