scbc_repos/CTSManager/CTSManager/Database.cpp

#include "pch.h"
#include "Database.h"

#define CHECKDB if (m_pConn == NULL || !m_bConnected) return FALSE

char* CDatabase::m_spImage = NULL;
CDatabase::CDatabase():m_pConn(NULL), m_pError(NULL), m_bConnected(FALSE)
{
	m_pConn = mysql_init(NULL);
}

CDatabase::~CDatabase()
{
	mysql_close(m_pConn);
	mysql_library_end();
}

BOOL CDatabase::SetBinaryField(std::string strCondition, void* pDataIn, int nDataLen)
{
	CHECKDB;
	BOOL bResult = FALSE;
	MYSQL_STMT* stmt = NULL;
	stmt = mysql_stmt_init(m_pConn);
	if (!stmt)
		return FALSE;

	if (!mysql_stmt_prepare(stmt, strCondition.c_str(), strCondition.length()))
	{
		MYSQL_BIND bind;
		memset(&bind, 0, sizeof(MYSQL_BIND));
		bind.buffer_type = MYSQL_TYPE_BLOB;
		bind.buffer_length = nDataLen;
		bind.buffer = pDataIn;
		if (!mysql_stmt_bind_param(stmt, (MYSQL_BIND*)&bind))
		{
			if (!mysql_stmt_execute(stmt))
			{
				bResult = TRUE;
			}
		}
	}

	mysql_stmt_close(stmt);

	return bResult;
}

BOOL CDatabase::GetBinaryField(std::string strCondition, void** lpDataOut, unsigned long& nDataLen)
{
	CHECKDB;
	BOOL bResult = FALSE;
	MYSQL_STMT* stmt = NULL;
	stmt = mysql_stmt_init(m_pConn);
	if (!stmt)
		return FALSE;

	MYSQL_RES* prepare_meta_result;
	if (!mysql_stmt_prepare(stmt, strCondition.c_str(), strCondition.length()))
	{
		if ( !mysql_stmt_execute(stmt) )
		{
			/* Fetch result set meta information */
			prepare_meta_result = mysql_stmt_result_metadata(stmt);
			if (prepare_meta_result)
			{
				/* Get total columns in the query */
				//int column_count = mysql_num_fields(prepare_meta_result);

				my_bool is_null;
				my_bool error;
				MYSQL_BIND bind[1];
				memset(bind, 0, sizeof(bind));

				bind[0].buffer_type = MYSQL_TYPE_BLOB;
				bind[0].buffer = 0;
				bind[0].buffer_length = 0;
				bind[0].is_null = &is_null;
				bind[0].length = &nDataLen; // 返回的缓存长度;
				bind[0].error = &error;

				/* Bind the result buffers */
				if (!mysql_stmt_bind_result(stmt, bind))
				{
					// 由于没有绑定缓冲区指针,第一次返回缓冲区大小;
					if (mysql_stmt_fetch(stmt))
					{
						if (nDataLen > 1)
						{
							*lpDataOut = new char[nDataLen];
							memset(*lpDataOut, 0, nDataLen);
							bind[0].buffer = *lpDataOut;
							bind[0].buffer_length = nDataLen;
							mysql_stmt_fetch_column(stmt, bind, 0, 0);
							bResult = TRUE;
						}
					}
				}

				/* Free the prepared result metadata */
				mysql_free_result(prepare_meta_result);
			}			
		}
	}

	/* Close the statement */
	mysql_stmt_close(stmt);

	return bResult;
}

BOOL CDatabase::Init(std::string host, std::string user, std::string password, std::string db)
{
	if (!m_pConn)
		return FALSE;

	m_pError = mysql_error(m_pConn);
	if (!mysql_real_connect(m_pConn, host.c_str(), user.c_str(), password.c_str(), db.c_str(), 0, NULL, 0)) {
#ifdef _DEBUG
		TRACE1(_T("mysql_real_connect error=%s\n"), m_pError ? m_pError: "");
#endif
		return FALSE;
	}

	return m_bConnected = TRUE;
}

BOOL CDatabase::ExcuteSQL(std::string sql)
{
	if (m_pConn == NULL || !m_bConnected)
		return FALSE;

	if (0 != mysql_query(m_pConn, sql.c_str())) {
#ifdef _DEBUG
		TRACE1(_T("ExcuteSQL error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

#if DEPRECATED
BOOL CDatabase::InserProvider(const STProvider& provider)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = {0};
	_stprintf_s(szSql, INSER_PROVIDER, provider.name.c_str(), provider.note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InserProvider error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::InserProvider(std::string name, std::string note)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_PROVIDER, name.c_str(), note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InserProvider error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::DeleteProvider(std::string name)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, DEL_PROVIDER, name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteProvider error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::UpdateProvider(std::string name, const STProvider& provider)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, MOD_PROVIDER, provider.name.c_str(), provider.note.c_str(), name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteProvider error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::QueryProvider(std::vector<STProvider>& vtProvider)
{
	CHECKDB;
	if (0 != mysql_query(m_pConn, QUERY_PROVIDER)) {
#ifdef _DEBUG
		TRACE1(_T("QueryProvider error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	// 获取表数据;
	MYSQL_RES* pData = mysql_store_result(m_pConn);
	if ( pData == NULL ) {
#ifdef _DEBUG
		TRACE1(_T("QueryProvider error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

#ifdef _DEBUG
	// 统计表字段;
	unsigned int nLen = mysql_num_fields(pData);
	// 字段长度是否一致;
	if (nLen != 2) {
		mysql_free_result(pData);
		return FALSE;
	}

	// 打印出字段名称;
	TCHAR szLog[MAX_PATH] = {0};
	for ( int i = 0; i < nLen; i++ ) {
		_stprintf_s(szLog, _T("字段名称：%s\n"), mysql_fetch_field(pData)->name);
		OutputDebugString(szLog);
	}
#endif

	// 遍历数据;
	MYSQL_ROW row;
	while ( (row = mysql_fetch_row(pData)) != NULL ) {
		STProvider provider;
		provider.name = row[0];
		provider.note = row[1];
		vtProvider.push_back(provider);
	}

	// 释放内存;
	mysql_free_result(pData);

	return TRUE;
}
#endif

BOOL CDatabase::InserSoc(const STSOC& soc)
{
	CHECKDB;
#if 0
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_SOC, soc.name.c_str(), soc.provider.c_str(), soc.note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InserSoc error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
#else
	MYSQL_STMT* stmt;
	MYSQL_BIND bind[3];
	TCHAR szQuery[MAX_PATH] = { 0 };
	strcpy_s(szQuery, _T("INSERT INTO soc(name, provider, note) VALUES(?,?,?);"));
	BOOL bResult = FALSE;
	//初始化stmt
	stmt = mysql_stmt_init(m_pConn);
	if (!stmt)
		return FALSE;

	//预处理语句
	if (mysql_stmt_prepare(stmt, szQuery, strlen(szQuery)))
		goto over;
	
	//初始化参数
	bind[0].buffer_type = MYSQL_TYPE_VARCHAR;
	bind[0].buffer = (void*)soc.name.c_str();
	bind[0].buffer_length = soc.name.size();
	bind[0].is_null = 0;

	bind[1].buffer_type = MYSQL_TYPE_VARCHAR;
	bind[1].buffer = (void*)soc.provider.c_str();
	bind[1].buffer_length = soc.provider.size();
	bind[1].is_null = 0;

	bind[2].buffer_type = MYSQL_TYPE_VARCHAR;
	bind[2].buffer = (void*)soc.note.c_str();
	bind[2].buffer_length = soc.note.size();
	bind[2].is_null = 0;

	//绑定参数123
	if (mysql_stmt_bind_param(stmt, bind))
		goto over;

	//执行预处理mysql语句
	if (!mysql_stmt_execute(stmt))
		bResult = TRUE;

over:
	mysql_stmt_close(stmt);

#ifdef _DEBUG
	if ( !bResult )
		TRACE1(_T("InserSoc error=%s\n"), m_pError ? m_pError : "");
#endif

	return bResult;
#endif
}

BOOL CDatabase::InserSoc(std::string name, std::string provider, std::string note)
{
	CHECKDB;
#if 0
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_SOC, name.c_str(), provider.c_str(), note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InserSoc error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
#else
	//https://dev.mysql.com/doc/c-api/5.6/en/mysql-stmt-execute.html
	MYSQL_STMT* stmt;
	MYSQL_BIND bind[3];
	TCHAR szQuery[MAX_PATH] = { 0 };
	strcpy_s(szQuery, _T("INSERT INTO soc(name, provider, note) VALUES(?,?,?);"));
	//初始化stmt
	BOOL bResult = FALSE;
	stmt = mysql_stmt_init(m_pConn);
	if (!stmt)
		return FALSE;

	//预处理语句
	if (mysql_stmt_prepare(stmt, szQuery, strlen(szQuery)))
		goto over;

	// 字段数量是否匹配;
	int param_count = mysql_stmt_param_count(stmt);
	if (param_count != 3)
		goto over;

	//初始化参数
	memset(bind, 0, sizeof(bind));
	bind[0].buffer_type = MYSQL_TYPE_VARCHAR;
	bind[0].buffer = (void*)name.c_str();
	bind[0].buffer_length = name.size();
	bind[0].is_null = 0;

	bind[1].buffer_type = MYSQL_TYPE_VARCHAR;
	bind[1].buffer = (void*)provider.c_str();
	bind[1].buffer_length = provider.size();
	bind[1].is_null = 0;

	bind[2].buffer_type = MYSQL_TYPE_VARCHAR;
	bind[2].buffer = (void*)note.c_str();
	bind[2].buffer_length = note.size();
	bind[2].is_null = 0;

	//绑定参数123
	if (mysql_stmt_bind_param(stmt, bind))
		goto over;
	
	//执行预处理mysql语句
	if (mysql_stmt_execute(stmt))
		goto over;

	// 影响的行数;
	int affected_rows = mysql_stmt_affected_rows(stmt);
	if (affected_rows == 1)
		bResult = TRUE;

over:
	mysql_stmt_close(stmt);

#ifdef _DEBUG
	if (!bResult)
		TRACE1(_T("InserSoc error=%s\n"), m_pError ? m_pError : "");
#endif

	return bResult;
#endif
}

BOOL CDatabase::DeleteSoc(std::string name)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, DEL_SOC, name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteSoc error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::UpdateSoc(std::string name, const STSOC& soc)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, MOD_SOC, soc.name.c_str(), soc.provider.c_str(), soc.note.c_str(), name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("UpdateSoc error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::QuerySoc(std::vector<STSOC>& vtSoc)
{
	CHECKDB;
	if (0 != mysql_query(m_pConn, QUERY_SOC)) {
#ifdef _DEBUG
		TRACE1(_T("QuerySoc error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	// 获取表数据;
	MYSQL_RES* pData = mysql_store_result(m_pConn);
	if (pData == NULL) {
#ifdef _DEBUG
		TRACE1(_T("QuerySoc error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

#ifdef _DEBUG
	// 统计表字段;
	unsigned int nLen = mysql_num_fields(pData);
	// 字段长度是否一致;
	if (nLen != 3) {
		mysql_free_result(pData);
		return FALSE;
	}

	// 打印出字段名称;
	TCHAR szLog[MAX_PATH] = { 0 };
	for (int i = 0; i < nLen; i++) {
		_stprintf_s(szLog, _T("字段名称：%s\n"), mysql_fetch_field(pData)->name);
		OutputDebugString(szLog);
	}
#endif

	// 遍历数据;
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(pData)) != NULL) {
		STSOC soc;
		soc.name = row[0];
		soc.provider = row[1];
		soc.note = row[2];
		vtSoc.push_back(soc);
	}

	// 释放内存;
	mysql_free_result(pData);

	return TRUE;
}


BOOL CDatabase::InsertBrand(const STBranch& brand)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_BRAND, brand.name.c_str(), brand.note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertBrand error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::InsertBrand(std::string name, std::string note)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_BRAND, name.c_str(), note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertBrand error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::DeleteBrand(std::string name)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, DEL_BRAND, name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteBrand error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::UpdateBrand(std::string name, const STBranch& brand)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, MOD_BRAND, brand.name.c_str(), brand.note.c_str(), name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("UpdateBrand error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::QueryBrand(std::vector<STBranch>& vtBrand)
{
	CHECKDB;
	if (0 != mysql_query(m_pConn, QUERY_BRAND)) {
#ifdef _DEBUG
		TRACE1(_T("QueryBrand error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	// 获取表数据;
	MYSQL_RES* pData = mysql_store_result(m_pConn);
	if (pData == NULL) {
#ifdef _DEBUG
		TRACE1(_T("QueryBrand error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

#ifdef _DEBUG
	// 统计表字段;
	unsigned int nLen = mysql_num_fields(pData);
	// 字段长度是否一致;
	if (nLen != 2) {
		mysql_free_result(pData);
		return FALSE;
	}

	// 打印出字段名称;
	TCHAR szLog[MAX_PATH] = { 0 };
	for (int i = 0; i < nLen; i++) {
		_stprintf_s(szLog, _T("字段名称：%s\n"), mysql_fetch_field(pData)->name);
		OutputDebugString(szLog);
	}
#endif

	// 遍历数据;
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(pData)) != NULL) {
		STBranch brand;
		brand.name = row[0];
		brand.note = row[1];
		vtBrand.push_back(brand);
	}

	// 释放内存;
	mysql_free_result(pData);

	return TRUE;
}

BOOL CDatabase::ImportLogo(std::string name, std::string file)
{
	if (!PathFileExists(file.c_str()))
		return FALSE;

	
	return 0;
}

BOOL CDatabase::ExportLogo(std::string name, std::string file)
{
	return 0;
}

BOOL CDatabase::InsertQuarter(const STQuarter& quarter)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_QUARTER, quarter.name.c_str(), quarter.scp.c_str(), quarter.note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertQuarter error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::InsertQuarter(std::string name, std::string scp, std::string note)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_QUARTER, name.c_str(), scp.c_str(), note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertQuarter error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::DeleteQuarter(std::string name)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, DEL_QUARTER, name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteQuarter error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::UpdateQuarter(std::string name, const STQuarter& quarter)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, MOD_QUARTER,quarter.name.c_str(), quarter.scp.c_str(), quarter.note.c_str(), name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("UpdateQuarter error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::QueryQuarter(std::vector<STQuarter>& vtQuarter)
{
	CHECKDB;
	if (0 != mysql_query(m_pConn, QUERY_QUARTER)) {
#ifdef _DEBUG
		TRACE1(_T("QueryQuarter error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	// 获取表数据;
	MYSQL_RES* pData = mysql_store_result(m_pConn);
	if (pData == NULL) {
#ifdef _DEBUG
		TRACE1(_T("QueryQuarter error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

#ifdef _DEBUG
	// 统计表字段;
	unsigned int nLen = mysql_num_fields(pData);
	// 字段长度是否一致;
	if (nLen != 3) {
		mysql_free_result(pData);
		return FALSE;
	}

	// 打印出字段名称;
	TCHAR szLog[MAX_PATH] = { 0 };
	for (int i = 0; i < nLen; i++) {
		_stprintf_s(szLog, _T("字段名称：%s\n"), mysql_fetch_field(pData)->name);
		OutputDebugString(szLog);
	}
#endif

	// 遍历数据;
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(pData)) != NULL) {
		STQuarter quarter;
		quarter.name = row[0];
		quarter.scp = row[1];
		quarter.note = row[2];
		vtQuarter.push_back(quarter);
	}

	// 释放内存;
	mysql_free_result(pData);

	return TRUE;
}

BOOL CDatabase::InsertServer(const STServer& Server)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_SERVER, Server.name.c_str(), Server.ip.c_str(), Server.type.c_str(), Server.user.c_str(), Server.password.c_str(),Server.note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertServer error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::InsertServer(std::string name, std::string ip, std::string type, std::string user, std::string password, std::string note)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_SERVER, name.c_str(), ip.c_str(), type.c_str(), user.c_str(), password.c_str(), note.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertServer error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::DeleteServer(std::string name)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, DEL_SERVER, name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteServer error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::UpdateServer(std::string name, const STServer& Server)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, MOD_SERVER, Server.name.c_str(), Server.ip.c_str(), Server.type.c_str(), Server.user.c_str(), Server.password.c_str(), Server.note.c_str(), name.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("UpdateServer error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::QueryServer(std::vector<STServer>& vtServer)
{
	CHECKDB;
	if (0 != mysql_query(m_pConn, QUERY_SERVER)) {
#ifdef _DEBUG
		TRACE1(_T("QueryServer error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	// 获取表数据;
	MYSQL_RES* pData = mysql_store_result(m_pConn);
	if (pData == NULL) {
#ifdef _DEBUG
		TRACE1(_T("QueryServer error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

#ifdef _DEBUG
	// 统计表字段;
	unsigned int nLen = mysql_num_fields(pData);
	// 字段长度是否一致;
	if (nLen != 6) {
		mysql_free_result(pData);
		return FALSE;
	}

	// 打印出字段名称;
	TCHAR szLog[MAX_PATH] = { 0 };
	for (int i = 0; i < nLen; i++) {
		_stprintf_s(szLog, _T("字段名称：%s\n"), mysql_fetch_field(pData)->name);
		OutputDebugString(szLog);
	}
#endif

	// 遍历数据;
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(pData)) != NULL) {
		STServer server;
		server.name = row[0];
		server.ip = row[1];
		server.type = row[2];
		server.user = row[3];
		server.password = row[4];
		server.note = row[5];
		vtServer.push_back(server);
	}

	// 释放内存;
	mysql_free_result(pData);

	return TRUE;
}

BOOL CDatabase::InsertUser(const STUser& user)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_USER, user.user.c_str(), user.password.c_str(), user.permission.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertUser error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::InsertUser(std::string user, std::string password, std::string permission)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, INSER_USER, user.c_str(), password.c_str(), permission.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("InsertUser error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::DeleteUser(std::string user)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, DEL_USER, user.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("DeleteUser error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::UpdateUser(std::string user, const STUser& stUser)
{
	CHECKDB;
	TCHAR szSql[MAX_PATH] = { 0 };
	_stprintf_s(szSql, MOD_USER, stUser.user.c_str(), stUser.password.c_str(), stUser.permission.c_str(), user.c_str());
	if (0 != mysql_query(m_pConn, szSql)) {
#ifdef _DEBUG
		TRACE1(_T("UpdateUser error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	return TRUE;
}

BOOL CDatabase::QueryUser(std::vector<STUser>& vtUser)
{
	CHECKDB;
	if (0 != mysql_query(m_pConn, QUERY_USER)) {
#ifdef _DEBUG
		TRACE1(_T("QueryUser error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

	// 获取表数据;
	MYSQL_RES* pData = mysql_store_result(m_pConn);
	if (pData == NULL) {
#ifdef _DEBUG
		TRACE1(_T("QueryUser error=%s\n"), m_pError ? m_pError : "");
#endif
		return FALSE;
	}

#ifdef _DEBUG
	// 统计表字段;
	unsigned int nLen = mysql_num_fields(pData);
	// 字段长度是否一致;
	if (nLen != 3) {
		mysql_free_result(pData);
		return FALSE;
	}

	// 打印出字段名称;
	TCHAR szLog[MAX_PATH] = { 0 };
	for (int i = 0; i < nLen; i++) {
		_stprintf_s(szLog, _T("字段名称：%s\n"), mysql_fetch_field(pData)->name);
		OutputDebugString(szLog);
	}
#endif

	// 遍历数据;
	MYSQL_ROW row;
	while ((row = mysql_fetch_row(pData)) != NULL) {
		STUser stUser;
		stUser.user = row[0];
		stUser.password = row[1];
		stUser.permission = row[2];
		vtUser.push_back(stUser);
	}

	// 释放内存;
	mysql_free_result(pData);

	return TRUE;
}

// 示例;
void test(MYSQL* mysql)
{
#define STRING_SIZE 50

#define SELECT_SAMPLE "SELECT col1, col2, col3, col4 \
                       FROM test_table"

	MYSQL_STMT* stmt;
	MYSQL_BIND    bind[4];
	MYSQL_RES* prepare_meta_result;
	MYSQL_TIME    ts;
	unsigned long length[4];
	int           param_count, column_count, row_count;
	short         small_data;
	int           int_data;
	char          str_data[STRING_SIZE];
	my_bool       is_null[4];
	my_bool       error[4];

	/* Prepare a SELECT query to fetch data from test_table */
	stmt = mysql_stmt_init(mysql);
	if (!stmt)
	{
		fprintf(stderr, " mysql_stmt_init(), out of memory\n");
		exit(0);
	}
	if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
	{
		fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}
	fprintf(stdout, " prepare, SELECT successful\n");

	/* Get the parameter count from the statement */
	param_count = mysql_stmt_param_count(stmt);
	fprintf(stdout, " total parameters in SELECT: %d\n", param_count);

	if (param_count != 0) /* validate parameter count */
	{
		fprintf(stderr, " invalid parameter count returned by MySQL\n");
		exit(0);
	}

	/* Execute the SELECT query */
	if (mysql_stmt_execute(stmt))
	{
		fprintf(stderr, " mysql_stmt_execute(), failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	/* Fetch result set meta information */
	prepare_meta_result = mysql_stmt_result_metadata(stmt);
	if (!prepare_meta_result)
	{
		fprintf(stderr,
			" mysql_stmt_result_metadata(), \
           returned no meta information\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	/* Get total columns in the query */
	column_count = mysql_num_fields(prepare_meta_result);
	fprintf(stdout,
		" total columns in SELECT statement: %d\n",
		column_count);

	if (column_count != 4) /* validate column count */
	{
		fprintf(stderr, " invalid column count returned by MySQL\n");
		exit(0);
	}

	/* Bind the result buffers for all 4 columns before fetching them */

	memset(bind, 0, sizeof(bind));

	/* INTEGER COLUMN */
	bind[0].buffer_type = MYSQL_TYPE_LONG;
	bind[0].buffer = (char*)&int_data;
	bind[0].is_null = &is_null[0];
	bind[0].length = &length[0];
	bind[0].error = &error[0];

	/* STRING COLUMN */
	bind[1].buffer_type = MYSQL_TYPE_STRING;
	bind[1].buffer = (char*)str_data;
	bind[1].buffer_length = STRING_SIZE;
	bind[1].is_null = &is_null[1];
	bind[1].length = &length[1];
	bind[1].error = &error[1];

	/* SMALLINT COLUMN */
	bind[2].buffer_type = MYSQL_TYPE_SHORT;
	bind[2].buffer = (char*)&small_data;
	bind[2].is_null = &is_null[2];
	bind[2].length = &length[2];
	bind[2].error = &error[2];

	/* TIMESTAMP COLUMN */
	bind[3].buffer_type = MYSQL_TYPE_TIMESTAMP;
	bind[3].buffer = (char*)&ts;
	bind[3].is_null = &is_null[3];
	bind[3].length = &length[3];
	bind[3].error = &error[3];

	/* Bind the result buffers */
	if (mysql_stmt_bind_result(stmt, bind))
	{
		fprintf(stderr, " mysql_stmt_bind_result() failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	/* Now buffer all results to client (optional step) */
	if (mysql_stmt_store_result(stmt))
	{
		fprintf(stderr, " mysql_stmt_store_result() failed\n");
		fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
		exit(0);
	}

	/* Fetch all rows */
	row_count = 0;
	fprintf(stdout, "Fetching results ...\n");
	while (!mysql_stmt_fetch(stmt))
	{
		row_count++;
		fprintf(stdout, "  row %d\n", row_count);

		/* column 1 */
		fprintf(stdout, "   column1 (integer)  : ");
		if (is_null[0])
			fprintf(stdout, " NULL\n");
		else
			fprintf(stdout, " %d(%ld)\n", int_data, length[0]);

		/* column 2 */
		fprintf(stdout, "   column2 (string)   : ");
		if (is_null[1])
			fprintf(stdout, " NULL\n");
		else
			fprintf(stdout, " %s(%ld)\n", str_data, length[1]);

		/* column 3 */
		fprintf(stdout, "   column3 (smallint) : ");
		if (is_null[2])
			fprintf(stdout, " NULL\n");
		else
			fprintf(stdout, " %d(%ld)\n", small_data, length[2]);

		/* column 4 */
		fprintf(stdout, "   column4 (timestamp): ");
		if (is_null[3])
			fprintf(stdout, " NULL\n");
		else
			fprintf(stdout, " %04d-%02d-%02d %02d:%02d:%02d (%ld)\n",
				ts.year, ts.month, ts.day,
				ts.hour, ts.minute, ts.second,
				length[3]);
		fprintf(stdout, "\n");
	}

	/* Validate rows fetched */
	fprintf(stdout, " total rows fetched: %d\n", row_count);
	if (row_count != 2)
	{
		fprintf(stderr, " MySQL failed to return all rows\n");
		exit(0);
	}

	/* Free the prepared result metadata */
	mysql_free_result(prepare_meta_result);


	/* Close the statement */
	if (mysql_stmt_close(stmt))
	{
		/* mysql_stmt_close() invalidates stmt, so call          */
		/* mysql_error(mysql) rather than mysql_stmt_error(stmt) */
		fprintf(stderr, " failed while closing the statement\n");
		fprintf(stderr, " %s\n", mysql_error(mysql));
		exit(0);
	}
}