//H文件
#pragma once
#include "PublicDefine1.h"
//CSaveDataToDatabase类使用方法：
/*
1定义类：
CSaveDataToDatabase g_tCSaveDataToDatabase;
2 初始化
InitLoginStruct(m_tLoginStruct);
g_tCSaveDataToDatabase.SetLoginStruct(m_tLoginStruct);
3 初始化InitMySQL();
4 检测重号CheckIMEIRename();
5 保存数据SaveDataToMySQL();
6 结束数据库 g_tCSaveDataToDatabase.EndMySQL();
Date:20130702 auther:LIaoyuhuan
*/
static bool g_bInitMySQLInstance;
class CSaveDataToDatabase
{
public:
CSaveDataToDatabase(void);
~CSaveDataToDatabase(void);
protected:
SaveDataStruct m_tSaveDataStruct;
LoginStruct m_tLoginStruct;
public:
bool InitMySQLInstance(CString &sReturn,bool &bInitMySQLInstance = g_bInitMySQLInstance);//初始化MySQL
void SetLoginStruct(const LoginStruct & tLoginStruct){m_tLoginStruct = tLoginStruct;}
bool SaveData(const SaveDataStruct &tSaveDataStruct,CString &sReturn);
bool CheckReNumber(CString sIMEI,CString &sReturn);
void EndMySQL();
};
#if 0
//Login结构体
typedef struct LOGIN_STRUCT{
CString sHost;//localhost ip or name
CString sUserName;//
CString sPasswd;//
CString sDB;//database name
CString sTable;//database table
int iPort;//port
CString sUnixSocket;//通常为NULL，socket或命名管道
unsigned long ulClientFlag;//通常为0
LOGIN_STRUCT & operator=(const LOGIN_STRUCT& SrcStruct)
{
sHost= SrcStruct.sHost;
sUserName= SrcStruct.sUserName;
sPasswd= SrcStruct.sPasswd;
sDB= SrcStruct.sDB;
sTable= SrcStruct.sTable;
iPort= SrcStruct.iPort;
sUnixSocket= SrcStruct.sUnixSocket;
ulClientFlag= SrcStruct.ulClientFlag;
return *this;
}
}LoginStruct,*LoginStructPtr;
//Save Data struct
typedef struct SAVE_DATA_STRUCT{
CString sIMEI;//IMEI
CString sSN;//SN
CString sMAC;//MAC
SAVE_DATA_STRUCT & operator=(const SAVE_DATA_STRUCT& SrcStruct)
{
sIMEI= SrcStruct.sIMEI;
sSN= SrcStruct.sSN;
sMAC= SrcStruct.sMAC;
return *this;
}
}SaveDataStruct,*SaveDataStructPtr;
#endif
//CPP文件
#include "StdAfx.h"
#include "SaveDataToDatabase.h"
#include <WinSock.h>
#include "MySQL.h"
#include <iostream>
#include <string>
using namespace std;
#pragma comment(lib,"libmysql.lib")
#pragma comment(lib,"mysqlclient.lib")
CSaveDataToDatabase::CSaveDataToDatabase(void)
{
g_bInitMySQLInstance = false;
}
CSaveDataToDatabase::~CSaveDataToDatabase(void)
{
}
//mysql
MYSQL g_tMySQLDB;
bool PingMysql(MYSQL &tMySQL,CString &sReturn);
bool QueryMysql(MYSQL &tMySQL,const char *p,CString &sReturn);
bool QueryMysqlSelect(MYSQL &tMySQL,CString sTable,CString sIMEI,CString &sReturn);
bool QueryMysqlInsert(MYSQL &tMySQL,CString sTable,SaveDataStruct &tSaveDataStruct,CString &sReturn);
bool GetQueryResult(MYSQL &tMySQL,CString &sReturn);
bool QueryRealConnectMysql(MYSQL &tMySQL,const LoginStruct &tSrcLoginStruct,CString &sReturn);
bool CheckDoubleDesign(MYSQL &tMySQL,const CString &sTable,CString sIMEI,CString &sReturn);
bool SaveToSQL(MYSQL &tMySQL,SaveDataStruct &tSaveDataStruct,const CString &sTable,CString &sReturn);
//string
void GetStringDataByLoginStruct(string &sHost,string &sUserName,string &sPasswd,string &sDB,LoginStruct &tLoginStruct);
// CMySQLControlControlDlg 对话框
//
void GetStringDataByLoginStruct(string &sHost,string &sUserName,string &sPasswd,string &sDB,LoginStruct &tLoginStruct)
{
sHost= (CStringA)tLoginStruct.sHost.GetBuffer(tLoginStruct.sHost.GetLength());
sUserName = (CStringA)tLoginStruct.sUserName.GetBuffer(tLoginStruct.sUserName.GetLength());
sPasswd= (CStringA)tLoginStruct.sPasswd.GetBuffer(tLoginStruct.sPasswd.GetLength());
sDB= (CStringA)tLoginStruct.sDB.GetBuffer(tLoginStruct.sDB.GetLength());
tLoginStruct.sHost.ReleaseBuffer();
tLoginStruct.sUserName.ReleaseBuffer();
tLoginStruct.sPasswd.ReleaseBuffer();
tLoginStruct.sDB.ReleaseBuffer();
}
//ping MySQL
bool PingMysql(MYSQL &tMySQL,CString &sReturn)
{
int iPingReturn = -1;
for(unsigned int i=0;;i++)
{
iPingReturn = mysql_ping(&tMySQL);
if(i>=5)
{
sReturn.Format(_T("mysql_query() ping failed"));
TRACE(_T("mysql_query() ping failed\r\n"));
return false;
}else if(0 == iPingReturn)
{
sReturn.Format(_T("mysql_query() ping succeed"));
TRACE("mysql_query() ping MySQL succeed\r\n");
break;
}
Sleep(100);
}
return true;
}
//Query Mysql
bool QueryMysql(MYSQL &tMySQL,const char *p,CString &sReturn)
{
int iQueryReturn = -1;
for(unsigned int i=0;;i++)
{
sReturn.Empty();
iQueryReturn = mysql_query(&tMySQL,p);
if(i>=5)
{
sReturn.Format(_T("mysql_query() %s false"),p);
TRACE(sReturn);
return false;
}else if(0 == iQueryReturn)
{
sReturn.Format(_T("mysql_query() %s succeed"),p);
TRACE(sReturn);
break;
}
Sleep(100);
}
return true;
}
//query MySQL ：select
bool QueryMysqlSelect(MYSQL &tMySQL,CString sTable,CString sIMEI,CString &sReturn)
{
string strQuery;
CString sQuery;
sQuery.Format(_T("select * from %s where IMEI = '%s'"),sTable,sIMEI);
strQuery = (CStringA)sQuery.GetBuffer(sQuery.GetLength());
sQuery.ReleaseBuffer();
return QueryMysql(tMySQL,strQuery.c_str(),sReturn);
}
//insert data to mysql
bool QueryMysqlInsert(MYSQL &tMySQL,CString sTable,SaveDataStruct &tSaveDataStruct,CString &sReturn)
{
string strQuery;
CString sSaveData;
sReturn.Empty();
if(!PingMysql(tMySQL,sReturn))
return false;
sSaveData.Format(_T("insert into %s values(NULL,'%s','%s')"),sTable,tSaveDataStruct.sIMEI,tSaveDataStruct.sSN);
strQuery = (CStringA)sSaveData.GetBuffer(sSaveData.GetLength());
sSaveData.ReleaseBuffer();
return QueryMysql(tMySQL,strQuery.c_str(),sReturn);
}
//Query Result
bool GetQueryResult(MYSQL &tMySQL,CString &sReturn)
{
MYSQL_RES *result = NULL;
sReturn.Empty();
result = mysql_store_result(&tMySQL);
unsigned int rowcount = mysql_num_rows(result);
if(rowcount!=0)
{
sReturn.Format(_T("mysql_query() IMEI re-number test false"));
TRACE("mysql_query() IMEI re-number test false\r\n");
mysql_free_result(result);
return false;
}
sReturn.Format(_T("mysql_query() IMEI re-number test succeed"));
TRACE("mysql_query() IMEI re-number test succeed\r\n");
mysql_free_result(result);
return true;
}
//Query connect
bool QueryRealConnectMysql(MYSQL &tMySQL,const LoginStruct &tSrcLoginStruct,CString &sReturn)
{
LoginStruct tLoginStruct = tSrcLoginStruct;
string sHost,sUserName,sPasswd,sDB;
GetStringDataByLoginStruct(sHost,sUserName,sPasswd,sDB,tLoginStruct);
if(NULL != mysql_real_connect(&g_tMySQLDB, sHost.c_str(), sUserName.c_str(), sPasswd.c_str(), sDB.c_str(),tLoginStruct.iPort, NULL, tLoginStruct.ulClientFlag))
TRACE("mysql_real_connect() succeed");
else
{
sReturn.Format(_T("mysql_real_connect() failed"));
TRACE("mysql_real_connect() failed");
return false;
}
sReturn.Format(_T("mysql init succeed"));
TRACE("mysql init succeed");
return true;
}
bool CheckDoubleDesign(MYSQL &tMySQL,const CString &sTable,CString sIMEI,CString &sReturn)
{
if(!PingMysql(tMySQL,sReturn))
return false;
if(!QueryMysqlSelect(tMySQL,sTable,sIMEI,sReturn))
return false;
if(!GetQueryResult(tMySQL,sReturn))
return false;
return true;
}
bool SaveToSQL(MYSQL &tMySQL,SaveDataStruct &tSaveDataStruct,const CString &sTable,CString &sReturn)
{
string sTemp;
int iPingReturn=-1;
CString sSaveData;
sReturn.Empty();
if(!PingMysql(tMySQL,sReturn))
return false;
return QueryMysqlInsert(tMySQL,sTable,tSaveDataStruct,sReturn);
}
bool CSaveDataToDatabase::CheckReNumber(CString sIMEI,CString &sReturn)
{
//ensure connect
if(!g_bInitMySQLInstance)
{
g_bInitMySQLInstance = InitMySQLInstance(sReturn);
if(!g_bInitMySQLInstance)
return false;
}
return CheckDoubleDesign(g_tMySQLDB,m_tLoginStruct.sTable,sIMEI,sReturn);
}
bool CSaveDataToDatabase::InitMySQLInstance(CString &sReturn,bool &bInitMySQLInstance)//初始化MySQL
{
//init mysql
if (0 == mysql_library_init(0, NULL, NULL))
TRACE("mysql_library_init() succeed\r\n");
else
{
sReturn.Format(_T("mysql_library_init() failed"));
TRACE("mysql_library_init() failed\r\n");
mysql_close(&g_tMySQLDB);
return false;
}
//init data struct
if (NULL != mysql_init(&g_tMySQLDB))
TRACE("mysql_init() succeed\r\n");
else
{
sReturn.Format(_T("mysql_init() failed"));
TRACE("mysql_init() failed\r\n");
mysql_close(&g_tMySQLDB);
return false;
}
if (0 == mysql_options(&g_tMySQLDB, MYSQL_OPT_RECONNECT, "gbk"))
TRACE("mysql_options() succeed");
else
{
sReturn.Format(_T("mysql_options() init failed"));
TRACE("mysql_options() init failed");
mysql_close(&g_tMySQLDB);
return false;
}
bInitMySQLInstance = QueryRealConnectMysql(g_tMySQLDB,m_tLoginStruct,sReturn);
return bInitMySQLInstance;
}
bool CSaveDataToDatabase::SaveData(const SaveDataStruct &tSaveDataStruct,CString &sReturn)
{
m_tSaveDataStruct = tSaveDataStruct;
//ensure connect
if(!g_bInitMySQLInstance)
{
if(!InitMySQLInstance(sReturn))
return false;
}
return SaveToSQL(g_tMySQLDB,m_tSaveDataStruct,m_tLoginStruct.sTable,sReturn);
}
void CSaveDataToDatabase::EndMySQL()
{
mysql_close(&g_tMySQLDB);
}