otl处理不同类型字段

实现在两个库之间数据传输

在db2库测试通过，oracle未测试。

但遇到blob字段类型时，

需要把该字段作为select的最后一个处理，否则会报错。

以下是实现：

 

//执行sql语句获得返回值

template <typename T>
T Exec_Select_Value(const char sSQL[], T vtype, otl_connect &db)
{
  T var;
    try{    
     otl_stream istream(1,
                sSQL,
                db
               );
      while(!istream.eof()) istream>>var;
    }catch(otl_exception& p){      
       cerr<<p.msg<<endl;
       cerr<<p.stm_text<<endl;
       cerr<<p.sqlstate<<endl;
       cerr<<p.var_info<<endl;
         return vtype;
    }
  return var; 
}

 

//类型转换

template<typename out_type,typename in_value>
out_type convert(const in_value & t)
{
 stringstream stream;
 stream<<t;
 out_type result;
 stream>>result;
 return result;
}

 

//数据迁移

bool TransDataDirectory(const char sSELECT[],const char sSRCTAB[],const char sDESTTAB[],const char sDESTFIELD[])
{  
  int iSRCDB;
  int iDESTDB;
  int desc_len;
  string sINT,sLEN;
  string sSTRINGS;
  otl_stream istream;
  stringstream sSTREAM;
  otl_column_desc* desc;  
  string sTAB(sDESTTAB);
  string sTAB2(sSRCTAB);
  string sSQL1(sSELECT);
  string sCLEAN_DEST_TAB;
  string sFIELD(sDESTFIELD);
  string sINSERT_SQL = "insert into " + sTAB + "(" + sFIELD + ") values(";

  iSRCDB = 0;
  iDESTDB = 1;
  db_connect[0].rlogon("db2info/infodb21@shcrm2");
  db_connect[1].rlogon("db2info/infodb21@shcrm");
  otl_stream iss(1,sSQL1.c_str(),db_connect[iSRCDB]);
  desc=iss.describe_select(desc_len);
  for(int n=0;n<desc_len;++n){
     switch(desc[n].otl_var_dbtype){
     case 1:
                  {
                    sLEN = convert<string>(desc[n].dbsize+1);
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<char[" + sLEN + "]>,";
                      break;
                  }
     case 2:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<double>,";
                      break;
                  }
     case 3:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<float>,";
                      break;
                  }
     case 4:
     case 5:
     case 6:
     case 7:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<int>,";
                      break;
                  }
     case 20:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<bigint>,";
                      break;
                  }
     case 8:
     case 16:
     case 17:
     case 18:
     case 19:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<timestamp>,";
                      break;
                  }
     case 9:
     case 11:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<varchar_long>,";
            istream.set_lob_stream_mode(true);
            if(desc[n].dbsize > 32767){
             db_connect[0].set_max_long_size(desc[n].dbsize);
             db_connect[1].set_max_long_size(desc[n].dbsize);
            }
                      break;
                  }
     case 12:
     case 10:
                  {
            sINT = convert<string>(n);
            sINSERT_SQL += ":f" + sINT + "<raw_long>,";
            istream.set_lob_stream_mode(true);
            if(desc[n].dbsize > 32767){
             db_connect[0].set_max_long_size(desc[n].dbsize);
             db_connect[1].set_max_long_size(desc[n].dbsize);
            }
                      break;                    
                  }
     
     }
     
  }
  
  
  string sCOUNTSQL="select cast(count(1) as double) as rownum from " + sTAB2 + " with ur";
    double idsget=0;
    double iSRCNUM,iDESTNUM=0;
    iSRCNUM = Exec_Select_Value(sCOUNTSQL.c_str(),idsget,db_connect[0]);
            
   sCLEAN_DEST_TAB = "alter table " + sTAB + " activate  not logged initially";
   otl_cursor::direct_exec
    (
     db_connect[iDESTDB],
   sCLEAN_DEST_TAB.c_str(),
     otl_exception::disabled // disable OTL exceptions
    );   
   sCLEAN_DEST_TAB = "delete from " + sTAB;   
   otl_cursor::direct_exec
    (
     db_connect[iDESTDB],
   sCLEAN_DEST_TAB.c_str(),
     otl_exception::disabled // disable OTL exceptions
    );
   
  sINSERT_SQL[sINSERT_SQL.length()-1]=')';
  
  istream.open(1,sSQL1.c_str(),db_connect[iSRCDB]);
  while(!istream.eof()){
   try{
     ++iDESTNUM;     
     otl_stream ostream;
     ostream.set_commit(0);
     ostream.set_lob_stream_mode(true);
     ostream.open(1,sINSERT_SQL.c_str(),db_connect[iDESTDB]);
     for(int n=0;n<desc_len;++n){
      switch(desc[n].otl_var_dbtype){
                  case 1:
       {
        string f1;
        istream>>f1;
        ostream<<f1;
        break;
       }
                  case 2:
       {
        double f1;
        istream>>f1;
        ostream<<f1;
        break;
       }
                  case 3:
       {
        float f1;
        istream>>f1;
        ostream<<f1;
        break;
       }
                  case 4:
                  case 5:
                  case 6:
                  case 7:
                  case 20:
       {
        int f1;
        istream>>f1;
        ostream<<f1;
        break;
       }
                  case 8:
                  case 16:
                  case 17:
                  case 18:
                  case 19:
       {
        otl_datetime f1;
        istream>>f1;
        if(istream.is_null())
         ostream<<otl_null();
        else
         ostream<<f1;        
        break;
       }
                  case 9:
                  case 10:
                  case 11:
                  case 12:
       {
        otl_lob_stream ilob;
        otl_lob_stream olob;
        otl_long_string f2(1000);
        istream>>ilob;
        ostream<<olob;
        while(!ilob.eof()){
         ilob>>f2;
         olob<<f2;
         if((int)(100*rand()/(RAND_MAX+1.0)) > 90)
          cout<<"正在导入blob, 请耐心等待..."<<(int)(100*rand()/(RAND_MAX+1.0)) <<endl;
        }
        ilob.close();
        olob.close();
        break;       
       }
      }
     }
     if(iSRCNUM == iDESTNUM or (int)(100*rand()/(RAND_MAX+1.0)) > 90){
      cout<<"表[" <<sDESTTAB <<"]总记录数: " <<iSRCNUM ;
      cout<<" 导入记录数: " <<iDESTNUM;
      cout<<" 完成比例 " <<iDESTNUM/iSRCNUM*100 <<"%" <<endl;
      ostream.flush();
     }
    }catch(otl_exception& p){      
       cerr<<p.msg<<endl;
       cerr<<p.stm_text<<endl;
       cerr<<p.sqlstate<<endl;
       cerr<<p.var_info<<endl;
      }
  }
  db_connect[iDESTDB].commit();
  db_connect[iDESTDB].logoff();
  db_connect[iSRCDB].logoff();
  return true;
}

int main()
{
 string sDESTTAB("DB2INFO.TEST_TAB3");
 string sSRCTAB("nwh.proc");
 string sSELECT(
   "select PROC_NAME,INTERCODE,CHART,PROCCNNAME from nwh.proc where PROCCNNAME is not null"
   );
 string sFIELD(
 "PROC_NAME,INTERCODE,CHART,PROCCNNAME"
    );
 TransDataDirectory(sSELECT.c_str(),sSRCTAB.c_str(),sDESTTAB.c_str(),sFIELD.c_str());
 return 0;
}