1// Copyright (C) 2020 The Qt Company Ltd.
2// SPDX-License-Identifier: LicenseRef-Qt-Commercial OR GFDL-1.3-no-invariants-only
6 \title SQL Database Drivers
7 \brief How to configure and install Qt SQL drivers for supported databases.
9 The Qt SQL module uses driver \l{How to Create Qt
10 Plugins}{plugins} to communicate with the different database
11 APIs. Since Qt's SQL Module API is database-independent, all
12 database-specific code is contained within these drivers. Several
13 drivers are supplied with Qt, and other drivers can be added. The
14 driver source code is supplied and can be used as a model for
15 \l{#development}{writing your own drivers}.
19 \section1 Supported Databases
21 The table below lists the drivers included with Qt:
24 \header \li Driver name \li DBMS
25 \row \li \l{#QDB2}{QDB2} \li IBM DB2 (version 7.1 and above)
26 \row \li \l{#QIBASE}{QIBASE} \li Borland InterBase / Firebird
27 \row \li \l{#QMYSQL}{QMYSQL / MARIADB} \li MySQL or MariaDB (version 5.6 and above)
28 \row \li \l{#QOCI}{QOCI} \li Oracle Call Interface Driver (version 12.1 and above)
29 \row \li \l{#QODBC}{QODBC}
30 \li Open Database Connectivity (ODBC) - Microsoft SQL Server and other
31 ODBC-compliant databases
32 \row \li \l{#QPSQL}{QPSQL} \li PostgreSQL (versions 7.3 and above)
33 \row \li \l{#QSQLITE}{QSQLITE} \li SQLite version 3
34 \row \li \l{#QMIMER}{QMIMER} \li Mimer SQL (version 11 and above)
37 SQLite is the in-process database system with the best test coverage
38 and support on all platforms. Oracle via OCI, PostgreSQL, and MySQL
39 through either ODBC or a native driver are well-tested on Windows and
40 Linux. The completeness of the support for other systems depends on the
41 availability and quality of client libraries.
43 \note To build a driver plugin you need to have the appropriate
44 client library for your Database Management System (DBMS). This provides
45 access to the API exposed by the DBMS, and is typically shipped with it.
46 Most installation programs also allow you to install "development
47 libraries", and these are what you need. These libraries are responsible
48 for the low-level communication with the DBMS. Also make sure to install
49 the correct database libraries for your Qt architecture (32 or 64 bit).
51 \note When using Qt under Open Source terms but with a proprietary
52 database, verify the client library's license compatibility with
56 \section1 Building the Drivers
58 \section2 Compile Qt with a specific driver
60 The Qt \c configure script tries to
61 automatically detect the available client libraries on your
62 machine. Run \c{configure -help} to see what drivers can be
63 built. You should get an output similar to this:
65 \snippet code/doc_src_sql-driver.qdoc 0
67 The \c configure script cannot detect the necessary libraries
68 and include files if they are not in the standard paths, so it
69 may be necessary to specify these paths using either
70 driver-specific include and library path variables or \c CMAKE_INCLUDE_PATH
71 and \c CMAKE_LIBRARY_PATH. For example, if your MySQL files are
72 installed in \c{C:\mysql-connector-c-6.1.11-winx64} on Windows, then pass
73 the following parameter to double-dash part of configure line:
74 \snippet code/doc_src_sql-driver.qdoc 42
75 When you configure drivers in the manner described above, CMake skips any
76 dependency checks and uses the provided paths as is. This is especially
77 useful if the package provides its own set of system libraries that
78 should not be recognized by the build routine.
80 In some cases it's more convenient to use \c CMAKE_INCLUDE_PATH
81 and \c CMAKE_LIBRARY_PATH variables to locate required libraries.
82 You should prefer this method if module needs to set properties
83 for the provided target libraries (e.g. this is required for PostgreSQL
85 For example, you can do this as follows, to locate MySQL:
86 \snippet code/doc_src_sql-driver.qdoc 43
88 The particulars for each driver are explained below.
90 \note If something goes wrong and you want CMake to recheck your
91 available drivers, you might need to remove \e{CMakeCache.txt} from the build
94 \target DriverStandalone
95 \section2 Compile only a specific sql driver
97 A typical \c qt-cmake run (in this case to configure for MySQL) looks like this:
99 \snippet code/doc_src_sql-driver.qdoc 41
100 \note As mentioned in \l{#DriverWithQt}{Compile Qt with a specific driver},
101 if the driver could not be found or is not enabled, start over by removing
104 Due to the practicalities of dealing with external dependencies,
105 only the SQLite plugin is shipped with binary builds of Qt.
106 Binary builds of Qt for Windows also include the ODBC plugin.
107 To be able to add additional drivers to the Qt installation
108 without re-building all of Qt, it is possible to configure
109 and build the \c qtbase/src/plugins/sqldrivers directory outside
110 of a full Qt build directory. Note that it is not possible to
111 \e configure each driver separately, only all of them at once.
112 Drivers can be \e built separately, though.
114 \note You need to specify \c{CMAKE_INSTALL_PREFIX}, if you want to install
115 plugins after the build is finished.
117 \section1 Driver Specifics
120 \section2 QMYSQL for MySQL or MariaDB 5.6 and higher
122 MariaDB is a fork of MySQL intended to remain free and open-source software
123 under the GNU General Public License. MariaDB intended to maintain high
124 compatibility with MySQL, ensuring a drop-in replacement capability with
125 library binary parity and exact matching with MySQL APIs and commands.
126 Therefore the plugin for MySQL and MariaDB are combined into one Qt plugin.
128 \section3 QMYSQL Stored Procedure Support
130 MySQL has stored procedure support at the SQL level, but no
131 API to control IN, OUT, and INOUT parameters. Therefore, parameters
132 have to be set and read using SQL commands instead of QSqlQuery::bindValue().
134 Example stored procedure:
136 \snippet code/doc_src_sql-driver.qdoc 1
138 Source code to access the OUT values:
140 \snippet code/doc_src_sql-driver.cpp 2
142 \note \c{@outval1} and \c{@outval2} are variables local to the current
143 connection and will not be affected by queries sent from another host
146 \section3 Embedded MySQL Server
148 The MySQL embedded server is a drop-in replacement for the normal
149 client library. With the embedded MySQL server, a MySQL server is
150 not required to use MySQL functionality.
152 To use the embedded MySQL server, simply link the Qt plugin to \c
153 libmysqld instead of \c libmysqlclient. This can be done by adding
154 \c{-DMySQL_LIBRARY=<path/to/mysqld/>libmysqld.<so|lib|dylib>} to the configure command line.
156 Please refer to the MySQL documentation, chapter "libmysqld, the Embedded
157 MySQL Server Library" for more information about the MySQL embedded server.
159 \section3 Connection options
160 The Qt MySQL/MariaDB plugin honors the following connection options:
162 \header \li Attribute \li Possible value
165 \li If set, switches to compressed protocol after successful authentication
167 \li CLIENT_FOUND_ROWS
168 \li If set, send found rows instead of affected rows
170 \li CLIENT_IGNORE_SPACE
171 \li If set, ignore spaces before '('
174 \li If set, don't allow database.table.column
176 \li CLIENT_INTERACTIVE
177 \li If set, client is treated as interactive
179 \li MYSQL_OPT_PROTOCOL
180 \li explicitly specify the protocol to use:\br
181 MYSQL_PROTOCOL_TCP: use tcp connection (ip/hostname specified through setHostname())
182 MYSQL_PROTOCOL_SOCKET: connect through a socket specified in UNIX_SOCKET
183 MYSQL_PROTOCOL_PIPE: connect through a named pipe specified in UNIX_SOCKET
184 MYSQL_PROTOCOL_MEMORY: connect through shared memory specified in MYSQL_SHARED_MEMORY_BASE_NAME
187 \li Specifies the socket or named pipe to use, even it's called UNIX_SOCKET it
188 can also be used on windows
190 \li MYSQL_SHARED_MEMORY_BASE_NAME
191 \li Specified the shared memory segment name to use
193 \li MYSQL_OPT_RECONNECT
194 \li TRUE or 1: Automatically reconnect after connection loss\br
195 FALSE or 0: No automatic reconnect after connection loss (default)\br
196 See \l {https://dev.mysql.com/doc/c-api/8.0/en/c-api-auto-reconnect.html}
197 {Automatic Reconnection Control}
199 \li MYSQL_OPT_CONNECT_TIMEOUT
200 \li The connect timeout in seconds
202 \li MYSQL_OPT_READ_TIMEOUT
203 \li The timeout in seconds for each attempt to read from the server
205 \li MYSQL_OPT_WRITE_TIMEOUT
206 \li The timeout in seconds for each attempt to write to the server
208 \li MYSQL_OPT_LOCAL_INFILE
209 \li Set to 1 to enable the support for local
210 \l {https://dev.mysql.com/doc/refman/8.0/en/load-data.html} {LOAD_DATA},
211 disabled if not set or 0
213 \li MYSQL_OPT_SSL_MODE
214 \li The security state to use for the connection to the server: SSL_MODE_DISABLED,
215 SSL_MODE_PREFERRED, SSL_MODE_REQUIRED, SSL_MODE_VERIFY_CA, SSL_MODE_VERIFY_IDENTITY.
217 \li MYSQL_OPT_TLS_VERSION
218 \li A list of protocols the client permits for encrypted connections. The value can be
219 a combination of 'TLSv1' ,' TLSv1.1', 'TLSv1.2' or 'TLSv1.3' depending on the used \l
220 {https://dev.mysql.com/doc/refman/8.0/en/encrypted-connection-protocols-ciphers.html#encrypted-connection-protocol-configuration}
221 {MySQL server} version.
223 \li MYSQL_OPT_SSL_KEY / SSL_KEY (deprecated)
224 \li The path name of the client private key file
226 \li MYSQL_OPT_SSL_CERT / SSL_CERT (deprecated)
227 \li The path name of the client public key certificate file
229 \li MYSQL_OPT_SSL_CA / SSL_CA (deprecated)
230 \li The path name of the Certificate Authority (CA) certificate file
232 \li MYSQL_OPT_SSL_CAPATH / SSL_CAPATH (deprecated)
233 \li The path name of the directory that contains trusted SSL CA certificate files
235 \li MYSQL_OPT_SSL_CIPHER / SSL_CIPHER (deprecated)
236 \li The list of permissible ciphers for SSL encryption
238 \li MYSQL_OPT_SSL_CRL
239 \li The path name of the file containing certificate revocation lists
241 \li MYSQL_OPT_SSL_CRLPATH
242 \li The path name of the directory that contains files containing certificate revocation lists
244 For more detailed information about the connect options please refer
245 to the \l {https://dev.mysql.com/doc/c-api/8.0/en/mysql-options.html}
246 {mysql_options()} MySQL documentation.
249 \section3 How to Build the QMYSQL Plugin on Unix and \macos
251 You need the MySQL / MariaDB header files, as well as the shared library
252 \c{libmysqlclient.<so|dylib>} / \c{libmariadb.<so|dylib>}. Depending on your Linux distribution,
253 you may need to install a package which is usually called "mysql-devel"
256 Tell \c qt-cmake where to find the MySQL / MariaDB header files and shared
257 libraries (here it is assumed that MySQL / MariaDB is installed in
258 \c{/usr/local}) and build:
260 \snippet code/doc_src_sql-driver.qdoc 3
262 \section3 How to Build the QMYSQL Plugin on Windows
264 You need to get the MySQL installation files (e.g.
265 \l {https://dev.mysql.com/downloads/installer/}{MySQL web installer} or
266 \l {https://downloads.mariadb.com/Connectors/c/}{MariaDB C Connector}).
268 select custom installation and install the MySQL C Connector
269 which matches your Qt installation (x86 or x64).
270 After installation check that the needed files are there:
272 \li \c {<MySQL dir>/lib/libmysql.lib}
273 \li \c {<MySQL dir>/lib/libmysql.dll}
274 \li \c {<MySQL dir>/include/mysql.h}
278 \li \c {<MariaDB dir>/lib/libmariadb.lib}
279 \li \c {<MariaDB dir>/lib/libmariadb.dll}
280 \li \c {<MariaDB dir>/include/mysql.h}
283 \note As of MySQL 8.0.19, the C Connector is no longer offered as a standalone
284 installable component. Instead, you can get \c{mysql.h} and \c{libmysql.*} by
285 installing the full MySQL Server (x64 only) or the
286 \l{https://downloads.mariadb.org/connector-c/}{MariaDB C Connector}.
288 Build the plugin as follows (here it is assumed that \c{<MySQL dir>} is
289 \c{C:\mysql-8.0.22-winx64}):
291 \snippet code/doc_src_sql-driver.qdoc 5
293 When you distribute your application, remember to include \e libmysql.dll / \e libmariadb.dll
294 in your installation package. It must be placed in the same folder
295 as the application executable. \e libmysql.dll additionally needs the
296 MSVC runtime libraries which can be installed with
297 \l {https://support.microsoft.com/en-us/help/2977003/the-latest-supported-visual-c-downloads}{vcredist.exe}
300 \section2 QOCI for the Oracle Call Interface (OCI)
302 The Qt OCI plugin supports connecting to Oracle database as determined by
303 the version of the instant client used. This is dependent on what Oracle
304 indicates it supports. The plugin will auto-detect the database version
305 and enable features accordingly.
307 It's possible to connect to a Oracle database without a tnsnames.ora file.
308 This requires that the database SID is passed to the driver as the database
309 name, and that a hostname is given.
311 \section3 OCI User Authentication
313 The Qt OCI plugin supports authentication using
314 external credentials (OCI_CRED_EXT). Usually, this means that the database
315 server will use the user authentication provided by the operating system
316 instead of its own authentication mechanism.
318 Leave the username and password empty when opening a connection with
319 QSqlDatabase to use the external credentials authentication.
321 \section3 OCI BLOB/LOB Support
323 Binary Large Objects (BLOBs) can be read and written, but be aware
324 that this process may require a lot of memory. You should use a forward
325 only query to select LOB fields (see QSqlQuery::setForwardOnly()).
327 Inserting BLOBs should be done using either a prepared query where the
328 BLOBs are bound to placeholders or QSqlTableModel, which uses a prepared
329 query to do this internally.
331 \section3 Connection options
332 The Qt OCI plugin honors the following connection options:
334 \header \li Attribute \li Possible value
336 \li OCI_ATTR_PREFETCH_ROWS
337 \li Sets the OCI attribute
338 \l {https://docs.oracle.com/database/121/LNOCI/oci04sql.htm#LNOCI16355}
339 {OCI_ATTR_PREFETCH_ROWS} to the specified value
341 \li OCI_ATTR_PREFETCH_MEMORY
342 \li Sets the OCI attribute
343 \l {https://docs.oracle.com/database/121/LNOCI/oci04sql.htm#LNOCI16355}
344 {OCI_ATTR_PREFETCH_MEMORY} to the specified value
347 \li OCI_SYSDBA: authenticate for SYSDBA access\br
348 OCI_SYSOPER: authenticate for SYSOPER access\br
349 OCI_DEFAULT: authenticate with normal access\br
350 see \l {https://docs.oracle.com/cd/B10500_01/appdev.920/a96584/oci15r13.htm}
351 {OCISessionBegin} for more information about the access modes
354 \section3 How to Build the OCI Plugin on Unix and \macos
356 All you need is the " - Basic" and "Instant Client
359 Oracle library files required to build the driver:
362 \li \c libclntsh.<so|dylib> (all versions)
365 Tell \c qt-cmake where to find the Oracle header files and shared
368 We assume that you installed the RPM packages of the Instant Client Package SDK
369 (you need to adjust the version number accordingly):
370 \snippet code/doc_src_sql-driver.qdoc 7
372 \note If you are using the Oracle Instant Client package,
373 you will need to set LD_LIBRARY_PATH when building the OCI SQL plugin,
374 and when running an application that uses the OCI SQL plugin.
376 \section3 How to Build the OCI Plugin on Windows
378 Choosing the option "Programmer" in the Oracle Client Installer from
379 the Oracle Client Installation CD is generally sufficient to build the
380 plugin. For some versions of Oracle Client, you may also need to select
381 the "Call Interface (OCI)" option if it is available.
383 Build the plugin as follows (here it is assumed that Oracle Client is
384 installed in \c{C:\oracle} and SDK is installed in \c{C:\oracle\sdk}):
386 \snippet code/doc_src_sql-driver.qdoc 8
388 When you run your application, you will also need to add the \c oci.dll
389 path to your \c PATH environment variable:
391 \snippet code/doc_src_sql-driver.qdoc 9
394 \section2 QODBC for Open Database Connectivity (ODBC)
396 ODBC is a general interface that allows you to connect to multiple
397 DBMSs using a common interface. The QODBC driver allows you to connect
398 to an ODBC driver manager and access the available data sources. Note
399 that you also need to install and configure ODBC drivers for the ODBC
400 driver manager that is installed on your system. The QODBC plugin
401 then allows you to use these data sources in your Qt applications.
403 \note You should use the native driver, if it is available, instead
404 of the ODBC driver. ODBC support can be used as a fallback for compliant
405 databases if no native driver is available.
407 On Windows, an ODBC driver manager is installed by default.
408 For Unix systems, there are some implementations which must be
409 installed first. Note that every end user of your application is
410 required to have an ODBC driver manager installed, otherwise the
411 QODBC plugin will not work.
413 When connecting to an ODBC datasource, you should pass the name
414 of the ODBC datasource (DSN) to the QSqlDatabase::setDatabaseName()
415 function, rather than the actual database name.
416 It's also possible to pass a FILEDSN (*.dsn) filename or a complete
417 ODBC driver string. When passing a driver string you must make sure,
418 that all parameters (username, password, ...) are properly escaped.
419 Passing the username or password through the QSqlDatabase functions,
420 the escaping is done by the QODBC plugin.
422 The QODBC Plugin needs an ODBC compliant driver manager version 2.0 or
423 later. Some ODBC drivers claim to be version-2.0-compliant,
424 but do not offer all the necessary functionality. The QODBC plugin
425 therefore checks whether the data source can be used after a
426 connection has been established, and refuses to work if the check
427 fails. If you do not like this behavior, you can remove the \c{#define
428 ODBC_CHECK_DRIVER} line from the file \c{qsql_odbc.cpp}. Do this at
431 By default, Qt instructs the ODBC driver to behave as an ODBC 2.x
432 driver. However, for some \e{driver-manager/ODBC 3.x-driver}
433 combinations (e.g., \e{unixODBC/MaxDB ODBC}), telling the ODBC
434 driver to behave as a 2.x driver can cause the driver plugin to
435 have unexpected behavior. To avoid this problem, instruct the ODBC
436 driver to behave as a 3.x driver by
437 \l{QSqlDatabase::setConnectOptions()} {setting the connect option}
438 \c{"SQL_ATTR_ODBC_VERSION=SQL_OV_ODBC3"} before you
439 \l{QSqlDatabase::open()} {open your database connection}. Note
440 that this will affect multiple aspects of ODBC driver behavior,
441 e.g., the SQLSTATEs. Before setting this connect option, consult
442 your ODBC documentation about behavior differences you can expect.
444 If you experience very slow access of the ODBC datasource, make sure
445 that ODBC call tracing is turned off in the ODBC datasource manager.
447 Some drivers do not support scrollable cursors. In that case, only
448 queries in \l QSqlQuery::setForwardOnly() mode can be used successfully.
450 \section3 ODBC Stored Procedure Support
452 With Microsoft SQL Server the result set returned by a stored
453 procedure that uses the return statement, or returns multiple result
454 sets, will be accessible only if you set the query's forward only
455 mode to \e forward using \l QSqlQuery::setForwardOnly().
457 \snippet code/doc_src_sql-driver.cpp 10
459 \note The value returned by the stored procedure's return statement
462 \section3 ODBC Unicode Support
464 The QODBC Plugin will use the Unicode API if UNICODE is defined. On
465 Windows based systems, this is the default. Note that the ODBC
466 driver and the DBMS must also support Unicode.
468 For the Oracle 9 ODBC driver (Windows), it is necessary to check
469 "SQL_WCHAR support" in the ODBC driver manager otherwise Oracle
470 will convert all Unicode strings to local 8-bit representation.
472 \section3 Connection options
473 The Qt ODBC plugin honors the following connection options:
475 \header \li Attribute \li Possible value
477 \li SQL_ATTR_ACCESS_MODE
478 \li SQL_MODE_READ_ONLY: open the database in read-only mode\br
479 SQL_MODE_READ_WRITE: open the database in read-write mode (default)
481 \li SQL_ATTR_LOGIN_TIMEOUT
482 \li Number of seconds to wait for the database connection
483 during login (a value of 0 will wait forever)
485 \li SQL_ATTR_CONNECTION_TIMEOUT
486 \li Number of seconds to wait for any request to the database
487 (a value of 0 will wait forever)
489 \li SQL_ATTR_CURRENT_CATALOG
490 \li The catalog (database) to use for this connection
492 \li SQL_ATTR_METADATA_ID
493 \li SQL_TRUE: the string argument of catalog functions are treated
495 SQL_FALSE: the string arguments of catalog functions are not
496 treated as identifiers
498 \li SQL_ATTR_PACKET_SIZE
499 \li Specifies the network packet size in bytes
501 \li SQL_ATTR_TRACEFILE
502 \li A string containing the name of the trace file
505 \li SQL_OPT_TRACE_ON: Enable database query tracing\br
506 SQL_OPT_TRACE_OFF: Disable database query tracing (default)
508 \li SQL_ATTR_CONNECTION_POOLING
509 \li Enable or disable connection pooling at the environment level.\br
510 SQL_CP_DEFAULT, SQL_CP_OFF: Connection pooling is turned off (default)\br
511 SQL_CP_ONE_PER_DRIVER: A single connection pool is supported for each driver\br
512 SQL_CP_ONE_PER_HENV: A single connection pool is supported for each environment
514 \li SQL_ATTR_ODBC_VERSION
515 \li SQL_OV_ODBC3: The driver should act as a ODBC 3.x driver\br
516 SQL_OV_ODBC2: The driver should act as a ODBC 2.x driver (default)
518 For more detailed information about the connect options please refer
519 to the \l {https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlsetconnectattr-function}
520 {SQLSetConnectAttr()} ODBC documentation.
522 \section3 How to Build the ODBC Plugin on Unix and \macos
524 It is recommended that you use unixODBC. You can find the latest
525 version and ODBC drivers at \l http://www.unixodbc.org.
526 You need the unixODBC header files and shared libraries.
528 Tell \c qt-cmake where to find the unixODBC header files and shared
529 libraries (here it is assumed that unixODBC is installed in
530 \c{/usr/local/unixODBC}) and build:
532 \snippet code/doc_src_sql-driver.qdoc 11
534 \section3 How to Build the ODBC Plugin on Windows
536 The ODBC header and include files should already be installed in the
537 right directories. You just have to build the plugin as follows:
539 \snippet code/doc_src_sql-driver.qdoc 12
542 \section2 QPSQL for PostgreSQL (Version 7.3 and above)
544 The QPSQL driver supports version 7.3 and higher of the PostgreSQL server.
546 For more information about PostgreSQL visit \l http://www.postgresql.org.
548 \section3 QPSQL Unicode Support
550 The QPSQL driver automatically detects whether the PostgreSQL
551 database you are connecting to supports Unicode or not. Unicode is
552 automatically used if the server supports it. Note that the driver
553 only supports the UTF-8 encoding. If your database uses any other
554 encoding, the server must be compiled with Unicode conversion
557 Unicode support was introduced in PostgreSQL version 7.1 and it will
558 only work if both the server and the client library have been compiled
559 with multibyte support. More information about how to set up a
560 multibyte enabled PostgreSQL server can be found in the PostgreSQL
561 Administrator Guide, Chapter 5.
563 \section3 QPSQL Case Sensitivity
565 PostgreSQL databases will only respect case sensitivity if the table or field
566 name is quoted when the table is created. So for example, a SQL query such
569 \snippet code/doc_src_sql-driver.qdoc 39
571 will ensure that it can be accessed with the same case that was used. If the
572 table or field name is not quoted when created, the actual table name
573 or field name will be lower-case. When QSqlDatabase::record() or
574 QSqlDatabase::primaryIndex() access a table or field that was unquoted
575 when created, the name passed to the function must be lower-case to
576 ensure it is found. For example:
578 \snippet code/doc_src_sql-driver.qdoc 40
580 \section3 QPSQL Forward-only query support
582 To use forward-only queries, you must build the QPSQL plugin with
583 PostreSQL client library version 9.2 or later. If the plugin is
584 built with an older version, then forward-only mode will not be
585 available - calling QSqlQuery::setForwardOnly() with \c true will
588 \warning If you build the QPSQL plugin with PostgreSQL version 9.2 or later,
589 then you must distribute your application with libpq version 9.2 or later.
590 Otherwise, loading the QPSQL plugin will fail with the following message:
592 \snippet code/doc_src_sql-driver.qdoc 35
594 While navigating the results in forward-only mode, the handle of
595 QSqlResult may change. Applications that use the low-level handle of
596 SQL result must get a new handle after each call to any of QSqlResult
597 fetch functions. Example:
599 \snippet code/doc_src_sql-driver_snippet.cpp 36
601 While reading the results of a forward-only query with PostgreSQL,
602 the database connection cannot be used to execute other queries.
603 This is a limitation of libpq library. Example:
605 \snippet code/doc_src_sql-driver.cpp 37
607 This problem will not occur if query1 and query2 use different
608 database connections, or if we execute query2 after the while loop.
610 \note Some methods of QSqlDatabase like tables(), primaryIndex()
611 implicitly execute SQL queries, so these also cannot be used while
612 navigating the results of forward-only query.
614 \note QPSQL will print the following warning if it detects a loss of
617 \snippet code/doc_src_sql-driver.qdoc 38
619 \section3 Connection options
620 The Qt PostgreSQL plugin honors all connection options specified in the
621 \l {https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS}
622 {connect()} PostgreSQL documentation.
624 \section3 How to Build the QPSQL Plugin on Unix and \macos
626 You need the PostgreSQL client library and headers installed.
628 To make \c qt-cmake find the PostgreSQL header files and shared
629 libraries, build the plugin the following way (assuming that the
630 PostgreSQL client is installed in \c{/usr/local/pgsql}):
632 \snippet code/doc_src_sql-driver.qdoc 13
634 \section3 How to Build the QPSQL Plugin on Windows
636 Install the appropriate PostgreSQL developer libraries for your
637 compiler. Assuming that PostgreSQL was installed in \c{C:\pgsql},
638 build the plugin as follows:
640 \snippet code/doc_src_sql-driver.qdoc 15
642 Users of MinGW may wish to consult the following online document:
643 \l{http://www.postgresql.org/docs/current/static/installation-platform-notes.html#INSTALLATION-NOTES-MINGW}{PostgreSQL MinGW/Native Windows}.
645 When you distribute your application, remember to include libpq.dll
646 in your installation package. It must be placed in the same folder
647 as the application executable.
650 \section2 QDB2 for IBM DB2 (Version 7.1 and above)
652 The Qt DB2 plugin makes it possible to access IBM DB2 databases. It
653 has been tested with IBM DB2 v7.1 and 7.2. You must install the IBM
654 DB2 development client library, which contains the header and library
655 files necessary for compiling the QDB2 plugin.
657 The QDB2 driver supports prepared queries, reading/writing of Unicode
658 strings and reading/writing of BLOBs.
660 We suggest using a forward-only query when calling stored procedures
661 in DB2 (see QSqlQuery::setForwardOnly()).
663 \section3 Connection options
664 The Qt IBM DB2 plugin honors the following connection options:
666 \header \li Attribute \li Possible value
668 \li SQL_ATTR_ACCESS_MODE
669 \li SQL_MODE_READ_ONLY: open the database in read-only mode\br
670 SQL_MODE_READ_WRITE: open the database in read-write mode (default)
672 \li SQL_ATTR_LOGIN_TIMEOUT
673 \li Number of seconds to wait for the database connection
674 during login (max: 32767, a value of 0 will wait forever)
677 \section3 How to Build the QDB2 Plugin on Unix and \macos
679 \snippet code/doc_src_sql-driver.qdoc 18
681 \section3 How to Build the QDB2 Plugin on Windows
683 The DB2 header and include files should already be installed in the
684 right directories. You just have to build the plugin as follows:
686 \snippet code/doc_src_sql-driver.qdoc 20
689 \section2 QSQLITE for SQLite (Version 3 and above)
691 The Qt SQLite plugin makes it possible to access SQLite
692 databases. SQLite is an in-process database, which means that it
693 is not necessary to have a database server. SQLite operates on a
694 single file, which must be set as the database name when opening
695 a connection. If the file does not exist, SQLite will try to
696 create it. SQLite also supports in-memory and temporary databases. Simply
697 pass respectively ":memory:" or an empty string as the database name.
699 SQLite has some restrictions regarding multiple users and
700 multiple transactions. If you try to read/write on a resource from different
701 transactions, your application might freeze until one transaction commits
702 or rolls back. The Qt SQLite driver will retry to write to a locked resource
703 until it runs into a timeout (see \c{QSQLITE_BUSY_TIMEOUT}
704 at QSqlDatabase::setConnectOptions()).
706 In SQLite any column, with the exception of an INTEGER PRIMARY KEY column,
707 may be used to store any type of value. For instance, a column declared as
708 INTEGER may contain an integer value in one row and a text value in the
709 next. This is due to SQLite associating the type of a value with the value
710 itself rather than with the column it is stored in. A consequence of this
711 is that the type returned by QSqlField::type() only indicates the field's
712 recommended type. No assumption of the actual type should be made from
713 this and the type of the individual values should be checked.
715 The driver is locked for updates while a select is executed. This
716 may cause problems when using QSqlTableModel because Qt's item views
717 fetch data as needed (with QSqlQuery::fetchMore() in the case of
720 You can find information about SQLite on \l{http://www.sqlite.org}.
722 \section3 Connection options
723 The Qt SQLite plugin honors the following connection options:
725 \header \li Attribute \li Possible value
727 \li QSQLITE_BUSY_TIMEOUT
728 \li Busy handler timeout in milliseconds (val <= 0: disabled),
729 see \l {https://www.sqlite.org/c3ref/busy_timeout.html}
730 {SQLite documentation} for more information
733 \li QSQLITE_USE_QT_VFS
734 \li If set, the database is opened using Qt's VFS which allows to
735 open databases using QFile. This way it can open databases from
736 any read-write locations (e.g.android shared storage) but also
737 from read-only resources (e.g. qrc or android assets). Be aware
738 that when opening databases from read-only resources make sure
739 you add QSQLITE_OPEN_READONLY attribute as well.
740 Otherwise it will fail to open it.
743 \li QSQLITE_OPEN_READONLY
744 \li If set, the database is open in read-only mode which will fail
745 if no database exists. Otherwise the database will be opened in
746 read-write mode and created if the database file does not yet
750 \li The given filename is interpreted as an uri, see
751 \l {https://www.sqlite.org/c3ref/open.html} {SQLITE_OPEN_URI}
753 \li QSQLITE_ENABLE_SHARED_CACHE
754 \li If set, the database is opened in
755 \l {https://www.sqlite.org/sharedcache.html} {shared cache mode},
756 otherwise in private cache mode
758 \li QSQLITE_ENABLE_REGEXP
759 \li If set, the plugin defines a function 'regex' which can be used
760 in queries, QRegularExpression is used for evaluation of the regex query
762 \li QSQLITE_NO_USE_EXTENDED_RESULT_CODES
763 \li Disables the usage of the \l {https://www.sqlite.org/c3ref/extended_result_codes.html}
764 {extended result code} feature in SQLite
766 \li QSQLITE_ENABLE_NON_ASCII_CASE_FOLDING
767 \li If set, the plugin replaces the functions 'lower' and 'upper' with
768 QString functions for correct case folding of non-ascii characters
770 \li QSQLITE_OPEN_NOFOLLOW
771 \li If set, the database filename is not allowed to contain a symbolic link
774 \section3 How to Build the QSQLITE Plugin
776 SQLite version 3 is included as a third-party library within Qt.
777 It can be built by passing the \c{-DFEATURE_system_sqlite=OFF} parameter to the
778 \c qt-cmake command line.
780 If you do not want to use the SQLite library included with Qt, you
781 can pass \c{-DFEATURE_system_sqlite=ON} to the \c qt-cmake command
782 line to use the SQLite libraries of the operating system. This is
783 recommended whenever possible, as it reduces the installation size
784 and removes one component for which you need to track security
787 On Unix and \macos (replace \c $SQLITE with the directory where
790 \snippet code/doc_src_sql-driver.qdoc 21
792 On Windows (assuming that SQLite is installed in \c{C:\SQLITE}):
794 \snippet code/doc_src_sql-driver.qdoc 23
796 \section3 Enable REGEXP operator
798 SQLite comes with a REGEXP operation. However the needed implementation must
799 be provided by the user. For convenience a default implementation can be
800 enabled by \l{QSqlDatabase::setConnectOptions()} {setting the connect
801 option} \c{QSQLITE_ENABLE_REGEXP} before \l{QSqlDatabase::open()} {the
802 database connection is opened}. Then a SQL statement like "column REGEXP
803 'pattern'" basically expands to the Qt code
805 \snippet code/doc_src_sql-driver.cpp 34
807 For better performance the regular expressions are cached internally. By
808 default the cache size is 25, but it can be changed through the option's
809 value. For example passing "\c{QSQLITE_ENABLE_REGEXP=10}" reduces the
812 \section3 QSQLITE File Format Compatibility
814 SQLite minor releases sometimes break file format forward compatibility.
815 For example, SQLite 3.3 can read database files created with SQLite 3.2,
816 but databases created with SQLite 3.3 cannot be read by SQLite 3.2.
817 Please refer to the SQLite documentation and change logs for information about
818 file format compatibility between versions.
820 Qt minor releases usually follow the SQLite minor releases, while Qt patch releases
821 follow SQLite patch releases. Patch releases are therefore both backward and forward
824 To force SQLite to use a specific file format, it is necessary to build and
825 ship your own database plugin with your own SQLite library as illustrated above.
826 Some versions of SQLite can be forced to write a specific file format by setting
827 the \c{SQLITE_DEFAULT_FILE_FORMAT} define when building SQLite.
830 \section2 QMIMER for Mimer SQL version 11 and higher
832 The Qt Mimer SQL plugin makes it possible to work with the Mimer SQL RDBMS.
833 Mimer SQL provides small footprint, scalable and robust relational database
834 solutions that conform to international ISO SQL standards. Mimer SQL is available
835 on Windows, Linux, \macos, and OpenVMS as well as several embedded platforms like QNX, Android,
838 Mimer SQL fully support Unicode. To work with Unicode data the column types National Character (NCHAR),
839 National Character Varying (NVARCHAR), or National Character Large Object (NCLOB) must be used.
840 For more information about Mimer SQL and unicode, see \l{https://developer.mimer.com/features/multilingual-support}
842 \section3 QMIMER Stored Procedure Support
844 Mimer SQL have stored procedures according to the SQL standard (PSM) and
845 the plugin fully support IN, OUT, INOUT parameters as well as resultset procedures.
847 Example stored procedure with INOUT and OUT parameters:
849 \snippet code/doc_src_sql-driver.qdoc 44
851 Source code to access the INOUT and OUT values:
853 \snippet code/doc_src_sql-driver.cpp 40
855 \section3 How to Build the QMIMER Plugin on Unix and \macos
857 You need the Mimer SQL header files and shared libraries. Get them by installing
858 any of the Mimer SQL variants found at \l{https://developer.mimer.com}.
861 \snippet code/doc_src_sql-driver.qdoc 31
863 \section3 How to Build the QMIMER Plugin on Windows
865 You need the Mimer SQL header files and shared libraries. Get them by installing
866 any of the Mimer SQL variants found at \l{https://developer.mimer.com}.
868 \snippet code/doc_src_sql-driver.qdoc 32
871 \section2 QIBASE for Borland InterBase
873 The Qt InterBase plugin makes it possible to access the InterBase and
874 Firebird databases. InterBase can either be used as a client/server or
875 without a server in which case it operates on local files. The
876 database file must exist before a connection can be established. Firebird
877 must be used with a server configuration.
879 Note that InterBase requires you to specify the full path to the
880 database file, no matter whether it is stored locally or on another
883 \section3 Connection options
884 The Qt Borland InterBase plugin honors the following connection options:
886 \header \li Attribute \li Possible value
888 \li ISC_DPB_SQL_ROLE_NAME
889 \li Specifies the login role name
893 \section3 How to Build the QIBASE Plugin
894 \snippet code/doc_src_sql-driver.cpp 24
896 You need the InterBase/Firebird development headers and libraries
897 to build this plugin.
899 Due to license incompatibilities with the GPL, users of the Qt Open Source
900 Edition are not allowed to link this plugin to the commercial editions of
901 InterBase. Please use Firebird or the free edition of InterBase.
903 \section3 QIBASE Stored procedures
905 InterBase/Firebird return OUT values as result set, so when calling stored
906 procedure, only IN values need to be bound via QSqlQuery::bindValue(). The
907 RETURN/OUT values can be retrieved via QSqlQuery::value(). Example:
909 \snippet code/doc_src_sql-driver.cpp 26
911 \section3 How to Build the QIBASE Plugin on Unix and \macos
913 The following assumes InterBase or Firebird is installed in
916 If you are using InterBase:
918 \snippet code/doc_src_sql-driver.qdoc 27
920 If you are using Firebird, the Firebird library has to be set explicitly:
922 \snippet code/doc_src_sql-driver.qdoc 28
924 \section3 How to Build the QIBASE Plugin on Windows
926 The following assumes InterBase or Firebird is installed in
929 If you are using InterBase:
931 \snippet code/doc_src_sql-driver.qdoc 29
933 If you are using Firebird:
935 \snippet code/doc_src_sql-driver.qdoc 30
937 Note that \c{C:\interbase\bin} must be in the \c PATH.
939 \target troubleshooting
940 \section1 Troubleshooting
942 You should always use client libraries that have been compiled with
943 the same compiler as you are using for your project. If you cannot get
944 a source distribution to compile the client libraries yourself, you
945 must make sure that the pre-compiled library is compatible with
946 your compiler, otherwise you will get a lot of "undefined symbols"
947 errors. Some compilers have tools to convert libraries, e.g. Borland
948 ships the tool \c{COFF2OMF.EXE} to convert libraries that have been
949 generated with Microsoft Visual C++.
951 If the compilation of a plugin succeeds but it cannot be loaded,
952 make sure that the following requirements are met:
955 \li Ensure that the plugin is in the correct directory. You can use
956 QApplication::libraryPaths() to determine where Qt looks for plugins.
957 \li Ensure that the client libraries of the DBMS are available on the
958 system. On Unix, run the command \c{ldd} and pass the name of the
959 plugin as parameter, for example \c{ldd libqsqlmysql.so}. You will
960 get a warning if any of the client libraries could not be found.
961 On Windows, you can use Visual Studio's dependency walker. With
962 Qt Creator, you can update the \c PATH environment variable in the
963 \gui Run section of the \gui Project panel to include the path to
964 the folder containing the client libraries.
965 \li Compile Qt with \c{QT_DEBUG_PLUGINS} defined to get very verbose
966 debug output when loading plugins.
969 Make sure you have followed the guide to \l{Deploying Plugins}.
972 \section1 How to Write Your Own Database Driver
974 QSqlDatabase is responsible for loading and managing database driver
975 plugins. When a database is added (see QSqlDatabase::addDatabase()),
976 the appropriate driver plugin is loaded (using QSqlDriverPlugin).
977 QSqlDatabase relies on the driver plugin to provide interfaces for
978 QSqlDriver and QSqlResult.
980 QSqlDriver is an abstract base class which defines the functionality
981 of a SQL database driver. This includes functions such as
982 QSqlDriver::open() and QSqlDriver::close(). QSqlDriver is responsible
983 for connecting to a database, establish the proper environment, etc.
984 In addition, QSqlDriver can create QSqlQuery objects appropriate for
985 the particular database API. QSqlDatabase forwards many of its
986 function calls directly to QSqlDriver which provides the concrete
989 QSqlResult is an abstract base class which defines the functionality
990 of a SQL database query. This includes statements such as \c{SELECT},
991 \c{UPDATE}, and \c{ALTER} \c{TABLE}. QSqlResult contains functions
992 such as QSqlResult::next() and QSqlResult::value(). QSqlResult is
993 responsible for sending queries to the database, returning result
994 data, etc. QSqlQuery forwards many of its function calls directly to
995 QSqlResult which provides the concrete implementation.
997 QSqlDriver and QSqlResult are closely connected. When implementing a
998 Qt SQL driver, both of these classes must to be subclassed and the
999 abstract virtual methods in each class must be implemented.
1001 To implement a Qt SQL driver as a plugin (so that it is
1002 recognized and loaded by the Qt library at runtime), the driver
1003 must use the Q_PLUGIN_METADATA() macro. Read \l{How to Create Qt
1004 Plugins} for more information on this. You can also check out how
1005 this is done in the SQL plugins that are provided with Qt in
1006 \c{QTDIR/qtbase/src/plugins/sqldrivers}.
1008 The following code can be used as a skeleton for a SQL driver:
1010 \snippet sqldatabase/sqldatabase.cpp 47
1012 \snippet sqldatabase/sqldatabase.cpp 48