ODBC access with Win32

ROOT
1

Hi,

I am new to ROOT and am trying to figure out how to access data from a Microsoft SQL Server. I noticed that a new ODBC driver exists in ROOT 5.11 and thought I’d give it a try.

On my Windows XP machine I used the ODBC Data Source Administrator to add a connection to the SQL Server. There is a Test Data Source… button which I used to verify my connection and that seems to be working.

I downloaded the binaries for Root 5.11 and tried the following…

TSQLServer::Connect(“odbc://127.0.0.1/db_server”,“userid”,“pw”)

ROOT replied…

Error in : RODBC does not exist in .;c:\root/bin;C:\cygwin\usr\local\bin;C:\cygwin\bin;C:\cygwi\bin;C:\cygwin\usr\X11R6\bin;C:\cygwin\bin;c:\progra~1\emacs\bin;c:\Program Files\ThinkPad\Utilities;c:\WINDOWS\system32;c:\WINDOWS;c:\WINDOWS\System32\Wbem;c:\Program Files\ATI Technologies\ATI.ACE;c:\Program Files\IBM ThinkVantage\Client Security Solution;c:\Program Files\ThinkPad\ConnectUtilities;c:\root\bin;c:\PROGRA~1\F-Secure\ssh;c:\Program Files\Diskeeper Corporation\Diskeeper;c:\Program Files\Microsoft Visual Studio .NET 2003\Vc7\bin;c:\Program Files\Microsoft Visual Studio .NET 2003\Common7\IDE;C:\cygwin\c\WINDOWS\system32;C:\cygwin\c\WINDOWS;C:\cygwin\c\Program Files\Microsoft Visual C++ Toolkit 2003\bin;C:\cygwin\c\Program Files\Microsoft Visual C++ Toolkit 2003;C:\cygwin\c\Program Files\Microsoft Visual Studio .NET 2003\Vc7\bin;C:\cygwin\c\Program Files\Microsoft SDK\Bin\Win64;C:\cygwin\c\Program Files\Microsoft SDK\Bin, or has wrong file extension (.dll)
(class TSQLServer*)0x0

I took a look around for libRODBC.dll and couldn’t find it anywhere on my machine.

I also tried to use TODBCServer (as in TODBCServer::Connect(“odbc://127.0.0.1/db_server”,“userid”,“pw”) but that doesn’t seem to exist.

Being new I’d also really appreciate pointers to understanding how to map the database tables into trees with the least amount of coding.

2

The Windows version 5.11/02 does not include the libRODBC.dll interface.
We generated this library only on Unix and Macosx systems.
We will include it in the next release.

Rene

3

Rene - Thanks for the quick response. I may have asked the wrong question. What I’d really like to do is connect root to a SQL Server database from a Windows machine. I thought the correct method was via ODBC hence my question. Recently I noticed the following in the 5.11 news…

ODBC interface: New package

New ODBC driver that allows generic access to many DB’s. This version should also compile on Windows for access to MS DB’s (Access, SQLServer).

The text about SQLServer leads to believe there is some current method of using ROOT to talk directly with SQLServer. If this is true then how would one do it. I’ve tried lots of combinations of TSQLServer::Connect but they either ask for RODBC (which isn’t available), return 0, or complain about my format.

4

Hi,

no there is no direct TSQLServer (the ROOT abstract generic SQL server interface) based interface to MS SQLServer. The only way to access that is via the ODBC plugin that we now provide. However, how to use that on Windows I don’t know. Probably Bertrand or Sergei Linev can answer that.

Cheers, Fons.

5

Hi,

ODBC has several methods how you can establish connection to database.

First - via defining user data source. Under Windows XP you can define them
in Settings -> Control Panel -> Administrative Tools -> Data sources (ODBC) -> User DSN. There you can specify all yoyr specific configuration and access them via the DSN entry name. To connect to such database, you should use

TSQLServer::Connect(“odbcn://MySpecialConfig”,“user”,“pass”);

odbcn:// prefix defines that “simple” SQLConnect ODBC function will be used.

If you don’t like create special DSN entry, you can specify all data inside connect string:

TSQLServer::Connect(“odbcd://DRIVER={Oracle in instantclient10_2};DBQ=db-test;UID=user;PWD=pass;”,0,0);

odbcd:// prefix means that SQLDriverConnect function is used.
Exact form of connect string differ from one ODBC driver to another. This is working example (for me) for Oracle ODBC under Windows.
Name “Oracle in instantclient10_2” you should see in:
Settings -> Control Panel -> Administrative Tools -> Data sources (ODBC) -> Drivers

You can also use odbc:// prefix for connecting to data base.
In that case connection string look like “normal” URL:

TSQLServer::Connect(“odbc://host.domain:3306/test?MySQL”,“user”,“pass”);

But such form tested only for MySQL ODBC driver.

More detail information about ODBC plugin you can find here:
http://root.cern.ch/root/htmldoc/TODBCServer.html

6

Thanks to all the recent SQL work I can now connect from a Windows machine to a SQL Server and run queries. Of course, now that this is working I’d like to ask for more.

I’m trying to import a table that has a timestamp of the form…

1/1/05 12:30 AM

when I try this with Root 5-11-06 via…

TSQLServer *srv = TSQLServer::Connect("odbcd://DRIVER={SQL Server};SERVER=db-server;DATABASE=db;UID=dbuser;PWD=dbpasswd;",0,0); TSQLStatement *stmt; stmt = srv->Statement("SELECT Timestamp FROM Table",100); stmt->Process(); stmt->StoreResult();

I get an error

Error in : Code: -1 Msg: SQL type 93 not supported

When I ask for other types of data everything seems to work so I guess this has to do with time formats. Is there anything that can be done?

Also, for results which work I move forward with…

Is there anyway to go backwards? After reading in a query I step through it with NextResultRow to determine the number of rows which I use to make an array of the correct size. Next I need to insert the data but my stmt is at the end of the result with no clear way to move back to the beginning. My work around is to issue an identical query but this seems to be wasteful.

Thanks again for the great work.

John Estrada

7

Hi,

For the moment TSQLStatement class supports only basic data types like floats, integers, strings. I do not include support for date, time, intervals and blobs yet. This is in my mid-term plans. Unfortunately, different databases has different representation of datatypes like date/time/intervals and I need some time to investigate all possible cases.

For the moment you can try TSQLResult class, produced by serv->Query() method. It is fully string-based class, therefore types like date and time should work with it.

About stmt->NextResultRow() method. All SQL plugins, which I know, does not support backward navigation, therefore TSQLStatement will not support such kind of operation too. Here you again can try TSQLResult and TSQLRow classes. You can produce code like this:

TSQLResult* res = serv->Query(“SELECT * FROM MyTable”);
TSQLRow* row = 0;
TList lst;
while (row = res->Next()) lst.Add(row);

From this place on you can iterate over list as many time as you want. In the end just do:

lst.Delete();
delete res;

You should only be aware, that TSQLResult is about 2-10 times slower than TSQLStatement class. Biggest difference you will observe with Oracle. With TSQLResult class you should be aware about memory consumption, when producing large results sets.

Regards,
Sergey