[转载]Linux(Unix)下MySQL数据库访问接口程序MCI
<p>信息来源: CU</p><p>[color=Red]<b>郑重声明：本MCI程序已经过严格的，长时间的反复测试和使用！ <br />可支持对char,varchar,varchar2,int,float等类型数据进行各种select,update,insert操作 <br />欢迎C++高手,MySQL来指点</b>[/color] <br />本接口程序用C++完成，尤其适合server端使用，可通行于Linux/Unix/Windows平台 <br />包括:mci.hmci.cpp以及测试用例test.htest.cpp和一个makefile文件,可直接在RedHat上编译运行 <br />本接口功能相信可满足大部分朋友的需要,你只需在你的程序里面包含这两个文件即可 <br />(当然相应的makefile文件也需修改) <br />作者：欧昕中国-成都欢迎大家改进之，让其功能更加强大，我的联系QQ：30991118，欢迎骚扰...:) <br />[color=Red]<b>[mci.h]:</b>[/color] <br />[CODE] <br />#ifndef_MCI_H_ <br />#define_MCI_H_ <br />#include<iostream> <br />#include<pthread.h> <br />#include<time.h> <br />#include<stdlib.h> <br />#include<stdio.h> <br />#include<memory.h> <br />#include"/usr/local/mysql/include/mysql.h" <br />constunsignedintMAX_FIELD_LEN=1024*1; <br />classMCIException <br />{ <br />public: <br />int ErrNo; <br />char ErrInfo[256]; <br />MCIException(constchar*errinfo,interrno); <br />char*getErrInfo(); <br />intgetErrNo(){returnErrNo;}; <br />//自定义错误类型 <br />//1不支持的字段类型 <br />//2字段越界 <br />//3字段不存在 <br />//MySQL内部错误类型 <br />//2002Can'tconnecttolocalMySQLserverthroughsocket <br />//2003Can'tconnecttoMySQLserver <br />//2013LostconnectiontoMySQLserverduringquery <br />//1045Accessdeniedforuser <br />}; <br /><br />classMCIDatabase <br />{ <br />public: <br />charDBIP[20];//数据库IP地址 <br />charUser[10]; //用户名 <br />charPwd[10]; //密码 <br />charDBName[20];//数据库名 <br />MYSQL*mysql; <br />public: <br />MCIDatabase(); <br />MYSQL*getMySQL(){returnmysql;}; <br />voidsetLogin(constchar*dbip,constchar*usr,constchar*pwd,constchar*dbname); <br />intconnect(); <br />voiddisConnect(); <br />}; <br /><br />classMCIField <br />{ <br />public: <br />friendclassMCIQuery; <br />MCIQuery*pParentQuery; //指向该Field所属于的Query <br />charFieldName[30]; //字段名称(目前支持30长度) <br />char StrBuf[255]; //用于保存转换为字符串后的值 <br />unsignedchar* DataBuf; //预绑定缓冲区 <br />enum_field_typesFieldType; //MySQL内部数据类型 <br />unsignedint FieldLength; //数据长度 <br />public: <br />MCIField(); <br />~MCIField(); <br />voidsetFieldName(constchar*s); <br />voidsetFieldType(enum_field_typesn); <br />voidsetFieldLength(unsignedintn); <br /><br />char*getFieldName(); <br />char*getStrBuf(); <br />enum_field_typesgetFieldType(); <br />unsignedintgetFieldLength(); <br /><br />MCIQuery*getParentQuery(); <br />voidsetParentQuery(MCIQuery*pQry); <br />staticvoidtrimLeft(char*str); <br />staticvoidtrimRight(char*str); <br />staticchar*allTrim(char*str); <br />char*asString(); <br />intasInteger(); <br />floatasFloat(); <br />charasChar(intpos=0); <br /><br />}; <br /><br />classMCIQuery <br />{ <br />public: <br />MCIDatabase*pDB; <br />MYSQL_RES*pRes; <br /><br />intFieldNum;//字段个数 <br />MYSQL_FIELD*pFields; //得到的字段信息 <br />MCIField*pMCIFieldList;//在内部保存的所有字段信息 <br /> <br />MYSQL_ROWRow; <br />intRowNum; <br />charSqlStr[1024*3]; <br />int CurrRow; <br /><br />/* <br />//检查超时用 <br />int ThreadExist; <br />int ThreadMode; <br />pthread_tQryID; <br />intErrNo; <br />charErrInfo[256]; <br />*/ <br /><br />public: <br />MCIQuery(); <br />voidsetDB(MCIDatabase*dblink); <br />~MCIQuery(); <br />voidsetSql(char*sqlstr); <br /> voidopen();//执行select型SQL语句 <br /> intgetFieldsDef();//获得字段信息,并为字段分配取值的缓冲区 <br /> intgetRecordCount(); //返回查询到的符合条件的记录的条数 <br /> intnext(); //移动到下一个记录,同时获取字段值 <br /> MCIField*field(inti);//取相应字段值 <br /> MCIField*fieldByName(constchar*s); <br /> intexec(); //执行insert,update型SQL语句,返回被此语句影响的记录条数 <br /> voidclose(); //关闭一个Query,为下次执行做准备 <br /> <br /> /* <br /> //检查超时用 <br /> intrunSql(); <br /> staticvoid*QryThread(void*arg); <br /> */ <br />}; <br />#endif <br />[color=Red]<b>[mci.cpp]:</b>[/color] <br />#include"mci.h" <br /><br />MCIException::MCIException(constchar*errinfo,interrno) <br />{ <br />memset(ErrInfo,0,sizeof(ErrInfo)); <br />strncpy(ErrInfo,errinfo,sizeof(ErrInfo)-1); <br />ErrNo=errno; <br />} <br /><br />char*MCIException::getErrInfo() <br />{ <br />returnErrInfo; <br />} <br /><br />MCIDatabase::MCIDatabase() <br />{ <br />memset(DBName,0,sizeof(DBName)); <br />memset(User,0,sizeof(User)); <br />memset(Pwd,0,sizeof(Pwd)); <br />mysql=NULL; <br />} <br /><br />//设置登陆信息 <br />voidMCIDatabase::setLogin(constchar*dbip,constchar*usr,constchar*pwd,constchar*dbname) <br />{ <br />memset(DBIP,0,sizeof(DBIP)); <br />strcpy(DBIP,dbip); <br />memset(User,0,sizeof(User)); <br />strcpy(User,usr); <br />memset(Pwd,0,sizeof(Pwd)); <br />strcpy(Pwd,pwd); <br />memset(DBName,0,sizeof(DBName)); <br />strcpy(DBName,dbname); <br />} <br /><br />//连接到数据库 <br />intMCIDatabase::connect() <br />{ <br />mysql=NULL; <br />mysql=mysql_init(NULL); <br />if(mysql==NULL) <br />{ <br />charerrinfo[256]; <br />memset(errinfo,0,sizeof(errinfo)); <br />sprintf(errinfo,"%s\n",mysql_error(mysql)); <br />interrno=mysql_errno(mysql); <br />throwMCIException(errinfo,errno); <br />return0; <br />} <br />unsignedinttimeout=3; <br />mysql_options(mysql,MYSQL_OPT_CONNECT_TIMEOUT,(char*)&timeout);//超时 <br />//mysql_options(mysql,MYSQL_OPT_COMPRESS,NULL);//与服务器的通信采用压缩协议 <br />//建立连接 <br />//DBIP如果设为"localhost"或NULL,则表示使用socket连接本地主机 <br />//DBIP如果设为字符串或IP数字形式的主机名,则表示使用TCP/IP连接本地主机 <br />if(mysql_real_connect(mysql,DBIP,User,Pwd,DBName,0,NULL,CLIENT_INTERACTIVE)==NULL) <br />{ <br />charerrinfo[256]; <br />memset(errinfo,0,sizeof(errinfo)); <br />sprintf(errinfo,"%s\n",mysql_error(mysql)); <br />interrno=mysql_errno(mysql); <br />throwMCIException(errinfo,errno); <br />return0; <br />} <br />return1; <br />} <br /><br />//关闭连接 <br />voidMCIDatabase::disConnect() <br />{ <br />if(mysql!=NULL)mysql_close(mysql); <br />} <br /><br />MCIField::MCIField() <br />{ <br />pParentQuery=NULL; <br />memset(FieldName,0,sizeof(FieldName)); <br />memset(StrBuf,0,sizeof(StrBuf)); <br />DataBuf=NULL; <br />FieldType=FIELD_TYPE_STRING; <br />} <br /><br />MCIField::~MCIField() <br />{ <br />if(DataBuf!=NULL) <br />{ <br />delete[]DataBuf; <br />DataBuf=NULL; <br />} <br />} <br /><br />MCIQuery*MCIField::getParentQuery() <br />{ <br />returnpParentQuery; <br />} <br /><br />voidMCIField::setParentQuery(MCIQuery*pQry) <br />{ <br />pParentQuery=pQry; <br />} <br /><br />char*MCIField::getFieldName() <br />{ <br />returnFieldName; <br />} <br /><br />char*MCIField::getStrBuf() <br />{ <br />returnStrBuf; <br />} <br /><br />enum_field_typesMCIField::getFieldType() <br />{ <br />returnFieldType; <br />} <br /><br />unsignedintMCIField::getFieldLength() <br />{ <br />returnFieldLength; <br />} <br /><br />voidMCIField::setFieldName(constchar*s) <br />{ <br />memset(FieldName,0,sizeof(FieldName)); <br />strncpy(FieldName,s,sizeof(FieldName)-1); <br />} <br /><br />voidMCIField::setFieldType(enum_field_typesn) <br />{ <br />FieldType=n; <br />} <br /><br />voidMCIField::setFieldLength(unsignedintn) <br />{ <br />FieldLength=n; <br />} <br /><br />MCIQuery::MCIQuery() <br />{ <br />pDB=NULL; <br />pRes=NULL; <br />FieldNum=0; <br />pFields=NULL; <br />pMCIFieldList=NULL; <br />RowNum=0; <br />memset(SqlStr,0,sizeof(SqlStr)); <br />CurrRow=0; <br /><br />/* <br />//检查超时用 <br />ThreadExist=0; <br />ThreadMode=0; <br />QryID=0; <br />ErrNo=0; <br />memset(ErrInfo,0,sizeof(ErrInfo)); <br />*/ <br />} <br /><br />//确定Qry指向的DataBase <br />voidMCIQuery::setDB(MCIDatabase*dblink) <br />{ <br />pDB=dblink; <br />} <br /><br />MCIQuery::~MCIQuery() <br />{ <br />if(pRes!=NULL) <br />{ <br />mysql_free_result(pRes); <br />pRes=NULL; <br /><br />} <br />if(pMCIFieldList!=NULL) <br />{ <br />delete[]pMCIFieldList; <br />pMCIFieldList=NULL; <br />} <br />pFields=NULL; <br />} <br /><br />//设置SQL语句 <br />voidMCIQuery::setSql(char*sqlstr) <br />{ <br />memset(SqlStr,0,sizeof(SqlStr)); <br />strcpy(SqlStr,sqlstr); <br />} <br /><br />intMCIQuery::getRecordCount() <br />{ <br />returnRowNum; <br />} <br /><br />//获得字段信息,并为字段分配取值的缓冲区 <br />intMCIQuery::getFieldsDef() <br />{ <br />pRes=mysql_store_result(pDB->getMySQL());//获取结果集 <br />pFields=mysql_fetch_fields(pRes); //获取MySQL字段信息 <br />FieldNum=mysql_num_fields(pRes); //字段个数 <br />if(FieldNum>0) <br />{ <br />pMCIFieldList=newMCIField[FieldNum];//建立自己的字段信息 <br />MCIField*pCurrField=NULL; <br />for(inti=0;i<FieldNum;i++) <br />{ <br />pCurrField=&pMCIFieldList<u>; <br />//设置此字段名称-类型-字段宽度 <br />pCurrField->setParentQuery(this); <br />pCurrField->setFieldName(pFields<u>.name); <br />pCurrField->setFieldType(pFields<u>.type); <br />pCurrField->setFieldLength(pFields<u>.length); <br />if(pCurrField->getFieldLength()>MAX_FIELD_LEN) <br /> { <br /> //fprintf(stdout,"field:[%s]'sLength:[%d]MoreThan1024\n",pCurrField->FieldName,pCurrField->FieldLength);fflush(stdout); <br /> pCurrField->setFieldLength(MAX_FIELD_LEN); <br /> } <br /> //建立供输出数据的缓冲区 <br />switch(pCurrField->getFieldType()) <br />{ <br />caseFIELD_TYPE_SET: <br />throwMCIException("NotSupportedDataType:[FIELD_TYPE_SET]",1); <br />break; <br />caseFIELD_TYPE_ENUM: <br />throwMCIException("NotSupportedDataType:[FIELD_TYPE_ENUM]",1); <br />break; <br />caseFIELD_TYPE_NULL: <br />throwMCIException("NotSupportedDataType:[FIELD_TYPE_NULL]",1); <br />break; <br />default: <br />pCurrField->DataBuf=newunsignedchar[pCurrField->getFieldLength()+1]; <br />memset(pCurrField->DataBuf,0,sizeof(pCurrField->DataBuf)); <br />} <br />} <br />return1; <br />} <br />return0; <br />} <br /><br />//移动到下一个记录,同时获取字段值 <br />intMCIQuery::next() <br />{ <br />if(RowNum<=0)return0; <br />if(CurrRow>RowNum)return0; <br />//将当前行的各个列的值写入MCIField中 <br />Row=mysql_fetch_row(pRes); <br />if(Row==NULL)return0; <br />for(inti=0;i<FieldNum;i++) <br />{ <br />if((Row<u>==NULL)||(pMCIFieldList<u>.DataBuf==NULL))continue; <br />memcpy(pMCIFieldList<u>.DataBuf,Row<u>,pMCIFieldList<u>.getFieldLength()); <br />} <br />CurrRow++; <br />return1; <br />} <br /><br />MCIField*MCIQuery::field(inti) <br />{ <br />if((i>=0)&&(i<FieldNum)) <br />return&pMCIFieldList<u>; <br />else <br />{ <br />charerrinfo[256]; <br />memset(errinfo,0,sizeof(errinfo)); <br />sprintf(errinfo,"Field:[%d]OutOfBound",i); <br />interrno=2; <br />throwMCIException(errinfo,errno); <br />} <br />} <br /><br />MCIField*MCIQuery::fieldByName(constchar*s) <br />{ <br />for(inti=0;i<FieldNum;i++) <br />{ <br />if(strcmp(pMCIFieldList<u>.getFieldName(),s)==0) <br />return&pMCIFieldList<u>; <br />} <br />charerrinfo[256]; <br />memset(errinfo,0,sizeof(errinfo)); <br />sprintf(errinfo,"Field:[%s]NotFind",s); <br />interrno=3; <br />throwMCIException(errinfo,errno); <br />} <br /><br />char*MCIField::allTrim(char*szString) <br />{ <br />trimLeft(szString); <br />trimRight(szString); <br />returnszString; <br />} <br /><br />voidMCIField::trimLeft(char*str) <br />{ <br />intiStart=0; <br />intiLen,iCount; <br /><br />iLen=strlen(str); <br />while((str[iStart]=='')||(str[iStart]=='\t'))iStart++; <br />for(iCount=iStart;iCount<=iLen;iCount++) <br />{ <br />str[iCount-iStart]=str[iCount]; <br />} <br />} <br /><br />voidMCIField::trimRight(char*str) <br />{ <br />intlen=strlen(str); <br />while(1) <br />{ <br />if(len<=0)break; <br />if((str[len-1]=='')||(str[len-1]=='\t')) <br />{ <br />str[len-1]=0; <br />len--; <br />} <br />else <br />break; <br />} <br />} <br /><br />char*MCIField::asString() <br />{ <br />staticcharnullstr[]=""; <br />char*p=(char*)DataBuf; <br />if((p==NULL)||(strlen(p)==0)) <br />{ <br />returnnullstr; <br />} <br />else <br />returnallTrim(p); <br />} <br />intMCIField::asInteger() <br />{ <br />return(atoi((char*)DataBuf)); <br />} <br />floatMCIField::asFloat() <br />{ <br />return(atof((char*)DataBuf)); <br />} <br /><br />charMCIField::asChar(intpos) <br />{ <br />returnDataBuf[pos]; <br />} <br /><br /><br />voidMCIQuery::close() <br />{ <br />memset(SqlStr,0,sizeof(SqlStr)); <br />if((RowNum>0)&&(pRes!=NULL)) <br />{ <br />mysql_free_result(pRes); <br />pRes=NULL; <br />} <br />if(FieldNum>0) <br />{ <br />for(inti=0;i<FieldNum;i++) <br />{ <br />MCIField*pCurrField=&pMCIFieldList<u>; <br />delete[]pCurrField->DataBuf; <br />pCurrField->DataBuf=NULL; <br />} <br />} <br />if(pMCIFieldList!=NULL) <br />{ <br />delete[]pMCIFieldList; <br />pMCIFieldList=NULL; <br />} <br />if(pFields!=NULL) <br />{ <br />//deletepFields; <br />pFields=NULL; <br />} <br />FieldNum=0; <br />RowNum=0; <br />CurrRow=0; <br />} <br /><br />//执行需要返回结果集的SQL语句 <br />voidMCIQuery::open() <br />{ <br />if(pDB->getMySQL()==NULL)return; <br />/* <br />//fprintf(stdout,"mysql_ping\n");fflush(stdout); <br />if(mysql_ping(pDB->getMySQL())!=0) <br />{ <br />//fprintf(stdout,"mysql_pingfailure\n");fflush(stdout); <br />intErrNo=mysql_errno(pDB->getMySQL()); <br />charErrInfo[256]; <br />memset(ErrInfo,0,sizeof(ErrInfo)); <br />sprintf(ErrInfo,"%s\n",mysql_error(pDB->getMySQL())); <br />throwMCIException(ErrInfo,ErrNo); <br />} <br />*/ <br />if(mysql_real_query(pDB->getMySQL(),SqlStr,strlen(SqlStr))!=0) <br />{ <br />intErrNo=mysql_errno(pDB->getMySQL()); <br />charErrInfo[256]; <br />memset(ErrInfo,0,sizeof(ErrInfo)); <br />sprintf(ErrInfo,"%s\n",mysql_error(pDB->getMySQL())); <br />throwMCIException(ErrInfo,ErrNo); <br />} <br /><br />if(getFieldsDef()==1) <br />{ <br />RowNum=mysql_num_rows(pRes); <br />CurrRow=0; <br />} <br />} <br /><br /><br />intMCIQuery::exec() <br />{ <br />if(pDB->getMySQL()==NULL)return0; <br />/* <br />//fprintf(stdout,"mysql_ping\n");fflush(stdout); <br />if(mysql_ping(pDB->getMySQL())!=0) <br />{ <br />//fprintf(stdout,"mysql_pingfailure\n");fflush(stdout); <br />intErrNo=mysql_errno(pDB->getMySQL()); <br />charErrInfo[256]; <br />memset(ErrInfo,0,sizeof(ErrInfo)); <br />sprintf(ErrInfo,"%s\n",mysql_error(pDB->getMySQL())); <br />throwMCIException(ErrInfo,ErrNo); <br />} <br />*/ <br />//fprintf(stdout,"mysql_real_query\n");fflush(stdout); <br />if(mysql_real_query(pDB->getMySQL(),SqlStr,strlen(SqlStr))!=0) <br />{ <br />//fprintf(stdout,"mysql_real_queryfailure\n");fflush(stdout); <br />intErrNo=mysql_errno(pDB->getMySQL()); <br />charErrInfo[256]; <br />memset(ErrInfo,0,sizeof(ErrInfo)); <br />sprintf(ErrInfo,"%s\n",mysql_error(pDB->getMySQL())); <br />throwMCIException(ErrInfo,ErrNo); <br />} <br />//fprintf(stdout,"mysql_real_querysuccess\n");fflush(stdout); <br />returnmysql_affected_rows(pDB->getMySQL()); <br />} <br />[color=Red]<b>[test.h]</b>[/color]//呵呵,test.h其实什么东西都没有，只是一个架子 <br />#ifndef_TEST_H_ <br />#define_TEST_H_ <br />#include"mci.h" <br />#endif <br />[color=Red]<b>[test.cpp]</b>[/color] <br />#include"test.h" <br /><br />MCIDatabaseMCIDB; <br /><br />intmain(intargc,char*argv[]) <br />{ <br />charSqlStr[256]; <br />try <br />{ <br />//连接数据库 <br />MCIDB.disConnect(); <br />MCIDB.setLogin("192.168.0.111","sa","abc","atcdb"); <br />MCIDB.connect(); <br />fprintf(stdout,"connectdbsuccess\n");fflush(stdout); <br />//创建一个Query <br />MCIQuery*q=newMCIQuery(); <br />q->setDB(&MCIDB); <br />//执行select语句 <br />memset(SqlStr,0,sizeof(SqlStr)); <br />strcpy(SqlStr,"select*fromtable1");//假设有表table1 <br />q->close(); <br />q->setSql(SqlStr); <br />q->open(); <br />while(q->next()) <br />{ <br />fprintf(stdout,"%s\n",q->fieldByName("field1")->asString());fflush(stdout);//假设该表有varchar2型字段field1 <br />} <br />//执行update或insert语句 <br />q->close(); <br />sprintf(sql,"deletefromtable1"); <br />q->setSql(sql); <br />q->exec(); <br />q->close(); <br />//释放内存 <br />deleteq; <br />MCIDB.disConnect(); <br />} <br />catch(MCIException&oe) <br />{ <br />fprintf(stdout,"%s\n",oe.getErrInfo()); <br />MCIDB.disConnect(); <br />deleteq; <br />} <br />} <br />[/CODE] <br />[color=Red]<b>[makefile]</b>[/color] <br />CC =g++ <br />CFLAGS =-Wall <br />SQLHOME =-L/usr/lib64-lmysqlclient <br /><br />all:test <br />test:mci.otest.o <br />$(CC)-s-otest-m64*.o$(SQLHOME) <br /><br />mci.o:mci.cppmci.h <br />$(CC)$(CFLAGS)-cmci.cpp <br />test.o:test.cpptest.h <br />$(CC)$(CFLAGS)-ctest.cpp <br /><br />clean:: <br />rm-f*.o<br /></u></u></u></u></u></u></u></u></u></u></u></u></u></p>页:
[1]