CSCOnlineDB.cc

Go to the documentation of this file.

#include "OnlineDB/CSCCondDB/interface/CSCOnlineDB.h"

  condbon::condbon () noexcept(false)
  {
    std::string db_user;
    std::string db_pass;
    env = oracle::occi::Environment::createEnvironment (oracle::occi::Environment::OBJECT);
    char* c_user = ::getenv("CONDBON_AUTH_USER");
    char* c_pass = ::getenv("CONDBON_AUTH_PASSWORD");
    db_user=std::string(c_user);
    db_pass=std::string(c_pass);
    con = env->createConnection (db_user,db_pass,"omds");
    std::cout << "Connection to Online DB is done." << std::endl;
  }// end of constructor condbon ()
  condbon::~condbon () noexcept(false)
  {
    env->terminateConnection (con);
    oracle::occi::Environment::terminateEnvironment (env);
  }  // end of ~condbon ()

  void condbon::cdbon_write (CSCobject *obj, std::string obj_name, int record,
                            int global_run, std::string data_time)
{
  int i,j,k;
  std::string tab, tab_map, tab_data;
  std::string sqlStmt, sqlStmt1;
  int rec_id =0, map_id =0, map_index =0;
  tm curtime;
  time_t now;

  tab=obj_name;
  tab_map=obj_name+"_map";
  tab_data=obj_name+"_data";
  if(obj_name=="test"){
   tab="gains";
   tab_map="gains_map";
   tab_data="gains_data";
  }
  stmt = con->createStatement ();

  oracle::occi::ResultSet *rset;

  sqlStmt = "SELECT max(record_id) from "+tab;
  stmt->setSQL(sqlStmt);
  rset = stmt->executeQuery ();
  //  try{
    while (rset->next ())
    {rec_id= rset->getInt (1);}
    //     }catch(oracle::occi::SQLException ex)
    //    {
    // std::cout<<"Exception thrown: "<<std::endl;
    // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
    // std::cout<<ex.getMessage() << std::endl;
    //}
  stmt->closeResultSet (rset);

  if(record>rec_id){
   sqlStmt = "INSERT INTO "+tab+" VALUES (:1, :2, :3, :4, :5, null)";
   stmt->setSQL(sqlStmt);
   time(&now);
   curtime=*localtime(&now);
   try{
   stmt->setInt (1, record);
   stmt->setInt (2, global_run);
   stmt->setInt (5, 0);
/* For time as "05/17/2006 16:30:07"
  std::string st=data_time.substr(0,2);
  int mon=atoi(st.c_str());
  st=data_time.substr(3,2);
  int mday=atoi(st.c_str());
  st=data_time.substr(6,4);
  int year=atoi(st.c_str());
  st=data_time.substr(11,2);
  int hour=atoi(st.c_str());
  st=data_time.substr(14,2);
  int min=atoi(st.c_str());
  st=data_time.substr(17,2);
  int sec=atoi(st.c_str());
*/
/* For time of format "Mon May 29 10:28:58 2006" */
   std::map <std::string, int> month;
   month["Jan"]=1; month["Feb"]=2; month["Mar"]=3; month["Apr"]=4;
   month["May"]=5; month["Jun"]=6; month["Jul"]=7; month["Aug"]=8;
   month["Sep"]=9; month["Oct"]=10; month["Nov"]=11; month["Dec"]=12;
   std::string st=data_time.substr(4,3);
   int mon=month[st];
   st=data_time.substr(8,2);
   int mday=atoi(st.c_str());
   st=data_time.substr(20,4);
   int year=atoi(st.c_str());
   st=data_time.substr(11,2);
   int hour=atoi(st.c_str());
   st=data_time.substr(14,2);
   int min=atoi(st.c_str());
   st=data_time.substr(17,2);
   int sec=atoi(st.c_str());
   oracle::occi::Date edate(env, year, mon, mday, hour, min, sec);
   stmt->setDate(3, edate);
   oracle::occi::Date edate_c(env, curtime.tm_year+1900, curtime.tm_mon+1, curtime.tm_mday,
   curtime.tm_hour, curtime.tm_min, curtime.tm_sec);
   stmt->setDate(4, edate_c);
   if(obj_name!="test") stmt->executeUpdate ();
   }catch(oracle::occi::SQLException &ex)
   {
    std::cout<<"Exception thrown for insertBind"<<std::endl;
    std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
#if defined(_GLIBCXX_USE_CXX11_ABI) && (_GLIBCXX_USE_CXX11_ABI == 0)
    std::cout << getOraMessage(&ex) << std::endl;
#endif
   }
  }

  sqlStmt = "SELECT max(map_id) from "+tab_map;
  stmt->setSQL(sqlStmt);
  rset = stmt->executeQuery ();
  //  try{
    while (rset->next ())
    {map_id= rset->getInt (1);}
    //     }catch(oracle::occi::SQLException ex)
    //{
    // std::cout<<"Exception thrown: "<<std::endl;
    // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
    // std::cout<<ex.getMessage() << std::endl;
    //}
  stmt->closeResultSet (rset);

  std::ostringstream ss;
  ss<<record;

  sqlStmt = "SELECT max(map_index) from "+tab_map+" where record_id="+ss.str();
  ss.str(""); // clear
  stmt->setSQL(sqlStmt);
  rset = stmt->executeQuery ();
  //  try{
    while (rset->next ())
    {map_index= rset->getInt (1);}
    //     }catch(oracle::occi::SQLException ex)
    //{
    // std::cout<<"Exception thrown: "<<std::endl;
    // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
    // std::cout<<ex.getMessage() << std::endl;
    //}
  stmt->closeResultSet (rset);

  sqlStmt = "INSERT INTO "+tab_map+" VALUES (:1, :2, :3, :4)";
  stmt->setSQL(sqlStmt);

  std::map<int,std::vector<std::vector<float> > >::const_iterator itm;
  itm=obj->obj.begin();
  int sizeint=itm->second[0].size();
  sqlStmt1 = "INSERT INTO "+tab_data+" VALUES (:1, :2";
  for(i=1;i<sizeint+1;++i){
    ss<<i+2;
    sqlStmt1=sqlStmt1+", :"+ss.str();
    ss.str(""); // clear
  }
  sqlStmt1=sqlStmt1+")";

  sb4 si=sizeof(int);
  sb4 sf=sizeof(float);
  ub2 elensi[200];
  ub2 elensf[200];
  int c1[200],c2[200];
  float c[100][200];
  for(i=0;i<200;++i){
    elensi[i]=si;
    elensf[i]=sf;
  }

  stmt1 = con->createStatement ();
  stmt1->setSQL(sqlStmt1);

  for(itm=obj->obj.begin();itm!=obj->obj.end(); ++itm){
   int id_det=itm->first;
   int sizev=obj->obj[id_det].size();

   map_id=map_id+1;
   map_index=map_index+1;
   //  try{
   stmt->setInt (1, map_id);
   stmt->setInt (2, record);
   stmt->setInt (3, map_index);
   stmt->setInt (4, id_det);
   if(obj_name!="test") stmt->executeUpdate ();
   //}catch(oracle::occi::SQLException ex)
   //{
   //std::cout<<"Exception thrown for insertBind"<<std::endl;
   //std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
   //std::cout<<ex.getMessage() << std::endl;
   //}

   k=0;
   for(i=0;i<sizev;++i){
    int sizei=obj->obj[id_det][i].size();
    if(sizei!=sizeint){
      std::cout<<"Inconsistent object - dimention of internal vector is not "
      <<sizeint<<std::endl;
      exit(1);
    }
    c1[i]=map_id;
    k=k+1;
    c2[i]=k;
    for(j=0;j<sizei;++j){
     c[j][i]=obj->obj[id_det][i][j];
    }
   }
   //  try{
   stmt1->setDataBuffer(1, c1, oracle::occi::OCCIINT, si , &elensi[0]);
   stmt1->setDataBuffer(2, c2, oracle::occi::OCCIINT, si , &elensi[0]);
    for(j=0;j<sizeint;++j){
     stmt1->setDataBuffer(j+3, c[j], oracle::occi::OCCIFLOAT, sf, &elensf[0]);
    }
   if(obj_name!="test") stmt1->executeArrayUpdate(sizev);
   //     }catch(oracle::occi::SQLException ex)
   // {
   //  std::cout<<"Exception thrown: "<<std::endl;
   //  std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
   //  std::cout<<ex.getMessage() << std::endl;
   // }
  }
  con->commit();
  con->terminateStatement (stmt);
  con->terminateStatement (stmt1);
} //end of cdbon_write

void condbon::cdbon_last_record (std::string obj_name, int *record)
{
  std::string sqlStmt;
  sqlStmt = "SELECT max(record_id) from "+obj_name;
  stmt=con->createStatement ();
  stmt->setSQL(sqlStmt);
  oracle::occi::ResultSet *rset;
  rset = stmt->executeQuery ();
  //try{
    while (rset->next ())
    {*record = rset->getInt (1);}
    //     }catch(oracle::occi::SQLException ex)
    //{
    // std::cout<<"Exception thrown: "<<std::endl;
    // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
    // std::cout<<ex.getMessage() << std::endl;
    //}
  con->terminateStatement (stmt);
} // end of cdbon_last_record

  void condbon::cdbon_read_rec (std::string obj_name, int record,
                                CSCobject *obj)
{
  int i,len;
  int map_id=0, layer_id=0;
  std::string tab, tab_map, tab_data;
  tab=obj_name;
  tab_map=obj_name+"_map";
  tab_data=obj_name+"_data";
  int num_var =0;
  int vec_index;

  std::string sqlStmt,sqlStmt1;
  stmt=con->createStatement ();
  stmt1=con->createStatement ();
  oracle::occi::ResultSet *rset, *rset1;
  std::ostringstream ss;

  char d;
  const char* p=tab_data.c_str();
  len=tab_data.length();
  for(i=0;i<len;++i){
   d=toupper(*(p+i));
   ss<<d;
  }
  sqlStmt = "SELECT count(column_name) from user_tab_columns where table_name='"+ss.str()+"'";
  ss.str(""); // clear
  stmt->setSQL(sqlStmt);
  rset = stmt->executeQuery ();
  //  try{
    while (rset->next ())
    {num_var= rset->getInt(1) - 2;}
    //     }catch(oracle::occi::SQLException ex)
    //{
    // std::cout<<"Exception thrown: "<<std::endl;
    // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
    // std::cout<<ex.getMessage() << std::endl;
    //}
  stmt->closeResultSet (rset);

  ss<<record;

sqlStmt = "SELECT map_id,map_index,layer_id from "+tab_map+" where record_id="+ss.str()+" order by map_index";
  ss.str(""); // clear
  stmt->setSQL(sqlStmt);
  rset = stmt->executeQuery ();
  //  try{
    while (rset->next ())
    {map_id = rset->getInt (1);
     rset->getInt (2);
     layer_id = rset->getInt (3);
     ss<<map_id;
     sqlStmt1 = "SELECT * from "+tab_data+" where map_id="+ss.str()+" order by vec_index";
     ss.str(""); // clear
     stmt1->setSQL(sqlStmt1);
     rset1 = stmt1->executeQuery ();
     //     try{
       while (rset1->next ())
       {vec_index = rset1->getInt (2);
               obj->obj[layer_id].resize(vec_index);
        for(i=0;i<num_var;++i){
         obj->obj[layer_id][vec_index-1].push_back(rset1->getFloat(3+i));
                              }
       }
       //   }catch(oracle::occi::SQLException ex)
       //{
       // std::cout<<"Exception thrown: "<<std::endl;
       // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
       // std::cout<<ex.getMessage() << std::endl;
       //}
    }
    // }catch(oracle::occi::SQLException ex)
    //{
    // std::cout<<"Exception thrown: "<<std::endl;
    // std::cout<<"Error number: "<<  ex.getErrorCode() << std::endl;
    // std::cout<<ex.getMessage() << std::endl;
    //}
} // end of cdbon_read_rec