ii_mysql.cpp

linux下mysql调用

//====================================================================
// 文件名: ii_mysql.cpp
//
// 文件描述:
// ------------------------------------------------------------------
// 跨平台通用数据库接口Mysql数据库插件实现
// ------------------------------------------------------------------
// 
// 时间: 2002.9
// 编程: 喻宜
// ------------------------------------------------------------------
// 修改说明(请按格式说明)...
//====================================================================

#include <stdlib.h>
#include "../../../../include/ies_templ.h"
#include "../../../../include/dbi_odbc.h"

#ifdef WIN32
#define __WIN__
#include <winsock.h>
#endif

#include "./include/mysql.h"

///////////////////////////////////////////////////////////////////
// MYSQL DATABASE
///////////////////////////////////////////////////////////////////
class CIIMysqlDBI
{
public:
	CIIMysqlDBI();
	~CIIMysqlDBI();

public:
	//连接和断开数据库
	bool Open(const char *i_pIpAddr, const char *i_pDbName,
					const char *i_pszUser, const char *i_pszPwd);
	void Close();

	bool ExecuteSQL(const char *i_pszSqlStr);				//执行SQL语句

	bool IsTableExist(const char *i_szTableName);			//判断表是否存在

	//获取错误号和错误字符串
	int GetErrorNo();
	const char* GetErrorStr();

	bool PrepareSQL(const char *i_pStringSQL);			//准备SQL语句
	void CloseSQL();									//关闭本次SQL处理

	//ODBC操作
	bool BindCol(int i_nColType,
			int i_nColSize, void *i_pColAddr);		//绑定列
	bool BindPara(int i_nParaType,
			int i_nParaSize, void *i_pParaAddr);	//绑定参数

	int Exec();										//执行SQL语句
	bool Fetch();									//获取查询结果

	bool IsEnd()									//是否到了记录尾
		{return m_bIsEnd;}

	// 事务操作
	bool BeginTrans();
	bool Commit();
	bool Rollback();

protected:
	bool PassPara();								//传递参数信息
	bool PassCol();									//传递列信息

	bool MakeColInfo();								//整理列信息(为计算偏移量)

private:
	bool		m_bIsOpenDB;						//是否打开数据库
	bool		m_bIsPrepareSQL;					//是否准备SQL语句
	bool		m_bIsEnd;							//是否查询到尾
	CIIString	m_pszSql;							//SQL语句
	CIIString	m_pszSqlBak;						//SQL语句备份
	CIIString	m_pszNextSql;
	CIILog		m_iiLog;							//日志
	CIIString	m_pszError;							//错误字符串

	MYSQL*		m_pConnMysql;						//连接参数
	MYSQL_RES*	m_pResMysql;						//查询结果
	MYSQL_ROW	m_sRowMysql;						//记录集

	CIIArray<SBind>	m_arPara;						//参数信息集
	CIIArray<SBind> m_arCol;						//字段信息集

	int			m_nRowSize;							//一个记录的大小
	int			m_nRowCount;						//记录个数
	int			m_nColCount;						//字段个数
	int			m_nRowId;							//ODBC中记录获取第几次	
	int			m_nSqlType;
};

CIIMysqlDBI::CIIMysqlDBI():
	m_pResMysql(NULL),
	m_sRowMysql(NULL),
	m_pConnMysql(NULL),
	m_bIsPrepareSQL(false),
	m_nRowCount(0),
	m_nRowId(0),
	m_nSqlType(0),
	m_bIsOpenDB(false),
	m_bIsEnd(true)
{
	m_iiLog.SetProcName("mysql_db");
	m_arPara.SetSize(10);
	m_arCol.SetSize(20);
}

CIIMysqlDBI::~CIIMysqlDBI()
{
	if (m_bIsOpenDB)
		Close();
}

bool CIIMysqlDBI::Open(const char *i_pIpAddr, const char *i_pDbName, const char *i_pszUser, const char *i_pszPwd)
{
	m_pConnMysql = mysql_init((MYSQL*)0);

	if (m_pConnMysql == NULL)
		return false;

	if (mysql_real_connect(m_pConnMysql, i_pIpAddr, i_pszUser, 
							i_pszPwd, i_pDbName, 0, NULL, CLIENT_FOUND_ROWS) == NULL)
	{
		return false;
	}

	m_bIsOpenDB = true;
	return true;
}

void CIIMysqlDBI::Close()
{
	if (m_bIsPrepareSQL)
	{
		CloseSQL();
		m_bIsPrepareSQL = false;
	}

	if (m_pConnMysql != NULL)
	{
		mysql_close(m_pConnMysql);
		m_pConnMysql = NULL;
	}
	m_bIsOpenDB = false;
}

bool CIIMysqlDBI::ExecuteSQL(const char *i_pszSqlStr)
{
	if (m_pConnMysql == NULL)
		return false;

	if (mysql_query(m_pConnMysql, i_pszSqlStr) == 0)
		return true;

	return false;
}

bool CIIMysqlDBI::IsTableExist(const char *i_szTableName)
{
	CIIString tSQLString = "SELECT * FROM ";

	if (i_szTableName == NULL)
		return false;

	tSQLString += i_szTableName;
	tSQLString += " WHERE 1=0";

	return ExecuteSQL(tSQLString.GetBuf());
}

int CIIMysqlDBI::GetErrorNo()
{
	return mysql_errno(m_pConnMysql);
}

const char* CIIMysqlDBI::GetErrorStr()
{
	m_pszError = mysql_error(m_pConnMysql);
	return m_pszError.GetBuf();
}

bool CIIMysqlDBI::PrepareSQL(const char *i_pStringSQL)
{
	if (!m_bIsOpenDB)
		return false;

	//如果未关闭SQL，则先关闭SQL
	if (m_bIsPrepareSQL)
		CloseSQL();

	m_pszSql = i_pStringSQL;
	m_pszSql.TrimAll();
	m_pszSqlBak = m_pszSql;
	m_bIsPrepareSQL = true;

	CIIString tStr = m_pszSql.Left(6);
	tStr.ToUpper();

	if (tStr == "SELECT")
		m_nSqlType = 1;
	else if (tStr == "UPDATE" || tStr == "DELETE")
		m_nSqlType = 2;
	else
		m_nSqlType = 0;

	return true;
}

void CIIMysqlDBI::CloseSQL()
{
	if (m_pResMysql)
	{
		mysql_free_result(m_pResMysql);
		m_pResMysql = NULL;
	}

	m_arCol.Empty();
	m_arPara.Empty();
	m_pszNextSql.Empty();

	m_nRowCount = 0;
	m_nRowId = 0;
	m_bIsEnd = false;
	m_bIsPrepareSQL = false;
}

bool CIIMysqlDBI::BindCol(int i_nColType,
						int i_nColSize, void *i_pColAddr)
{
	SBind tmp;
	bool bRet;

	if (i_pColAddr == NULL)
		return false;

	tmp.nType = i_nColType;
	tmp.pAddr = i_pColAddr;
	tmp.nSize = i_nColSize;

	bRet = m_arCol.AddLast(tmp);
	return bRet;
}

bool CIIMysqlDBI::BindPara(int i_nParaType,
						   int i_nParaSize, void *i_pParaAddr)
{
	SBind tmp;
	bool bRet;

	if (i_pParaAddr == NULL)
		return false;

	tmp.nType = i_nParaType;
	tmp.pAddr = i_pParaAddr;
	tmp.nSize = i_nParaSize;

	bRet = m_arPara.AddLast(tmp);
	return bRet;
}

bool CIIMysqlDBI::PassCol()
{
	CIITime tmpTime;
	STimeInfo *tTime;

	if (!m_bIsOpenDB)
		return false;

	for (int i = 0; i < m_arCol.GetNum(); i++)
	{
		if (m_sRowMysql[i] == NULL)		//结果为空则下一个
			continue;

		switch (m_arCol[i].nType)
		{
		case IES_SQL_BOOL:
		case IES_SQL_UTINYINT:
			*(Juint8*)m_arCol[i].pAddr = (Juint8)atoi(m_sRowMysql[i]);
			break;
		case IES_SQL_SMALLINT:
			*(Jint16*)m_arCol[i].pAddr = (Jint16)atoi(m_sRowMysql[i]);
			break;
		case IES_SQL_USMALLINT:
			*(Juint16*)m_arCol[i].pAddr = (Juint16)atoi(m_sRowMysql[i]);
			break;
		case IES_SQL_INT:
			*(Jint32*)m_arCol[i].pAddr = (Jint32)atoi(m_sRowMysql[i]);
			break;
		case IES_SQL_UINT:
			*(Juint32*)m_arCol[i].pAddr = (Juint32)atoi(m_sRowMysql[i]);
			break;
		case IES_SQL_FLOAT:
			*(float*)m_arCol[i].pAddr = (float)atof(m_sRowMysql[i]);
			break;
		case IES_SQL_DOUBLE:
			*(double*)m_arCol[i].pAddr = (double)atof(m_sRowMysql[i]);
			break;
		case IES_SQL_TIMESTAMP:
			tmpTime.SetTime(m_sRowMysql[i]);
			tTime = (STimeInfo*)m_arCol[i].pAddr;
			tTime->nYear = tmpTime.GetYear();
			tTime->nMonth = tmpTime.GetMonth();
			tTime->nDay = tmpTime.GetDay();
			tTime->nHour = tmpTime.GetHour();
			tTime->nMinute = tmpTime.GetMinute();
			tTime->nSecond = tmpTime.GetSecond();
			break;
		case IES_SQL_STRING:
			strcpy((char*)m_arCol[i].pAddr, m_sRowMysql[i]);
			break;
		case IES_SQL_BINARY:
			memcpy(m_arCol[i].pAddr, m_sRowMysql[i], m_arCol[i].nSize);
			break;
		}
	}
	return true;
}

bool CIIMysqlDBI::PassPara()
{
	char tmpBuf[513];
	int id = 0;
	int len;
	CIITime tTime;
	CIIString tmp;

	for (int i = 0; i < m_arPara.GetNum(); i++)
	{
		len = 0;
		STimeInfo *pTime;

		tmp = "\'";

		id = m_pszSql.Find("?");

		switch (m_arPara[i].nType)
		{
		case IES_SQL_BOOL:			//
		case IES_SQL_UTINYINT:		//tinyint -> char
			sprintf(tmpBuf, "%d", *(Juint8*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_SMALLINT:		//smallint -> short
			sprintf(tmpBuf, "%d", *(Jint16*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_USMALLINT:		//smallint -> short
			sprintf(tmpBuf, "%d", *(Juint16*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_INT:			//int -> int
			sprintf(tmpBuf, "%d", *(Jint32*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_UINT:			//int -> int
			sprintf(tmpBuf, "%d", *(Juint32*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_FLOAT:		//float -> float
			sprintf(tmpBuf, "%f", *(float*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_DOUBLE:		//double -> double
			sprintf(tmpBuf, "%f", *(double*)(m_arPara[i].pAddr));
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_TIMESTAMP:	//timestamp -> STimeInfo
			pTime = (STimeInfo*)m_arPara[i].pAddr;
			sprintf(tmpBuf, "\'%04d-%02d-%02d %02d:%02d:%02d\'", pTime->nYear, pTime->nMonth,
					pTime->nDay, pTime->nHour, pTime->nMinute, pTime->nSecond);
			m_pszSql.ReplaceAt(id, 1, tmpBuf);
			break;
		case IES_SQL_STRING:		//字符串
			strncpy(tmpBuf, (char*)m_arPara[i].pAddr, m_arPara[i].nSize);
			tmp += tmpBuf;
			tmp += "\'";
			m_pszSql.ReplaceAt(id, 1, tmp.GetBuf());
			break;
		case IES_SQL_BINARY:		//二进制
			mysql_escape_string(tmpBuf, (char*)m_arPara[i].pAddr, m_arPara[i].nSize);
			tmp += tmpBuf;
			tmp += "\'";
			m_pszSql.ReplaceAt(id, 1, tmp.GetBuf());
			break;
		}
	}
	return true;
}

int CIIMysqlDBI::Exec()
{
	int nRet = 1;
	if (!m_bIsOpenDB)
	{
		nRet = 0;
		goto CLEAR;
	}

	if (!PassPara())
	{
		nRet = 0;
		goto CLEAR;
	}

	if (mysql_query(m_pConnMysql, m_pszSql.GetBuf()) != 0)
	{
		nRet = 0;
		goto CLEAR;
	}
	
	if (m_nSqlType == 1)		//SELECT语句
	{
		m_pResMysql = mysql_store_result(m_pConnMysql);

		if (m_pResMysql == 0)
		{
			nRet = 0;
			goto CLEAR;
		}

		m_nRowCount = (int)mysql_num_rows(m_pResMysql);
		return 1;
	}
	else if (m_nSqlType == 2)	//UPDATE语句
	{
		if (mysql_affected_rows(m_pConnMysql) == 0)
			nRet = -1;
	}

CLEAR:
	m_pszSql = m_pszSqlBak;
	m_arPara.Empty();
	return nRet;
}

bool CIIMysqlDBI::Fetch()
{
	bool bRet;

	if (!m_bIsOpenDB)
		return false;

	m_nRowId++;
	if (m_nRowId > m_nRowCount)
	{
		m_bIsEnd = true;
		return true;
	}

	m_sRowMysql = mysql_fetch_row(m_pResMysql);

	if (m_sRowMysql == NULL)
	{
		m_bIsEnd = true;
		return false;
	}
	m_bIsEnd = false;
	
	bRet = PassCol();
	return bRet;
}

bool CIIMysqlDBI::BeginTrans()
{
    if (mysql_query(m_pConnMysql, "BEGIN WORK"))
		return false;
    return true;
}

bool CIIMysqlDBI::Commit()
{
    if (mysql_query(m_pConnMysql, "COMMIT"))
		return false;
    return true;
}

bool CIIMysqlDBI::Rollback()
{
    if (mysql_query(m_pConnMysql, "ROLLBACK"))
		return false;
    return true;
}

//=======================================================================
// 接口实现
//=======================================================================
extern "C" void* ii_open(const char *i_pszSrv, const char *i_pszIpAddr,
						const char *i_pszDBName, const char *i_pszUser, const char *i_pszPwd)
{
	CIIMysqlDBI *t_pDatabase = new CIIMysqlDBI;

	if (!t_pDatabase->Open(i_pszIpAddr, i_pszDBName, i_pszUser, i_pszPwd))
	{
		delete t_pDatabase;
		return NULL;
	}
	return t_pDatabase;
}

extern "C" void ii_close(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	t_pDatabase->Close();

	if (t_pDatabase != NULL)
		delete t_pDatabase;
}

extern "C" bool ii_execute_sql(const void *i_pDatabase, const char *i_pszSqlStr)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->ExecuteSQL(i_pszSqlStr);
}

extern "C" bool ii_is_table_exist(const void *i_pDatabase, const char *i_szTableName)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->IsTableExist(i_szTableName);
}

extern "C" bool ii_prepare_sql(const void *i_pDatabase, const char *i_pStringSQL)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->PrepareSQL(i_pStringSQL);
}

extern "C" bool ii_bind_col(const void *i_pDatabase, int i_nColType,
								int i_nColSize, void *i_pColAddr)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->BindCol(i_nColType, i_nColSize, i_pColAddr);
}

extern "C" bool ii_bind_para(const void *i_pDatabase, int i_nParaType,
								int i_nParaSize, void *i_pParaAddr)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->BindPara(i_nParaType, i_nParaSize, i_pParaAddr);
}

extern "C" int ii_exec(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->Exec();
}

extern "C" bool ii_fetch(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->Fetch();
}

extern "C" bool ii_is_end(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->IsEnd();
}

extern "C" void ii_close_sql(const void *i_pDatabase, bool i_bCommit)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	t_pDatabase->CloseSQL();
}

extern "C" int ii_get_error_no(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->GetErrorNo();
}

extern "C" const char* ii_get_error_str(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->GetErrorStr();
}

extern "C" bool ii_begin_trans(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->BeginTrans();
}

extern "C" bool ii_commit(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->Commit();
}

extern "C" bool ii_rollback(const void *i_pDatabase)
{
	CIIMysqlDBI *t_pDatabase = (CIIMysqlDBI*)i_pDatabase;
	return t_pDatabase->Rollback();
}