Logo ROOT   6.14/05
Reference Guide
TODBCServer.cxx
Go to the documentation of this file.
1 // @(#)root/odbc:$Id$
2 // Author: Sergey Linev 6/02/2006
3 
4 /*************************************************************************
5  * Copyright (C) 1995-2006, 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 "TODBCServer.h"
13 
14 #include "TODBCRow.h"
15 #include "TODBCResult.h"
16 #include "TODBCStatement.h"
17 #include "TSQLColumnInfo.h"
18 #include "TSQLTableInfo.h"
19 #include "TUrl.h"
20 #include "TString.h"
21 #include "TObjString.h"
22 #include "TList.h"
23 #include "Riostream.h"
24 
25 
26 #include <sqlext.h>
27 
28 
30 
31 ////////////////////////////////////////////////////////////////////////////////
32 /// Open a connection to a ODBC server. The db arguments can be:
33 /// 1. Form "odbc://[user[:passwd]@]<host>[:<port>][/<database>][?Driver]",
34 /// e.g.: "odbc://pcroot.cern.ch:3306/test?MySQL".
35 /// Driver argument specifies ODBC driver, which should be used for
36 /// connection. By default, MyODBC driver name is used.
37 /// The uid is the username and pw the password that should be used
38 /// for the connection.
39 /// If uid and pw are not specified (==0), user and passwd arguments from
40 /// URL will be used. Works only with MySQL ODBC, probably with PostrSQL
41 /// ODBC.
42 /// 2. Form "odbcd://DRIVER={MyODBC};SERVER=pcroot.cern.ch;DATABASE=test;USER=user;PASSWORD=pass;OPTION=3;PORT=3306;"
43 /// This is a form, which is accepted by SQLDriverConnect function of ODBC.
44 /// Here some other arguments can be specified, which are not included
45 /// in standard URL format.
46 /// 3. Form "odbcn://MySpecialConfig", where MySpecialConfig is entry,
47 /// defined in user DSN (user data source). Here uid and pw should be
48 /// always specified.
49 ///
50 /// Configuring unixODBC under Linux: http://www.unixodbc.org/odbcinst.html
51 /// Remarks: for variants 1 & 2 it is enough to create/configure
52 /// odbcinst.ini file. For variant 3 file odbc.ini should be created.
53 /// Path to this files can be specified in environmental variables like
54 /// export ODBCINI=/home/my/unixODBC/etc/odbc.ini
55 /// export ODBCSYSINI=/home/my/unixODBC/etc
56 ///
57 /// Configuring MySQL ODBC under Windows.
58 /// Installing ODBC driver for MySQL is enough to use it under Windows.
59 /// Afer odbcd:// variant can be used with DRIVER={MySQL ODBC 3.51 Driver};
60 /// To configure User DSN, go into Start menu -> Settings ->
61 /// Control panel -> Administrative tools-> Data Sources (ODBC).
62 ///
63 /// To install Oracle ODBC driver for Windows, one should download
64 /// and install either complete Oracle client (~500 MB), or so-called
65 /// Instant Client Basic and Instant Client ODBC (~20 MB together).
66 /// Some remark about Instant Client:
67 /// 1) Two additional DLLs are required: mfc71.dll & msver71.dll
68 /// They can be found either in MS VC++ 7.1 Free Toolkit or
69 /// downloaded from other Internet sites
70 /// 2) ORACLE_HOME environment variable should be specified and point to
71 /// location, where Instant Client files are extracted
72 /// 3) Run odbc_install.exe from account with administrative rights
73 /// 3) In $ORACLE_HOME/network/admin/ directory appropriate *.ora files
74 /// like ldap.ora, sqlnet.ora, tnsnames.ora should be installed.
75 /// Contact your Oracle administrator to get these files.
76 /// After Oracle ODBC driver is installed, appropriate entry in ODBC drivers
77 /// list like "Oracle in instantclient10_2" should appiar. Connection
78 /// string example:
79 /// "odbcd://DRIVER={Oracle in instantclient10_2};DBQ=db-test;UID=user_name;PWD=user_pass;";
80 
81 TODBCServer::TODBCServer(const char *db, const char *uid, const char *pw) :
82  TSQLServer()
83 {
84  TString connstr;
85  Bool_t simpleconnect = kTRUE;
86 
87  SQLRETURN retcode;
88  SQLHWND hwnd;
89 
90  fPort = 1; // indicate that we are connected
91 
92  if ((strncmp(db, "odbc", 4)!=0) || (strlen(db)<8)) {
93  SetError(-1, "db argument should be started from odbc...","TODBCServer");
94  goto zombie;
95  }
96 
97  if (strncmp(db, "odbc://", 7)==0) {
98  TUrl url(db);
99  if (!url.IsValid()) {
100  SetError(-1, Form("not valid URL: %s", db), "TODBCServer");
101  goto zombie;
102  }
103  const char* driver = "MyODBC";
104  const char* dbase = url.GetFile();
105  if (dbase!=0)
106  if (*dbase=='/') dbase++; //skip leading "/" if appears
107 
108  if (((uid==0) || (*uid==0)) && (strlen(url.GetUser())>0)) {
109  uid = url.GetUser();
110  pw = url.GetPasswd();
111  }
112 
113  if (strlen(url.GetOptions())!=0) driver = url.GetOptions();
114 
115  connstr.Form("DRIVER={%s};"
116  "SERVER=%s;"
117  "DATABASE=%s;"
118  "USER=%s;"
119  "PASSWORD=%s;"
120  "OPTION=3;",
121  driver, url.GetHost(), dbase, uid, pw);
122  if (url.GetPort()>0)
123  connstr += Form("PORT=%d;", url.GetPort());
124 
125  fHost = url.GetHost();
126  fPort = url.GetPort()>0 ? url.GetPort() : 1;
127  fDB = dbase;
128  simpleconnect = kFALSE;
129  } else
130  if (strncmp(db, "odbcd://", 8)==0) {
131  connstr = db+8;
132  simpleconnect = kFALSE;
133  } else
134  if (strncmp(db, "odbcn://", 8)==0) {
135  connstr = db+8;
136  simpleconnect = kTRUE;
137  } else {
138  SetError(-1, "db argument is invalid", "TODBCServer");
139  goto zombie;
140  }
141 
142  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &fHenv);
143  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
144 
145  /* Set the ODBC version environment attribute */
146  retcode = SQLSetEnvAttr(fHenv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
147  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
148 
149  /* Allocate connection handle */
150  retcode = SQLAllocHandle(SQL_HANDLE_DBC, fHenv, &fHdbc);
151  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
152 
153  /* Set login timeout to 5 seconds. */
154  retcode = SQLSetConnectAttr(fHdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER) 5, 0);
155  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
156 
157  char sbuf[2048];
158 
159  SQLSMALLINT reslen;
160  SQLINTEGER reslen1;
161 
162  hwnd = 0;
163 
164  if (simpleconnect)
165  retcode = SQLConnect(fHdbc, (SQLCHAR*) connstr.Data(), SQL_NTS,
166  (SQLCHAR*) uid, SQL_NTS,
167  (SQLCHAR*) pw, SQL_NTS);
168  else
169  retcode = SQLDriverConnect(fHdbc, hwnd,
170  (SQLCHAR*) connstr.Data(), SQL_NTS,
171  (SQLCHAR*) sbuf, sizeof(sbuf), &reslen, SQL_DRIVER_NOPROMPT);
172 
173  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
174 
175  fType = "ODBC";
176 
177  retcode = SQLGetInfo(fHdbc, SQL_USER_NAME, sbuf, sizeof(sbuf), &reslen);
178  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
179  fUserId = sbuf;
180 
181  retcode = SQLGetInfo(fHdbc, SQL_DBMS_NAME, sbuf, sizeof(sbuf), &reslen);
182  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
183  fServerInfo = sbuf;
184  fType = sbuf;
185 
186  retcode = SQLGetInfo(fHdbc, SQL_DBMS_VER, sbuf, sizeof(sbuf), &reslen);
187  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
188  fServerInfo += " ";
189  fServerInfo += sbuf;
190 
191  // take current catalog - database name
192  retcode = SQLGetConnectAttr(fHdbc, SQL_ATTR_CURRENT_CATALOG, sbuf, sizeof(sbuf), &reslen1);
193  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
194  if (fDB.Length()==0) fDB = sbuf;
195 
196  retcode = SQLGetInfo(fHdbc, SQL_SERVER_NAME, sbuf, sizeof(sbuf), &reslen);
197  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
198  if (fHost.Length()==0) fHost = sbuf;
199 
200 /*
201 
202  SQLUINTEGER iinfo;
203  retcode = SQLGetInfo(fHdbc, SQL_PARAM_ARRAY_ROW_COUNTS, &iinfo, sizeof(iinfo), 0);
204  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
205  Info("Constr", "SQL_PARAM_ARRAY_ROW_COUNTS = %u", iinfo);
206 
207  retcode = SQLGetInfo(fHdbc, SQL_PARAM_ARRAY_SELECTS, &iinfo, sizeof(iinfo), 0);
208  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
209  Info("Constr", "SQL_PARAM_ARRAY_SELECTS = %u", iinfo);
210 
211  retcode = SQLGetInfo(fHdbc, SQL_BATCH_ROW_COUNT, &iinfo, sizeof(iinfo), 0);
212  if (ExtractErrors(retcode, "TODBCServer")) goto zombie;
213  Info("Constr", "SQL_BATCH_ROW_COUNT = %u", iinfo);
214 */
215 
216  return;
217 
218 zombie:
219  fPort = -1;
220  fHost = "";
221  MakeZombie();
222 }
223 
224 ////////////////////////////////////////////////////////////////////////////////
225 /// Close connection to MySQL DB server.
226 
228 {
229  if (IsConnected())
230  Close();
231 }
232 
233 ////////////////////////////////////////////////////////////////////////////////
234 /// Produce TList object with list of available
235 /// ODBC drivers (isdrivers = kTRUE) or data sources (isdrivers = kFALSE)
236 
238 {
239  SQLHENV henv;
240  SQLRETURN retcode;
241 
242  retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
243  if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO)) return 0;
244 
245  retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
246  if ((retcode!=SQL_SUCCESS) && (retcode!=SQL_SUCCESS_WITH_INFO)) return 0;
247 
248  TList* lst = 0;
249 
250  char namebuf[2048], optbuf[2048];
251  SQLSMALLINT reslen1, reslen2;
252 
253  do {
254  strlcpy(namebuf, "",2048);
255  strlcpy(optbuf, "",2048);
256  if (isdrivers)
257  retcode = SQLDrivers(henv, (lst==0 ? SQL_FETCH_FIRST : SQL_FETCH_NEXT),
258  (SQLCHAR*) namebuf, sizeof(namebuf), &reslen1,
259  (SQLCHAR*) optbuf, sizeof(optbuf), &reslen2);
260  else
261  retcode = SQLDataSources(henv, (lst==0 ? SQL_FETCH_FIRST : SQL_FETCH_NEXT),
262  (SQLCHAR*) namebuf, sizeof(namebuf), &reslen1,
263  (SQLCHAR*) optbuf, sizeof(optbuf), &reslen2);
264 
265  if (retcode==SQL_NO_DATA) break;
266  if ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO)) {
267  if (lst==0) {
268  lst = new TList;
269  lst->SetOwner(kTRUE);
270  }
271  for (int n=0;n<reslen2-1;n++)
272  if (optbuf[n]=='\0') optbuf[n] = ';';
273 
274  lst->Add(new TNamed(namebuf, optbuf));
275  }
276  } while ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO));
277 
278  SQLFreeHandle(SQL_HANDLE_ENV, henv);
279 
280  return lst;
281 
282 }
283 
284 
285 ////////////////////////////////////////////////////////////////////////////////
286 /// Produce TList object with list of available ODBC drivers
287 /// User must delete TList object aftewards
288 /// Name of driver can be used in connecting to data base in form
289 /// TSQLServer::Connect("odbcd://DRIVER={<drivername>};DBQ=<dbname>;UID=user;PWD=pass;", 0, 0);
290 
292 {
293  return ListData(kTRUE);
294 }
295 
296 ////////////////////////////////////////////////////////////////////////////////
297 /// Print list of ODBC drivers in form:
298 /// <name> : <options list>
299 
301 {
302  TList* lst = GetDrivers();
303  std::cout << "List of ODBC drivers:" << std::endl;
304  TIter iter(lst);
305  TNamed* n = 0;
306  while ((n = (TNamed*) iter()) != 0)
307  std::cout << " " << n->GetName() << " : " << n->GetTitle() << std::endl;
308  delete lst;
309 }
310 
311 ////////////////////////////////////////////////////////////////////////////////
312 /// Produce TList object with list of available ODBC data sources
313 /// User must delete TList object aftewards
314 /// Name of data source can be used later for connection:
315 /// TSQLServer::Connect("odbcn://<data_source_name>", "user", "pass");
316 
318 {
319  return ListData(kFALSE);
320 }
321 
322 ////////////////////////////////////////////////////////////////////////////////
323 /// Print list of ODBC data sources in form:
324 /// <name> : <options list>
325 
327 {
328  TList* lst = GetDataSources();
329  std::cout << "List of ODBC data sources:" << std::endl;
330  TIter iter(lst);
331  TNamed* n = 0;
332  while ((n = (TNamed*) iter()) != 0)
333  std::cout << " " << n->GetName() << " : " << n->GetTitle() << std::endl;
334  delete lst;
335 }
336 
337 ////////////////////////////////////////////////////////////////////////////////
338 /// Extract errors, produced by last ODBC function call
339 
340 Bool_t TODBCServer::ExtractErrors(SQLRETURN retcode, const char* method)
341 {
342  if ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO)) return kFALSE;
343 
344  SQLINTEGER i = 0;
345  SQLINTEGER native;
346  SQLCHAR state[7];
347  SQLCHAR text[256];
348  SQLSMALLINT len;
349 
350  while (SQLGetDiagRec(SQL_HANDLE_ENV, fHenv, ++i, state, &native, text,
351  sizeof(text), &len ) == SQL_SUCCESS)
352  //Error(method, "%s:%ld:%ld:%s\n", state, i, native, text);
353  SetError(native, (const char*) text, method);
354 
355  i = 0;
356 
357  while (SQLGetDiagRec(SQL_HANDLE_DBC, fHdbc, ++i, state, &native, text,
358  sizeof(text), &len ) == SQL_SUCCESS)
359 // Error(method, "%s:%ld:%ld:%s\n", state, i, native, text);
360  SetError(native, (const char*) text, method);
361 
362  return kTRUE;
363 }
364 
365 // Reset error and check that server connected
366 #define CheckConnect(method, res) \
367  { \
368  ClearError(); \
369  if (!IsConnected()) { \
370  SetError(-1,"ODBC driver is not connected",method); \
371  return res; \
372  } \
373  }
374 
375 ////////////////////////////////////////////////////////////////////////////////
376 /// Close connection to MySQL DB server.
377 
379 {
380  SQLDisconnect(fHdbc);
381  SQLFreeHandle(SQL_HANDLE_DBC, fHdbc);
382  SQLFreeHandle(SQL_HANDLE_ENV, fHenv);
383  fPort = -1;
384 }
385 
386 ////////////////////////////////////////////////////////////////////////////////
387 /// Execute SQL command. Result object must be deleted by the user.
388 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
389 /// The result object must be deleted by the user.
390 
392 {
393  CheckConnect("Query", 0);
394 
395  SQLRETURN retcode;
396  SQLHSTMT hstmt;
397 
398  SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
399 
400  retcode = SQLExecDirect(hstmt, (SQLCHAR*) sql, SQL_NTS);
401  if (ExtractErrors(retcode, "Query")) {
402  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
403  return 0;
404  }
405 
406  return new TODBCResult(hstmt);
407 }
408 
409 ////////////////////////////////////////////////////////////////////////////////
410 /// Executes query which does not produce any results set
411 /// Return kTRUE if successfull
412 
413 Bool_t TODBCServer::Exec(const char* sql)
414 {
415  CheckConnect("Exec", 0);
416 
417  SQLRETURN retcode;
418  SQLHSTMT hstmt;
419 
420  SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
421 
422  retcode = SQLExecDirect(hstmt, (SQLCHAR*) sql, SQL_NTS);
423 
424  Bool_t res = !ExtractErrors(retcode, "Exec");
425 
426  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
427 
428  return res;
429 }
430 
431 ////////////////////////////////////////////////////////////////////////////////
432 /// Select a database. Returns 0 if successful, non-zero otherwise.
433 /// Not all RDBMS support selecting of database (catalog) after connecting
434 /// Normally user should specify database name at time of connection
435 
437 {
438  CheckConnect("SelectDataBase", -1);
439 
440  SQLRETURN retcode = SQLSetConnectAttr(fHdbc, SQL_ATTR_CURRENT_CATALOG, (SQLCHAR*) db, SQL_NTS);
441  if (ExtractErrors(retcode, "SelectDataBase")) return -1;
442 
443  fDB = db;
444 
445  return 0;
446 }
447 
448 ////////////////////////////////////////////////////////////////////////////////
449 /// List all available databases. Wild is for wildcarding "t%" list all
450 /// databases starting with "t".
451 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
452 /// The result object must be deleted by the user.
453 
455 {
456  CheckConnect("GetDataBases", 0);
457 
458  return 0;
459 }
460 
461 ////////////////////////////////////////////////////////////////////////////////
462 /// List all tables in the specified database. Wild is for wildcarding
463 /// "t%" list all tables starting with "t".
464 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
465 /// The result object must be deleted by the user.
466 
467 TSQLResult *TODBCServer::GetTables(const char*, const char* wild)
468 {
469  CheckConnect("GetTables", 0);
470 
471  SQLRETURN retcode;
472  SQLHSTMT hstmt;
473 
474  SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
475 
476  SQLCHAR* schemaName = 0;
477  SQLSMALLINT schemaNameLength = 0;
478 
479 /*
480  TString schemabuf;
481  // schema is used by Oracle to specify to which user belong table
482  // therefore, to see correct tables, schema name is set to user name
483  if ((fUserId.Length()>0) && (fServerInfo.Contains("Oracle"))) {
484  schemabuf = fUserId;
485  schemabuf.ToUpper();
486  schemaName = (SQLCHAR*) schemabuf.Data();
487  schemaNameLength = schemabuf.Length();
488  }
489 */
490 
491  SQLCHAR* tableName = 0;
492  SQLSMALLINT tableNameLength = 0;
493 
494  if ((wild!=0) && (strlen(wild)!=0)) {
495  tableName = (SQLCHAR*) wild;
496  tableNameLength = strlen(wild);
497  SQLSetStmtAttr(hstmt, SQL_ATTR_METADATA_ID, (SQLPOINTER) SQL_FALSE, 0);
498  }
499 
500  retcode = SQLTables(hstmt, NULL, 0, schemaName, schemaNameLength, tableName, tableNameLength, (SQLCHAR*) "TABLE", 5);
501  if (ExtractErrors(retcode, "GetTables")) {
502  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
503  return 0;
504  }
505 
506  return new TODBCResult(hstmt);
507 }
508 
509 ////////////////////////////////////////////////////////////////////////////////
510 /// Return list of tables in database
511 /// See TSQLServer::GetTablesList() for details.
512 
514 {
515  CheckConnect("GetTablesList", 0);
516 
517  TSQLResult* res = GetTables(0, wild);
518  if (res==0) return 0;
519 
520  TList* lst = 0;
521 
522  TSQLRow* row = 0;
523 
524  while ((row = res->Next())!=0) {
525  const char* tablename = row->GetField(2);
526  if (tablename!=0) {
527 // Info("List","%s %s %s %s %s", tablename, row->GetField(0), row->GetField(1), row->GetField(3), row->GetField(4));
528  if (lst==0) {
529  lst = new TList;
530  lst->SetOwner(kTRUE);
531  }
532  lst->Add(new TObjString(tablename));
533  }
534  delete row;
535  }
536 
537  delete res;
538 
539  return lst;
540 }
541 
542 
543 ////////////////////////////////////////////////////////////////////////////////
544 /// Produces SQL table info
545 /// Object must be deleted by user
546 
548 {
549  CheckConnect("GetTableInfo", 0);
550 
551  #define STR_LEN 128+1
552  #define REM_LEN 254+1
553 
554  /* Declare buffers for result set data */
555 
556  SQLCHAR szCatalog[STR_LEN], szSchema[STR_LEN];
557  SQLCHAR szTableName[STR_LEN], szColumnName[STR_LEN];
558  SQLCHAR szTypeName[STR_LEN], szRemarks[REM_LEN];
559  SQLCHAR szColumnDefault[STR_LEN], szIsNullable[STR_LEN];
560  SQLLEN columnSize, bufferLength, charOctetLength, ordinalPosition;
561  SQLSMALLINT dataType, decimalDigits, numPrecRadix, nullable;
562  SQLSMALLINT sqlDataType, datetimeSubtypeCode;
563  SQLRETURN retcode;
564  SQLHSTMT hstmt;
565 
566  /* Declare buffers for bytes available to return */
567 
568  SQLLEN cbCatalog, cbSchema, cbTableName, cbColumnName;
569  SQLLEN cbDataType, cbTypeName, cbColumnSize, cbBufferLength;
570  SQLLEN cbDecimalDigits, cbNumPrecRadix, cbNullable, cbRemarks;
571  SQLLEN cbColumnDefault, cbSQLDataType, cbDatetimeSubtypeCode, cbCharOctetLength;
572  SQLLEN cbOrdinalPosition, cbIsNullable;
573 
574 
575  SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
576 
577  retcode = SQLColumns(hstmt, NULL, 0, NULL, 0, (SQLCHAR*) tablename, SQL_NTS, NULL, 0);
578  if (ExtractErrors(retcode, "GetTableInfo")) {
579  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
580  return 0;
581  }
582 
583  TList* lst = 0;
584 
585  /* Bind columns in result set to buffers */
586 
587  SQLBindCol(hstmt, 1, SQL_C_CHAR, szCatalog, STR_LEN,&cbCatalog);
588  SQLBindCol(hstmt, 2, SQL_C_CHAR, szSchema, STR_LEN, &cbSchema);
589  SQLBindCol(hstmt, 3, SQL_C_CHAR, szTableName, STR_LEN,&cbTableName);
590  SQLBindCol(hstmt, 4, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);
591  SQLBindCol(hstmt, 5, SQL_C_SSHORT, &dataType, 0, &cbDataType);
592  SQLBindCol(hstmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);
593  SQLBindCol(hstmt, 7, SQL_C_SLONG, &columnSize, 0, &cbColumnSize);
594  SQLBindCol(hstmt, 8, SQL_C_SLONG, &bufferLength, 0, &cbBufferLength);
595  SQLBindCol(hstmt, 9, SQL_C_SSHORT, &decimalDigits, 0, &cbDecimalDigits);
596  SQLBindCol(hstmt, 10, SQL_C_SSHORT, &numPrecRadix, 0, &cbNumPrecRadix);
597  SQLBindCol(hstmt, 11, SQL_C_SSHORT, &nullable, 0, &cbNullable);
598  SQLBindCol(hstmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, &cbRemarks);
599  SQLBindCol(hstmt, 13, SQL_C_CHAR, szColumnDefault, STR_LEN, &cbColumnDefault);
600  SQLBindCol(hstmt, 14, SQL_C_SSHORT, &sqlDataType, 0, &cbSQLDataType);
601  SQLBindCol(hstmt, 15, SQL_C_SSHORT, &datetimeSubtypeCode, 0, &cbDatetimeSubtypeCode);
602  SQLBindCol(hstmt, 16, SQL_C_SLONG, &charOctetLength, 0, &cbCharOctetLength);
603  SQLBindCol(hstmt, 17, SQL_C_SLONG, &ordinalPosition, 0, &cbOrdinalPosition);
604  SQLBindCol(hstmt, 18, SQL_C_CHAR, szIsNullable, STR_LEN, &cbIsNullable);
605 
606  retcode = SQLFetch(hstmt);
607 
608  while ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO)) {
609 
610  Int_t sqltype = kSQL_NONE;
611 
612  Int_t data_size = -1; // size in bytes
613  Int_t data_length = -1; // declaration like VARCHAR(n) or NUMERIC(n)
614  Int_t data_scale = -1; // second argument in declaration
615  Int_t data_sign = -1; // no info about sign
616 
617  switch (dataType) {
618  case SQL_CHAR:
619  sqltype = kSQL_CHAR;
620  data_size = columnSize;
621  data_length = charOctetLength;
622  break;
623  case SQL_VARCHAR:
624  case SQL_LONGVARCHAR:
625  sqltype = kSQL_VARCHAR;
626  data_size = columnSize;
627  data_length = charOctetLength;
628  break;
629  case SQL_DECIMAL:
630  case SQL_NUMERIC:
631  sqltype = kSQL_NUMERIC;
632  data_size = columnSize; // size of column in database
633  data_length = columnSize;
634  data_scale = decimalDigits;
635  break;
636  case SQL_INTEGER:
637  case SQL_TINYINT:
638  case SQL_BIGINT:
639  sqltype = kSQL_INTEGER;
640  data_size = columnSize;
641  break;
642  case SQL_REAL:
643  case SQL_FLOAT:
644  sqltype = kSQL_FLOAT;
645  data_size = columnSize;
646  data_sign = 1;
647  break;
648  case SQL_DOUBLE:
649  sqltype = kSQL_DOUBLE;
650  data_size = columnSize;
651  data_sign = 1;
652  break;
653  case SQL_BINARY:
654  case SQL_VARBINARY:
655  case SQL_LONGVARBINARY:
656  sqltype = kSQL_BINARY;
657  data_size = columnSize;
658  break;
659  case SQL_TYPE_TIMESTAMP:
660  sqltype = kSQL_TIMESTAMP;
661  data_size = columnSize;
662  break;
663  }
664 
665  if (lst==0) lst = new TList;
666 
667  lst->Add(new TSQLColumnInfo((const char*) szColumnName,
668  (const char*) szTypeName,
669  nullable!=0,
670  sqltype,
671  data_size,
672  data_length,
673  data_scale,
674  data_sign));
675 
676  retcode = SQLFetch(hstmt);
677  }
678 
679  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
680 
681  return new TSQLTableInfo(tablename, lst);
682 }
683 
684 ////////////////////////////////////////////////////////////////////////////////
685 /// List all columns in specified table in the specified database.
686 /// Wild is for wildcarding "t%" list all columns starting with "t".
687 /// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
688 /// The result object must be deleted by the user.
689 
690 TSQLResult *TODBCServer::GetColumns(const char*, const char *table, const char*)
691 {
692  CheckConnect("GetColumns", 0);
693 
694  SQLRETURN retcode;
695  SQLHSTMT hstmt;
696 
697  SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
698 
699  retcode = SQLColumns(hstmt, NULL, 0, NULL, 0, (SQLCHAR*) table, SQL_NTS, NULL, 0);
700  if (ExtractErrors(retcode, "GetColumns")) {
701  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
702  return 0;
703  }
704 
705  return new TODBCResult(hstmt);
706 }
707 
708 ////////////////////////////////////////////////////////////////////////////////
709 /// returns maximum allowed length of identifier (table name, column name, index name)
710 
712 {
713  CheckConnect("GetMaxIdentifierLength", 20);
714 
715  SQLUINTEGER info = 0;
716  SQLRETURN retcode;
717 
718  retcode = SQLGetInfo(fHdbc, SQL_MAX_IDENTIFIER_LEN, (SQLPOINTER)&info, sizeof(info), NULL);
719 
720  if (ExtractErrors(retcode, "GetMaxIdentifierLength")) return 20;
721 
722  return info;
723 }
724 
725 ////////////////////////////////////////////////////////////////////////////////
726 /// Create a database. Returns 0 if successful, non-zero otherwise.
727 
729 {
730  CheckConnect("CreateDataBase", -1);
731 
732  return -1;
733 }
734 
735 ////////////////////////////////////////////////////////////////////////////////
736 /// Drop (i.e. delete) a database. Returns 0 if successful, non-zero
737 /// otherwise.
738 
740 {
741  CheckConnect("DropDataBase", -1);
742 
743  return -1;
744 }
745 
746 ////////////////////////////////////////////////////////////////////////////////
747 /// Reload permission tables. Returns 0 if successful, non-zero
748 /// otherwise. User must have reload permissions.
749 
751 {
752  CheckConnect("Reload", -1);
753 
754  return -1;
755 }
756 
757 ////////////////////////////////////////////////////////////////////////////////
758 /// Shutdown the database server. Returns 0 if successful, non-zero
759 /// otherwise. User must have shutdown permissions.
760 
762 {
763  CheckConnect("Shutdown", -1);
764 
765  return -1;
766 }
767 
768 ////////////////////////////////////////////////////////////////////////////////
769 /// Return server info.
770 
772 {
773  CheckConnect("ServerInfo", 0);
774 
775  return fServerInfo;
776 }
777 
778 ////////////////////////////////////////////////////////////////////////////////
779 /// Creates ODBC statement for provided query.
780 /// See TSQLStatement class for more details.
781 
782 TSQLStatement *TODBCServer::Statement(const char *sql, Int_t bufsize)
783 {
784  CheckConnect("Statement", 0);
785 
786  if (!sql || !*sql) {
787  SetError(-1, "no query string specified", "Statement");
788  return 0;
789  }
790 
791 // SQLUINTEGER info = 0;
792 // SQLGetInfo(fHdbc, SQL_PARAM_ARRAY_ROW_COUNTS, (SQLPOINTER)&info, sizeof(info), NULL);
793 // if (info==SQL_PARC_BATCH) Info("Statement","info==SQL_PARC_BATCH"); else
794 // if (info==SQL_PARC_NO_BATCH) Info("Statement","info==SQL_PARC_NO_BATCH"); else
795 // Info("Statement","info==%u", info);
796 
797 
798  SQLRETURN retcode;
799  SQLHSTMT hstmt;
800 
801  retcode = SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
802  if (ExtractErrors(retcode, "Statement")) return 0;
803 
804  retcode = SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS);
805  if (ExtractErrors(retcode, "Statement")) {
806  SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
807  return 0;
808  }
809 
810  return new TODBCStatement(hstmt, bufsize, fErrorOut);
811 }
812 
813 ////////////////////////////////////////////////////////////////////////////////
814 /// Starts transaction.
815 /// Check for transaction support.
816 /// Switch off autocommitment mode.
817 
819 {
820  CheckConnect("StartTransaction", kFALSE);
821 
822  SQLUINTEGER info = 0;
823  SQLRETURN retcode;
824 
825  retcode = SQLGetInfo(fHdbc, SQL_TXN_CAPABLE, (SQLPOINTER)&info, sizeof(info), NULL);
826  if (ExtractErrors(retcode, "StartTransaction")) return kFALSE;
827 
828  if (info==0) {
829  SetError(-1,"Transactions not supported","StartTransaction");
830  return kFALSE;
831  }
832 
833  if (!Commit()) return kFALSE;
834 
835  retcode = SQLSetConnectAttr(fHdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, 0);
836  if (ExtractErrors(retcode, "StartTransaction")) return kFALSE;
837 
838  return kTRUE;
839 }
840 
841 ////////////////////////////////////////////////////////////////////////////////
842 /// Complete current transaction (commit = kTRUE) or rollback
843 /// Switches on autocommit mode of ODBC driver
844 
846 {
847  const char* method = commit ? "Commit" : "Rollback";
848 
849  CheckConnect(method, kFALSE);
850 
851  SQLRETURN retcode = SQLEndTran(SQL_HANDLE_DBC, fHdbc, commit ? SQL_COMMIT : SQL_ROLLBACK);
852  if (ExtractErrors(retcode, method)) return kFALSE;
853 
854  retcode = SQLSetConnectAttr(fHdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0);
855 
856  return kTRUE;
857 }
858 
859 ////////////////////////////////////////////////////////////////////////////////
860 /// Commit transaction
861 
863 {
864  return EndTransaction(kTRUE);
865 }
866 
867 ////////////////////////////////////////////////////////////////////////////////
868 /// Rollback transaction
869 
871 {
872  return EndTransaction(kFALSE);
873 }
virtual const char * GetName() const
Returns name of object.
Definition: TNamed.h:47
TString fDB
Definition: TSQLServer.h:46
Int_t SelectDataBase(const char *dbname)
Select a database.
static void PrintDrivers()
Print list of ODBC drivers in form: <name> : <options list>="">
Collectable string class.
Definition: TObjString.h:28
static TList * ListData(Bool_t isdrivers)
Produce TList object with list of available ODBC drivers (isdrivers = kTRUE) or data sources (isdrive...
const char Option_t
Definition: RtypesCore.h:62
Bool_t EndTransaction(Bool_t commit)
Complete current transaction (commit = kTRUE) or rollback Switches on autocommit mode of ODBC driver...
TSQLResult * Query(const char *sql)
Execute SQL command.
This class represents a WWW compatible URL.
Definition: TUrl.h:35
virtual void SetOwner(Bool_t enable=kTRUE)
Set whether this collection is the owner (enable==true) of its content.
TString fServerInfo
Definition: TODBCServer.h:35
TSQLTableInfo * GetTableInfo(const char *tablename)
Produces SQL table info Object must be deleted by user.
Basic string class.
Definition: TString.h:131
int Int_t
Definition: RtypesCore.h:41
bool Bool_t
Definition: RtypesCore.h:59
const char * GetOptions() const
Definition: TUrl.h:74
Bool_t IsValid() const
Definition: TUrl.h:82
Bool_t Exec(const char *sql)
Executes query which does not produce any results set Return kTRUE if successfull.
const char * GetFile() const
Definition: TUrl.h:72
void Close(Option_t *opt="")
Close connection to MySQL DB server.
const char * GetHost() const
Definition: TUrl.h:70
The TNamed class is the base class for all named ROOT classes.
Definition: TNamed.h:29
static TList * GetDataSources()
Produce TList object with list of available ODBC data sources User must delete TList object aftewards...
A doubly linked list.
Definition: TList.h:44
#define STR_LEN
const char * GetUser() const
Definition: TUrl.h:68
const char * GetPasswd() const
Definition: TUrl.h:69
#define REM_LEN
TList * GetTablesList(const char *wild=0)
Return list of tables in database See TSQLServer::GetTablesList() for details.
void Form(const char *fmt,...)
Formats a string using a printf style format descriptor.
Definition: TString.cxx:2264
SQLHDBC fHdbc
Definition: TODBCServer.h:34
char * Form(const char *fmt,...)
Ssiz_t Length() const
Definition: TString.h:405
virtual const char * GetField(Int_t field)=0
Bool_t ExtractErrors(SQLRETURN retcode, const char *method)
Extract errors, produced by last ODBC function call.
static TList * GetDrivers()
Produce TList object with list of available ODBC drivers User must delete TList object aftewards Name...
TSQLStatement * Statement(const char *sql, Int_t=100)
Creates ODBC statement for provided query.
TString fHost
Definition: TSQLServer.h:45
const Bool_t kFALSE
Definition: RtypesCore.h:88
Bool_t StartTransaction()
Starts transaction.
TString fUserId
Definition: TODBCServer.h:36
#define ClassImp(name)
Definition: Rtypes.h:359
Int_t fPort
Definition: TSQLServer.h:47
Int_t Shutdown()
Shutdown the database server.
TText * text
#define CheckConnect(method, res)
TSQLResult * GetTables(const char *dbname, const char *wild=0)
List all tables in the specified database.
SQLHENV fHenv
Definition: TODBCServer.h:33
Int_t DropDataBase(const char *dbname)
Drop (i.e.
Int_t GetPort() const
Definition: TUrl.h:81
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
Bool_t Commit()
Commit transaction.
Int_t Reload()
Reload permission tables.
virtual Bool_t IsConnected() const
Definition: TSQLServer.h:95
static void PrintDataSources()
Print list of ODBC data sources in form: <name> : <options list>="">
const char * ServerInfo()
Return server info.
TSQLResult * GetDataBases(const char *wild=0)
List all available databases.
virtual void Add(TObject *obj)
Definition: TList.h:87
void MakeZombie()
Definition: TObject.h:49
virtual ~TODBCServer()
Close connection to MySQL DB server.
Bool_t Rollback()
Rollback transaction.
TString fType
Definition: TSQLServer.h:44
const Bool_t kTRUE
Definition: RtypesCore.h:87
TODBCServer(const char *db, const char *uid, const char *pw)
Open a connection to a ODBC server.
Definition: TODBCServer.cxx:81
const Int_t n
Definition: legend1.C:16
virtual const char * GetTitle() const
Returns title of object.
Definition: TNamed.h:48
Int_t CreateDataBase(const char *dbname)
Create a database. Returns 0 if successful, non-zero otherwise.
TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=0)
List all columns in specified table in the specified database.
Bool_t fErrorOut
Definition: TSQLServer.h:50
virtual TSQLRow * Next()=0
const char * Data() const
Definition: TString.h:364
Int_t GetMaxIdentifierLength()
returns maximum allowed length of identifier (table name, column name, index name) ...