Saturday, July 24, 2010

SQLite: 3.Qt and SQLite

I mentioned before that SQLite is a C library. To use this library in C++ applications, you can use many wrappers. But since I'm going in the direction of Qt, I want to use the QtSql module. QtSql module uses some drivers to communicate with databases like SQLite, MySQL, Oracle ...

Or add
//in the *.pro file
QT += sql
and include
//in any class
#include <QtSql>

The process is very easy: Create a database object. Open it. Then execute some queries.

For this entry, I created a project to explain the basic features of QtSql. I will try to explain some parts of it, and you can download it and try to understand the complete code.

Viewing Table Contents:

To view table contents, you need a QSqlTableModel object. QSqlTableModel is a high-level interface for reading and writing database records from a single table.

A simple way to use it is like this:
After creating a database object, I create a QSqlTableModel object and connect it to my database. Then I name the table I want this object to represent.
QSqlDatabase *database = new QSqlDatabase();

//set database driver to QSQLITE
*database = QSqlDatabase::addDatabase("QSQLITE");


QSqlTableModel *all_model = new QSqlTableModel(this, *database);



Then I can use this model to preview the contents of this table in some 'item views' available in the GUI designers (List View, Tree View, Table View, Column View)


QSqlTableModel has some other methods like: sorting, filtering, editing a record...etc. For example, searching a database and viewing results can be implemented by filtering the current model:

//search database for a certain name
search_model->setFilter("Name = \'"+ui->search_lineEdit->text()+"\'");


QSqlQuery is a class that provides a means of executing and manipulating SQL statements. Its simplest statement looks like this:
QSqlQuery query("delete from Contacts where Mobile = 1234");

The exec() method returns 'true' if query is successful, and 'false' if it fails.

To make a query containing some variables, you can do this:

QSqlQuery query ("insert into Contacts (Name, Mobile, City) values (:name, :mobile, :city)");
    query.bindValue(0, ui->add_name_lineEdit->text());
    query.bindValue(1, ui->add_mobile_lineEdit->text());
    query.bindValue(2, ui->add_city_lineEdit->text());

        QMessageBox::warning(0,"Error", "Please check your entered data.");

where 0, 1, and 2 are the positions of the variables :name, :mobile, and :city. You can also replace the positions by the variable names:

    query.bindValue(":name", ui->add_name_lineEdit->text());
    query.bindValue(":mobile", ui->add_mobile_lineEdit->text());
    query.bindValue(":address", ui->add_city_lineEdit->text();

Note: sometimes i needed to change the query depending on a certain condition. One way to do this is using the prepare() method:

    QSqlQuery query;

         query.prepare("delete from Contacts where Name = \'" + ui->remove_lineEdit->text()+"\'");
        query.prepare("delete from Contacts where Mobile = \'" + ui->remove_lineEdit->text()+"\'");

        QMessageBox::warning(0,"Error", "Please check your entered data.");

The Complete Example:

Now, here is the complete example for Qt with SQLite. It is a phonebook application that views all the contacts (using QSqlTableModel and a Table View), searches for a specific contact (using the filter() method of QSqlTableModel), and adds or removes a contact (using the queries).

Test it and read the code carefully. You can ask me anything you want.


Creative Commons License
Blog Example by Mahmoud Adly Ezzat is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Monday, July 12, 2010

SQLite: 2.Administration Tool

After making some operations on a SQLite database using terminal, it would be better to have an easier and a more convenient tool. I wanted a free and easy-to-use program and i found Lita.

Lita is a free SQLite database administration tool for Windows, MacOSX and Linux. Using Lita, you can:
  • Open, create, compact, manage SQLite databases
  • Create, rename, delete, and empty tables
  • Create, rename and delete columns
  • Create, modify and delete records
  • Encrypt or re-encrypt your databases
  • Run, import and export your custom SQL statements
  • Create and delete indices

Once I opened this program, I didn't need any tutorial to start using it. You should try it yourself.

Thursday, July 8, 2010

SQLite: 1.Terminal Commands Tutorial

SQLite is a C library that implements a database engine. It is a perfect choice for embedded systems because of its small size and big capabilities. It may have some missing features (click me) but it is still a good choice (click me). 

 I want to start with a nice tutorial that uses only a command-line program for accessing and modifying SQLite databases ( This tutorial does not require any previous knowledge of SQL databases. And even if you know, I guess you will not get bored because these are short and divided videos, so you can jump through them or have a quick overlook.

Sunday, July 4, 2010

Static Building for Qt Apps on Windows

I have talked before about dynamic building (click me) and now is the time for the static building. This is a long process that has many steps but I have to share my knowledge for those who are interested because I didn’t find a page that goes throw the entire process in details.
The main reason for static building is collecting all the needed DLLs that you need in a single executable file (*.exe), so the target computer does not need to install any packages or programs.

The steps are as follows:
1-    Configuring Qt Creator for static building, and compiling it.
2-    Building your application project statically using the configured Qt Creator.

1-    Configuring Qt Creator for static building:
Unfortunately, the normal Qt Creator that you install is only configured for static building. So you have to do one of the following:

1.1-    Download the Qt-Everywhere project and build your own customized Qt Creator:
-    This is done by downloading the (
-    Uncompress the file in a directory like C:\Qt\4.6.3
-    Use Visual Studio Command Prompt to browse to this folder (is should be containing a ‘configure’ file).
-    Assuming you have Visual Studio 2005 or 2008, configure this project for windows with a command like this from Visual Studio Command Prompt (not tested):

configure -platform win32-msvc2008 –static –release -nomake demos -nomake examples –opensource -confirm-license -no-exceptions

for more configure options: type
configure –help
or download this file.

This may take about 30 minutes, and when done you can type
nmake sub-src
    then just relax and find something to do in the next two hours (give or take an hour)

1.2-    If you have already installed Qt Creator on your PC:
You can reconfigure it to build statically by writing the following command in the ‘Qt Command Prompt’ (I prefer taking a copy and configuring it, so I use the original for developing and debuggingm, and the new one for the final build):
configure -static -release -no-exceptions -nomake demos -nomake examples –opensource -confirm-license

then when done after about 30 minutes or less, type
mingw32-make sub-src

then just relax and find something to do in the next two hours (give or take an hour)

Now you have a Qt Creator configured for static building.

2-    Building your application project statically using the configured Qt Creator:

2.1 Create any Qt project you want, than add the following lines in the project (*.pro) file:
win32 {
      QMAKE_LFLAGS += -static-libgcc

2.2 Now go to the Qt Command Prompt and browse to the folder of your application and type

Where: is the project file of my Qt application.

qmake is the file used to create the Makefile.

C:\Qt\Qt-2010.01-static\qt\bin\ is the directory of qmake in the newly compiled Qt Creator.

2.3 Now write

2.4 The statically built application can be found in the ‘Release’ folder in your project.

Some Comments:

1-    Why bothering myself and doing all these steps? For me, nothing is wrong with adding the needed DLLs in the same folder with the dynamically built application.
2-    There is this lovely warning that appears while configuring Qt:
WARNING: Using static linking will disable the use of plugins. Make sure you compile ALL needed modules into the library.
So I have to make sure that all the plugins that I may use in the future are available. (Click here for more:step3)

Example: Simple Map