所谓的自动执行sql脚本小程序，c/c++连接sybase

最近同事每天都要用访问sybase数据库统计数据：

虽然可以直接写成sql脚本，但是由于执行sql时间比较长，涉及好多表的连接操作等，怕把数据库跑死了，

所以，每天都要做一些重复性恶心的工作，复制sql脚本，去数据库执行，还要把得到的数据统计汇总。

由于哥们没事儿闲得蛋疼：主动提议写一个小程序，正好也熟悉一下如何用c/c++访问sybase/oralce ，于是

便有了该程序的诞生；

程序需求：主要实现的功能就是按照配置文件中配置的sql脚本，自动去数据库执行(配置sql等待时间，防止

库跑死)，返回结果，生成报告，有需要的话以后还要做一些自动的统计等。

c连接sybase需要sybase的客户端，因为编译的时候需要sybase的库和同文件，先说配置文件吧：

makefile:

DB_PATH=/opt/sybase
DB_DFLAG= -DSYBASE
SYBASE_LIB= -lsybtcl -lsybcs -lsybcomn -lsybintl -lsybunic -lsybct -lsybblk -lm

testSybase:testSybase.cpp Sybase.cpp
g++ -Wall -g testSybase.cpp Sybase.cpp -L${DB_PATH}/OCS-15_0/lib -I${DB_PATH}/OCS-15_0/include ${SYBASE_LIB} -o testSybase
clean:
rm -rf testSybase
rm -rf *.o

==========================

说的很详细，不做分析了

==========================

下面就没什么好说的了，访问sybase，主要用到了<ctpublic.h>这个库中的一些函数，

按部就班的来就行了，哥们把这些封装成了一个类。直接上代码了：

Sybase.h

//
//
//author：jimmy
//CSybase
//Sybase.h
//连接sybase库的头文件
#ifndef _SYBASE_H
#define _SYBASE_H

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctpublic.h>
#include <iostream>
#include <iomanip>
#include <string>

#define MAX_COL 10
#define MAX_ROW 100

using namespace std;

class CSybase{
public:
   CSybase();
   ~CSybase();
   CSybase(string user, string pwd, string server);
   void init();
   bool Connect();
   void DisConnect();
   bool ExecuteSql(string sql);
   void ShowAllRecords();
  
   string GetRecord(int row, int col){return _record[row-1][col-1];}
   int GetRowcount(){return _rowcounts;}
   int GetClonum(){return _colnum;}
  
   void SetRecordW(int wide){_iwide = wide;}
private:
   CS_CONTEXT* _context;        
   CS_CONNECTION* _ptrconnection;    
  
   void ShowDBError(int nErrCode);    //显示连接数据库时的错误信息
  
   string _user;
   string _pwd;
   string _server;
   int _rowcounts;
   int _colnum;
   int _iwide;              //列宽
   string _record[MAX_ROW][MAX_COL];   //记录集

};
#endif
=====================

Sybase.cpp

//
//
//
//CSybase
//Sybase.cpp
//连接sybase库的源文件

#include "Sybase.h"

CSybase::CSybase()
{
_user      = "";
_pwd       = "";
_server    = "";
_rowcounts = 0;
_colnum    = 0;
_iwide     = 10;

}
CSybase::CSybase(string user, string pwd, string server)
{
_user      = user;
_pwd       = pwd;
_server    = server;
_rowcounts = 0;
_colnum    = 0;
_iwide     = 10;
}

CSybase::~CSybase()
{
DisConnect();
}
void CSybase::init()
{
#ifdef DEBUG
   cout<<"CSybase init..."<<endl;
#endif

}
bool CSybase::Connect()
{
CS_RETCODE ret;


if((ret=cs_ctx_alloc(CS_VERSION_100, &_context)) != CS_SUCCEED)
{
   //不成功则调用ShowDBError显示错误代码
   ShowDBError(ret);
   cout<<"alloc error!"<<endl;
   return false;
}

if((ret=ct_init(_context,CS_VERSION_100)) != CS_SUCCEED)
{
   ShowDBError(ret);
   cout<<"init error!"<<endl;
   return false;
}

if((ret=ct_con_alloc(_context, &_ptrconnection))!=CS_SUCCEED)
{
ShowDBError(ret);
cout<<"con_alloc error!"<<endl;
return false;
}

if((ret=ct_con_props(_ptrconnection,CS_SET,CS_USERNAME,(CS_VOID*)_user.c_str(),CS_NULLTERM,NULL))!=CS_SUCCEED)
{
ShowDBError(ret);
   cout<<"username error!"<<endl;
return false;
}
if((ret=ct_con_props(_ptrconnection,CS_SET,CS_PASSWORD,(CS_VOID*)_pwd.c_str(),CS_NULLTERM,NULL))!=CS_SUCCEED)
{
ShowDBError(ret);
cout<<"password error!"<<endl;
return false;
}

#ifdef DEBUG
cout<<"prepare to connect server!"<<endl;
   cout<<"DBName : "<<_server<<endl;
#endif
if((ret=ct_connect(_ptrconnection, (CS_CHAR *)(_server.c_str()), _server.length())) != CS_SUCCEED) //在网上找到的代码这里写错了，用的是对char数组求sizeof(),我调试了半天，后来改成strlen()就对了。鄙视传代码那个哥们，骗人的....

{
ShowDBError(ret);
cout<<"conncet error!"<<endl;
return false;
}
else
cout<<"成功连接到服务器 : "<<_server<<endl;
return true;

}
void CSybase::DisConnect()
{
CS_RETCODE ret;
if ((ret = ct_close(_ptrconnection, CS_UNUSED))!= CS_SUCCEED)
{
   ShowDBError(ret);
}
if ((ret = ct_con_drop(_ptrconnection)) != CS_SUCCEED)
{
   ShowDBError(ret);
}
if((ret=ct_exit(_context,CS_UNUSED))!=CS_SUCCEED)
{
ShowDBError(ret);
}
if((ret=cs_ctx_drop(_context))!=CS_SUCCEED)
{
ShowDBError(ret);
}
_context = (CS_CONTEXT *)NULL;
#ifdef DEBUG
   cout<<"success to disconnect Sybase"<<endl;
#endif
return;
}
bool CSybase::ExecuteSql(string sql)
{
CS_RETCODE ret;
CS_COMMAND* com;
CS_INT result;
CS_INT numcol;
CS_INT rowread;
CS_DATAFMT target_fmt;
CS_BYTE temp[1024][5];
#ifdef DEBUG
    cout<<"prepare to execute sql :"<<sql<<endl;
   #endif
if((ret=ct_cmd_alloc(_ptrconnection,&com)) != CS_SUCCEED)
{
ShowDBError(ret);
   cout<<"command alloc error!"<<endl;
return false;
}

   if((ret = ct_command(com, CS_LANG_CMD, (CS_CHAR*)sql.c_str(), CS_NULLTERM, CS_UNUSED))!= CS_SUCCEED)
   {
    ShowDBError(ret);
    cout<<"ct_command error!"<<endl;
return false;
   }
   if((ret = ct_send(com)) != CS_SUCCEED)
{
    ShowDBError(ret);
    cout<<"ct_send error!"<<endl;
return false;
   }
   //准备结果集

   while((ret = ct_results(com , &result)) != CS_SUCCEED) //注意result的使用
   {
    ShowDBError(ret);
    cout<<"ct_results error!"<<endl;
    return false;
   }
  
   //information
if((ret = ct_res_info(com, CS_NUMDATA, &numcol, CS_UNUSED, NULL)) !=CS_SUCCEED)
{
ShowDBError(ret);
    cout<<"ct_res_info error!"<<endl;
    return false;
   }
   _colnum = (int)numcol;
   #ifdef DEBUG
    cout<<"记录共有 :"<<_colnum<<" 列"<<endl;
   #endif
  
   for(int i=1; i<=_colnum; i++)
{
   if((ret = ct_describe(com, i,&target_fmt)) != CS_SUCCEED)
   {
    ShowDBError(ret);
     cout<<"ct_describe error!"<<endl;
     return false;
   }
   else
   {
    cout<<setw(_iwide)<<target_fmt.name<<" ";
   }
}
cout<<endl;
  
   memset(&target_fmt, 0, CS_SIZEOF(target_fmt));

   ///设置格式
target_fmt.datatype = CS_CHAR_TYPE;
target_fmt.maxlength = 50;
target_fmt.count = 1;
target_fmt.format = CS_FMT_NULLTERM;
绑定参数
for(int i=0; i<numcol; i++)
{
   if((ret = ct_bind(com, (i+1), &target_fmt, (CS_VOID*)temp[i], NULL, NULL))!= CS_SUCCEED)
   {
      ShowDBError(ret);
    cout<<"ct_bind error!"<<endl;
    return false;
    }
}
   //=====
   //循环取记录
   int count = 0;
while (((ret = ct_fetch(com ,CS_UNUSED, CS_UNUSED, CS_UNUSED, &rowread)) == CS_SUCCEED)|| (ret == CS_ROW_FAIL))
{
    if (ret == CS_ROW_FAIL)
    {
    cout<<"error on row"<<endl;
    break;
    }
    else
    {
    count++;
    for (int i=0; i<numcol ; i++ )
    {
       _record[count-1][i] = (char*)(temp[i]);
    }
   }
}
_rowcounts = count;
#ifdef DEBUG
   cout<<"记录共有: "<<count<<" 行"<<endl;
#endif

// 关闭命令,如果不加这个的话，直接调用Disconnect ，会发生错误！
if ((ret=ct_cancel(NULL, com, CS_CANCEL_ALL)) != CS_SUCCEED)
{
   cout<<"ct_cancle error!"<<endl;
   return false;
}

   return true;

}
void CSybase::ShowDBError(int nErrCode)
{
switch(nErrCode)
{
case CS_MEM_ERROR:
cout<<"内存不足或地址分配错误!"<<endl;
break;
case CS_PENDING:
cout<<"异步网络I/O正在进行!"<<endl;
break;
case CS_BUSY:
   cout<<"当前连接内有一异步操作正在进行!"<<endl;
break;
case CS_CANCELED:
cout<< "操作被取消!"<<endl;
break;
case CS_END_RESULTS:
   cout<<"从服务器返回的结果处理结束!"<<endl;
break;
case CS_ROW_FAIL:
    cout<<"提取当前行的数据失败!"<<endl;
break;
case CS_END_DATA:
cout<<"数据提取结束!"<<endl;
break;
case CS_FAIL:
cout<<"函数执行失败!"<<endl;
break;
default:
cout<<"系统不识别的错误!"<<endl;
break;
}
}
void CSybase::ShowAllRecords()
{
int i,j;
for(i=0; i<_rowcounts; i++)
{
   for(j=0; j<_colnum; j++)
   {
    cout<<setw(_iwide)<<GetRecord(i+1,j+1)<<" ";
   }
   cout<<endl;
}
}

====================

主函数

、#include <iostream>
#include <vector>
#include <fstream>
#include <map>
#include <time.h>

#include "Sybase.h"

typedef struct _DATA
{
string nename;
int    fail;
int    all;
float percent;
string sql1;
string sql2;
}DATA;

typedef map<string,string> MP;

#define MAXNECOUNT 30

MP sqllist;     //保存sql信息
MP conf;          //保存配置信息
vector<DATA> vecdata;
int necount = 0;

void init()
{
sqllist.clear();
conf.clear();
vecdata.clear();
}
void load(string file)//将file文件的n*3条信息提取出来
{
int i ;
string tmpstr;
ifstream in(file.c_str());
getline(in, tmpstr);
necount = atoi(tmpstr.c_str());
DATA tmpdata;
for(i=0; i<necount; i++)
{
   getline(in, tmpstr);
   tmpdata.nename = tmpstr;
   getline(in, tmpstr);
   tmpdata.sql1   = tmpstr;
   getline(in, tmpstr);
   tmpdata.sql2   = tmpstr;
  
   vecdata.push_back(tmpdata);
}
#ifdef DEBUG
   cout<<"load "<<file <<" OK!"<<endl;
#endif
in.close();
}
void load(string file, MP* pmp)//将file文件的n行 xx=yy 提取到pmp中
{
int i ;
string tmpstr;
ifstream in(file.c_str());
getline(in, tmpstr);
int count = atoi(tmpstr.c_str());
size_t pos;
for(i=0; i< count; i++)
{
   getline(in, tmpstr);
   pos = tmpstr.find_first_of("=");
   string temp = tmpstr.substr(0,pos);
   tmpstr = tmpstr.substr(pos+1, tmpstr.length()-pos-1);
   pmp->insert(make_pair(temp,tmpstr));
}
#ifdef DEBUG
   cout<<"Load "<< file <<" OK!"<<endl;
#endif
in.close();
}
void wirtefile(string file)
{
ofstream out(file.c_str());

for(int i=0; i<100; i++)
{
   out<<"hello"<<endl;
   sleep(1);
  
}
out.close();
}
string processStr(string strbegin, string strend, string sql )
{
size_t pos = sql.find("[begin]");
int len = strlen("[begin]");
string temp = sql;

if(pos != string::npos)
{
   temp.replace(pos, len, strbegin);
}
pos = temp.find("[end]");
len = strlen("[end]");
if(pos != string::npos)
{
   temp = temp.replace(pos, len, strend);
}
return temp;
}
void compute(vector<DATA>* pvec)
{
vector<DATA>::iterator it;
for(it=pvec->begin(); it!=pvec->end(); it++)
{
   it->percent = (float)(((float)(it->fail))/((float)(it->all)));
}
#ifdef DEBUG
   cout<<"compute 执行完毕！"<<endl;
#endif
}
int main(int argc, char** argv)
{

string struser,strpwd,strserver;
string sql;
int iwaittime = 2;
int i = 0;
int wide = 10;
time_t tm;

system("clear");

load("conf.txt", &conf);//读取配置信息
load("sql.txt", &sqllist);//读取sql脚本

struser = (conf.find("USERNAME"))->second;
strpwd = (conf.find("PWD"))->second;
strserver = (conf.find("SERVER"))->second;
iwaittime = atoi(((conf.find("SLEEPTIME"))->second).c_str());
wide = atoi(((conf.find("WIDE"))->second).c_str());

CSybase cs(struser, strpwd, strserver);//
cs.Connect();
cs.SetRecordW(wide);
cout<<"开始自动执行脚本 :"<<endl;
cout<<endl<<"----------------------------------------------------"<<endl<<endl;
MP::iterator it;
for(it=sqllist.begin(); it!=sqllist.end(); it++)
{
   cout<<"[" <<(++i)<<"]: "<<(it->first)<<endl;
   cs.ExecuteSql((it->second));
   cs.ShowAllRecords();
   sleep(iwaittime);
}
time(&tm);
cout<<endl<<"----------------------------------------------------"<<endl<<endl;
cout<<"结束全部统计当前时间为： "<<ctime(&tm)<<"共统计 "<< i <<" 条记录"<<endl<<"谢谢使用，程序自动退出！"<<endl;


return 0;
}