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");


database->setDatabaseName("./phonebook.db");

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


all_model->setTable("Contacts");

all_model->select();


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)

ui->contacts_tableView->setModel(all_model);

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()+"\'");


Queries:

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");
query.exec();

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());

    if(!query.exec())
    {
        QMessageBox::warning(0,"Error", "Please check your entered data.");
        return;
    }

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;

    if(ui->remove_name_radioButton->isChecked())
    {
         query.prepare("delete from Contacts where Name = \'" + ui->remove_lineEdit->text()+"\'");
    }
    else
    {
        query.prepare("delete from Contacts where Mobile = \'" + ui->remove_lineEdit->text()+"\'");
    }

    if(!query.exec())
    {
        QMessageBox::warning(0,"Error", "Please check your entered data.");
        return;
    }


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.


Download



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

7 comments:

  1. many thanks for this nice example ...

    ReplyDelete
  2. I have try your example, but The no result displayed and cannot add data

    ReplyDelete
    Replies
    1. Did you debug to narrow the problem scope?

      Delete
    2. Hi,
      Try to set database path. (not only "./phonebook.db", full path)


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

      database->setDatabaseName("C:\\Users\\xxxx\\Desktop\\SQLite_example\\debug\\phonebook.db");

      Delete
  3. Thanks for this very good tutorial!

    ReplyDelete
  4. One suggestion: maybe you could add some functionality, like selecting one record in the listbox and edit the item in some textfields...

    ReplyDelete
  5. hey i have lots of file and i want to add path of file in database table and then want to access file according to fault in file or fault size or date time .can u give some suggession how i do it.

    ReplyDelete