Logo ROOT  
Reference Guide
sqlselect.C File Reference

Detailed Description

Query example to MySQL test database. Example of query by using the test database made in MySQL, you need the database test installed in localhost, with user nobody without password.

#include <TSQLServer.h>
#include <TSQLResult.h>
#include <TSQLRow.h>
void sqlselect()
{
TSQLServer *db = TSQLServer::Connect("mysql://localhost/test","nobody", "");
printf("Server info: %s\n", db->ServerInfo());
TSQLRow *row;
TSQLResult *res;
// list databases available on server
printf("\nList all databases on server %s\n", db->GetHost());
res = db->GetDataBases();
while ((row = res->Next())) {
printf("%s\n", row->GetField(0));
delete row;
}
delete res;
// list tables in database "test" (the permission tables)
printf("\nList all tables in database \"test\" on server %s\n",
db->GetHost());
res = db->GetTables("test");
while ((row = res->Next())) {
printf("%s\n", row->GetField(0));
delete row;
}
delete res;
// list columns in table "runcatalog" in database "mysql"
printf("\nList all columns in table \"runcatalog\" in database \"test\" on server %s\n",
db->GetHost());
res = db->GetColumns("test", "runcatalog");
while ((row = res->Next())) {
printf("%s\n", row->GetField(0));
delete row;
}
delete res;
// start timer
TStopwatch timer;
timer.Start();
// query database and print results
const char *sql = "select dataset,rawfilepath from test.runcatalog "
"WHERE tag&(1<<2) AND (run=490001 OR run=300122)";
// const char *sql = "select count(*) from test.runcatalog "
// "WHERE tag&(1<<2)";
res = db->Query(sql);
int nrows = res->GetRowCount();
printf("\nGot %d rows in result\n", nrows);
int nfields = res->GetFieldCount();
for (int i = 0; i < nfields; i++)
printf("%40s", res->GetFieldName(i));
printf("\n");
for (int i = 0; i < nfields*40; i++)
printf("=");
printf("\n");
for (int i = 0; i < nrows; i++) {
row = res->Next();
for (int j = 0; j < nfields; j++) {
printf("%40s", row->GetField(j));
}
printf("\n");
delete row;
}
delete res;
delete db;
// stop timer and print results
timer.Stop();
Double_t rtime = timer.RealTime();
Double_t ctime = timer.CpuTime();
printf("\nRealTime=%f seconds, CpuTime=%f seconds\n", rtime, ctime);
}
Author
Sergey Linev, Juan Fernando Jaramillo Botero

Definition in file sqlselect.C.

TSQLResult::Next
virtual TSQLRow * Next()=0
TSQLServer::GetDataBases
virtual TSQLResult * GetDataBases(const char *wild=nullptr)=0
TSQLServer::ServerInfo
virtual const char * ServerInfo()=0
TSQLServer.h
TSQLServer::GetColumns
virtual TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=nullptr)=0
TSQLServer::GetHost
const char * GetHost() const
Definition: TSQLServer.h:96
sqlselect
Definition: sqlselect.py:1
TSQLResult
Definition: TSQLResult.h:32
TStopwatch::RealTime
Double_t RealTime()
Stop the stopwatch (if it is running) and return the realtime (in seconds) passed between the start a...
Definition: TStopwatch.cxx:110
TSQLServer::Connect
static TSQLServer * Connect(const char *db, const char *uid, const char *pw)
The db should be of the form: <dbms>://<host>[:<port>][/<database>], e.g.
Definition: TSQLServer.cxx:61
TSQLServer::Query
virtual TSQLResult * Query(const char *sql)=0
TSQLRow
Definition: TSQLRow.h:30
TSQLRow.h
TStopwatch::Start
void Start(Bool_t reset=kTRUE)
Start the stopwatch.
Definition: TStopwatch.cxx:58
TStopwatch::CpuTime
Double_t CpuTime()
Stop the stopwatch (if it is running) and return the cputime (in seconds) passed between the start an...
Definition: TStopwatch.cxx:125
TSQLResult.h
Double_t
double Double_t
Definition: RtypesCore.h:59
TStopwatch
Definition: TStopwatch.h:28
TStopwatch::Stop
void Stop()
Stop the stopwatch.
Definition: TStopwatch.cxx:77
TSQLRow::GetField
virtual const char * GetField(Int_t field)=0
TSQLResult::GetFieldName
virtual const char * GetFieldName(Int_t field)=0
TSQLServer::GetTables
virtual TSQLResult * GetTables(const char *dbname, const char *wild=nullptr)=0
TSQLServer
Definition: TSQLServer.h:41
TSQLResult::GetRowCount
virtual Int_t GetRowCount() const
Definition: TSQLResult.h:45
TSQLResult::GetFieldCount
virtual Int_t GetFieldCount()=0