Logo ROOT  
Reference Guide
 
Loading...
Searching...
No Matches
TOracleServer.cxx
Go to the documentation of this file.
1// @(#)root/oracle:$Id$
2// Author: Yan Liu and Shaowen Wang 23/11/04
3
4/*************************************************************************
5 * Copyright (C) 1995-2005, 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// TOracleServer //
15// //
16// This class implements an OCCI interface to Oracle data bases. //
17// It uses the instantclient10 software available from Oracle. //
18// To install this client software do: //
19// 1) Download Instant Client Packages (4 files) from: //
20// http://www.oracle.com/technology/software/tech/oci/instantclient/index.html
21// 2) Unzip the files into instantclient10_2 (Mac OS X example here): //
22// unzip instantclient-basic-macosx-10.2.0.4.zip //
23// unzip instantclient-sqlplus-macosx-10.2.0.4.zip //
24// unzip instantclient-sdk-macosx-10.2.0.4.zip //
25// unzip instantclient-jdbc-macosx-10.2.0.4.zip //
26// 3) Create two symbolic links for the files that have the version //
27// appended: //
28// ln -s libclntsh.dylib.10.1 libclntsh.dylib //
29// ln -s libocci.dylib.10.1 libocci.dylib //
30// 4) Add instantclient10_1 directory to your (DY)LD_LIBRARY_PATH //
31// in your .profile: //
32// export (DY)LD_LIBRARY_PATH="<pathto>/instantclient10_2" //
33// Use DY only on Mac OS X. //
34// 5) If you also want to use the sqlplus command line app add also //
35// export SQLPATH="<pathto>/instantclient10_2" //
36// 6) If you want to connect to a remote db server you will also need //
37// to create a tnsname.ora file which describes the local_name for //
38// the remote db servers (at CERN most public machines have this //
39// file in /etc). If it is not in /etc create TNS_ADMIN: //
40// export TNS_ADMIN="<path-to-dir-containing-tnsname.ora>" //
41// 7) Test it our with the sqlplus command line app: //
42// sqlplus [username][/password]@<local_name> //
43// or //
44// sqlplus [username][/password]@//[hostname][:port][/database] //
45// //
46//////////////////////////////////////////////////////////////////////////
47
48
49#include "TOracleServer.h"
50#include "TOracleResult.h"
51#include "TOracleStatement.h"
52#include "TSQLColumnInfo.h"
53#include "TSQLTableInfo.h"
54#include "TUrl.h"
55#include "TList.h"
56#include "TObjString.h"
57
58#ifndef R__WIN32
59#include <sys/time.h>
60#endif
61
62#include <occi.h>
63
65
66using namespace oracle::occi;
67
68const char *TOracleServer::fgDatimeFormat = "MM/DD/YYYY, HH24:MI:SS";
69
70
71// Reset error and check that server connected
72#define CheckConnect(method, res) \
73 ClearError(); \
74 if (!IsConnected()) { \
75 SetError(-1,"Oracle database is not connected",method); \
76 return res; \
77 }
78
79// catch Oracle exception after try block
80#define CatchError(method) \
81 catch (SQLException &oraex) { \
82 SetError(oraex.getErrorCode(), oraex.getMessage().c_str(), method); \
83 }
84
85////////////////////////////////////////////////////////////////////////////////
86/// Open a connection to a Oracle DB server. The db arguments should be
87/// of the form "oracle://connection_identifier[/<database>]", e.g.:
88/// "oracle://cmscald.fnal.gov/test". The uid is the username and pw
89/// the password that should be used for the connection.
90
91TOracleServer::TOracleServer(const char *db, const char *uid, const char *pw)
92{
93 if (gDebug>0) {
94 // this code is necessary to guarantee, that libclntsh.so will be
95 // linked to libOracle.so.
96 sword major_version(0), minor_version(0), update_num(0), patch_num(0), port_update_num(0);
97 OCIClientVersion(&major_version, &minor_version, &update_num, &patch_num, &port_update_num);
98 Info("TOracleServer","Oracle Call Interface version %u.%u.%u.%u.%u",
99 (unsigned) major_version, (unsigned) minor_version, (unsigned) update_num, (unsigned) patch_num, (unsigned) port_update_num);
100 }
101
102 TUrl url(db);
103
104 if (!url.IsValid()) {
105 TString errmsg = "Malformed db argument ";
106 errmsg+=db;
107 SetError(-1, errmsg.Data(), "TOracleServer");
108 MakeZombie();
109 return;
110 }
111
112 if (strncmp(url.GetProtocol(), "oracle", 6)) {
113 SetError(-1, "protocol in db argument should be oracle://", "TOracleServer");
114 MakeZombie();
115 return;
116 }
117
118 const char *conn_str = url.GetFile();
119 if (conn_str)
120 if (*conn_str == '/') conn_str++; //skip leading "/" if appears
121
122 try {
123 // found out whether to use objet mode
124 TString options = url.GetOptions();
125 Int_t pos = options.Index("ObjectMode");
126 // create environment accordingly
127 if (pos != kNPOS) {
128 fEnv = Environment::createEnvironment(Environment::OBJECT);
129 } else {
130 fEnv = Environment::createEnvironment();
131 }
132 fConn = fEnv->createConnection(uid, pw, conn_str ? conn_str : "");
133
134 fType = "Oracle";
135 fHost = url.GetHost();
136 fDB = conn_str;
137 fPort = url.GetPort();
138 fPort = (fPort>0) ? fPort : 1521;
139 return;
140
141 } CatchError("TOracleServer")
142
143 MakeZombie();
144}
145
146////////////////////////////////////////////////////////////////////////////////
147/// Close connection to Oracle DB server.
148
150{
151 if (IsConnected())
152 Close();
153}
154
155
156////////////////////////////////////////////////////////////////////////////////
157/// Close connection to Oracle DB server.
158
160{
161 ClearError();
162
163 try {
164 if (fConn)
165 fEnv->terminateConnection(fConn);
166 if (fEnv)
167 Environment::terminateEnvironment(fEnv);
168 } CatchError("Close")
169
170 fPort = -1;
171}
172
173////////////////////////////////////////////////////////////////////////////////
174
176{
177 CheckConnect("Statement",0);
178
179 if (!sql || !*sql) {
180 SetError(-1, "no query string specified","Statement");
181 return nullptr;
182 }
183
184 try {
185 oracle::occi::Statement *stmt = fConn->createStatement(sql);
186
187 Blob parblob(fConn);
188
189 return new TOracleStatement(fEnv, fConn, stmt, niter, fErrorOut);
190
191 } CatchError("Statement")
192
193 return nullptr;
194}
195
196////////////////////////////////////////////////////////////////////////////////
197/// Execute SQL command. Result object must be deleted by the user.
198/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
199
201{
202 CheckConnect("Query",0);
203
204 if (!sql || !*sql) {
205 SetError(-1, "no query string specified","Query");
206 return nullptr;
207 }
208
209 try {
210 oracle::occi::Statement *stmt = fConn->createStatement();
211
212 // NOTE: before special COUNT query was executed to define number of
213 // rows in result set. Now it is not required, while TOracleResult class
214 // will automatically fetch all rows from result set when
215 // GetRowCount() will be called first time.
216 // It is better do not use GetRowCount() to avoid unnecessary memory usage.
217
218 stmt->setSQL(sql);
219 stmt->setPrefetchRowCount(1000);
220 stmt->setPrefetchMemorySize(1000000);
221 stmt->execute();
222
223 TOracleResult *res = new TOracleResult(fConn, stmt);
224 return res;
225 } CatchError("Query")
226
227 return nullptr;
228}
229
230////////////////////////////////////////////////////////////////////////////////
231/// Execute sql command which does not produce any result set.
232/// Return kTRUE if successful
233
235{
236 CheckConnect("Exec", kFALSE);
237
238 if (!sql || !*sql) {
239 SetError(-1, "no query string specified","Exec");
240 return kFALSE;
241 }
242
243 oracle::occi::Statement *stmt = nullptr;
244
245 Bool_t res = kFALSE;
246
247 try {
248 stmt = fConn->createStatement(sql);
249 stmt->execute();
250 res = kTRUE;
251 } CatchError("Exec")
252
253 try {
254 fConn->terminateStatement(stmt);
255 } CatchError("Exec")
256
257 return res;
258}
259
260////////////////////////////////////////////////////////////////////////////////
261/// List all tables in the specified database. Wild is for wildcarding
262/// "t%" list all tables starting with "t".
263/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
264/// The result object must be deleted by the user.
265
266TSQLResult *TOracleServer::GetTables(const char *dbname, const char * /*wild*/)
267{
268 // In Oracle 9 and above, table is accessed in schema.table format.
269 // GetTables returns tables in all schemas accessible for the user.
270 // Assumption: table ALL_OBJECTS is accessible for the user, which is true in Oracle 10g
271 // The returned TSQLResult has two columns: schema_name, table_name
272 // "dbname": if specified, return table list of this schema, or return all tables
273 // "wild" is not used in this implementation
274
275 CheckConnect("GetTables",0);
276
277 TString sqlstr("SELECT object_name,owner FROM ALL_OBJECTS WHERE object_type='TABLE'");
278 if (dbname && dbname[0])
279 sqlstr = sqlstr + " AND owner='" + dbname + "'";
280
281 return Query(sqlstr.Data());
282}
283
284////////////////////////////////////////////////////////////////////////////////
285
287{
288 CheckConnect("GetTablesList",0);
289
290 TString cmd("SELECT table_name FROM user_tables");
291 if ((wild!=0) && (*wild!=0))
292 cmd+=Form(" WHERE table_name LIKE '%s'", wild);
293
294 TSQLStatement* stmt = Statement(cmd);
295 if (!stmt) return nullptr;
296
297 TList *lst = nullptr;
298
299 if (stmt->Process()) {
300 stmt->StoreResult();
301 while (stmt->NextResultRow()) {
302 const char* tablename = stmt->GetString(0);
303 if (!tablename) continue;
304 if (!lst) {
305 lst = new TList;
306 lst->SetOwner(kTRUE);
307 }
308 lst->Add(new TObjString(tablename));
309 }
310 }
311
312 delete stmt;
313
314 return lst;
315}
316
317////////////////////////////////////////////////////////////////////////////////
318/// Produces SQL table info
319/// Object must be deleted by user
320
322{
323 CheckConnect("GetTableInfo",0);
324
325 if (!tablename || (*tablename==0)) return nullptr;
326
327 TString table(tablename);
328 table.ToUpper();
329 TString sql;
330 sql.Form("SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, CHAR_COL_DECL_LENGTH FROM user_tab_columns WHERE table_name = '%s' ORDER BY COLUMN_ID", table.Data());
331
332 TSQLStatement* stmt = Statement(sql.Data(), 10);
333 if (!stmt) return nullptr;
334
335 if (!stmt->Process()) {
336 delete stmt;
337 return nullptr;
338 }
339
340 TList *lst = nullptr;
341
342 stmt->StoreResult();
343
344 while (stmt->NextResultRow()) {
345 const char* columnname = stmt->GetString(0);
346 TString data_type = stmt->GetString(1);
347 Int_t data_length = stmt->GetInt(2); // this is size in bytes
348 Int_t data_precision = stmt->GetInt(3);
349 Int_t data_scale = stmt->GetInt(4);
350 const char* nstr = stmt->GetString(5);
351 Int_t char_col_decl_length = stmt->GetInt(6);
352 Int_t data_sign = -1; // no info about sign
353
354 Int_t sqltype = kSQL_NONE;
355
356 if (data_type=="NUMBER") {
357 sqltype = kSQL_NUMERIC;
358 if (data_precision<=0) {
359 data_precision = -1;
360 data_scale = -1;
361 } else
362 if (data_scale<=0)
363 data_scale = -1;
364 data_sign = 1;
365 } else
366
367 if (data_type=="CHAR") {
368 sqltype = kSQL_CHAR;
369 data_precision = char_col_decl_length;
370 data_scale = -1;
371 } else
372
373 if ((data_type=="VARCHAR") || (data_type=="VARCHAR2")) {
374 sqltype = kSQL_VARCHAR;
375 data_precision = char_col_decl_length;
376 data_scale = -1;
377 } else
378
379 if (data_type=="FLOAT") {
380 sqltype = kSQL_FLOAT;
381 data_scale = -1;
382 if (data_precision==126) data_precision = -1;
383 data_sign = 1;
384 } else
385
386 if (data_type=="BINARY_FLOAT") {
387 sqltype = kSQL_FLOAT;
388 data_scale = -1;
389 data_precision = -1;
390 data_sign = 1;
391 } else
392
393 if (data_type=="BINARY_DOUBLE") {
394 sqltype = kSQL_DOUBLE;
395 data_scale = -1;
396 data_precision = -1;
397 data_sign = 1;
398 } else
399
400 if (data_type=="LONG") {
401 sqltype = kSQL_VARCHAR;
402 data_length = 0x7fffffff; // size of LONG 2^31-1
403 data_precision = -1;
404 data_scale = -1;
405 } else
406
407 if (data_type.Contains("TIMESTAMP")) {
408 sqltype = kSQL_TIMESTAMP;
409 data_precision = -1;
410 }
411
412 Bool_t IsNullable = kFALSE;
413 if (nstr!=0)
414 IsNullable = (*nstr=='Y') || (*nstr=='y');
415
416 TSQLColumnInfo* info =
417 new TSQLColumnInfo(columnname,
418 data_type,
419 IsNullable,
420 sqltype,
421 data_length,
422 data_precision,
423 data_scale,
424 data_sign);
425
426 if (!lst) lst = new TList;
427 lst->Add(info);
428 }
429
430 delete stmt;
431
432 return new TSQLTableInfo(tablename, lst);
433}
434
435////////////////////////////////////////////////////////////////////////////////
436/// List all columns in specified table in the specified database.
437/// Wild is for wildcarding "t%" list all columns starting with "t".
438/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
439/// The result object must be deleted by the user.
440
441TSQLResult *TOracleServer::GetColumns(const char * /*dbname*/, const char *tablename,
442 const char * wild)
443{
444 CheckConnect("GetColumns",0);
445
446// make no sense, while method is not implemented
447// if (SelectDataBase(dbname) != 0) {
448// SetError(-1, "Database is not connected","GetColumns");
449// return nullptr;
450// }
451
452 TString sql;
453 TString table(tablename);
454 table.ToUpper();
455 if (wild && wild[0])
456 sql.Form("select COLUMN_NAME, concat(concat(concat(data_type,'('),data_length),')') \"Type\" FROM user_tab_columns WHERE table_name like '%s' ORDER BY COLUMN_ID", wild);
457 else
458 sql.Form("select COLUMN_NAME, concat(concat(concat(data_type,'('),data_length),')') \"Type\" FROM user_tab_columns WHERE table_name = '%s' ORDER BY COLUMN_ID", table.Data());
459 return Query(sql);
460}
461
462////////////////////////////////////////////////////////////////////////////////
463/// Select a database. Returns 0 if successful, non-zero otherwise.
464/// NOT IMPLEMENTED.
465
466Int_t TOracleServer::SelectDataBase(const char * /*dbname*/)
467{
468 CheckConnect("SelectDataBase", -1);
469
470 // do nothing and return success code
471 return 0;
472}
473
474////////////////////////////////////////////////////////////////////////////////
475/// List all available databases. Wild is for wildcarding "t%" list all
476/// databases starting with "t".
477/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
478/// The result object must be deleted by the user.
479/// NOT IMPLEMENTED.
480
482{
483 CheckConnect("GetDataBases",0);
484
485 return nullptr;
486}
487
488////////////////////////////////////////////////////////////////////////////////
489/// Create a database. Returns 0 if successful, non-zero otherwise.
490/// NOT IMPLEMENTED.
491
492Int_t TOracleServer::CreateDataBase(const char * /*dbname*/)
493{
494 CheckConnect("CreateDataBase",-1);
495
496 return -1;
497}
498
499////////////////////////////////////////////////////////////////////////////////
500/// Drop (i.e. delete) a database. Returns 0 if successful, non-zero
501/// otherwise.
502/// NOT IMPLEMENTED.
503
504Int_t TOracleServer::DropDataBase(const char * /*dbname*/)
505{
506 CheckConnect("DropDataBase",-1);
507
508 return -1;
509}
510
511////////////////////////////////////////////////////////////////////////////////
512/// Reload permission tables. Returns 0 if successful, non-zero
513/// otherwise. User must have reload permissions.
514/// NOT IMPLEMENTED.
515
517{
518 CheckConnect("Reload", -1);
519
520 return -1;
521}
522
523////////////////////////////////////////////////////////////////////////////////
524/// Shutdown the database server. Returns 0 if successful, non-zero
525/// otherwise. User must have shutdown permissions.
526/// NOT IMPLEMENTED.
527
529{
530 CheckConnect("Shutdown", -1);
531
532 return -1;
533}
534
535////////////////////////////////////////////////////////////////////////////////
536/// Return Oracle server version info.
537
539{
540 CheckConnect("ServerInfo", 0);
541
542 fInfo = "Oracle";
543 TSQLStatement* stmt = Statement("select * from v$version");
544 if (stmt!=0) {
546 if (stmt->Process()) {
547 fInfo = "";
548 stmt->StoreResult();
549 while (stmt->NextResultRow()) {
550 if (fInfo.Length()>0) fInfo += "\n";
551 fInfo += stmt->GetString(0);
552 }
553 }
554 delete stmt;
555 }
556
557 return fInfo.Data();
558}
559
560////////////////////////////////////////////////////////////////////////////////
561/// Call Commit() to submit all chanes, done before.
562/// Commit() ot Rollback() must be used to complete submitted actions or cancel them
563
565{
566 return Commit();
567}
568
569////////////////////////////////////////////////////////////////////////////////
570/// Commits all changes made since the previous Commit() or Rollback()
571/// Return kTRUE if OK
572
574{
575 CheckConnect("Commit", kFALSE);
576
577 try {
578 fConn->commit();
579 return kTRUE;
580 } CatchError("Commit")
581
582 return kFALSE;
583}
584
585////////////////////////////////////////////////////////////////////////////////
586/// Drops all changes made since the previous Commit() or Rollback()
587/// Return kTRUE if OK
588
590{
591 CheckConnect("Rollback", kFALSE);
592
593 try {
594 fConn->rollback();
595 return kTRUE;
596 } CatchError("Rollback")
597
598 return kFALSE;
599}
600
601////////////////////////////////////////////////////////////////////////////////
602/// set format for converting timestamps or date field into string
603/// default value is "MM/DD/YYYY, HH24:MI:SS"
604
606{
607 if (fmt==0) fmt = "MM/DD/YYYY, HH24:MI:SS";
608 fgDatimeFormat = fmt;
609}
610
611////////////////////////////////////////////////////////////////////////////////
612/// return value of actul convertion format from timestamps or date to string
613
615{
616 return fgDatimeFormat;
617}
const Ssiz_t kNPOS
Definition RtypesCore.h:124
const Bool_t kFALSE
Definition RtypesCore.h:101
const Bool_t kTRUE
Definition RtypesCore.h:100
const char Option_t
Definition RtypesCore.h:66
#define ClassImp(name)
Definition Rtypes.h:364
#define CheckConnect(method, res)
#define CatchError(method)
Int_t gDebug
Definition TROOT.cxx:592
char * Form(const char *fmt,...)
virtual void SetOwner(Bool_t enable=kTRUE)
Set whether this collection is the owner (enable==true) of its content.
A doubly linked list.
Definition TList.h:38
virtual void Add(TObject *obj)
Definition TList.h:81
Collectable string class.
Definition TObjString.h:28
void MakeZombie()
Definition TObject.h:53
virtual void Info(const char *method, const char *msgfmt,...) const
Issue info message.
Definition TObject.cxx:937
static void SetDatimeFormat(const char *fmt="MM/DD/YYYY, HH24:MI:SS")
set format for converting timestamps or date field into string default value is "MM/DD/YYYY,...
oracle::occi::Connection * fConn
TSQLResult * GetTables(const char *dbname, const char *wild=nullptr) final
List all tables in the specified database.
Int_t DropDataBase(const char *dbname) final
Drop (i.e.
Int_t Shutdown() final
Shutdown the database server.
TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=nullptr) final
List all columns in specified table in the specified database.
static const char * fgDatimeFormat
TSQLStatement * Statement(const char *sql, Int_t niter=100) final
Bool_t StartTransaction() final
Call Commit() to submit all chanes, done before.
~TOracleServer()
Close connection to Oracle DB server.
Bool_t Commit() final
Commits all changes made since the previous Commit() or Rollback() Return kTRUE if OK.
void Close(Option_t *opt="") final
Close connection to Oracle DB server.
Bool_t Rollback() final
Drops all changes made since the previous Commit() or Rollback() Return kTRUE if OK.
const char * ServerInfo() final
Return Oracle server version info.
TSQLResult * Query(const char *sql) final
Execute SQL command.
Bool_t Exec(const char *sql) final
Execute sql command which does not produce any result set.
TList * GetTablesList(const char *wild=nullptr) final
Return list of user tables Parameter wild specifies wildcard for table names.
TSQLTableInfo * GetTableInfo(const char *tablename) final
Produces SQL table info Object must be deleted by user.
Int_t CreateDataBase(const char *dbname) final
Create a database.
TSQLResult * GetDataBases(const char *wild=nullptr) final
List all available databases.
static const char * GetDatimeFormat()
return value of actul convertion format from timestamps or date to string
oracle::occi::Environment * fEnv
Bool_t IsConnected() const final
Int_t Reload() final
Reload permission tables.
Int_t SelectDataBase(const char *dbname) final
Select a database.
TOracleServer(const TOracleServer &)=delete
format for converting date and time stamps into string
void ClearError()
reset error fields
TString fHost
Definition TSQLServer.h:45
void SetError(Int_t code, const char *msg, const char *method=nullptr)
set new values for error fields if method is specified, displays error message
Int_t fPort
Definition TSQLServer.h:47
Bool_t fErrorOut
Definition TSQLServer.h:50
TString fDB
Definition TSQLServer.h:46
TString fType
Definition TSQLServer.h:44
virtual void EnableErrorOutput(Bool_t on=kTRUE)
virtual const char * GetString(Int_t)
virtual Bool_t NextResultRow()=0
virtual Int_t GetInt(Int_t)
virtual Bool_t Process()=0
virtual Bool_t StoreResult()=0
Basic string class.
Definition TString.h:136
Ssiz_t Length() const
Definition TString.h:410
const char * Data() const
Definition TString.h:369
void ToUpper()
Change string to upper case.
Definition TString.cxx:1163
void Form(const char *fmt,...)
Formats a string using a printf style format descriptor.
Definition TString.cxx:2314
Bool_t Contains(const char *pat, ECaseCompare cmp=kExact) const
Definition TString.h:624
Ssiz_t Index(const char *pat, Ssiz_t i=0, ECaseCompare cmp=kExact) const
Definition TString.h:639
This class represents a WWW compatible URL.
Definition TUrl.h:33
const char * GetFile() const
Definition TUrl.h:69
Bool_t IsValid() const
Definition TUrl.h:79
const char * GetHost() const
Definition TUrl.h:67
const char * GetOptions() const
Definition TUrl.h:71
const char * GetProtocol() const
Definition TUrl.h:64
Int_t GetPort() const
Definition TUrl.h:78