|  | Home · Overviews · Examples |

# QSqlQuery Class Reference
[com.trolltech.qt.sql module]

The `QSqlQuery` class provides a means of executing and manipulating SQL statements. More...

## Detailed Description

The `QSqlQuery` class provides a means of executing and manipulating SQL statements.

`QSqlQuery` encapsulates the functionality involved in creating, navigating and retrieving data from SQL queries which are executed on a `QSqlDatabase`. It can be used to execute DML (data manipulation language) statements, such as `SELECT`, `INSERT`, `UPDATE` and `DELETE`, as well as DDL (data definition language) statements, such as `CREATE` `TABLE`. It can also be used to execute database-specific commands which are not standard SQL (e.g. `SET DATESTYLE=ISO` for PostgreSQL).

Successfully executed SQL statements set the query's state to active; `isActive` then returns true. Otherwise the query's state is set to inactive. In either case, when executing a new SQL statement, the query is positioned on an invalid record; an active query must be navigated to a valid record (so that `isValid` returns true) before values can be retrieved.

Navigating records is performed with the following functions:

- `next`
- `previous`
- `first`
- `last`
- `seek`

These functions allow the programmer to move forward, backward or arbitrarily through the records returned by the query. If you only need to move forward through the results (e.g., by using `next`), you can use `setForwardOnly`, which will save a significant amount of memory overhead and improve performance on some databases. Once an active query is positioned on a valid record, data can be retrieved using `value`. All data is transferred from the SQL backend using QVariants.

For example:

            QSqlQuery query("SELECT country FROM artist");
            while (query.next()) {
                QString country = query.value(0).toString();
                doSomething(country);
            }

To access the data returned by a query, use value(int). Each field in the data returned by a `SELECT` statement is accessed by passing the field's position in the statement, starting from 0. This makes using `SELECT *` queries inadvisable because the order of the fields returned is indeterminate.

For the sake of efficiency, there are no functions to access a field by name (unless you use prepared queries with names, as explained below). To convert a field name into an index, use `record`.indexOf(), for example:

            QSqlQuery query("SELECT * FROM artist");
            int fieldNo = query.record().indexOf("country");
            while (query.next()) {
                QString country = query.value(fieldNo).toString();
                doSomething(country);
            }

`QSqlQuery` supports prepared query execution and the binding of parameter values to placeholders. Some databases don't support these features, so for those, Qt emulates the required functionality. For example, the Oracle and ODBC drivers have proper prepared query support, and Qt makes use of it; but for databases that don't have this support, Qt implements the feature itself, e.g. by replacing placeholders with actual values when a query is executed. Use `numRowsAffected` to find out how many rows were affected by a non-`SELECT` query, and `size` to find how many were retrieved by a `SELECT`.

Oracle databases identify placeholders by using a colon-name syntax, e.g `:name`. ODBC simply uses `?` characters. Qt supports both syntaxes, with the restriction that you can't mix them in the same query.

You can retrieve the values of all the fields in a single variable (a map) using `boundValues`.

### Approaches to Binding Values

Below we present the same example using each of the four different binding approaches, as well as one example of binding values to a stored procedure.

Named binding using named placeholders:

            QSqlQuery query;
            query.prepare("INSERT INTO person (id, forename, surname) "
                          "VALUES (:id, :forename, :surname)");
            query.bindValue(":id", 1001);
            query.bindValue(":forename", "Bart");
            query.bindValue(":surname", "Simpson");
            query.exec();

Positional binding using named placeholders:

            QSqlQuery query;
            query.prepare("INSERT INTO person (id, forename, surname) "
                          "VALUES (:id, :forename, :surname)");
            query.bindValue(0, 1001);
            query.bindValue(1, "Bart");
            query.bindValue(2, "Simpson");
            query.exec();

Binding values using positional placeholders (version 1):

            QSqlQuery query;
            query.prepare("INSERT INTO person (id, forename, surname) "
                          "VALUES (?, ?, ?)");
            query.bindValue(0, 1001);
            query.bindValue(1, "Bart");
            query.bindValue(2, "Simpson");
            query.exec();

Binding values using positional placeholders (version 2):

            QSqlQuery query;
            query.prepare("INSERT INTO person (id, forename, surname) "
                          "VALUES (?, ?, ?)");
            query.addBindValue(1001);
            query.addBindValue("Bart");
            query.addBindValue("Simpson");
            query.exec();

Binding values to a stored procedure:

This code calls a stored procedure called `AsciiToInt()`, passing it a character through its in parameter, and taking its result in the out parameter.

            QSqlQuery query;
            query.prepare("CALL AsciiToInt(?, ?)");
            query.bindValue(0, "A");
            query.bindValue(1, 0, QSql::Out);
            query.exec();
            int i = query.boundValue(1).toInt(); // i is 65

Note that unbound parameters will retain their values.

Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported. For specific details see SQL Database Drivers.

Warning: You must load the SQL driver and open the connection before a `QSqlQuery` is created. Also, the connection must remain open while the query exists; otherwise, the behavior of `QSqlQuery` is undefined.

See also `QSqlDatabase`, `QSqlQueryModel`, `QSqlTableModel`, and `QVariant`.