QSqlQuery and QSqlTableModel

Once we have established a connection to a database is quite easy to perform operations on its tables.

Qt let us do it in two ways: through QSqlQuery and QSqlTableModel.

The first way makes direct usage of SQL statements: we create a QSqlQuery object, execute a SQL query on it, and check the result.

For instance, to perform a select we could do something like that:

QSqlQuery query;
query.exec("SELECT sku, name, price FROM products WHERE price < 3");

if(query.isActive() == false) // 1.
    qDebug("Database error: %s", query.lastError().text().toStdString().c_str());

while(query.next()) // 2.
{
    QString sku = query.value(0).toString();
    QString name = query.value(1).toString();
    double price = query.value(2).toDouble();

    qDebug("%s %s: %f", sku.toStdString().c_str(), name.toStdString().c_str(), price);
}

1. the flag active is set to false in case of error
2. the result of performing a select is populating the query resultset. Its next() method allows us to navigate in it.

To perform an insert we write code like this:

QSqlQuery query;
query.exec("INSERT INTO products (id, sku, name, price) "
            "VALUES (42, 'X42', 'Something wierd', 2.02)");
if(query.numRowsAffected() == -1)
{
    qDebug("Database error: %s", query.lastError().text().toStdString().c_str());
}

If the number of affected rows is -1 that means an error occurred.

It is possible to prepare a query and then binding the values on the prepared statement, using both the Oracle and the ODBC syntax.

Using QSqlTableModel we can avoid the direct usage of raw SQL statements, letting Qt to generate the actual SQL code accordingly to the database we are using.

For instance, to execute the same select as above we can write something like this:

QSqlTableModel model;
model.setTable("products");
model.setFilter("price < 3");
model.select();

for(int i = 0; i < model.rowCount(); ++i)
{
    QSqlRecord record = model.record(i);

    int id = record.value(0).toInt();
    QString sku = record.value(1).toString();
    QString name = record.value(2).toString();
    double price = record.value(3).toDouble();

    qDebug("%d %s %s: %f", id, sku.toStdString().c_str(), name.toStdString().c_str(), price);
}

I wrote this post while reading "C++ GUI Programming with Qt 4, Second Edition" by Jasmin Blanchette and Mark Summerfield.