cppodbc--c++的odbc封装类

    近日闲暇时研究了一下linux下的开源项目unixodbc，使用起来很是方便。现在总结一下，以飨读者。

    关于ODBC的介绍，在网上找了一段比较经典的解释：ODBC 是Open Database Connect 即开放数据库互连的简称，它是由Microsoft 公司于1991 年提出的一个用于访问数据库的统一界面标准，是应用程序和数据库系统之间的中间件。它通过使用相应应用平台上和所需数据库对应的驱动程序与应用程序的交互来实现对数据库的操作，避免了在应用程序中直接调用与数据库相关的操作，从而提供了数据库的独立性。 

    ODBC 主要由驱动程序和驱动程序管理器组成。驱动程序是一个用以支持ODBC 函数调用的模块，每个驱动程序对应于相应的数据库，当应用程序从基于一个数据库系统移植到另一个时，只需更改应用程序中由ODBC 管理程序设定的与相应数据库系统对应的别名即可。驱动程序管理器可链接到所有ODBC 应用程序中，它负责管理应用程序中ODBC 函数与DLL 中函数的绑定。

    ODBC 使用层次的方法来管理数据库，在数据库通信结构的每一层，对可能出现依赖数据库产品自身特性的地方，ODBC 都引入一个公共接口以解决潜在的不一致性，从而很好地解决了基于数据库系统应用程序的相对独立性，这也是ODBC 一经推出就获得巨大成功的重要原因之一。

    unix下著名的ODBC项目有unixodbc和iodbc，其中iodbc最近有所更新，但是笔者尝试安装没有成功。而且在Google上搜索这两个关键字，unixodbc远高于iodbc，所以笔者肤浅的认为unixodbc要比iodbc更加受欢迎。

unixodbc的官方网站是，目前最新的版本是2.2.14, 地址是：。

安装步骤：

tar zxvf unixODBC-2.2.14.tar.gz
cd unixODBC-2.2.14
./configure --prefix=/usr/local/unixODBC-2.2.14 --includedir=/usr/include --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etc
make
make install

 

这时，unixodbc已经安装完毕，这时候可以使用odbc的通用api进行编程了。

但是如果要操作具体的数据库，还需要相应数据库提供的odbc驱动，比如操作mysql的ODBC驱动，下载地址是：http://dev.mysql.com/downloads/connector/odbc/5.1.html。

安装步骤：

tar zxvf mysql-5.1.38-linux-i686-icc-glibc23.tar.gz

cd mysql-connector-odbc-5.1.5-linux-x86-32bit

cp lib/* /usr/lib //把该目录下所有的文件拷贝到系统库目录下

./bin/ myodbc-installer //会显示怎么建立数据源

 

根据提示建立数据源，会在/etc/下产生两个文件odbc.ini  odbcinst.ini，

其中 odbc.ini内容如下：

[test]

Driver          = /usr/lib/libmyodbc5.so

SERVER          = localhost

UID             = root

PWD             = root

DATABASE                = mysql

PORT            = 3306

 

odbcinst.ini内容如下：

[MySQL ODBC 5.1 Driver]

Driver          = /usr/lib/myodbc5.so

SETUP           = /usr/lib/myodbc3S.so

UsageCount     = 1

 

到此，数据源就建立了，通过下面的cppodbc封装类，就可以对mysql数据库进行操作了。

 

//.h

////////////////////////////////////////////////////////////////////////////////

// CppMysql - A C++ wrapper around the odbc interface library.

//

// Copyright (c) 2009 Rob Groves. All Rights Reserved. [e-mail]

//

// Permission to use, copy, modify, and distribute this software and its

// documentation for any purpose, without fee, and without a written

// agreement, is hereby granted, provided that the above copyright notice,

// this paragraph and the following two paragraphs appear in all copies,

// modifications, and distributions.

//

// IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,

// INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST

// PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION,

// EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

//

// THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT

// LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A

// PARTICULAR PURPOSE. THE SOFTWARE AND ACCOMPANYING DOCUMENTATION, IF

// ANY, PROVIDED HEREUNDER IS PROVIDED "AS IS". THE AUTHOR HAS NO OBLIGATION

// TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

//

// if u use it, u should show the source

// from http://rainfish.cublog.cn

// by ben

//

// if u find some questions, please tell me with email

// [e-mail]

//

// V1.1               20/09/2009      -Initial Version for cppodbc

////////////////////////////////////////////////////////////////////////////////

 

 

#ifndef _CPP_ODBC_H_

#define _CPP_ODBC_H_

 

#include

#include

#include

#include

#include

#include

 

//查询的最大字段数量

#define FIELD_NUM 1024

 

class CppODBC

{

public:

         CppODBC( );

         virtual ~CppODBC( );

         //公共接口

public:

         bool Open( );

         bool Close( );

         bool Connect( const char* pszDSN, const char* pszUName, const char* pszUPasswd );

         bool DisConnect( );

         bool Clear( );

         unsigned int     SQLQuery( const char* pszSQL );

         unsigned int     SQLExec( const char* pszSQL );

         unsigned int     SQLExecAutoID( char *pszSQL );

         bool    IsOpen( );

         //查询的结果数量，更新时返回更新的记录数量，删除时返回删除的数量

         unsigned int   GetCount( );

         //返回查询结果的列数两

         unsigned int   GetColumns( );

         int     GetIntValue( unsigned int uiIndex );

         char *                          GetStrValue( unsigned int uiIndex );

         //取消操作

         bool                    Cancel( );

         //获取错误代码

         long   GetError( );

         //下一个

         bool           Next( );

         bool           Eof( );

        

         bool    Lock();

         bool    UnLock();

        

private:

         SQLHENV                   V_OD_Env_;    // Handle ODBC environment 存放环境变量

         SQLHDBC                            V_OD_hdbc_;    // Handle connection 连接句柄

         SQLHSTMT                 V_OD_hstmt_;   // SQL语句的句柄

         SQLINTEGER             V_OD_rowanz_;   // 操作影响的记录数量

         SQLSMALLINT           V_OD_colanz_;   // 操作影响的记录包含的字段数量

         SQLINTEGER             V_OD_err_;  // sql语句执行后的错误代码

         char*                           pszField_[FIELD_NUM];  // 存放一条查询结果集,缓冲区根据查询结果创建

         int                                nMaxFiledLen_;    //字段的最大值

         bool                    bOpened_;

         bool                    bConnected_;

         bool                    bEof_;

        

         pthread_mutex_t mutex_;

         bool                    mutex_inited_;

};

 

#endif

 

 

//.cpp

////////////////////////////////////////////////////////////////////////////////

// CppMysql - A C++ wrapper around the odbc interface library.

//

// Copyright (c) 2009 Rob Groves. All Rights Reserved. [e-mail]

//

// Permission to use, copy, modify, and distribute this software and its

// documentation for any purpose, without fee, and without a written

// agreement, is hereby granted, provided that the above copyright notice,

// this paragraph and the following two paragraphs appear in all copies,

// modifications, and distributions.

//

// IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT,

// INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST

// PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION,

// EVEN IF THE AUTHOR HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

//

// THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT

// LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A

// PARTICULAR PURPOSE. THE SOFTWARE AND ACCOMPANYING DOCUMENTATION, IF

// ANY, PROVIDED HEREUNDER IS PROVIDED "AS IS". THE AUTHOR HAS NO OBLIGATION

// TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

//

// if u use it, u should show the source

// from http://rainfish.cublog.cn

// by ben

//

// if u find some questions, please tell me with email

// [e-mail]

//

// V1.1               20/09/2009      -Initial Version for cppodbc

////////////////////////////////////////////////////////////////////////////////

 

#include "cppodbc.h"

 

#include

 

 

CppODBC::CppODBC( )

{

         V_OD_err_ = 0;

 

         bOpened_ = false;

         bConnected_ = false;

         nMaxFiledLen_ = 512;

         bEof_ = false;

        

         for(int i=0; i

                   pszField_[i] = NULL;

        

         mutex_inited_ = false;

 

         //初始化互斥体

         if ( !mutex_inited_ )

         {

                   pthread_mutex_init(&mutex_, NULL);

                   mutex_inited_ = true;

         }

}

 

CppODBC::~CppODBC( )

{

         if (mutex_inited_)

                   pthread_mutex_destroy(&mutex_);

 

         Clear( );

}

 

bool  CppODBC::Open()

{

         if ( bOpened_ )//已经打开了

                   return true;

        

         long     V_OD_erg; // result of functions 存放错误代码

        

         // allocate Environment handle and register version

         V_OD_erg = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &V_OD_Env_ );

         if ( (V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO) )

         {

                   printf("Error AllocHandle\n");

                   return false;

         }

        

         V_OD_erg = SQLSetEnvAttr( V_OD_Env_, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0 );

         if ( (V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO) )

         {

                   printf("Error SetEnv\n");

                   SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env_);

                   return false;

         }

        

         bOpened_ = true;

         return true;

}

 

bool  CppODBC::Close( )

{

         if ( bConnected_ )

                   return false;

        

         SQLFreeHandle(SQL_HANDLE_ENV, V_OD_Env_);

         bOpened_ = false;

        

         return true;

}

 

bool   CppODBC::Connect( const char* pszDSN, const char* pszUName, const char* pszUPasswd )

{

         if ( !bOpened_ )

                   return false;

        

         if ( pszDSN == NULL )

                   return false;

        

         long           V_OD_erg = 0;

         SQLCHAR          V_OD_stat[64]= {0}; // Status SQL 执行sql语句的结果状态

         SQLSMALLINT V_OD_mlen = 0;  // 错误返回的消息文本大小

         SQLCHAR     V_OD_msg[256] = {0};// 错误消息缓冲区

        

         // allocate connection handle, set timeout

         V_OD_erg = SQLAllocHandle( SQL_HANDLE_DBC, V_OD_Env_, &V_OD_hdbc_ );

         if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

         {

                   printf("Error AllocHDB %ld\n",V_OD_erg);

                   return false;

         }

        

         //(SQLPOINTER *)

         V_OD_erg = SQLSetConnectAttr(V_OD_hdbc_, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);

         if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

         {

                   printf("Error SQLSetConnectAttr %ld\n",V_OD_erg);

                   SQLFreeHandle( SQL_HANDLE_DBC, V_OD_hdbc_ );

                   return false;

         }

        

         // Connect to the datasource

         //MysqlODBC //MyPostgres // mysqlitedb

         V_OD_erg = SQLConnect(V_OD_hdbc_, (SQLCHAR*) pszDSN, SQL_NTS,

                   (SQLCHAR*) pszUName, SQL_NTS,

                   (SQLCHAR*) pszUPasswd, SQL_NTS);

         if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

         {

                   printf("Error SQLConnect %ld\n",V_OD_erg);

                   SQLGetDiagRec( SQL_HANDLE_DBC, V_OD_hdbc_, 1,

                            V_OD_stat, &V_OD_err_, V_OD_msg, 256, &V_OD_mlen );

                   printf("%s (%ld)\n",V_OD_msg, V_OD_err_);

                   SQLFreeHandle( SQL_HANDLE_DBC, V_OD_hdbc_ );

                   return false;

         }

        

         printf("Connected !\n");

        

         V_OD_erg = SQLAllocHandle(SQL_HANDLE_STMT, V_OD_hdbc_ , &V_OD_hstmt_);

         if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

         {

                   printf("Fehler im AllocStatement %ld\n",V_OD_erg);

                   SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc_, 1, V_OD_stat, &V_OD_err_, V_OD_msg, 256, &V_OD_mlen);

                   printf("%s (%ld)\n", V_OD_msg, V_OD_err_);

                   SQLDisconnect( V_OD_hdbc_ );

                   SQLFreeHandle( SQL_HANDLE_DBC, V_OD_hdbc_ );

                   return false;

         }

        

         bConnected_ = true;

         return true;

}

 

bool   CppODBC::DisConnect( )

{

         if ( bConnected_ )

         {

                   SQLFreeHandle( SQL_HANDLE_STMT,V_OD_hstmt_ );

                   SQLDisconnect( V_OD_hdbc_ );

                   SQLFreeHandle( SQL_HANDLE_DBC, V_OD_hdbc_ );

                   bConnected_ = false;

         }

        

         return true;

}

 

unsigned int  CppODBC::SQLQuery( const char* pszSQL )

{

         if ( pszSQL == NULL )

                   return 0;

        

         long   V_OD_erg = 0;

         SQLCHAR   V_OD_stat[64]= {0}; // Status SQL 执行sql语句的结果状态

         SQLSMALLINT  V_OD_mlen = 0;  // 错误返回的消息文本大小

         SQLCHAR         V_OD_msg[256] = {0};// 错误消息缓冲区

         char*   pszBuf = NULL;

        

         //清空缓冲区

         Clear();

         //查询

        

    V_OD_erg=SQLExecDirect(V_OD_hstmt_, (SQLCHAR*)pszSQL, SQL_NTS);  

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

    {

                   printf("Error in Select %ld\n", V_OD_erg);

                   SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc_, 1, V_OD_stat, &V_OD_err_, V_OD_msg, 256, &V_OD_mlen);

                   printf("%s (%ld)\n",V_OD_msg, V_OD_err_);

                   return 0;

    }

   

         //获取查询结果的数量

    V_OD_erg = SQLRowCount(V_OD_hstmt_, &V_OD_rowanz_);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

    {

                   return 0;

    }

   

    if ( V_OD_rowanz_ == 0 )//没有查询结果    

                   return 0;

   

    //获取结果字段的数量

    V_OD_erg=SQLNumResultCols(V_OD_hstmt_, &V_OD_colanz_);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

    {

                   return 0;

    }

    printf("Number of Columns %d\n",V_OD_colanz_);

 

         //列属性

         char sz_buf[256] = {0};

         SQLSMALLINT buf_len = 0;

         SQLINTEGER colLen = 0;

//      SQLINTEGER colInfo = 0;

         SQLINTEGER colType = 0;

         for (int i=1; i<=V_OD_colanz_; i++)//列索引是从1开始到V_OD_colanz_

         {

                   //SQLColAttributes(V_OD_hstmt_, i, SQL_DESC_LENGTH, sz_buf, 256, &colLen, &colInfo);

                   SQLColAttribute(V_OD_hstmt_, i, SQL_DESC_NAME, sz_buf, 256, &buf_len, 0);

                   /* SQL data type codes */

// #define          SQL_UNKNOWN_TYPE    0

// #define SQL_CHAR            1

// #define SQL_NUMERIC         2

// #define SQL_DECIMAL         3

// #define SQL_INTEGER         4

// #define SQL_SMALLINT        5

// #define SQL_FLOAT           6

// #define SQL_REAL            7

// #define SQL_DOUBLE          8

// #if (ODBCVER >= 0x0300)

// #define SQL_DATETIME        9

// #endif

// #define SQL_VARCHAR        12

                   SQLColAttribute(V_OD_hstmt_, i, SQL_DESC_TYPE, 0, 0, 0, &colType);

 

                   //获取制定列的长度

                   SQLColAttribute(V_OD_hstmt_, i, SQL_DESC_LENGTH, NULL, 0, 0, &colLen);

 

                   //绑定字段缓冲区

                   pszBuf = (char*)malloc(colLen + 1);// char[ colLen+ 1 ];

                   memset( pszBuf, 0, colLen + 1 );

                   pszField_[ i ] = pszBuf;

                   SQLBindCol(V_OD_hstmt_, i, SQL_C_CHAR, pszBuf, colLen, &V_OD_err_);

 

                   printf("col %d szBuf %s, buflen is %d, len is %ld, type is %ld\n", i, sz_buf, buf_len,

                            colLen, colType);

         }

 

    //得到一行结果，结果放在绑定的缓冲区里面

    V_OD_erg = SQLFetch( V_OD_hstmt_ );

    if ( V_OD_erg != SQL_NO_DATA )

                   bEof_ = false;

        

    return V_OD_rowanz_;

}

 

bool   CppODBC::Clear( )

{

         V_OD_rowanz_ = 0;

         V_OD_colanz_ = 0;

         bEof_ = true;

         for (int i=0; i

         {

                   if ( pszField_[i] != NULL )

                            free(pszField_[i]);

                   pszField_[i] = NULL;

         }

         return true;

}

 

unsigned int  CppODBC::SQLExec( const char* pszSQL )

{

         if ( pszSQL == NULL )

                   return 0;

        

         long   V_OD_erg = 0;

         SQLCHAR   V_OD_stat[64]= {0}; // Status SQL 执行sql语句的结果状态

         SQLSMALLINT  V_OD_mlen = 0;  // 错误返回的消息文本大小

         SQLCHAR         V_OD_msg[256] = {0};// 错误消息缓冲区

//      char*   pszBuf = NULL;

        

         //清空缓冲区

    Clear();

        

    V_OD_erg=SQLExecDirect(V_OD_hstmt_, (SQLCHAR*)pszSQL, SQL_NTS);  

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

    {

                   printf("Error in Select %ld\n", V_OD_erg);

                   SQLGetDiagRec(SQL_HANDLE_DBC, V_OD_hdbc_, 1, V_OD_stat, &V_OD_err_, V_OD_msg, 256, &V_OD_mlen);

                   printf("%s (%ld)\n",V_OD_msg, V_OD_err_);

                   return 0;

    }

   

    //获取查询结果的数量

    V_OD_erg = SQLRowCount(V_OD_hstmt_, &V_OD_rowanz_);

    if ((V_OD_erg != SQL_SUCCESS) && (V_OD_erg != SQL_SUCCESS_WITH_INFO))

    {

                   return 0;

    }

        

    return V_OD_rowanz_;

}

 

unsigned int  CppODBC::SQLExecAutoID( char *pszSQL )

{

         return 0;

}

 

bool    CppODBC::IsOpen( )

{

         return bOpened_;

}

 

//查询的结果数量，更新时返回更新的记录数量，删除时返回删除的数量

unsigned int   CppODBC::GetCount( )

{

         return V_OD_rowanz_;

}

 

//返回查询结果的列数两

unsigned int   CppODBC::GetColumns( )

{

         return V_OD_colanz_;

}

 

int   CppODBC::GetIntValue( unsigned int uiIndex )

{

         if ( uiIndex < 0 || (short)uiIndex > V_OD_colanz_ )

                   return 0;

        

         int nField = 0;

        

         nField = atoi( pszField_[uiIndex] );

        

         return nField;

}

 

char *     CppODBC::GetStrValue( unsigned int uiIndex )

{

         if ( uiIndex < 0 || (short)uiIndex > V_OD_colanz_ )

                   return NULL;

        

         return pszField_[uiIndex];

}

 

//取消操作

bool       CppODBC::Cancel( )

{

         return true;

}

 

//获取错误代码

long CppODBC::GetError( )

{

         return V_OD_err_;

}

 

//下一个

bool       CppODBC::Next( )

{

         long V_OD_erg = 0;

        

         V_OD_erg = SQLFetch( V_OD_hstmt_ );

    if ( V_OD_erg != SQL_NO_DATA )

                   bEof_ = false;

    else

                   bEof_ = true;

   

    return !bEof_;   

}

 

bool   CppODBC::Eof( )

{

         return bEof_;

}

 

bool    CppODBC::Lock()

{

         if (!mutex_inited_)

                   return false;

        

         pthread_mutex_lock(&mutex_);

         return true;

}

 

bool    CppODBC::UnLock()

{

         if (!mutex_inited_)

                   return false;

        

         pthread_mutex_unlock(&mutex_);

         return true;

}

 

 

 

 

//demo

 

#include "cppodbc.h"

 

int main()

{

         CppODBC cppOdbc;

        

         bool bRes = cppOdbc.Open();

         if ( !bRes )

         {

                   printf ( "Open error!\n " );

                   return 0;

         }

         printf ( "Open OK!\n " );

         bRes = cppOdbc.Connect("test1",  "root" ,  "root");

 

         if ( !bRes )

         {

                   printf ( "Connect error!\n " );

                   return 0;

         }

        

         printf ( "Connect OK!\n " );

         int nRes = 0;

        

         //INSERT INTO UserInfo VALUES('44', 'ODBCTest22')

         //nRes = cppOdbc.SQLExec( "INSERT INTO UserInfo VALUES('ODBCTest22', '44')" );

        

         //printf ( "SQLExec the nRes is %d\n", nRes );

 

         nRes = cppOdbc.SQLExec( "UPDATE UserInfo SET Age = 20 WHERE UID='55'" );

        

         printf ( "SQLExec the nRes is %d\n", nRes );

        

         nRes = cppOdbc.SQLQuery("SELECT * FROM UserInfo");

         printf ( "SQLQuery the nRes is %d\n", nRes );

        

         char *pszBuf = NULL;

         int  nBuf = 0;

         int  i = 0;

         while( !cppOdbc.Eof() )

         {

                   i = 0;

                   nBuf = cppOdbc.GetIntValue( i++ );

                   printf ( " UID is %d\n", nBuf );

                  

                   //nBuf = cppOdbc.GetIntValue( i++ );

                   //printf ( " UID is %d\n", nBuf );

                  

                   pszBuf = cppOdbc.GetStrValue( i++ );

                   printf ( " UName is %s\n", pszBuf );

 

                   nBuf = cppOdbc.GetIntValue( i++ );

                   printf ( " Age is %d\n", nBuf );

                  

                   cppOdbc.Next();

                  

                   //getchar();

         }

        

         cppOdbc.DisConnect();

         cppOdbc.Close();

        

         return 0;

}

 

 

 

By lizhiping

2009-10-10

文件:	cppodbc.rar
大小:	23KB
下载:	下载