Logo ROOT   6.14/05
Reference Guide
TSapDBServer.cxx
Go to the documentation of this file.
1 // @(#)root/sapdb:$Id$
2 // Author: Mark Hemberger & Fons Rademakers 03/08/2001
3 
4 /*************************************************************************
5  * Copyright (C) 1995-2001, 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 #include "TSapDBServer.h"
13 #include "TSapDBResult.h"
14 #include "TSapDBRow.h"
15 #include "TUrl.h"
16 #include <ctype.h>
17 
18 
20 
21 ////////////////////////////////////////////////////////////////////////////////
22 /// Open a connection to a SapDB DB server. The db arguments should be
23 /// of the form "sapdb://<host>[:<port>][/<database>]", e.g.:
24 /// "sapdb://pcroot.cern.ch:3456/test". The uid is the username and pw
25 /// the password that should be used for the connection.
26 
27 TSapDBServer::TSapDBServer(const char *db, const char *uid, const char *pw)
28 {
29  fSapDB = 0;
30  fEnv = 0;
31  fStmt = 0;
32  fStmtCnt = 0;
33 
34  TUrl url(db);
35 
36  if (!url.IsValid()) {
37  Error("TSapDBServer", "malformed db argument %s", db);
38  MakeZombie();
39  return;
40  }
41 
42  if (strncmp(url.GetProtocol(), "sapdb", 5)) {
43  Error("TSapDBServer", "protocol in db argument should be sapdb it is %s",
44  url.GetProtocol());
45  MakeZombie();
46  return;
47  }
48 
49  const char *dbase = url.GetFile();
50 
51  // Allocate environment, connection, and statement handle
52  RETCODE rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &fEnv);
53  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
54  Error("TSapDBServer", "allocation of environment failed");
55  MakeZombie();
56  return;
57  }
58 
59  rc = SQLAllocHandle(SQL_HANDLE_DBC, fEnv, &fSapDB);
60  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
61  printSQLError(fSapDB, SQL_NULL_HSTMT);
62  Error("TSapDBServer", "allocation of db failed");
63  MakeZombie();
64  return;
65  }
66 
67  // Connect to data source
68  const char *dbnam = Form("%s:%s", url.GetHost(), dbase);
69  rc = SQLConnect(fSapDB, (SQLCHAR*) dbnam, SQL_NTS,
70  (SQLCHAR*) uid, SQL_NTS, (SQLCHAR*) pw, SQL_NTS);
71 
72  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
73  printSQLError(fSapDB, SQL_NULL_HSTMT);
74  Error("TSapDBServer", "connection to %s:%s failed", url.GetHost(), dbase);
75  MakeZombie();
76  return;
77  }
78 
79  rc = SQLAllocHandle(SQL_HANDLE_STMT, fSapDB, &fStmt);
80  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
82  Error("TSapDBServer", "allocation of statement handle failed");
83  MakeZombie();
84  return;
85  }
86  rc = SQLAllocHandle(SQL_HANDLE_STMT, fSapDB, &fStmtCnt);
87  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
89  Error("TSapDBServer", "allocation of count statement handle failed");
90  MakeZombie();
91  return;
92  }
93 
94  fType = "SapDB";
95  fHost = url.GetHost();
96  fDB = dbase;
97  fPort = url.GetPort();
98 }
99 
100 ////////////////////////////////////////////////////////////////////////////////
101 /// Close connection to SapDB DB server.
102 
104 {
105  if (IsConnected())
106  Close();
107 }
108 
109 ////////////////////////////////////////////////////////////////////////////////
110 /// Close connection to SapDB DB server.
111 
113 {
114  // Disconnect from the data source and free all handles
115  RETCODE rc = SQLDisconnect(fSapDB);
116  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
117  printSQLError(fSapDB, SQL_NULL_HSTMT);
118  Error("TSapDBServer", "disconnect during close failed");
119  }
120 
121  rc = SQLFreeHandle(SQL_HANDLE_STMT, fStmt);
122  if (rc != SQL_SUCCESS) {
123  //Error("TSapDBServer", "free statement handle during close failed");
124  }
125 
126  rc = SQLFreeHandle(SQL_HANDLE_STMT, fStmtCnt);
127  if (rc != SQL_SUCCESS) {
128  //Error("TSapDBServer", "free count statement handle during close failed");
129  }
130 
131  rc = SQLFreeHandle(SQL_HANDLE_DBC, fSapDB);
132  if (rc != SQL_SUCCESS) {
133  printSQLError(fSapDB, SQL_NULL_HSTMT);
134  Error("TSapDBServer", "free database handle during close failed");
135  }
136 
137  rc = SQLFreeHandle(SQL_HANDLE_ENV, fEnv);
138  if (rc != SQL_SUCCESS) {
139  Error("TSapDBServer", "free environment handle during close failed");
140  }
141 
142  fPort = -1;
143 }
144 
145 ////////////////////////////////////////////////////////////////////////////////
146 /// Execute SQL command. Result object must be deleted by the user.
147 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
148 /// The result object must be deleted by the user.
149 
151 {
152  if (!IsConnected()) {
153  Error("Query", "not connected");
154  return 0;
155  }
156 
157  RETCODE rc = SQLFreeHandle(SQL_HANDLE_STMT, fStmt);
158  if (rc != SQL_SUCCESS) {
160  Error("TSapDBServer", "free statement handle failed");
161  }
162 
163  rc = SQLAllocHandle(SQL_HANDLE_STMT, fSapDB, &fStmt);
164  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
166  Error("TSapDBServer", "allocation statement handle failed");
167  }
168 
169  rc = SQLFreeHandle(SQL_HANDLE_STMT, fStmtCnt);
170  if (rc != SQL_SUCCESS) {
172  Error("TSapDBServer", "free count statement handle failed");
173  }
174 
175  rc = SQLAllocHandle(SQL_HANDLE_STMT, fSapDB, &fStmtCnt);
176  if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
178  Error("TSapDBServer", "allocation count statement handle failed");
179  }
180 
181  SDWORD slRowCount;
182  TString sqlcnt = "SELECT COUNT(*) ";
183  TString sqlt = sql;
184  sqlt = sqlt.Strip(TString::kBoth);
185 
186  if (sqlt.BeginsWith("SELECT", TString::kIgnoreCase)) {
187  Ssiz_t i = sqlt.Index("FROM", 0, TString::kIgnoreCase);
188  if (i != kNPOS)
189  sqlcnt += sqlt(i, sqlt.Length());
190 
191  if (SQLExecDirect(fStmtCnt, (SQLCHAR*)sqlcnt.Data(), SQL_NTS) !=
192  SQL_SUCCESS) {
194  return 0;
195  }
196 
197  SQLBindCol(fStmtCnt, 1, SQL_C_LONG, &slRowCount, 0, 0);
198  rc = SQLFetch(fStmtCnt);
199  //if (rc == SQL_SUCCESS)
200  // printf("RowCount: %ld\n", slRowCount);
201  }
202 
203  if (SQLPrepare(fStmt, (SQLCHAR*)sqlt.Data(), SQL_NTS) != SQL_SUCCESS) {
205  return 0;
206  }
207 
208  if (SQLExecute(fStmt) != SQL_SUCCESS) {
210  return 0;
211  }
212  if (SQLEndTran(SQL_HANDLE_DBC, fSapDB, SQL_COMMIT) != SQL_SUCCESS) {
214  return 0;
215  }
216 
217  return new TSapDBResult(fStmt, slRowCount);
218 }
219 
220 ////////////////////////////////////////////////////////////////////////////////
221 /// Select a database. Returns 0 if successful, non-zero otherwise.
222 /// For SapDB: only to be used to check the dbname.
223 
225 {
226  if (!IsConnected()) {
227  Error("SelectDataBase", "not connected");
228  return -1;
229  }
230 
231  if (fDB != dbname) {
232  Error("SelectDataBase", "no such database");
233  return -1;
234  }
235 
236  return 0;
237 }
238 
239 ////////////////////////////////////////////////////////////////////////////////
240 /// List all available databases. Wild is for wildcarding "t%" list all
241 /// databases starting with "t".
242 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
243 /// The result object must be deleted by the user.
244 /// For SapDB: you are connected to a certain database, so give me a
245 /// list of tables
246 
248 {
249  if (!IsConnected()) {
250  Error("GetDataBases", "not connected");
251  return 0;
252  }
253 
254  return GetTables(fDB, wild);
255 }
256 
257 ////////////////////////////////////////////////////////////////////////////////
258 /// List all tables in the specified database. Wild is for wildcarding
259 /// "t%" list all tables starting with "t".
260 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
261 /// The result object must be deleted by the user.
262 
263 TSQLResult *TSapDBServer::GetTables(const char * /*dbname*/, const char *wild)
264 {
265  if (!IsConnected()) {
266  Error("GetTables", "not connected");
267  return 0;
268  }
269 
270  TString sql = "SELECT TABLENAME FROM TABLES";
271  if (wild)
272  sql += Form(" WHERE TABLENAME LIKE '%s'", wild);
273 
274  return Query(sql);
275 }
276 
277 ////////////////////////////////////////////////////////////////////////////////
278 /// List all columns in specified table in the specified database.
279 /// Wild is for wildcarding "t%" list all columns starting with "t".
280 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
281 /// The result object must be deleted by the user.
282 
283 TSQLResult *TSapDBServer::GetColumns(const char *dbname, const char *table,
284  const char *wild)
285 {
286  if (!IsConnected()) {
287  Error("GetColumns", "not connected");
288  return 0;
289  }
290 
291  if (SelectDataBase(dbname) == 0) {
292  Error("GetColumns", "no such database %s", dbname);
293  return 0;
294  }
295 
296  char *sql;
297  if (wild)
298  sql = Form("SELECT COLUMNNAME FROM COLUMNS WHERE TABLENAME LIKE '%s' AND COLUMNNAME LIKE '%s'", table, wild);
299  else
300  sql = Form("SELECT COLUMNNAME FROM COLUMNS WHERE TABLENAME LIKE '%s'", table);
301 
302  return Query(sql);
303 }
304 
305 ////////////////////////////////////////////////////////////////////////////////
306 /// Create a database. Returns 0 if successful, non-zero otherwise.
307 /// For SapDB: do nothing
308 
309 Int_t TSapDBServer::CreateDataBase(const char * /*dbname*/)
310 {
311  if (!IsConnected()) {
312  Error("CreateDataBase", "not connected");
313  return -1;
314  }
315 
316  Error("CreateDataBase", "not implemented");
317  return 0;
318 }
319 
320 ////////////////////////////////////////////////////////////////////////////////
321 /// Drop (i.e. delete) a database. Returns 0 if successful, non-zero
322 /// otherwise.
323 /// For SapDB: do nothing
324 
325 Int_t TSapDBServer::DropDataBase(const char * /*dbname*/)
326 {
327  if (!IsConnected()) {
328  Error("DropDataBase", "not connected");
329  return -1;
330  }
331 
332  Error("DropDataBase", "not implemented");
333  return 0;
334 }
335 
336 ////////////////////////////////////////////////////////////////////////////////
337 /// Reload permission tables. Returns 0 if successful, non-zero
338 /// otherwise. User must have reload permissions.
339 /// For SapDB: do nothing
340 
342 {
343  if (!IsConnected()) {
344  Error("Reload", "not connected");
345  return -1;
346  }
347 
348  Error("Reload", "not implemented");
349  return 0;
350 }
351 
352 ////////////////////////////////////////////////////////////////////////////////
353 /// Shutdown the database server. Returns 0 if successful, non-zero
354 /// otherwise. User must have shutdown permissions.
355 /// for SapDB: do nothing
356 
358 {
359  if (!IsConnected()) {
360  Error("Shutdown", "not connected");
361  return -1;
362  }
363 
364  Error("Shutdown", "not implemented");
365  return 0;
366 }
367 
368 ////////////////////////////////////////////////////////////////////////////////
369 /// Return server info.
370 
372 {
373  if (!IsConnected()) {
374  Error("ServerInfo", "not connected");
375  return 0;
376  }
377 
378  TString sql = "SELECT KERNEL,RUNTIMEENVIRONMENT FROM DOMAIN.VERSIONS";
379  TSQLResult *res_info = Query(sql);
380 
381  TSQLRow *row_info = res_info->Next();
382 
383  TString info;
384  while (row_info) {
385  info = row_info->GetField(0);
386  info += " ";
387  info += row_info->GetField(1);
388  row_info = res_info->Next();
389  }
390 
391  delete res_info;
392  delete row_info;
393 
394  return info;
395 }
396 
397 ////////////////////////////////////////////////////////////////////////////////
398 /// Print SapDB error message.
399 
400 Int_t TSapDBServer::printSQLError(SQLHDBC hdbc, SQLHSTMT hstmt)
401 {
402  UCHAR sqlstate[10];
403  SDWORD sqlcode;
404  UCHAR errortxt[512+1];
405  SWORD usederrortxt;
406 
407  SQLError(SQL_NULL_HENV, hdbc, hstmt, sqlstate, &sqlcode, errortxt,
408  512, &usederrortxt);
409 
410  printf ("SQL state: %s\n", sqlstate);
411  printf ("SQL code: %ld\n", long(sqlcode));
412  printf ("SQL Errortext:\n%s\n\n", errortxt);
413 
414  return 0;
415 }
SQLHDBC fSapDB
Definition: TSapDBServer.h:31
TString fDB
Definition: TSQLServer.h:46
TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=0)
List all columns in specified table in the specified database.
const char Option_t
Definition: RtypesCore.h:62
TSQLResult * GetDataBases(const char *wild=0)
List all available databases.
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
SQLHSTMT fStmt
Definition: TSapDBServer.h:33
Ssiz_t Index(const char *pat, Ssiz_t i=0, ECaseCompare cmp=kExact) const
Definition: TString.h:634
Basic string class.
Definition: TString.h:131
int Int_t
Definition: RtypesCore.h:41
const char * ServerInfo()
Return server info.
Bool_t IsValid() const
Definition: TUrl.h:82
const char * GetFile() const
Definition: TUrl.h:72
const char * GetHost() const
Definition: TUrl.h:70
Int_t Reload()
Reload permission tables.
TSapDBServer(const char *db, const char *uid, const char *pw)
Open a connection to a SapDB DB server.
SQLHENV fEnv
Definition: TSapDBServer.h:32
Bool_t BeginsWith(const char *s, ECaseCompare cmp=kExact) const
Definition: TString.h:610
virtual void Error(const char *method, const char *msgfmt,...) const
Issue error message.
Definition: TObject.cxx:880
char * Form(const char *fmt,...)
Ssiz_t Length() const
Definition: TString.h:405
TSubString Strip(EStripType s=kTrailing, char c=' ') const
Return a substring of self stripped at beginning and/or end.
Definition: TString.cxx:1081
virtual const char * GetField(Int_t field)=0
Int_t SelectDataBase(const char *dbname)
Select a database.
void Close(Option_t *opt="")
Close connection to SapDB DB server.
TString fHost
Definition: TSQLServer.h:45
Int_t CreateDataBase(const char *dbname)
Create a database.
SQLHSTMT fStmtCnt
Definition: TSapDBServer.h:34
Int_t DropDataBase(const char *dbname)
Drop (i.e.
TSQLResult * GetTables(const char *dbname, const char *wild=0)
List all tables in the specified database.
int Ssiz_t
Definition: RtypesCore.h:63
TSQLResult * Query(const char *sql)
Execute SQL command.
#define ClassImp(name)
Definition: Rtypes.h:359
Int_t fPort
Definition: TSQLServer.h:47
Int_t Shutdown()
Shutdown the database server.
Int_t GetPort() const
Definition: TUrl.h:81
virtual Bool_t IsConnected() const
Definition: TSQLServer.h:95
void MakeZombie()
Definition: TObject.h:49
TString fType
Definition: TSQLServer.h:44
~TSapDBServer()
Close connection to SapDB DB server.
static Int_t printSQLError(SQLHDBC hdbc, SQLHSTMT hstmt)
Print SapDB error message.
virtual TSQLRow * Next()=0
const char * Data() const
Definition: TString.h:364