Re: [ROOT] accessing MySql

From: Christian Holm Christensen (cholm@hehi03.nbi.dk)
Date: Tue Aug 07 2001 - 11:24:46 MEST


On Mon, 6 Aug 2001 18:48:34 -0400 (EDT)
Matthieu Guillo <guillo@jlab.org> wrote
concerning ": [ROOT] accessing MySql":
> Hello Rooters,
> 
> I am trying to access a MySQL database through ROOT. I have MySQL client
> installed on my machine and I can use Perl to access the database I want
>  and make some querries but I would like now to do the same under ROOT (I
> compiled it with MySql option).
> Does anybody has some examples on how to do that? I would like to
> do very simple things like:
> 
> SELECT run, event FROM run_table WHERE run > 1234 AND event < 10;

This is rather simpel to do: 

1)  Make a connection to your database: 

  TSQLServer* serv = 
    TSQLServer::Connect("mysql://your.host.name/your_database", 
                        "login_name", "password"); 
  
  Please note that the older (3.22) MySQL C API had a problem with
  hostname being "localhost", so you may have to specify the full
  hostname.  

  A note: I really don't like the way you have to specify the password
  to the connection.  I believe a scheme close to what was implemented
  for rootd (i.e. read from a access protected file, and if that
  doesn't resovle it, then from stdin, using a non-echoing prompt)
  would be better.  Such a thing could be implemented in
  TSQLServer::Connect rather than in the TMySQLServer CTOR.

2) Make your query: 
 
    TString query("SELECT run, event FROM run_table " 
                  "WHERE run > 1234 AND event < 10");
    TSQLResult* res = serv->Query(query); 

  This will give you the "view" you want.  Notice that I prefer to
  store the query string so that I can use that in error messages. 

3) Check your query: 

    if (!res) { 
      Warning("Foo", "Query \"%s\" -> no result", query.Data()); 
      delete res;
      return;
    }
    if (res->GetRowCount() <= 0) { 
      Warning("Foo", "Query \"%s\" returned nothing", query.Data()); 
      delete res;
      return;
    }

  Note that you have to delete the TSQLResult object. 

4) Loop over the rows you got out of the query:

   TSQLRow* row = 0; 
   while ((row = res->Next())) { 
     Int_t runNo   = strtol(row->GetField(0), NULL, 0);
     Int_t eventNo = strtol(row->GetField(1), NULL, 0);
     cout << "Run # " << setw(8) << runNo << "   Event # " 
          << setw(8) << eventNo << endl;
     // and so on. 
   }
   delete res; 

5) Finally close your connection: 

     serv->Close(); 

Note that if you use a PostGreSQL server, you also need to send the
queries 

  BEGIN WORK 
  END WORK 
  COMMIT 

or something like that. 

I hope this helps you.  Perhaps a short section on this could go into
the Users Guide?  

Yours, 

Christian  -----------------------------------------------------------
Holm Christensen                             Phone:  (+45) 35 35 96 91 
  Sankt Hansgade 23, 1. th.                  Office: (+45) 353  25 305 
  DK-2200 Copenhagen N                       Web:    www.nbi.dk/~cholm    
  Denmark                                    Email:       cholm@nbi.dk
  



This archive was generated by hypermail 2b29 : Tue Jan 01 2002 - 17:50:56 MET