|
|
| Home | All Classes | Main Classes | Annotated | Grouped Classes | Functions | |
Qt's SQL classes help you provide seamless database integration to your Qt applications.
This overview assumes that you have at least a basic knowledge of SQL. You should be able to understand simple SELECT, INSERT, UPDATE and DELETE commands. Although the QSqlCursor class provides an interface to database browsing and editing that does not require a knowledge of SQL, a basic understanding of SQL is highly recommended. A standard text covering SQL databases is An Introduction to Database Systems (7th ed.) by C. J. Date, ISBN 0201385902.
Whilst this module overview presents the classes from a purely programmatic point of view the Qt Designer manual's "Creating Database Applications" chapter takes a higher-level approach demonstrating how to set up master-detail relationships between widgets, perform drilldown and handle foreign key lookups.
This document is divided into six sections:
SQL Module Architecture. This describes how the classes fit together.
Connecting to Databases. This section explains how to set up database connections using the QSqlDatabase class.
Executing SQL Commands. This section demonstrates how to issue the standard data manipulation commands, SELECT, INSERT, UPDATE and DELETE on tables in the database (although any valid SQL statement can be sent to the database). The focus is purely on database interaction using QSqlQuery.
Using Cursors. This section explains how to use the QSqlCursor class which provides a simpler API than the raw SQL used with QSqlQuery.
Data-Aware Widgets. This section shows how to programmatically link your database to the user interface. In this section we introduce the QDataTable, QSqlForm, QSqlPropertyMap and QSqlEditorFactory classes and demonstrate how to use custom data-aware widgets. Qt Designer provides an easy visual way of achieving the same thing. See the Qt Designer manual, QDataBrowser and QDataView for more information.
Subclassing QSqlCursor. This section gives examples of subclassing QSqlCursor. Subclassing can be used to provide default and calculated values for fields (such as auto-numbered primary index fields), and to display calculated data, e.g. showing names rather than ids of foreign keys.
All the examples in this document use the tables defined in the Example Tables section.
The SQL classes are divided into three layers:
User Interface Layer. These classes provide data-aware widgets that can be connected to tables or views in the database (by using a QSqlCursor as a data source). End users can interact directly with these widgets to browse or edit data. Qt Designer is fully integrated with the SQL classes and can be used to create data-aware forms. The data-aware widgets can also be programmed directly with your own C++ code. The classes that support this layer include QSqlEditorFactory, QSqlForm, QSqlPropertyMap, QDataTable, QDataBrowser and QDataView.
SQL API Layer. These classes provide access to databases. Connections are made using the QSqlDatabase class. Database interaction is achieved either by using the QSqlQuery class and executing SQL commands directly or by using the higher level QSqlCursor class which composes SQL commands automatically. In addition to QSqlDatabase, QSqlCursor and QSqlQuery, the SQL API layer is supported by QSqlError, QSqlField, QSqlFieldInfo, QSqlIndex, QSqlRecord and QSqlRecordInfo.
Driver Layer. This comprises three classes, QSqlResult, QSqlDriver and QSqlDriverFactoryInterface. This layer provides the low level bridge between the database and the SQL classes. This layer is documented separately since it is only relevant to driver writers, and is rarely used in standard database application programming. See here for more information on implementing a Qt SQL driver plugin.
The Qt SQL module can dynamically load new drivers at runtime using the Plugins.
The SQL driver documentation describes how to build plugins for specific database management systems.
Once a plugin is built, Qt will automatically load it, and the driver will be available for use by QSqlDatabase (see QSqlDatabase::drivers() for more information).
At least one database connection must be created and opened before the QSqlQuery or QSqlCursor classes can be used.
If the application only needs a single database connection, the QSqlDatabase class can create a connection which is used by default for all SQL operations. If multiple database connections are required these can easily be set up.
QSqlDatabase requires the qsqldatabase.h header file.
Making a database connection is a simple three step process: activate the driver, set up the connection information, and open the connection.
#include <qapplication.h>
#include <qsqldatabase.h>
#include "../connection.h"
int main( int argc, char *argv[] )
{
QApplication app( argc, argv, FALSE );
QSqlDatabase *defaultDB = QSqlDatabase::addDatabase( DB_SALES_DRIVER );
defaultDB->setDatabaseName( DB_SALES_DBNAME );
defaultDB->setUserName( DB_SALES_USER );
defaultDB->setPassword( DB_SALES_PASSWD );
defaultDB->setHostName( DB_SALES_HOST );
if ( defaultDB->open() ) {
// Database successfully opened; we can now issue SQL commands.
}
return 0;
}
First we activate the driver by calling QSqlDatabase::addDatabase(), passing the name of the driver we wish to use for this connection. At the time of writing the available drivers are: QODBC3 (Open Database Connectivity, includes Microsoft SQL Server support), QOCI8 (Oracle 8 and 9), QTDS7 (Sybase Adaptive Server), QPSQL7 (PostgreSQL 6 and 7), QMYSQL3 (MySQL), QDB2 (IBM DB2), QSQLITE (SQLite) and QIBASE (Interbase). Note that some of these drivers aren't included in the Qt Open Source Edition; see the README files for details.
The connection which is created becomes the application's default database connection and will be used by the Qt SQL classes if no other database is specified.
Second we call setDatabaseName(), setUserName(), setPassword() and setHostName() to initialize the connection information. Note that for the QOCI8 (Oracle 8 and 9) driver the TNS Service Name must be passed to setDatbaseName(). When connecting to ODBC data sources the Data Source Name (DSN) should be used in the setDatabaseName() call.
Third we call open() to open the database and give us access to the data. If this call fails it will return FALSE; error information can be obtained from QSqlDatabase::lastError().
Connecting to multiple databases is achieved using the two argument form of QSqlDatabase::addDatabase() where the second argument is a unique identifier distinguishing the connection.
In the example below we have moved the connections into their own function, createConnections(), and added some basic error handling.
#define DB_SALES_DRIVER "QPSQL7" #define DB_SALES_DBNAME "sales" #define DB_SALES_USER "salesperson" #define DB_SALES_PASSWD "salesperson" #define DB_SALES_HOST "database.domain.no" #define DB_ORDERS_DRIVER "QOCI8" #define DB_ORDERS_DBNAME "orders" #define DB_ORDERS_USER "orderperson" #define DB_ORDERS_PASSWD "orderperson" #define DB_ORDERS_HOST "database.domain.no" bool createConnections();
We set up some constants and also declare the createConnections() function in connection.h.
#include <qsqldatabase.h>
#include "connection.h"
bool createConnections()
{
QSqlDatabase *defaultDB = QSqlDatabase::addDatabase( DB_SALES_DRIVER );
defaultDB->setDatabaseName( DB_SALES_DBNAME );
defaultDB->setUserName( DB_SALES_USER );
defaultDB->setPassword( DB_SALES_PASSWD );
defaultDB->setHostName( DB_SALES_HOST );
if ( ! defaultDB->open() ) {
qWarning( "Failed to open sales database: " + defaultDB->lastError().text() );
return FALSE;
}
QSqlDatabase *oracle = QSqlDatabase::addDatabase( DB_ORDERS_DRIVER, "ORACLE" );
oracle->setDatabaseName( DB_ORDERS_DBNAME );
oracle->setUserName( DB_ORDERS_USER );
oracle->setPassword( DB_ORDERS_PASSWD );
oracle->setHostName( DB_ORDERS_HOST );
if ( ! oracle->open() ) {
qWarning( "Failed to open orders database: " + oracle->lastError().text() );
return FALSE;
}
QSqlQuery q(QString::null, defaultDB);
q.exec("create table people (id integer primary key, name char(40))");
q.exec("create table staff (id integer primary key, forename char(40), "
"surname char(40), salary float, statusid integer)");
q.exec("create table status (id integer primary key, name char(30))");
q.exec("create table creditors (id integer primary key, forename char(40), "
"surname char(40), city char(30))");
q.exec("create table prices (id integer primary key, name char(40), price float)");
q.exec("create table invoiceitem (id integer primary key, "
"pricesid integer, quantity integer, paiddate date)");
QSqlQuery q2(QString::null, oracle);
q2.exec("create table people (id integer primary key, name char(40))");
return TRUE;
}
We've chosen to isolate database connection in our createConnections() function.cpp.
#include <qapplication.h>
#include <qsqldatabase.h>
#include "../connection.h"
int main( int argc, char *argv[] )
{
QApplication app( argc, argv, FALSE );
if ( createConnections() ) {
// Databases successfully opened; get pointers to them:
QSqlDatabase *oracledb = QSqlDatabase::database( "ORACLE" );
// Now we can now issue SQL commands to the oracle connection
// or to the default connection
}
return 0;
}
The static function QSqlDatabase::database() can be called from anywhere to provide a pointer to a database connection. If we call it without a parameter it will return the default connection. If called with the identifier we've used for a connection, e.g. "ORACLE", in the above example, it will return a pointer to the specified connection.
If you create a main.cpp using Qt Designer, it will not include our example createConnections() function. This means that applications that preview correctly in Qt Designer will not run unless you implement your own database connections function.
Note that in the code above the ODBC connection was not named and is therefore used as the default connection. QSqlDatabase maintains ownership of the pointers returned by the addDatabase() static function. To remove a database from the list of maintained connections, first close the database with QSqlDatabase::close(), and then remove it using the static function QSqlDatabase::removeDatabase().
The QSqlQuery class provides an interface for executing SQL commands. It also has functions for navigating through the result sets of SELECT queries and for retrieving individual records and field values.
The QSqlCursor class described in the next section inherits from QSqlQuery and provides a higher level interface that composes SQL commands for us. QSqlCursor is particularly easy to integrate with on-screen widgets. Programmers unfamiliar with SQL can safely skip this section and use the QSqlCursor class covered in "Using QSqlCursor".
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().
#include <qapplication.h>
#include <qsqldatabase.h>
#include <qsqlquery.h>
#include "../connection.h"
int main( int argc, char *argv[] )
{
QApplication app( argc, argv, FALSE );
if ( createConnections() ) {
QSqlDatabase *oracledb = QSqlDatabase::database( "ORACLE" );
// Copy data from the oracle database to the ODBC (default)
// database
QSqlQuery target;
QSqlQuery query( "SELECT id, name FROM people", oracledb );
if ( query.isActive() ) {
while ( query.next() ) {
target.exec( "INSERT INTO people ( id, name ) VALUES ( " +
query.value(0).toString() +
", '" + query.value(1).toString() + "' )" );
}
}
}
return 0;
}
In the example above we've added an additional header file, qsqlquery.h. The first query we create, target, uses the default database and is initially empty. For the second query, q, we specify the "ORACLE" database that we want to retrieve records from. Both the database connections were set up in the createConnections() function we wrote earlier.
After creating the initial SELECT statement, isActive() is checked to see if the query executed successfully. The next() function is used to iterate through the query results. The value() function returns the contents of fields as QVariants. The insertions are achieved by creating and executing queries against the default database using the target QSqlQuery.
Note that this example and all the other examples in this document use the tables defined in the Example Tables section.
int count = 0;
if ( query.isActive() ) {
while ( query.next() ) {
target.exec( "INSERT INTO people ( id, name ) VALUES ( " +
query.value(0).toString() +
", '" + query.value(1).toString() + "' )" );
if ( target.isActive() )
count += target.numRowsAffected();
}
}
The above code introduces a count of how many records are successfully inserted. Note that isActive() returns FALSE if the query, e.g. the insertion, fails. numRowsAffected() returns -1 if the number of rows cannot be determined, e.g. if the query fails.
** $Id: qt/main.cpp 3.3.8 edited Jan 11 14:37 $
**
** Copyright (C) 1992-2007 Trolltech ASA. All rights reserved.
**
** This file is part of an example program for Qt. This example
** program may be used, distributed and modified without limitation.
**
*****************************************************************************/
#include <qapplication.h>
#include <qsqldatabase.h>
#include <qsqlquery.h>
#include "../connection.h"
bool createConnections();
int main( int argc, char *argv[] )
{
QApplication app( argc, argv, FALSE );
int rows = 0;
if ( createConnections() ) {
QSqlQuery query( "INSERT INTO staff ( id, forename, surname, salary ) "
"VALUES ( 1155, 'Ginger', 'Davis', 50000 )" );
if ( query.isActive() ) rows += query.numRowsAffected() ;
query.exec( "UPDATE staff SET salary=60000 WHERE id=1155" );
if ( query.isActive() ) rows += query.numRowsAffected() ;
query.exec( "DELETE FROM staff WHERE id=1155" );
if ( query.isActive() ) rows += query.numRowsAffected() ;
}
return ( rows == 3 ) ? 0 : 1;
}
This example demonstrates straightforward SQL DML (data manipulation language) commands. Since we did not specify a database in the QSqlQuery constructor the default database is used. QSqlQuery objects can also be used to execute SQL DDL (data definition language) commands such as CREATE TABLE and CREATE INDEX.
Once a SELECT query has been executed successfully we have access to the result set of records that matched the query criteria. We have already used one of the navigation functions, next(), which can be used alone to step sequentially through the records. QSqlQuery also provides first(), last() and prev(). After any of these commands we can check that we are on a valid record by calling isValid().
We can also navigate to any arbitrary record using seek(). The first record in the dataset is zero. The number of the last record is size() - 1. Note that not all databases provide the size of a SELECT query and in such cases size() returns -1.
if ( createConnections() ) {
QSqlQuery query( "SELECT id, name FROM people ORDER BY name" );
if ( ! query.isActive() ) return 1; // Query failed
int i;
i = query.size(); // In this example we have 9 records; i == 9.
query.first(); // Moves to the first record.
i = query.at(); // i == 0
query.last(); // Moves to the last record.
i = query.at(); // i == 8
query.seek( query.size() / 2 ); // Moves to the middle record.
i = query.at(); // i == 4
}
The example above shows some of the navigation functions in use.
Not all drivers support size(), but we can interrogate the driver to find out:
QSqlDatabase* defaultDB = QSqlDatabase::database();
if ( defaultDB->driver()->hasFeature( QSqlDriver::QuerySize ) ) {
// QSqlQuery::size() supported
}
else {
// QSqlQuery::size() cannot be relied upon
}
Once we have located the record we are interested in we may wish to retrieve data from it.
if ( createConnections() ) {
QSqlQuery query( "SELECT id, surname FROM staff" );
if ( query.isActive() ) {
while ( query.next() ) {
qDebug( query.value(0).toString() + ": " +
query.value(1).toString() );
}
}
}
Note that if you wish to iterate through the record set in order the only navigation function you need is next().
Tip: The lastQuery() function returns the text of the last query executed. This can be useful to check that the query you think is being executed is the one actually being executed.
The QSqlCursor class provides a high level interface to browsing and editing records in SQL database tables or views without the need to write your own SQL.
QSqlCursor can do almost everything that QSqlQuery can, with two exceptions. Since cursors represent tables or views within the database, by default, QSqlCursor objects retrieve all the fields of each record in the table or view whenever navigating to a new record. If only some fields are relevant simply confine your processing to those and ignore the others. Or, manually disable the generation of certain fields using QSqlRecord::setGenerated(). Another approach is to create a VIEW which only presents the fields you're interested in; but note that some databases do not support editable views. So if you really don't want to retrieve all the fields in the cursor, then you should use a QSqlQuery instead, and customize the query to suit your needs. You can edit records using a QSqlCursor providing that the table or view has a primary index that uniquely distinguishes each record. If this condition is not met then you'll need to use a QSqlQuery for edits.
QSqlCursor operates on a single record at a time. Whenever performing an insert, update or delete using QSqlCursor, only a single record in the database is affected. When navigating through records in the cursor, only one record at a time is available in application code. In addition, QSqlCursor maintains a separate 'edit buffer' which is used to make changes to a single record in the database. The edit buffer is maintained in a separate memory area, and is unnaffected by the 'navigation buffer' which changes as the cursor moves from record to record.
Before we can use QSqlCursor objects we must first create and open a database connection. Connecting is described in the Connecting to Databases section above. For the examples that follow we will assume that the connections have been created using the createConnections() function defined in the QSqlDatabase example presented earlier.
In the data-aware widgets section that follows this one we show how to link widgets to database cursors. Once we have a knowledge of both cursors and data-aware widgets we can discuss subclassing QSqlCursor.
The QSqlCursor class requires the qsqlcursor.h header file.
#include <qapplication.h>
#include <qsqldatabase.h>
#include <qsqlcursor.h>
#include "../connection.h"
int main( int argc, char *argv[] )
{
QApplication app( argc, argv );
if ( createConnections() ) {
QSqlCursor cur( "staff" ); // Specify the table/view name
cur.select(); // We'll retrieve every record
while ( cur.next() ) {
qDebug( cur.value( "id" ).toString() + ": " +
cur.value( "surname" ).toString() + " " +
cur.value( "salary" ).toString() );
}
}
return 0;
}
We create the QSqlCursor object, specifying the table or view to use. If we need to use a database other than the default we can specify it in the QSqlCursor constructor.
The SQL executed by the cur.select() call is
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid FROM staff
Next, we iterate through the records returned by this select statement using cur.next(). Field values are retrieved in in a similar way to QSqlQuery, except that we pass field names rather than numeric indexes to value() and setValue().
To specify a subset of records to retrieve we can pass filtering criteria to the select() function. Each record that is returned will meet the criteria of the filter (the filter corresponds to the SQL statement's WHERE clause).
cur.select( "id > 100" );
This select() call will execute the SQL
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff WHERE staff.id > 100
This will retrieve only those staff whose id is greater than 100.
In addition to retrieving selected records we often want to specify a sort order for the returned records. This is achieved by creating a QSqlIndex object which contains the names of the field(s) we wish to sort by and pass this object to the select() call.
QSqlCursor cur( "staff" );
QSqlIndex nameIndex = cur.index( "surname" );
cur.select( nameIndex );
Here we create a QSqlIndex object with one field, "surname". When we call the select() function we pass the index object, which specifies that the records should be returned sorted by staff.surname. Each field in the index object is used in the ORDER BY clause of the select statement. The SQL executed here is
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff ORDER BY staff.surname ASC
Combining the retrieval of a subset of records and ordering the results is straightforward.
cur.select( "staff.surname LIKE 'A%'", nameIndex );
We pass in a filter string (the WHERE clause), and the QSqlIndex object to sort by (the ORDER BY clause). This produces
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff WHERE staff.surname LIKE 'A%' ORDER BY staff.surname ASC
To sort by more than one field, an index can be created which contains multiple fields. Ascending and descending order can be set using QSqlIndex::setDescending(); the default is ascending.
QSqlCursor cur( "staff" );
QStringList fields = QStringList() << "surname" << "forename";
QSqlIndex order = cur.index( fields );
cur.select( order );
while ( cur.next() ) {
Here we create a string list containing the fields we wish to sort by, in the order they are to be used. Then we create a QSqlIndex object based on these fields, finally executing the select() call using this index. This executes
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff ORDER BY staff.surname ASC, staff.forename ASC
If we need to retrieve records with fields that match specific criteria we can create a filter based on an index.
QSqlCursor cur( "staff" );
QStringList fields = QStringList() << "id" << "forename";
QSqlIndex order = cur.index( fields );
QSqlIndex filter = cur.index( "surname" );
cur.setValue( "surname", "Bloggs" );
cur.select( filter, order );
while ( cur.next() ) {
This executes
SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
FROM staff WHERE staff.surname='Bloggs' ORDER BY staff.id ASC, staff.forename ASC
The "order" QSqlIndex contains two fields, "id" and "forename" which are used to order the results. The "filter" QSqlIndex contains a single field, "surname". When an index is passed as a filter to the select() function, for each field in the filter, a fieldname=value subclause is created where the value is taken from the current cursor's value for that field. We use setValue() to ensure that the value used is the one we want.
QSqlCursor cur( "creditors" );
QStringList orderFields = QStringList() << "surname" << "forename";
QSqlIndex order = cur.index( orderFields );
QStringList filterFields = QStringList() << "surname" << "city";
QSqlIndex filter = cur.index( filterFields );
cur.setValue( "surname", "Chirac" );
cur.setValue( "city", "Paris" );
cur.select( filter, order );
while ( cur.next() ) {
int id = cur.value( "id" ).toInt();
QString name = cur.value( "forename" ).toString() + " " +
cur.value( "surname" ).toString();
qDebug( QString::number( id ) + ": " + name );
}
In this example we begin by creating a cursor on the creditors table. We create two QSqlIndex objects. The first, "order", is created from the "orderFields" string list. The second, "filter", is created from the "filterFields" string list. We set the values of the two fields used in the filter, "surname" and "city", to the values we're interested in. Now we call select() which generates and executes the following SQL:
SELECT creditors.city, creditors.surname, creditors.forename, creditors.id
FROM creditors
WHERE creditors.surname = 'Chirac' AND creditors.city = 'Paris'
ORDER BY creditors.surname ASC, creditors.forename ASC
The filter fields are used in the WHERE clause. Their values are
taken from the cursor's current values for those fields; we set these
values ourselves with the setValue() calls. The order fields are used
in the ORDER BY clause.
Now we iterate through each matching record (if any). We retrieve the contents of the id, forename and surname fields and pass them on to some processing function, in this example a simple qDebug() call.
Records can be inserted, updated or deleted in a table or view using a QSqlCursor providing that the table or view has a primary index that uniquely distinguishes each record. If this is not the case a QSqlQuery must be used instead. (Note that not all databases support editable views.)
Each cursor has an internal 'edit buffer' which is used by all the edit operations (insert, update and delete). The editing process is the same for each operation: acquire a pointer to the relevant buffer; call setValue() to prime the buffer with the values you want; call insert() or update() or del() to perform the desired operation. For example, when inserting a record using a cursor, you call primeInsert() to get a pointer to the edit buffer and then call setValue() on this buffer to set each field's value. Then you call QSQlCursor::insert() to insert the contents of the edit buffer into the database. Similarly, when updating (or deleting) a record, the values of the fields in the edit buffer are used to update (or delete) the record in the database. The 'edit buffer' is unaffected by any cursor navigation functions. Note that if you pass a string value to setValue() any single quotes will be escaped (turned into a pair of single quotes) since a single quote is a special character in SQL.
The primeInsert(), primeUpdate() and primeDelete() methods all return a pointer to the internal edit buffer. Each method can potentially perform different operations on the edit buffer before returning it. By default, QSqlCursor::primeInsert() clears all the field values in the edit buffer (see QSqlRecord::clearValues()). Both QSqlCursor::primeUpdate() and QSqlCursor::primeDelete() initialize the edit buffer with the current contents of the cursor before returning it. All three of these functions are virtual, so you can redefine the behavior (for example, reimplementing primeInsert() to auto-number fields in the edit buffer). Data-aware user-interface controls emit signals, e.g. primeInsert(), that you can connect to; these pass a pointer to the appropriate buffer so subclassing may not be necessary. See subclassing QSqlCursor for more information on subclassing; see the Qt Designer manual for more on connecting to the primeInsert() signal.
When insert(), update() or del() is called on a cursor, it will be invalidated and will no longer be positioned on a valid record. If you need to move to another record after performing an insert(), update() or del() you must make a fresh select() call. This ensures that changes to the database are accurately reflected in the cursor.
QSqlCursor cur( "prices" );
QStringList names = QStringList() <<
"Screwdriver" << "Hammer" << "Wrench" << "Saw";
int id = 20;
for ( QStringList::Iterator name = names.begin();
name != names.end(); ++name ) {
QSqlRecord *buffer = cur.primeInsert();
buffer->setValue( "id", id );
buffer->setValue( "name", *name );
buffer->setValue( "price", 100.0 + (double)id );
count += cur.insert();
id++;
}
In this example we create a cursor on the "prices" table. Next we create a list of product names which we iterate over. For each iteration we call the cursor's primeInsert() method. This method returns a pointer to a QSqlRecord buffer in which all the fields are set to NULL. (Note that QSqlCursor::primeInsert() is virtual, and can be customized by derived classes. See QSqlCursor). Next we call setValue() for each field that requires a value. Finally we call insert() to insert the record. The insert() call returns the number of rows inserted.
We obtained a pointer to a QSqlRecord object from the primeInsert() call. QSqlRecord objects can hold the data for a single record plus some meta-data about the record. In practice most interaction with a QSqlRecord consists of simple value() and setValue() calls as shown in this and the following example.
QSqlCursor cur( "prices" );
cur.select( "id=202" );
if ( cur.next() ) {
QSqlRecord *buffer = cur.primeUpdate();
double price = buffer->value( "price" ).toDouble();
double newprice = price * 1.05;
buffer->setValue( "price", newprice );
cur.update();
}
This example begins with the creation of a cursor over the prices table. We select the record we wish to update with the select() call and move to it with the next() call. We call primeUpdate() to get a QSqlRecord pointer to a buffer which is populated with the contents of the current record. We retrieve the value of the price field, calculate a new price, and set the the price field to the newly calculated value. Finally we call update() to update the record. The update() call returns the number of rows updated.
If many identical updates need to be performed, for example increasing the price of every item in the price list, using a single SQL statement with QSqlQuery is more efficient, e.g.
QSqlQuery query( "UPDATE prices SET price = price * 1.05" );
QSqlCursor cur( "prices" );
cur.select( "id=999" );
if ( cur.next() ) {
cur.primeDelete();
cur.del();
To delete records, select the record to be deleted and navigate to it. Then call primeDelete() to populate the cursor with the primary key of the selected record, (in this example, the prices.id field), and then call QSqlCursor::del() to delete it.
As with update(), if multiple deletions need to be made with some common criteria it is more efficient to do so using a single SQL statement, e.g.
QSqlQuery query( "DELETE FROM prices WHERE id >= 2450 AND id <= 2500" );
Data-Aware Widgets provide a simple yet powerful means of connecting databases to Qt user interfaces. The easiest way of creating and manipulating data-aware widgets is with Qt Designer. For those who prefer a purely programmatic approach the following examples and explanations provide an introduction. Note that the "Creating Database Applications" chapter of the Qt Designer manual and its accompanying examples provides additional information.
#include <qapplication.h>
#include <qsqldatabase.h>
#include <qsqlcursor.h>
#include <qdatatable.h>
#include "../connection.h"
int main( int argc, char *argv[] )
{
QApplication app( argc, argv );
if ( createConnections() ) {
QSqlCursor staffCursor( "staff" );
QDataTable *staffTable = new QDataTable( &staffCursor, TRUE );
app.setMainWidget( staffTable );
staffTable->refresh();
staffTable->show();
return app.exec();
}
return 0;
}
Data-Aware tables require the qdatatable.h and qsqlcursor.h header files. We create our application object, call createConnections() and create the cursor. We create the QDataTable passing it a pointer to the cursor, and set the autoPopulate flag to TRUE. Next we make our QDataTable the main widget and call refresh() to populate it with data and call show() to make it visible.
The autoPopulate flag tells the QDataTable whether or nor it should create columns based on the cursor. autoPopulate does not affect the loading of data into the table; that is achieved by the refresh() function.
QSqlCursor staffCursor( "staff" );
QDataTable *staffTable = new QDataTable( &staffCursor );
app.setMainWidget( staffTable );
staffTable->addColumn( "forename", "Forename" );
staffTable->addColumn( "surname", "Surname" );
staffTable->addColumn( "salary", "Annual Salary" );
QStringList order = QStringList() << "surname" << "forename";
staffTable->setSort( order );
staffTable->refresh();
staffTable->show();
We create an empty QDataTable which we make into our main widget and then we manually add the columns we want in the order we wish them to appear. For each column we specify the field name and optionally a display label.
We have also opted to sort the rows in the table; this could also have been achieved by applying the sort to the cursor itself.
Once everything is set up we call refresh() to load the data from the database and show() to make the widget visible.
QDataTables only retrieve visible rows which (depending on the driver) allows even large tables to be displayed very quickly with minimal memory cost.
Creating data-aware forms is more involved than using data-aware tables because we must take care of each field individually. Most of the code below can be automatically generated by Qt Designer. See the Qt Designer manual for more details.
#include <qapplication.h>
#include <qdialog.h>
#include <qlabel.h>
#include <qlayout.h>
#include <qlineedit.h>
#include <qsqldatabase.h>
#include <qsqlcursor.h>
#include <qsqlform.h>
#include "../connection.h"
class FormDialog : public QDialog
{
public:
FormDialog();
};
FormDialog::FormDialog()
{
QLabel *forenameLabel = new QLabel( "Forename:", this );
QLabel *forenameDisplay = new QLabel( this );
QLabel *surnameLabel = new QLabel( "Surname:", this );
QLabel *surnameDisplay = new QLabel( this );
QLabel *salaryLabel = new QLabel( "Salary:", this );
QLineEdit *salaryEdit = new QLineEdit( this );
QGridLayout *grid = new QGridLayout( this );
grid->addWidget( forenameLabel, 0, 0 );
grid->addWidget( forenameDisplay, 0, 1 );
grid->addWidget( surnameLabel, 1, 0 );
grid->addWidget( surnameDisplay, 1, 1 );
grid->addWidget( salaryLabel, 2, 0 );
grid->addWidget( salaryEdit, 2, 1 );
grid->activate();
QSqlCursor staffCursor( "staff" );
staffCursor.select();
staffCursor.next();
QSqlForm sqlForm( this );
sqlForm.setRecord( staffCursor.primeUpdate() );
sqlForm.insert( forenameDisplay, "forename" );
sqlForm.insert( surnameDisplay, "surname" );
sqlForm.insert( salaryEdit, "salary" );
sqlForm.readFields();
}
int main( int argc, char *argv[] )
{
QApplication app( argc, argv );
if ( ! createConnections() ) return 1;
FormDialog *formDialog = new FormDialog();
formDialog->show();
app.setMainWidget( formDialog );
return app.exec();
}
We include the header files for the widgets that we need. We also include qsqldatabase.h and qsqlcursor.h as usual, but we now add qsqlform.h.
The form will be presented as a dialog so we subclass QDialog with our own FormDialog class. We use a QLineEdit for the salary so that the user can change it. All the widgets are laid out using a grid.
We create a cursor on the staff table, select all records and move to the first record.
Now we create a QSqlForm object and set the QSqlForm's record buffer to the cursor's update buffer. For each widget that we wish to make data-aware we insert a pointer to the widget and the associated field name into the QSqlForm. Finally we call readFields() to populate the widgets with data from the database via the cursor's buffer.
QDataView is a Widget that can hold a read-only QSqlForm. In addition to QSqlForm it offers the slot refresh( QSqlRecord * ) so it can easily be linked together with a QDataTable to display a detailed view of a record:
connect( myDataTable, SIGNAL( currentChanged( QSqlRecord* ) ),
myDataView, SLOT( refresh( QSqlRecord* ) ) );
This example is similar to the previous one so we will focus on the differences.
class FormDialog : public QDialog
{
Q_OBJECT
public:
FormDialog();
~FormDialog();
public slots:
void save();
private:
QSqlCursor staffCursor;
QSqlForm *sqlForm;
QSqlIndex idIndex;
};
The save slot will be used for a button that the user can press to confirm their update. We also hold pointers to the QSqlCursor and the QSqlForm since they will need to be accessed outside the constructor.
staffCursor.setTrimmed( "forename", TRUE );
staffCursor.setTrimmed( "surname", TRUE );
We call setTrimmed() on the text fields so that any spaces used to right pad the fields are removed when the fields are retrieved.
Properties that we might wish to apply to fields, such as alignment and validation are achieved in the conventional way, for example, by calling QLineEdit::setAlignment() and QLineEdit::setValidator().
QLineEdit *forenameEdit = new QLineEdit( this );
QPushButton *saveButton = new QPushButton( "&Save", this );
connect( saveButton, SIGNAL(clicked()), this, SLOT(save()) );
The FormDialog constructor is similar to the one in the previous example. We have changed the forename and surname widgets to QLineEdits to make them editable and have added a QPushButton the user can click to save their updates.
grid->addWidget( saveButton, 3, 0 );
We add an extra row to the grid containing the save button.
idIndex = staffCursor.index( "id" );
staffCursor.select( idIndex );
staffCursor.first();
We create a QSqlIndex object and then execute a select() using the index. We then move to the first record in the result set.
sqlForm = new QSqlForm( this );
sqlForm->setRecord( staffCursor.primeUpdate() );
We create a new QSqlForm object and set it's record buffer to the cursor's update buffer.
sqlForm->insert( forenameEdit, "forename" );
sqlForm->insert( surnameEdit, "surname" );
sqlForm->insert( salaryEdit, "salary" );
sqlForm->readFields();
Now we link the buffer's fields to the QLineEdit controls. (In the previous example we linked the cursor's fields.) The edit controls are populated by the readFields() call as before.
FormDialog::~FormDialog()
{
}
In the destructor we don't have to worry about the widgets or QSqlForm since they are children of the form and will be deleted by Qt at the right time.
void FormDialog::save()
{
sqlForm->writeFields();
staffCursor.update();
staffCursor.select( idIndex );
staffCursor.first();
}
Finally we add the save functionality for when the user presses the save button. We write back the data from the widgets to the QSqlRecord buffer with the writeFields() call. Then we update the database with the updated version of the record with the cursor's update() function. At this point the cursor is no longer positioned at a valid record so we reissue the select() call using our QSqlIndex and move to the first record.
QDataBrowser and QDataView are widgets which provide a great deal of the above functionality. QDataBrowser provides a data form which allows editing of and navigation through a cursor's records. QDataView provides a read only form for data in a cursor or database record. See the class documentation or the Qt Designer manual for more information on using these widgets.
Link to sql/overview/form2/main.cpp
QSqlForm uses QSqlPropertyMap to handle the transfer of data between widgets and database fields. Custom widgets can also be used in a form by installing a property map that contains information about the properties of the custom widget which should be used to transfer the data.
This example is based on the form2 example in the previous section so we will only cover the differences here. The full source is in sql/overview/custom1/main.h and sql/overview/custom1/main.cpp
class CustomEdit : public QLineEdit
{
Q_OBJECT
Q_PROPERTY( QString upperLine READ upperLine WRITE setUpperLine )
public:
CustomEdit( QWidget *parent=0, const char *name=0 );
QString upperLine() const;
void setUpperLine( const QString &line );
public slots:
void changed( const QString &line );
private:
QString upperLineText;
};
We've created a simple subclass of QLineEdit and added a property, upperLineText, which will hold an uppercase version of the text. We also created a slot, changed().
QSqlPropertyMap *propMap;
We will be using a property map so we add a pointer to a property map to our FormDialog's private data.
CustomEdit::CustomEdit( QWidget *parent, const char *name ) :
QLineEdit( parent, name )
{
connect( this, SIGNAL(textChanged(const QString &)),
this, SLOT(changed(const QString &)) );
}
In the CustomEdit constructor we use the QLineEdit constructor and add a connection between the textChanged signal and our own changed slot.
void CustomEdit::changed( const QString &line )
{
setUpperLine( line );
}
The changed() slot calls our setUpperLine() function.
void CustomEdit::setUpperLine( const QString &line )
{
upperLineText = line.upper();
setText( upperLineText );
}
The setUpperLine() function places an uppercase copy of the text in the upperLineText buffer and then sets the text of the widget to this text.
Our CustomEdit class ensures that the text entered is always uppercase and provides a property that can be used with a property map to link CustomEdit instances directly to database fields.
CustomEdit *forenameEdit = new CustomEdit( this );
CustomEdit *surnameEdit = new CustomEdit( this );
We use the same FormDialog as we did before, but this time replace two of the QLineEdit widgets with our own CustomEdit widgets.
Laying out the grid and setting up the cursor is the same as before.
propMap = new QSqlPropertyMap;
propMap->insert( forenameEdit->className(), "upperLine" );
We create a new property map on the heap and register our CustomEdit class and its upperLine property with the property map.
sqlForm = new QSqlForm( this );
sqlForm->setRecord( staffCursor->primeUpdate() );
sqlForm->installPropertyMap( propMap );
The final change is to install the property map into the QSqlForm once the QSqlForm has been created. This passes responsibility for the property map's memory to QSqlForm which itself is owned by the FormDialog, so Qt will delete them at the right time.
The behaviour of this example is identical to the previous one except that the forename and surname fields will be uppercase since they use our CustomEdit widget.
We must reimpliment QSqlEditorFactory to use custom editor widgets in tables. In the following example we will create a custom editor based on QComboBox and a QSqlEditorFactory subclass to show how a QDataTable can use a custom editor.
class StatusPicker : public QComboBox
{
Q_OBJECT
Q_PROPERTY( int statusid READ statusId WRITE setStatusId )
public:
StatusPicker( QWidget *parent=0, const char *name=0 );
int statusId() const;
void setStatusId( int id );
private:
QMap< int, int > index2id;
};
We create a property, statusid, and define our READ and WRITE methods for it. The statusid's in the status table will probably be different from the combobox's indexes so we create a QMap to map combobox indexes to/from the statusids that we will list in the combobox.
class CustomSqlEditorFactory : public QSqlEditorFactory
{
Q_OBJECT
public:
QWidget *createEditor( QWidget *parent, const QSqlField *field );
};
We also need to subclass QSqlEditorFactory declaring a createEditor() function since that is the only function we need to reimplement.
StatusPicker::StatusPicker( QWidget *parent, const char *name )
: QComboBox( parent, name )
{
QSqlCursor cur( "status" );
cur.select( cur.index( "name" ) );
int i = 0;
while ( cur.next() ) {
insertItem( cur.value( "name" ).toString(), i );
index2id[i] = cur.value( "id" ).toInt();
i++;
}
In the StatusPicker's constructor we create a cursor over the status table indexed by the name field. We then iterate over each record in the status table inserting each name into the combobox. We store the statusid for each name in the index2id QMap using the same QMap index as the combobox index.
int StatusPicker::statusId() const
{
return index2id[ currentItem() ];
}
The statusid property READ function simply involves looking up the combobox's index for the currently selected item in the index2id QMap which maps combobox indexes to statusids.
void StatusPicker::setStatusId( int statusid )
{
QMap<int,int>::Iterator it;
for ( it = index2id.begin(); it != index2id.end(); ++it ) {
if ( it.data() == statusid ) {
setCurrentItem( it.key() );
break;
}
}
}
The statusId() function implements the statusid property's WRITE function. We create an iterator over a QMap and iterate over the index2id QMap. We compare each index2id element's data (statusid) to the id parameter's value. If we have a match we set the combobox's current item to the index2id element's key (the combobox index), and leave the loop.
When the user edits the status field in the QDataTable they will be presented with a combobox of valid status names taken from the status table. However the status displayed is still the raw statusid. To display the status name when the field isn't being edited requires us to subclass QDataTable and reimplement the paintField() function.
class CustomTable : public QDataTable
{
Q_OBJECT
public:
CustomTable(
QSqlCursor *cursor, bool autoPopulate = FALSE,
QWidget * parent = 0, const char * name = 0 ) :
QDataTable( cursor, autoPopulate, parent, name ) {}
void paintField(
QPainter * p, const QSqlField* field, const QRect & cr, bool );
};
We simply call the original QDataTable constructor without changing anything. We also declare the paintField function.
void CustomTable::paintField( QPainter * p, const QSqlField* field,
const QRect & cr, bool b)
{
if ( !field )
return;
if ( field->name() == "statusid" ) {
QSqlQuery query( "SELECT name FROM status WHERE id=" +
field->value().toString() );
QString text;
if ( query.next() ) {
text = query.value( 0 ).toString();
}
p->drawText( 2,2, cr.width()-4, cr.height()-4, fieldAlignment( field ), text );
}
else {
QDataTable::paintField( p, field, cr, b) ;
}
The paintField code is based on QDataTable's source code. We need to make three changes. Firstly add an if clause field->name() == "statusid" and look up the textual value for the id with a straighforward QSqlQuery. Secondly call the superclass to handle other fields. The last change is in our main function where we change staffTable from being a QDataTable to being a CustomTable.
#include <qapplication.h>
#include <qsqldatabase.h>
#include <qsqlcursor.h>
#include <qdatatable.h>
#include "../connection.h"
int main( int argc, char *argv[] )
{
QApplication app( argc, argv );
if ( createConnections() ) {
QSqlCursor invoiceItemCursor( "invoiceitem" );
QDataTable *invoiceItemTable = new QDataTable( &invoiceItemCursor );
app.setMainWidget( invoiceItemTable );
invoiceItemTable->addColumn( "pricesid", "PriceID" );
invoiceItemTable->addColumn( "quantity", "Quantity" );
invoiceItemTable->addColumn( "paiddate", "Paid" );
invoiceItemTable->refresh();
invoiceItemTable->show();
return app.exec();
}
return 1;
}
This example is very similar to the table1 example presented earlier. We create a cursor, add the fields and their display labels to a QDataTable, call refresh() to load the data and call show() to show the widget.
Unfortunately this example is unsatisfactory. It is tedious to set the table name and any custom characteristics for the fields every time we need a cursor over this table. And it would be far better if we displayed the name of the product rather than its pricesid. Since we know the price of the product and the quantity we could also show the product cost and the cost of each invoiceitem. Finally it would be useful (or even essential for primary keys) if we could default some of the values when the user adds a new record.
class InvoiceItemCursor : public QSqlCursor
{
public:
InvoiceItemCursor();
};
We have created a separate header file and subclassed QSqlCursor.
InvoiceItemCursor::InvoiceItemCursor() :
QSqlCursor( "invoiceitem" )
{
// NOOP
}
In our class's constructor we call the QSqlCursor constructor with the name of the table. We don't have any other characteristics to add at this stage.
InvoiceItemCursor invoiceItemCursor;
Whenever we require a cursor over the invoiceitem table we can create an InvoiceItemCursor instead of a generic QSqlCursor.
We still need to show the product name rather than the pricesid.
protected:
QVariant calculateField( const QString & name );
The change in the header file is minimal: we simply add the signature of the calculateField() function since we will be reimplementing it.
InvoiceItemCursor::InvoiceItemCursor() :
QSqlCursor( "invoiceitem" )
{
QSqlFieldInfo productName( "productname", QVariant::String );
append( productName );
setCalculated( productName.name(), TRUE );
}
QVariant InvoiceItemCursor::calculateField( const QString & name )
{
if ( name == "productname" ) {
QSqlQuery query( "SELECT name FROM prices WHERE id=" +
field( "pricesid" )->value().toString() );
if ( query.next() )
return query.value( 0 );
}
return QVariant( QString::null );
}
We have changed the InvoiceItemCursor constructor. We now create a new QSqlField called productname and append this to the InvoiceItemCursor's set of fields. We call setCalculated() on productname to identify it as a calculated field. The first argument to setCalculated() is the field name, the second a bool which if TRUE signifies that calculateField() must be called to get the field's value.
invoiceItemTable->addColumn( "productname", "Product" );
We add our new fields with addColumn() which adds them to the form and sets their display names.
We have to define our own calculateField() function. In our example database the pricesid in the invoiceitem table is a foreign key into the prices table. We find the name of the product by executing a query on the prices table using the pricesid. This returns the product's name.
We are now able to extend the example to include calculated fields which perform real calculations.
The header file, sql/overview/subclass4/main.h, remains unchanged from the previous example, but the constructor and calculateField() function require some simple expansion. We'll look at each in turn.
InvoiceItemCursor::InvoiceItemCursor() :
QSqlCursor( "invoiceitem" )
{
QSqlFieldInfo productName( "productname", QVariant::String );
append( productName );
setCalculated( productName.name(), TRUE );
QSqlFieldInfo productPrice( "price", QVariant::Double );
append( productPrice );
setCalculated( productPrice.name(), TRUE );
QSqlFieldInfo productCost( "cost", QVariant::Double );
append( productCost );
setCalculated( productCost.name(), TRUE );
}
We create two extra fields, price and cost, and append them to the cursor's set of fields. Both are registered as calculated fields with calls to setCalculated().
QVariant InvoiceItemCursor::calculateField( const QString & name )
{
if ( name == "productname" ) {
QSqlQuery query( "SELECT name FROM prices WHERE id=" +
field( "pricesid" )->value().toString() );
if ( query.next() )
return query.value( 0 );
}
else if ( name == "price" ) {
QSqlQuery query( "SELECT price FROM prices WHERE id=" +
field( "pricesid" )->value().toString() );
if ( query.next() )
return query.value( 0 );
}
else if ( name == "cost" ) {
QSqlQuery query( "SELECT price FROM prices WHERE id=" +
field( "pricesid" )->value().toString() );
if ( query.next() )
return QVariant( query.value( 0 ).toDouble() *
value( "quantity").toDouble() );
}
return QVariant( QString::null );
}
The calculateField() function has expanded slightly because now we must calculate the value of three different fields. The productname and price fields are produced by looking up the corresponding values in the prices table keyed by pricesid. The cost field is calculated simply by multiplying the price by the quantity. Note that we cast the cost to a QVariant since that is the type that calculateField() must return.
We've written three separate queries rather than one to make the example more like a real application where it is more likely that each calculated field would be a lookup against a different table or view.
The last feature that we need to add is defaulting values when the user attempts to insert a new record.
QSqlRecord *primeInsert();
We declare our own primeInsert() function since we will need to reimplement this.
The constructor and the calculateField() function remain unchanged.
QSqlRecord *InvoiceItemCursor::primeInsert()
{
QSqlRecord *buffer = editBuffer();
QSqlQuery query( "SELECT NEXTVAL( 'invoiceitem_seq' )" );
if ( query.next() )
buffer->setValue( "id", query.value( 0 ) );
buffer->setValue( "paiddate", QDate::currentDate() );
buffer->setValue( "quantity", 1 );
return buffer;
}
We get a pointer to the internal edit buffer that the cursor uses for inserts and updates. The id field is a unique integer that we generate using the invoiceitem_seq. We default the value of the paiddate field to today's date and default the quantity to 1. Finally we return a pointer to the buffer. The rest of the code is unchanged from the previous version.
The example tables used can be recreated with the following standard SQL. You may need to modify the SQL to match that used by your particular database.
create table people (id integer primary key, name char(40))
create table staff (id integer primary key, forename char(40),
surname char(40), salary float, statusid integer)
create table status (id integer primary key, name char(30))
create table creditors (id integer primary key, forename char(40),
surname char(40), city char(30))
create table prices (id integer primary key, name char(40), price float)
create table invoiceitem (id integer primary key,
pricesid integer, quantity integer,
paiddate date)
A sequence was used in the calculateField() example above. Note that sequences are not supported in all databases.
create sequence invoiceitem_seq
| Copyright © 2007 Trolltech | Trademarks | Qt 3.3.8
|