RDBC/macros/RDBCemp.C

0001 // $Id: RDBCemp.C,v 1.1.1.1 2004/02/18 20:58:02 dave Exp $
0002 //
0003 //     This file is part of the RDBC
0004 //     Author: Valeriy Onuchin <onuchin@sirius.ihep.su>
0005 /////////////////////////////////////////////////////////////////////
0006 //
0007 //      RDBCemp -  Demo macro
0008 //
0009 //    DESCRIPTION
0010 //
0011 //      This is a sample macro to demostrate how to retrieve data
0012 //      from Oracle database and draw simple graphs.
0013 //
0014 //    REQUIREMENTS
0015 //
0016 //      o ORACLE demo table EMP. To create this demo table for MySQL  run:
0017 //
0018 //          $ mysql test -u username -p < demobld.mysql
0019 //
0020 //////////////////////////////////////////////////////////////////////
0021 //
0022 // usage:
0023 //
0024 // root[] gSystem->Load("libRDBC.so");    // load library
0025 // root[] .L RDBCemp.C                    // load macro
0026 // root[] RDBCemp(dsn,[usr],[pwd]);       // execute the function from macro
0027 //
0028 //Begin_Html
0029 /*
0030 <img src="oemp.gif">
0031 */
0032 //End_Html
0033
0034 #ifndef __CINT__
0035 #include <TError.h>
0036 #include <TString.h>
0037 #include <RDBC/TSQLDriverManager.h>
0038 #include <RDBC/TSQLConnection.h>
0039 #include <RDBC/TSQLDatabaseMetaData.h>
0040 #include <RDBC/TSQLResultSet.h>
0041 #include <RDBC/TSQLResultSetMetaData.h>
0042 #include <RDBC/TSQLPreparedStatement.h>
0043 #include <RDBC/TSQLCallableStatement.h>
0044 #include <RDBC/TSQLTypes.h>
0045 #include <TROOT.h>
0046 #include <TGraph.h>
0047 #include <TCanvas.h>
0048 #include <TH1.h>
0049 #include <TText.h>
0050 #include <TStyle.h>
0051 #include <TInterpreter.h>
0052 #include <stdlib.h>
0053 #endif
0054
0055 //___________________________________________________________________
0056 void DrawGraph(TGraph* graph, TString* labels, const TString& xname,
0057                Float_t xmin,Float_t xmax,Float_t ymin,Float_t ymax)
0058 {
0059    //
0060
0061    gPad->SetFrameFillColor(18);
0062    gStyle->SetOptStat(0);
0063    Int_t nrDivisions = graph->GetN();
0064
0065    TH1F *frame = new TH1F(graph->GetName(),graph->GetTitle(),2,xmin,xmax+1);
0066    frame->SetMinimum(ymin);
0067    frame->SetMaximum(ymax);
0068    frame->SetLabelOffset(10);
0069    frame->SetLabelSize(0.0);
0070    frame->SetNdivisions(0);
0071    frame->SetTitleOffset(1.1,"X");
0072    frame->SetXTitle((char*)xname.Data());
0073    frame->SetTitleOffset(1.1,"Y");
0074    frame->Draw();
0075
0076    TText *t = new TText();
0077    t->SetTextAlign(22);
0078    t->SetTextSize(0.022);
0079
0080    Float_t span = xmax-xmin;
0081    Float_t step = Float_t(span/nrDivisions);
0082    Float_t dist = TMath::Abs(ymax-ymin);
0083
0084    for (Int_t j=0; j<nrDivisions;j++) {
0085       Float_t xpos = step+xmin+j*step;
0086       Float_t ypos = ymin-0.05*dist;
0087       t->DrawText(xpos,ypos,labels[j].Data());
0088    }
0089
0090 //-- Finally plot the graph
0091    graph->SetFillColor(rand()%149);
0092    graph->SetLineWidth(1.0);
0093    graph->Draw("B");
0094 }
0095
0096 //___________________________________________________________________
0097 Int_t RDBCemp( const Text_t* dsn,
0098                const Text_t* usr="scott",
0099                const Text_t* pwd="tiger" )
0100 {
0101    //
0102
0103    TString str;
0104
0105    // set error handler
0106    TSQL::SetHandler("Catch(TSQLException*)");
0107
0108    str = "Connecting to dsn="; str += dsn;
0109    str += ", uid="; str += usr;
0110    str += ", pwd="; str += pwd;
0111    str += " ...";
0112    printf("%s\n",str.Data());
0113
0114    TSQLConnection* gConn = gSQLDriverManager->GetConnection(dsn,usr,pwd);
0115    if(!gConn) return -1;
0116
0117    printf("\t\t\t DONE.\n");
0118
0119    TSQLStatement* stmt = gConn->CreateStatement();
0120    TSQLResultSet* rs = stmt->ExecuteQuery("select * from EMP");
0121    if(!rs) return -1;
0122
0123    const Int_t nrows = 14;
0124    Float_t sal[nrows];
0125    Float_t empno[nrows];
0126    Float_t mgr[nrows];
0127    Float_t comm[nrows];
0128    Float_t row[nrows];
0129    TString ename[nrows];
0130
0131    for(int i=0; i<nrows; i++) {
0132       rs->Next();    // iterate rows
0133       empno[i] = rs->GetInt(1); // empno
0134       ename[i] = rs->GetString(2); // ename
0135       mgr[i] = rs->GetFloat(4); // mgr
0136       sal[i] = rs->GetFloat(6); // sal
0137       comm[i] = rs->GetFloat(7); // comm
0138       row[i] = rs->GetRow();
0139    }
0140
0141    TGraph* gr; //
0142    TCanvas* c = new TCanvas("EMP","EMP table",900,600);
0143    c->Divide(2,2);
0144
0145    Float_t xmin = 0;
0146    Float_t xmax = nrows;
0147    Float_t ymin;
0148    Float_t ymax;
0149    Int_t ind;
0150
0151    c->cd(1);
0152    gPad->SetFillColor(38);
0153    gPad->SetToolTipText("This is EMPNO distribution");
0154    ind = TMath::LocMin(nrows,empno);
0155    ymin = 7000; //empno[ind];
0156    ind = TMath::LocMax(nrows,empno);
0157    ymax = empno[ind]*1.05;
0158    gr = new TGraph(nrows,row,empno);
0159    gr->SetName("empno");
0160    gr->SetTitle("empno");
0161    DrawGraph(gr,ename,"ename",xmin,xmax,ymin,ymax);
0162
0163    c->cd(2);
0164    gPad->SetFillColor(30);
0165    gPad->SetToolTipText("This is SAL distribution");
0166    ind = TMath::LocMin(nrows,sal);
0167    ymin = sal[ind]*0.8;
0168    ind = TMath::LocMax(nrows,sal);
0169    ymax = sal[ind]*1.05;
0170    gr = new TGraph(nrows,row,sal);
0171    gr->SetName("sal");
0172    gr->SetTitle("sal");
0173    DrawGraph(gr,ename,"sal",xmin,xmax,ymin,ymax);
0174
0175    c->cd(3);
0176    gPad->SetFillColor(47);
0177    gPad->SetToolTipText("This is MGR distribution");
0178    ind = TMath::LocMin(nrows,mgr);
0179    ymin = mgr[ind]*0.9;
0180    ymin = 7000;
0181    ind = TMath::LocMax(nrows,mgr);
0182    ymax = mgr[ind]*1.05;
0183    gr = new TGraph(nrows,row,mgr);
0184    gr->SetName("mgr");
0185    gr->SetTitle("mgr");
0186    DrawGraph(gr,ename,"ename",xmin,xmax,ymin,ymax);
0187
0188    c->cd(4);
0189    gPad->SetFillColor(42);
0190    gPad->SetToolTipText("This is COMM distribution");
0191    ind = TMath::LocMin(nrows,comm);
0192    ymin = comm[ind]*0.9;
0193    ind = TMath::LocMax(nrows,comm);
0194    ymax = comm[ind]*1.05;
0195    gr = new TGraph(nrows,row,comm);
0196    gr->SetName("comm");
0197    gr->SetTitle("comm");
0198    DrawGraph(gr,ename,"ename",xmin,xmax,ymin,ymax);
0199 //   gConn->Close();
0200    return 0;
0201 }
0202
0203 //___________________________________________________________________
0204 void Catch(TSQLException* e)
0205 {
0206    // handle exceptions
0207
0208    TString str = e->GetMessage();
0209    printf("SQL Error: %s\n",str.Data());
0210 }
0211
0212 //////////////////////////// Main program ////////////////////////////////////
0213 #ifdef STANDALONE
0214
0215 #include <TROOT.h>
0216 #include <TSystem.h>
0217 #include <iostream>
0218 #include <TApplication.h>
0219
0220 //---- Main program ------------------------------------------------------------
0221
0222 TROOT root("RDBCemp", "EMP table graphs");
0223
0224 int main(int argc, char **argv)
0225 {
0226    if(argc!=3 && argc!=4) {
0227       cerr << "Usage: " << argv[0] << " url username" << endl
0228            << "or     " << argv[0] << " url username password" << endl;
0229       return 0;
0230    }
0231
0232    TApplication theApp("App", &argc, argv);
0233
0234    if (gROOT->IsBatch()) {
0235       cerr << argv[0] << "cannot run in batch mode" << endl;
0236       return 1;
0237    }
0238
0239    gSystem->Load("libRDBC");
0240    Int_t  ret;
0241
0242    if(argc==3) ret= RDBCemp(argv[1],argv[2],"");
0243    else  ret = RDBCemp(argv[1],argv[2],argv[3]);
0244
0245    if(ret) return ret;    // failure
0246
0247    theApp.Run();
0248    return 0;
0249 }
0250 #endif