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/// downloaded 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 appiar. 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!=0)
108 if (*dbase=='/') dbase++; //skip leading "/" if appears
109
110 if (((uid==0) || (*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 = 0;
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 0;
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 0;
249
250 TList* lst = 0;
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==0 ? 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==0 ? 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==0) {
270 lst = new TList;
271 lst->SetOwner(kTRUE);
272 }
273 for (int n=0;n<reslen2-1;n++)
274 if (optbuf[n]=='\0') optbuf[n] = ';';
275
276 lst->Add(new TNamed(namebuf, optbuf));
277 }
278 } while ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO));
279
280 SQLFreeHandle(SQL_HANDLE_ENV, henv);
281
282 return lst;
283
284}
285
286
287////////////////////////////////////////////////////////////////////////////////
288/// Produce TList object with list of available ODBC drivers
289/// User must delete TList object aftewards
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 TNamed* n = 0;
308 while ((n = (TNamed*) iter()) != 0)
309 std::cout << " " << n->GetName() << " : " << n->GetTitle() << std::endl;
310 delete lst;
311}
312
313////////////////////////////////////////////////////////////////////////////////
314/// Produce TList object with list of available ODBC data sources
315/// User must delete TList object aftewards
316/// Name of data source can be used later for connection:
317/// TSQLServer::Connect("odbcn://<data_source_name>", "user", "pass");
318
320{
321 return ListData(kFALSE);
322}
323
324////////////////////////////////////////////////////////////////////////////////
325/// Print list of ODBC data sources in form:
326/// `<name>` : `<options list>`
327
329{
330 TList* lst = GetDataSources();
331 std::cout << "List of ODBC data sources:" << std::endl;
332 TIter iter(lst);
333 TNamed* n = 0;
334 while ((n = (TNamed*) iter()) != 0)
335 std::cout << " " << n->GetName() << " : " << n->GetTitle() << std::endl;
336 delete lst;
337}
338
339////////////////////////////////////////////////////////////////////////////////
340/// Extract errors, produced by last ODBC function call
341
342Bool_t TODBCServer::ExtractErrors(SQLRETURN retcode, const char* method)
343{
344 if ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO)) return kFALSE;
345
346 SQLINTEGER i = 0;
347 SQLINTEGER native;
348 SQLCHAR state[7];
349 SQLCHAR text[256];
350 SQLSMALLINT len;
351
352 while (SQLGetDiagRec(SQL_HANDLE_ENV, fHenv, ++i, state, &native, text,
353 sizeof(text), &len ) == SQL_SUCCESS)
354 //Error(method, "%s:%ld:%ld:%s\n", state, i, native, text);
355 SetError(native, (const char*) text, method);
356
357 i = 0;
358
359 while (SQLGetDiagRec(SQL_HANDLE_DBC, fHdbc, ++i, state, &native, text,
360 sizeof(text), &len ) == SQL_SUCCESS)
361// Error(method, "%s:%ld:%ld:%s\n", state, i, native, text);
362 SetError(native, (const char*) text, method);
363
364 return kTRUE;
365}
366
367// Reset error and check that server connected
368#define CheckConnect(method, res) \
369 { \
370 ClearError(); \
371 if (!IsConnected()) { \
372 SetError(-1,"ODBC driver is not connected",method); \
373 return res; \
374 } \
375 }
376
377////////////////////////////////////////////////////////////////////////////////
378/// Close connection to MySQL DB server.
379
381{
382 SQLDisconnect(fHdbc);
383 SQLFreeHandle(SQL_HANDLE_DBC, fHdbc);
384 SQLFreeHandle(SQL_HANDLE_ENV, fHenv);
385 fPort = -1;
386}
387
388////////////////////////////////////////////////////////////////////////////////
389/// Execute SQL command. Result object must be deleted by the user.
390/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
391/// The result object must be deleted by the user.
392
394{
395 CheckConnect("Query", 0);
396
397 SQLRETURN retcode;
398 SQLHSTMT hstmt;
399
400 SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
401
402 retcode = SQLExecDirect(hstmt, (SQLCHAR*) sql, SQL_NTS);
403 if (ExtractErrors(retcode, "Query")) {
404 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
405 return 0;
406 }
407
408 return new TODBCResult(hstmt);
409}
410
411////////////////////////////////////////////////////////////////////////////////
412/// Executes query which does not produce any results set
413/// Return kTRUE if successfull
414
415Bool_t TODBCServer::Exec(const char* sql)
416{
417 CheckConnect("Exec", 0);
418
419 SQLRETURN retcode;
420 SQLHSTMT hstmt;
421
422 SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
423
424 retcode = SQLExecDirect(hstmt, (SQLCHAR*) sql, SQL_NTS);
425
426 Bool_t res = !ExtractErrors(retcode, "Exec");
427
428 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
429
430 return res;
431}
432
433////////////////////////////////////////////////////////////////////////////////
434/// Select a database. Returns 0 if successful, non-zero otherwise.
435/// Not all RDBMS support selecting of database (catalog) after connecting
436/// Normally user should specify database name at time of connection
437
439{
440 CheckConnect("SelectDataBase", -1);
441
442 SQLRETURN retcode = SQLSetConnectAttr(fHdbc, SQL_ATTR_CURRENT_CATALOG, (SQLCHAR*) db, SQL_NTS);
443 if (ExtractErrors(retcode, "SelectDataBase")) return -1;
444
445 fDB = db;
446
447 return 0;
448}
449
450////////////////////////////////////////////////////////////////////////////////
451/// List all available databases. Wild is for wildcarding "t%" list all
452/// databases starting with "t".
453/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
454/// The result object must be deleted by the user.
455
457{
458 CheckConnect("GetDataBases", 0);
459
460 return 0;
461}
462
463////////////////////////////////////////////////////////////////////////////////
464/// List all tables in the specified database. Wild is for wildcarding
465/// "t%" list all tables starting with "t".
466/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
467/// The result object must be deleted by the user.
468
469TSQLResult *TODBCServer::GetTables(const char*, const char* wild)
470{
471 CheckConnect("GetTables", 0);
472
473 SQLRETURN retcode;
474 SQLHSTMT hstmt;
475
476 SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
477
478 SQLCHAR* schemaName = 0;
479 SQLSMALLINT schemaNameLength = 0;
480
481/*
482 TString schemabuf;
483 // schema is used by Oracle to specify to which user belong table
484 // therefore, to see correct tables, schema name is set to user name
485 if ((fUserId.Length()>0) && (fServerInfo.Contains("Oracle"))) {
486 schemabuf = fUserId;
487 schemabuf.ToUpper();
488 schemaName = (SQLCHAR*) schemabuf.Data();
489 schemaNameLength = schemabuf.Length();
490 }
491*/
492
493 SQLCHAR* tableName = 0;
494 SQLSMALLINT tableNameLength = 0;
495
496 if ((wild!=0) && (strlen(wild)!=0)) {
497 tableName = (SQLCHAR*) wild;
498 tableNameLength = strlen(wild);
499 SQLSetStmtAttr(hstmt, SQL_ATTR_METADATA_ID, (SQLPOINTER) SQL_FALSE, 0);
500 }
501
502 retcode = SQLTables(hstmt, NULL, 0, schemaName, schemaNameLength, tableName, tableNameLength, (SQLCHAR*) "TABLE", 5);
503 if (ExtractErrors(retcode, "GetTables")) {
504 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
505 return 0;
506 }
507
508 return new TODBCResult(hstmt);
509}
510
511////////////////////////////////////////////////////////////////////////////////
512/// Return list of tables in database
513/// See TSQLServer::GetTablesList() for details.
514
516{
517 CheckConnect("GetTablesList", 0);
518
519 TSQLResult* res = GetTables(0, wild);
520 if (res==0) return 0;
521
522 TList* lst = 0;
523
524 TSQLRow* row = 0;
525
526 while ((row = res->Next())!=0) {
527 const char* tablename = row->GetField(2);
528 if (tablename!=0) {
529// Info("List","%s %s %s %s %s", tablename, row->GetField(0), row->GetField(1), row->GetField(3), row->GetField(4));
530 if (lst==0) {
531 lst = new TList;
532 lst->SetOwner(kTRUE);
533 }
534 lst->Add(new TObjString(tablename));
535 }
536 delete row;
537 }
538
539 delete res;
540
541 return lst;
542}
543
544
545////////////////////////////////////////////////////////////////////////////////
546/// Produces SQL table info
547/// Object must be deleted by user
548
550{
551 CheckConnect("GetTableInfo", 0);
552
553 #define STR_LEN 128+1
554 #define REM_LEN 254+1
555
556 /* Declare buffers for result set data */
557
558 SQLCHAR szCatalog[STR_LEN], szSchema[STR_LEN];
559 SQLCHAR szTableName[STR_LEN], szColumnName[STR_LEN];
560 SQLCHAR szTypeName[STR_LEN], szRemarks[REM_LEN];
561 SQLCHAR szColumnDefault[STR_LEN], szIsNullable[STR_LEN];
562 SQLLEN columnSize, bufferLength, charOctetLength, ordinalPosition;
563 SQLSMALLINT dataType, decimalDigits, numPrecRadix, nullable;
564 SQLSMALLINT sqlDataType, datetimeSubtypeCode;
565 SQLRETURN retcode;
566 SQLHSTMT hstmt;
567
568 /* Declare buffers for bytes available to return */
569
570 SQLLEN cbCatalog, cbSchema, cbTableName, cbColumnName;
571 SQLLEN cbDataType, cbTypeName, cbColumnSize, cbBufferLength;
572 SQLLEN cbDecimalDigits, cbNumPrecRadix, cbNullable, cbRemarks;
573 SQLLEN cbColumnDefault, cbSQLDataType, cbDatetimeSubtypeCode, cbCharOctetLength;
574 SQLLEN cbOrdinalPosition, cbIsNullable;
575
576
577 SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
578
579 retcode = SQLColumns(hstmt, NULL, 0, NULL, 0, (SQLCHAR*) tablename, SQL_NTS, NULL, 0);
580 if (ExtractErrors(retcode, "GetTableInfo")) {
581 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
582 return 0;
583 }
584
585 TList* lst = 0;
586
587 /* Bind columns in result set to buffers */
588
589 SQLBindCol(hstmt, 1, SQL_C_CHAR, szCatalog, STR_LEN,&cbCatalog);
590 SQLBindCol(hstmt, 2, SQL_C_CHAR, szSchema, STR_LEN, &cbSchema);
591 SQLBindCol(hstmt, 3, SQL_C_CHAR, szTableName, STR_LEN,&cbTableName);
592 SQLBindCol(hstmt, 4, SQL_C_CHAR, szColumnName, STR_LEN, &cbColumnName);
593 SQLBindCol(hstmt, 5, SQL_C_SSHORT, &dataType, 0, &cbDataType);
594 SQLBindCol(hstmt, 6, SQL_C_CHAR, szTypeName, STR_LEN, &cbTypeName);
595 SQLBindCol(hstmt, 7, SQL_C_SLONG, &columnSize, 0, &cbColumnSize);
596 SQLBindCol(hstmt, 8, SQL_C_SLONG, &bufferLength, 0, &cbBufferLength);
597 SQLBindCol(hstmt, 9, SQL_C_SSHORT, &decimalDigits, 0, &cbDecimalDigits);
598 SQLBindCol(hstmt, 10, SQL_C_SSHORT, &numPrecRadix, 0, &cbNumPrecRadix);
599 SQLBindCol(hstmt, 11, SQL_C_SSHORT, &nullable, 0, &cbNullable);
600 SQLBindCol(hstmt, 12, SQL_C_CHAR, szRemarks, REM_LEN, &cbRemarks);
601 SQLBindCol(hstmt, 13, SQL_C_CHAR, szColumnDefault, STR_LEN, &cbColumnDefault);
602 SQLBindCol(hstmt, 14, SQL_C_SSHORT, &sqlDataType, 0, &cbSQLDataType);
603 SQLBindCol(hstmt, 15, SQL_C_SSHORT, &datetimeSubtypeCode, 0, &cbDatetimeSubtypeCode);
604 SQLBindCol(hstmt, 16, SQL_C_SLONG, &charOctetLength, 0, &cbCharOctetLength);
605 SQLBindCol(hstmt, 17, SQL_C_SLONG, &ordinalPosition, 0, &cbOrdinalPosition);
606 SQLBindCol(hstmt, 18, SQL_C_CHAR, szIsNullable, STR_LEN, &cbIsNullable);
607
608 retcode = SQLFetch(hstmt);
609
610 while ((retcode==SQL_SUCCESS) || (retcode==SQL_SUCCESS_WITH_INFO)) {
611
612 Int_t sqltype = kSQL_NONE;
613
614 Int_t data_size = -1; // size in bytes
615 Int_t data_length = -1; // declaration like VARCHAR(n) or NUMERIC(n)
616 Int_t data_scale = -1; // second argument in declaration
617 Int_t data_sign = -1; // no info about sign
618
619 switch (dataType) {
620 case SQL_CHAR:
621 sqltype = kSQL_CHAR;
622 data_size = columnSize;
623 data_length = charOctetLength;
624 break;
625 case SQL_VARCHAR:
626 case SQL_LONGVARCHAR:
627 sqltype = kSQL_VARCHAR;
628 data_size = columnSize;
629 data_length = charOctetLength;
630 break;
631 case SQL_DECIMAL:
632 case SQL_NUMERIC:
633 sqltype = kSQL_NUMERIC;
634 data_size = columnSize; // size of column in database
635 data_length = columnSize;
636 data_scale = decimalDigits;
637 break;
638 case SQL_INTEGER:
639 case SQL_TINYINT:
640 case SQL_BIGINT:
641 sqltype = kSQL_INTEGER;
642 data_size = columnSize;
643 break;
644 case SQL_REAL:
645 case SQL_FLOAT:
646 sqltype = kSQL_FLOAT;
647 data_size = columnSize;
648 data_sign = 1;
649 break;
650 case SQL_DOUBLE:
651 sqltype = kSQL_DOUBLE;
652 data_size = columnSize;
653 data_sign = 1;
654 break;
655 case SQL_BINARY:
656 case SQL_VARBINARY:
657 case SQL_LONGVARBINARY:
658 sqltype = kSQL_BINARY;
659 data_size = columnSize;
660 break;
661 case SQL_TYPE_TIMESTAMP:
662 sqltype = kSQL_TIMESTAMP;
663 data_size = columnSize;
664 break;
665 }
666
667 if (lst==0) lst = new TList;
668
669 lst->Add(new TSQLColumnInfo((const char*) szColumnName,
670 (const char*) szTypeName,
671 nullable!=0,
672 sqltype,
673 data_size,
674 data_length,
675 data_scale,
676 data_sign));
677
678 retcode = SQLFetch(hstmt);
679 }
680
681 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
682
683 return new TSQLTableInfo(tablename, lst);
684}
685
686////////////////////////////////////////////////////////////////////////////////
687/// List all columns in specified table in the specified database.
688/// Wild is for wildcarding "t%" list all columns starting with "t".
689/// Returns a pointer to a TSQLResult object if successful, 0 otherwise.
690/// The result object must be deleted by the user.
691
692TSQLResult *TODBCServer::GetColumns(const char*, const char *table, const char*)
693{
694 CheckConnect("GetColumns", 0);
695
696 SQLRETURN retcode;
697 SQLHSTMT hstmt;
698
699 SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
700
701 retcode = SQLColumns(hstmt, NULL, 0, NULL, 0, (SQLCHAR*) table, SQL_NTS, NULL, 0);
702 if (ExtractErrors(retcode, "GetColumns")) {
703 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
704 return 0;
705 }
706
707 return new TODBCResult(hstmt);
708}
709
710////////////////////////////////////////////////////////////////////////////////
711/// returns maximum allowed length of identifier (table name, column name, index name)
712
714{
715 CheckConnect("GetMaxIdentifierLength", 20);
716
717 SQLUINTEGER info = 0;
718 SQLRETURN retcode;
719
720 retcode = SQLGetInfo(fHdbc, SQL_MAX_IDENTIFIER_LEN, (SQLPOINTER)&info, sizeof(info), NULL);
721
722 if (ExtractErrors(retcode, "GetMaxIdentifierLength")) return 20;
723
724 return info;
725}
726
727////////////////////////////////////////////////////////////////////////////////
728/// Create a database. Returns 0 if successful, non-zero otherwise.
729
731{
732 CheckConnect("CreateDataBase", -1);
733
734 return -1;
735}
736
737////////////////////////////////////////////////////////////////////////////////
738/// Drop (i.e. delete) a database. Returns 0 if successful, non-zero
739/// otherwise.
740
742{
743 CheckConnect("DropDataBase", -1);
744
745 return -1;
746}
747
748////////////////////////////////////////////////////////////////////////////////
749/// Reload permission tables. Returns 0 if successful, non-zero
750/// otherwise. User must have reload permissions.
751
753{
754 CheckConnect("Reload", -1);
755
756 return -1;
757}
758
759////////////////////////////////////////////////////////////////////////////////
760/// Shutdown the database server. Returns 0 if successful, non-zero
761/// otherwise. User must have shutdown permissions.
762
764{
765 CheckConnect("Shutdown", -1);
766
767 return -1;
768}
769
770////////////////////////////////////////////////////////////////////////////////
771/// Return server info.
772
774{
775 CheckConnect("ServerInfo", 0);
776
777 return fServerInfo;
778}
779
780////////////////////////////////////////////////////////////////////////////////
781/// Creates ODBC statement for provided query.
782/// See TSQLStatement class for more details.
783
785{
786 CheckConnect("Statement", 0);
787
788 if (!sql || !*sql) {
789 SetError(-1, "no query string specified", "Statement");
790 return 0;
791 }
792
793// SQLUINTEGER info = 0;
794// SQLGetInfo(fHdbc, SQL_PARAM_ARRAY_ROW_COUNTS, (SQLPOINTER)&info, sizeof(info), NULL);
795// if (info==SQL_PARC_BATCH) Info("Statement","info==SQL_PARC_BATCH"); else
796// if (info==SQL_PARC_NO_BATCH) Info("Statement","info==SQL_PARC_NO_BATCH"); else
797// Info("Statement","info==%u", info);
798
799
800 SQLRETURN retcode;
801 SQLHSTMT hstmt;
802
803 retcode = SQLAllocHandle(SQL_HANDLE_STMT, fHdbc, &hstmt);
804 if (ExtractErrors(retcode, "Statement")) return 0;
805
806 retcode = SQLPrepare(hstmt, (SQLCHAR*) sql, SQL_NTS);
807 if (ExtractErrors(retcode, "Statement")) {
808 SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
809 return 0;
810 }
811
812 return new TODBCStatement(hstmt, bufsize, fErrorOut);
813}
814
815////////////////////////////////////////////////////////////////////////////////
816/// Starts transaction.
817/// Check for transaction support.
818/// Switch off autocommitment mode.
819
821{
822 CheckConnect("StartTransaction", kFALSE);
823
824 SQLUINTEGER info = 0;
825 SQLRETURN retcode;
826
827 retcode = SQLGetInfo(fHdbc, SQL_TXN_CAPABLE, (SQLPOINTER)&info, sizeof(info), NULL);
828 if (ExtractErrors(retcode, "StartTransaction")) return kFALSE;
829
830 if (info==0) {
831 SetError(-1,"Transactions not supported","StartTransaction");
832 return kFALSE;
833 }
834
835 if (!Commit()) return kFALSE;
836
837 retcode = SQLSetConnectAttr(fHdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_OFF, 0);
838 if (ExtractErrors(retcode, "StartTransaction")) return kFALSE;
839
840 return kTRUE;
841}
842
843////////////////////////////////////////////////////////////////////////////////
844/// Complete current transaction (commit = kTRUE) or rollback
845/// Switches on autocommit mode of ODBC driver
846
848{
849 const char* method = commit ? "Commit" : "Rollback";
850
851 CheckConnect(method, kFALSE);
852
853 SQLRETURN retcode = SQLEndTran(SQL_HANDLE_DBC, fHdbc, commit ? SQL_COMMIT : SQL_ROLLBACK);
854 if (ExtractErrors(retcode, method)) return kFALSE;
855
856 retcode = SQLSetConnectAttr(fHdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, 0);
857
858 return kTRUE;
859}
860
861////////////////////////////////////////////////////////////////////////////////
862/// Commit transaction
863
865{
866 return EndTransaction(kTRUE);
867}
868
869////////////////////////////////////////////////////////////////////////////////
870/// Rollback transaction
871
873{
874 return EndTransaction(kFALSE);
875}
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:2447
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 aftewards Name...
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 aftewards...
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 successfull.
TString fServerInfo
Definition: TODBCServer.h:35
Collectable string class.
Definition: TObjString.h:28
void MakeZombie()
Definition: TObject.h:49
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:2314
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