Logo ROOT   6.08/07
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++;
257  if (fIterationCount>=fNumResultRows)
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;
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  // Set parameter value as binary data.
763 
764  size_t sz = size, mxsz = maxsize;
765  unsigned char* escape_ptr = PQescapeBytea((const unsigned char*)mem, sz, &mxsz);
766  unsigned char* binary_ptr = PQunescapeBytea((const unsigned char*)escape_ptr, &mxsz);
767  PQfreemem(escape_ptr);
768 
769  delete [] fBind[npar];
770  fBind[npar] = new char[mxsz+1];
771  fBind[npar][mxsz] = '\0';
772  memcpy(fBind[npar], binary_ptr, mxsz);
773 
774  PQfreemem(binary_ptr);
775  return kTRUE;
776 }
777 
778 ////////////////////////////////////////////////////////////////////////////////
779 /// Set parameter value to large object and immediately insert the large object into DB.
780 
781 Bool_t TPgSQLStatement::SetLargeObject(Int_t npar, void* mem, Long_t size, Long_t /*maxsize*/)
782 {
783  // All this needs to happen inside a transaction, or it will NOT work.
784  PGresult *res=PQexec(fStmt->fConn,"BEGIN");
785 
786  CheckErrResult("GetLargeObject", res, kFALSE);
787  PQclear(res);
788 
789  Int_t lObjID = lo_creat(fStmt->fConn, INV_READ | INV_WRITE);
790  if (lObjID<0) {
791  Error("SetLargeObject", "Error in SetLargeObject: %s", PQerrorMessage(fStmt->fConn));
792  RollBackTransaction("GetLargeObject");
793  return kFALSE;
794  }
795 
796  Int_t lObjFD = lo_open(fStmt->fConn, lObjID, INV_READ | INV_WRITE);
797  if (lObjFD<0) {
798  Error("SetLargeObject", "Error in SetLargeObject: %s", PQerrorMessage(fStmt->fConn));
799  RollBackTransaction("GetLargeObject");
800  return kFALSE;
801  }
802 
803  Int_t writtenBytes = lo_write(fStmt->fConn, lObjFD, (char*)mem, size);
804 
805  if (writtenBytes != size) {
806  Error("SetLargeObject", "SQL Error on lo_write: %s", PQerrorMessage(fStmt->fConn));
807  RollBackTransaction("GetLargeObject");
808  return kFALSE;
809  }
810 
811  if (lo_close(fStmt->fConn, lObjFD) != 0) {
812  Error("SetLargeObject", "SQL Error on lo_close: %s", PQerrorMessage(fStmt->fConn));
813  RollBackTransaction("GetLargeObject");
814  return kFALSE;
815  }
816 
817  res=PQexec(fStmt->fConn,"COMMIT");
818  ExecStatusType stat = PQresultStatus(res);
819  if (!pgsql_success(stat)) {
820  Error("SetLargeObject", "SQL Error on COMMIT: %s", PQerrorMessage(fStmt->fConn));
821  PQclear(res);
822  return kFALSE;
823  }
824  PQclear(res);
825 
826  snprintf(fBind[npar],kBindStringSize,"%d",lObjID);
827 
828  return kTRUE;
829 }
830 
831 ////////////////////////////////////////////////////////////////////////////////
832 /// Set parameter value as date.
833 
834 Bool_t TPgSQLStatement::SetDate(Int_t npar, Int_t year, Int_t month, Int_t day)
835 {
836  TDatime d =TDatime(year,month,day,0,0,0);
837  snprintf(fBind[npar],kBindStringSize,"%s",(char*)d.AsSQLString());
838 
839  return kTRUE;
840 }
841 
842 ////////////////////////////////////////////////////////////////////////////////
843 /// Set parameter value as time.
844 
846 {
847  TDatime d=TDatime(2000,1,1,hour,min,sec);
848  snprintf(fBind[npar],kBindStringSize,"%s",(char*)d.AsSQLString());
849  return kTRUE;
850 }
851 
852 ////////////////////////////////////////////////////////////////////////////////
853 /// Set parameter value as date & time.
854 
855 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)
856 {
857  TDatime d=TDatime(year,month,day,hour,min,sec);
858  snprintf(fBind[npar],kBindStringSize,"%s",(char*)d.AsSQLString());
859  return kTRUE;
860 }
861 
862 ////////////////////////////////////////////////////////////////////////////////
863 /// Set parameter value as timestamp.
864 /// Second fraction is assumed as value in microseconds,
865 /// i.e. as a fraction with six decimal places.
866 /// See GetTimestamp() for an example.
867 
868 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)
869 {
870  TDatime d(year,month,day,hour,min,sec);
871  snprintf(fBind[npar],kBindStringSize,"%s.%06d",(char*)d.AsSQLString(),frac);
872  return kTRUE;
873 }
874 
875 #else
876 
877 ////////////////////////////////////////////////////////////////////////////////
878 /// Normal constructor.
879 /// For PgSQL version < 8.2 no statement is supported.
880 
882 {
883 }
884 
885 ////////////////////////////////////////////////////////////////////////////////
886 /// Destructor.
887 
889 {
890 }
891 
892 ////////////////////////////////////////////////////////////////////////////////
893 /// Close statement.
894 
896 {
897 }
898 
899 ////////////////////////////////////////////////////////////////////////////////
900 /// Process statement.
901 
903 {
904  return kFALSE;
905 }
906 
907 ////////////////////////////////////////////////////////////////////////////////
908 /// Return number of affected rows after statement is processed.
909 
911 {
912  return 0;
913 }
914 
915 ////////////////////////////////////////////////////////////////////////////////
916 /// Return number of statement parameters.
917 
919 {
920  return 0;
921 }
922 
923 ////////////////////////////////////////////////////////////////////////////////
924 /// Store result of statement processing to access them
925 /// via GetInt(), GetDouble() and so on methods.
926 
928 {
929  return kFALSE;
930 }
931 
932 ////////////////////////////////////////////////////////////////////////////////
933 /// Return number of fields in result set.
934 
936 {
937  return 0;
938 }
939 
940 ////////////////////////////////////////////////////////////////////////////////
941 /// Returns field name in result set.
942 
944 {
945  return 0;
946 }
947 
948 ////////////////////////////////////////////////////////////////////////////////
949 /// Shift cursor to nect row in result set.
950 
952 {
953  return kFALSE;
954 }
955 
956 
957 ////////////////////////////////////////////////////////////////////////////////
958 /// Increment iteration counter for statement, where parameter can be set.
959 /// Statement with parameters of previous iteration
960 /// automatically will be applied to database.
961 
963 {
964  return kFALSE;
965 }
966 
967 ////////////////////////////////////////////////////////////////////////////////
968 /// Release all buffers, used by statement.
969 
971 {
972 }
973 
974 ////////////////////////////////////////////////////////////////////////////////
975 /// Allocate buffers for statement parameters/ result fields.
976 
978 {
979 }
980 
981 ////////////////////////////////////////////////////////////////////////////////
982 /// Convert field value to string.
983 
985 {
986  return 0;
987 }
988 
989 ////////////////////////////////////////////////////////////////////////////////
990 /// Convert field to numeric value.
991 
993 {
994  return 0;
995 }
996 
997 ////////////////////////////////////////////////////////////////////////////////
998 /// Checks if field value is null.
999 
1001 {
1002  return kTRUE;
1003 }
1004 
1005 ////////////////////////////////////////////////////////////////////////////////
1006 /// Return field value as integer.
1007 
1009 {
1010  return 0;
1011 }
1012 
1013 ////////////////////////////////////////////////////////////////////////////////
1014 /// Return field value as unsigned integer.
1015 
1017 {
1018  return 0;
1019 }
1020 
1021 ////////////////////////////////////////////////////////////////////////////////
1022 /// Return field value as long integer.
1023 
1025 {
1026  return 0;
1027 }
1028 
1029 ////////////////////////////////////////////////////////////////////////////////
1030 /// Return field value as 64-bit integer.
1031 
1033 {
1034  return 0;
1035 }
1036 
1037 ////////////////////////////////////////////////////////////////////////////////
1038 /// Return field value as unsigned 64-bit integer.
1039 
1041 {
1042  return 0;
1043 }
1044 
1045 ////////////////////////////////////////////////////////////////////////////////
1046 /// Return field value as double.
1047 
1049 {
1050  return 0.;
1051 }
1052 
1053 ////////////////////////////////////////////////////////////////////////////////
1054 /// Return field value as string.
1055 
1057 {
1058  return 0;
1059 }
1060 
1061 ////////////////////////////////////////////////////////////////////////////////
1062 /// Return field value as binary array.
1063 
1065 {
1066  return kFALSE;
1067 }
1068 
1069 ////////////////////////////////////////////////////////////////////////////////
1070 /// Return large object whose oid is in the given field.
1071 
1073 {
1074  return kFALSE;
1075 }
1076 
1077 ////////////////////////////////////////////////////////////////////////////////
1078 /// Return field value as date.
1079 
1081 {
1082  return kFALSE;
1083 }
1084 
1085 ////////////////////////////////////////////////////////////////////////////////
1086 /// Return field value as time.
1087 
1089 {
1090  return kFALSE;
1091 }
1092 
1093 ////////////////////////////////////////////////////////////////////////////////
1094 /// Return field value as date & time.
1095 
1097 {
1098  return kFALSE;
1099 }
1100 
1101 ////////////////////////////////////////////////////////////////////////////////
1102 /// Return field value as time stamp.
1103 
1105 {
1106  return kFALSE;
1107 }
1108 
1109 ////////////////////////////////////////////////////////////////////////////////
1110 /// Set parameter type to be used as buffer.
1111 /// Used in both setting data to database and retriving data from data base.
1112 /// Initialize proper PGSQL_BIND structure and allocate required buffers.
1113 
1115 {
1116  return kFALSE;
1117 }
1118 
1119 ////////////////////////////////////////////////////////////////////////////////
1120 /// Set NULL as parameter value.
1121 /// If NULL should be set for statement parameter during first iteration,
1122 /// one should call before proper Set... method to identify type of argument for
1123 /// the future. For instance, if one suppose to have double as type of parameter,
1124 /// code should look like:
1125 /// stmt->SetDouble(2, 0.);
1126 /// stmt->SetNull(2);
1127 
1129 {
1130  if ((npar >= 0) && (npar < fNumBuffers))
1131  fBind[npar] = 0;
1132  return kFALSE;
1133 }
1134 
1135 ////////////////////////////////////////////////////////////////////////////////
1136 /// Set parameter value as integer.
1137 
1139 {
1140  return kFALSE;
1141 }
1142 
1143 ////////////////////////////////////////////////////////////////////////////////
1144 /// Set parameter value as unsigned integer.
1145 
1147 {
1148  return kFALSE;
1149 }
1150 
1151 ////////////////////////////////////////////////////////////////////////////////
1152 /// Set parameter value as long integer.
1153 
1155 {
1156  return kFALSE;
1157 }
1158 
1159 ////////////////////////////////////////////////////////////////////////////////
1160 /// Set parameter value as 64-bit integer.
1161 
1163 {
1164  return kFALSE;
1165 }
1166 
1167 ////////////////////////////////////////////////////////////////////////////////
1168 /// Set parameter value as unsigned 64-bit integer.
1169 
1171 {
1172  return kFALSE;
1173 }
1174 
1175 ////////////////////////////////////////////////////////////////////////////////
1176 /// Set parameter value as double.
1177 
1179 {
1180  return kFALSE;
1181 }
1182 
1183 ////////////////////////////////////////////////////////////////////////////////
1184 /// Set parameter value as string.
1185 
1187 {
1188  return kFALSE;
1189 }
1190 
1191 ////////////////////////////////////////////////////////////////////////////////
1192 /// Set parameter value as binary data.
1193 
1195 {
1196  return kFALSE;
1197 }
1198 
1199 ////////////////////////////////////////////////////////////////////////////////
1200 /// Set parameter value to large object and immediately insert the large object into DB.
1201 
1203 {
1204  return kFALSE;
1205 }
1206 
1207 ////////////////////////////////////////////////////////////////////////////////
1208 /// Set parameter value as date.
1209 
1211 {
1212  return kFALSE;
1213 }
1214 
1215 ////////////////////////////////////////////////////////////////////////////////
1216 /// Set parameter value as time.
1217 
1219 {
1220  return kFALSE;
1221 }
1222 
1223 ////////////////////////////////////////////////////////////////////////////////
1224 /// Set parameter value as date & time.
1225 
1227 {
1228  return kFALSE;
1229 }
1230 
1231 ////////////////////////////////////////////////////////////////////////////////
1232 /// Set parameter value as timestamp.
1233 
1235 {
1236  return kFALSE;
1237 }
1238 
1239 #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...
const char * ConvertToString(Int_t npar)
Convert field value to string.
virtual Int_t GetNumAffectedRows()
Return number of affected rows after statement is processed.
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.
virtual Bool_t SetDouble(Int_t npar, Double_t value)
Set parameter value as double.
#define CheckStmt(method, res)
const char Option_t
Definition: RtypesCore.h:62
virtual Int_t GetInt(Int_t npar)
Return field value as integer.
virtual Bool_t SetNull(Int_t npar)
Set NULL as parameter value.
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.
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.
void ConvertTimeToUTC(const TString &PQvalue, Int_t &year, Int_t &month, Int_t &day, Int_t &hour, Int_t &min, Int_t &sec)
const char * Data() const
Definition: TString.h:669
virtual Bool_t SetTimestamp(Int_t npar, Int_t year, Int_t month, Int_t day, Int_t hour, Int_t min, Int_t sec, Int_t frac=0)
Set parameter value as timestamp.
virtual 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.
virtual Bool_t IsNull(Int_t npar)
Checks if field value is null.
Int_t GetDay() const
Definition: TDatime.h:69
virtual void Close(Option_t *="")
Close statement.
void ClearError()
reset error fields
Definition: TSQLServer.cxx:119
long double ConvertToNumeric(Int_t npar)
Convert field to numeric value.
virtual Int_t GetNumFields()
Return number of fields in result set.
Int_t GetMonth() const
Definition: TDatime.h:68
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 ULong64_t GetULong64(Int_t npar)
Return field value as unsigned 64-bit integer.
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)
Int_t GetSecond() const
Definition: TDatime.h:73
Int_t GetMinute() const
Definition: TDatime.h:72
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.
Int_t GetHour() const
Definition: TDatime.h:71
virtual Bool_t SetLong(Int_t npar, Long_t value)
Set parameter value as long integer.
const char * AsSQLString() const
Return the date & time in SQL compatible string format, like: 1997-01-15 20:16:28.
Definition: TDatime.cxx:151
unsigned int UInt_t
Definition: RtypesCore.h:42
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition: TObject.cxx:925
Ssiz_t Length() const
Definition: TString.h:390
void Close(Option_t *opt="")
Close connection to PgSQL DB server.
virtual const char * GetFieldName(Int_t nfield)
Returns field name in result set.
long Long_t
Definition: RtypesCore.h:50
int Ssiz_t
Definition: RtypesCore.h:63
void FreeBuffers()
Release all buffers, used by statement.
#define ClassImp(name)
Definition: Rtypes.h:279
double Double_t
Definition: RtypesCore.h:55
Ssiz_t Last(char c) const
Find last occurrence of a character c.
Definition: TString.cxx:865
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.
void SetError(Int_t code, const char *msg, const char *method=0)
set new values for error fields if method is specified, displays error message
Definition: TSQLServer.cxx:129
#define CheckGetField(method, defres)
const Ssiz_t kNPOS
Definition: Rtypes.h:115
#define NULL
Definition: Rtypes.h:82
#define snprintf
Definition: civetweb.c:822
virtual Bool_t NextIteration()
Increment iteration counter for statement, where parameter can be set.
virtual Bool_t SetBinary(Int_t npar, void *mem, Long_t size, Long_t maxsize=0x1000)
Set parameter value as binary data.
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.
Int_t GetYear() const
Definition: TDatime.h:67
virtual ~TPgSQLStatement()
Destructor.
const Bool_t kTRUE
Definition: Rtypes.h:91
TPgSQLStatement(PgSQL_Stmt_t *stmt, Bool_t errout=kTRUE)
Normal constructor.
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.
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
const char * Data() const
Definition: TString.h:349