CopperSpice API: Executing SQL Statements

The QSqlQuery class provides an interface for executing SQL statements and navigating through the result set of a query. The QSqlQueryModel and QSqlTableModel classes described in the next section provide a higher level interface for accessing databases.

Executing a Query

To execute an SQL statement create a QSqlQuery object and call QSqlQuery::exec().

QSqlQuery query;

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

The QSqlQuery constructor accepts an optional QSqlDatabase object that specifies which database connection to use. In the example above, we do not specify any connection, so the default connection is used. If an error occurs, QSqlQuery::exec() returns false. The error is then available as QSqlQuery::lastError().

Navigating the Result Set

QSqlQuery provides access to a result set one record at a time. After the call to QSqlQuery::exec() the cursor is located one position before the first record in the result set. You need to call QSqlQuery::next() once to advance to the first record and then call it repeatedly to access the subsequent records until it returns false.

The cursor in the QSqlQuery object can be moved by calling QSqlQuery::next(), QSqlQuery::previous(), QSqlQuery::first(), QSqlQuery::last(), and QSqlQuery::seek(). 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("SELECT name, salary FROM employee WHERE salary > 50000");
 
while (query.next()) {
   QString name = query.value(0).toString();
   int salary   = query.value(1).toInt();
 
   qDebug() << name << salary;
}

The QSqlQuery::value() method returns the value of a field in the current record. Fields are specified using zero based indexes. QSqlQuery::value() returns a QVariant which can hold various data types such as int, QString, and QByteArray. The data in the database is automatically mapped to the closest CopperSpice equivalent type.

In the prior example, QVariant::toString() and QVariant::toInt() are used to convert variant values to QString and int values. For an overview of the recommended types for use with CopperSpice supported Databases, refer to Data Types.

Features of a Database

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 if the underlying database supports this feature, otherwise, we navigate to the last record and use the query's position to tell us how many records there are.

int numRows;
 
QSqlQuery query;
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;
}

Inserting, Updating, and Deleting Records

QSqlQuery can execute any SQL statement. The following example inserts a record into a table using an INSERT statement.

QSqlQuery query;

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

If you want to insert multiple records at the same time it is often more efficient to separate the query from the actual values being inserted. This can be done using placeholders. CopperSpice supports two placeholder syntaxes, named binding and positional binding.

QSqlQuery query;
 
// named binding
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();
 
// positional binding
query.prepare("INSERT INTO employee (id, name, salary) VALUES (?, ?, ?)");
 
query.addBindValue(1001);
query.addBindValue("Thad Beaumont");
query.addBindValue(65000);
query.exec();

Both forms work with all database drivers provided by CopperSpice. If the database supports the syntax natively CopperSpice simply forwards the query to the DBMS, otherwise CopperSpice simulates the placeholder syntax by preprocessing the query. The actual query which ends up being executed is available as QSqlQuery::executedQuery().

When using prepared statements you only need to call QSqlQuery::prepare() once. Then call QSqlQuery::bindValue() or QSqlQuery::addBindValue() followed by QSqlQuery::exec() as many times as necessary. Besides performance, one advantage of placeholders is that you can easily specify arbitrary values without having to worry about escaping special characters.

Updating or deleting a record is similar to inserting it into a table.

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

Transactions

Transactions can be used to ensure that a complex operation is atomic (for example, looking up a foreign key and creating a record), or to provide a means of canceling a complex change in the middle.

If the underlying database engine supports transactions, QSqlDriver::hasFeature(QSqlDriver::Transactions) will return true. You can use QSqlDatabase::transaction() to initiate a transaction, followed by the SQL commands you want to execute within the context of the transaction, and then either QSqlDatabase::commit() or QSqlDatabase::rollback(). When using transactions you must start the transaction before you create your query.

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