MySQL Call Interface - Linux/Unix下MySQL数据库访问接口程序

 MySQL Call Interface

 Linux/Unix下的MySQL数据库访问接口程序

很好！很强大！充分满足你绝大部分需要！

本接口程序用C++完成，通行于Linux/Unix平台，尤其适合服务器端使用。

包括:mci.h mci.cpp test.h test.cpp makefile 共4个文件(完整代码),

建议把所有代码copy到 Ultra Edit里面去看，并且把tab设置为4，这样就很工整！

有一定基础的开发人员绝对可看懂。

版权声明：如在别的任何网站有看到该文章，如果未注明作者为欧昕，均为抄袭。

更新说明:

[2008-09-11 更新，纠正了next() 函数中 (Row[i] == NULL) 可能导致异常退出的问题]

 

【mci.h】

#ifndef _MCI_H_
#define _MCI_H_
#include <iostream>
#include <pthread.h>
#include <time.h>
#include <stdlib.h>
#include <stdio.h>
#include <list>
#include <memory.h>
#include "mysql.h"

const unsigned int MAX_FIELD_LEN = 512;   //一个字段值允许的最大长度
const unsigned int MAX_FIELD_NUM = 256;   //允许字段数的最大值
const unsigned int MAX_SQL_LEN = 1024*5; //支持sql语句的最大长度

class MCIException
{
public:
    int ErrNo;
    //自定义错误类型
//1    不支持的字段类型
//2    字段越界
//3    字段不存在
//MySQL错误类型
//2002 Can't connect to local MySQL server through socket
//2003 Can't connect to MySQL server
//2006 MySQL server has gone away
//2008 MySQL client ran out of memory
//2013 Lost connection to MySQL server during query
//1045 Access denied for user
char ErrInfo[256];
public:
   MCIException(const char *errinfo,int errno);
   char *getErrInfo();
   int   getErrNo(){return ErrNo;};
};

class MCIDatabase
{
public:
MYSQL* mysql;
int Valid;
char DBIP[20];
char User[10];
char Pwd[10];
char DBName[20];
public:
   MCIDatabase();
   ~MCIDatabase(){};
   void setLogin(const char* dbip,const char* usr, const char* pwd, const char* dbname) ;
   int connect();
   void disConnect();
};

class MCIField
{
public:
char             FieldName[30];
enum_field_types FieldType;    //MySQL内部数据类型
unsigned int FieldLength;   //数据长度
char    DataBuf[MAX_FIELD_LEN+1]; //数据存储区
public:
   MCIField();
   void clearInfo();
   void setFieldName(const char* s);
   void setFieldType(enum_field_types n);
   void setFieldLength(unsigned int n);
   enum_field_types getFieldType();
   char*        asString();
   int      asInt();
   float        asFloat();
   char         asChar(int pos = 0);
  
};

class MCIQuery
{
public:
MCIDatabase* pDB;
MYSQL_RES*   pRes;
MYSQL_FIELD* pFields;
unsigned int FieldNum;
MYSQL_ROW    Row;
int          RowNum;
char         SqlStr[MAX_SQL_LEN];
int       CurrRow;
MCIField FieldList[MAX_FIELD_NUM];
public:
   MCIQuery();
   ~MCIQuery();
   void clearInfo();
   void setDB(MCIDatabase *db);
   void setSql(const char* s);
    void open();
    int   getFieldsDef();
    int   getRecordCount();
    int   next();
    MCIField* field(unsigned int i);
    MCIField* fieldByName(const char* s);
    void exec();
    void close();
};

#endif
【mci.cpp】

#include "mci.h"
#include "fdp_kernal.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()
{
Valid = 0;
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()
{
Valid = 0;
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 = 5;//如果发现连接数据库缓慢,应在/etc/my.cnf中mysqld组中添加skip-name-resolve选项
mysql_options(mysql, MYSQL_OPT_CONNECT_TIMEOUT,(char*)&timeout);
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;
}
Valid = 1;
return 1;
}

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

MCIField::MCIField()
{
clearInfo();
}

void MCIField::clearInfo()
{
memset(FieldName,0,sizeof(FieldName));
memset(DataBuf,0,sizeof(DataBuf));
FieldType = FIELD_TYPE_STRING;
FieldLength = 0;
}

enum_field_types MCIField::getFieldType()
{
return FieldType;
}
  
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()
{
clearInfo();
}

void MCIQuery::clearInfo()
{
pDB = NULL;
pRes = NULL;
FieldNum = 0;
pFields = NULL;
RowNum = 0;
memset(SqlStr,0,sizeof(SqlStr));
CurrRow = 0;
for (unsigned int i=0;i<MAX_FIELD_NUM-1;i++) FieldList[i].clearInfo();
}

void MCIQuery::setDB(MCIDatabase *db)
{
pDB = db;
}

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

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

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

int MCIQuery::getFieldsDef()
{
pRes = mysql_store_result(pDB->mysql);
pFields = mysql_fetch_fields(pRes);
FieldNum = mysql_num_fields(pRes);
if (FieldNum > 0)
{
   for(unsigned int i = 0; i < FieldNum; i++)
   {
    if (i >= MAX_FIELD_NUM) break;
    FieldList[i].clearInfo();
    FieldList[i].setFieldName(pFields[i].name);
    FieldList[i].setFieldType(pFields[i].type);
    if (pFields[i].length > MAX_FIELD_LEN)
     FieldList[i].setFieldLength(MAX_FIELD_LEN);
    else
     FieldList[i].setFieldLength(pFields[i].length);
    //PT::logInfo(1,"Field Name:[%s] Type:[%d] Length:[%d]",FieldList[i].FieldName,FieldList[i].FieldType,FieldList[i].FieldLength);
   }
   return 1;
}
return 0;
}

//移动到下一个记录,同时获取字段值
int MCIQuery::next()
{
if (RowNum <= 0) return 0;
if (CurrRow > RowNum) return 0;
Row = mysql_fetch_row(pRes);
if (Row == NULL) return 0;
for(unsigned int i = 0; i < FieldNum; i++)
{
   if (i >= MAX_FIELD_NUM) break;
   memset(FieldList[i].DataBuf,0,sizeof(FieldList[i].DataBuf));
   if (Row[i] == NULL) continue;
   unsigned int len = strlen(Row[i]);
   //PT::logInfo(1,"[%s] == [%s] L1:[%d] L2:[%d]",FieldList[i].FieldName,Row[i],FieldList[i].FieldLength,len);
   if (len <= 0) continue;
   if (len > FieldList[i].FieldLength) len = FieldList[i].FieldLength;
   memcpy(FieldList[i].DataBuf,Row[i],len);
   FieldList[i].DataBuf[len] = 0;
   PT::allTrim(FieldList[i].DataBuf);
   //PT::logInfo(1,"DataBuf == [%s]",FieldList[i].DataBuf);
}
CurrRow++;
return 1;
}

MCIField* MCIQuery::field(unsigned int i)
{
if ((0 <= i) && (i < FieldNum) && (i < MAX_FIELD_NUM))
   return &FieldList[i];
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(unsigned int i = 0;i < FieldNum;i++)
{
   if (strcmp(FieldList[i].FieldName,s) == 0) return &FieldList[i];
}
char errinfo[256];
memset(errinfo,0,sizeof(errinfo));
sprintf(errinfo, "Field:[%s] Not Find",s);
int errno = 3;
throw MCIException(errinfo,errno);
}

char* MCIField::asString()
{
static char nullstr[] = "";
if (strlen(DataBuf) <= 0) return nullstr;
static char rstr[MAX_FIELD_LEN+1];
strcpy(rstr,DataBuf);
return rstr;
}

int MCIField::asInt()
{
return(atoi(DataBuf));
}

float MCIField::asFloat()
{
return(atof(DataBuf));
}

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

void MCIQuery::close()
{
if (pRes != NULL)
{
   mysql_free_result(pRes);
   pRes = NULL;
}
pFields = NULL;
memset(SqlStr,0,sizeof(SqlStr));
FieldNum = 0;
RowNum = 0;
CurrRow = 0;
for (unsigned int i=0;i<MAX_FIELD_NUM-1;i++) FieldList[i].clearInfo();
}

void MCIQuery::open()
{
if (pDB->Valid == 0) return;
if (pDB->mysql == NULL) return;
if (mysql_real_query(pDB->mysql,SqlStr, strlen(SqlStr)) != 0)
{
   int ErrNo = mysql_errno(pDB->mysql);
   char ErrInfo[256];
   memset(ErrInfo,0,sizeof(ErrInfo));
   sprintf(ErrInfo, "%s/n",mysql_error(pDB->mysql));
   throw MCIException(ErrInfo,ErrNo);
   return;
}
if (getFieldsDef() == 1)
{
   RowNum = mysql_num_rows(pRes);
   CurrRow = 0;
}
}

void MCIQuery::exec()
{
if (pDB->Valid == 0) return;
if (pDB->mysql == NULL) return;
if (mysql_real_query(pDB->mysql,SqlStr, strlen(SqlStr)) != 0)
{
   int ErrNo = mysql_errno(pDB->mysql);
   char ErrInfo[256];
   memset(ErrInfo,0,sizeof(ErrInfo));
   sprintf(ErrInfo, "%s/n",mysql_error(pDB->mysql));
   throw MCIException(ErrInfo,ErrNo);
   return;
}
//return mysql_affected_rows(pDB->mysql);
}
【test.h】

#ifndef _TEST_H_
#define _TEST_H_

#include <iostream>
#include <pthread.h>
#include <sched.h>
#include <assert.h>

using namespace std;
#include "mci.h"

#endif

【test.cpp】

#include "test.h"

MCIDatabase   MCIDB;

int main(int argc,char* argv[])
{

MCIQuery* q = new MCIQuery();
char SqlStr[256];
try                               
    {                     
    //断开已有的连接           
    MCIDB.disConnect();
    //设置登陆信息,假设存在数据库db1,IP地址为192.168.0.100,用户名sa,密码abc
    //请修改为你自己的数据库
        MCIDB.setLogin("192.168.0.111", "fdp", "abc","atcdb");
        //连接数据库
        MCIDB.connect();
    fprintf(stdout,"connect db success/n");fflush(stdout);
    //为Query设置其连接的数据库
   q->setDB(&MCIDB);

   //演示select功能
   //初始化一个Query
   q->close();
   //设置sql语句
   memset(SqlStr,0,sizeof(SqlStr));
   strcpy(SqlStr,"select * from atcdb.tab_rpl");
        q->setSql(SqlStr);
        //执行select型sql语句-open
        q->open();
        //遍历结果集
        while(q->next())
   {
    fprintf(stdout,"%s/n",q->fieldByName("flight")->asString());
      }
  
   /*
   //演示update功能
   q->close();
   memset(SqlStr,0,sizeof(SqlStr));
   strcpy(SqlStr,"update db1.table1 set field1 = 1");
   q->setSql(SqlStr);
   q->exec();
  
   //演示delete功能
   q->close();
   memset(SqlStr,0,sizeof(SqlStr));
   strcpy(SqlStr,"delete from db1.table1");
   q->setSql(SqlStr);
   q->exec();
  
   */
   //收尾工作,释放资源
   q->close();
   delete q;
        MCIDB.disConnect();
}
   catch(MCIException &oe)           
    {                 
    fprintf(stdout,"%s/n",oe.getErrInfo());
    MCIDB.disConnect();
    delete q;
    }

}

【makefile】

CC     = g++
CFLAGS    = -Wall -ansi -O3
MYSQLINCLUDE = -I /usr/include
MYSQLLIBS = -L/usr/lib -lmysqlclient

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

all:libmci.a
libmci.a: mci.o
ar rc libmci.a mci.o

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

install:
rm -f /home/cdatc/AirNet.1/bin/libmci.a
cp libmci.a /home/cdatc/AirNet.1/bin

clean::
rm -f *.o *.a