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");
if (q.next())
    qDebug() << q.value(0) << q.value(1); // outputs "42" and "43"

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 -DMySQL_LIBRARY=<path/to/mysqld/>libmysqld.<so|lib|dylib> 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 / MariaDB header files, as well as the shared library libmysqlclient.<so|dylib> / libmariadb.<so|dylib>. Depending on your Linux distribution, you may need to install a package which is usually called "mysql-devel" or "mariadb-devel".

Tell qt-cmake where to find the MySQL / MariaDB header files and shared libraries (here it is assumed that MySQL / MariaDB is installed in /usr/local) and build:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DMySQL_INCLUDE_DIR="/usr/local/mysql/include" -DMySQL_LIBRARY="/usr/local/mysql/lib/libmysqlclient.<so|dylib>"
cmake --build .
cmake --install .

You need to get the MySQL installation files (e.g. mysql-installer-web-community-8.0.22.0.msi or mariadb-connector-c-3.1.11-win64.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

and for MariaDB

• <MariaDB dir>/lib/libmariadb.lib

• <MariaDB dir>/lib/libmariadb.dll

• <MariaDB dir>/include/mysql.h

Build the plugin as follows (here it is assumed that <MySQL dir> is C:\mysql-8.0.22-winx64):

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DMySQL_INCLUDE_DIR="C:\mysql-8.0.22-winx64\include" -DMySQL_LIBRARY="C:\mysql-8.0.22-winx64\lib\libmysql.lib"
cmake --build .
cmake --install .

When you distribute your application, remember to include libmysql.dll / libmariadb.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.

All you need is the " - Basic" and "Instant Client Package - SDK".

Oracle library files required to build the driver:

• libclntsh.<so|dylib> (all versions)

Tell qt-cmake where to find the Oracle header files and shared libraries and build.

We assume that you installed the RPM packages of the Instant Client Package SDK (you need to adjust the version number accordingly):

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DOracle_INCLUDE_DIR="/usr/include/oracle/21/client64" -DOracle_LIBRARY="/usr/lib/oracle/21/client64/lib/libclntsh.<so|dylib>"
cmake --build .
cmake --install .

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 and SDK is installed in C:\oracle\sdk):

mkdir build-sqldrivers
cd build-sqldrivers
qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DOracle_INCLUDE_DIR="C:\oracle\sdk\include" -DOracle_LIBRARY="C:\oracle\oci.lib"
cmake --build .
cmake --install .

When you run your application, you will also need to add the oci.lib path to your PATH environment variable:

set PATH=%PATH%;C:\oracle

With Microsoft SQL Server the result set returned by a stored procedure that uses the return statement, or returns multiple result sets, will be accessible only if you set the query's forward only mode to forward using QSqlQuery::setForwardOnly().

// STORED_PROC uses the return statement or returns multiple result sets
QSqlQuery query;
query.setForwardOnly(true);
query.exec("{call STORED_PROC}");

The QODBC Plugin will use the Unicode API if UNICODE is defined. On Windows NT based systems, this is the default. Note that the ODBC driver and the DBMS must also support Unicode.

For the Oracle 9 ODBC driver (Windows), it is necessary to check "SQL_WCHAR support" in the ODBC driver manager otherwise Oracle will convert all Unicode strings to local 8-bit.

It is recommended that you use unixODBC. You can find the latest version and ODBC drivers at http://www.unixodbc.org. You need the unixODBC header files and shared libraries.

Tell qt-cmake where to find the unixODBC header files and shared libraries (here it is assumed that unixODBC is installed in /usr/local/unixODBC) and build:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DODBC_INCLUDE_DIR="/usr/local/unixODBC/include" -DODBC_LIBRARY="/usr/local/unixODBC/lib/libodbc.<so|dylib>"
cmake --build .
cmake --install .

The ODBC header and include files should already be installed in the right directories. You just have to build the plugin as follows:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform>
cmake --build .
cmake --install .

The QPSQL driver automatically detects whether the PostgreSQL database you are connecting to supports Unicode or not. Unicode is automatically used if the server supports it. Note that the driver only supports the UTF-8 encoding. If your database uses any other encoding, the server must be compiled with Unicode conversion support.

Unicode support was introduced in PostgreSQL version 7.1 and it will only work if both the server and the client library have been compiled with multibyte support. More information about how to set up a multibyte enabled PostgreSQL server can be found in the PostgreSQL Administrator Guide, Chapter 5.

PostgreSQL databases will only respect case sensitivity if the table or field name is quoted when the table is created. So for example, a SQL query such as:

CREATE TABLE "testTable" ("id" INTEGER);

will ensure that it can be accessed with the same case that was used. If the table or field name is not quoted when created, the actual table name or field name will be lower-case. When QSqlDatabase::record() or QSqlDatabase::primaryIndex() access a table or field that was unquoted when created, the name passed to the function must be lower-case to ensure it is found. For example:

QString tableString("testTable");
QSqlQuery q;
// Create table query is not quoted, therefore it is mapped to lower case
q.exec(QString("CREATE TABLE %1 (id INTEGER)").arg(tableString));
// Call toLower() on the string so that it can be matched
QSqlRecord rec = database.record(tableString.toLower());

To use forward-only queries, you must build the QPSQL plugin with PostreSQL client library version 9.2 or later. If the plugin is built with an older version, then forward-only mode will not be available - calling QSqlQuery::setForwardOnly() with true will have no effect.

QSqlDatabase: QPSQL driver not loaded
QSqlDatabase: available drivers: QSQLITE QMYSQL QMARIADB QODBC QPSQL
Could not create database object

While navigating the results in forward-only mode, the handle of QSqlResult may change. Applications that use the low-level handle of SQL result must get a new handle after each call to any of QSqlResult fetch functions. Example:

QSqlQuery query;
QVariant v;
query.setForwardOnly(true);
query.exec("SELECT * FROM table");
while (query.next()) {
    // Handle changes in every iteration of the loop
    v = query.result()->handle();

    if (qstrcmp(v.typeName(), "PGresult*") == 0) {
        PGresult *handle = *static_cast<PGresult **>(v.data());
        if (handle) {
            // Do something...
        }
    }
}

While reading the results of a forward-only query with PostgreSQL, the database connection cannot be used to execute other queries. This is a limitation of libpq library. Example:

int value;
QSqlQuery query1;
query1.setForwardOnly(true);
query1.exec("select * FROM table1");
while (query1.next()) {
    value = query1.value(0).toInt();
    if (value == 1) {
        QSqlQuery query2;
        query2.exec("update table2 set col=2");  // WRONG: This will discard all results of
    }                                            // query1, and cause the loop to quit
}

This problem will not occur if query1 and query2 use different database connections, or if we execute query2 after the while loop.

QPSQLDriver::getResult: Query results lost - probably discarded on executing another SQL query.

You need the PostgreSQL client library and headers installed.

To make qt-cmake find the PostgreSQL header files and shared libraries, build the plugin the following way (assuming that the PostgreSQL client is installed in /usr/local/pgsql):

mkdir build-psql-driver
cd build-psql-driver

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers-DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DCMAKE_INCLUDE_PATH="/usr/local/pgsql/include" -DCMAKE_LIBRARY_PATH="/usr/local/pgsql/lib"
cmake --build .
cmake --install .

Install the appropriate PostgreSQL developer libraries for your compiler. Assuming that PostgreSQL was installed in C:\pgsql, build the plugin as follows:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DCMAKE_INCLUDE_PATH="C:\pgsql\include" -DCMAKE_LIBRARY_PATH="C:\pgsql\lib"
cmake --build .
cmake --install .

Users of MinGW may wish to consult the following online document: PostgreSQL MinGW/Native Windows.

When you distribute your application, remember to include libpq.dll in your installation package. It must be placed in the same folder as the application executable.

The DB2 header and include files should already be installed in the right directories. You just have to build the plugin as follows:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DDB2_INCLUDE_DIR="C:\db2\include" -DDB2_LIBRARY="C:\db2\lib\db2.lib"
cmake --build .
cmake --install .

SQLite version 3 is included as a third-party library within Qt. It can be built by passing the -DFEATURE_system_sqlite=OFF parameter to the qt-cmake command line.

If you do not want to use the SQLite library included with Qt, you can pass -DFEATURE_system_sqlite=ON to the qt-cmake command line to use the SQLite libraries of the operating system. This is recommended whenever possible, as it reduces the installation size and removes one component for which you need to track security advisories.

On Unix and macOS (replace $SQLITE with the directory where SQLite resides):

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DFEATURE_system_sqlite=ON -DCMAKE_INCLUDE_PATH="$SQLITE/include" -DCMAKE_LIBRARY_PATH="$SQLITE/lib"
cmake --build .
cmake --install .

On Windows (assuming that SQLite is installed in C:\SQLITE):

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DFEATURE_system_sqlite=ON -DCMAKE_INCLUDE_PATH="C:\SQLITE\include" -DCMAKE_LIBRARY_PATH="C:\SQLITE\lib"
cmake --build .
cmake --install .

SQLite comes with a REGEXP operation. However the needed implementation must be provided by the user. For convenience a default implementation can be enabled by setting the connect option QSQLITE_ENABLE_REGEXP before the database connection is opened. Then a SQL statement like "column REGEXP 'pattern'" basically expands to the Qt code

column.contains(QRegularExpression("pattern"));

For better performance the regular expressions are cached internally. By default the cache size is 25, but it can be changed through the option's value. For example passing "QSQLITE_ENABLE_REGEXP=10" reduces the cache size to 10.

SQLite minor releases sometimes break file format forward compatibility. For example, SQLite 3.3 can read database files created with SQLite 3.2, but databases created with SQLite 3.3 cannot be read by SQLite 3.2. Please refer to the SQLite documentation and change logs for information about file format compatibility between versions.

Qt minor releases usually follow the SQLite minor releases, while Qt patch releases follow SQLite patch releases. Patch releases are therefore both backward and forward compatible.

To force SQLite to use a specific file format, it is necessary to build and ship your own database plugin with your own SQLite library as illustrated above. Some versions of SQLite can be forced to write a specific file format by setting the SQLITE_DEFAULT_FILE_FORMAT define when building SQLite.

By default the driver connects to the database using UNICODE_FSS. This can be overridden by setting the ISC_DPB_LC_CTYPE parameter with QSqlDatabase::setConnectOptions() before opening the connection.

// connect to database using the Latin-1 character set
db.setConnectOptions("ISC_DPB_LC_CTYPE=Latin1");
if (db.open())
    qDebug("The database connection is open.");

If Qt does not support the given text encoding the driver will issue a warning message and connect to the database using UNICODE_FSS.

Note that if the text encoding set when connecting to the database is not the same as in the database, problems with transliteration might arise.

InterBase/Firebird return OUT values as result set, so when calling stored procedure, only IN values need to be bound via QSqlQuery::bindValue(). The RETURN/OUT values can be retrieved via QSqlQuery::value(). Example:

QSqlQuery q;
q.exec("execute procedure my_procedure");
if (q.next())
    qDebug() << q.value(0); // outputs the first RETURN/OUT value

The following assumes InterBase or Firebird is installed in /opt/interbase:

If you are using InterBase:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DInterbase_INCLUDE_DIR="/opt/interbase/include" -DInterbase_LIBRARY="/opt/interbase/lib/libgds.<so|dylib>"
cmake --build .
cmake --install .

If you are using Firebird, the Firebird library has to be set explicitly:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>/Src/qtbase/src/plugins/sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>/<platform> -DInterbase_INCLUDE_DIR="/opt/interbase/include" -DInterbase_LIBRARY="/opt/interbase/lib/libfbclient.<so|dylib>"
cmake --build .
cmake --install .

The following assumes InterBase or Firebird is installed in C:\interbase:

If you are using InterBase:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DInterbase_INCLUDE_DIR="C:\interbase\include" -DInterbase_LIBRARY="C:\interbase\gds.lib"
cmake --build .
cmake --install .

If you are using Firebird:

mkdir build-sqldrivers
cd build-sqldrivers

qt-cmake -G Ninja <qt_installation_path>\Src\qtbase\src\plugins\sqldrivers -DCMAKE_INSTALL_PREFIX=<qt_installation_path>\<platform> -DInterbase_INCLUDE_DIR="C:\interbase\include" -DInterbase_LIBRARY="C:\interbase\lib\fbclient_ms.lib"
cmake --build .
cmake --install .

Note that C:\interbase\bin must be in the PATH.