diff options
Diffstat (limited to 'doc/sql.doc')
| -rw-r--r-- | doc/sql.doc | 1351 |
1 files changed, 1351 insertions, 0 deletions
diff --git a/doc/sql.doc b/doc/sql.doc new file mode 100644 index 0000000..5aa8266 --- /dev/null +++ b/doc/sql.doc @@ -0,0 +1,1351 @@ +/**************************************************************************** +** +** 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 sales@trolltech.com. +** +** 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 + +*/ |