source: trunk/doc/html/sql.html@ 190

Last change on this file since 190 was 190, checked in by rudi, 14 years ago

reference documentation added
File size: 94.3 KB
Line 
1<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
2<!-- /home/espenr/tmp/qt-3.3.8-espenr-2499/qt-x11-free-3.3.8/doc/sql.doc:69 -->
3<html>
4<head>
5<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
6<title>SQL Module</title>
7<style type="text/css"><!--
8fn { margin-left: 1cm; text-indent: -1cm; }
9a:link { color: #004faf; text-decoration: none }
10a:visited { color: #672967; text-decoration: none }
11body { background: #ffffff; color: black; }
12--></style>
13</head>
14<body>
15
16<table border="0" cellpadding="0" cellspacing="0" width="100%">
17<tr bgcolor="#E5E5E5">
18<td valign=center>
19 <a href="index.html">
20<font color="#004faf">Home</font></a>
21 | <a href="classes.html">
22<font color="#004faf">All&nbsp;Classes</font></a>
23 | <a href="mainclasses.html">
24<font color="#004faf">Main&nbsp;Classes</font></a>
25 | <a href="annotated.html">
26<font color="#004faf">Annotated</font></a>
27 | <a href="groups.html">
28<font color="#004faf">Grouped&nbsp;Classes</font></a>
29 | <a href="functions.html">
30<font color="#004faf">Functions</font></a>
31</td>
32<td align="right" valign="center"><img src="logo32.png" align="right" width="64" height="32" border="0"></td></tr></table><h1 align=center>SQL Module</h1>
33
34
35<p>
36<p> <center><table cellpadding="4" cellspacing="2" border="0">
37<tr bgcolor="#f0f0f0">
38<td valign="top"><a href="qsql.html">QSql</a>
39<td valign="top"><a href="qsqlcursor.html">QSqlCursor</a>
40<td valign="top"><a href="qsqldatabase.html">QSqlDatabase</a>
41<td valign="top"><a href="qsqldriver.html">QSqlDriver</a>
42<td valign="top"><a href="qsqldriverplugin.html">QSqlDriverPlugin</a>
43<tr bgcolor="#d0d0d0">
44<td valign="top"><a href="qsqleditorfactory.html">QSqlEditorFactory</a>
45<td valign="top"><a href="qsqlerror.html">QSqlError</a>
46<td valign="top"><a href="qsqlfield.html">QSqlField</a>
47<td valign="top"><a href="qsqlfieldinfo.html">QSqlFieldInfo</a>
48<td valign="top"><a href="qsqlform.html">QSqlForm</a>
49<tr bgcolor="#f0f0f0">
50<td valign="top"><a href="qsqlindex.html">QSqlIndex</a>
51<td valign="top"><a href="qsqlpropertymap.html">QSqlPropertyMap</a>
52<td valign="top"><a href="qsqlquery.html">QSqlQuery</a>
53<td valign="top"><a href="qsqlrecord.html">QSqlRecord</a>
54<td valign="top"><a href="qsqlrecordinfo.html">QSqlRecordInfo</a>
55<tr bgcolor="#d0d0d0">
56<td valign="top"><a href="qsqlresult.html">QSqlResult</a>
57<td valign="top"><a href="qsqlselectcursor.html">QSqlSelectCursor</a>
58<td valign="top" colspan="3" rowspan="1"> See also: <a href="sql-driver.html">Supported Drivers</a>
59</table></center>
60<p> <!-- toc -->
61<ul>
62<li><a href="#1"> Introduction
63</a>
64<li><a href="#2"> SQL Module Architecture
65</a>
66<li><a href="#3"> SQL Driver Plugins
67</a>
68<li><a href="#4"> Connecting to Databases
69</a>
70<li><a href="#5"> Connecting to a Single Database
71</a>
72<ul>
73<li><a href="#5-1"> Connecting to Multiple Databases
74</a>
75</ul>
76<li><a href="#6"> Executing SQL Commands Using QSqlQuery
77</a>
78<ul>
79<li><a href="#6-1"> Transactions
80</a>
81<li><a href="#6-2"> Basic Browsing
82</a>
83<li><a href="#6-3"> Basic Data Manipulation
84</a>
85<li><a href="#6-4"> Navigating Result Sets
86</a>
87</ul>
88<li><a href="#7"> Using QSqlCursor
89</a>
90<ul>
91<li><a href="#7-1"> Retrieving Records
92</a>
93<ul>
94<li><a href="#7-1-1"> Sorting and Filtering Records
95</a>
96<li><a href="#7-1-2"> Extracting Data
97</a>
98</ul>
99<li><a href="#7-2"> Manipulating Records
100</a>
101<ul>
102<li><a href="#7-2-1"> Inserting Records
103</a>
104<li><a href="#7-2-2"> Updating Records
105</a>
106<li><a href="#7-2-3"> Deleting Records
107</a>
108</ul>
109</ul>
110<li><a href="#8"> Data-Aware Widgets
111</a>
112<ul>
113<li><a href="#8-1"> Data-Aware Tables
114</a>
115<li><a href="#8-2"> Creating Data-Aware Forms
116</a>
117<ul>
118<li><a href="#8-2-1"> Displaying a Record
119</a>
120<li><a href="#8-2-2"> Displaying a Record in a Data Form
121</a>
122<li><a href="#8-2-3"> Editing a Record
123</a>
124</ul>
125<li><a href="#8-3"> Custom Editor Widgets
126</a>
127<ul>
128<li><a href="#8-3-1"> Custom Editor Widgets for Tables
129</a>
130</ul>
131</ul>
132<li><a href="#9"> Subclassing QSqlCursor
133</a>
134<li><a href="#10"> The Example Tables
135</a>
136</ul>
137<!-- endtoc -->
138
139<p> <a name="Introduction"></a>
140<h2> Introduction
141</h2>
142<a name="1"></a><p> Qt's SQL classes help you provide seamless database integration to
143your Qt applications.
144<p> <blockquote>
145This overview assumes that you have at least a basic knowledge of SQL.
146You should be able to understand simple <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt>
147and <tt>DELETE</tt> commands. Although the <a href="qsqlcursor.html">QSqlCursor</a> class provides an
148interface to database browsing and editing that does not <em>require</em> a
149knowledge of SQL, a basic understanding of SQL is highly recommended. A
150standard text covering SQL databases is <em>An Introduction to Database Systems (7th ed.)</em> by C. J. Date, ISBN 0201385902.
151</blockquote>
152<p> Whilst this module overview presents the classes from a purely
153programmatic point of view the <a href="designer-manual.html">Qt
154Designer</a> manual's "Creating Database Applications" chapter
155takes a higher-level approach demonstrating how to set up
156master-detail relationships between widgets, perform drilldown and
157handle foreign key lookups.
158<p> This document is divided into six sections:
159<p> <a href="#Architecture">SQL Module Architecture</a>. This describes
160how the classes fit together.
161<p> <a href="#Connecting_to_Databases">Connecting to Databases</a>.
162This section explains how to set up database connections using the <a href="qsqldatabase.html">QSqlDatabase</a> class.
163<p> <a href="#Executing_SQL_commands">Executing SQL Commands</a>. This
164section demonstrates how to issue the standard data manipulation
165commands, <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt> on tables in
166the database (although any valid SQL statement can be sent to the
167database). The focus is purely on database interaction using <a href="qsqlquery.html">QSqlQuery</a>.
168<p> <a href="#Using_QSqlCursor">Using Cursors</a>. This section explains
169how to use the <a href="qsqlcursor.html">QSqlCursor</a> class which provides a simpler API than the
170raw SQL used with <a href="qsqlquery.html">QSqlQuery</a>.
171<p> <a href="#Data-Aware_Widgets">Data-Aware Widgets</a>. This section shows
172how to programmatically link your database to the user interface. In
173this section we introduce the <a href="qdatatable.html">QDataTable</a>, <a href="qsqlform.html">QSqlForm</a>, <a href="qsqlpropertymap.html">QSqlPropertyMap</a> and <a href="qsqleditorfactory.html">QSqlEditorFactory</a> classes and demonstrate how to
174use custom data-aware widgets. <a href="designer-manual.html">Qt
175Designer</a> provides an easy visual way of achieving the same
176thing. See the <a href="designer-manual.html">Qt Designer</a> manual,
177<a href="qdatabrowser.html">QDataBrowser</a> and <a href="qdataview.html">QDataView</a> for more information.
178<p> <a href="#Subclassing_QSqlCursor">Subclassing QSqlCursor</a>. This
179section gives examples of subclassing QSqlCursor. Subclassing can be
180used to provide default and calculated values for fields (such as
181auto-numbered primary index fields), and to display calculated data,
182e.g. showing names rather than ids of foreign keys.
183<p> All the examples in this document use the tables defined in the
184<a href="#Example_Tables">Example Tables</a> section.
185<p> <a name="Architecture"></a>
186<h2> SQL Module Architecture
187</h2>
188<a name="2"></a><p> The SQL classes are divided into three layers:
189<p> <em>User Interface Layer.</em> These classes provide data-aware widgets
190that can be connected to tables or views in the database (by using a
191<a href="qsqlcursor.html">QSqlCursor</a> as a data source). End users can interact directly with
192these widgets to browse or edit data. <a href="designer-manual.html">Qt
193Designer</a> is fully integrated with the SQL classes and can be
194used to create data-aware forms. The data-aware widgets can also be
195programmed directly with your own C++ code. The classes that support
196this layer include <a href="qsqleditorfactory.html">QSqlEditorFactory</a>, <a href="qsqlform.html">QSqlForm</a>, <a href="qsqlpropertymap.html">QSqlPropertyMap</a>, <a href="qdatatable.html">QDataTable</a>, <a href="qdatabrowser.html">QDataBrowser</a> and <a href="qdataview.html">QDataView</a>.
197<p> <em>SQL API Layer.</em> These classes provide access to databases.
198Connections are made using the <a href="qsqldatabase.html">QSqlDatabase</a> class. Database
199interaction is achieved either by using the <a href="qsqlquery.html">QSqlQuery</a> class and
200executing SQL commands directly or by using the higher level <a href="qsqlcursor.html">QSqlCursor</a> class which composes SQL commands automatically. In
201addition to <a href="qsqldatabase.html">QSqlDatabase</a>, <a href="qsqlcursor.html">QSqlCursor</a> and <a href="qsqlquery.html">QSqlQuery</a>, the SQL
202API layer is supported by <a href="qsqlerror.html">QSqlError</a>, <a href="qsqlfield.html">QSqlField</a>, <a href="qsqlfieldinfo.html">QSqlFieldInfo</a>,
203<a href="qsqlindex.html">QSqlIndex</a>, <a href="qsqlrecord.html">QSqlRecord</a> and <a href="qsqlrecordinfo.html">QSqlRecordInfo</a>.
204<p> <em>Driver Layer.</em> This comprises three classes, <a href="qsqlresult.html">QSqlResult</a>, <a href="qsqldriver.html">QSqlDriver</a> and QSqlDriverFactoryInterface. This layer provides the
205low level bridge between the database and the SQL classes. This layer
206is <a href="sql-driver.html">documented separately</a> since it is
207only relevant to driver writers, and is rarely used in standard
208database application programming. See <a href="sql-driver.html">here</a> for more information on implementing a Qt SQL driver plugin.
209<p> <a name="Plugins"></a>
210<h2> SQL Driver Plugins
211</h2>
212<a name="3"></a><p> The Qt SQL module can dynamically load new drivers at runtime using
213the <a href="plugins-howto.html">Plugins</a>.
214<p> The <a href="sql-driver.html">SQL driver documentation</a> describes
215how to build plugins for specific database management systems.
216<p> Once a plugin is built, Qt will automatically load it, and the driver
217will be available for use by <a href="qsqldatabase.html">QSqlDatabase</a> (see <a href="qsqldatabase.html#drivers">QSqlDatabase::drivers</a>()
218for more information).
219<p> <a name="Connecting_to_Databases"></a>
220<h2> Connecting to Databases
221</h2>
222<a name="4"></a><p> At least one database connection must be created and opened before the
223<a href="qsqlquery.html">QSqlQuery</a> or <a href="qsqlcursor.html">QSqlCursor</a> classes can be used.
224<p> If the application only needs a single database connection, the <a href="qsqldatabase.html">QSqlDatabase</a> class can create a connection which is used by default
225for all SQL operations. If multiple database connections are required
226these can easily be set up.
227<p> <a href="qsqldatabase.html">QSqlDatabase</a> requires the <a href="qsqldatabase-h.html">qsqldatabase.h</a> header file.
228<p> <a name="Connecting_to_a_Single_Database"></a>
229<h2> Connecting to a Single Database
230</h2>
231<a name="5"></a><p> Making a database connection is a simple three step process: activate
232the driver, set up the connection information, and open the
233connection.
234<p>
235
236<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
237 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
238 #include "../connection.h"
239
240 int main( int argc, char *argv[] )
241 {
242 <a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
243
244 <a name="x2141"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *defaultDB = QSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER );
245 <a name="x2143"></a> defaultDB-&gt;<a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME );
246 <a name="x2146"></a> defaultDB-&gt;<a href="qsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER );
247 <a name="x2145"></a> defaultDB-&gt;<a href="qsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD );
248 <a name="x2144"></a> defaultDB-&gt;<a href="qsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST );
249
250 <a name="x2142"></a> if ( defaultDB-&gt;<a href="qsqldatabase.html#open">open</a>() ) {
251 // Database successfully opened; we can now issue SQL commands.
252 }
253
254 return 0;
255 }
256</pre><blockquote><p align="center"><em> From <a href="sql-overview-connect1-main-cpp.html">sql/overview/connect1/main.cpp</a>
257</em></p>
258</blockquote><p> First we activate the driver by calling <a href="qsqldatabase.html#addDatabase">QSqlDatabase::addDatabase</a>(),
259passing the name of the driver we wish to use for this connection. At
260the time of writing the available drivers are: <a href="sql-driver.html#QODBC3">QODBC3</a> (Open Database
261Connectivity, includes Microsoft SQL Server support), <a href="sql-driver.html#QOCI8">QOCI8</a> (Oracle 8 and 9),
262<a href="sql-driver.html#QTDS7">QTDS7</a> (Sybase Adaptive Server), <a href="sql-driver.html#QPSQL7">QPSQL7</a> (PostgreSQL 6 and 7),
263<a href="sql-driver.html#QMYSQL3">QMYSQL3</a> (MySQL), <a href="sql-driver.html#QDB2">QDB2</a> (IBM DB2), <a href="sql-driver.html#QSQLITE">QSQLITE</a> (SQLite) and <a href="sql-driver.html#QIBASE">QIBASE</a> (Interbase).
264Note that some of these drivers aren't included in the Qt Open Source Edition; see
265the <tt>README</tt> files for details.
266<p> The connection which is created becomes the application's default
267database connection and will be used by the Qt SQL classes if no
268other database is specified.
269<p> Second we call setDatabaseName(), setUserName(), setPassword() and
270setHostName() to initialize the connection information. Note that for
271the QOCI8 (Oracle 8 and 9) driver the TNS Service Name must be passed
272to setDatbaseName(). When connecting to ODBC data sources the Data
273Source Name (DSN) should be used in the setDatabaseName() call.
274<p> Third we call open() to open the database and give us access to the
275data. If this call fails it will return FALSE; error information can
276be obtained from <a href="qsqldatabase.html#lastError">QSqlDatabase::lastError</a>().
277<p> <a name="Connecting_to_Multiple_Databases"></a>
278<h3> Connecting to Multiple Databases
279</h3>
280<a name="5-1"></a><p> Connecting to multiple databases is achieved using the two argument form
281of <a href="qsqldatabase.html#addDatabase">QSqlDatabase::addDatabase</a>() where the second argument is a unique
282identifier distinguishing the connection.
283<p> In the example below we have moved the connections into their own
284function, <tt>createConnections()</tt>, and added some basic error handling.
285<p> <pre>
286#define DB_SALES_DRIVER "QPSQL7"
287#define DB_SALES_DBNAME "sales"
288#define DB_SALES_USER "salesperson"
289#define DB_SALES_PASSWD "salesperson"
290#define DB_SALES_HOST "database.domain.no"
291
292#define DB_ORDERS_DRIVER "QOCI8"
293#define DB_ORDERS_DBNAME "orders"
294#define DB_ORDERS_USER "orderperson"
295#define DB_ORDERS_PASSWD "orderperson"
296#define DB_ORDERS_HOST "database.domain.no"
297
298bool createConnections();
299</pre>
300
301<p> We set up some constants and also declare the <tt>createConnections()</tt>
302function in <tt>connection.h</tt>.
303<p>
304
305<pre> #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
306 #include "connection.h"
307
308 bool createConnections()
309 {
310
311 <a name="x2147"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *defaultDB = QSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER );
312 <a name="x2150"></a> defaultDB-&gt;<a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME );
313 <a name="x2153"></a> defaultDB-&gt;<a href="qsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER );
314 <a name="x2152"></a> defaultDB-&gt;<a href="qsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD );
315 <a name="x2151"></a> defaultDB-&gt;<a href="qsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST );
316 <a name="x2149"></a> if ( ! defaultDB-&gt;<a href="qsqldatabase.html#open">open</a>() ) {
317 <a name="x2148"></a> <a href="qapplication.html#qWarning">qWarning</a>( "Failed to open sales database: " + defaultDB-&gt;<a href="qsqldatabase.html#lastError">lastError</a>().text() );
318 return FALSE;
319 }
320
321 <a href="qsqldatabase.html">QSqlDatabase</a> *oracle = QSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_ORDERS_DRIVER, "ORACLE" );
322 oracle-&gt;<a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_ORDERS_DBNAME );
323 oracle-&gt;<a href="qsqldatabase.html#setUserName">setUserName</a>( DB_ORDERS_USER );
324 oracle-&gt;<a href="qsqldatabase.html#setPassword">setPassword</a>( DB_ORDERS_PASSWD );
325 oracle-&gt;<a href="qsqldatabase.html#setHostName">setHostName</a>( DB_ORDERS_HOST );
326 if ( ! oracle-&gt;<a href="qsqldatabase.html#open">open</a>() ) {
327 <a href="qapplication.html#qWarning">qWarning</a>( "Failed to open orders database: " + oracle-&gt;<a href="qsqldatabase.html#lastError">lastError</a>().text() );
328 return FALSE;
329 }
330
331 <a href="qsqlquery.html">QSqlQuery</a> q(QString::null, defaultDB);
332 <a name="x2154"></a> q.<a href="qsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))");
333 q.<a href="qsqlquery.html#exec">exec</a>("create table staff (id integer primary key, forename char(40), "
334 "surname char(40), salary float, statusid integer)");
335 q.<a href="qsqlquery.html#exec">exec</a>("create table status (id integer primary key, name char(30))");
336 q.<a href="qsqlquery.html#exec">exec</a>("create table creditors (id integer primary key, forename char(40), "
337 "surname char(40), city char(30))");
338 q.<a href="qsqlquery.html#exec">exec</a>("create table prices (id integer primary key, name char(40), price float)");
339 q.<a href="qsqlquery.html#exec">exec</a>("create table invoiceitem (id integer primary key, "
340 "pricesid integer, quantity integer, paiddate date)");
341
342 <a href="qsqlquery.html">QSqlQuery</a> q2(QString::null, oracle);
343 q2.<a href="qsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))");
344
345 return TRUE;
346 }
347</pre><blockquote><p align="center"><em> From <a href="sql.html">sql/overview/connection.cpp</a>
348</em></p>
349</blockquote><p> We've chosen to isolate database connection in our <tt>createConnections()</tt> function.cpp.
350<p> <a name="create_connections"></a>
351
352
353<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
354 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
355 #include "../connection.h"
356
357 int main( int argc, char *argv[] )
358 {
359 <a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
360
361 if ( createConnections() ) {
362 // Databases successfully opened; get pointers to them:
363 <a name="x2155"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *oracledb = QSqlDatabase::<a href="qsqldatabase.html#database">database</a>( "ORACLE" );
364 // Now we can now issue SQL commands to the oracle connection
365 // or to the default connection
366 }
367
368 return 0;
369 }
370</pre><blockquote><p align="center"><em> From <a href="sql-overview-create_connections-main-cpp.html">sql/overview/create_connections/main.cpp</a>
371</em></p>
372</blockquote><p> The static function <a href="qsqldatabase.html#database">QSqlDatabase::database</a>() can be called from
373anywhere to provide a pointer to a database connection. If we call it
374without a parameter it will return the default connection. If called
375with the identifier we've used for a connection, e.g. "ORACLE", in the
376above example, it will return a pointer to the specified connection.
377<p> If you create a <tt>main.cpp</tt> using <a href="designer-manual.html">Qt
378Designer</a>, it will <em>not</em> include our example
379createConnections() function. This means that applications that
380preview correctly in <a href="designer-manual.html">Qt Designer</a>
381will not run unless you implement your own database connections
382function.
383<p> Note that in the code above the ODBC connection was not named and is
384therefore used as the default connection. <a href="qsqldatabase.html">QSqlDatabase</a> maintains
385ownership of the pointers returned by the addDatabase() static
386function. To remove a database from the list of maintained
387connections, first close the database with <a href="qsqldatabase.html#close">QSqlDatabase::close</a>(), and
388then remove it using the static function
389<a href="qsqldatabase.html#removeDatabase">QSqlDatabase::removeDatabase</a>().
390<p> <a name="Executing_SQL_commands"></a>
391<h2> Executing SQL Commands Using <a href="qsqlquery.html">QSqlQuery</a>
392</h2>
393<a name="6"></a><p> The <a href="qsqlquery.html">QSqlQuery</a> class provides an interface for executing SQL commands.
394It also has functions for navigating through the result sets of <tt>SELECT</tt>
395queries and for retrieving individual records and field values.
396<p> The <a href="qsqlcursor.html">QSqlCursor</a> class described in the next section inherits from <a href="qsqlquery.html">QSqlQuery</a> and provides a higher level interface that composes SQL
397commands for us. <a href="qsqlcursor.html">QSqlCursor</a> is particularly easy to integrate with
398on-screen widgets. Programmers unfamiliar with SQL can safely skip this
399section and use the <a href="qsqlcursor.html">QSqlCursor</a> class covered in
400<a href="#Using_QSqlCursor">"Using QSqlCursor"</a>.
401<p> <a name="Transactions"></a>
402<h3> Transactions
403</h3>
404<a name="6-1"></a><p> If the underlying database engine supports transactions
405<a href="qsqldriver.html#hasFeature">QSqlDriver::hasFeature</a>( QSqlDriver::Transactions ) will return TRUE.
406You can use <a href="qsqldatabase.html#transaction">QSqlDatabase::transaction</a>() to initiate a transaction,
407followed by the SQL commands you want to execute within the context of
408the transaction, and then either <a href="qsqldatabase.html#commit">QSqlDatabase::commit</a>() or
409<a href="qsqldatabase.html#rollback">QSqlDatabase::rollback</a>().
410<p> <a name="Basic_Browsing"></a>
411<h3> Basic Browsing
412</h3>
413<a name="6-2"></a><p>
414
415<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
416 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
417 #include &lt;<a href="qsqlquery-h.html">qsqlquery.h</a>&gt;
418 #include "../connection.h"
419
420 int main( int argc, char *argv[] )
421 {
422 <a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
423
424 if ( createConnections() ) {
425 <a name="x2156"></a> <a href="qsqldatabase.html">QSqlDatabase</a> *oracledb = QSqlDatabase::<a href="qsqldatabase.html#database">database</a>( "ORACLE" );
426 // Copy data from the oracle database to the ODBC (default)
427 // database
428 <a href="qsqlquery.html">QSqlQuery</a> target;
429 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT id, name FROM people", oracledb );
430 <a name="x2158"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) {
431 <a name="x2159"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) {
432 <a name="x2157"></a> target.<a href="qsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " +
433 <a name="x2160"></a> query.<a href="qsqlquery.html#value">value</a>(0).toString() +
434 ", '" + query.<a href="qsqlquery.html#value">value</a>(1).toString() + "' )" );
435 }
436 }
437 }
438
439 return 0;
440 }
441</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicbrowsing-main-cpp.html">sql/overview/basicbrowsing/main.cpp</a>
442</em></p>
443</blockquote><p> In the example above we've added an additional header file,
444<a href="qsqlquery-h.html">qsqlquery.h</a>. The first query we create, <tt>target</tt>, uses the default
445database and is initially empty. For the second query, <tt>q</tt>, we specify
446the "ORACLE" database that we want to retrieve records from. Both the
447database connections were set up in the createConnections() function we
448wrote earlier.
449<p> After creating the initial <tt>SELECT</tt> statement, isActive() is checked
450to see if the query executed successfully. The next() function is
451used to iterate through the query results. The value() function
452returns the contents of fields as QVariants. The insertions are
453achieved by creating and executing queries against the default
454database using the <tt>target</tt> <a href="qsqlquery.html">QSqlQuery</a>.
455<p> Note that this example and all the other examples in this document use
456the tables defined in the <a href="#Example_Tables">Example Tables</a>
457section.
458<p>
459
460<pre> int count = 0;
461 <a name="x2162"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) {
462 <a name="x2163"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) {
463 <a name="x2161"></a> target.<a href="qsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " +
464 <a name="x2165"></a> query.<a href="qsqlquery.html#value">value</a>(0).toString() +
465 ", '" + query.<a href="qsqlquery.html#value">value</a>(1).toString() + "' )" );
466 if ( target.<a href="qsqlquery.html#isActive">isActive</a>() )
467 <a name="x2164"></a> count += target.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>();
468 }
469 }
470</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicbrowsing2-main-cpp.html">sql/overview/basicbrowsing2/main.cpp</a>
471</em></p>
472</blockquote><p> The above code introduces a count of how many records are successfully
473inserted. Note that isActive() returns FALSE if the query, e.g. the
474insertion, fails. numRowsAffected() returns -1 if the number of rows
475cannot be determined, e.g. if the query fails.
476<p> <a name="Basic_Data_Manipulation"></a>
477<h3> Basic Data Manipulation
478</h3>
479<a name="6-3"></a><p>
480
481<pre> ** $Id: sql.html 2051 2007-02-21 10:04:20Z chehrlic $
482 **
483 ** Copyright (C) 1992-2007 Trolltech ASA. All rights reserved.
484 **
485 ** This file is part of an example program for Qt. This example
486 ** program may be used, distributed and modified without limitation.
487 **
488 *****************************************************************************/
489
490 #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
491 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
492 #include &lt;<a href="qsqlquery-h.html">qsqlquery.h</a>&gt;
493 #include "../connection.h"
494
495 bool createConnections();
496
497 int main( int argc, char *argv[] )
498 {
499 <a href="qapplication.html">QApplication</a> app( argc, argv, FALSE );
500
501 int rows = 0;
502
503 if ( createConnections() ) {
504 <a href="qsqlquery.html">QSqlQuery</a> query( "INSERT INTO staff ( id, forename, surname, salary ) "
505 "VALUES ( 1155, 'Ginger', 'Davis', 50000 )" );
506 <a name="x2168"></a><a name="x2167"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
507
508 <a name="x2166"></a> query.<a href="qsqlquery.html#exec">exec</a>( "UPDATE staff SET salary=60000 WHERE id=1155" );
509 if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
510
511 query.<a href="qsqlquery.html#exec">exec</a>( "DELETE FROM staff WHERE id=1155" );
512 if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
513 }
514
515 return ( rows == 3 ) ? 0 : 1;
516 }
517</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicdatamanip-main-cpp.html">sql/overview/basicdatamanip/main.cpp</a>
518</em></p>
519</blockquote><p> This example demonstrates straightforward SQL DML (data manipulation
520language) commands. Since we did not specify a database in the <a href="qsqlquery.html">QSqlQuery</a> constructor the default database is used. <a href="qsqlquery.html">QSqlQuery</a> objects
521can also be used to execute SQL DDL (data definition language) commands
522such as <tt>CREATE TABLE</tt> and <tt>CREATE INDEX</tt>.
523<p> <a name="Navigating_Result_Sets"></a>
524<h3> Navigating Result Sets
525</h3>
526<a name="6-4"></a><p> Once a <tt>SELECT</tt> query has been executed successfully we have access
527to the result set of records that matched the query criteria. We have
528already used one of the navigation functions, next(), which can be
529used alone to step sequentially through the records. <a href="qsqlquery.html">QSqlQuery</a> also
530provides first(), last() and prev(). After any of these commands we
531can check that we are on a valid record by calling isValid().
532<p> We can also navigate to any arbitrary record using seek(). The
533first record in the dataset is zero. The number of the last record is
534size() - 1. Note that not all databases provide the size of a
535<tt>SELECT</tt> query and in such cases size() returns -1.
536<p>
537
538<pre> if ( createConnections() ) {
539 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT id, name FROM people ORDER BY name" );
540 <a name="x2171"></a> if ( ! query.<a href="qsqlquery.html#isActive">isActive</a>() ) return 1; // Query failed
541 int i;
542 <a name="x2174"></a> i = query.<a href="qsqlquery.html#size">size</a>(); // In this example we have 9 records; i == 9.
543 <a name="x2170"></a> query.<a href="qsqlquery.html#first">first</a>(); // Moves to the first record.
544 <a name="x2169"></a> i = query.<a href="qsqlquery.html#at">at</a>(); // i == 0
545 <a name="x2172"></a> query.<a href="qsqlquery.html#last">last</a>(); // Moves to the last record.
546 i = query.<a href="qsqlquery.html#at">at</a>(); // i == 8
547 <a name="x2173"></a> query.<a href="qsqlquery.html#seek">seek</a>( query.<a href="qsqlquery.html#size">size</a>() / 2 ); // Moves to the middle record.
548 i = query.<a href="qsqlquery.html#at">at</a>(); // i == 4
549 }
550</pre><blockquote><p align="center"><em> From <a href="sql-overview-navigating-main-cpp.html">sql/overview/navigating/main.cpp</a>
551</em></p>
552</blockquote><p> The example above shows some of the navigation functions in use.
553<p> Not all drivers support size(), but we can interrogate the driver to
554find out:
555<p> <pre>
556 <a href="qsqldatabase.html">QSqlDatabase</a>* defaultDB = QSqlDatabase::<a href="qsqldatabase.html#database">database</a>();
557 if ( defaultDB-&gt;<a href="qsqldatabase.html#driver">driver</a>()-&gt;hasFeature( QSqlDriver::QuerySize ) ) {
558 // QSqlQuery::size() supported
559 }
560 else {
561 // QSqlQuery::size() cannot be relied upon
562 }
563</pre>
564
565<p> Once we have located the record we are interested in we may wish to
566retrieve data from it.
567<p>
568
569<pre> if ( createConnections() ) {
570 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT id, surname FROM staff" );
571 <a name="x2175"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) {
572 <a name="x2176"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) {
573 <a href="qapplication.html#qDebug">qDebug</a>( query.<a href="qsqlquery.html#value">value</a>(0).toString() + ": " +
574 <a name="x2177"></a> query.<a href="qsqlquery.html#value">value</a>(1).toString() );
575 }
576 }
577 }
578</pre><blockquote><p align="center"><em> From <a href="sql-overview-retrieve1-main-cpp.html">sql/overview/retrieve1/main.cpp</a>
579</em></p>
580</blockquote><p> Note that if you wish to iterate through the record set in order the
581only navigation function you need is next().
582<p> Tip: The lastQuery() function returns the text of the last query
583executed. This can be useful to check that the query you think is being
584executed is the one actually being executed.
585<p> <a name="Using_QSqlCursor"></a>
586<h2> Using <a href="qsqlcursor.html">QSqlCursor</a>
587</h2>
588<a name="7"></a><p> The <a href="qsqlcursor.html">QSqlCursor</a> class provides a high level interface to browsing and
589editing records in SQL database tables or views without the need to
590write your own SQL.
591<p> QSqlCursor can do almost everything that <a href="qsqlquery.html">QSqlQuery</a> can, with two
592exceptions. Since cursors represent tables or views within the
593database, by default, <a href="qsqlcursor.html">QSqlCursor</a> objects retrieve all the fields of
594each record in the table or view whenever navigating to a new
595record. If only some fields are relevant simply confine your
596processing to those and ignore the others. Or, manually disable the
597generation of certain fields using <a href="qsqlrecord.html#setGenerated">QSqlRecord::setGenerated</a>(). Another
598approach is to create a <tt>VIEW</tt> which only presents the fields you're
599interested in; but note that some databases do not support editable
600views. So if you really don't want to retrieve all the fields in the
601cursor, then you should use a <a href="qsqlquery.html">QSqlQuery</a> instead, and customize the
602query to suit your needs. You can edit records using a <a href="qsqlcursor.html">QSqlCursor</a>
603providing that the table or view has a primary index that uniquely
604distinguishes each record. If this condition is not met then you'll
605need to use a <a href="qsqlquery.html">QSqlQuery</a> for edits.
606<p> <a href="qsqlcursor.html">QSqlCursor</a> operates on a single record at a time. Whenever performing
607an insert, update or delete using QSqlCursor, only a single record in
608the database is affected. When navigating through records in the
609cursor, only one record at a time is available in application code.
610In addition, QSqlCursor maintains a separate 'edit buffer' which is
611used to make changes to a single record in the database. The edit
612buffer is maintained in a separate memory area, and is unnaffected by
613the 'navigation buffer' which changes as the cursor moves from record
614to record.
615<p> Before we can use <a href="qsqlcursor.html">QSqlCursor</a> objects we must first create and open
616a database connection. Connecting is described in the <a href="#Connecting_to_Databases">Connecting to Databases</a> section
617above. For the examples that follow we will assume that the
618connections have been created using the createConnections() function
619defined in the <a href="#create_connections">QSqlDatabase example</a>
620presented earlier.
621<p> In the <a href="#Data-Aware_Widgets">data-aware widgets</a> section that
622follows this one we show how to link widgets to database cursors. Once
623we have a knowledge of both cursors and data-aware widgets we can
624discuss <a href="#Subclassing_QSqlCursor">subclassing QSqlCursor</a>.
625<p> The <a href="qsqlcursor.html">QSqlCursor</a> class requires the <a href="qsqlcursor-h.html">qsqlcursor.h</a> header file.
626<p> <a name="Retrieving_Records"></a>
627<h3> Retrieving Records
628</h3>
629<a name="7-1"></a><p>
630
631<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
632 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
633 #include &lt;<a href="qsqlcursor-h.html">qsqlcursor.h</a>&gt;
634 #include "../connection.h"
635
636 int main( int argc, char *argv[] )
637 {
638 <a href="qapplication.html">QApplication</a> app( argc, argv );
639
640 if ( createConnections() ) {
641 <a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" ); // Specify the table/view name
642 <a name="x2178"></a> cur.<a href="qsqlcursor.html#select">select</a>(); // We'll retrieve every record
643 while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
644 <a name="x2179"></a> <a href="qapplication.html#qDebug">qDebug</a>( cur.<a href="qsqlquery.html#value">value</a>( "id" ).toString() + ": " +
645 cur.<a href="qsqlquery.html#value">value</a>( "surname" ).toString() + " " +
646 cur.<a href="qsqlquery.html#value">value</a>( "salary" ).toString() );
647 }
648 }
649
650 return 0;
651 }
652</pre><blockquote><p align="center"><em> From <a href="sql-overview-retrieve2-main-cpp.html">sql/overview/retrieve2/main.cpp</a>
653</em></p>
654</blockquote><p> We create the <a href="qsqlcursor.html">QSqlCursor</a> object, specifying the table or view to use.
655If we need to use a database other than the default we can specify it
656in the <a href="qsqlcursor.html">QSqlCursor</a> constructor.
657<p> The SQL executed by the cur.select() call is
658<p> <pre>
659 SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid FROM staff
660</pre>
661
662<p> Next, we iterate through the records returned by this select statement
663using cur.next(). Field values are retrieved in in a similar way to
664<a href="qsqlquery.html">QSqlQuery</a>, except that we pass field names rather than numeric indexes
665to value() and setValue().
666<p> <a name="Sorting_Data"></a>
667<h4> Sorting and Filtering Records
668</h4>
669<a name="7-1-1"></a><p> To specify a subset of records to retrieve we can pass filtering
670criteria to the select() function. Each record that is returned will
671meet the criteria of the filter (the filter corresponds to the SQL
672statement's <tt>WHERE</tt> clause).
673<p> <pre>
674 cur.select( "id &gt; 100" );
675</pre>
676
677<p> This select() call will execute the SQL
678<pre>
679 SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
680 FROM staff WHERE staff.id &gt; 100
681</pre>
682
683<p> This will retrieve only those staff whose <tt>id</tt> is greater than 100.
684<p> In addition to retrieving selected records we often want to specify a
685sort order for the returned records. This is achieved by creating a <a href="qsqlindex.html">QSqlIndex</a> object which contains the names of the field(s) we wish to
686sort by and pass this object to the select() call.
687<p> <pre>
688 <a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" );
689 <a href="qsqlindex.html">QSqlIndex</a> nameIndex = cur.<a href="qsqlcursor.html#index">index</a>( "surname" );
690 cur.<a href="qsqlcursor.html#select">select</a>( nameIndex );
691</pre>
692
693<p> Here we create a <a href="qsqlindex.html">QSqlIndex</a> object with one field, "surname". When
694we call the select() function we pass the index object, which
695specifies that the records should be returned sorted by
696staff.surname. Each field in the index object is used in the ORDER BY
697clause of the select statement. The SQL executed here is
698<pre>
699 SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
700 FROM staff ORDER BY staff.surname ASC
701</pre>
702
703<p> Combining the retrieval of a subset of records and ordering the results
704is straightforward.
705<p> <pre>
706 cur.select( "staff.surname LIKE 'A%'", nameIndex );
707</pre>
708
709<p> We pass in a filter string (the <tt>WHERE</tt> clause), and the <a href="qsqlindex.html">QSqlIndex</a>
710object to sort by (the <tt>ORDER BY</tt> clause). This produces
711<p> <pre>
712 SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
713 FROM staff WHERE staff.surname LIKE 'A%' ORDER BY staff.surname ASC
714</pre>
715
716<p> To sort by more than one field, an index can be created which contains
717multiple fields. Ascending and descending order can be set using
718<a href="qsqlindex.html#setDescending">QSqlIndex::setDescending</a>(); the default is ascending.
719<p>
720
721<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" );
722 <a href="qstringlist.html">QStringList</a> fields = QStringList() &lt;&lt; "surname" &lt;&lt; "forename";
723 <a name="x2181"></a> <a href="qsqlindex.html">QSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( fields );
724 <a name="x2182"></a> cur.<a href="qsqlcursor.html#select">select</a>( order );
725 <a name="x2183"></a> while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
726</pre><blockquote><p align="center"><em> From <a href="sql-overview-order1-main-cpp.html">sql/overview/order1/main.cpp</a>
727</em></p>
728</blockquote><p> Here we create a string list containing the fields we wish to sort by,
729in the order they are to be used. Then we create a <a href="qsqlindex.html">QSqlIndex</a> object
730based on these fields, finally executing the select() call using this
731index. This executes
732<pre>
733 SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
734 FROM staff ORDER BY staff.surname ASC, staff.forename ASC
735</pre>
736
737<p> If we need to retrieve records with fields that match specific criteria we
738can create a filter based on an index.
739<p>
740
741<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "staff" );
742 <a href="qstringlist.html">QStringList</a> fields = QStringList() &lt;&lt; "id" &lt;&lt; "forename";
743 <a name="x2184"></a> <a href="qsqlindex.html">QSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( fields );
744 <a href="qsqlindex.html">QSqlIndex</a> filter = cur.<a href="qsqlcursor.html#index">index</a>( "surname" );
745 <a name="x2186"></a> cur.<a href="qsqlrecord.html#setValue">setValue</a>( "surname", "Bloggs" );
746 <a name="x2185"></a> cur.<a href="qsqlcursor.html#select">select</a>( filter, order );
747 while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
748</pre><blockquote><p align="center"><em> From <a href="sql-overview-order2-main-cpp.html">sql/overview/order2/main.cpp</a>
749</em></p>
750</blockquote><p> This executes
751<pre>
752 SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid
753 FROM staff WHERE staff.surname='Bloggs' ORDER BY staff.id ASC, staff.forename ASC
754</pre>
755
756<p> The "order" <a href="qsqlindex.html">QSqlIndex</a> contains two fields, "id" and "forename"
757which are used to order the results. The "filter" <a href="qsqlindex.html">QSqlIndex</a>
758contains a single field, "surname". When an index is passed as a
759filter to the select() function, for each field in the filter, a
760<em>fieldname=value</em> subclause is created where the value
761is taken from the current cursor's value for that field. We use
762setValue() to ensure that the value used is the one we want.
763<p> <a name="Extracting_Data"></a>
764<h4> Extracting Data
765</h4>
766<a name="7-1-2"></a><p>
767
768<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "creditors" );
769 <a href="qstringlist.html">QStringList</a> orderFields = QStringList() &lt;&lt; "surname" &lt;&lt; "forename";
770 <a name="x2188"></a> <a href="qsqlindex.html">QSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( orderFields );
771
772 <a href="qstringlist.html">QStringList</a> filterFields = QStringList() &lt;&lt; "surname" &lt;&lt; "city";
773 <a href="qsqlindex.html">QSqlIndex</a> filter = cur.<a href="qsqlcursor.html#index">index</a>( filterFields );
774 <a name="x2190"></a> cur.<a href="qsqlrecord.html#setValue">setValue</a>( "surname", "Chirac" );
775 cur.<a href="qsqlrecord.html#setValue">setValue</a>( "city", "Paris" );
776
777 <a name="x2189"></a> cur.<a href="qsqlcursor.html#select">select</a>( filter, order );
778
779 while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
780 <a name="x2191"></a> int id = cur.<a href="qsqlquery.html#value">value</a>( "id" ).toInt();
781 <a href="qstring.html">QString</a> name = cur.<a href="qsqlquery.html#value">value</a>( "forename" ).toString() + " " +
782 cur.<a href="qsqlquery.html#value">value</a>( "surname" ).toString();
783 <a name="x2193"></a> <a href="qapplication.html#qDebug">qDebug</a>( QString::<a href="qstring.html#number">number</a>( id ) + ": " + name );
784 }
785</pre><blockquote><p align="center"><em> From <a href="sql-overview-extract-main-cpp.html">sql/overview/extract/main.cpp</a>
786</em></p>
787</blockquote><p> In this example we begin by creating a cursor on the creditors table.
788We create two <a href="qsqlindex.html">QSqlIndex</a> objects. The first, "order", is created
789from the "orderFields" string list. The second, "filter", is created
790from the "filterFields" string list. We set the values of the two
791fields used in the filter, "surname" and "city", to the values we're
792interested in. Now we call select() which generates and executes the
793following SQL:
794<pre>
795 SELECT creditors.city, creditors.surname, creditors.forename, creditors.id
796 FROM creditors
797 WHERE creditors.surname = 'Chirac' AND creditors.city = 'Paris'
798 ORDER BY creditors.surname ASC, creditors.forename ASC
799</pre>
800
801The filter fields are used in the <tt>WHERE</tt> clause. Their values are
802taken from the cursor's current values for those fields; we set these
803values ourselves with the setValue() calls. The order fields are used
804in the <tt>ORDER BY</tt> clause.
805<p> Now we iterate through each matching record (if any). We retrieve the
806contents of the id, forename and surname fields and pass them on to
807some processing function, in this example a simple <a href="qapplication.html#qDebug">qDebug</a>() call.
808<p> <a name="Manipulating_Records"></a>
809<h3> Manipulating Records
810</h3>
811<a name="7-2"></a><p> Records can be inserted, updated or deleted in a table or view using a
812<a href="qsqlcursor.html">QSqlCursor</a> providing that the table or view has a primary index
813that uniquely distinguishes each record. If this is not the case a <a href="qsqlquery.html">QSqlQuery</a> must be used instead. (Note that not all databases support
814editable views.)
815<p> Each cursor has an internal 'edit buffer' which is used by all the
816edit operations (insert, update and delete). The editing process is
817the same for each operation: acquire a pointer to the relevant buffer;
818call setValue() to <a href="primes.html#prime">prime</a> the buffer with the values you want; call
819insert() or update() or del() to perform the desired operation. For
820example, when inserting a record using a cursor, you call
821primeInsert() to get a pointer to the edit buffer and then call
822setValue() on this buffer to set each field's value. Then you call
823QSQlCursor::insert() to insert the contents of the edit buffer into
824the database. Similarly, when updating (or deleting) a record, the
825values of the fields in the edit buffer are used to update (or delete)
826the record in the database. The 'edit buffer' is unaffected by any
827<a href="#Navigating_Result_Sets">cursor navigation</a> functions.
828Note that if you pass a string value to setValue() any single quotes
829will be escaped (turned into a pair of single quotes) since a single
830quote is a special character in SQL.
831<p> The primeInsert(), primeUpdate() and primeDelete() methods all return
832a pointer to the internal edit buffer. Each method can potentially
833perform different operations on the edit buffer before returning it.
834By default, <a href="qsqlcursor.html#primeInsert">QSqlCursor::primeInsert</a>() clears all the field values in
835the edit buffer (see <a href="qsqlrecord.html#clearValues">QSqlRecord::clearValues</a>()). Both <a href="qsqlcursor.html#primeUpdate">QSqlCursor::primeUpdate</a>() and <a href="qsqlcursor.html#primeDelete">QSqlCursor::primeDelete</a>() initialize the
836edit buffer with the current contents of the cursor before returning
837it. All three of these functions are virtual, so you can redefine the
838behavior (for example, reimplementing primeInsert() to auto-number
839fields in the edit buffer). Data-aware user-interface controls emit
840signals, e.g. primeInsert(), that you can connect to; these pass a
841pointer to the appropriate buffer so subclassing may not be necessary.
842See <a href="#Subclassing_QSqlCursor">subclassing QSqlCursor</a> for
843more information on subclassing; see the <a href="designer-manual.html">Qt
844Designer</a> manual for more on connecting to the primeInsert()
845signal.
846<p> When insert(), update() or del() is called on a cursor, it will be
847invalidated and will no longer be positioned on a valid record. If you
848need to move to another record after performing an insert(), update()
849or del() you must make a fresh select() call. This ensures that
850changes to the database are accurately reflected in the cursor.
851<p> <a name="Inserting_Records"></a>
852<h4> Inserting Records
853</h4>
854<a name="7-2-1"></a><p>
855
856<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "prices" );
857 <a href="qstringlist.html">QStringList</a> names = QStringList() &lt;&lt;
858 "Screwdriver" &lt;&lt; "Hammer" &lt;&lt; "Wrench" &lt;&lt; "Saw";
859 int id = 20;
860 <a name="x2197"></a> for ( QStringList::Iterator name = names.<a href="qvaluelist.html#begin">begin</a>();
861 <a name="x2198"></a> name != names.<a href="qvaluelist.html#end">end</a>(); ++name ) {
862 <a name="x2195"></a> <a href="qsqlrecord.html">QSqlRecord</a> *buffer = cur.<a href="qsqlcursor.html#primeInsert">primeInsert</a>();
863 <a name="x2196"></a> buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "id", id );
864 buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "name", *name );
865 buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "price", 100.0 + (double)id );
866 <a name="x2194"></a> count += cur.<a href="qsqlcursor.html#insert">insert</a>();
867 id++;
868 }
869</pre><blockquote><p align="center"><em> From <a href="sql-overview-insert-main-cpp.html">sql/overview/insert/main.cpp</a>
870</em></p>
871</blockquote><p> In this example we create a cursor on the "prices" table. Next we
872create a list of product names which we iterate over. For each
873iteration we call the cursor's primeInsert() method. This method
874returns a pointer to a <a href="qsqlrecord.html">QSqlRecord</a> buffer in which all the fields
875are set to <tt>NULL</tt>. (Note that <a href="qsqlcursor.html#primeInsert">QSqlCursor::primeInsert</a>() is virtual,
876and can be customized by derived classes. See <a href="qsqlcursor.html">QSqlCursor</a>). Next we
877call setValue() for each field that requires a value. Finally we call
878insert() to insert the record. The insert() call returns the number of
879rows inserted.
880<p> We obtained a pointer to a <a href="qsqlrecord.html">QSqlRecord</a> object from the primeInsert()
881call. <a href="qsqlrecord.html">QSqlRecord</a> objects can hold the data for a single record plus some
882meta-data about the record. In practice most interaction with a
883QSqlRecord consists of simple value() and setValue() calls as shown in
884this and the following example.
885<p> <a name="Updating_Records"></a>
886<h4> Updating Records
887</h4>
888<a name="7-2-2"></a><p>
889
890<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "prices" );
891 <a name="x2200"></a> cur.<a href="qsqlcursor.html#select">select</a>( "id=202" );
892 if ( cur.<a href="qsqlquery.html#next">next</a>() ) {
893 <a name="x2199"></a> <a href="qsqlrecord.html">QSqlRecord</a> *buffer = cur.<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>();
894 <a name="x2204"></a> double price = buffer-&gt;<a href="qsqlrecord.html#value">value</a>( "price" ).toDouble();
895 double newprice = price * 1.05;
896 <a name="x2203"></a> buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "price", newprice );
897 <a name="x2201"></a> cur.<a href="qsqlcursor.html#update">update</a>();
898 }
899</pre><blockquote><p align="center"><em> From <a href="sql-overview-update-main-cpp.html">sql/overview/update/main.cpp</a>
900</em></p>
901</blockquote><p> This example begins with the creation of a cursor over the prices table.
902We select the record we wish to update with the select() call and
903move to it with the next() call. We call primeUpdate() to get a <a href="qsqlrecord.html">QSqlRecord</a> pointer to a buffer which is populated with the contents of
904the current record. We retrieve the value of the price field, calculate
905a new price, and set the the price field to the newly calculated value.
906Finally we call update() to update the record. The update() call returns
907the number of rows updated.
908<p> If many identical updates need to be performed, for example increasing
909the price of every item in the price list, using a single SQL statement
910with <a href="qsqlquery.html">QSqlQuery</a> is more efficient, e.g.
911<p> <pre>
912 <a href="qsqlquery.html">QSqlQuery</a> query( "UPDATE prices SET price = price * 1.05" );
913</pre>
914
915<p> <a name="Deleting_Records"></a>
916<h4> Deleting Records
917</h4>
918<a name="7-2-3"></a><p>
919
920<pre> <a href="qsqlcursor.html">QSqlCursor</a> cur( "prices" );
921 <a name="x2207"></a> cur.<a href="qsqlcursor.html#select">select</a>( "id=999" );
922 <a name="x2208"></a> if ( cur.<a href="qsqlquery.html#next">next</a>() ) {
923 <a name="x2206"></a> cur.<a href="qsqlcursor.html#primeDelete">primeDelete</a>();
924 <a name="x2205"></a> cur.<a href="qsqlcursor.html#del">del</a>();
925</pre><blockquote><p align="center"><em> From <a href="sql-overview-delete-main-cpp.html">sql/overview/delete/main.cpp</a>
926</em></p>
927</blockquote><p> To delete records, select the record to be deleted and navigate to it.
928Then call primeDelete() to populate the cursor with the primary key
929of the selected record, (in this example, the <tt>prices.id</tt> field), and
930then call <a href="qsqlcursor.html#del">QSqlCursor::del</a>() to delete it.
931<p> As with update(), if multiple deletions need to be made with some common
932criteria it is more efficient to do so using a single SQL statement,
933e.g.
934<p> <pre>
935 <a href="qsqlquery.html">QSqlQuery</a> query( "DELETE FROM prices WHERE id &gt;= 2450 AND id &lt;= 2500" );
936</pre>
937
938<p> <a name="Data-Aware_Widgets"></a>
939<h2> Data-Aware Widgets
940</h2>
941<a name="8"></a><p> Data-Aware Widgets provide a simple yet powerful means of connecting
942databases to Qt user interfaces. The easiest way of creating and
943manipulating data-aware widgets is with <a href="designer-manual.html">Qt
944Designer</a>. For those who prefer a purely programmatic approach
945the following examples and explanations provide an introduction. Note
946that the "Creating Database Applications" chapter of the <a href="designer-manual.html">Qt Designer</a> manual and its accompanying
947examples provides additional information.
948<p> <a name="Data-Aware_Tables"></a>
949<h3> Data-Aware Tables
950</h3>
951<a name="8-1"></a><p>
952
953<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
954 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
955 #include &lt;<a href="qsqlcursor-h.html">qsqlcursor.h</a>&gt;
956 #include &lt;<a href="qdatatable-h.html">qdatatable.h</a>&gt;
957 #include "../connection.h"
958
959 int main( int argc, char *argv[] )
960 {
961 <a href="qapplication.html">QApplication</a> app( argc, argv );
962
963 if ( createConnections() ) {
964 <a href="qsqlcursor.html">QSqlCursor</a> staffCursor( "staff" );
965 <a href="qdatatable.html">QDataTable</a> *staffTable = new <a href="qdatatable.html">QDataTable</a>( &amp;staffCursor, TRUE );
966 app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( staffTable );
967 <a name="x2211"></a> staffTable-&gt;<a href="qdatatable.html#refresh">refresh</a>();
968 staffTable-&gt;<a href="qwidget.html#show">show</a>();
969
970 return app.<a href="qapplication.html#exec">exec</a>();
971 }
972
973 return 0;
974 }
975</pre><blockquote><p align="center"><em> From <a href="sql-overview-table1-main-cpp.html">sql/overview/table1/main.cpp</a>
976</em></p>
977</blockquote><p> Data-Aware tables require the <a href="qdatatable-h.html">qdatatable.h</a> and <a href="qsqlcursor-h.html">qsqlcursor.h</a> header
978files. We create our application object, call createConnections() and
979create the cursor. We create the <a href="qdatatable.html">QDataTable</a> passing it a pointer to
980the cursor, and set the autoPopulate flag to TRUE. Next we make our <a href="qdatatable.html">QDataTable</a> the main widget and call refresh() to populate it with data
981and call show() to make it visible.
982<p> The autoPopulate flag tells the <a href="qdatatable.html">QDataTable</a> whether or nor it should
983create columns based on the cursor. autoPopulate does not affect the
984loading of data into the table; that is achieved by the refresh()
985function.
986<p>
987
988<pre> <a href="qsqlcursor.html">QSqlCursor</a> staffCursor( "staff" );
989 <a href="qdatatable.html">QDataTable</a> *staffTable = new <a href="qdatatable.html">QDataTable</a>( &amp;staffCursor );
990
991 app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( staffTable );
992
993 <a name="x2214"></a> staffTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "forename", "Forename" );
994 staffTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "surname", "Surname" );
995 staffTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "salary", "Annual Salary" );
996
997 <a href="qstringlist.html">QStringList</a> order = QStringList() &lt;&lt; "surname" &lt;&lt; "forename";
998 <a name="x2216"></a> staffTable-&gt;<a href="qdatatable.html#setSort">setSort</a>( order );
999
1000 <a name="x2215"></a> staffTable-&gt;<a href="qdatatable.html#refresh">refresh</a>();
1001 staffTable-&gt;<a href="qwidget.html#show">show</a>();
1002</pre><blockquote><p align="center"><em> From <a href="sql-overview-table2-main-cpp.html">sql/overview/table2/main.cpp</a>
1003</em></p>
1004</blockquote><p> We create an empty <a href="qdatatable.html">QDataTable</a> which we make into our main widget and
1005then we manually add the columns we want in the order we wish them to
1006appear. For each column we specify the field name and optionally a
1007display label.
1008<p> We have also opted to sort the rows in the table; this could also have
1009been achieved by applying the sort to the cursor itself.
1010<p> Once everything is set up we call refresh() to load the data from the
1011database and show() to make the widget visible.
1012<p> QDataTables only retrieve visible rows which (depending on the driver)
1013allows even large tables to be displayed very quickly with minimal
1014memory cost.
1015<p> <a name="Creating_Forms"></a>
1016<h3> Creating Data-Aware Forms
1017</h3>
1018<a name="8-2"></a><p> Creating data-aware forms is more involved than using data-aware
1019tables because we must take care of each field individually. Most of
1020the code below can be automatically generated by <a href="designer-manual.html">Qt Designer</a>. See the <a href="designer-manual.html">Qt Designer</a> manual for more details.
1021<p> <a name="Displaying_a_Record"></a>
1022<h4> Displaying a Record
1023</h4>
1024<a name="8-2-1"></a><p>
1025
1026<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
1027 #include &lt;<a href="qdialog-h.html">qdialog.h</a>&gt;
1028 #include &lt;<a href="qlabel-h.html">qlabel.h</a>&gt;
1029 #include &lt;<a href="qlayout-h.html">qlayout.h</a>&gt;
1030 #include &lt;<a href="qlineedit-h.html">qlineedit.h</a>&gt;
1031 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
1032 #include &lt;<a href="qsqlcursor-h.html">qsqlcursor.h</a>&gt;
1033 #include &lt;<a href="qsqlform-h.html">qsqlform.h</a>&gt;
1034 #include "../connection.h"
1035
1036 class FormDialog : public <a href="qdialog.html">QDialog</a>
1037 {
1038 public:
1039 FormDialog();
1040 };
1041
1042 FormDialog::FormDialog()
1043 {
1044 <a href="qlabel.html">QLabel</a> *forenameLabel = new <a href="qlabel.html">QLabel</a>( "Forename:", this );
1045 <a href="qlabel.html">QLabel</a> *forenameDisplay = new <a href="qlabel.html">QLabel</a>( this );
1046 <a href="qlabel.html">QLabel</a> *surnameLabel = new <a href="qlabel.html">QLabel</a>( "Surname:", this );
1047 <a href="qlabel.html">QLabel</a> *surnameDisplay = new <a href="qlabel.html">QLabel</a>( this );
1048 <a href="qlabel.html">QLabel</a> *salaryLabel = new <a href="qlabel.html">QLabel</a>( "Salary:", this );
1049 <a href="qlineedit.html">QLineEdit</a> *salaryEdit = new <a href="qlineedit.html">QLineEdit</a>( this );
1050
1051 <a href="qgridlayout.html">QGridLayout</a> *grid = new <a href="qgridlayout.html">QGridLayout</a>( this );
1052 <a name="x2221"></a> grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( forenameLabel, 0, 0 );
1053 grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( forenameDisplay, 0, 1 );
1054 grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( surnameLabel, 1, 0 );
1055 grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( surnameDisplay, 1, 1 );
1056 grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( salaryLabel, 2, 0 );
1057 grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( salaryEdit, 2, 1 );
1058 <a name="x2222"></a> grid-&gt;<a href="qlayout.html#activate">activate</a>();
1059
1060 <a href="qsqlcursor.html">QSqlCursor</a> staffCursor( "staff" );
1061 staffCursor.<a href="qsqlcursor.html#select">select</a>();
1062 staffCursor.<a href="qsqlquery.html#next">next</a>();
1063
1064 <a href="qsqlform.html">QSqlForm</a> sqlForm( this );
1065 <a name="x2227"></a><a name="x2223"></a> sqlForm.<a href="qsqlform.html#setRecord">setRecord</a>( staffCursor.<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>() );
1066 <a name="x2225"></a> sqlForm.<a href="qsqlform.html#insert">insert</a>( forenameDisplay, "forename" );
1067 sqlForm.<a href="qsqlform.html#insert">insert</a>( surnameDisplay, "surname" );
1068 sqlForm.<a href="qsqlform.html#insert">insert</a>( salaryEdit, "salary" );
1069 <a name="x2226"></a> sqlForm.<a href="qsqlform.html#readFields">readFields</a>();
1070 }
1071
1072 int main( int argc, char *argv[] )
1073 {
1074 <a href="qapplication.html">QApplication</a> app( argc, argv );
1075
1076 if ( ! createConnections() ) return 1;
1077
1078 FormDialog *formDialog = new FormDialog();
1079 <a name="x2220"></a> formDialog-&gt;<a href="qdialog.html#show">show</a>();
1080 app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( formDialog );
1081
1082 return app.<a href="qapplication.html#exec">exec</a>();
1083 }
1084</pre><blockquote><p align="center"><em> From <a href="sql-overview-form1-main-cpp.html">sql/overview/form1/main.cpp</a>
1085</em></p>
1086</blockquote><p> We include the header files for the widgets that we need. We also
1087include <a href="qsqldatabase-h.html">qsqldatabase.h</a> and <a href="qsqlcursor-h.html">qsqlcursor.h</a> as usual, but we now add
1088<a href="qsqlform-h.html">qsqlform.h</a>.
1089<p> The form will be presented as a dialog so we subclass <a href="qdialog.html">QDialog</a> with
1090our own FormDialog class. We use a <a href="qlineedit.html">QLineEdit</a> for the salary so that
1091the user can change it. All the widgets are laid out using a grid.
1092<p> We create a cursor on the staff table, select all records and move to
1093the first record.
1094<p> Now we create a <a href="qsqlform.html">QSqlForm</a> object and set the <a href="qsqlform.html">QSqlForm</a>'s record buffer
1095to the cursor's update buffer. For each widget that we wish to make
1096data-aware we insert a pointer to the widget and the associated field
1097name into the <a href="qsqlform.html">QSqlForm</a>. Finally we call readFields() to populate the
1098widgets with data from the database via the cursor's buffer.
1099<p> <a name="Displaying_a_Record_in_a_DataForm"></a>
1100<h4> Displaying a Record in a Data Form
1101</h4>
1102<a name="8-2-2"></a><p> <a href="qdataview.html">QDataView</a> is a Widget that can hold a read-only <a href="qsqlform.html">QSqlForm</a>. In
1103addition to <a href="qsqlform.html">QSqlForm</a> it offers the slot refresh( <a href="qsqlrecord.html">QSqlRecord</a> * ) so it
1104can easily be linked together with a <a href="qdatatable.html">QDataTable</a> to display a detailed
1105view of a record:
1106<p> <pre>
1107 connect( myDataTable, SIGNAL( currentChanged( <a href="qsqlrecord.html">QSqlRecord</a>* ) ),
1108 myDataView, SLOT( refresh( <a href="qsqlrecord.html">QSqlRecord</a>* ) ) );
1109</pre>
1110
1111<p> <a name="Editing_a_Record"></a>
1112<h4> Editing a Record
1113</h4>
1114<a name="8-2-3"></a><p> This example is similar to the previous one so we will focus on the
1115differences.
1116<p>
1117
1118<pre> class FormDialog : public <a href="qdialog.html">QDialog</a>
1119 {
1120 <a href="metaobjects.html#Q_OBJECT">Q_OBJECT</a>
1121 public:
1122 FormDialog();
1123 ~FormDialog();
1124 public slots:
1125 void save();
1126 private:
1127 <a href="qsqlcursor.html">QSqlCursor</a> staffCursor;
1128 <a href="qsqlform.html">QSqlForm</a> *sqlForm;
1129 <a href="qsqlindex.html">QSqlIndex</a> idIndex;
1130 };
1131</pre><blockquote><p align="center"><em> From <a href="sql-overview-form2-main-h.html">sql/overview/form2/main.h</a>
1132</em></p>
1133</blockquote><p> The save slot will be used for a button that the user can press to
1134confirm their update. We also hold pointers to the <a href="qsqlcursor.html">QSqlCursor</a> and the
1135<a href="qsqlform.html">QSqlForm</a> since they will need to be accessed outside the constructor.
1136<p>
1137
1138<pre> staffCursor.setTrimmed( "forename", TRUE );
1139 staffCursor.setTrimmed( "surname", TRUE );
1140</pre>
1141<p> We call setTrimmed() on the text fields so that any spaces used to
1142right pad the fields are removed when the fields are retrieved.
1143<p> Properties that we might wish to apply to fields, such as alignment
1144and validation are achieved in the conventional way, for example, by
1145calling <a href="qlineedit.html#setAlignment">QLineEdit::setAlignment</a>() and <a href="qlineedit.html#setValidator">QLineEdit::setValidator</a>().
1146<p> <pre> <a href="qlineedit.html">QLineEdit</a> *forenameEdit = new <a href="qlineedit.html">QLineEdit</a>( this );
1147</pre>
1148<p> <pre> <a href="qpushbutton.html">QPushButton</a> *saveButton = new <a href="qpushbutton.html">QPushButton</a>( "&amp;Save", this );
1149 <a href="qobject.html#connect">connect</a>( saveButton, SIGNAL(<a href="qbutton.html#clicked">clicked</a>()), this, SLOT(save()) );
1150</pre>
1151<p> The FormDialog constructor is similar to the one in the previous
1152example. We have changed the forename and surname widgets to
1153<a href="qlineedit.html">QLineEdit</a>s to make them editable and have added a <a href="qpushbutton.html">QPushButton</a>
1154the user can click to save their updates.
1155<p> <pre> <a name="x2230"></a> grid-&gt;<a href="qgridlayout.html#addWidget">addWidget</a>( saveButton, 3, 0 );
1156</pre>
1157<p> We add an extra row to the grid containing the save button.
1158<p> <pre> idIndex = staffCursor.index( "id" );
1159 staffCursor.select( idIndex );
1160 staffCursor.first();
1161</pre>
1162<p> We create a <a href="qsqlindex.html">QSqlIndex</a> object and then execute a select() using the
1163index. We then move to the first record in the result set.
1164<p> <pre> sqlForm = new <a href="qsqlform.html">QSqlForm</a>( this );
1165 <a name="x2233"></a> sqlForm-&gt;<a href="qsqlform.html#setRecord">setRecord</a>( staffCursor.primeUpdate() );
1166</pre>
1167<p> We create a new <a href="qsqlform.html">QSqlForm</a> object and set it's record buffer to the
1168cursor's update buffer.
1169<p> <pre> <a name="x2231"></a> sqlForm-&gt;<a href="qsqlform.html#insert">insert</a>( forenameEdit, "forename" );
1170 sqlForm-&gt;<a href="qsqlform.html#insert">insert</a>( surnameEdit, "surname" );
1171 sqlForm-&gt;<a href="qsqlform.html#insert">insert</a>( salaryEdit, "salary" );
1172 <a name="x2232"></a> sqlForm-&gt;<a href="qsqlform.html#readFields">readFields</a>();
1173</pre>
1174<p> Now we link the buffer's fields to the <a href="qlineedit.html">QLineEdit</a> controls. (In the
1175previous example we linked the cursor's fields.) The edit controls are
1176populated by the readFields() call as before.
1177<p> <pre> FormDialog::~FormDialog()
1178 {
1179
1180 }
1181</pre>
1182<p> In the destructor we don't have to worry about the widgets or QSqlForm
1183since they are children of the form and will be deleted by Qt at the
1184right time.
1185<p> <pre> void FormDialog::save()
1186 {
1187 <a name="x2234"></a> sqlForm-&gt;<a href="qsqlform.html#writeFields">writeFields</a>();
1188 staffCursor.update();
1189 staffCursor.select( idIndex );
1190 staffCursor.first();
1191 }
1192</pre>
1193<p> Finally we add the save functionality for when the user presses the
1194save button. We write back the data from the widgets to the <a href="qsqlrecord.html">QSqlRecord</a> buffer with the writeFields() call. Then we update the
1195database with the updated version of the record with the cursor's
1196update() function. At this point the cursor is no longer positioned at
1197a valid record so we reissue the select() call using our <a href="qsqlindex.html">QSqlIndex</a>
1198and move to the first record.
1199<p> <a href="qdatabrowser.html">QDataBrowser</a> and <a href="qdataview.html">QDataView</a> are widgets which provide a great deal of
1200the above functionality. <a href="qdatabrowser.html">QDataBrowser</a> provides a data form which
1201allows editing of and navigation through a cursor's records. <a href="qdataview.html">QDataView</a> provides a read only form for data in a cursor or database
1202record. See the class documentation or the <a href="designer-manual.html">Qt Designer</a> manual for more information on using these
1203widgets.
1204<p> Link to <a href="sql.html">sql/overview/form2/main.cpp</a>
1205<p> <a name="Custom_Editor_Widgets"></a>
1206<h3> Custom Editor Widgets
1207</h3>
1208<a name="8-3"></a><p> <a href="qsqlform.html">QSqlForm</a> uses <a href="qsqlpropertymap.html">QSqlPropertyMap</a> to handle the transfer of data between
1209widgets and database fields. Custom widgets can also be used in a form
1210by installing a property map that contains information about the
1211properties of the custom widget which should be used to transfer the
1212data.
1213<p> This example is based on the form2 example in the previous section so
1214we will only cover the differences here. The full source is in <a href="sql-overview-custom1-main-h.html">sql/overview/custom1/main.h</a> and <a href="sql-overview-custom1-main-cpp.html">sql/overview/custom1/main.cpp</a>
1215<p>
1216
1217<pre> class CustomEdit : public <a href="qlineedit.html">QLineEdit</a>
1218 {
1219 Q_OBJECT
1220 Q_PROPERTY( QString upperLine READ upperLine WRITE setUpperLine )
1221 public:
1222 CustomEdit( <a href="qwidget.html">QWidget</a> *parent=0, const char *name=0 );
1223 <a href="qstring.html">QString</a> upperLine() const;
1224 void setUpperLine( const <a href="qstring.html">QString</a> &amp;line );
1225 public slots:
1226 void changed( const <a href="qstring.html">QString</a> &amp;line );
1227 private:
1228 <a href="qstring.html">QString</a> upperLineText;
1229 };
1230</pre>
1231<p> We've created a simple subclass of <a href="qlineedit.html">QLineEdit</a> and added a property,
1232upperLineText, which will hold an uppercase version of the text. We
1233also created a slot, changed().
1234<p> <pre> <a href="qsqlpropertymap.html">QSqlPropertyMap</a> *propMap;
1235</pre>
1236<p> We will be using a property map so we add a pointer to a property map
1237to our FormDialog's private data.
1238<p>
1239
1240<pre> CustomEdit::CustomEdit( <a href="qwidget.html">QWidget</a> *parent, const char *name ) :
1241 <a href="qlineedit.html">QLineEdit</a>( parent, name )
1242 {
1243 <a href="qobject.html#connect">connect</a>( this, SIGNAL(<a href="qlineedit.html#textChanged">textChanged</a>(const <a href="qstring.html">QString</a> &amp;)),
1244 this, SLOT(changed(const <a href="qstring.html">QString</a> &amp;)) );
1245 }
1246</pre>
1247<p> In the CustomEdit constructor we use the QLineEdit constructor and add
1248a connection between the textChanged signal and our own changed slot.
1249<p> <pre> void CustomEdit::changed( const <a href="qstring.html">QString</a> &amp;line )
1250 {
1251 setUpperLine( line );
1252 }
1253</pre>
1254<p> The changed() slot calls our setUpperLine() function.
1255<p> <pre> void CustomEdit::setUpperLine( const <a href="qstring.html">QString</a> &amp;line )
1256 {
1257 <a name="x2240"></a> upperLineText = line.<a href="qstring.html#upper">upper</a>();
1258 setText( upperLineText );
1259 }
1260</pre>
1261<p> The setUpperLine() function places an uppercase copy of the text in the
1262upperLineText buffer and then sets the text of the widget to this text.
1263<p> Our CustomEdit class ensures that the text entered is always uppercase
1264and provides a property that can be used with a property map to link
1265CustomEdit instances directly to database fields.
1266<p> <pre> CustomEdit *forenameEdit = new CustomEdit( this );
1267</pre>
1268<p> <pre> CustomEdit *surnameEdit = new CustomEdit( this );
1269</pre>
1270<p> We use the same FormDialog as we did before, but this time replace two
1271of the <a href="qlineedit.html">QLineEdit</a> widgets with our own CustomEdit widgets.
1272<p> Laying out the grid and setting up the cursor is the same as before.
1273<p> <pre> propMap = new <a href="qsqlpropertymap.html">QSqlPropertyMap</a>;
1274 <a name="x2239"></a><a name="x2235"></a> propMap-&gt;<a href="qsqlpropertymap.html#insert">insert</a>( forenameEdit-&gt;<a href="qobject.html#className">className</a>(), "upperLine" );
1275</pre>
1276<p> We create a new property map on the heap and register our CustomEdit
1277class and its upperLine property with the property map.
1278<p> <pre> sqlForm = new <a href="qsqlform.html">QSqlForm</a>( this );
1279 <a name="x2238"></a><a name="x2236"></a> sqlForm-&gt;<a href="qsqlform.html#setRecord">setRecord</a>( staffCursor-&gt;<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>() );
1280 <a name="x2237"></a> sqlForm-&gt;<a href="qsqlform.html#installPropertyMap">installPropertyMap</a>( propMap );
1281</pre>
1282<p> The final change is to install the property map into the <a href="qsqlform.html">QSqlForm</a> once
1283the QSqlForm has been created. This passes responsibility for the
1284property map's memory to QSqlForm which itself is owned by the
1285FormDialog, so Qt will delete them at the right time.
1286<p> The behaviour of this example is identical to the previous one except
1287that the forename and surname fields will be uppercase since they use
1288our CustomEdit widget.
1289<p> <a name="Custom_Editor_Widgets_for_Tables"></a>
1290<h4> Custom Editor Widgets for Tables
1291</h4>
1292<a name="8-3-1"></a><p> We must reimpliment <a href="qsqleditorfactory.html">QSqlEditorFactory</a> to use custom editor widgets in
1293tables. In the following example we will create a custom editor based
1294on <a href="qcombobox.html">QComboBox</a> and a QSqlEditorFactory subclass to show how a <a href="qdatatable.html">QDataTable</a>
1295can use a custom editor.
1296<p>
1297
1298<pre> class StatusPicker : public <a href="qcombobox.html">QComboBox</a>
1299 {
1300 Q_OBJECT
1301 Q_PROPERTY( int statusid READ statusId WRITE setStatusId )
1302 public:
1303 StatusPicker( <a href="qwidget.html">QWidget</a> *parent=0, const char *name=0 );
1304 int statusId() const;
1305 void setStatusId( int id );
1306 private:
1307 <a href="qmap.html">QMap</a>&lt; int, int &gt; index2id;
1308 };
1309</pre><blockquote><p align="center"><em> From <a href="sql-overview-table3-main-h.html">sql/overview/table3/main.h</a>
1310</em></p>
1311</blockquote><p> We create a property, statusid, and define our READ and WRITE methods
1312for it. The statusid's in the status table will probably be different
1313from the combobox's indexes so we create a <a href="qmap.html">QMap</a> to map combobox indexes
1314to/from the statusids that we will list in the combobox.
1315<p> <pre> class CustomSqlEditorFactory : public <a href="qsqleditorfactory.html">QSqlEditorFactory</a>
1316 {
1317 Q_OBJECT
1318 public:
1319 <a href="qwidget.html">QWidget</a> *createEditor( <a href="qwidget.html">QWidget</a> *parent, const <a href="qsqlfield.html">QSqlField</a> *field );
1320 };
1321</pre>
1322<p> We also need to subclass <a href="qsqleditorfactory.html">QSqlEditorFactory</a> declaring a createEditor()
1323function since that is the only function we need to reimplement.
1324<p>
1325
1326<pre> StatusPicker::StatusPicker( <a href="qwidget.html">QWidget</a> *parent, const char *name )
1327 : <a href="qcombobox.html">QComboBox</a>( parent, name )
1328 {
1329 <a href="qsqlcursor.html">QSqlCursor</a> cur( "status" );
1330 <a name="x2242"></a><a name="x2241"></a> cur.<a href="qsqlcursor.html#select">select</a>( cur.<a href="qsqlcursor.html#index">index</a>( "name" ) );
1331
1332 int i = 0;
1333 while ( cur.<a href="qsqlquery.html#next">next</a>() ) {
1334 <a name="x2243"></a> <a href="qcombobox.html#insertItem">insertItem</a>( cur.<a href="qsqlquery.html#value">value</a>( "name" ).toString(), i );
1335 index2id[i] = cur.<a href="qsqlquery.html#value">value</a>( "id" ).toInt();
1336 i++;
1337 }
1338</pre><blockquote><p align="center"><em> From <a href="sql-overview-table3-main-cpp.html">sql/overview/table3/main.cpp</a>
1339</em></p>
1340</blockquote><p> In the StatusPicker's constructor we create a cursor over the status
1341table indexed by the name field. We then iterate over each record in the
1342status table inserting each name into the combobox. We store the
1343statusid for each name in the index2id <a href="qmap.html">QMap</a> using the same QMap index as
1344the combobox index.
1345<p> <pre> int StatusPicker::statusId() const
1346 {
1347 return index2id[ currentItem() ];
1348 }
1349</pre>
1350<p> The statusid property READ function simply involves looking up the
1351combobox's index for the currently selected item in the index2id QMap
1352which maps combobox indexes to statusids.
1353<p> <pre> void StatusPicker::setStatusId( int statusid )
1354 {
1355 QMap&lt;int,int&gt;::Iterator it;
1356 for ( it = index2id.begin(); it != index2id.end(); ++it ) {
1357 if ( it.data() == statusid ) {
1358 setCurrentItem( it.key() );
1359 break;
1360 }
1361 }
1362 }
1363</pre>
1364<p> The statusId() function implements the statusid property's WRITE
1365function. We create an iterator over a QMap and iterate over the
1366index2id QMap. We compare each index2id element's data (statusid) to
1367the id parameter's value. If we have a match we set the combobox's
1368current item to the index2id element's key (the combobox index), and
1369leave the loop.
1370<p> When the user edits the status field in the <a href="qdatatable.html">QDataTable</a> they will be
1371presented with a combobox of valid status names taken from the status
1372table. However the status displayed is still the raw statusid. To
1373display the status name when the field isn't being edited requires us
1374to subclass QDataTable and reimplement the paintField() function.
1375<p>
1376
1377<pre> class CustomTable : public <a href="qdatatable.html">QDataTable</a>
1378 {
1379 Q_OBJECT
1380 public:
1381 CustomTable(
1382 <a href="qsqlcursor.html">QSqlCursor</a> *cursor, bool autoPopulate = FALSE,
1383 <a href="qwidget.html">QWidget</a> * parent = 0, const char * name = 0 ) :
1384 <a href="qdatatable.html">QDataTable</a>( cursor, autoPopulate, parent, name ) {}
1385 void paintField(
1386 <a href="qpainter.html">QPainter</a> * p, const <a href="qsqlfield.html">QSqlField</a>* field, const <a href="qrect.html">QRect</a> &amp; cr, bool );
1387
1388 };
1389</pre><blockquote><p align="center"><em> From <a href="sql-overview-table4-main-h.html">sql/overview/table4/main.h</a>
1390</em></p>
1391</blockquote><p> We simply call the original <a href="qdatatable.html">QDataTable</a> constructor without changing
1392anything. We also declare the paintField function.
1393<p>
1394
1395<pre> <a name="x2245"></a>void CustomTable::<a href="qdatatable.html#paintField">paintField</a>( <a href="qpainter.html">QPainter</a> * p, const <a href="qsqlfield.html">QSqlField</a>* field,
1396 const <a href="qrect.html">QRect</a> &amp; cr, bool b)
1397 {
1398 if ( !field )
1399 return;
1400 <a name="x2249"></a> if ( field-&gt;<a href="qsqlfield.html#name">name</a>() == "statusid" ) {
1401 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT name FROM status WHERE id=" +
1402 <a name="x2250"></a> field-&gt;<a href="qsqlfield.html#value">value</a>().toString() );
1403 <a href="qstring.html">QString</a> text;
1404 if ( query.<a href="qsqlquery.html#next">next</a>() ) {
1405 <a name="x2252"></a> text = query.<a href="qsqlquery.html#value">value</a>( 0 ).toString();
1406 }
1407 p-&gt;<a href="qpainter.html#drawText">drawText</a>( 2,2, cr.<a href="qrect.html#width">width</a>()-4, cr.<a href="qrect.html#height">height</a>()-4, fieldAlignment( field ), text );
1408 }
1409 else {
1410 QDataTable::<a href="qdatatable.html#paintField">paintField</a>( p, field, cr, b) ;
1411 }
1412</pre><blockquote><p align="center"><em> From <a href="sql-overview-table4-main-cpp.html">sql/overview/table4/main.cpp</a>
1413</em></p>
1414</blockquote><p> The paintField code is based on <a href="qdatatable.html">QDataTable</a>'s source code. We need to
1415make three changes. Firstly add an if clause <tt>field-&gt;name() == "statusid"</tt> and look up the textual value for the id with a
1416straighforward <a href="qsqlquery.html">QSqlQuery</a>. Secondly call the superclass to handle other
1417fields. The last change is in our main function where we change
1418staffTable from being a QDataTable to being a CustomTable.
1419<p> <a name="Subclassing_QSqlCursor"></a>
1420<h2> Subclassing <a href="qsqlcursor.html">QSqlCursor</a>
1421</h2>
1422<a name="9"></a><p>
1423
1424<pre> #include &lt;<a href="qapplication-h.html">qapplication.h</a>&gt;
1425 #include &lt;<a href="qsqldatabase-h.html">qsqldatabase.h</a>&gt;
1426 #include &lt;<a href="qsqlcursor-h.html">qsqlcursor.h</a>&gt;
1427 #include &lt;<a href="qdatatable-h.html">qdatatable.h</a>&gt;
1428 #include "../connection.h"
1429
1430 int main( int argc, char *argv[] )
1431 {
1432 <a href="qapplication.html">QApplication</a> app( argc, argv );
1433
1434 if ( createConnections() ) {
1435 <a href="qsqlcursor.html">QSqlCursor</a> invoiceItemCursor( "invoiceitem" );
1436
1437 <a href="qdatatable.html">QDataTable</a> *invoiceItemTable = new <a href="qdatatable.html">QDataTable</a>( &amp;invoiceItemCursor );
1438
1439 app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( invoiceItemTable );
1440
1441 <a name="x2255"></a> invoiceItemTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "pricesid", "PriceID" );
1442 invoiceItemTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "quantity", "Quantity" );
1443 invoiceItemTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "paiddate", "Paid" );
1444
1445 <a name="x2256"></a> invoiceItemTable-&gt;<a href="qdatatable.html#refresh">refresh</a>();
1446 invoiceItemTable-&gt;<a href="qwidget.html#show">show</a>();
1447
1448 return app.<a href="qapplication.html#exec">exec</a>();
1449 }
1450
1451 return 1;
1452 }
1453</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass1-main-cpp.html">sql/overview/subclass1/main.cpp</a>
1454</em></p>
1455</blockquote><p> This example is very similar to the table1 example presented earlier. We
1456create a cursor, add the fields and their display labels to a <a href="qdatatable.html">QDataTable</a>,
1457call refresh() to load the data and call show() to show the widget.
1458<p> Unfortunately this example is unsatisfactory. It is tedious to set the
1459table name and any custom characteristics for the fields every time we
1460need a cursor over this table. And it would be far better if we
1461displayed the name of the product rather than its pricesid. Since we
1462know the price of the product and the quantity we could also show the
1463product cost and the cost of each invoiceitem. Finally it would be
1464useful (or even essential for primary keys) if we could default some of
1465the values when the user adds a new record.
1466<p>
1467
1468<pre> class InvoiceItemCursor : public <a href="qsqlcursor.html">QSqlCursor</a>
1469 {
1470 public:
1471 InvoiceItemCursor();
1472 };
1473</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass2-main-h.html">sql/overview/subclass2/main.h</a>
1474</em></p>
1475</blockquote><p> We have created a separate header file and subclassed <a href="qsqlcursor.html">QSqlCursor</a>.
1476<p>
1477
1478<pre> InvoiceItemCursor::InvoiceItemCursor() :
1479 <a href="qsqlcursor.html">QSqlCursor</a>( "invoiceitem" )
1480 {
1481 // NOOP
1482 }
1483</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass2-main-cpp.html">sql/overview/subclass2/main.cpp</a>
1484</em></p>
1485</blockquote><p> In our class's constructor we call the QSqlCursor constructor with the
1486name of the table. We don't have any other characteristics to add at
1487this stage.
1488<p> <pre> InvoiceItemCursor invoiceItemCursor;
1489</pre>
1490<p> Whenever we require a cursor over the invoiceitem table we can create
1491an InvoiceItemCursor instead of a generic QSqlCursor.
1492<p> We still need to show the product name rather than the pricesid.
1493<p>
1494
1495<pre> protected:
1496 <a href="qvariant.html">QVariant</a> calculateField( const <a href="qstring.html">QString</a> &amp; name );
1497</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass3-main-h.html">sql/overview/subclass3/main.h</a>
1498</em></p>
1499</blockquote><p> The change in the header file is minimal: we simply add the signature
1500of the calculateField() function since we will be reimplementing it.
1501<p>
1502
1503<pre> InvoiceItemCursor::InvoiceItemCursor() :
1504 <a href="qsqlcursor.html">QSqlCursor</a>( "invoiceitem" )
1505 {
1506 <a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productName( "productname", QVariant::String );
1507 <a href="qsqlcursor.html#append">append</a>( productName );
1508 <a name="x2260"></a> <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
1509 }
1510
1511 <a name="x2259"></a>QVariant InvoiceItemCursor::<a href="qsqlcursor.html#calculateField">calculateField</a>( const <a href="qstring.html">QString</a> &amp; name )
1512 {
1513 if ( name == "productname" ) {
1514 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT name FROM prices WHERE id=" +
1515 <a href="qsqlrecord.html#field">field</a>( "pricesid" )-&gt;value().toString() );
1516 if ( query.<a href="qsqlquery.html#next">next</a>() )
1517 <a name="x2262"></a> return query.<a href="qsqlquery.html#value">value</a>( 0 );
1518 }
1519
1520 return QVariant( <a href="qstring.html#QString-null">QString::null</a> );
1521 }
1522</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass3-main-cpp.html">sql/overview/subclass3/main.cpp</a>
1523</em></p>
1524</blockquote><p> We have changed the InvoiceItemCursor constructor. We now create a new
1525<a href="qsqlfield.html">QSqlField</a> called productname and append this to the
1526InvoiceItemCursor's set of fields. We call setCalculated() on
1527productname to identify it as a calculated field. The first argument
1528to setCalculated() is the field name, the second a bool which if TRUE
1529signifies that calculateField() must be called to get the field's
1530value.
1531<p> <pre> <a name="x2258"></a> invoiceItemTable-&gt;<a href="qdatatable.html#addColumn">addColumn</a>( "productname", "Product" );
1532</pre>
1533<p> We add our new fields with addColumn() which adds them to the form and
1534sets their display names.
1535<p> We have to define our own calculateField() function. In our example
1536database the pricesid in the invoiceitem table is a foreign key into
1537the prices table. We find the name of the product by executing a query
1538on the prices table using the pricesid. This returns the product's
1539name.
1540<p> We are now able to extend the example to include calculated fields
1541which perform real calculations.
1542<p> The header file, <a href="sql-overview-subclass4-main-h.html">sql/overview/subclass4/main.h</a>, remains unchanged
1543from the previous example, but the constructor and calculateField()
1544function require some simple expansion. We'll look at each in turn.
1545<p>
1546
1547<pre> InvoiceItemCursor::InvoiceItemCursor() :
1548 <a href="qsqlcursor.html">QSqlCursor</a>( "invoiceitem" )
1549 {
1550 <a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productName( "productname", QVariant::String );
1551 <a href="qsqlcursor.html#append">append</a>( productName );
1552 <a name="x2264"></a> <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
1553
1554 <a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productPrice( "price", QVariant::Double );
1555 <a href="qsqlcursor.html#append">append</a>( productPrice );
1556 <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productPrice.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
1557
1558 <a href="qsqlfieldinfo.html">QSqlFieldInfo</a> productCost( "cost", QVariant::Double );
1559 <a href="qsqlcursor.html#append">append</a>( productCost );
1560 <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productCost.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
1561 }
1562</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass4-main-cpp.html">sql/overview/subclass4/main.cpp</a>
1563</em></p>
1564</blockquote><p> We create two extra fields, price and cost, and append them to the
1565cursor's set of fields. Both are registered as calculated fields with
1566calls to setCalculated().
1567<p> <pre> <a name="x2263"></a>QVariant InvoiceItemCursor::<a href="qsqlcursor.html#calculateField">calculateField</a>( const <a href="qstring.html">QString</a> &amp; name )
1568 {
1569
1570 if ( name == "productname" ) {
1571 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT name FROM prices WHERE id=" +
1572 <a href="qsqlrecord.html#field">field</a>( "pricesid" )-&gt;value().toString() );
1573 <a name="x2265"></a> if ( query.<a href="qsqlquery.html#next">next</a>() )
1574 <a name="x2266"></a> return query.<a href="qsqlquery.html#value">value</a>( 0 );
1575 }
1576 else if ( name == "price" ) {
1577 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT price FROM prices WHERE id=" +
1578 <a href="qsqlrecord.html#field">field</a>( "pricesid" )-&gt;value().toString() );
1579 if ( query.<a href="qsqlquery.html#next">next</a>() )
1580 return query.<a href="qsqlquery.html#value">value</a>( 0 );
1581 }
1582 else if ( name == "cost" ) {
1583 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT price FROM prices WHERE id=" +
1584 <a href="qsqlrecord.html#field">field</a>( "pricesid" )-&gt;value().toString() );
1585 if ( query.<a href="qsqlquery.html#next">next</a>() )
1586 return QVariant( query.<a href="qsqlquery.html#value">value</a>( 0 ).toDouble() *
1587 <a href="qsqlquery.html#value">value</a>( "quantity").toDouble() );
1588 }
1589
1590 return QVariant( QString::null );
1591 }
1592</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass4-main-cpp.html">sql/overview/subclass4/main.cpp</a>
1593</em></p>
1594</blockquote><p> The calculateField() function has expanded slightly because now we
1595must calculate the value of three different fields. The productname
1596and price fields are produced by looking up the corresponding values
1597in the prices table keyed by pricesid. The cost field is calculated
1598simply by multiplying the price by the quantity. Note that we cast the
1599cost to a <a href="qvariant.html">QVariant</a> since that is the type that calculateField() must
1600return.
1601<p> We've written three separate queries rather than one to make the
1602example more like a real application where it is more likely that each
1603calculated field would be a lookup against a different table or view.
1604<p> The last feature that we need to add is defaulting values when the
1605user attempts to insert a new record.
1606<p>
1607
1608<pre> <a href="qsqlrecord.html">QSqlRecord</a> *primeInsert();
1609</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass5-main-h.html">sql/overview/subclass5/main.h</a>
1610</em></p>
1611</blockquote><p> We declare our own primeInsert() function since we will need to
1612reimplement this.
1613<p> The constructor and the calculateField() function remain unchanged.
1614<p>
1615
1616<pre> <a name="x2267"></a>QSqlRecord *InvoiceItemCursor::<a href="qsqlcursor.html#primeInsert">primeInsert</a>()
1617 {
1618 <a href="qsqlrecord.html">QSqlRecord</a> *buffer = <a href="qsqlcursor.html#editBuffer">editBuffer</a>();
1619 <a href="qsqlquery.html">QSqlQuery</a> query( "SELECT NEXTVAL( 'invoiceitem_seq' )" );
1620 <a name="x2268"></a> if ( query.<a href="qsqlquery.html#next">next</a>() )
1621 <a name="x2270"></a><a name="x2269"></a> buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "id", query.<a href="qsqlquery.html#value">value</a>( 0 ) );
1622 buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "paiddate", QDate::currentDate() );
1623 buffer-&gt;<a href="qsqlrecord.html#setValue">setValue</a>( "quantity", 1 );
1624
1625 return buffer;
1626 }
1627</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass5-main-cpp.html">sql/overview/subclass5/main.cpp</a>
1628</em></p>
1629</blockquote><p> We get a pointer to the internal edit buffer that the cursor uses for
1630inserts and updates. The id field is a unique integer that we generate
1631using the invoiceitem_seq. We default the value of the paiddate field
1632to today's date and default the quantity to 1. Finally we return a
1633pointer to the buffer. The rest of the code is unchanged from the
1634previous version.
1635<p> <a name="Example_Tables"></a>
1636<h2> The Example Tables
1637</h2>
1638<a name="10"></a><p> The example tables used can be recreated with the following standard
1639SQL. You may need to modify the SQL to match that used by your
1640particular database.
1641<p> <pre>
1642create table people (id integer primary key, name char(40))
1643
1644create table staff (id integer primary key, forename char(40),
1645 surname char(40), salary float, statusid integer)
1646
1647create table status (id integer primary key, name char(30))
1648
1649create table creditors (id integer primary key, forename char(40),
1650 surname char(40), city char(30))
1651
1652create table prices (id integer primary key, name char(40), price float)
1653
1654create table invoiceitem (id integer primary key,
1655 pricesid integer, quantity integer,
1656 paiddate date)
1657</pre>
1658
1659<p> A sequence was used in the calculateField() example above. Note that
1660sequences are not supported in all databases.
1661<p> <pre>
1662create sequence invoiceitem_seq
1663</pre>
1664
1665<p>
1666<!-- eof -->
1667<p><address><hr><div align=center>
1668<table width=100% cellspacing=0 border=0><tr>
1669<td>Copyright &copy; 2007
1670<a href="troll.html">Trolltech</a><td align=center><a href="trademarks.html">Trademarks</a>
1671<td align=right><div align=right>Qt 3.3.8</div>
1672</table></div></address></body>
1673</html>
Note: See TracBrowser for help on using the repository browser.