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