Executing SQL Statements

Navigating the Result Set

QSqlQuery provides access to the result set one record at a time. After the call to exec(), QSqlQuery's internal pointer is located one position before the first record. We must call QSqlQuery::next() once to advance to the first record, then next() again repeatedly to access the other records, until it returns false. Here's a typical loop that iterates over all the records in order:

     while (query.next()) {
         QString name = query.value(0).toString();
         int salary = query.value(1).toInt();
         qDebug() << name << salary;
     }

The QSqlQuery::value() function returns the value of a field in the current record. Fields are specified as zero-based indexes. QSqlQuery::value() returns a QVariant, a type that can hold various C++ and core Qt data types such as int, QString, and QByteArray. The different database types are automatically mapped into the closest Qt equivalent. In the code snippet, we call QVariant::toString() and QVariant::toInt() to convert variants to QString and int.

For an overview of the recommended types for use with Qt-supported Databases, please refer to this table.

You can iterate back and forth using QSqlQuery::next(), QSqlQuery::previous(), QSqlQuery::first(), QSqlQuery::last(), and QSqlQuery::seek(). The current row index is returned by QSqlQuery::at(), and the total number of rows in the result set is avaliable as QSqlQuery::size() for databases that support it.

To determine whether a database driver supports a given feature, use QSqlDriver::hasFeature(). In the following example, we call QSqlQuery::size() to determine the size of a result set of the underlying database supports that feature; otherwise, we navigate to the last record and use the query's position to tell us how many records there are.

     QSqlQuery query;
     int numRows;
     query.exec("SELECT name, salary FROM employee WHERE salary > 50000");

     QSqlDatabase defaultDB = QSqlDatabase::database();
     if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) {
         numRows = query.size();
     } else {
         // this can be very slow
         query.last();
         numRows = query.at() + 1;
     }

If you iterate through a result set only using next() and seek() with positive values, you can call QSqlQuery::setForwardOnly(true) before calling exec(). This is an easy optimization that will speed up the query significantly when operating on large result sets.

     QSqlQuery query;
     query.exec("INSERT INTO employee (id, name, salary) "
                "VALUES (1001, 'Thad Beaumont', 65000)");

     QSqlQuery query;
     query.prepare("INSERT INTO employee (id, name, salary) "
                   "VALUES (:id, :name, :salary)");
     query.bindValue(":id", 1001);
     query.bindValue(":name", "Thad Beaumont");
     query.bindValue(":salary", 65000);
     query.exec();

     QSqlQuery query;
     query.prepare("INSERT INTO employee (id, name, salary) "
                   "VALUES (?, ?, ?)");
     query.addBindValue(1001);
     query.addBindValue("Thad Beaumont");
     query.addBindValue(65000);
     query.exec();

     QSqlQuery query;
     query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");

     QSqlQuery query;
     query.exec("DELETE FROM employee WHERE id = 1007");

     QSqlDatabase::database().transaction();
     QSqlQuery query;
     query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
     if (query.next()) {
         int employeeId = query.value(0).toInt();
         query.exec("INSERT INTO project (id, name, ownerid) "
                    "VALUES (201, 'Manhattan Project', "
                    + QString::number(employeeId) + ')');
     }
     QSqlDatabase::database().commit();