Connecting and Querying

To execute SQL queries, we must first establish a connection with a database. Typically, database connections are set up in a separate function that we call at application startup. For example:

bool createConnection() { QSqlDatabase *db = QSqlDatabase::addDatabase("QOCI8"); db->setHostName("mozart.konkordia.edu"); db->setDatabaseName("musicdb"); db->setUserName("gbatstone"); db->setPassword("T17aV44"); if (!db->open()) { db->lastError().showMessage(); return false; } return true; }

First, we call QSqlDatabase::addDatabase() to create a QSqlDatabase object. The argument to addDatabase() specifies which database driver Qt must use to access the database. In this case, we use Oracle. The commercial version of Qt 3.2 includes the following drivers: QODBC3 (ODBC), QOCI8 (Oracle), QTDS7 (Sybase Adaptive Server), QPSQL7 (PostgreSQL), QMYSQL3 (MySQL), and QDB2 (IBM DB2). The free and non-commercial editions contain a subset of these.[*] See http://doc.trolltech.com/3.2/sql-driver.html for information on building the database drivers.

[*] The Qt packages on the accompanying CD include SQLite, a public domain in-process database, and QSQLITEX, an experimental driver. These are only intended for use with the examples on the CD.

Next, we set the database host name, the database name, the user name, and the password, and we try to open the connection. If open() fails, we show an error message using QSqlError::showMessage().

Typically, we would call createConnection() in main():

int main(int argc, char *argv[]) { QApplication app(argc, argv); if (!createConnection()) return 1; ... return app.exec(); }

Once a connection is established, we can use QSqlQuery to execute any SQL statement that the underlying database supports. For example, here's how to execute a SELECT statement:

QSqlQuery query; query.exec("SELECT title, year FROM cd WHERE year >= 1998");

After the exec() call, we can navigate through the query's result set:

while (query.next()) { QString title = query.value(0).toString(); int year = query.value(1).toInt(); cerr << title.ascii() << ": " << year << endl; }

We call next() once to position the QSqlQuery on the first record of the result set. Subsequent calls to next() advance the record pointer by one record each time, until the end is reached, at which point next() returns false. If the result set is empty, the first call to next() will return false.

The value() function returns the value of a field as a QVariant. The fields are numbered from 0 in the order given in the SELECT statement. The QVariant class can hold many C++ and Qt types, including int and QString. The different types of data that can be stored in a database are mapped into the corresponding C++ and Qt types and stored in QVariants. For example, a VARCHAR is represented as a QString and a DATETIME as a QDateTime.

QSqlQuery provides some other functions to navigate through the result set: first(), last(), prev(), seek(), and at(). These functions are convenient, but for some databases they can be slow and memory-hungry. For an easy optimization when operating on large data sets, we can call QSqlQuery::setForwardOnly(true) before calling exec(), and then only use next() for navigating the result set.

Earlier we specified the SQL query as an argument to exec(), but we can also pass it directly to the constructor, which executes it immediately:

QSqlQuery query("SELECT title, year FROM cd WHERE year >= 1998");

Here's how we would check for an error and pop up a QMessageBox if a problem occurred:

if (!query.isActive()) query.lastError().showMessage();

Doing an INSERT is almost as easy as doing a SELECT:

QSqlQuery query("INSERT INTO cd (id, artistid, title, year) " "VALUES (203, 102, 'Living in America', 2002)");

After this, QSqlQuery::numRowsAffected() returns the number of rows that were affected by the SQL statement (or -1 if the database cannot provide that information).

If we need to insert a lot of records, or if we want to avoid converting values to strings (and escaping them correctly), we can use prepare() to specify a query that contains placeholders and then bind the values we want to insert. Qt supports both the Oracle-style and the ODBC-style syntax for placeholders for all databases, using native support where it is available and simulating it otherwise. Here's an example that uses the Oracle-style syntax with named placeholders:

QSqlQuery query(db); query.prepare("INSERT INTO cd (id, artistid, title, year) " "VALUES (:id, :artistid, :title, :year)"); query.bindValue(":id", 203); query.bindValue(":artistid", 102); query.bindValue(":title", QString("Living in America")); query.bindValue(":year", 2002); query.exec();

Here's the same example using ODBC-style positional placeholders:

QSqlQuery query(db); query.prepare("INSERT INTO cd (id, artistid, title, year) " "VALUES (?, ?, ?, ?)"); query.addBindValue(203); query.addBindValue(102); query.addBindValue(QString("Living in America")); query.addBindValue(2002); query.exec();

After the call to prepare(), we can call bindValue() or addBindValue() to bind new values, then call exec() again to execute the query with the new values.

Placeholders are often used to specify binary data or strings that contain non-ASCII or non-Latin-1 characters. Behind the scenes, Qt uses Unicode with those databases that support Unicode, and for those that don't, Qt transparently converts strings to the appropriate encoding.

Qt supports SQL transactions on databases where they are available. To start a transaction, we call transaction() on the QSqlDatabase object that represents the database connection. To terminate the transaction, we call either commit() or rollback(). For example, here's how we would look up a foreign key and execute an INSERT statement inside a transaction:

QSqlDatabase::database()->transaction(); QSqlQuery query; query.exec("SELECT id FROM artist WHERE name = 'Gluecifer'"); if (query.next()) { int artistId = query.value(0).toInt(); query.exec("INSERT INTO cd (id, artistid, title, year) " "VALUES (201, " + QString::number(artistId) + ", 'Riding the Tiger', 1997)"); } QSqlDatabase::database()->commit();

The QSqlDatabase::database() function returns a pointer to the QSqlDatabase object we created in createConnection(). If a transaction cannot be started, QSqlDatabase::transaction() returns false.

Some databases don't support transactions. For those, the transaction(), commit(), and rollback() functions do nothing. We can test whether a database supports transactions using hasFeature() on the QSqlDriver associated with the database:

QSqlDriver *driver = QSqlDatabase::database()->driver(); if (driver->hasFeature(QSqlDriver::Transactions)) ...

In the examples so far, we have assumed that the application is using a single database connection. If we want to use multiple connections, we can pass a name as second argument to addDatabase(). For example:

QSqlDatabase *db = QSqlDatabase::addDatabase("QPSQL7", "OTHER"); db->setHostName("saturn.mcmanamy.edu"); db->setDatabaseName("starsdb"); db->setUserName("gilbert"); db->setPassword("ixtapa6");

We can then retrieve a pointer to the QSqlDatabase object by passing the name to QSqlDatabase::database():

QSqlDatabase *db = QSqlDatabase::database("OTHER");

To execute queries using the other connection, we pass the QSqlDatabase object to the QSqlQuery constructor:

QSqlQuery query(db); query.exec("SELECT id FROM artist WHERE name = 'Mando Diao'");

Multiple connections are useful if we want to perform more than one transaction at a time, since each connection can only handle a single active transaction. When we use multiple database connections, we can still have one nameless connection, and QSqlQuery will use that connection if none is specified.

In addition to QSqlQuery, Qt provides the QSqlCursor class, a higher-level class that inherits QSqlQuery and extends it with convenience functions so that we can avoid typing raw SQL for performing the most common SQL operations: SELECT, INSERT, UPDATE, and DELETE. QSqlCursor is also the class that ties a QDataTable to a database. We will cover QSqlCursor here, and in the next section we will see how to use QDataTable, a database-aware QTable subclass.

Here's an example that uses QSqlCursor to perform a SELECT:

QSqlCursor cursor("cd"); cursor.select("year >= 1998");

An equivalent QSqlQuery would be

QSqlQuery query("SELECT id, artistid, title, year FROM cd " "WHERE year >= 1998");

Navigating through the result set is the same as for QSqlQuery, except that we can pass field names to value() instead of field numbers:

while (cursor.next()) { QString title = cursor.value("title").toString(); int year = cursor.value("year").toInt(); cerr << title.ascii() << ": " << year << endl; }

To insert a record into a table, we must first call primeInsert(), which returns a pointer to a new QSqlRecord. Then we call setValue() for each of the fields in the QSqlRecord that we want to set, and we call insert() to insert the QSqlRecord's data into the database. For example:

QSqlCursor cursor("cd"); QSqlRecord *buffer = cursor.primeInsert(); buffer->setValue("id", 113); buffer->setValue("artistid", 224); buffer->setValue("title", "Shanghai My Heart"); buffer->setValue("year", 2003); cursor.insert();

To update a record, we must first position the QSqlCursor on the record we want to modify (for example, using select() and next()). Then we call primeUpdate() to get a pointer to a QSqlRecord that contains a copy of the record's data. We can then use setValue() to set the fields we want to change, and call update() to write these changes back to the database. For example:

QSqlCursor cursor("cd"); cursor.select("id = 125"); if (cursor.next()) { QSqlRecord *buffer = cursor.primeUpdate(); buffer->setValue("title", "Melody A.M."); buffer->setValue("year", buffer->value("year").toInt() + 1); cursor.update(); }

Deleting a record is similar to updating, but easier:

QSqlCursor cursor("cd"); cursor.select("id = 128"); if (cursor.next()) { cursor.primeDelete(); cursor.del(); }

The QSqlQuery and QSqlCursor classes provide an interface between Qt and a SQL database. In the next two sections, we will see how to use them from within a GUI application to allow the user to view and interact with the data stored in a database.