SQLite database with Qt – step by step

Accessing SQL databases from C++ applications is very simple with Qt library. Here is some short example that presents how to do it. I have chosen SQLite engine because it’s the easiest engine to set up (it requires no server, no configuration…), still it’s suitable for the most of possible applications.

Before you begin – sql drivers

Qt requires drivers to deal with SQL databases. Depending on your distribution, you can have the drivers installed by default with your Qt or not. If you face problems like “QSqlDatabase: QSQLITE driver not loaded” you need to install drivers manually. Error should be followed by a console output that informs you which drivers you have installed: “QSqlDatabase: available drivers:”. Read more about sql drivers…

Design and create the database

Let’s assume, for simplicity, that we need to store people data — only name and corresponding id. With sqlite you can create such a simple database with two console commands. First line is creating database people. Second one is creating the table people. It cointains a key (integer) and name (text). To leave the sqlite console just type “.quit”.

$ sqlite3 people.db
sqlite> CREATE TABLE people(ids integer primary key, name text);
sqlite> .quit

Congrats, your database is ready. Now we can create C++ application to play with it.

Access database from Qt application

Create new Qt project. In .pro file you need to add:

QT += sql

It will link your project against QtSql module. If you don’t use QtCreator but another IDE, for example Visual Studio, your project may need setting linker dependency to QtSql.lib. You need to do it if your compilation fails with unresolved external symbol error. In VS linker settings is located in Properties/Configuration properties/Linker/Input. In the Additional Dependencies add Qt5Sqld.lib for Debug and Qt5Sql.lib for release.

Now you can create a class responsible for database access. Let’s call it DbManager.

class DbManager
{
public:
    DbManager(const QString& path);
private:
    QSqlDatabase m_db;
};

The constructor of DbManager sets up the database connection and opens it. The path argument value is the path to your database file.

DbManager::DbManager(const QString& path)
{
   m_db = QSqlDatabase::addDatabase("QSQLITE");
   m_db.setDatabaseName(path);

   if (!m_db.open())
   {
      qDebug() << "Error: connection with database fail";
   }
   else
   {
      qDebug() << "Database: connection ok";
   }
}

Define needed operations

For managing people resources we need the possibility to:

  • add new person,
  • remove person,
  • check if person exists in database,
  • print all persons,
  • delete all persons.

Actions can be implemented in following way: define QSqlQuery, then call prepare method — it can be raw SQL query string, or it can contain placeholders for variables — placeholders must begin with colon. After prepare call bindValue to fill the placeholders with proper values. When the query is ready, execute it.

bool DbManager::addPerson(const QString& name)
{
   bool success = false;
   // you should check if args are ok first...
   QSqlQuery query;
   query.prepare("INSERT INTO people (name) VALUES (:name)");
   query.bindValue(":name", name);
   if(query.exec())
   {
       success = true;
   }
   else
   {
        qDebug() << "addPerson error:  "
                 << query.lastError();
   }

   return success;
}

QSqlQuery::lastError() is very useful, you should not skip it in your implementation. Rules of creating queries are simple, so I think you already know how removing person looks like:

if (personExists(name))
{
   QSqlQuery query;
   query.prepare("DELETE FROM people WHERE name = (:name)");
   query.bindValue(":name", name);
   success = query.exec();

   if(!success)
   {
       qDebug() << "removePerson error: "
                << query.lastError();
   }
}

Print all persons:

QSqlQuery query("SELECT * FROM people");
int idName = query.record().indexOf("name");
while (query.next())
{
   QString name = query.value(idName).toString();
   qDebug() << name;
}

Check if person with specific name exists:

QSqlQuery query;
query.prepare("SELECT name FROM people WHERE name = (:name)");
query.bindValue(":name", name);

if (query.exec())
{
   if (query.next())
   {
      // it exists
   }
}

To remove all persons, just run:

QSqlQuery query;
query.prepare("DELETE FROM people");
query.exec();

You can find the whole source code on my github. All you need to do before running this example is to create your SQLite database and write the valid path to it in main.cpp.

Advertisements

14 thoughts on “SQLite database with Qt – step by step

  1. GFURSLF says:

    The code to open the database is wrong.

    m_db = QSqlDatabase::addDatabase(“QSQLITE”);
    m_db.setDatabaseName(path);

    if (!m_db.open())
    {
    qDebug() << "Error: connection with database fail";

    The above error can NEVER happen. It is dead code.

    Like

  2. saim says:

    if path folder is invalid ‘open()’ method returns false. Otherwise folder path is true and if file doesn’t exist, the function creates db file. Maybe you can use QFile class whether file exists. I tried on windows

    Like

  3. Gema Putra says:

    Just letting you know, there are 2 of your code snippets above which are html-malformed. the & (ampersand) is escaped. I know those will do no harm for someone who get used to code, but it may confuse copy-paster or newbie.
    Try searching (ctrl+f) for “&”.

    Regards,

    Like

    • katecpp says:

      I’m sorry azispratama, but I don’t know – I’ve never met cdb extension. If cdb is really editable with sqlite then it should be totally possible to rewrite this app in Qt. You should try :)

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s