ROOT logo

From $ROOTSYS/tutorials/sql/sqltables.C

// This is an example illustrating how the TSQLFile class can be used.
// Histogram, list of TBox and clones array of TBox objects are stored
// to TSQLFile and read back.
// Except for the specific TSQLFile configuration, the TSQLFile functionality
// is absolutely similar to a normal root TFile
// Author: S.Linev

// example configuration for MySQL 4.1
const char* dbname = "mysql://host.domain/test";
const char* username = "user";
const char* userpass = "pass";

// example configuration for Oracle 9i
//const char* dbname = "oracle://host.domain/db-test";
//const char* username = "user";
//const char* userpass = "pass";


void sqltables() 
{
   tables_write();
   tables_read();
}

void tables_write() 
{
   // first connect to data base
   // "recreate" option delete all your tables !!!! 
   TSQLFile* f = new TSQLFile(dbname, "recreate", username, userpass);
   if (f->IsZombie()) { delete f; return; }
 
   // you can change configuration only until first object 
   // is writen to TSQLFile
   f->SetUseSuffixes(kFALSE);
   f->SetArrayLimit(1000);
   f->SetUseIndexes(1);
//   f->SetTablesType("ISAM");
//   f->SetUseTransactions(kFALSE);

   // lets first write histogram
   TH1I* h1 = new TH1I("histo1","histo title", 1000, -4., 4.);
   h1->FillRandom("gaus",10000);
   h1->Write("histo");
   h1->SetDirectory(0);

   // here we create list of objects and store them as single key
   // without kSingleKey all TBox objects will appear as separate keys
   TList* arr = new TList;
   for(Int_t n=0;n<10;n++) {
      TBox* b = new TBox(n*10,n*100,n*20,n*200);  
      arr->Add(b, Form("option_%d_option",n));
   }
   arr->Write("list",TObject::kSingleKey);

   // clones array is also strored as single key
   TClonesArray clones("TBox",10);
   for(int n=0;n<10;n++) 
       new (clones[n]) TBox(n*10,n*100,n*20,n*200);
   clones.Write("clones",TObject::kSingleKey);

   // close connection to database
   delete f;
}


void tables_read() 
{
   // now open connection to database for read-only 
   TSQLFile* f = new TSQLFile(dbname, "open", username, userpass);
   if (f->IsZombie()) { delete f; return; }
   
   // see list of keys
   f->ls();
   
   // get histogram from DB and draw it
   TH1* h1 = (TH1*) f->Get("histo");
   if (h1!=0) {
       h1->SetDirectory(0);
       h1->Draw();
   }
   
   // get TList with other objects
   TObject* obj = f->Get("list");
   cout << "Printout of TList object" << endl;
   if (obj!=0) obj->Print("*");
   delete obj;

   // and get TClonesArray
   obj = f->Get("clones");
   cout << "Printout of TClonesArray object" << endl;
   if (obj!=0) obj->Print("*");
   delete obj;

   // this is query to select data of hole class from different tables
   cout << "================ TBox QUERY ================ " << endl;
   cout << f->MakeSelectQuery(TBox::Class()) << endl;
   cout << "================ END of TBox QUERY ================ " << endl;

   cout << "================== TH1I QUERY ================ " << endl;
   cout << f->MakeSelectQuery(TH1I::Class()) << endl;
   cout << "================ END of TH1I QUERY ================ " << endl;
   
   // close connection to database
   delete f;
}
 sqltables.C:1
 sqltables.C:2
 sqltables.C:3
 sqltables.C:4
 sqltables.C:5
 sqltables.C:6
 sqltables.C:7
 sqltables.C:8
 sqltables.C:9
 sqltables.C:10
 sqltables.C:11
 sqltables.C:12
 sqltables.C:13
 sqltables.C:14
 sqltables.C:15
 sqltables.C:16
 sqltables.C:17
 sqltables.C:18
 sqltables.C:19
 sqltables.C:20
 sqltables.C:21
 sqltables.C:22
 sqltables.C:23
 sqltables.C:24
 sqltables.C:25
 sqltables.C:26
 sqltables.C:27
 sqltables.C:28
 sqltables.C:29
 sqltables.C:30
 sqltables.C:31
 sqltables.C:32
 sqltables.C:33
 sqltables.C:34
 sqltables.C:35
 sqltables.C:36
 sqltables.C:37
 sqltables.C:38
 sqltables.C:39
 sqltables.C:40
 sqltables.C:41
 sqltables.C:42
 sqltables.C:43
 sqltables.C:44
 sqltables.C:45
 sqltables.C:46
 sqltables.C:47
 sqltables.C:48
 sqltables.C:49
 sqltables.C:50
 sqltables.C:51
 sqltables.C:52
 sqltables.C:53
 sqltables.C:54
 sqltables.C:55
 sqltables.C:56
 sqltables.C:57
 sqltables.C:58
 sqltables.C:59
 sqltables.C:60
 sqltables.C:61
 sqltables.C:62
 sqltables.C:63
 sqltables.C:64
 sqltables.C:65
 sqltables.C:66
 sqltables.C:67
 sqltables.C:68
 sqltables.C:69
 sqltables.C:70
 sqltables.C:71
 sqltables.C:72
 sqltables.C:73
 sqltables.C:74
 sqltables.C:75
 sqltables.C:76
 sqltables.C:77
 sqltables.C:78
 sqltables.C:79
 sqltables.C:80
 sqltables.C:81
 sqltables.C:82
 sqltables.C:83
 sqltables.C:84
 sqltables.C:85
 sqltables.C:86
 sqltables.C:87
 sqltables.C:88
 sqltables.C:89
 sqltables.C:90
 sqltables.C:91
 sqltables.C:92
 sqltables.C:93
 sqltables.C:94
 sqltables.C:95
 sqltables.C:96
 sqltables.C:97
 sqltables.C:98
 sqltables.C:99
 sqltables.C:100
 sqltables.C:101
 sqltables.C:102
 sqltables.C:103
 sqltables.C:104
 sqltables.C:105
 sqltables.C:106