C++ MySQL OTL Getting Started. ODBC Configuration & Errors

Question

I'm trying to connect to MySQL database with C++. I have never interfaced with a database with C++ prior to this.

My weapon of choice for that is OTL. My test project consists of one OTL header file and main.c. Nothing else.

I ran into an issue that I can't connect to the database (literally the first line of example code to interact with the database). Unfortunately, information about getting started is not very helpful, as it assumes a lot of previous knowledge and throws a lot of words at me. There is basically no zero to hero explanation, there is no "getting started" explanation (at least I failed to find any) and intensive internet search led me pretty much nowhere.

My endgoal: connect to the local database using @username @password, run a couple queries, disconnect. That's it.

Current setup: a local XAMPP MySQL server on the developer Windows 10 machine, the server is running and I can access it via PHPMyAdmin, for C++ I'm using Visual Studio.

---

My current experience:

Trying to connect to local database using example code:

otl_connect db; // connect object
otl_connect::otl_initialize();
//db.rlogon("Server = localhost; Port = 3306; Database = testdb; Uid = testuser; Pwd = testpass;");
db.rlogon("root@127.0.0.1");

both connect functions fail to connect with an exception:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I noticed this is a common error, and I found multiple posts on StackOverflow with that error. Unfortunately, their solutions either didn't work for me, or I didn't understand them.

From this StackOverflow post, it seems that I can either use something called ODBC (which is a kind of a cross-platform middleware to interface with various databases?), OR (OR?) I can use a string from the connectionstrings.com. In the code provided above, the commented out line is actually taken from there. It gives the same exception.

---

I decided that I probably need a DSN (do I? I have no clue if it's strictly required). So I went into Windows ODBC data source administration (64-bit) (10 minutes after I learned that a thing called ODBC exists). There I tried to create a system DSN. The list of drivers is 3 times Microsoft Access something, one Microsoft Excel and one SQL Server. I picked SQL Server. Then it wants a "Name", "Description" and "Server" text fields to be filled out.

I'm not sure what to write there, so I went with MyName, MyDescription, 127.0.0.1 respectively.

Next, I decided to configure database login by explicitly specifying port (it's correct). I can't use any login, because when I click "Next", the window freezes for 3 seconds and gives network error:

If I specify login, this error pops up:

So I went without login, since it is the only way to proceed in this configuration. After that there are 2 windows with additional settings. I decided not to change anything there except writing my database name.

The final result looks the following way:

And the test result is a general network error, whatever it means:

---

Questions:

1. Do I even need DSN in ODBC for the application I'm writing (local app to run queries on the local database)?
2. What would be the proper way to get started, how can I make my first connection to the database with C++, run a query and disconnect using OTL? What is the FULL list of things I need for that?

Answer 1

With the help of others in the comments, I have finally managed to connect to a database with my C++ app for the first time. So in case someone ever looks for how to make their first ever database connection with C++, here is how it went for me:

In the C++ test project, I have main.cpp and otlv4.h from OTL, nothing else.

main.cpp:

#include <iostream>

#define OTL_ODBC // Compile OTL 4.0/ODBC
// The following #define is required with MyODBC 3.51.11 and higher
#define OTL_ODBC_SELECT_STM_EXECUTE_BEFORE_DESCRIBE
// #define OTL_ODBC_UNIX // uncomment this line if UnixODBC is used
#include "otlv4.h"


int main()
{
    //from example code
    otl_connect db; // connect object
    otl_connect::otl_initialize();


    try {
        db.rlogon("Driver={MySQL ODBC 8.2 ANSI Driver};Server=localhost;Port=3306;Database=my_db;User=db_user;Password=db_user_password;");
    }
    catch (otl_exception& p) { // intercept OTL exceptions
        std::cerr << p.msg << std::endl; // print out error message
        std::cerr << p.stm_text << std::endl; // print out SQL that caused the error
        std::cerr << p.sqlstate << std::endl; // print out SQLSTATE message
        std::cerr << p.var_info << std::endl; // print out the variable that caused the error
    }

    db.logoff(); // disconnect from ODBC

    std::cout << "Hello World!\n";
}

This is basically taken from the example code, with the exception of the connection string (db.rlogon()), more on it below.

For the connection string to work, I needed the correct ODBC driver specifically for my database, as ODBC acts as a universal driver for connecting to various databases using the same API (basically, it abstracts away different database interfaces, makes ODBC pretty much platform agnostic both in terms of what database that is and who tries to connect to it). In my case, I have downloaded and installed an ODBC driver for MySQL for Windows. In the Window's "ODBC Data Source Administration (64-bit)" utility, if I try to add a new System DSN, I can now choose MySQL ODBC 8.2 ANSI Driver (and there is a unicode option too). Before installing this driver, there was no such option there.

Now that the driver is installed on the system, there are two options to connect. Either create a DSN in ODCB configuration on the system, which is pretty much a connection configuration file (contains connection details - server address and port, database name, database user name etc.), or write the connection configuration string with all the connection details straight in the program without using DSN (which I chose to do). The only thing necessary to do is to bind the freshly installed MySQL driver by explicitly specifying the driver in the connection string:

db.rlogon("Driver={MySQL ODBC 8.2 ANSI Driver};Server=localhost;Port=3306;Database=my_db;User=db_user;Password=db_user_password;");

The name of the driver in {MySQL ODBC 8.2 ANSI Driver} must be exactly the same as in ODBC Administrator utility.

Very important: there can be NO spaces before equal sign of the word Driver= (but there CAN be spaces around equal signs for any other parameter I have, even if that parameter's value is encapsulated into {val}). I have tested it all:

//This WORKS
db.rlogon("Driver= {MySQL ODBC 8.2 ANSI Driver};"
        "Server = {localhost};"
        "Port=3306;"
        "Database =my_db;"
        "User= db_user;"
        "Password=db_user_password;");

//This does NOT work  
db.rlogon("Driver = {MySQL ODBC 8.2 ANSI Driver};" //this line ruins it
        "Server = {localhost};"
        "Port=3306;"
        "Database =my_db;"
        "User= db_user;"
        "Password=db_user_password;");

To avoid unnecessary headache, just don't have spaces around equal signs in the connection string.

Final connection string that works:

db.rlogon("Driver={MySQL ODBC 8.2 ANSI Driver};"
            "Server=localhost;"
            "Port=3306;"
            "Database=my_db;"
            "User=db_user;"
            "Password=db_user_password;");