/*
Tested on lxplus.cern.ch

 Environment:
. /afs/cern.ch/sw/lcg/contrib/gcc/4.6/x86_64-slc6/setup.sh
export ROOTSYS=/afs/cern.ch/sw/lcg/app/releases/ROOT/5.34.07/x86_64-slc6-gcc46-dbg/root/
export PATH=${ROOTSYS}bin:${PATH}

how to run: root -q -b generic.C
*/

#include "TSQLServer.h"
#include "TSQLResult.h"
#include "TSQLRow.h"
#include "TOracleResult.h"
#include "TOracleRow.h"
#include <string.h>
#include <vector.h>

void generic(){

  ofstream fp1;
  fp1.open("output.txt");

  TSQLServer *db= TSQLServer::Connect("oracle:///databasename","username","password"); 
  if(!db->IsConnected()){
    cout<<"not connected"<<endl;
    db->Close();
    return;
  }
  cout<<"connected to database"<<endl;
  
  string element_name="dcs1:ELMB/BUS_2/ELMB_10/AI/pressure_sensor_59.value"; // data point element name in PVSS
  string start_t="9-JUL-2010 09:00:00.0 AM"; //UTC
  string end_t="9-JUL-2010 11:00:00.0 AM";  //UTC
  cout<<"start_t = "<<start_t<<endl;
  cout<<"end_t = "<<end_t<<endl;
  char query[1000];
  sprintf(query,"select cast(b.value_number as decimal(7,3)),to_char(b.ts,'yyyy-mm-dd hh24:mi:ss' ) 
    from comp_pvss.elements a inner join comp_pvss.eventhistory b on a.element_id=b.element_id 
    where element_name \= \'%s\' 
    and ts between \'%s\' and \'%s\' 
    order by b.ts",element_name.c_str(),start_t.c_str(),end_t.c_str());
  cout<<"query: "<<query<<endl;
  
  TSQLResult * res=db->Query(query);
  if(db->IsError()){
    db->Close();
    return; 
  }
  if(res==NULL){
    db->Close();
    return;
  }  
  int fields=0, rows=0;
  fields=res->GetFieldCount();
  rows=res->GetRowCount();
  cout << "rows = " << rows << endl;
  for(int rc=0;rc<rows;rc++){
    TSQLRow *row=res->Next(); 
    cout<<row->GetField(0)<<" "<<row->GetField(1)<<endl;
    fp1<<row->GetField(0)<<" "<<row->GetField(1)<<endl;
  } 

  delete res;
  db->Close();
  fp1.close();
}