Working with MySQL in VC++

OVERVIEW

One of the chief advantages of using Mysql databases in VC++ is that it is incredibly fast. Since almost every application which uses MySQL uses a server language or a high-level language which goes through several layers of the software stack to work with MySQL, the resulting execution is not really fast. In fact, it is well known that database operations are among the slowest parts of any application. But since modern hardware is cheap and fast and so are network speeds, users and developers never really feel that the database is slow and in most cases, the speed is perfectly fine for most use-cases. But the first time you run an SQL query in C++, you find out how fast the database can actually be. Queries which would take a few seconds to run on a server, execute almost instantly when using VC++.

SETTING IT UP

As with all things related to C++ , setting up the whole MySQL Connector library is a little messy and it took me a couple of days to get it right, because there is so much conflicting info on the net regarding this.

Download in the C++ connector library from https://dev.mysql.com/downloads/connector/cpp/

Choose the Windows (x86, 64-bit) ZIP Archive for Release builds or Windows (x86, 64-bit) ZIP Archive Debug Binaries for Debug builds. This post assumes that we are using the Debug version. The only difference between the two zip files is that the file and folders will be slightly different. The process will remain the same.

Unzip the file into a folder. This will contain all the include,lib and DLL files required to build applications for MySQL. Assume the folder is d:\mysql-connector-c++-8.1.0-winx64. You can have your own folder, Just use your own folder name wherever it is mentioned below.

Now we will create an Empty C/C++ project for Console. Create an empty file called main.cpp. We will add the code later. The most important thing is to set up the correct folders and libraries. Note that we will be setting up the MySQL Connector library for static linking and not dynamic linking. That means we will link with the lib files. But during runtime we still need the supporting DLL files. The content below assumes you are doing a Debug build. Just change the folders as required for a Release Build.

Go to Project->Properties->VC++ Directories and add a new entry to Include Directories

Go to Project->Properties->C++->Preprocessor and add the following entry to Preprocessor Definitions

In Project->Properties->C++->Code Generation make sure Runtime Library is set to (/MDd) for Debug builds or (/MD) for Release builds.

Go to Project->Properties->Linker->General and add a new entry in Additional Library Directories

Go to Project->Properties->Linker->Input and add a new entry in Additional Dependencies

TESTING WITH CODE

In main.cpp, use the sample code below. You will have to change the mysql server connection details and the sql query as required for your database.

#include <stdlib.h>
#include <iostream>

using namespace std;



#include <jdbc/mysql_connection.h>
#include <jdbc/cppconn/connection.h>
#include <jdbc/cppconn/driver.h>
#include <jdbc/cppconn/resultset.h>
#include <jdbc/cppconn/statement.h>
#include <jdbc/cppconn/prepared_statement.h>


const std::string HOST = "localhost";
const std::string DB = "test-db";
const std::string USER = "root";
const std::string PWD = "password";

int main(int argc, char** argv) {
	
	cout << "Starting.." << endl;

	try {
		sql::Driver *driver = NULL;
		sql::Connection* conn = NULL;
		sql::Statement* stmt = NULL;
		sql::ResultSet* rst = NULL;

		driver = get_driver_instance();
		std::string connString = "tcp://" + HOST + ":3306";
		conn = driver->connect(connString, USER, PWD);
		cout << "Database server connected" << endl;

		conn->setSchema(DB);
		stmt = conn->createStatement();
		rst = stmt->executeQuery("select * from Graph");
		while (rst->next()) {
			cout << "ID=" << rst->getInt("id") << "," << rst->getString("name") << endl;
		}

		delete rst;
		delete stmt;
		delete conn;
		cout << "Closed connection" << endl;
	}
	catch (sql::SQLException& ex) {
		cout << "Error:" << ex.what() << " Code: " << ex.getErrorCode() << endl;

		return EXIT_FAILURE;
	}
	cout << "Ending" << endl;

	return EXIT_SUCCESS;

CONCLUSION

If everything is fine, the code will compile and run. You can find several code samples in https://dev.mysql.com/doc/connector-cpp/1.1/en/connector-cpp-examples-results.html