VC++ mysql 操作
本地类:CMysqlHelper
#if !defined(CMysqlHelper_fdfdkjfdkfdjlkfds1111111) #define CMysqlHelper_fdfdkjfdkfdjlkfds1111111 #include "mysql.h" #if _MSC_VER > 1000 #pragma once #endif // _MSC_VER > 1000 struct TDataBaseConfig { CString strIp; CString strUser; CString strPwd; CString strDbNmae; }; class CMysqlHelper { public: CMysqlHelper(); ~CMysqlHelper(); bool Connect(); void Close(); char * Gbk2UTF8(const char * gb2312); bool ExeSqlCmd(CString cmd); void MysqlTest(); private: MYSQL * m_pConnect; TDataBaseConfig m_dbCfg; bool m_bConnect; }; #endif // !defined(AFX_CONTROL_H__01D78FC5_AA56_47DE_A532_73EC71F4C0C6__INCLUDED_) #include "stdafx.h" #include "MysqlHelper.h" #ifdef _DEBUG #undef THIS_FILE static char THIS_FILE[]=__FILE__; #define new DEBUG_NEW #endif ////////////////////////////////////////////////////////////////////// // Construction/Destruction ////////////////////////////////////////////////////////////////////// CMysqlHelper::CMysqlHelper() { m_dbCfg.strDbNmae = "hpw"; m_dbCfg.strIp = "127.0.0.1"; m_dbCfg.strPwd = "colibri"; m_dbCfg.strUser = "root"; m_bConnect = false; } CMysqlHelper::~CMysqlHelper() { } bool CMysqlHelper::Connect() { __try { m_pConnect = mysql_init((MYSQL*)0); if (m_pConnect != NULL && mysql_real_connect(m_pConnect, m_dbCfg.strIp, m_dbCfg.strUser, m_dbCfg.strPwd, m_dbCfg.strDbNmae, 3306, NULL, 0)) { if (!mysql_select_db(m_pConnect, m_dbCfg.strDbNmae)) { if (!mysql_set_character_set(m_pConnect, "utf8")) //设置编码为UTF8 { m_bConnect = true; return true; } } } else { return false; } } _except(1) { } return false; } void CMysqlHelper::Close() { m_bConnect = false; mysql_close(m_pConnect); } char * CMysqlHelper::Gbk2UTF8(const char* gb2312) { int len = MultiByteToWideChar(CP_ACP, 0, gb2312, -1, NULL, 0); wchar_t* wstr = new wchar_t[len + 1]; memset(wstr, 0, len + 1); MultiByteToWideChar(CP_ACP, 0, gb2312, -1, wstr, len); len = WideCharToMultiByte(CP_UTF8, 0, wstr, -1, NULL, 0, NULL, NULL); char* str = new char[len + 1]; memset(str, 0, len + 1); WideCharToMultiByte(CP_UTF8, 0, wstr, -1, str, len, NULL, NULL); if (wstr) delete[] wstr; return str; } //列名中有空格,需要`` 分隔(1左边的键) bool CMysqlHelper::ExeSqlCmd(CString cmd) { if (!m_bConnect) { Connect(); } __try { char * pchar = Gbk2UTF8((LPSTR)(LPCTSTR) cmd); //列名中有中文,格式转换 if (mysql_real_query(m_pConnect, pchar, strlen(pchar)) ==0) { return true; } } _except(1) { } m_bConnect = false; return false; }
void CMysqlHelper::MysqlTest() { //return; MYSQL * con; //= mysql_init((MYSQL*) 0); MYSQL_RES *res; MYSQL_ROW row; char tmp[400]; //database configuartion char dbuser[30] = "root"; char dbpasswd[30] = "colibri"; // it must be changed char dbip[30] = "localhost"; char dbname[50] = "localdb"; char tablename[50] = "shiftinfo"; char *query = NULL; int x; int y; int rt;//return value unsigned int t; int count = 0; con = mysql_init((MYSQL*)0); if (con != NULL && mysql_real_connect(con, dbip, dbuser, dbpasswd, dbname, 3306, NULL, 0)) { if (!mysql_select_db(con, dbname)) { TRACE("Select successfully the database!\n"); con->reconnect = 1; query = "set names \'GBK\'"; rt = mysql_real_query(con, query, strlen(query)); if (rt) { TRACE("Error making query: %s !!!\n", mysql_error(con)); } else { TRACE("query %s succeed!\n", query); } } } else { AfxMessageBox("Unable to connect the database,check your configuration!"); } //sprintf(tmp, "insert into %s values(%s,%d,%d)", tablename, "null", x, y); //注意如何向具有自增字段的数据库中插入记录 //sprintf(tmp, "insert into shiftinfo values(2,'2019-09-03 M',0,0)"); sprintf(tmp, "insert into %s (ShiftNo,YieldNum,OKCount) values(%s,%d,%d)", tablename, "'2019-09-03 M'", 0, 0); for (int i = 0; i < 10; i++) { rt = mysql_real_query(con, tmp, strlen(tmp)); if (rt) { TRACE("Error making query: %s !!!\n", mysql_error(con)); } else { TRACE("%s executed!!!\n", tmp); } } sprintf(tmp, "select * from %s", tablename); rt = mysql_real_query(con, tmp, strlen(tmp)); if (rt) { TRACE("Error making query: %s !!!\n", mysql_error(con)); } else { TRACE("%s executed!!!\n", tmp); } res = mysql_store_result(con);//将结果保存在res结构体中 while (row = mysql_fetch_row(res)) { for (t = 0; t<mysql_num_fields(res); t++) { TRACE("%s ", row[t]); } TRACE(".............\n"); count++; } /*sprintf(tmp, "truncate table %s", tablename); rt = mysql_real_query(con, tmp, strlen(tmp)); if (rt) { TRACE("Error making query: %s !!!\n", mysql_error(con)); } else { TRACE("%s executed!!!\n", tmp); }*/ sprintf(tmp, "delete from %s where id > 2", tablename); rt = mysql_real_query(con, tmp, strlen(tmp)); if (rt) { TRACE("Error making query: %s !!!\n", mysql_error(con)); } else { TRACE("%s executed!!!\n", tmp); } TRACE("number of rows %d\n", count); TRACE("mysql_free_result...\n"); mysql_free_result(res); mysql_close(con); }
if (!m_mySql.Connect()) { return -1; } CString cmd = "insert into productdata (日期,二维码,`Tab W1结果`,`Tab W1`) values('2020-08-22 17:00:00','1222','OK',123);"; m_mySql.ExeSqlCmd(cmd);
posted on 2020-08-24 17:10 strangeman 阅读(328) 评论(0) 编辑 收藏 举报