MySQL : C++ Connector vs ODBC

Question

I'm planning to start a web based project and i'm planning on MySQL as Database back-end. My Server side programming language is C++. I have used unixodbc before. Since i'm starting on a new project, i wanted to check if its good to continue with odbc or should try a different connector. I know that using odbc will offer portability among RDBMS. I really don't care about it, because if i switch, i will switch to cassandra or mongodb.

My Requirements for C++ Connection library:

1. Should perform well on large datasets, i expect the MySQL Data to grow much bigger.
2. I expect large number of incoming connections, so hope my server will open more MySQL connections.
3. Performance is more important.
4. Connection Pooling.
5. Feature set vs ODBC.

I'm planning only for Linux/Unix environment, preferably CentOS/FreeBSD. Please help me choose a good approach to connect to mysql from c++.

Answer 1

I like the SOCI library. http://soci.sourceforge.net/

It has a distinct EDSL (embedded domain specific language) which builds expression trees to compile the query and populate your objects, rather than pure SQL queries that send over new (variant) types and leaves you the job of figuring out how to convert them properly.

It's lightweight, adapts to your objects, very intuitive, connects to any type of database in case you'd like to change, supports even stored procedures. It's certainly convenient after a while, but there's a slight learning curve in regards to what you might have been used to.

Code example:

{

    session sql(mysql, "db=test user=root password='Ala ma kota'");

    boost::tuple<string, boost::optional<string>, int> person;

    sql << "select name, phone, salary from persons where ...",
        into(person);

    if (person.get<1>().is_initialized())
    {
        // the given person has a phone number
    }
    else
    {
        // this person does not have a phone number
    }

} // session closed at scope end

Answer 2

The Qt library has a nice SQL support in its QtSql module. The QtSql module has very good documentation, nice object-oriented API, different database backend support(MySQL, ODBC through unixODBC, PostgreSQL, ORACLE). You don't need to use Qt GUI features to use QtSql module.