Logo ROOT  
Reference Guide
sqlselect.C
Go to the documentation of this file.
1/// \file
2/// \ingroup tutorial_sql
3/// Query example to MySQL test database.
4/// Example of query by using the test database made in MySQL, you need the
5/// database test installed in localhost, with user nobody without password.
6///
7/// \macro_code
8///
9/// \author Sergey Linev, Juan Fernando Jaramillo Botero
10
11#include <TSQLServer.h>
12#include <TSQLResult.h>
13#include <TSQLRow.h>
14
15
16void sqlselect()
17{
18 TSQLServer *db = TSQLServer::Connect("mysql://localhost/test","nobody", "");
19
20 printf("Server info: %s\n", db->ServerInfo());
21
22 TSQLRow *row;
23 TSQLResult *res;
24
25 // list databases available on server
26 printf("\nList all databases on server %s\n", db->GetHost());
27 res = db->GetDataBases();
28 while ((row = res->Next())) {
29 printf("%s\n", row->GetField(0));
30 delete row;
31 }
32 delete res;
33
34 // list tables in database "test" (the permission tables)
35 printf("\nList all tables in database \"test\" on server %s\n",
36 db->GetHost());
37 res = db->GetTables("test");
38 while ((row = res->Next())) {
39 printf("%s\n", row->GetField(0));
40 delete row;
41 }
42 delete res;
43
44 // list columns in table "runcatalog" in database "mysql"
45 printf("\nList all columns in table \"runcatalog\" in database \"test\" on server %s\n",
46 db->GetHost());
47 res = db->GetColumns("test", "runcatalog");
48 while ((row = res->Next())) {
49 printf("%s\n", row->GetField(0));
50 delete row;
51 }
52 delete res;
53
54 // start timer
55 TStopwatch timer;
56 timer.Start();
57
58 // query database and print results
59 const char *sql = "select dataset,rawfilepath from test.runcatalog "
60 "WHERE tag&(1<<2) AND (run=490001 OR run=300122)";
61 // const char *sql = "select count(*) from test.runcatalog "
62 // "WHERE tag&(1<<2)";
63
64 res = db->Query(sql);
65
66 int nrows = res->GetRowCount();
67 printf("\nGot %d rows in result\n", nrows);
68
69 int nfields = res->GetFieldCount();
70 for (int i = 0; i < nfields; i++)
71 printf("%40s", res->GetFieldName(i));
72 printf("\n");
73 for (int i = 0; i < nfields*40; i++)
74 printf("=");
75 printf("\n");
76
77 for (int i = 0; i < nrows; i++) {
78 row = res->Next();
79 for (int j = 0; j < nfields; j++) {
80 printf("%40s", row->GetField(j));
81 }
82 printf("\n");
83 delete row;
84 }
85
86 delete res;
87 delete db;
88
89 // stop timer and print results
90 timer.Stop();
91 Double_t rtime = timer.RealTime();
92 Double_t ctime = timer.CpuTime();
93
94 printf("\nRealTime=%f seconds, CpuTime=%f seconds\n", rtime, ctime);
95}
double Double_t
Definition: RtypesCore.h:59
virtual const char * GetFieldName(Int_t field)=0
virtual Int_t GetRowCount() const
Definition: TSQLResult.h:44
virtual TSQLRow * Next()=0
virtual Int_t GetFieldCount()=0
virtual const char * GetField(Int_t field)=0
const char * GetHost() const
Definition: TSQLServer.h:96
virtual TSQLResult * GetColumns(const char *dbname, const char *table, const char *wild=nullptr)=0
virtual TSQLResult * GetDataBases(const char *wild=nullptr)=0
virtual const char * ServerInfo()=0
virtual TSQLResult * Query(const char *sql)=0
virtual TSQLResult * GetTables(const char *dbname, const char *wild=nullptr)=0
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.: mysql://pcroot....
Definition: TSQLServer.cxx:61
Stopwatch class.
Definition: TStopwatch.h:28
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
void Start(Bool_t reset=kTRUE)
Start the stopwatch.
Definition: TStopwatch.cxx:58
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
void Stop()
Stop the stopwatch.
Definition: TStopwatch.cxx:77