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