path: root/doc/sql.doc

/****************************************************************************
**
** Documentation for sql programming
**
** Copyright (C) 1992-2008 Trolltech ASA.  All rights reserved.
**
** This file is part of the Qt GUI Toolkit.
**
** This file may be used under the terms of the GNU General
** Public License versions 2.0 or 3.0 as published by the Free
** Software Foundation and appearing in the files LICENSE.GPL2
** and LICENSE.GPL3 included in the packaging of this file.
** Alternatively you may (at your option) use any later version
** of the GNU General Public License if such license has been
** publicly approved by Trolltech ASA (or its successors, if any)
** and the KDE Free Qt Foundation.
**
** Please review the following information to ensure GNU General
** Public Licensing requirements will be met:
** http://trolltech.com/products/qt/licenses/licensing/opensource/.
** If you are unsure which license is appropriate for your use, please
** review the following information:
** http://trolltech.com/products/qt/licenses/licensing/licensingoverview
** or contact the sales department at [email protected].
**
** This file may be used under the terms of the Q Public License as
** defined by Trolltech ASA and appearing in the file LICENSE.QPL
** included in the packaging of this file.  Licensees holding valid Qt
** Commercial licenses may use this file in accordance with the Qt
** Commercial License Agreement provided with the Software.
**
** This file is provided "AS IS" with NO WARRANTY OF ANY KIND,
** INCLUDING THE WARRANTIES OF DESIGN, MERCHANTABILITY AND FITNESS FOR
** A PARTICULAR PURPOSE. Trolltech reserves all rights not granted
** herein.
**
**********************************************************************/
/*! \file sql/overview/connect1/main.cpp */
/*! \file sql/overview/create_connections/main.cpp  */
/*! \file sql/overview/basicbrowsing/main.cpp */
/*! \file sql/overview/basicbrowsing2/main.cpp */
/*! \file sql/overview/basicdatamanip/main.cpp */
/*! \file sql/overview/navigating/main.cpp */
/*! \file sql/overview/retrieve1/main.cpp */
/*! \file sql/overview/retrieve2/main.cpp */
/*! \file sql/overview/order1/main.cpp */
/*! \file sql/overview/order2/main.cpp */
/*! \file sql/overview/extract/main.cpp */
/*! \file sql/overview/insert/main.cpp */
/*! \file sql/overview/update/main.cpp */
/*! \file sql/overview/delete/main.cpp */
/*! \file sql/overview/table1/main.cpp */
/*! \file sql/overview/table2/main.cpp */
/*! \file sql/overview/table3/main.h */
/*! \file sql/overview/table3/main.cpp */
/*! \file sql/overview/table4/main.h */
/*! \file sql/overview/table4/main.cpp */
/*! \file sql/overview/form1/main.cpp */
/*! \file sql/overview/form2/main.h */
/*! \file sql/overview/custom1/main.h */
/*! \file sql/overview/custom1/main.cpp */
/*! \file sql/overview/subclass1/main.cpp */
/*! \file sql/overview/subclass2/main.h */
/*! \file sql/overview/subclass2/main.cpp */
/*! \file sql/overview/subclass3/main.h */
/*! \file sql/overview/subclass3/main.cpp */
/*! \file sql/overview/subclass4/main.h */
/*! \file sql/overview/subclass4/main.cpp */
/*! \file sql/overview/subclass5/main.h */
/*! \file sql/overview/subclass5/main.cpp */

/*! \page sql.html
 
\title SQL Module

\if defined(commercial)
This module is part of the \link commercialeditions.html Qt Enterprise Edition
\endlink.
\endif

\table
\row
\i \l QSql
\i \l QSqlCursor
\i \l QSqlDatabase
\i \l QSqlDriver
\i \l QSqlDriverPlugin
\row
\i \l QSqlEditorFactory
\i \l QSqlError
\i \l QSqlField
\i \l QSqlFieldInfo
\i \l QSqlForm
\row
\i \l QSqlIndex
\i \l QSqlPropertyMap
\i \l QSqlQuery
\i \l QSqlRecord
\i \l QSqlRecordInfo
\row
\i \l QSqlResult
\i \l QSqlSelectCursor
\i31 See also: \link sql-driver.html Supported Drivers\endlink
\endtable

\tableofcontents

\target Introduction
\section1 Introduction

Qt's SQL classes help you provide seamless database integration to
your Qt applications.

<blockquote>
This overview assumes that you have at least a basic knowledge of SQL.
You should be able to understand simple \c SELECT, \c INSERT, \c UPDATE
and \c DELETE commands. Although the \l QSqlCursor class provides an
interface to database browsing and editing that does not \e require a
knowledge of SQL, a basic understanding of SQL is highly recommended. A
standard text covering SQL databases is \e {An Introduction to Database
Systems (7th ed.)} by C. J. Date, ISBN 0201385902.
</blockquote>

Whilst this module overview presents the classes from a purely
programmatic point of view the \link designer-manual.book Qt
Designer\endlink 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:

\link #Architecture SQL Module Architecture \endlink. This describes
how the classes fit together.

\link #Connecting_to_Databases Connecting to Databases \endlink. 
This section explains how to set up database connections using the \l
QSqlDatabase class.

\link #Executing_SQL_commands Executing SQL Commands \endlink. This
section demonstrates how to issue the standard data manipulation
commands, \c SELECT, \c INSERT, \c UPDATE and \c 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 \l
QSqlQuery.

\link #Using_QSqlCursor Using Cursors \endlink. This section explains
how to use the QSqlCursor class which provides a simpler API than the
raw SQL used with \l QSqlQuery.

\link #Data-Aware_Widgets Data-Aware Widgets \endlink. This section shows
how to programmatically link your database to the user interface. In
this section we introduce the \l QDataTable, \l QSqlForm, \l
QSqlPropertyMap and QSqlEditorFactory classes and demonstrate how to
use custom data-aware widgets. \link designer-manual.book Qt
Designer\endlink provides an easy visual way of achieving the same
thing. See the \link designer-manual.book Qt Designer\endlink manual,
\l QDataBrowser and \l QDataView for more information.

\link #Subclassing_QSqlCursor Subclassing QSqlCursor \endlink. 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
\link #Example_Tables Example Tables\endlink section.

\target Architecture
\section1 SQL Module Architecture

The SQL classes are divided into three layers:

\e {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. \link designer-manual.book Qt
Designer\endlink 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 \l QSqlEditorFactory, \l QSqlForm, \l
QSqlPropertyMap, \l QDataTable, \l QDataBrowser and \l QDataView.

\e {SQL API Layer.} These classes provide access to databases.
Connections are made using the \l QSqlDatabase class. Database
interaction is achieved either by using the QSqlQuery class and
executing SQL commands directly or by using the higher level \l
QSqlCursor class which composes SQL commands automatically. In
addition to \l QSqlDatabase, \l QSqlCursor and \l QSqlQuery, the SQL
API layer is supported by QSqlError, QSqlField, QSqlFieldInfo,
QSqlIndex, QSqlRecord and QSqlRecordInfo.

\e {Driver Layer.} This comprises three classes, \l QSqlResult, \l
QSqlDriver and QSqlDriverFactoryInterface. This layer provides the
low level bridge between the database and the SQL classes. This layer
is \link sql-driver.html documented separately \endlink since it is
only relevant to driver writers, and is rarely used in standard
database application programming. See \link sql-driver.html here
\endlink for more information on implementing a Qt SQL driver plugin.

\target Plugins
\section1 SQL Driver Plugins

The Qt SQL module can dynamically load new drivers at runtime using
the \link plugins-howto.html Plugins \endlink.

The \link sql-driver.html SQL driver documentation\endlink 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).

\target Connecting_to_Databases
\section1 Connecting to Databases

At least one database connection must be created and opened before the
\l QSqlQuery or \l QSqlCursor classes can be used.

If the application only needs a single database connection, the \l
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.

\l QSqlDatabase requires the \c qsqldatabase.h header file.

\target Connecting_to_a_Single_Database
\section1 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.

\quotefile sql/overview/connect1/main.cpp
\skipto include
\printline include
\printuntil return 0
\printline
\caption From \l sql/overview/connect1/main.cpp

First we activate the driver by calling \l 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 \c 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 \l QSqlDatabase::lastError().

\target Connecting_to_Multiple_Databases
\section2 Connecting to Multiple Databases

Connecting to multiple databases is achieved using the two argument form
of \l 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, \c createConnections(), and added some basic error handling. 

\code
#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();
\endcode

We set up some constants and also declare the \c createConnections()
function in \c connection.h.

\quotefile sql/overview/connection.cpp
\skipto #include
\printuntil return TRUE
\printuntil }
\caption From \l sql/overview/connection.cpp

We've chosen to isolate database connection in our \c
createConnections() function.cpp.

\target create_connections
\quotefile sql/overview/create_connections/main.cpp
\skipto include
\printline include
\printuntil return 0 
\printline
\caption From \l sql/overview/create_connections/main.cpp

The static function \l 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 \c main.cpp using \link designer-manual.book Qt
Designer\endlink, it will \e not include our example
createConnections() function. This means that applications that
preview correctly in \link designer-manual.book Qt Designer\endlink
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. \l 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().

\target Executing_SQL_commands
\section1 Executing SQL Commands Using QSqlQuery

The \l QSqlQuery class provides an interface for executing SQL commands.
It also has functions for navigating through the result sets of \c SELECT
queries and for retrieving individual records and field values.

The \l QSqlCursor class described in the next section inherits from \l
QSqlQuery and provides a higher level interface that composes SQL
commands for us. \l QSqlCursor is particularly easy to integrate with
on-screen widgets. Programmers unfamiliar with SQL can safely skip this
section and use the \l QSqlCursor class covered in 
\link #Using_QSqlCursor "Using QSqlCursor" \endlink.

\target Transactions
\section2 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
\l{QSqlDatabase::rollback()}.

\target Basic_Browsing
\section2 Basic Browsing

\quotefile sql/overview/basicbrowsing/main.cpp
\skipto include
\printline include
\printuntil return 0 
\printline
\caption From \l sql/overview/basicbrowsing/main.cpp

In the example above we've added an additional header file, 
\c qsqlquery.h. The first query we create, \c target, uses the default
database and is initially empty. For the second query, \c 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 \c 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 \c target QSqlQuery.

Note that this example and all the other examples in this document use
the tables defined in the \link #Example_Tables Example Tables\endlink
section.

\quotefile sql/overview/basicbrowsing2/main.cpp
\skipto count
\printline 
\printuntil numRows 
\printline
\printline
\caption From \l 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.

\target Basic_Data_Manipulation
\section2 Basic Data Manipulation

\quotefile sql/overview/basicdatamanip/main.cpp
\skipto main
\printline main
\printuntil return ( rows 
\printline
\caption From \l sql/overview/basicdatamanip/main.cpp

This example demonstrates straightforward SQL DML (data manipulation
language) commands. Since we did not specify a database in the \l
QSqlQuery constructor the default database is used. \l QSqlQuery objects
can also be used to execute SQL DDL (data definition language) commands
such as \c{CREATE TABLE} and \c{CREATE INDEX}.

\target Navigating_Result_Sets
\section2 Navigating Result Sets

Once a \c 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. \l 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
\c SELECT query and in such cases size() returns -1.

\quotefile sql/overview/navigating/main.cpp
\skipto if (
\printline if (
\printuntil i == 4 
\printline
\caption From \l 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:

\code
    QSqlDatabase* defaultDB = QSqlDatabase::database();
    if ( defaultDB->driver()->hasFeature( QSqlDriver::QuerySize ) ) {
	// QSqlQuery::size() supported
    }
    else {
	// QSqlQuery::size() cannot be relied upon
    }
\endcode


Once we have located the record we are interested in we may wish to
retrieve data from it. 

\quotefile sql/overview/retrieve1/main.cpp
\skipto if (
\printline if (
\printuntil qDebug 
\printline
\printline
\printline
\printline
\caption From \l 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.

\target Using_QSqlCursor
\section1 Using QSqlCursor

The \l 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, \l 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 \c 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 \l QSqlQuery instead, and customize the
query to suit your needs. You can edit records using a \l 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 \l 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 \l QSqlCursor objects we must first create and open
a database connection. Connecting is described in the \link
#Connecting_to_Databases Connecting to Databases \endlink section
above. For the examples that follow we will assume that the
connections have been created using the createConnections() function
defined in the \link #create_connections QSqlDatabase example \endlink
presented earlier.

In the \link #Data-Aware_Widgets data-aware widgets \endlink 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 \link #Subclassing_QSqlCursor subclassing QSqlCursor \endlink.

The \l QSqlCursor class requires the \c qsqlcursor.h header file.

\target Retrieving_Records
\section2 Retrieving Records

\quotefile sql/overview/retrieve2/main.cpp
\skipto include
\printline include
\printuntil return 0 
\printline
\caption From \l sql/overview/retrieve2/main.cpp

We create the \l 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

\code
    SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid FROM staff
\endcode

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

\target Sorting_Data
\section3 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 \c WHERE clause).

\code
    cur.select( "id > 100" );
\endcode

This select() call will execute the SQL
\code
    SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid 
    FROM staff WHERE staff.id > 100
\endcode

This will retrieve only those staff whose \c 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 \l
QSqlIndex object which contains the names of the field(s) we wish to
sort by and pass this object to the select() call.

\code
    QSqlCursor cur( "staff" );
    QSqlIndex nameIndex = cur.index( "surname" ); 
    cur.select( nameIndex );
\endcode

Here we create a \l 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 
\code
    SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid 
    FROM staff ORDER BY staff.surname ASC
\endcode

Combining the retrieval of a subset of records and ordering the results
is straightforward.

\code
    cur.select( "staff.surname LIKE 'A%'", nameIndex );
\endcode

We pass in a filter string (the \c WHERE clause), and the \l QSqlIndex
object to sort by (the \c{ORDER BY} clause). This produces

\code
    SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
    FROM staff WHERE staff.surname LIKE 'A%' ORDER BY staff.surname ASC
\endcode

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.

\quotefile sql/overview/order1/main.cpp
\skipto QSqlCursor
\printline QSqlCursor
\printuntil while 
\caption From \l 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 \l QSqlIndex object
based on these fields, finally executing the select() call using this
index. This executes
\code
    SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
    FROM staff ORDER BY staff.surname ASC, staff.forename ASC
\endcode

If we need to retrieve records with fields that match specific criteria we
can create a filter based on an index.

\quotefile sql/overview/order2/main.cpp
\skipto QSqlCursor
\printline QSqlCursor
\printuntil while 
\caption From \l sql/overview/order2/main.cpp

This executes
\code
    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
\endcode

The "order" \l QSqlIndex contains two fields, "id" and "forename"
which are used to order the results. The "filter" \l 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
\e{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.

\target Extracting_Data
\section3 Extracting Data

\quotefile sql/overview/extract/main.cpp
\skipto QSqlCursor
\printline QSqlCursor
\printuntil qDebug
\printline
\caption From \l sql/overview/extract/main.cpp

In this example we begin by creating a cursor on the creditors table.
We create two \l 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:
\code
    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
\endcode
The filter fields are used in the \c 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 \c{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.

\target Manipulating_Records
\section2 Manipulating Records

Records can be inserted, updated or deleted in a table or view using a
\l QSqlCursor providing that the table or view has a primary index
that uniquely distinguishes each record. If this is not the case a \l
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
\link #Navigating_Result_Sets cursor navigation \endlink 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 \l QSqlRecord::clearValues()). Both \l
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 \link #Subclassing_QSqlCursor subclassing QSqlCursor \endlink for
more information on subclassing; see the \link designer-manual.book Qt
Designer\endlink 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.

\target Inserting_Records
\section3 Inserting Records

\quotefile sql/overview/insert/main.cpp
\skipto QSqlCursor
\printline QSqlCursor
\printuntil } 
\caption From \l 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 \l QSqlRecord buffer in which all the fields
are set to \c NULL. (Note that QSqlCursor::primeInsert() is virtual,
and can be customized by derived classes. See \l 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 \l 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. 

\target Updating_Records
\section3 Updating Records

\quotefile sql/overview/update/main.cpp
\skipto QSqlCursor
\printline QSqlCursor
\printuntil update
\printline
\caption From \l 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 \l
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 \l QSqlQuery is more efficient, e.g.

\code
    QSqlQuery query( "UPDATE prices SET price = price * 1.05"  );
\endcode

\target Deleting_Records
\section3 Deleting Records

\quotefile sql/overview/delete/main.cpp
\skipto QSqlCursor
\printline QSqlCursor
\printuntil del
\caption From \l 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 \c 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.

\code
    QSqlQuery query( "DELETE FROM prices WHERE id >= 2450 AND id <= 2500" );
\endcode

\target Data-Aware_Widgets
\section1 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 \link designer-manual.book Qt
Designer\endlink. 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 \link
designer-manual.book Qt Designer\endlink manual and its accompanying
examples provides additional information.

\target Data-Aware_Tables
\section2 Data-Aware Tables

\quotefile sql/overview/table1/main.cpp
\skipto include
\printline include
\printuntil return 0
\printline
\caption From \l sql/overview/table1/main.cpp

Data-Aware tables require the \c qdatatable.h and \c qsqlcursor.h header
files. We create our application object, call createConnections() and
create the cursor. We create the \l QDataTable passing it a pointer to
the cursor, and set the autoPopulate flag to TRUE. Next we make our \l
QDataTable the main widget and call refresh() to populate it with data
and call show() to make it visible.

The autoPopulate flag tells the \l 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.

\quotefile sql/overview/table2/main.cpp
\skipto staffCursor
\printline staffCursor
\printuntil show 
\caption From \l sql/overview/table2/main.cpp

We create an empty \l 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.

\target Creating_Forms
\section2 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 \link
designer-manual.book Qt Designer\endlink. See the \link
designer-manual.book Qt Designer\endlink manual for more details.

\target Displaying_a_Record
\section3 Displaying a Record

\quotefile sql/overview/form1/main.cpp
\skipto include
\printline include
\printuntil app.exec
\printline
\caption From \l sql/overview/form1/main.cpp

We include the header files for the widgets that we need. We also
include \c qsqldatabase.h and \c qsqlcursor.h as usual, but we now add
\c qsqlform.h.

The form will be presented as a dialog so we subclass \l QDialog with
our own FormDialog class. We use a \l 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 \l 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 \l QSqlForm. Finally we call readFields() to populate the
widgets with data from the database via the cursor's buffer.

\target Displaying_a_Record_in_a_DataForm
\section3 Displaying a Record in a Data Form

\l QDataView is a Widget that can hold a read-only \l QSqlForm. In
addition to \l QSqlForm it offers the slot refresh( \l QSqlRecord * ) so it
can easily be linked together with a \l QDataTable to display a detailed
view of a record:

\code
    connect( myDataTable, SIGNAL( currentChanged( QSqlRecord* ) ), 
	     myDataView, SLOT( refresh( QSqlRecord* ) ) );
\endcode

\target Editing_a_Record
\section3 Editing a Record

This example is similar to the previous one so we will focus on the
differences.

\quotefile sql/overview/form2/main.h
\skipto class
\printline class
\printuntil };
\caption From \l 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 \l QSqlCursor and the
\l QSqlForm since they will need to be accessed outside the constructor.

\quotefile sql/overview/form2/main.cpp
\skipto setTrimmed
\printline setTrimmed
\printline

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

\skipto forenameEdit
\printline forenameEdit

\skipto saveButton
\printline saveButton
\printline connect

The FormDialog constructor is similar to the one in the previous
example. We have changed the forename and surname widgets to
\l{QLineEdit}s to make them editable and have added a \l QPushButton
the user can click to save their updates.

\skipto saveButton
\printline saveButton

We add an extra row to the grid containing the save button.

\skipto staffCursor
\printline staffCursor
\printuntil first

We create a \l QSqlIndex object and then execute a select() using the
index. We then move to the first record in the result set.

\skipto new QSqlForm
\printline 
\printline

We create a new QSqlForm object and set it's record buffer to the
cursor's update buffer.

\skipto insert
\printline insert
\printuntil readFields

Now we link the buffer's fields to the \l QLineEdit controls. (In the
previous example we linked the cursor's fields.) The edit controls are
populated by the readFields() call as before.

\skipto FormDialog::
\printline FormDialog::
\printuntil }

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.

\skipto save
\printline save
\printuntil }

Finally we add the save functionality for when the user presses the
save button. We write back the data from the widgets to the \l
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 \l QSqlIndex
and move to the first record.

QDataBrowser and QDataView are widgets which provide a great deal of
the above functionality. \l QDataBrowser provides a data form which
allows editing of and navigation through a cursor's records. \l
QDataView provides a read only form for data in a cursor or database
record. See the class documentation or the \link designer-manual.book
Qt Designer\endlink manual for more information on using these
widgets.

Link to \l sql/overview/form2/main.cpp

\target Custom_Editor_Widgets
\section2 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 \l
sql/overview/custom1/main.h and \l sql/overview/custom1/main.cpp

\quotefile sql/overview/custom1/main.h
\skipto CustomEdit
\printline CustomEdit
\printuntil };

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

\skipto propMap
\printline propMap

We will be using a property map so we add a pointer to a property map
to our FormDialog's private data.

\quotefile sql/overview/custom1/main.cpp
\skipto CustomEdit
\printline CustomEdit
\printuntil }

In the CustomEdit constructor we use the QLineEdit constructor and add
a connection between the textChanged signal and our own changed slot.

\skipto changed
\printline changed
\printuntil }

The changed() slot calls our setUpperLine() function.

\skipto setUpperLine
\printline setUpperLine
\printuntil }

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.

\skipto FormDialog
\skipto CustomEdit
\printline CustomEdit

\skipto CustomEdit
\printline CustomEdit

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.

\skipto propMap
\printline propMap
\printline propMap

We create a new property map on the heap and register our CustomEdit
class and its upperLine property with the property map.

\skipto QSqlForm
\printline QSqlForm
\printline
\printline 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.

\target Custom_Editor_Widgets_for_Tables
\section3 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.

\quotefile sql/overview/table3/main.h
\skipto StatusPicker
\printline StatusPicker
\printuntil }; 
\caption From \l 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.

\skipto CustomSqlEditor
\printline CustomSqlEditor
\printuntil }; 

We also need to subclass QSqlEditorFactory declaring a createEditor()
function since that is the only function we need to reimplement. 

\quotefile sql/overview/table3/main.cpp
\skipto StatusPicker
\printline StatusPicker
\printuntil index2id
\printline
\printline
\caption From \l 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.

\skipto StatusPicker
\printline StatusPicker
\printuntil } 

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.

\skipto StatusPicker
\printline StatusPicker
\printuntil } 
\printline
\printline

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.

\quotefile sql/overview/table4/main.h
\skipto CustomTable
\printline CustomTable
\printuntil };
\caption From \l sql/overview/table4/main.h

We simply call the original QDataTable constructor without changing
anything. We also declare the paintField function.

\quotefile sql/overview/table4/main.cpp
\skipto CustomTable
\printline CustomTable
\printuntil QDataTable
\printline
\caption From \l 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 \c{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. 

\target Subclassing_QSqlCursor
\section1 Subclassing QSqlCursor

\quotefile sql/overview/subclass1/main.cpp
\skipto include
\printline include
\printuntil return 1
\printline
\caption From \l 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. 

\quotefile sql/overview/subclass2/main.h
\skipto InvoiceItem
\printline InvoiceItem
\printuntil };
\caption From \l sql/overview/subclass2/main.h

We have created a separate header file and subclassed QSqlCursor.

\quotefile sql/overview/subclass2/main.cpp
\skipto InvoiceItem
\printline InvoiceItem
\printuntil }
\caption From \l 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.

\skipto InvoiceItemCursor
\printline 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.

\quotefile sql/overview/subclass3/main.h
\skipto protected
\printline protected
\printline
\caption From \l 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.

\quotefile sql/overview/subclass3/main.cpp
\skipto InvoiceItem
\printline InvoiceItem
\printuntil return QVariant
\printline
\caption From \l 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.

\skipto addColumn
\printline addColumn

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, \l 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.

\quotefile sql/overview/subclass4/main.cpp
\skipto InvoiceItem
\printline InvoiceItem
\printuntil }
\caption From \l 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().

\skipto InvoiceItem
\printline InvoiceItem
\printuntil QString::null
\printline
\caption From \l 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. 

\quotefile sql/overview/subclass5/main.h
\skipto primeInsert
\printline primeInsert
\caption From \l 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.

\quotefile sql/overview/subclass5/main.cpp
\skipto primeInsert
\printline primeInsert
\printuntil }
\caption From \l 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.

\target Example_Tables
\section1 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. 

\code
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)
\endcode 

A sequence was used in the calculateField() example above. Note that
sequences are not supported in all databases.

\code
create sequence invoiceitem_seq
\endcode

*/