From bd0f3345a938b35ce6a12f6150373b0955b8dd12 Mon Sep 17 00:00:00 2001 From: Timothy Pearson Date: Sun, 10 Jul 2011 15:24:15 -0500 Subject: Add Qt3 development HEAD version --- doc/html/sql.html | 1673 +++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 1673 insertions(+) create mode 100644 doc/html/sql.html (limited to 'doc/html/sql.html') diff --git a/doc/html/sql.html b/doc/html/sql.html new file mode 100644 index 0000000..7dce635 --- /dev/null +++ b/doc/html/sql.html @@ -0,0 +1,1673 @@ + + + + + +SQL Module + + + + + + + +

SQL Module

+ + +

+ + + + +

QSql +	QSqlCursor +	QSqlDatabase +	QSqlDriver +	QSqlDriverPlugin +
QSqlEditorFactory +	QSqlError +	QSqlField +	QSqlFieldInfo +	QSqlForm +
QSqlIndex +	QSqlPropertyMap +	QSqlQuery +	QSqlRecord +	QSqlRecordInfo +
QSqlResult +	QSqlSelectCursor +	See also: Supported Drivers +

Introduction + +
SQL Module Architecture + +
SQL Driver Plugins + +
Connecting to Databases + +
Connecting to a Single Database + +
- Connecting to Multiple Databases + +
+
Executing SQL Commands Using QSqlQuery + +
+
Using QSqlCursor + +
- Retrieving Records + +
  - Sorting and Filtering Records + +
  - Extracting Data + +
  +
- Manipulating Records + +
  - Inserting Records + +
  - Updating Records + +
  - Deleting Records + +
  +
+
Data-Aware Widgets + +
+
Subclassing QSqlCursor + +
The Example Tables + +

+ + +

Introduction +

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. +

SQL Module Architecture +

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. +

SQL Driver Plugins +

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). +

Connecting to Databases +

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. +

Connecting to a Single Database +

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;
+    }
+

From sql/overview/connect1/main.cpp +
+

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 +

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;
+    }
+

From sql/overview/connection.cpp +
+

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;
+    }
+

From sql/overview/create_connections/main.cpp +
+

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(). +

Executing SQL Commands Using QSqlQuery +

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". +

Transactions +

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(). +

Basic Browsing +

+ +

    #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;
+    }
+

From sql/overview/basicbrowsing/main.cpp +
+

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

From sql/overview/basicbrowsing2/main.cpp +
+

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. +

Basic Data Manipulation +

+ +

    ** $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;
+    }
+

From sql/overview/basicdatamanip/main.cpp +
+

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. +

Navigating Result Sets +

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
+        }
+

From sql/overview/navigating/main.cpp +
+

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

From sql/overview/retrieve1/main.cpp +
+

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. +

Using QSqlCursor +

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. +

Retrieving Records +

+ +

    #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;
+    }
+

From sql/overview/retrieve2/main.cpp +
+

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(). +

Sorting and Filtering Records +

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() ) {
+

From sql/overview/order1/main.cpp +
+

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() ) {
+

From sql/overview/order2/main.cpp +
+

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. +

Extracting Data +

+ +

            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 );
+            }
+

From sql/overview/extract/main.cpp +
+

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. +

Manipulating Records +

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. +

Inserting Records +

+ +

            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++;
+            }
+

From sql/overview/insert/main.cpp +
+

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. +

Updating Records +

+ +

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

From sql/overview/update/main.cpp +
+

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"  );
+

+ +

Deleting Records +

+ +

            QSqlCursor cur( "prices" );
+            cur.select( "id=999" );
+            if ( cur.next() ) {
+                cur.primeDelete();
+                cur.del();
+

From sql/overview/delete/main.cpp +
+

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 +

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. +

Data-Aware Tables +

+ +

    #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;
+    }
+

From sql/overview/table1/main.cpp +
+

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

From sql/overview/table2/main.cpp +
+

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 +

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. +

Displaying a Record +

+ +

    #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();
+    }
+

From sql/overview/form1/main.cpp +
+

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. +

Displaying a Record in a Data Form +

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* ) ) );
+

+ +

Editing a Record +

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;
+    };
+

From sql/overview/form2/main.h +
+

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 +

Custom Editor Widgets +

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. +

Custom Editor Widgets for Tables +

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;
+    };
+

From sql/overview/table3/main.h +
+

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++;
+        }
+

From sql/overview/table3/main.cpp +
+

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 );
+
+    };
+

From sql/overview/table4/main.h +
+

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) ;
+        }
+

From sql/overview/table4/main.cpp +
+

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. +

Subclassing QSqlCursor +

+ +

    #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;
+    }
+

From sql/overview/subclass1/main.cpp +
+

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

From sql/overview/subclass2/main.h +
+

We have created a separate header file and subclassed QSqlCursor. +

+ +

    InvoiceItemCursor::InvoiceItemCursor() :
+        QSqlCursor( "invoiceitem" )
+    {
+        // NOOP
+    }
+

From sql/overview/subclass2/main.cpp +
+

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

From sql/overview/subclass3/main.h +
+

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 );
+    }
+

From sql/overview/subclass3/main.cpp +
+

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 );
+    }
+

From sql/overview/subclass4/main.cpp +
+

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 );
+    }
+

From sql/overview/subclass4/main.cpp +
+

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

From sql/overview/subclass5/main.h +
+

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;
+    }
+

From sql/overview/subclass5/main.cpp +
+

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 +

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
+

+ +

Trademarks +

Qt 3.3.8

+ -- cgit v1.2.1