Linux(Unix)下MySQL数据库访问接口程序MCI

引用:

#ifndef  _MCI_H_
#define  _MCI_H_
#include <iostream>
#include <pthread.h>
#include <time.h>
#include <stdlib.h>
#include <stdio.h>
#include <memory.h>
#include "/usr/local/mysql/include/mysql.h"
const unsigned int MAX_FIELD_LEN = 1024*1;
class MCIException
{
public:
     int  ErrNo;
     char ErrInfo[256];
     MCIException(const char *errinfo,int errno);
char *getErrInfo();
int   getErrNo(){return ErrNo;};
//自定义错误类型
//1    不支持的字段类型
//2    字段越界
//3    字段不存在
//MySQL内部错误类型
//2002 Can't connect to local MySQL server through socket
//2003 Can't connect to MySQL server
//2013 Lost connection to MySQL server during query
//1045 Access denied for user
};

class MCIDatabase
{
public:
char DBIP[20];   //数据库IP地址
char User[10];  //用户名
char Pwd[10];  //密码
char DBName[20]; //数据库名
MYSQL *mysql;
public:
MCIDatabase();
MYSQL* getMySQL(){return mysql;};
void setLogin(const char* dbip,const char* usr, const char* pwd, const char* dbname) ;
int  connect();
void disConnect();
};

class MCIField
{
public:
friend class MCIQuery;
MCIQuery*        pParentQuery; //指向该Field所属于的Query
char             FieldName[30];  //字段名称(目前支持30长度)
char         StrBuf[255]; //用于保存转换为字符串后的值
unsigned char*  DataBuf; //预绑定缓冲区
enum_field_types FieldType; //MySQL内部数据类型
unsigned int  FieldLength; //数据长度
public:
MCIField();
~MCIField();
void setFieldName(const char* s);
void setFieldType(enum_field_types n);
void setFieldLength(unsigned int n);

char* getFieldName();
char* getStrBuf();
enum_field_types getFieldType();
unsigned int getFieldLength();

MCIQuery* getParentQuery();
void setParentQuery(MCIQuery* pQry);
        static void  trimLeft(char* str);
        static void  trimRight(char* str);
        static char* allTrim(char* str);
char*        asString();
int          asInteger();
float        asFloat();
char         asChar(int pos = 0);

};

class MCIQuery
{
public:
MCIDatabase* pDB;
MYSQL_RES*   pRes;

int          FieldNum;       //字段个数                 
MYSQL_FIELD* pFields;      //得到的字段信息           
    MCIField*    pMCIFieldList;  //在内部保存的所有字段信息
      
MYSQL_ROW    Row;
int          RowNum;
char         SqlStr[1024*3];
int       CurrRow;

/*
//检查超时用
int  ThreadExist;
int  ThreadMode;
pthread_t    QryID;
int  ErrNo;
char ErrInfo[256];
*/

public:
MCIQuery();
void setDB(MCIDatabase *dblink);
~MCIQuery();
void  setSql(char* sqlstr);
     void  open();                   //执行select型SQL语句
     int   getFieldsDef();           //获得字段信息,并为字段分配取值的缓冲区
     int   getRecordCount(); //返回查询到的符合条件的记录的条数
     int   next();                 //移动到下一个记录,同时获取字段值
     MCIField* field(int i);         //取相应字段值
     MCIField* fieldByName(const char* s);
     int   exec();          //执行insert,update型SQL语句,返回被此语句影响的记录条数
     void  close(); //关闭一个Query,为下次执行做准备
     
     /*
     //检查超时用
     int runSql();
     static void* QryThread(void* arg);
     */
};
#endif
[mci.cpp]:
#include "mci.h"

MCIException::MCIException(const char *errinfo,int errno)
{
memset(ErrInfo,0,sizeof(ErrInfo));
strncpy(ErrInfo,errinfo,sizeof(ErrInfo)-1);
ErrNo = errno;
}

char* MCIException::getErrInfo()
{
return ErrInfo;
}

MCIDatabase::MCIDatabase()
{
memset(DBName,0,sizeof(DBName));
memset(User,0,sizeof(User));
memset(Pwd,0,sizeof(Pwd));
mysql = NULL;
}

//设置登陆信息
void MCIDatabase::setLogin(const char* dbip,const char* usr, const char* pwd,const char* dbname)
{
memset(DBIP,0,sizeof(DBIP));
strcpy(DBIP,dbip);
memset(User,0,sizeof(User));
strcpy(User,usr);
memset(Pwd,0,sizeof(Pwd));
strcpy(Pwd,pwd);
memset(DBName,0,sizeof(DBName));
strcpy(DBName,dbname);
}

//连接到数据库
int MCIDatabase::connect()
{
mysql = NULL;
mysql = mysql_init(NULL);
if (mysql == NULL)  
{
char errinfo[256];
memset(errinfo,0,sizeof(errinfo));
sprintf(errinfo, "%s\n",mysql_error(mysql));
int errno = mysql_errno(mysql);
throw MCIException(errinfo,errno);
return 0;
}
unsigned int timeout = 3;
mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT,(char *) &timeout);//超时
//mysql_options(mysql, MYSQL_OPT_COMPRESS,NULL);//与服务器的通信采用压缩协议
//建立连接
//DBIP如果设为"localhost"或NULL,则表示使用socket连接本地主机
//DBIP如果设为字符串或IP数字形式的主机名,则表示使用TCP/IP连接本地主机
if(mysql_real_connect(mysql,DBIP,User,Pwd,DBName,0,NULL,CLIENT_INTERACTIVE) == NULL)
{
char errinfo[256];
memset(errinfo,0,sizeof(errinfo));
sprintf(errinfo, "%s\n",mysql_error(mysql));
int errno = mysql_errno(mysql);
throw MCIException(errinfo,errno);
return 0;
}
return 1;
}

//关闭连接
void MCIDatabase::disConnect()
{
if (mysql != NULL) mysql_close(mysql);
}

MCIField::MCIField()
{
pParentQuery = NULL;
memset(FieldName,0,sizeof(FieldName));
memset(StrBuf,0,sizeof(StrBuf));
DataBuf = NULL;
FieldType = FIELD_TYPE_STRING;
}

MCIField::~MCIField()
{
if (DataBuf != NULL)  
{
delete[] DataBuf;
DataBuf = NULL;
}
}

MCIQuery* MCIField::getParentQuery()
{
return pParentQuery;
}

void MCIField::setParentQuery(MCIQuery* pQry)
{
pParentQuery = pQry;
}

char* MCIField::getFieldName()
{
return FieldName;
}

char* MCIField::getStrBuf()
{
return StrBuf;
}

enum_field_types MCIField::getFieldType()
{
return FieldType;
}

unsigned int MCIField::getFieldLength()
{
return FieldLength;
}

void MCIField::setFieldName(const char* s)
{
memset(FieldName,0,sizeof(FieldName));
strncpy(FieldName,s,sizeof(FieldName)-1);
}

void MCIField::setFieldType(enum_field_types n)
{
FieldType = n;
}

void MCIField::setFieldLength(unsigned int n)
{
FieldLength = n;
}

MCIQuery::MCIQuery()
{
pDB = NULL;
pRes = NULL;
FieldNum = 0;
pFields = NULL;      
    pMCIFieldList = NULL;
RowNum = 0;
memset(SqlStr,0,sizeof(SqlStr));
CurrRow = 0;

/*
//检查超时用
ThreadExist = 0;
ThreadMode = 0;
QryID = 0;
ErrNo = 0;
memset(ErrInfo,0,sizeof(ErrInfo));
*/
}

//确定Qry指向的DataBase
void MCIQuery::setDB(MCIDatabase *dblink)
{
pDB = dblink;
}

MCIQuery::~MCIQuery()
{
if(pRes != NULL)
{
mysql_free_result(pRes);
pRes = NULL;

}
if(pMCIFieldList != NULL)
{
delete[] pMCIFieldList;
pMCIFieldList = NULL;
}
pFields = NULL;
}

//设置SQL语句
void MCIQuery::setSql(char* sqlstr)
{
memset(SqlStr,0,sizeof(SqlStr));
strcpy(SqlStr,sqlstr);
}

int MCIQuery::getRecordCount()
{
return RowNum;
}

//获得字段信息,并为字段分配取值的缓冲区
int MCIQuery::getFieldsDef()
{
pRes = mysql_store_result(pDB->getMySQL()); //获取结果集
pFields =  mysql_fetch_fields(pRes);   //获取MySQL字段信息
FieldNum = mysql_num_fields(pRes);        //字段个数
if (FieldNum > 0)
{
pMCIFieldList = new MCIField[FieldNum];//建立自己的字段信息
MCIField *pCurrField = NULL;
for(int i = 0; i < FieldNum; i ++)
{
pCurrField = &pMCIFieldList;
//设置此字段名称-类型-字段宽度
pCurrField->setParentQuery(this);
pCurrField->setFieldName(pFields.name);
pCurrField->setFieldType(pFields.type);
pCurrField->setFieldLength(pFields.length);
if (pCurrField->getFieldLength() > MAX_FIELD_LEN)
       {
       //fprintf(stdout,"field:[%s]'s Length:[%d] More Than 1024\n",pCurrField->FieldName,pCurrField->FieldLength);fflush(stdout);
       pCurrField->setFieldLength(MAX_FIELD_LEN);
       }
       //建立供输出数据的缓冲区
        switch (pCurrField->getFieldType())
        {
case FIELD_TYPE_SET:
throw MCIException("Not Supported Data Type:[FIELD_TYPE_SET]",1);
break;
case FIELD_TYPE_ENUM:
throw MCIException("Not Supported Data Type:[FIELD_TYPE_ENUM]",1);
break;
case FIELD_TYPE_NULL:
throw MCIException("Not Supported Data Type:[FIELD_TYPE_NULL]",1);
break;
default:
pCurrField->DataBuf = new unsigned char[pCurrField->getFieldLength() + 1];
memset(pCurrField->DataBuf,0,sizeof(pCurrField->DataBuf));
}
}
return 1;
}
return 0;
}

//移动到下一个记录,同时获取字段值
int MCIQuery::next()
{
if (RowNum <= 0) return 0;
if (CurrRow > RowNum) return 0;
//将当前行的各个列的值写入MCIField中
Row = mysql_fetch_row(pRes);
if (Row == NULL) return 0;
for(int i = 0; i < FieldNum; i ++)
{
if( (Row == NULL) || (pMCIFieldList.DataBuf == NULL) ) continue;
memcpy(pMCIFieldList.DataBuf,Row,pMCIFieldList.getFieldLength());
}
CurrRow++;
return 1;
}

MCIField* MCIQuery::field(int i)
{
if ( (i>=0) && (i<FieldNum) )  
return &pMCIFieldList;
else  
{
char errinfo[256];
memset(errinfo,0,sizeof(errinfo));
sprintf(errinfo, "Field:[%d] Out Of Bound",i);
int errno = 2;
throw MCIException(errinfo,errno);
}
}

MCIField* MCIQuery::fieldByName(const char* s)
{
for(int i = 0;i < FieldNum;i++)
{
if (strcmp(pMCIFieldList.getFieldName(),s) == 0)
return &pMCIFieldList;
}
char errinfo[256];
memset(errinfo,0,sizeof(errinfo));
sprintf(errinfo, "Field:[%s] Not Find",s);
int errno = 3;
throw MCIException(errinfo,errno);
}

char* MCIField::allTrim(char *szString)
{
trimLeft(szString);
trimRight(szString);
return szString;
}

void MCIField::trimLeft(char *str)
{
int iStart=0;
int iLen, iCount;

iLen=strlen(str);
while( (str[iStart] == ' ') || (str[iStart] == '\t') ) iStart++;
for(iCount=iStart; iCount<=iLen; iCount++)
{
str[iCount-iStart]=str[iCount];
}
}

void MCIField::trimRight(char *str)
{
int len=strlen(str);
while(1)
{
if (len<=0) break;
if( (str[len-1]==' ') || (str[len-1]=='\t') )
{
            str[len-1]=0;
            len--;
        }
else
    break;
}
}

char* MCIField::asString()
{
static  char  nullstr[] = "";
char* p = (char *)DataBuf;
if ( (p == NULL) || (strlen(p) == 0) )
{
return nullstr;
}
else
return allTrim(p);
}
int MCIField::asInteger()
{
return(atoi((char *)DataBuf));
}
float MCIField::asFloat()
{
return(atof((char *)DataBuf));
}

char MCIField::asChar(int pos)
{
return DataBuf[pos];
}


void MCIQuery::close()
{
memset(SqlStr,0,sizeof(SqlStr));
if( (RowNum >0) && (pRes != NULL) )
{
mysql_free_result(pRes);
pRes = NULL;
}
if (FieldNum >0)
{
for(int i = 0; i < FieldNum; i ++)
{
MCIField *pCurrField = &pMCIFieldList;
delete[] pCurrField->DataBuf;
pCurrField->DataBuf = NULL;
}
}
if (pMCIFieldList != NULL)
{
delete[] pMCIFieldList;
pMCIFieldList = NULL;
}
if (pFields != NULL)
{
//delete pFields;
pFields = NULL;
}
FieldNum = 0;
RowNum = 0;
CurrRow = 0;
}

//执行需要返回结果集的SQL语句
void MCIQuery::open()
{
if (pDB->getMySQL() == NULL) return;
/*
//fprintf(stdout,"mysql_ping\n");fflush(stdout);
if (mysql_ping(pDB->getMySQL()) != 0)
{
//fprintf(stdout,"mysql_ping failure\n");fflush(stdout);
int ErrNo = mysql_errno(pDB->getMySQL());
char ErrInfo[256];
memset(ErrInfo,0,sizeof(ErrInfo));
sprintf(ErrInfo, "%s\n",mysql_error(pDB->getMySQL()));
throw MCIException(ErrInfo,ErrNo);
}
*/
if (mysql_real_query(pDB->getMySQL(),SqlStr, strlen(SqlStr)) != 0)
{
int ErrNo = mysql_errno(pDB->getMySQL());
char ErrInfo[256];
memset(ErrInfo,0,sizeof(ErrInfo));
sprintf(ErrInfo, "%s\n",mysql_error(pDB->getMySQL()));
throw MCIException(ErrInfo,ErrNo);
}

if (getFieldsDef() == 1)
{
RowNum = mysql_num_rows(pRes);
CurrRow = 0;
}
}


int MCIQuery::exec()
{
if (pDB->getMySQL() == NULL) return 0;
/*
//fprintf(stdout,"mysql_ping\n");fflush(stdout);
if (mysql_ping(pDB->getMySQL()) != 0)
{
//fprintf(stdout,"mysql_ping failure\n");fflush(stdout);
int ErrNo = mysql_errno(pDB->getMySQL());
char ErrInfo[256];
memset(ErrInfo,0,sizeof(ErrInfo));
sprintf(ErrInfo, "%s\n",mysql_error(pDB->getMySQL()));
throw MCIException(ErrInfo,ErrNo);
}
*/
//fprintf(stdout,"mysql_real_query\n");fflush(stdout);
if (mysql_real_query(pDB->getMySQL(),SqlStr, strlen(SqlStr)) != 0)
{
//fprintf(stdout,"mysql_real_query failure\n");fflush(stdout);
int ErrNo = mysql_errno(pDB->getMySQL());
char ErrInfo[256];
memset(ErrInfo,0,sizeof(ErrInfo));
sprintf(ErrInfo, "%s\n",mysql_error(pDB->getMySQL()));
throw MCIException(ErrInfo,ErrNo);
}
//fprintf(stdout,"mysql_real_query success\n");fflush(stdout);
return mysql_affected_rows(pDB->getMySQL());
}
[test.h]//呵呵,test.h其实什么东西都没有，只是一个架子
#ifndef  _TEST_H_
#define  _TEST_H_
#include "mci.h"
#endif
[test.cpp]
#include "test.h"

MCIDatabase   MCIDB;

int main(int argc,char* argv[])
{
char SqlStr[256];
try                                 
{                 
//连接数据库                  
MCIDB.disConnect();            
MCIDB.setLogin("192.168.0.111", "sa", "abc","atcdb");
MCIDB.connect();
fprintf(stdout,"connect db success\n");fflush(stdout);
//创建一个Query
MCIQuery* q = new MCIQuery();
q->setDB(&MCIDB);
//执行select语句
memset(SqlStr,0,sizeof(SqlStr));
strcpy(SqlStr,"select * from table1");//假设有表table1
q->close();
q->setSql(SqlStr);
q->open();
while(q->next())
{
fprintf(stdout,"%s\n",q->fieldByName("field1")->asString());fflush(stdout);//假设该表有varchar2型字段field1
}
//执行update或insert语句
q->close();
sprintf(sql, "delete from table1");
q->setSql(sql);
q->exec();
q->close();
//释放内存
delete q;
MCIDB.disConnect();
}
catch(MCIException &oe)            
{                  
fprintf(stdout,"%s\n",oe.getErrInfo());
MCIDB.disConnect();
delete q;
}
}
[/CODE]
[makefile]
CC  = g++
CFLAGS  = -Wall
SQLHOME  = -L/usr/lib64 -lmysqlclient

all:test
test: mci.o test.o
$(CC) -s -o test -m64 *.o  $(SQLHOME)

mci.o: mci.cpp mci.h
$(CC) $(CFLAGS) -c mci.cpp
test.o: test.cpp test.h
$(CC) $(CFLAGS) -c test.cpp

clean::
rm -f *.o