Please note that I was using Qt 4.6.2 with MSVC 2008 as my compiler and MySQL Server 5.1 on Windows 7 32-bit at the time of this post.

For a while now, I've been interested to see how friendly Qt is with developing applications that interact with MySQL databases. When I set out to toy around with Qt's QtSql module, I learned some important things about how database interoperability in Qt depends heavily on drivers for the various database management systems (MySQL, Oracle, ODBC, SQLite, et. al.). This is to be expected, as with .NET and Java. However, the bump in the road I was encountering was that the QMYSQL driver (each DBMS has a corresponding driver, such as QODBC, QSQLITE, and in my case, QMYSQL) is not included by default, depending on if you compile Qt yourself (how you do so and in what environment are both important factors).

Although helpful, the Qt documentation for the SQL drivers was sparse at best, and did not mention some nuances associated with the process.

First, I wrote some basic code to test connectivity, and I could not open a connection with my MySQL database by any driver except QSQLITE, which could connect, but could not select any tables in the database. Before trying QSQLITE, I had given QMYSQL a try, but Qt appropriately informed me that the driver (a plugin) was not loaded.

With this information, I embarked on a journey to find out what I needed to do in order to load the driver. My initial investigations suggested that I needed to obtain the driver first, before it could be installed or loaded. I found it mind-numbingly difficult to obtain this information, because I had to dig through tons of Qt mailing list archives and old forum threads on QtForum.org (now defunct) and QtCentre.org (active) before I could find even this bit.

Next, I needed to know what I needed in order to build the QMYSQL driver plugin for Qt and then make use of it. It turns out, I needed to download the installation for MySQL Server, run it, choose Custom, and then install the C Include Files / Lib Files. That was easy.

Following that, the next step was to compile the QMYSQL driver, which requires the contents of $QTDIR%\src\plugins\sqldrivers\mysql as well as the contents of C:\MySQL\include and the C:\MySQL\lib\opt\libmysql.lib file.

Indeed, the aforementioned Qt documentation for the SQL drivers provides instructions for compiling each driver on both Linux and Windows. According to the documentation, it's as simple as doing the following in the Qt command prompt once you've installed the necessary files:

cd $QTDIR%\src\plugins\sqldrivers\mysql
qmake "INCLUDEPATH+=C:\MySQL\include" "LIBS+=C:\MySQL\MySQL Server <version>\lib\opt\libmysql.lib" mysql.pro
nmake

However, if you're on Windows and especially if you're using MSVC as your compiler (meaning you're using "nmake" instead of "make" in the Qt command prompt), then there is a major caveat that will ruin your day if you don't know about it (and most people don't, unfortunately). Basically, nmake is not at all friendly with file paths that contain spaces. This means that you will likely need to reinstall MySQL to a path on Windows that does not contain any spaces in any of the directories that are part of the path. This was incredibly frustrating, because there's not really a way to substitute a special character when these filepath strings are passed to nmake from the Qt command prompt or the mysql.pro file. I tried the various "space" equivalents and had no success. Luckily, uninstalling and reinstalling MySQL to a new location (i chose C:\MySQL\ instead of C:\Program Files\MySQL\MySQL Server 5.1\ which is default) was quick and easy.

Now that I could specify a path that contained no spaces, things were perfectly fine. The QMYSQL plugin compiled quickly and successfully. Alas, victory! Or so I thought…

The next issue I had was that my QSqlDatabase object kept saying that the QMYSQL driver was not loaded. This was a rather annoying issue that I spent yet another hour and half finding a solution for. Luckily, a respondent to an older thread on QtCentre.org suggested that the libmySQL.dll from C:\MySQL\bin\ needed to be copied and pasted into the directory where the binaries for the application were residing (either \debug\ or \release\, respectively).

After doing that, my little test application succeeded in connecting, running a query, and returned a nice little result to me without any issue whatsoever. Now that this hiccup has been overcome, I see that MySQL development with Qt will be both surprisingly simple and extremely elegant. It would be nice if the Qt documentation were updated to mention some of these caveats. In the meantime, I hope people will find this post of help!

« »