SQL Database Drivers

MySQL 5 has stored procedure support at the SQL level, but no API to control IN, OUT, and INOUT parameters. Therefore, parameters have to be set and read using SQL commands instead of QSqlQuery::bindValue().

Example stored procedure:

create procedure qtestproc (OUT param1 INT, OUT param2 INT)
BEGIN
    set param1 = 42;
    set param2 = 43;
END

Source code to access the OUT values:

QSqlQuery q;
q.exec("call qtestproc (@outval1, @outval2)");
q.exec("select @outval1, @outval2");
q.next();
qDebug() << q.value(0) << q.value(1); // outputs "42" and "43"

Note: @outval1 and @outval2 are variables local to the current connection and will not be affected by queries sent from another host or connection.

The MySQL embedded server is a drop-in replacement for the normal client library. With the embedded MySQL server, a MySQL server is not required to use MySQL functionality.

To use the embedded MySQL server, simply link the Qt plugin to libmysqld instead of libmysqlclient. This can be done by adding MYSQL_LIBS=-lmysqld to the configure command line.

Please refer to the MySQL documentation, chapter "libmysqld, the Embedded MySQL Server Library" for more information about the MySQL embedded server.

You need the MySQL header files, as well as the shared library libmysqlclient.so. Depending on your Linux distribution, you may need to install a package which is usually called "mysql-devel".

Tell qmake where to find the MySQL header files and shared libraries (here it is assumed that MySQL is installed in /usr/local) and run make:

cd $QTDIR/qtbase/src/plugins/sqldrivers
qmake -- MYSQL_PREFIX=/usr/local
make sub-mysql

Get the MySQL installer (e.g. mysql-installer-web-community-8.0.18.0.msi). Run the installer, select "Custom" installation, and install the MySQL C Connector which matches your Qt installation (x86 or x64). After installation check that the needed files are there:

• <MySQL dir>/lib/libmysql.lib

• <MySQL dir>/lib/libmysql.dll

• <MySQL dir>/include/mysql.h

Build the plugin as follows (here it is assumed that <MySQL dir> is C:/Program Files/MySQL/MySQL Connector C 6.1):

cd %QTDIR%\qtbase\src\plugins\sqldrivers
qmake -- MYSQL_INCDIR="C:/Program Files/MySQL/MySQL Connector C 6.1/include" MYSQL_LIBDIR="C:/Program Files/MySQL/MySQL Connector C 6.1/lib"
nmake sub-mysql
nmake install

If you are not using a Microsoft compiler, replace nmake with mingw32-make above.

When you distribute your application, remember to include libmysql.dll in your installation package. It must be placed in the same folder as the application executable. libmysql.dll additionally needs the MSVC runtime libraries which can be installed with vcredist.exe

The Qt OCI plugin supports authentication using external credentials (OCI_CRED_EXT). Usually, this means that the database server will use the user authentication provided by the operating system instead of its own authentication mechanism.

Leave the username and password empty when opening a connection with QSqlDatabase to use the external credentials authentication.

Binary Large Objects (BLOBs) can be read and written, but be aware that this process may require a lot of memory. You should use a forward only query to select LOB fields (see QSqlQuery::setForwardOnly()).

Inserting BLOBs should be done using either a prepared query where the BLOBs are bound to placeholders or QSqlTableModel, which uses a prepared query to do this internally.

For Oracle 10g, all you need is the "Instant Client Package - Basic" and "Instant Client Package - SDK". For Oracle prior to 10g, you require the standard Oracle client and the SDK packages.

Oracle library files required to build the driver:

• libclntsh.so (all versions)

• libwtc9.so (only Oracle 9)

Tell qmake where to find the Oracle header files and shared libraries and run make:

For Oracle version 9:

cd $QTDIR/qtbase/src/plugins/sqldrivers
qmake -- OCI_INCDIR="$ORACLE_HOME/rdbms/public" OCI_LIBDIR="$ORACLE_HOME/lib" OCI_LIBS="-lclntsh -lwtc9"
make sub-oci

For Oracle version 10, we assume that you installed the RPM packages of the Instant Client Package SDK (you need to adjust the version number accordingly):

cd $QTDIR/qtbase/src/plugins/sqldrivers
qmake -- OCI_INCDIR=/usr/include/oracle/10.1.0.3/client OCI_LIBDIR=/usr/lib/oracle/10.1.0.3/client/lib
make sub-oci

Note: If you are using the Oracle Instant Client package, you will need to set LD_LIBRARY_PATH when building the OCI SQL plugin, and when running an application that uses the OCI SQL plugin. You can avoid this requirement by setting RPATH, and listing all of the libraries to link to. Here is an example:

configure OCI_INCDIR=/usr/include/oracle/10.1.0.3/client OCI_LIBDIR=/usr/lib/oracle/10.1.0.3/client/lib -R /usr/lib/oracle/10.1.0.3/client/lib OCI_LIBS="-lclntsh -lnnz10"
make

If you wish to build the OCI plugin manually with this method, the procedure looks like this:

cd $QTDIR/qtbase/src/plugins/sqldrivers
qmake -- OCI_INCDIR=/usr/include/oracle/10.1.0.3/client OCI_LIBDIR=/usr/lib/oracle/10.1.0.3/client/lib OCI_LIBS="-Wl,-rpath,/usr/lib/oracle/10.1.0.3/client/lib -lclntsh -lnnz10"
make sub-oci

Choosing the option "Programmer" in the Oracle Client Installer from the Oracle Client Installation CD is generally sufficient to build the plugin. For some versions of Oracle Client, you may also need to select the "Call Interface (OCI)" option if it is available.

Build the plugin as follows (here it is assumed that Oracle Client is installed in C:\oracle):