Connecting to MySQL in C++

Question

I am trying to learn C++ and I am having a bit of nightmare doing a test where I connect to a MySQL database.

I've had issues with the MySQL connector not linking properly then was getting issues related to relocation truncated to fitr_x86_64_32 against symbol.

I think I have fixed that by adding a compiler flag and now the app successfully builds and links.

When I run the app, it gets as far as calling get_driver_instance but then it exits. No exception is thrown, no errors nothing just exit code 0.

Below is my DBManager class

#include "DBConnectionManager.h"

using namespace std;

DBConnectionManager::DBConnectionManager() {
    cout << "Starting DBConnectionManager - Updated" << endl;
    try {
        cout << "Getting driver instance" << endl;
        driver = get_driver_instance();
        cout << "Got driver instance" << endl;
        conn = driver->connect("tcp://127.0.0.1:3306", "root", "password");
        conn->setSchema("bugs");
        cout << "Connected to database" << endl;
    }
    catch (SQLException ex) {
        cout << "Error connecting to DB: " << ex.what() << endl;
    }
    catch (...) {
        cout << "Something has gone wrong" << endl;
    }
}

Below is the header file

#ifndef MYSQLTEST_DBCONNECTIONMANAGER_H
#define MYSQLTEST_DBCONNECTIONMANAGER_H
#include <driver.h>
#include <exception.h>
#include <resultset.h>
#include <statement.h>

using namespace sql;

class DBConnectionManager
{
private:
    sql::Driver *driver;
    sql::Connection *conn;
    sql::Statement *statement;
    sql::ResultSet *res;
public:
    DBConnectionManager();
    void performSql();
};
#endif //MYSQLTEST_DBCONNECTIONMANAGER_H

Below is my main method

#include "DBConnectionManager.h"

int main() {
    DBConnectionManager dbConnectionManager;
    dbConnectionManager.performSql();
    return 0;
}

Below is my CMakeLists.txt file

cmake_minimum_required(VERSION 3.6)
project(MySQLTest)

include_directories("C:\\Program Files\\MySQL\\MySQL Connector C++ 1.1.7\\include\\cppconn" "C:\\Program Files\\MySQL\\MySQL Connector C++ 1.1.7\\lib\\opt")

SET(GCC_COVERAGE_LINK_FLAGS    "-m64 -Wl,--image-base -Wl,0x10000000 -lpthread -pthread")
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -std=c++11 -m64 -Wl,--image-base -Wl,0x10000000 -lpthread -pthread ")

set(SOURCE_FILES main.cpp DBConnectionManager.cpp)
add_executable(MySQLTest ${SOURCE_FILES})
add_library(mysqlcppconn.lib)

set_target_properties(MySQLTest PROPERTIES LINKER_LANGUAGE CXX)
set_target_properties(mysqlcppconn.lib PROPERTIES LINKER_LANGUAGE CXX)

target_link_libraries(MySQLTest "C:\\Program Files\\MySQL\\MySQL Connector C++ 1.1.7\\lib\\opt\\mysqlcppconn.lib")

When I create the instance of my DBConnectionManager class it successfully calls the query and prints Starting DBConnectionManager - Updated followed by Getting Driver Instance but then it exits with Process finished with exit code 0 with no clues as to what went wrong.

Update

I'm finally getting somewhere. I found there are some MySQL client libraries within Cygwin so I have download them and referenced them in the cmake file.

My cmake file now looks like this:

cmake_minimum_required(VERSION 3.6)
project(MySQLTest)

SET(CPPCONN_PUBLIC_FUNC=)

SET(GCC_COVERAGE_LINK_FLAGS    "-g -m64 -DCPPCONN_PUBLIC_FUNC= -Dmysqlcppconn_EXPORTS -lpthread -pthread -Wl,--image-base -Wl,0x10000000  -lz")
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -DCPPCONN_PUBLIC_FUNC= -Dmysqlcppconn_EXPORTS -std=c++11 -g -m64 -Wl,--image-base -Wl,0x10000000  -lpthread -pthread  -lz")

include_directories("C:/mysql_connector/include")
include_directories("C:/boost_1_61_0")

set(BOOST_INCLUDE_DIR C:/boost_1_61_0)
set(BOOST_LIBRARY_DIR C:/boost_1_61_0/libs)
set(SOURCE_FILES main.cpp DBConnectionManager.cpp)

add_executable(MySQLTest ${SOURCE_FILES})

find_package(Boost COMPONENTS REQUIRED)

link_directories(C:/mysql_connector/lib)

target_link_libraries(MySQLTest "C:/mysql_connector/lib/mysqlcppconn.dll"  "C:/Program Files/MySQL/MySQL Server 5.7/lib/libmysql.dll" "C:/mysql_connector/lib/libmysqlclient.dll.a" "C:/mysql_connector/lib/libmysqlclient_r.dll.a" ${Boost_LIBRARY_DIR})

Notice how I have linked the libraries libmysqlclient.dll.a and libmysqlclient_r.dll.a which is what I got from Cygwin.

When I run the app now it successfully gets the driver instance and to the console is outputted

Starting DBConnectionManaged - Updated
Getting driver instance
Got driver instance

But when I try and connect with driver->connect I then get the following error

0 [main] MySQLTest 2976 C:\Users\Chris\.CLion2016.2\system\cmake\generated\MySQLTest-8702ae13\8702ae13\Debug\MySQLTest.exe: *** fatal error - Internal error: TP_NUM_C_BUFS too small: 50

When I put it through the debugger, it fails on the driver->connect with

gdb: unknown target exception 0xe06d7363 at 0x7fff11347788

Program received signal ?, Unknown signal.
0x00007fff11347788 in RaiseException () from /cygdrive/c/WINDOWS/System32/KERNELBASE.dll

Update 2

Everything I've read points the mysql connector binaries should work fine, so I started again. Below is now the contents of my cmake file

cmake_minimum_required(VERSION 3.6)
project(MySQLTest)


#add_compile_options("-v")

SET(GCC_COVERAGE_LINK_FLAGS )
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -std=c++11")

set(BOOST_INCLUDE_DIR C:/boost_1_61_0)
set(BOOST_LIBRARY_DIR C:/boost_1_61_0/libs)

include_directories("C:/Program\ Files/MySQL/MySQL\ Connector\ C++\ 1.1.7/include" "C:/Program\ Files/MySQL/MySQL\ Connector\ C++\ 1.1.7/include/cppconn" ${BOOST_INCLUDE_DIR})


set(SOURCE_FILES main.cpp DBConnectionManager.cpp)

add_executable(MySQLTest ${SOURCE_FILES})

find_package(Boost COMPONENTS REQUIRED)

link_directories(C:/Program\ Files/MySQL/MySQL\ Connector\ C++\ 1.1.7/lib/opt)

target_link_libraries(MySQLTest C:/Program\ Files/MySQL/MySQL\ Connector\ C++\ 1.1.7/lib/opt/mysqlcppconn.lib ${Boost_LIBRARY_DIR})

Now when I compile I get the original error

C:/Program Files/MySQL/MySQL Connector C++ 1.1.7/lib/opt/mysqlcppconn.lib(mysqlcppconn.dll.b):(.text+0x2): relocation truncated to fit: R_X86_64_32 against symbol `__imp_get_driver_instance' defined in .idata$5 section in C:/Program Files/MySQL/MySQL Connector C++ 1.1.7/lib/opt/mysqlcppconn.lib(mysqlcppconn.dll.b)

That sounds like to me like my app is compiling as 32 bit instead of 64 bit. As a test I ran the following code:

cout << "Int size is: " << sizeof(int) << endl;

The code above prints 4 (shouldn't it be 8 if it was compiled as 64 bit).

If my thinking is correct, why isn't it compiling it as 64 bit, I've tried setting the compiler flag -m64 but makes no difference. I've installed the Cygwinx64 as well which CLion is using.

I knew exit code 0 means no error, although that's obviously not the case, I've updated my question to include main.cpp — Boardy, Sep 27 '16 at 17:34
One strange part is if the program crashed it should not have an exit code of 0. — drescherjm, Sep 27 '16 at 18:07
With the above said you probably should check if `driver` is a nullptr before `conn = driver->connect("tcp://127.0.0.1:3306", "root", "password");` — drescherjm, Sep 27 '16 at 18:08
When I step through it, the IDE says it received a segmentation fault on the `get_driver_instance` call, not sure why it doesn't say that though when you run it normally but don't see why it would segfault either, it matches several examples I've seen on Google :( — Boardy, Sep 27 '16 at 18:13
`mysqlcppconn.lib` shouldn't the library have a `.a` extension for mingw/gcc? I don't think you can use Visual Studio binaries. — drescherjm, Sep 27 '16 at 18:15
should it. I downloaded the C++ connector for windows but only comes with a .dll and .lib file no .a — Boardy, Sep 27 '16 at 18:16
I think you will run into trouble mixing mingw with Visual Studio binaries because of CRT and other incompatibilities. — drescherjm, Sep 27 '16 at 18:18
hmm didn't realise their's different connector libraries depending on the compiler — Boardy, Sep 27 '16 at 18:24
http://forums.mysql.com/read.php?117,425191,425191#msg-425191 — drescherjm, Sep 27 '16 at 18:26
I've found mingw works better than cygwin so I've changed my IDE to use minggw instead but now get this completely random error: In function `sql::SQLException::SQLException(sql::SQLException const&)': C:/PROGRA~1/MySQL/MYSQLC~1.7/include/cppconn/exception.h:65: undefined reference to `__imp__ZTVN3sql12SQLExceptionE' — Boardy, Sep 27 '16 at 21:37
One problem that can occur is when the tools you use to build your application are not compatible with the tools used to build the binary versions of MySQL Connector/C++. Ideally, you need to build your application with the same tools that were used to build the MySQL Connector/C++ binaries. — johnathan, Sep 29 '16 at 02:11
Yea I've been trying to do that too, not getting very far, there doesn't seem to be any documentation about building the source with cygwin on Windows, it only seems to talk about Linux — Boardy, Sep 29 '16 at 08:25
So first things first, C++ (along with any other native language) is not write once, run anywhere like Java, Python, Node.js et al are. If you have a windows build of a library, _it will only work on Windows_ - no ifs, ands or buts. Further, there are architectures that you have to take into consideration (x86/i386, x86_64, ia-32, ARM, etc.), and then on Windows you even have the MSVC runtime versions in certain cases. All of these are factors of which builds of a library you can link against. GCC/MinGW will not link against MSVC builds in _most_ cases. — Qix - MONICA WAS MISTREATED, Oct 03 '16 at 19:50
My suggestion is to find a better MySQL connector library - one where you have the source code and can build it yourself. Then link against it in your application. What's more is that if the library has a CMakeLists.txt file, it's as easy as doing `add_subdirectory()` and then a subsequent `target_link_libraries(your-app the-mysql-connector)`. This way, you can control things like debugging symbols, which will allow you to meaningfully step through your application and the library itself. — Qix - MONICA WAS MISTREATED, Oct 03 '16 at 19:52
Further, `sizeof(int)` is not a good representation of whether or not you're building a 32-bit or 64-bit binary. If anything, `sizeof(void*)` is going to get you _closer_, though IIRC the C++ spec states that pointer-types to different base types (e.g. `T*` vs `Y*`) can have different sizes based on their base types (though in most modern day compilers, they are uniform across all pointer types). — Qix - MONICA WAS MISTREATED, Oct 03 '16 at 19:53
I recommend trying to link against the connector library using the MSVC compilers first, to make sure the library itself isn't faulty. Then you can continue trying to get it to work on your non-standard compilers (MinGW or CYGWIN, etc.) — Qix - MONICA WAS MISTREATED, Oct 03 '16 at 19:54
I've given that go, I've got it working fine when using Visual Studio 2015. I think I might just use that for the time being, CLion I think are bringing in make in addition to cmake so might give it another go then — Boardy, Oct 03 '16 at 19:56

score 6 · Answer 1 · answered Jun 25 '17 at 15:31

You can use MySQL Connector C++

This is how to configure using Cmake

cmake_minimum_required(VERSION 3.7)

project(projectname)

set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -std=c++11")

Include the dirs where you extracted the mysql-connector-cpp - download

include_directories(/usr/local/include/mysql-connector-cpp/include)

Create a cmake variable containing the project source files

set(SOURCE_FILES main.cpp)

Create your executable

add_executable(projectname ${SOURCE_FILES})

Link after creating executable

target_link_libraries(projectname mysqlcppconn)

Your CMakeList should at least have this or look like this in this order

cmake_minimum_required(VERSION 3.7)
project(projectname)
set(CMAKE_CXX_FLAGS "${CMAKE_CXX_FLAGS} -std=c++11")
include_directories(/usr/local/include/mysql-connector-cpp/include)
set(SOURCE_FILES main.cpp)
add_executable(projectname ${SOURCE_FILES})
target_link_libraries(projectname mysqlcppconn)

score 2 · Answer 2 · answered Oct 06 '16 at 10:06

You can use mysql++ library to connect to mysql from c++.
For that install mysql++,
(Ubuntu)

sudo apt-get install mysql-server mysql-client
sudo apt-get install libmysqlclient-dev libmysql++-dev libmysqlcppconn-dev

(Mac)

brew install mysql++

include library by adding,

/usr/include/mysql++
/usr/include/mysql

and add linkers,

-lmysqlpp -lmysqlclient

Example code,

#include <iostream>
#include <string>
#include <mysql++.h>
#include <mysql.h>

#define dbname "dbname"
#define server "localhost"
#define user "username"
#define pass "password"

using namespace std;
using namespace mysqlpp;

int main() {
    Connection con(true);
    try {
        con.connect(dbname, server, user, pass);
        cout << "Connected to database\n";
        string s = "SELECT * FROM mirrors_mee WHERE id=1";
        Query q = con.query(s);
        StoreQueryResult sq = q.store();
        StoreQueryResult::iterator it;
        it = sq.begin();
        while (it != sq.end()) {
            Row row = *it;
            cout << row[5] << " " << row[6] << " " << row[7] << endl;
            it++;
        }
    } catch (Exception &e) {
        cout << e.what() << endl;
    }
    return 0;
}

You will get full documentation of the library here.

This is for Mac and Linux, I don't use Mac but already said I've got it working on Linux no problem. Its Windows where the problem is, I did try this library as well but it didn't seem to work, can't remember why now though — Boardy, Oct 06 '16 at 15:58

Connecting to MySQL in C++

Update

Update 2

2 Answers2

Your CMakeList should at least have this or look like this in this order