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 "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
81TODBCServer::TODBCServer(const char *db, const char *uid, const char *pw) :
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
218zombie:
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
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 //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
413Bool_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
467TSQLResult *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
690TSQLResult *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
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}
const Bool_t kFALSE
Definition: RtypesCore.h:90
const Bool_t kTRUE
Definition: RtypesCore.h:89
const char Option_t
Definition: RtypesCore.h:64
#define ClassImp(name)
Definition: Rtypes.h:361
#define REM_LEN
#define CheckConnect(method, res)
#define STR_LEN
char * Form(const char *fmt,...)
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:44
virtual void Add(TObject *obj)
Definition: TList.h:87
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:81
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:131
Ssiz_t Length() const
Definition: TString.h:405
const char * Data() const
Definition: TString.h:364
void Form(const char *fmt,...)
Formats a string using a printf style format descriptor.
Definition: TString.cxx:2289
This class represents a WWW compatible URL.
Definition: TUrl.h:35
const char * GetFile() const
Definition: TUrl.h:71
Bool_t IsValid() const
Definition: TUrl.h:81
const char * GetUser() const
Definition: TUrl.h:67
const char * GetHost() const
Definition: TUrl.h:69
const char * GetPasswd() const
Definition: TUrl.h:68
const char * GetOptions() const
Definition: TUrl.h:73
Int_t GetPort() const
Definition: TUrl.h:80
TText * text
const Int_t n
Definition: legend1.C:16