My C++ Wrapper for FreeTDS

This artical introduce you my C++ wrapper for FreeTDS MSSqlServer interface.

 

With this wrapper you can easily connect/disconnect sqlserver database, and execute insert/delete/update/select/create table commands.

 

This wrapper is not thread-safe and not fully tested.  Leave me your better suggestions will be a good help.

 

You are free to use it in any way, but the author name should always be there.

 

//FreeTDSHelper.h//author: huanghaifeng#ifndef FREETDSHELPER_H#define FREETDSHELPER_H#include <stdio.h>#include <string.h>#include <stdlib.h>#include <unistd.h>#include </user/local/freetds/include/sybfront.h>#include </user/local/freetds/include/sybdb.h>#define MAX_ROWS 1024#define ERR_MSG_LEN 1024struct FIELD{char* data;int bufSize;FIELD(int size):data(NULL),bufSize(size){data = new char[size];}~FIELD(){if(data!=NULL)delete[] data;data=NULL;}};struct ROW{int colcount;int* fieldLens;FIELD** fields;ROW(int* fLens, int columncount):fields(NULL),colcount(columncount),fieldLens(fLens){fields = new FIELD*[columncount];for(int i=0;i<columncount;++i){fields[i] = new FIELD(fieldLens[i]);}}~ROW(){if(fields!=NULL){for(int i=0;i<colcount;++i){delete fields[i];fields[i]=NULL;}delete[] fields;fields=NULL;}}char* getField(int fieldNum){return fields[fieldNum]->data;}};//sqlserver wrapper, not thread-safeclass FreeTDSHelper{public:FreeTDSHelper();~FreeTDSHelper();/********************************************** *功能:打开指定数据库 *入参: 1,server为数据库主机,如192.168.90.5:1433 *2,user为数据库帐号 * 3,password为数据库密码 *4,dbname为数据库名 *5,charset为字符集,默认为gbk **********************************************/bool openDB(char* server, char* user, char* password, char* dbname, char* charset=NULL);/********************************************** *功能:关闭当前数据库 **********************************************/bool closeDB();/********************************************** *功能:增加, 删除, 修改, 建表 **********************************************/bool execute(char* sql);/********************************************** *功能:查询 *入参: 1,sql是查询语句 *2,maxRowCount表示需要缓存的最大行数 *返回: >=0代表结果集的行数, <0代表失败 **********************************************/int query(char* sql, int maxRowCount=MAX_ROWS);/********************************************** *功能:取第rownum行 **********************************************/ROW* getRow(int rownum);/********************************************** *功能:每次用完query必须调用此函数清理结果集 **********************************************/bool releaseResultSet();/********************************************** *功能:返回当前结果集的行数 **********************************************/int getRowCount() const;/********************************************** *功能:返回当前结果集的列数 **********************************************/int getColumnCount() const;/********************************************** *功能:返回上一个操作的错误信息 **********************************************/char* getErrorMessage();private:FreeTDSHelper(const FreeTDSHelper&);FreeTDSHelper& operator=(const FreeTDSHelper&);LOGINREC* loginrec; DBPROCESS* dbprocess;int rowCount;int colCount;int* pBufSize;ROW* currentRow;bool prepareRowBuffer();bool destroyRowBuffer();char errMsg[ERR_MSG_LEN];};#endif//FREETDSHELPER_H

 

//FreeTDSHelper.cpp//author: huanghaifeng#include "FreeTDSHelper.h"FreeTDSHelper::FreeTDSHelper():loginrec(NULL),dbprocess(NULL),currentRow(NULL),rowCount(0),colCount(0),pBufSize(NULL){memset(errMsg,0,ERR_MSG_LEN);dbinit();}FreeTDSHelper::~FreeTDSHelper(){closeDB();}bool FreeTDSHelper::openDB(char* server, char* user, char* password, char* dbname, char* charset){if(server==NULL || dbname==NULL){sprintf(errMsg,"Server-address or db-name you supplied is null, that is not allowed.");return false;}closeDB();loginrec = dblogin();DBSETLUSER(loginrec, user); DBSETLPWD(loginrec, password); DBSETLCHARSET(loginrec,charset==NULL?"GBK":charset);dbprocess = dbopen(loginrec, server);if(dbprocess == FAIL){sprintf(errMsg,"dbopen error, can not connect to db-server.");dbloginfree(loginrec);loginrec=NULL;return false; }//选定数据库if(dbuse(dbprocess, dbname) == FAIL){sprintf(errMsg,"dbuse error, can not use your chosen db.");dbclose(dbprocess);dbprocess=NULL;dbloginfree(loginrec);loginrec=NULL;return false;}return true;}bool FreeTDSHelper::closeDB(){if((dbprocess!=FAIL) || (dbprocess!=NULL)){if(currentRow != NULL){releaseResultSet();}dbclose(dbprocess);dbprocess=NULL;}if(loginrec != NULL){dbloginfree(loginrec);loginrec=NULL;}}bool FreeTDSHelper::execute(char* sql){//发送增删改命令dbcmd(dbprocess, sql);if(dbsqlexec(dbprocess) == FAIL){snprintf(errMsg,ERR_MSG_LEN-1,"dbsqlexec error(%s)",sql);return false; }return true;}int FreeTDSHelper::query(char * sql, int maxRowCount){if(sql==NULL){sprintf(errMsg,"Your sql string is null.");return -1;}//设置缓存, 最多缓存行数为MAX_ROWSchar bufLen[16]={0};sprintf(bufLen,"%d",maxRowCount);if(dbsetopt(dbprocess,DBBUFFER,bufLen,sizeof(bufLen)) == FAIL){sprintf(errMsg,"dbsetopt error, can not set buffer.");return -1;}releaseResultSet();//发送查询命令dbcmd(dbprocess, sql);if(dbsqlexec(dbprocess) == FAIL){snprintf(errMsg,ERR_MSG_LEN-1,"dbsqlexec error(%s)",sql);return -1; }//获取结果集并计算行数DBINT result_code;while ((result_code = dbresults(dbprocess)) != NO_MORE_RESULTS){if (result_code == SUCCEED){while (dbnextrow(dbprocess) != NO_MORE_ROWS){++rowCount;if(rowCount>MAX_ROWS){sprintf(errMsg,"Not enough buffer to hold all rows.");return -1;}}}}//准备一行数据所需的内存prepareRowBuffer();//绑定一行数据每列的内存for(int i=0;i<colCount;++i){if(dbbind(dbprocess, i+1, CHARBIND, (DBCHAR)0, (BYTE*)(currentRow->getField(i))) == FAIL){sprintf(errMsg, "dbbind error, the column number given isn't valid, or /the vartype isn't compatible with the sqlserver data type being returned, or varaddr is null.");return -1;}}return rowCount;}ROW* FreeTDSHelper::getRow(int rownum){for(int i=0;i<colCount;++i){memset(currentRow->getField(i), 0, pBufSize[i]);}//每调用一次dbgetrow, 被绑定的内存就会更新一次dbgetrow(dbprocess,rownum+1);return currentRow;}bool FreeTDSHelper::releaseResultSet(){dbfreebuf(dbprocess);rowCount=0;colCount=0;destroyRowBuffer();return true;}bool FreeTDSHelper::prepareRowBuffer(){//求列数colCount = dbnumcols(dbprocess);//分配空间存放各列的长度pBufSize = new int[colCount];//求各列的数据长度for(int i=1;i<=colCount;i++){pBufSize[i-1] = dbcollen(dbprocess, i);}//分配一行空间currentRow = new ROW(pBufSize, colCount); return true;}bool FreeTDSHelper::destroyRowBuffer(){if(currentRow!=NULL){delete currentRow;currentRow=NULL;}if(pBufSize!=NULL){delete[] pBufSize;pBufSize=NULL;}return true;}int FreeTDSHelper::getRowCount() const{return rowCount;}int FreeTDSHelper::getColumnCount() const{return colCount;}char* FreeTDSHelper::getErrorMessage(){return errMsg;}

 

//main.cpp//author: huanghaifeng#include "FreeTDSHelper.h"#include <iostream>using std::cin;int selectAndPrintTable(FreeTDSHelper& helper, char* sql){//查询if(helper.query(sql) == -1){printf("%s/n", helper.getErrorMessage());return -1;}printf("there are %d rows, %d columns/n", helper.getRowCount(), helper.getColumnCount());for(int i=0; i<helper.getRowCount(); ++i){ROW* r = helper.getRow(i);for(int j=0; j<helper.getColumnCount(); ++j){printf("%s/t", r->getField(j));}printf("/n");}helper.releaseResultSet();//查询后必须调用释放空间return 0;}int main(){printf("----------------------------------------/n");FreeTDSHelper helper;#if 1if(helper.openDB("192.168.90.5:1433","sa","123456","test") == false){printf("%s/n",helper.getErrorMessage());return -1;}//查询if(selectAndPrintTable(helper, "select * from table1") == -1) return -1;printf("----------------------------------------/n");printf("press enter to update table/n");cin.get();//修改if(helper.execute("update table1 set name='Anthony' where id = 1") == false){printf("%s/n",helper.getErrorMessage());return -1;}//查询if(selectAndPrintTable(helper, "select * from table1") == -1) return -1;printf("----------------------------------------/n");printf("press enter to insert one row to table/n");cin.get();//插入if(helper.execute("insert into table1 values(7,'Rock')") == false){printf("%s/n",helper.getErrorMessage());return -1;}//查询if(selectAndPrintTable(helper, "select * from table1") == -1) return -1;printf("----------------------------------------/n");printf("press enter to delete one row from table/n");cin.get();//删除if(helper.execute("delete from table1 where id = 7") == false){printf("%s/n",helper.getErrorMessage());return -1;}//查询if(selectAndPrintTable(helper, "select * from table1") == -1) return -1;printf("----------------------------------------/n");#endif#if 1printf("press enter to create table2 and insert rows into it/n");cin.get();//建表后插入if(helper.execute("create table table2(id int, name varchar(32), addr varchar(512))") == false){printf("%s/n",helper.getErrorMessage());return -1;}if(helper.execute("insert into table2 values(1,'Rock1','belibala~~~~~belibala')") == false){printf("%s/n",helper.getErrorMessage());return -1;}if(helper.execute("insert into table2 values(2,'Rock2','belibala~~~~~belibala~~~~~belibala')") == false){printf("%s/n",helper.getErrorMessage());return -1;}if(helper.execute("insert into table2 values(3,'Rock3','belibala~~~~~belibala~~~~~belibala~~~~~belibala')") == false){printf("%s/n",helper.getErrorMessage());return -1;}if(helper.execute("insert into table2 values(4,'Rock4','belibala~~~~~belibala~~~~~belibala~~~~~belibala~~~~~belibala')") == false){printf("%s/n",helper.getErrorMessage());return -1;}if(helper.execute("insert into table2 values(5,'Rock5','belibala~~~~~belibala~~~~~belibala~~~~~belibala~~~~~belibala~~~~~belibala')") == false){printf("%s/n",helper.getErrorMessage());return -1;}//查询if(selectAndPrintTable(helper, "select * from table2") == -1) return -1;printf("----------------------------------------/n");#endifreturn 0;}