MySQL-Connector.C++笔记

MySQL-Connector.C++笔记

Author: kagula
Date: 2016-03-08

 

内容简介

  为了实现MySQL客户端代码的简洁和跨平台使用MySQL-Connector.C++同MySQL服务器进行交互。
不同于C接口C++接口只要编译器版本不同，就有可能导致程序运行错误，所以这里主要时间花在接口编译上。

 

开发环境的准备

MySql Server自带的Connector.C++ library 同  调用者的STL版本不一致，会导致输出字符串失败。

所以一般都需要重新编译MySQL Connector.C++ library.

从下面URL下载Connector.C++
http://dev.mysql.com/downloads/connector/cpp/
Select Platform:Source
分别为不同的OS下载下面两个文件
mysql-connector-c++-1.1.7.zip  for windows
mysql-connector-c++-1.1.7.tar.gz  for linux

 

Windows下的编译

环境：
[1]Windows 10
[2]Visual studio 2013 Update5
[3]Boost 1.57
我计算机中的安装位置“D:\sdk\boost_1_57_0”
[4]MySQL 5.7 64bits
我计算机中的安装位置“C:\Program Files\MySQL\MySQL Server 5.7”
[5]mysql-connector-c++-1.1.7.zip  for windows
D:\sdk\mysql-connector-c++-1.1.7


第一步：使用CMake GUI
因为装的MySQL Server是64位的,所以只能选择“Visual Studio 12 2013 Win64”
添加或设置下面三个Entry
BOOST_ROOT=D:\sdk\boost_1_57_0
MYSQL_INCLUDE_DIR=C:/Program Files/MySQL/MySQL Server 5.7/include
MYSQL_LIB=C:/Program Files/MySQL/MySQL Server 5.7/lib/libmysql.lib
Generate后打开sln，分别编译出Debug、Release版本。


第二步：为MySQL测试项目设置include search path
C:\Program Files\MySQL\Connector.C++ 1.1
或
D:\sdk\mysql-connector-c++-1.1.7\driver
D:\sdk\mysql-connector-c++-1.1.7\cppconn


 and library search path
D:\sdk\mysql-connector-c++-1.1.7\build_vs2013\driver\Debug 对应 Debug mode
D:\sdk\mysql-connector-c++-1.1.7\build_vs2013\driver\Release 对应 Release mode
编译出来mysqlcppconn.dll复制到MySQL测试项目路径下。
MySQL Server安装路径下的C:\Program Files\MySQL\MySQL Server 5.7\lib\libmysql.dll复制到MySQL测试项目路径下。


第三步：如果，服务器和客户端不在同一个机器上，查看附录[2]给远程MySQL服务器授权。


第四步-final step：运行测试代码(Debug mode)成功。
 

CentOS下的编译

[1]CentOS7
[2]GCC 4.8.5
[3]Boost 1.57
[4]MySQL 5.7（必须是这个版本，否则Connector.C++ 1.1.7编译不过去）


第一步：
参考资料[3]卸载老的MySQL
第二步：
CentOS7下安装MysQL 5.7
#http://dev.mysql.com/downloads/repo/yum/
wget http://repo.mysql.com/mysql57-community-release-el7-7.noarch.rpm
rpm -ivh mysql57-community-release-el7-7.noarch.rpm
yum install mysql mysql-server mysql-devel
第三步：

使用CMake编译&安装 mysql-connector-c++-1.1.7

CMakeLists.txt

 

project(TestMySQL)
cmake_minimum_required(VERSION 2.8) #要求CMake的最低版本为2.8

SET( CMAKE_VERBOSE_MAKEFILE ON )    

aux_source_directory(. DIR_SRCS)    

#MySQL Connector.c++
INCLUDE_DIRECTORIES(/usr/local/include/cppconn)
LINK_DIRECTORIES(/usr/local/lib64)

add_executable(TestMySQL ${DIR_SRCS} )  
TARGET_LINK_LIBRARIES(TestMySQL mysqlcppconn) 

 

 

 

第四步-最后一步：
使用CMakeLists.txt把测试代码移植到CentOS7。
如果，服务器和客户端不在同一个机器上，查看附录[2]给远程MySQL服务器授权。

 

 

 

 

 

MySQL服务端测试代码

#include <iostream>
using namespace std;

#include <mysql_driver.h>
#include <cppconn/statement.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/prepared_statement.h>


/*
WIN32表示我们在Win OS下编译，不代表程序是32bits的。
*/

#ifdef WIN32
#pragma comment(lib,"mysqlcppconn.lib")
#endif // WIN32


void TestRunStatement(sql::Connection *con)
{
	sql::Statement *stmt;
	stmt = con->createStatement();	
	//stmt->execute("USE kagulatestschema");//kagulatestschema是我新建的schema名称
	stmt->execute("DROP TABLE IF EXISTS test");
	stmt->execute("CREATE TABLE test(id INT, label VARCHAR(32))");
	stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");
	stmt->execute("INSERT INTO test(id, label) VALUES (2, 'kagula')");

	delete stmt;
}

void TestFetchingResults(sql::Connection *con)
{
	sql::Statement *stmt;
	sql::ResultSet  *res;
	stmt = con->createStatement();
	//stmt->execute("USE kagulatestschema");
	res = stmt->executeQuery("SELECT id, label FROM test ORDER BY id ASC");
	while (res->next()) {
		// You can use either numeric offsets...
		cout << "id = " << res->getInt(1); // getInt(1) returns the first column
		// ... or column names for accessing results.
		// The latter is recommended.
		cout << ", label = '" << res->getString("label") << "'" << endl;
	}

	delete res;
	delete stmt;
}

void TestPreparedStatements(sql::Connection *con)
{
	sql::PreparedStatement  *prep_stmt;

	//before new parepareStatement, must choice schema.
	//sql::Statement *stmt;
	//stmt = con->createStatement();
	//stmt->execute("USE kagulatestschema");
	//delete stmt;
	
	//
	prep_stmt = con->prepareStatement("INSERT INTO test(id, label) VALUES (?, ?)");

	prep_stmt->setInt(1, 10);
	prep_stmt->setString(2, "a10");
	prep_stmt->execute();

	prep_stmt->setInt(1, 11);
	prep_stmt->setString(2, "a11");
	prep_stmt->execute();

	delete prep_stmt;
}

void TestPreparedStatements2UpdateAndQuery(sql::Connection *con)
{
	sql::PreparedStatement  *pstmt;
	/* '?' is the supported placeholder syntax */
	pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");
	for (int i = 100; i <= 110; i++) {
		pstmt->setInt(1, i);
		pstmt->executeUpdate();
	}
	delete pstmt;

	/* Select in ascending order */
	pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");
	sql::ResultSet  *res = pstmt->executeQuery();

	//Fetch in default order.
	//res->beforeFirst();//这个是默认返回，所以可注释。
	while (res->next())
		cout << "\t... MySQL counts: " << res->getInt("id") << endl;

	/* Fetch in reverse = descending order! */
	res->afterLast();
	while (res->previous())
		cout << "\t... MySQL counts reverse: " << res->getInt("id") << endl;
	delete res;

	delete pstmt;
}

int main(int argc, char * argv[])
{
	sql::mysql::MySQL_Driver *driver;
	sql::Connection *con;

	driver = sql::mysql::get_mysql_driver_instance();
#ifdef WIN32
	con = driver->connect("tcp://127.0.0.1:3306", "root", "123456");
#else
	con = driver->connect("tcp://192.168.0.113:3306", "root", "123456");
#endif

	if (!con->isValid())
	{
		cout << "Connect to MySQL Server failed!" << endl;
		goto _END;
	}

	try
	{
		/* Connect to the MySQL test database */
		con->setSchema("kagulatestschema");

		//TestRunStatement(con);
		//TestFetchingResults(con);
		//TestPreparedStatements(con);
		TestPreparedStatements2UpdateAndQuery(con);
	}
	catch (sql::SQLException e)
	{
		cout << "# ERR: SQLException in " << __FILE__;
		cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
		cout << "# ERR: " << e.what();
		cout << " (MySQL error code: " << e.getErrorCode();
		cout << ", SQLState: " << e.getSQLState() << " )" << endl;
	}

	cout << "Input any key will continue..." << endl;
	cin.get();
_END:
	delete con;

	return 0;
}

 

 

常见问题：

[1]如何查看MySQL程序(客户端)版本
mysql --version

[2]is not allowed to connect to this MySQL server解决办法

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

 

[3]在VS2017下对64位int类型做>=31的算术位移，只返回1。

VC2017自带的编译器只支持32位整数的算术位移运算，你指定数据源为unsigned long long或者程序的编译类型为64位，这些都没用，当<<或>>位移运算符后面跟的位移数>=31时，都会无效，所以当你位移需要>=31时，需要做特殊处理。

[3]使用sql::PreparedStatement的setUInt64方法设置33位整数值，MySQL C++ Driver返回超出数据表示范围的错误

超过32位整数的存储，你需要在MySQL中使用BigInt(64)而不是Int(64).

参考资料

[1]《MySQL Connector/C++ Developer Guide》
http://dev.mysql.com/doc/connector-cpp/en/
[2]《mysql之TIMESTAMP（时间戳）用法详解》
http://www.jb51.net/article/51794.htm
[3]《CentOS下MySQL 5.1升级到5.7》
http://www.linuxidc.com/Linux/2014-12/110266.htm