windows 下C++操作Mysql的一些总结
///
一、windows 下C++连接Mysql:
首先完全安装Mysql(注意要选择完全安装,又不然将没有include和lib),并把Mysql的include目录和lib\opt引入 ,还有libmySQL.dll。
http://www.blogjava.net/pdw2009/archive/2008/03/09/184869.html
二、在用VC 连接Mysql 5.0 时出现如下错误解决方案
C:/MYSQL/INCLUDE/mysql_com.h(116) : error C2146: syntax error : missing ';' before identifier 'fd'
C:/MYSQL/INCLUDE/mysql_com.h(116) : error C2501: 'SOCKET' : missing storage-class or type specifiers
C:/MYSQL/INCLUDE/mysql_com.h(116) : error C2501: 'fd' : missing storage-class or type specifiers
C:/MYSQL/INCLUDE/mysql_com.h(180) : error C2065: 'SOCKET' : undeclared identifier
C:/MYSQL/INCLUDE/mysql_com.h(180) : error C2146: syntax error : missing ')' before identifier 's'
C:/MYSQL/INCLUDE/mysql_com.h(181) : error C2059: syntax error : ')'
参考下面文章
http://topic.csdn.net/u/20080121/21/ac0ac04d-8071-456c-93fa-207ba1d37840.html
在StdAfx.h 中加入如下几行
#include "winsock2.h"
#pragma comment(lib,"ws2_32")
mysql api用到socket的. 在头部加上这两句就好了
就可以。
///
三、解决Mysql链接错误:
通过myodbc无法链接至MySQL的错误:Client does not support authentication protocol requested by server; consider upgrading MySQL client
官方的说法是
MySQL 4.1 and up uses an authentication protocol based on a password hashing algorithm that is incompatible with that used by older
clients. .....
如果你升级mysql到4.1以上版本后遇到以上问题,请先确定你的mysql client 是4.1或者更高版本.(WINDOWS下有问题你就直接跳到下面看解决方法了,因为
MYSQL 在WINDOWS是client和server一起装上了的)
请使用以下两种方法之一
进入命令行下,转到MYSQL目录的BIN目录下,进入MYSQL命令行模式:
例:d:\mysql\bin>mysql -uroot -p123 (用户root,密码123)
其一:
mysql>SET PASSWORD FOR
>'root(用户名)'@'localhost(主机地址)' = OLD_PASSWORD('你的密码');
其二:
mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
-> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;
四、VC++连MySQL中要用到的MySQL函数:
MYSQL* mysql_init(NULL) //初始化一个MYSQL对象,后面的操作要用到
MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user, const char *passwd, const char *db, unsigned int port,
const char *unix_socket, unsigned long client_flag) //建立连接,db是要访问的数据库,unix_socket和client_flag通常为NULL就可以了.连接成功返
回第一个参数,否则失败
nt mysql_query(MYSQL *mysql, const char *stmt_str) //执行一个查询,成功时返回0
MYSQL_RES *mysql_store_result(MYSQL *mysql) //获取查询返回的结果集,失败时返回0
unsigned int mysql_num_fields(MYSQL_RES *result) //获取返回结果集的字段数量
MYSQL_ROW mysql_fetch_row(MYSQL_RES *result) //获取一行记录,并将移动到下个记录,返回NULL时则遍历完了所有行或出错.MYSQL_ROW类型是个字符串
数组,下标为[0,columnLength-1]
void mysql_free_result(MYSQL_RES *result) //释放结果集
void mysql_close(MYSQL *mysql) //关闭连接
//
五、一个简单的例子:mysql_real_connect(mysql,"localhost", "root", "rootpwd", "test",3306,NULL,CLIENT_MULTI_STATEMENTS )
当然我的测试数据库是test,里面有个user表。
#include <stdio.h>
#include <windows.h>
#include <winsock2.h> //必须在mysql.h之前,因为里面用到socket
#include "include\mysql.h"
#include <stdlib.h>
#pragma comment(lib,"ws2_32")
#pragma comment(lib, "lib/libmysql.lib")
MYSQL *mysql; //mysql连接
char query[1024]; //查询语句
void QueryAll()
{
int t;
MYSQL_RES *recordSet; //这个结构代表返回行的一个查询结果集
MYSQL_ROW row; //一个行数据的类型安全(type-safe)的表示
strcpy(query,"select * from user");
t=mysql_real_query(mysql,query,(unsigned int)strlen(query));
if(t){
printf("异常1: %s\n",mysql_error(mysql));
}/*else printf("[%s] 构建成功 \n",query);*/
recordSet = mysql_store_result(mysql);
long count_res = mysql_num_rows(recordSet);
printf( "%ld records found.\n" ,count_res);
long count_field = mysql_num_fields(recordSet);
MYSQL_FIELD * field;
for(int i=0;i<count_field;i++)
{
field = mysql_fetch_field_direct(recordSet, i);
printf("%s\t", field->name);
}
printf("\n");
while(row=mysql_fetch_row(recordSet)) //获取下一条记录
{
for(t=0;t<mysql_num_fields(recordSet);t++) //获取字段数目
{
printf("%s\t",row[t]);
}
printf("\n");
}
mysql_free_result(recordSet);//释放结果集
}
bool InsertUser()
{
int ret;
strcpy(query,"insert into user values(id,'koko','123456789')");
ret=mysql_real_query(mysql,query,(unsigned int)strlen(query));
if(ret){
printf("异常2: %s\n",mysql_error(mysql));
return false;
}/*else printf("[%s] 构建成功 \n",query);*/
return true;
}
bool UpdateUser()
{
int ret;
strcpy(query,"update user set upass='weizengke' where uname='koko'");
ret=mysql_real_query(mysql,query,(unsigned int)strlen(query));
if(ret)
{
printf("异常3: %s\n",mysql_error(mysql));
return false;
}/*else printf("[%s] 构建成功 \n",query);*/
return true;
}
bool deleteUser()
{
int ret;
strcpy(query,"delete from user where uname='koko'");
ret=mysql_real_query(mysql,query,(unsigned int)strlen(query));
if(ret)
{
printf("异常: %s\n",mysql_error(mysql));
return false;
}/*else printf("[%s] 构建成功 \n",query);*/
return true;
}
void Init() //初始化mysql,并设置字符集
{
mysql=mysql_init((MYSQL*)0);
if(mysql!=0 && !mysql_real_connect(mysql,"localhost", "root", "rootpwd", "test",3306,NULL,CLIENT_MULTI_STATEMENTS )){
printf( "Error connecting to database: %s\n",mysql_error(mysql));
return ;
}
strcpy(query,"SET CHARACTER SET GBK"); //设置编码 gbk
int t=mysql_real_query(mysql,query,(unsigned int)strlen(query));
if(t){
printf("编码设置失败\n");
return ;
}
}
int main()
{
HINSTANCE hDll=LoadLibrary("libmySQL.dll");
if (!hDll){
printf("加载DLL失败");
return o;
}
Init();
///查询操作
QueryAll();
//插入
// printf("-----------------\ndo inser\n");
// InsertUser();
// QueryAll();
//修改
// printf("-----------------\ndo update\n");
// UpdateUser();
// QueryAll();
//删除
// printf("-----------------\ndo del\n");
// deleteUser();
// QueryAll();
mysql_close(mysql); //关闭连接
return 0;
}