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