myodbc.cpp

用ODBC写的数据库接口,支持SQlserver,mysql

// Odbc1.cpp: implementation of the CMyODBC class.
//
//////////////////////////////////////////////////////////////////////

//#include "stdafx.h"
#include "MyODBC.h"
#include <odbcinst.h>
#include <stdio.h>
//#include <dmodbcEx.h>
/*#ifdef _DEBUG
#undef THIS_FILE
static char THIS_FILE[]=__FILE__;
#define new DEBUG_NEW
#endif
*/
//////////////////////////////////////////////////////////////////////
// Construction/Destruction
//////////////////////////////////////////////////////////////////////
#pragma comment(lib, "odbc32.lib")
#pragma comment(lib, "odbccp32.lib")

void WriteLog(char *info)
{
    FILE *fp = fopen(".\\dblog.txt","r+");
    if(!fp)
        fp = fopen(".\\dblog.txt","w");
    if(fp)
    {
        fseek(fp,0,SEEK_END);
        fwrite(info,1,strlen(info),fp);
        fwrite("\n",1,1,fp);
        fclose(fp);
    }
}

CMyODBC::CMyODBC()
{
	this->m_hdbc = NULL;
	this->m_henv = NULL;
	this->m_hstmt = NULL;
	this->m_retcode = 0;
	m_pSet = NULL;
    m_SQLState = new unsigned char[6];
    m_FetchType = 0;
}

CMyODBC::~CMyODBC()
{
	DisConnect();
    delete m_SQLState;	
}

BOOL CMyODBC::ConnectDB(const char *cpConnectStr)
{
	//分配环境句柄
	m_retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		strcpy(m_Err,"分配环境句柄失败!");
		return FALSE;
	}
		/* Set the ODBC version environment attribute */
	m_retcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_henv, SQL_HANDLE_ENV, "设置odbc版本号时失败!");
		return FALSE;
	}	
	/*分配连接句柄 */
	m_retcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hdbc); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_henv, SQL_HANDLE_ENV,"分配连接句柄失败!");
		return FALSE;
	}

    SQLCHAR ConnStrOut[256];
    SQLSMALLINT ConnStrOutLen;
	/* 连接数据库 */
	m_retcode = SQLDriverConnect(m_hdbc,NULL,(SQLCHAR *)cpConnectStr,SQL_NTS,ConnStrOut,sizeof(ConnStrOut),
        &ConnStrOutLen,SQL_DRIVER_NOPROMPT);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"连接数据库失败!");
		return FALSE;
	}
	return TRUE;

/*SQLHENV henv;
SQLHDBC hdbc;
SQLRETURN retcode;
SQLCHAR ConnStr[256] = "DSN=MySQLServer;UID=sa;PWD=sa";
SQLCHAR ConnStrOut[256];
SQLSMALLINT ConnStrOutLen;
SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);
SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);
SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
retcode = SQLDriverConnect(hdbc,NULL,ConnStr,SQL_NTS,ConnStrOut, sizeof(ConnStrOut),&ConnStrOutLen,SQL_DRIVER_NOPROMPT);
if(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
cout << "ok" << endl;
cout << ConnStrOutLen << endl;
cout << ConnStrOut << endl;
}*/
}

BOOL CMyODBC::ConnectDB(const char *cpServerName,const char *cpUserName,const char *cpPassword)
{
	//分配环境句柄
	m_retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		strcpy(m_Err,"分配环境句柄失败!");
		return FALSE;
	}
		/* Set the ODBC version environment attribute */
	m_retcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_henv, SQL_HANDLE_ENV, "设置odbc版本号时失败!");
		return FALSE;
	}	
	/*分配连接句柄 */
	m_retcode = SQLAllocHandle(SQL_HANDLE_DBC, m_henv, &m_hdbc); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_henv, SQL_HANDLE_ENV,"分配连接句柄失败!");
		return FALSE;
	}

	/* 连接数据库 */
	m_retcode = SQLConnect(m_hdbc, (SQLCHAR*) cpServerName, SQL_NTS, (SQLCHAR*) cpUserName, SQL_NTS, (SQLCHAR*) cpPassword, SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"连接数据库失败!");
		return FALSE;
	}
	return TRUE;
}

BOOL CMyODBC::DisConnect()
{
	if(m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}

	if(this->m_hdbc != NULL)
	{
		SQLDisconnect(m_hdbc);
		SQLFreeHandle(SQL_HANDLE_DBC, m_hdbc);
		m_hdbc = NULL;
	}
	
	if(this->m_henv != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_ENV, m_henv);
		m_henv = NULL;
	}

	if(this->m_pSet != NULL)
	{
		m_pSet = NULL;
	}
	return TRUE;

}

BOOL CMyODBC::ExeSqlDirect(const char *cpSqlStmt)
{
	SQLHSTMT hStmt;
	if(this->m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}
	if(this->m_hdbc == NULL)
	{
		strcpy(m_Err,"没有连接数据库，请先进行联接!");
		return FALSE;
	}

	m_retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &hStmt); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"分配语句句柄失败,不能执行");
		return FALSE;
	}

//  SQLBindParameter(hstmt,   1,   SQL_PARAM_INPUT,   SQL_C_ULONG,
  //                                    SQL_INTEGER,   0,   0,   &sPartID,   0,   &cbPartID);
  //SQLBindParameter(hstmt,   2,   SQL_PARAM_INPUT,
  //                                    SQL_C_BINARY,   SQL_LONGVARBINARY,
  //                                    0,   0,   (SQLPOINTER)   2,   0,   &cbPhotoParam);

	m_retcode = SQLExecDirect(hStmt, (unsigned char *)cpSqlStmt, SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO) && (m_retcode != SQL_NO_DATA))
	{
		ReportError(hStmt, SQL_HANDLE_STMT, "执行sql语句失败,不能执行");
		SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
		hStmt = NULL;
		return FALSE;
	}
    while ( ( m_retcode = SQLMoreResults(hStmt) ) != SQL_NO_DATA );

	SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
	hStmt = NULL;
	return TRUE;
}


BOOL CMyODBC::PrepareSql(const char *cpSql, CODBCSet *rset)
{
	if(this->m_hstmt != NULL)
	{
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
	}
	if(this->m_hdbc == NULL)
	{
		strcpy(m_Err,"没有连接数据库，请先进行联接!");
		return FALSE;
	}
	m_retcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt); 
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hdbc, SQL_HANDLE_DBC,"分配语句句柄失败,不能执行");
		return FALSE;
	}

	this->m_pSet = rset;
	if(!m_pSet->IsEmpty())
	{
		m_pSet->Empty();
	}
//    SQLINTER cc = SQL_NC_OFF;

//    SQLSetStmtAttr(m_hstmt,SQL_SOPT_SS_NCOUNT_STATUS ,(void *)SQL_NC_OFF,sizeof(cc));
//SQL_ATTR_ROW_ARRAY_SIZE
//    SQLSetStmtAttr
//	ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_CONCURRENCY,
//		(SQLPOINTER) SQL_CONCUR_ROWVER, 0);
     //SQL_CURSOR_DYNAMIC,SQL_CURSOR_STATIC
    m_retcode = SQLSetStmtAttr(m_hstmt, SQL_ATTR_CURSOR_TYPE,
		(SQLPOINTER) SQL_CURSOR_STATIC, 0);

    // Use row-wise binding.
//    ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_BIND_TYPE,
//		(SQLPOINTER) sizeof(EMP_INFO), 0);

	m_retcode = SQLSetStmtAttr(m_hstmt, SQL_ATTR_ROW_ARRAY_SIZE,
		(SQLPOINTER) 1, 0);

//	ret = SQLSetStmtAttr(hstmt1, SQL_ATTR_ROW_STATUS_PTR,
//		RowStatusArray, 0);
//    SQLSetStmtAttr(m_hstmt, SQL_SOPT_SS_CURSOR_OPTIONS,SQL_CURSOR_DYNAMIC,0);

	m_retcode = SQLExecDirect(m_hstmt, (unsigned char *)cpSql, SQL_NTS);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hstmt,SQL_HANDLE_STMT, "执行sql语句失败");
		SQLFreeHandle(SQL_HANDLE_STMT, m_hstmt);
		m_hstmt = NULL;
		return FALSE;
	}
//    SQLGetStmtAttr();


	SQLSMALLINT iNumCols = 0;
	m_retcode = SQLNumResultCols(m_hstmt, &iNumCols);
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
	{
		ReportError(m_hstmt,SQL_HANDLE_STMT,  "取列数失败,不能执行");
		return FALSE;
	}
	m_pSet->m_cols = iNumCols;
	m_pSet->m_coldata = new COL_DATA_ODBC[iNumCols];
	if (m_pSet->m_coldata == NULL)
	{
		strcpy(m_Err,"分配每个列内存空间失败\n");
		return FALSE;
	}
    memset(m_pSet->m_coldata,0,sizeof(COL_DATA_ODBC)*iNumCols);
	m_pSet->m_coldatafmt  = new COL_DATAFMT_ODBC[iNumCols];
	if(m_pSet->m_coldatafmt == NULL)
	{
		strcpy(m_Err,"分配每个列信息的内存空间失败\n");
		delete []m_pSet->m_coldata;
		m_pSet->m_coldata = 0;
		return FALSE;
	}
    memset(m_pSet->m_coldatafmt,0,sizeof(COL_DATAFMT_ODBC)*iNumCols);

//	SQLINTEGER    dataLen;
//	SQLSMALLINT  SqlType, cType;
//	char   SqlColName[100];
	SQLSMALLINT StringLeng;
	//SqlType = SQL_INTEGER ;

	for(int iCount = 0; iCount <iNumCols; iCount++)
	{
/*		m_retcode = SQLColAttribute(m_hstmt, ((SQLUSMALLINT)iCount) + 1,
            SQL_DESC_NAME, (SQLPOINTER)m_pSet->m_coldatafmt[iCount].name,
            sizeof(m_pSet->m_coldatafmt[iCount].name), &StringLeng, 0);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT,"取列名时失败");
			return FALSE;
		}
//		strncpy(m_pSet->m_coldatafmt[iCount].name, SqlColName, StringLeng);
		m_pSet->m_coldatafmt[iCount].name[StringLeng] = 0;

		m_retcode = SQLColAttribute(m_hstmt, ((SQLUSMALLINT)iCount) + 1,
            SQL_DESC_TYPE, NULL, 0, NULL, (SQLPOINTER)&SqlType);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT,"取类型代码时失败");
			return FALSE;
		}
		cType = GetDefaultCType(SqlType);
		m_pSet->m_coldatafmt[iCount].datatype = cType;
		dataLen = GetColLength(m_hstmt, SqlType,iCount+1)  ;
		m_pSet->m_coldatafmt[iCount].maxlength = dataLen + 1;
*/
/*SQLRETURN  SQL_API SQLDescribeCol(SQLHSTMT StatementHandle,
           SQLUSMALLINT ColumnNumber, SQLCHAR *ColumnName,
           SQLSMALLINT BufferLength, SQLSMALLINT *NameLength,
           SQLSMALLINT *DataType, SQLULEN *ColumnSize,
           SQLSMALLINT *DecimalDigits, SQLSMALLINT *Nullable);*/

		m_retcode = SQLDescribeCol(m_hstmt,((SQLUSMALLINT)iCount) + 1,
			(UCHAR*)m_pSet->m_coldatafmt[iCount].name, MAX_FNAME_LEN,&StringLeng,
			&m_pSet->m_coldatafmt[iCount].datatype,
			&m_pSet->m_coldatafmt[iCount].maxlength,NULL,NULL);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT,"取类型代码时失败");
			return FALSE;
		}
        UpperStr((UCHAR*)m_pSet->m_coldatafmt[iCount].name,(UCHAR*)m_pSet->m_coldatafmt[iCount].name);

        m_pSet->m_coldatafmt[iCount].c_datatype = GetDefaultCType(m_pSet->m_coldatafmt[iCount].datatype);
        if(m_pSet->m_coldatafmt[iCount].c_datatype!=SQL_C_BINARY)
        {
            if(m_pSet->m_coldatafmt[iCount].maxlength>8000)  //其实大于应该用SQLgetdata获取
                m_pSet->m_coldatafmt[iCount].maxlength = 8000;
            m_pSet->m_coldatafmt[iCount].maxlength+=1;
    //			&m_rgODBCFieldInfos[n - 1].m_nNullability));
            //		m_pSet->m_coldata[iCount].valuelen = new long;
            m_pSet->m_coldata[iCount].value = new char[m_pSet->m_coldatafmt[iCount].maxlength+1];
            if( m_pSet->m_coldata[iCount].value == NULL )//||  m_pSet->m_coldata[iCount].valuelen == NULL)
            {
                strcpy(m_Err,"fail: new char[]");
                delete m_pSet->m_coldata;
                delete m_pSet->m_coldatafmt;
                m_pSet->m_coldata = 0;
                m_pSet->m_coldatafmt = 0;
                return FALSE;
            }
            memset(m_pSet->m_coldata[iCount].value, 0, m_pSet->m_coldatafmt[iCount].maxlength+1);
        }
	}
    //bind
/*
	for(int i = 0; i < iNumCols; i++)
	{
        if(m_pSet->m_coldatafmt[i].c_datatype!=SQL_C_BINARY)
        {
            m_retcode = SQLBindCol(m_hstmt, ((SQLUSMALLINT)i)+1,
                (SQLSMALLINT)m_pSet->m_coldatafmt[i].c_datatype ,
                m_pSet->m_coldata[i].value, m_pSet->m_coldatafmt[i].maxlength,
                (long *)&(m_pSet->m_coldata[i].valuelen));

            if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
            {
    //			sprintf(m_Err,"第 %d 列绑定失败,你指定的类型是%d",i + 1, m_pSet->m_coldatafmt[i].datatype);
                ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
                return FALSE;
            }
        }
	}
    */
//	SQLFetch(m_hstmt);
    m_retcode=SQLRowCount(m_hstmt,&(m_pSet->m_rows));
	if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO)
        ||m_pSet->m_rows == -1)
	{
		//ReportError(m_hstmt,SQL_HANDLE_STMT,  "取列数失败,不能执行");
		//return FALSE;
//        char currname[120];
//        SQLGetCursorName(m_hstmt,currname,120,&StringLeng);


//        while(1)
//        {
//            m_retcode = SQLFetchScroll(m_hstmt,SQL_FETCH_FIRST,0);
//		}
        /*if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
			return FALSE;
		}*/

        m_pSet->m_rows = 0;
        while((m_retcode = SQLFetch(m_hstmt)) != SQL_NO_DATA)
            m_pSet->m_rows++;
//        m_retcode = SQL_POSITION_TO(m_hstmt,1);
	for(int i = 0; i < iNumCols; i++)
	{
        if(m_pSet->m_coldatafmt[i].c_datatype!=SQL_C_BINARY)
        {
            m_retcode = SQLBindCol(m_hstmt, ((SQLUSMALLINT)i)+1,
                (SQLSMALLINT)m_pSet->m_coldatafmt[i].c_datatype ,
                m_pSet->m_coldata[i].value, m_pSet->m_coldatafmt[i].maxlength,
                (long *)&(m_pSet->m_coldata[i].valuelen));

            if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
            {
    //			sprintf(m_Err,"第 %d 列绑定失败,你指定的类型是%d",i + 1, m_pSet->m_coldatafmt[i].datatype);
                ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
                return FALSE;
            }
        }
	}
        m_retcode = SQLFetchScroll(m_hstmt,SQL_FETCH_FIRST,0);
		if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO) && (m_retcode != SQL_NO_DATA) )
		{
			ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
			return FALSE;
		}
        //SQLFetchScroll(m_hstmt,SQL_FETCH_FIRST,0);
        m_FetchType = 1;
//        if(m_pSet->m_rows>0)
//            ConvertDataToChar();
	}
    else
    {
	for(int i = 0; i < iNumCols; i++)
	{
        if(m_pSet->m_coldatafmt[i].c_datatype!=SQL_C_BINARY)
        {
            m_retcode = SQLBindCol(m_hstmt, ((SQLUSMALLINT)i)+1,
                (SQLSMALLINT)m_pSet->m_coldatafmt[i].c_datatype ,
                m_pSet->m_coldata[i].value, m_pSet->m_coldatafmt[i].maxlength,
                (long *)&(m_pSet->m_coldata[i].valuelen));

            if ((m_retcode != SQL_SUCCESS) && (m_retcode != SQL_SUCCESS_WITH_INFO))
            {
    //			sprintf(m_Err,"第 %d 列绑定失败,你指定的类型是%d",i + 1, m_pSet->m_coldatafmt[i].datatype);
                ReportError(m_hstmt,SQL_HANDLE_STMT, m_Err);
                return FALSE;
            }
        }
	}