In this chapter, we will cover the following recipes:
- Setting up SQL Driver for Qt
- Connecting to a database
- Writing basic SQL queries
- Creating a login screen with Qt
- Displaying information from a database on a model view
- Advanced SQL queries
SQL stands for Structured Query Language, a special programming language used to manage data held in a relational database management system. A SQL server is a database system designed to use one of the many types of SQL programming language to manage its data.
Note
If you want to learn more about SQL, visit this link: http://www.w3schools.com/sql/sql_intro.asp.
Qt supports several different types of SQL driver in the form of plugins/add-ons. However, it's very easy to integrate these drivers to your Qt project. We will learn how to do it in the following example.
Let's set up our SQL server before we dive into Qt:
- Before setting up Qt for SQL, we need to install and set up a MySQL server. There are many ways you can install it. The first method is to download MySQL from the official website at http://dev.mysql.com/downloads/mysql/ and install it. After that, you also need to install the MySQL Workbench from http://dev.mysql.com/downloads/workbench/ to administrate your databases.
- An alternative method is to install a third-party package that comes with MySQL and other useful applications, such as Apache web server, phpMyAdmin, and so on, all in a unified installer. Examples of such packages are XAMPP, https://sourceforge.net/projects/xampp/, and AppServ, https://www.appservnetwork.com/en/download/.
- In this example, we will install XAMPP. Open up your web browser, download the XAMPP installer from https://sourceforge.net/projects/xampp/, and proceed to install it on your computer.
- Once you have installed XAMPP, open up XAMPP Control Panel and you should see something like this:
- What we need is the Apache web server and the MySQL database server. Click the Start buttons next to the Apache and MySQL options on the control panel.
- Once the servers have been started, open up your web browser and visit http://localhost/phpmyadmin/. You will see a web interface by the name of PhpMyAdmin that looks like this:
- phpMyAdmin is a web-based utility that help you manage your MySQL databases, much like the official MySQL Workbench. In my opinion, phpMyAdmin is a lot simpler and better suited for beginners, which is why I recommend using it instead of MySQL Workbench.
- By default, phpMyAdmin automatically logs in to MySQL using the default user account
root, which is saved in its configuration file. We don't want to use that for security reasons. So the next thing we need to do is to create an account for ourselves. Go to the Users tab located at the top and once you're on that page, click Add user located at the bottom. Key in your desired username and password in the fields in the login information pane. Choose Local for the Host option for now. At the bottom, you will see options related to Global privileges; check the Check All option and click Go:
- Now that you have created your user account, go to XAMPP Control Panel and click Stop for both Apache and MySQL. Then, click the Config button on the Apache column and select the phpMyAdmin (config.inc.php) option. After that, the
config.inc.phpfile will be opened with your choice of text editor. - Search for the following line in
config.inc.phpand change the wordconfigtocookie:$cfg['Servers'][$i]['auth_type'] = 'config'; $cfg['Servers'][$i]['auth_type'] = 'cookie';
- After that, start Apache and MySQL again by clicking the Start buttons. This way, we force phpMyAdmin to reload its configurations and apply the changes. Go to phpmyAdmin again from your web browser, and this time around, a login screen should appear on the screen:
- Log in to phpMyAdmin, then click on the New link located on the side bar:
- Key in your desired database name and press the Create button. Once it's been created, the database name will appear on the side bar. Click on the database name and it will bring you to another page, which displays a message, No tables found in database. Under the message, you can create your first data table by filling in your desired table name and the number of columns for the table:
- After you click the Go button, you will be brought to another page where you will set up the new table you're going to create. In this example, we created an
employeetable that consists of five columns of data:id,name,age,gender, andmarried:
- Once you are done with that, click Save and now you will be able to see the
employeetable name appear on the side bar. We have successfully installed MySQL and set up our first database and data table. - After that, we need to insert data into the database from phpMyAdmin so that we will be able to retrieve it in the next example. Click on the Insert tab while you're still in the
employeetable; you will then be brought to another page for inserting new data into theemployeetable:
- Next, we will proceed to set up the SQL driver for our Qt project. Basically, all you need to do is to go to your Qt installation folder and look for the
sqldriversfolder. For example, mine is located atC:Qt5.5mingw492_32pluginssqldrivers. - Copy the entire
sqldriversfolder to your project's build directory. You can remove the DLL files that are not relevant to the SQL server you're running. In our case, since we're using a MySQL server, we can delete everything exceptqsqlmysql.dllandqsqlmysqld.dll. The DLL file with the letterdat the back is for debug builds only, while the other one is for release builds. Put those DLL files in their respective build directories, for example,builds/debug/sqldrivers/qsqlmysqld.dllfor debug builds andbuilds/release/sqldrivers/qsqlmysql.dllfor release builds. - The DLL files mentioned in the previous step are the drivers that enable Qt to communicate with different types of SQL architecture. You may also need the DLL file of the SQL client library in order for the driver to work. In our case, we need
libmysql.dllto be located in the same directory as our program's executable. You can either get it from the installation directory of MySQL or download the Connector/C++ package from the official website, https://dev.mysql.com/downloads/connector/cpp/.
Qt provides us with SQL drivers so that we can easily connect to different types of SQL servers without implementing them ourselves.
Currently, Qt officially supports SQLite, MySQL, ODBC, and PostgreSQL. SQL architectures that are forks from one of the supported architectures, such as MariaDB (a fork of MySQL), may still compatible with Qt without much problem.
If you are using an architecture that isn't supported by Qt, you can still interact with your SQL database indirectly by sending an HTTP request using QNetworkAccessManager to your backend script (such as PHP, ASP, JSP, and so on), which can then communicate with the database.
If you only need a simple file-based database and don't plan to use a server-based database, SQLite is a good choice for you.