公子周

                         --不乱于心，不困于情
随笔 - 3, 文章 - 0, 评论 - 5, 引用 - 0
数据加载中……

ODBC连接sql数据库，支持创建数据库，创建表，查询，更新，删除

此代码，在网上有多份，但是很多不知道，创建数据库，创建表，查询，更新，删除
class ODBC
{
private:  
SQLHANDLE hEnv;  
SQLHANDLE hDbc;  
SQLHANDLE hStmt;  
SQLRETURN retCode;  
SQLINTEGER retErro;  
SQLINTEGER rowCount;  
SQLSMALLINT colCount;  
vector<string> DBinfo;
bool bState;  
string ws2s(const wstring& ws);
wstring s2ws(const string& s);
bool Close();  
bool IsOpen();  
public:
ODBC(void);
~ODBC(void);
public:
bool Connect();  
   
    int GetRowCount(){return rowCount;}  
    int GetColCount(){return colCount;}  
void replace(string &str,const char *string_to_replace,const char *new_string);
void replace(string &str,const char *string_to_replace,int num);
    vector<string*> ExecuteQueryVector(const char* pszSql);  
    int ExecuteQuery(const char* pszSql);  //执行查询  
    int ExecuteNonQuery(const char* pszSql);//执行非查询(更新或删除)  
int createdatabase(string sql);//创建数据库,0为成功，-1为失败
int createtable(string sql);//创建数据表。0为成功,-1为失败,特别注意，语句不要带go字眼
};

ODBC::ODBC(void)
{
bState=false;  
rowCount=colCount=0;  
retCode=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&hEnv);  
if((retCode!=SQL_SUCCESS)&& (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro AllocHandle"<<retCode<<endl;  
return;  
}  
retCode=SQLSetEnvAttr(hEnv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,SQL_IS_INTEGER);  
if((retCode!=SQL_SUCCESS)&& (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro AllocHandle"<<retCode<<endl;  
SQLFreeHandle( SQL_HANDLE_DBC, hEnv );  
return;  
}  
retCode=SQLAllocHandle(SQL_HANDLE_DBC,hEnv,&hDbc);  
if((retCode!=SQL_SUCCESS)&& (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro AllocHandle"<<retCode<<endl;  
SQLFreeHandle( SQL_HANDLE_DBC, hEnv );  
return;  
}  
}
ODBC::~ODBC(void)
{
Close(); 
}
bool ODBC::Close()  
{  
if(bState)  
{  
SQLDisconnect(hDbc);  
SQLFreeHandle(SQL_HANDLE_DBC,hDbc);  
SQLFreeHandle(SQL_HANDLE_ENV,hEnv);  
bState=false;  
}  
return true;  
}  
bool ODBC::Connect()  
{  
string ip,user,password;
ip="127.0.0.1";
user="sa";
password="Nicholas@123";
if(bState==false)  
{  
// retCode=SQLConnect(hDbc,(SQLCHAR*) pszDSN,SQL_NTS,(SQLCHAR*) pszUName,SQL_NTS,(SQLCHAR*) pszUPassword,SQL_NTS);
string strConnect="Driver={SQL Server};Database=test;Server=";
strConnect+=ip;
strConnect+=";uid=";
strConnect+=user;
strConnect+=";pwd=";
strConnect+=password;
short sDriverOutputLength;
wchar_t szDriverOutput[256];
retCode=SQLDriverConnect(hDbc,NULL,(SQLWCHAR *)s2ws(strConnect).c_str(),SQL_NTS, (SQLWCHAR  *)szDriverOutput,256,&sDriverOutputLength,SQL_DRIVER_NOPROMPT);
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro Connect "<<retCode<<endl;  
SQLFreeHandle( SQL_HANDLE_DBC, hDbc );  
return false;  
}  
retCode=SQLAllocHandle(SQL_HANDLE_STMT,hDbc,&hStmt);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro Connect "<<retCode<<endl;  
SQLDisconnect( hDbc );  
SQLFreeHandle( SQL_HANDLE_DBC, hDbc);  
return false;  
}  
}       
bState=true;  
cout<<"success!"<<endl;  
return true;  
}  
string ODBC::ws2s(const wstring& ws)
{
_bstr_t t = ws.c_str();
char* pchar = (char*)t;
string result = pchar;
return result;
}
wstring ODBC::s2ws(const string& s)
{
_bstr_t t = s.c_str();
wchar_t* pwchar = (wchar_t*)t;
wstring result = pwchar;
return result;
}
int ODBC::ExecuteQuery(const char* pszSql)  
{  
string strr=pszSql;
if(pszSql==NULL)  
return 0;  
cout<<"hStmt="<<hStmt<<endl;  
retCode=SQLExecDirect(hStmt,(SQLWCHAR *)s2ws(strr).c_str(),SQL_NTS);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro ExecDirect "<<retCode<<endl;  
return -1;  
}  
/*  retCode=SQLRowCount(hStmt,&rowCount);  //不受select 影响。。 
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO)) 
{ 
cout<<"Erro RowCount "<<retCode<<endl; 
return -1; 
}*/  
retCode=SQLNumResultCols(hStmt,&colCount);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro ColCount "<<retCode<<endl;  
return -1;  
}  
rowCount=0;  
while(SQL_NO_DATA!=SQLFetch(hStmt))  
{  
//cout<<pszBuf<<endl;  
rowCount++;  
}  
SQLCancel(hStmt);  
return rowCount;  
}  
int ODBC::ExecuteNonQuery(const char* pszSql)  
{  
rowCount=0;  
if(pszSql==NULL)  
return 0;  
//cout<<"hStmt="<<hStmt<<endl;  
retCode=SQLExecDirect(hStmt,(SQLWCHAR *)s2ws(pszSql).c_str(),SQL_NTS);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
// cout<<"Erro ExecDirect "<<retCode<<endl;  
return -1;  
}  
retCode=SQLRowCount(hStmt,&rowCount);   
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
// cout<<"Erro RowCount "<<retCode<<endl;  
return -1;  
}  
retCode=SQLNumResultCols(hStmt,&colCount);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
// cout<<"Erro ColCount "<<retCode<<endl;  
return -1;  
}  
SQLCancel(hStmt);  
return rowCount;  
}  
vector<string* >  ODBC::ExecuteQueryVector(const char* pszSql)  
{  
string strr=pszSql;
vector<string* > v;  
if(pszSql==NULL)  
return v;  
retCode=SQLExecDirect(hStmt,(SQLWCHAR *)s2ws(strr).c_str(),SQL_NTS);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro ExecDirect "<<retCode<<endl;  
return v;  
}  
retCode=SQLNumResultCols(hStmt,&colCount);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
cout<<"Erro ColCount "<<retCode<<endl;  
return v;  
}  
rowCount=0;  
SQLINTEGER colLen = 0;  
SQLSMALLINT buf_len = 0;  
SQLINTEGER colType = 0;  
while(true)  
{    
char sz_buf[256];  
char* pszBuf;  
SQLINTEGER  buflen;  
string* rowData=new string[colCount+1];    
if(SQLFetch(hStmt)==SQL_NO_DATA)  
{  
break;  
}  
for(int i=1;i<=colCount;i++)  
{  
SQLColAttribute(hStmt, i, SQL_DESC_NAME, sz_buf, 256, &buf_len, 0);  
SQLColAttribute(hStmt, i, SQL_DESC_TYPE, 0, 0, 0, &colType);  
SQLColAttribute(hStmt, i, SQL_DESC_LENGTH, NULL, 0, 0, &colLen);  
pszBuf=new char[colLen+1];  
pszBuf[0]='/0';  
SQLGetData(hStmt,i,SQL_C_CHAR,pszBuf,colLen,&buflen);  
rowData[i-1]=pszBuf;  
}  
v.push_back(rowData);     
rowCount++;  
}  
SQLCancel(hStmt);  
return v;  
}  
bool ODBC::IsOpen()  
{  
return bState;  
}  
void ODBC::replace(string &str,const char *string_to_replace,const char *new_string)
{
    //   Find   the   first   string   to   replace
    int   index   =   str.find(string_to_replace);
    //   while   there   is   one
    while(index   !=   std::string::npos)
    {
        //   Replace   it
        str.replace(index,   strlen(string_to_replace),   new_string);
        //   Find   the   next   one
        index   =   str.find(string_to_replace,   index   +   strlen(new_string));
    }
} 
void ODBC::replace(string &str,const char *string_to_replace,int num)
{
stringstream strStream;
strStream<<num;
string new_string=strStream.str();
int   index   =   str.find(string_to_replace);
while(index   !=   std::string::npos)
{
str.replace(index,   strlen(string_to_replace),   new_string);
index   =   str.find(string_to_replace,   index   +  new_string.length());
}
}
//创建数据库
int ODBC::createdatabase(string sql)
{
string strr=sql;
if(sql=="")  
return -1;  
retCode=SQLExecDirect(hStmt,(SQLWCHAR *)s2ws(strr).c_str(),SQL_NTS);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
return -1;
}  
return 0;
}
//创建数据表。0为成功,-1为失败
int ODBC::createtable(string sql)
{
string strr=sql;
if(sql=="")  
return -1;  
retCode=SQLExecDirect(hStmt,(SQLWCHAR *)s2ws(strr).c_str(),SQL_NTS);  
if((retCode != SQL_SUCCESS) && (retCode != SQL_SUCCESS_WITH_INFO))  
{  
return -1;
}  
return 0;
}


ODBC odbc;
odbc.connect();
//在实际使用的时候，

string sql="insert into [test].[dbo].[Exception] values ('0%','1%',2%,3%,'4%','5%','6%',7%);";
odbc.replace(sql,"0%",iter->first.c_str());//站点名称
odbc.replace(sql,"1%",iter_url_ip->first.c_str());//IP地址
odbc.replace(sql,"2%",iter_url_ip->second.ipcount);//IP访问次数
odbc.replace(sql,"3%",1);//攻击类型
odbc.replace(sql,"4%",_Map.getTime().c_str());//时间
odbc.replace(sql,"5%",sessiontop10.c_str());//session top 10
odbc.replace(sql,"6%",urltop10.c_str());//url top 10
odbc.replace(sql,"7%",0);//DDOS判定原因
odbc.ExecuteNonQuery(sql.c_str());

posted on 2012-07-10 22:13 公子周 阅读(1563) 评论(0)  编辑 收藏 引用 所属分类: 数据库类


只有注册用户登录后才能发表评论。
网站导航: 博客园   IT新闻   BlogJava   知识库   博问   管理