diff options
Diffstat (limited to 'doc/html/sql.html')
| -rw-r--r-- | doc/html/sql.html | 1673 |
1 files changed, 1673 insertions, 0 deletions
diff --git a/doc/html/sql.html b/doc/html/sql.html new file mode 100644 index 000000000..7be337f72 --- /dev/null +++ b/doc/html/sql.html @@ -0,0 +1,1673 @@ +<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> +<!-- /home/espenr/tmp/qt-3.3.8-espenr-2499/qt-x11-free-3.3.8/doc/sql.doc:69 --> +<html> +<head> +<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> +<title>SQL Module</title> +<style type="text/css"><!-- +fn { margin-left: 1cm; text-indent: -1cm; } +a:link { color: #004faf; text-decoration: none } +a:visited { color: #672967; text-decoration: none } +body { background: #ffffff; color: black; } +--></style> +</head> +<body> + +<table border="0" cellpadding="0" cellspacing="0" width="100%"> +<tr bgcolor="#E5E5E5"> +<td valign=center> + <a href="index.html"> +<font color="#004faf">Home</font></a> + | <a href="classes.html"> +<font color="#004faf">All Classes</font></a> + | <a href="mainclasses.html"> +<font color="#004faf">Main Classes</font></a> + | <a href="annotated.html"> +<font color="#004faf">Annotated</font></a> + | <a href="groups.html"> +<font color="#004faf">Grouped Classes</font></a> + | <a href="functions.html"> +<font color="#004faf">Functions</font></a> +</td> +<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> + + +<p> +<p> <center><table cellpadding="4" cellspacing="2" border="0"> +<tr bgcolor="#f0f0f0"> +<td valign="top"><a href="qsql.html">TQSql</a> +<td valign="top"><a href="qsqlcursor.html">TQSqlCursor</a> +<td valign="top"><a href="qsqldatabase.html">TQSqlDatabase</a> +<td valign="top"><a href="qsqldriver.html">TQSqlDriver</a> +<td valign="top"><a href="qsqldriverplugin.html">TQSqlDriverPlugin</a> +<tr bgcolor="#d0d0d0"> +<td valign="top"><a href="qsqleditorfactory.html">TQSqlEditorFactory</a> +<td valign="top"><a href="qsqlerror.html">TQSqlError</a> +<td valign="top"><a href="qsqlfield.html">TQSqlField</a> +<td valign="top"><a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> +<td valign="top"><a href="qsqlform.html">TQSqlForm</a> +<tr bgcolor="#f0f0f0"> +<td valign="top"><a href="qsqlindex.html">TQSqlIndex</a> +<td valign="top"><a href="qsqlpropertymap.html">TQSqlPropertyMap</a> +<td valign="top"><a href="qsqlquery.html">TQSqlQuery</a> +<td valign="top"><a href="qsqlrecord.html">TQSqlRecord</a> +<td valign="top"><a href="qsqlrecordinfo.html">TQSqlRecordInfo</a> +<tr bgcolor="#d0d0d0"> +<td valign="top"><a href="qsqlresult.html">TQSqlResult</a> +<td valign="top"><a href="qsqlselectcursor.html">TQSqlSelectCursor</a> +<td valign="top" colspan="3" rowspan="1"> See also: <a href="sql-driver.html">Supported Drivers</a> +</table></center> +<p> <!-- toc --> +<ul> +<li><a href="#1"> Introduction +</a> +<li><a href="#2"> SQL Module Architecture +</a> +<li><a href="#3"> SQL Driver Plugins +</a> +<li><a href="#4"> Connecting to Databases +</a> +<li><a href="#5"> Connecting to a Single Database +</a> +<ul> +<li><a href="#5-1"> Connecting to Multiple Databases +</a> +</ul> +<li><a href="#6"> Executing SQL Commands Using TQSqlQuery +</a> +<ul> +<li><a href="#6-1"> Transactions +</a> +<li><a href="#6-2"> Basic Browsing +</a> +<li><a href="#6-3"> Basic Data Manipulation +</a> +<li><a href="#6-4"> Navigating Result Sets +</a> +</ul> +<li><a href="#7"> Using TQSqlCursor +</a> +<ul> +<li><a href="#7-1"> Retrieving Records +</a> +<ul> +<li><a href="#7-1-1"> Sorting and Filtering Records +</a> +<li><a href="#7-1-2"> Extracting Data +</a> +</ul> +<li><a href="#7-2"> Manipulating Records +</a> +<ul> +<li><a href="#7-2-1"> Inserting Records +</a> +<li><a href="#7-2-2"> Updating Records +</a> +<li><a href="#7-2-3"> Deleting Records +</a> +</ul> +</ul> +<li><a href="#8"> Data-Aware Widgets +</a> +<ul> +<li><a href="#8-1"> Data-Aware Tables +</a> +<li><a href="#8-2"> Creating Data-Aware Forms +</a> +<ul> +<li><a href="#8-2-1"> Displaying a Record +</a> +<li><a href="#8-2-2"> Displaying a Record in a Data Form +</a> +<li><a href="#8-2-3"> Editing a Record +</a> +</ul> +<li><a href="#8-3"> Custom Editor Widgets +</a> +<ul> +<li><a href="#8-3-1"> Custom Editor Widgets for Tables +</a> +</ul> +</ul> +<li><a href="#9"> Subclassing TQSqlCursor +</a> +<li><a href="#10"> The Example Tables +</a> +</ul> +<!-- endtoc --> + +<p> <a name="Introduction"></a> +<h2> Introduction +</h2> +<a name="1"></a><p> TQt's SQL classes help you provide seamless database integration to +your TQt applications. +<p> <blockquote> +This overview assumes that you have at least a basic knowledge of SQL. +You should be able to understand simple <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt> +and <tt>DELETE</tt> commands. Although the <a href="qsqlcursor.html">TQSqlCursor</a> class provides an +interface to database browsing and editing that does not <em>retquire</em> a +knowledge of SQL, a basic understanding of SQL is highly recommended. A +standard text covering SQL databases is <em>An Introduction to Database Systems (7th ed.)</em> by C. J. Date, ISBN 0201385902. +</blockquote> +<p> Whilst this module overview presents the classes from a purely +programmatic point of view the <a href="designer-manual.html">TQt +Designer</a> manual's "Creating Database Applications" chapter +takes a higher-level approach demonstrating how to set up +master-detail relationships between widgets, perform drilldown and +handle foreign key lookups. +<p> This document is divided into six sections: +<p> <a href="#Architecture">SQL Module Architecture</a>. This describes +how the classes fit together. +<p> <a href="#Connecting_to_Databases">Connecting to Databases</a>. +This section explains how to set up database connections using the <a href="qsqldatabase.html">TQSqlDatabase</a> class. +<p> <a href="#Executing_SQL_commands">Executing SQL Commands</a>. This +section demonstrates how to issue the standard data manipulation +commands, <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt> on tables in +the database (although any valid SQL statement can be sent to the +database). The focus is purely on database interaction using <a href="qsqlquery.html">TQSqlQuery</a>. +<p> <a href="#Using_QSqlCursor">Using Cursors</a>. This section explains +how to use the <a href="qsqlcursor.html">TQSqlCursor</a> class which provides a simpler API than the +raw SQL used with <a href="qsqlquery.html">TQSqlQuery</a>. +<p> <a href="#Data-Aware_Widgets">Data-Aware Widgets</a>. This section shows +how to programmatically link your database to the user interface. In +this section we introduce the <a href="qdatatable.html">TQDataTable</a>, <a href="qsqlform.html">TQSqlForm</a>, <a href="qsqlpropertymap.html">TQSqlPropertyMap</a> and <a href="qsqleditorfactory.html">TQSqlEditorFactory</a> classes and demonstrate how to +use custom data-aware widgets. <a href="designer-manual.html">TQt +Designer</a> provides an easy visual way of achieving the same +thing. See the <a href="designer-manual.html">TQt Designer</a> manual, +<a href="qdatabrowser.html">TQDataBrowser</a> and <a href="qdataview.html">TQDataView</a> for more information. +<p> <a href="#Subclassing_QSqlCursor">Subclassing TQSqlCursor</a>. This +section gives examples of subclassing TQSqlCursor. Subclassing can be +used to provide default and calculated values for fields (such as +auto-numbered primary index fields), and to display calculated data, +e.g. showing names rather than ids of foreign keys. +<p> All the examples in this document use the tables defined in the +<a href="#Example_Tables">Example Tables</a> section. +<p> <a name="Architecture"></a> +<h2> SQL Module Architecture +</h2> +<a name="2"></a><p> The SQL classes are divided into three layers: +<p> <em>User Interface Layer.</em> These classes provide data-aware widgets +that can be connected to tables or views in the database (by using a +<a href="qsqlcursor.html">TQSqlCursor</a> as a data source). End users can interact directly with +these widgets to browse or edit data. <a href="designer-manual.html">TQt +Designer</a> is fully integrated with the SQL classes and can be +used to create data-aware forms. The data-aware widgets can also be +programmed directly with your own C++ code. The classes that support +this layer include <a href="qsqleditorfactory.html">TQSqlEditorFactory</a>, <a href="qsqlform.html">TQSqlForm</a>, <a href="qsqlpropertymap.html">TQSqlPropertyMap</a>, <a href="qdatatable.html">TQDataTable</a>, <a href="qdatabrowser.html">TQDataBrowser</a> and <a href="qdataview.html">TQDataView</a>. +<p> <em>SQL API Layer.</em> These classes provide access to databases. +Connections are made using the <a href="qsqldatabase.html">TQSqlDatabase</a> class. Database +interaction is achieved either by using the <a href="qsqlquery.html">TQSqlQuery</a> class and +executing SQL commands directly or by using the higher level <a href="qsqlcursor.html">TQSqlCursor</a> class which composes SQL commands automatically. In +addition to <a href="qsqldatabase.html">TQSqlDatabase</a>, <a href="qsqlcursor.html">TQSqlCursor</a> and <a href="qsqlquery.html">TQSqlQuery</a>, the SQL +API layer is supported by <a href="qsqlerror.html">TQSqlError</a>, <a href="qsqlfield.html">TQSqlField</a>, <a href="qsqlfieldinfo.html">TQSqlFieldInfo</a>, +<a href="qsqlindex.html">TQSqlIndex</a>, <a href="qsqlrecord.html">TQSqlRecord</a> and <a href="qsqlrecordinfo.html">TQSqlRecordInfo</a>. +<p> <em>Driver Layer.</em> This comprises three classes, <a href="qsqlresult.html">TQSqlResult</a>, <a href="qsqldriver.html">TQSqlDriver</a> and TQSqlDriverFactoryInterface. This layer provides the +low level bridge between the database and the SQL classes. This layer +is <a href="sql-driver.html">documented separately</a> since it is +only relevant to driver writers, and is rarely used in standard +database application programming. See <a href="sql-driver.html">here</a> for more information on implementing a TQt SQL driver plugin. +<p> <a name="Plugins"></a> +<h2> SQL Driver Plugins +</h2> +<a name="3"></a><p> The TQt SQL module can dynamically load new drivers at runtime using +the <a href="plugins-howto.html">Plugins</a>. +<p> The <a href="sql-driver.html">SQL driver documentation</a> describes +how to build plugins for specific database management systems. +<p> Once a plugin is built, TQt will automatically load it, and the driver +will be available for use by <a href="qsqldatabase.html">TQSqlDatabase</a> (see <a href="qsqldatabase.html#drivers">TQSqlDatabase::drivers</a>() +for more information). +<p> <a name="Connecting_to_Databases"></a> +<h2> Connecting to Databases +</h2> +<a name="4"></a><p> At least one database connection must be created and opened before the +<a href="qsqlquery.html">TQSqlQuery</a> or <a href="qsqlcursor.html">TQSqlCursor</a> classes can be used. +<p> If the application only needs a single database connection, the <a href="qsqldatabase.html">TQSqlDatabase</a> class can create a connection which is used by default +for all SQL operations. If multiple database connections are retquired +these can easily be set up. +<p> <a href="qsqldatabase.html">TQSqlDatabase</a> retquires the <a href="qsqldatabase-h.html">qsqldatabase.h</a> header file. +<p> <a name="Connecting_to_a_Single_Database"></a> +<h2> Connecting to a Single Database +</h2> +<a name="5"></a><p> Making a database connection is a simple three step process: activate +the driver, set up the connection information, and open the +connection. +<p> + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include "../connection.h" + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv, FALSE ); + + <a name="x2141"></a> <a href="qsqldatabase.html">TQSqlDatabase</a> *defaultDB = TQSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER ); + <a name="x2143"></a> defaultDB-><a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME ); + <a name="x2146"></a> defaultDB-><a href="qsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER ); + <a name="x2145"></a> defaultDB-><a href="qsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD ); + <a name="x2144"></a> defaultDB-><a href="qsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST ); + + <a name="x2142"></a> if ( defaultDB-><a href="qsqldatabase.html#open">open</a>() ) { + // Database successfully opened; we can now issue SQL commands. + } + + return 0; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-connect1-main-cpp.html">sql/overview/connect1/main.cpp</a> +</em></p> +</blockquote><p> First we activate the driver by calling <a href="qsqldatabase.html#addDatabase">TQSqlDatabase::addDatabase</a>(), +passing the name of the driver we wish to use for this connection. At +the time of writing the available drivers are: <a href="sql-driver.html#TQODBC3">TQODBC3</a> (Open Database +Connectivity, includes Microsoft SQL Server support), <a href="sql-driver.html#TQOCI8">TQOCI8</a> (Oracle 8 and 9), +<a href="sql-driver.html#TQTDS7">TQTDS7</a> (Sybase Adaptive Server), <a href="sql-driver.html#TQPSQL7">TQPSQL7</a> (PostgreSQL 6 and 7), +<a href="sql-driver.html#TQMYSQL3">TQMYSQL3</a> (MySQL), <a href="sql-driver.html#TQDB2">TQDB2</a> (IBM DB2), <a href="sql-driver.html#TQSQLITE">TQSQLITE</a> (SQLite) and <a href="sql-driver.html#TQIBASE">TQIBASE</a> (Interbase). +Note that some of these drivers aren't included in the TQt Open Source Edition; see +the <tt>README</tt> files for details. +<p> The connection which is created becomes the application's default +database connection and will be used by the TQt SQL classes if no +other database is specified. +<p> Second we call setDatabaseName(), setUserName(), setPassword() and +setHostName() to initialize the connection information. Note that for +the TQOCI8 (Oracle 8 and 9) driver the TNS Service Name must be passed +to setDatbaseName(). When connecting to ODBC data sources the Data +Source Name (DSN) should be used in the setDatabaseName() call. +<p> Third we call open() to open the database and give us access to the +data. If this call fails it will return FALSE; error information can +be obtained from <a href="qsqldatabase.html#lastError">TQSqlDatabase::lastError</a>(). +<p> <a name="Connecting_to_Multiple_Databases"></a> +<h3> Connecting to Multiple Databases +</h3> +<a name="5-1"></a><p> Connecting to multiple databases is achieved using the two argument form +of <a href="qsqldatabase.html#addDatabase">TQSqlDatabase::addDatabase</a>() where the second argument is a unique +identifier distinguishing the connection. +<p> In the example below we have moved the connections into their own +function, <tt>createConnections()</tt>, and added some basic error handling. +<p> <pre> +#define DB_SALES_DRIVER "TQPSQL7" +#define DB_SALES_DBNAME "sales" +#define DB_SALES_USER "salesperson" +#define DB_SALES_PASSWD "salesperson" +#define DB_SALES_HOST "database.domain.no" + +#define DB_ORDERS_DRIVER "TQOCI8" +#define DB_ORDERS_DBNAME "orders" +#define DB_ORDERS_USER "orderperson" +#define DB_ORDERS_PASSWD "orderperson" +#define DB_ORDERS_HOST "database.domain.no" + +bool createConnections(); +</pre> + +<p> We set up some constants and also declare the <tt>createConnections()</tt> +function in <tt>connection.h</tt>. +<p> + +<pre> #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include "connection.h" + + bool createConnections() + { + + <a name="x2147"></a> <a href="qsqldatabase.html">TQSqlDatabase</a> *defaultDB = TQSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER ); + <a name="x2150"></a> defaultDB-><a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME ); + <a name="x2153"></a> defaultDB-><a href="qsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER ); + <a name="x2152"></a> defaultDB-><a href="qsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD ); + <a name="x2151"></a> defaultDB-><a href="qsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST ); + <a name="x2149"></a> if ( ! defaultDB-><a href="qsqldatabase.html#open">open</a>() ) { + <a name="x2148"></a> <a href="qapplication.html#qWarning">qWarning</a>( "Failed to open sales database: " + defaultDB-><a href="qsqldatabase.html#lastError">lastError</a>().text() ); + return FALSE; + } + + <a href="qsqldatabase.html">TQSqlDatabase</a> *oracle = TQSqlDatabase::<a href="qsqldatabase.html#addDatabase">addDatabase</a>( DB_ORDERS_DRIVER, "ORACLE" ); + oracle-><a href="qsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_ORDERS_DBNAME ); + oracle-><a href="qsqldatabase.html#setUserName">setUserName</a>( DB_ORDERS_USER ); + oracle-><a href="qsqldatabase.html#setPassword">setPassword</a>( DB_ORDERS_PASSWD ); + oracle-><a href="qsqldatabase.html#setHostName">setHostName</a>( DB_ORDERS_HOST ); + if ( ! oracle-><a href="qsqldatabase.html#open">open</a>() ) { + <a href="qapplication.html#qWarning">qWarning</a>( "Failed to open orders database: " + oracle-><a href="qsqldatabase.html#lastError">lastError</a>().text() ); + return FALSE; + } + + <a href="qsqlquery.html">TQSqlQuery</a> q(TQString::null, defaultDB); + <a name="x2154"></a> q.<a href="qsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))"); + q.<a href="qsqlquery.html#exec">exec</a>("create table staff (id integer primary key, forename char(40), " + "surname char(40), salary float, statusid integer)"); + q.<a href="qsqlquery.html#exec">exec</a>("create table status (id integer primary key, name char(30))"); + q.<a href="qsqlquery.html#exec">exec</a>("create table creditors (id integer primary key, forename char(40), " + "surname char(40), city char(30))"); + q.<a href="qsqlquery.html#exec">exec</a>("create table prices (id integer primary key, name char(40), price float)"); + q.<a href="qsqlquery.html#exec">exec</a>("create table invoiceitem (id integer primary key, " + "pricesid integer, quantity integer, paiddate date)"); + + <a href="qsqlquery.html">TQSqlQuery</a> q2(TQString::null, oracle); + q2.<a href="qsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))"); + + return TRUE; + } +</pre><blockquote><p align="center"><em> From <a href="sql.html">sql/overview/connection.cpp</a> +</em></p> +</blockquote><p> We've chosen to isolate database connection in our <tt>createConnections()</tt> function.cpp. +<p> <a name="create_connections"></a> + + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include "../connection.h" + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv, FALSE ); + + if ( createConnections() ) { + // Databases successfully opened; get pointers to them: + <a name="x2155"></a> <a href="qsqldatabase.html">TQSqlDatabase</a> *oracledb = TQSqlDatabase::<a href="qsqldatabase.html#database">database</a>( "ORACLE" ); + // Now we can now issue SQL commands to the oracle connection + // or to the default connection + } + + return 0; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-create_connections-main-cpp.html">sql/overview/create_connections/main.cpp</a> +</em></p> +</blockquote><p> The static function <a href="qsqldatabase.html#database">TQSqlDatabase::database</a>() can be called from +anywhere to provide a pointer to a database connection. If we call it +without a parameter it will return the default connection. If called +with the identifier we've used for a connection, e.g. "ORACLE", in the +above example, it will return a pointer to the specified connection. +<p> If you create a <tt>main.cpp</tt> using <a href="designer-manual.html">TQt +Designer</a>, it will <em>not</em> include our example +createConnections() function. This means that applications that +preview correctly in <a href="designer-manual.html">TQt Designer</a> +will not run unless you implement your own database connections +function. +<p> Note that in the code above the ODBC connection was not named and is +therefore used as the default connection. <a href="qsqldatabase.html">TQSqlDatabase</a> maintains +ownership of the pointers returned by the addDatabase() static +function. To remove a database from the list of maintained +connections, first close the database with <a href="qsqldatabase.html#close">TQSqlDatabase::close</a>(), and +then remove it using the static function +<a href="qsqldatabase.html#removeDatabase">TQSqlDatabase::removeDatabase</a>(). +<p> <a name="Executing_SQL_commands"></a> +<h2> Executing SQL Commands Using <a href="qsqlquery.html">TQSqlQuery</a> +</h2> +<a name="6"></a><p> The <a href="qsqlquery.html">TQSqlQuery</a> class provides an interface for executing SQL commands. +It also has functions for navigating through the result sets of <tt>SELECT</tt> +queries and for retrieving individual records and field values. +<p> The <a href="qsqlcursor.html">TQSqlCursor</a> class described in the next section inherits from <a href="qsqlquery.html">TQSqlQuery</a> and provides a higher level interface that composes SQL +commands for us. <a href="qsqlcursor.html">TQSqlCursor</a> is particularly easy to integrate with +on-screen widgets. Programmers unfamiliar with SQL can safely skip this +section and use the <a href="qsqlcursor.html">TQSqlCursor</a> class covered in +<a href="#Using_QSqlCursor">"Using TQSqlCursor"</a>. +<p> <a name="Transactions"></a> +<h3> Transactions +</h3> +<a name="6-1"></a><p> If the underlying database engine supports transactions +<a href="qsqldriver.html#hasFeature">TQSqlDriver::hasFeature</a>( TQSqlDriver::Transactions ) will return TRUE. +You can use <a href="qsqldatabase.html#transaction">TQSqlDatabase::transaction</a>() to initiate a transaction, +followed by the SQL commands you want to execute within the context of +the transaction, and then either <a href="qsqldatabase.html#commit">TQSqlDatabase::commit</a>() or +<a href="qsqldatabase.html#rollback">TQSqlDatabase::rollback</a>(). +<p> <a name="Basic_Browsing"></a> +<h3> Basic Browsing +</h3> +<a name="6-2"></a><p> + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include <<a href="qsqlquery-h.html">qsqlquery.h</a>> + #include "../connection.h" + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv, FALSE ); + + if ( createConnections() ) { + <a name="x2156"></a> <a href="qsqldatabase.html">TQSqlDatabase</a> *oracledb = TQSqlDatabase::<a href="qsqldatabase.html#database">database</a>( "ORACLE" ); + // Copy data from the oracle database to the ODBC (default) + // database + <a href="qsqlquery.html">TQSqlQuery</a> target; + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT id, name FROM people", oracledb ); + <a name="x2158"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) { + <a name="x2159"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2157"></a> target.<a href="qsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " + + <a name="x2160"></a> query.<a href="qsqlquery.html#value">value</a>(0).toString() + + ", '" + query.<a href="qsqlquery.html#value">value</a>(1).toString() + "' )" ); + } + } + } + + return 0; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicbrowsing-main-cpp.html">sql/overview/basicbrowsing/main.cpp</a> +</em></p> +</blockquote><p> In the example above we've added an additional header file, +<a href="qsqlquery-h.html">qsqlquery.h</a>. The first query we create, <tt>target</tt>, uses the default +database and is initially empty. For the second query, <tt>q</tt>, we specify +the "ORACLE" database that we want to retrieve records from. Both the +database connections were set up in the createConnections() function we +wrote earlier. +<p> After creating the initial <tt>SELECT</tt> statement, isActive() is checked +to see if the query executed successfully. The next() function is +used to iterate through the query results. The value() function +returns the contents of fields as TQVariants. The insertions are +achieved by creating and executing queries against the default +database using the <tt>target</tt> <a href="qsqlquery.html">TQSqlQuery</a>. +<p> Note that this example and all the other examples in this document use +the tables defined in the <a href="#Example_Tables">Example Tables</a> +section. +<p> + +<pre> int count = 0; + <a name="x2162"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) { + <a name="x2163"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2161"></a> target.<a href="qsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " + + <a name="x2165"></a> query.<a href="qsqlquery.html#value">value</a>(0).toString() + + ", '" + query.<a href="qsqlquery.html#value">value</a>(1).toString() + "' )" ); + if ( target.<a href="qsqlquery.html#isActive">isActive</a>() ) + <a name="x2164"></a> count += target.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>(); + } + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicbrowsing2-main-cpp.html">sql/overview/basicbrowsing2/main.cpp</a> +</em></p> +</blockquote><p> The above code introduces a count of how many records are successfully +inserted. Note that isActive() returns FALSE if the query, e.g. the +insertion, fails. numRowsAffected() returns -1 if the number of rows +cannot be determined, e.g. if the query fails. +<p> <a name="Basic_Data_Manipulation"></a> +<h3> Basic Data Manipulation +</h3> +<a name="6-3"></a><p> + +<pre> ** $Id: qt/main.cpp 3.3.8 edited Jan 11 14:37 $ + ** + ** Copyright (C) 1992-2007 Trolltech ASA. All rights reserved. + ** + ** This file is part of an example program for TQt. This example + ** program may be used, distributed and modified without limitation. + ** + *****************************************************************************/ + + #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include <<a href="qsqlquery-h.html">qsqlquery.h</a>> + #include "../connection.h" + + bool createConnections(); + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv, FALSE ); + + int rows = 0; + + if ( createConnections() ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "INSERT INTO staff ( id, forename, surname, salary ) " + "VALUES ( 1155, 'Ginger', 'Davis', 50000 )" ); + <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>() ; + + <a name="x2166"></a> query.<a href="qsqlquery.html#exec">exec</a>( "UPDATE staff SET salary=60000 WHERE id=1155" ); + if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ; + + query.<a href="qsqlquery.html#exec">exec</a>( "DELETE FROM staff WHERE id=1155" ); + if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="qsqlquery.html#numRowsAffected">numRowsAffected</a>() ; + } + + return ( rows == 3 ) ? 0 : 1; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-basicdatamanip-main-cpp.html">sql/overview/basicdatamanip/main.cpp</a> +</em></p> +</blockquote><p> This example demonstrates straightforward SQL DML (data manipulation +language) commands. Since we did not specify a database in the <a href="qsqlquery.html">TQSqlQuery</a> constructor the default database is used. <a href="qsqlquery.html">TQSqlQuery</a> objects +can also be used to execute SQL DDL (data definition language) commands +such as <tt>CREATE TABLE</tt> and <tt>CREATE INDEX</tt>. +<p> <a name="Navigating_Result_Sets"></a> +<h3> Navigating Result Sets +</h3> +<a name="6-4"></a><p> Once a <tt>SELECT</tt> query has been executed successfully we have access +to the result set of records that matched the query criteria. We have +already used one of the navigation functions, next(), which can be +used alone to step sequentially through the records. <a href="qsqlquery.html">TQSqlQuery</a> also +provides first(), last() and prev(). After any of these commands we +can check that we are on a valid record by calling isValid(). +<p> We can also navigate to any arbitrary record using seek(). The +first record in the dataset is zero. The number of the last record is +size() - 1. Note that not all databases provide the size of a +<tt>SELECT</tt> query and in such cases size() returns -1. +<p> + +<pre> if ( createConnections() ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT id, name FROM people ORDER BY name" ); + <a name="x2171"></a> if ( ! query.<a href="qsqlquery.html#isActive">isActive</a>() ) return 1; // Query failed + int i; + <a name="x2174"></a> i = query.<a href="qsqlquery.html#size">size</a>(); // In this example we have 9 records; i == 9. + <a name="x2170"></a> query.<a href="qsqlquery.html#first">first</a>(); // Moves to the first record. + <a name="x2169"></a> i = query.<a href="qsqlquery.html#at">at</a>(); // i == 0 + <a name="x2172"></a> query.<a href="qsqlquery.html#last">last</a>(); // Moves to the last record. + i = query.<a href="qsqlquery.html#at">at</a>(); // i == 8 + <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. + i = query.<a href="qsqlquery.html#at">at</a>(); // i == 4 + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-navigating-main-cpp.html">sql/overview/navigating/main.cpp</a> +</em></p> +</blockquote><p> The example above shows some of the navigation functions in use. +<p> Not all drivers support size(), but we can interrogate the driver to +find out: +<p> <pre> + <a href="qsqldatabase.html">TQSqlDatabase</a>* defaultDB = TQSqlDatabase::<a href="qsqldatabase.html#database">database</a>(); + if ( defaultDB-><a href="qsqldatabase.html#driver">driver</a>()->hasFeature( TQSqlDriver::QuerySize ) ) { + // TQSqlQuery::size() supported + } + else { + // TQSqlQuery::size() cannot be relied upon + } +</pre> + +<p> Once we have located the record we are interested in we may wish to +retrieve data from it. +<p> + +<pre> if ( createConnections() ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT id, surname FROM staff" ); + <a name="x2175"></a> if ( query.<a href="qsqlquery.html#isActive">isActive</a>() ) { + <a name="x2176"></a> while ( query.<a href="qsqlquery.html#next">next</a>() ) { + <a href="qapplication.html#qDebug">qDebug</a>( query.<a href="qsqlquery.html#value">value</a>(0).toString() + ": " + + <a name="x2177"></a> query.<a href="qsqlquery.html#value">value</a>(1).toString() ); + } + } + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-retrieve1-main-cpp.html">sql/overview/retrieve1/main.cpp</a> +</em></p> +</blockquote><p> Note that if you wish to iterate through the record set in order the +only navigation function you need is next(). +<p> Tip: The lastQuery() function returns the text of the last query +executed. This can be useful to check that the query you think is being +executed is the one actually being executed. +<p> <a name="Using_QSqlCursor"></a> +<h2> Using <a href="qsqlcursor.html">TQSqlCursor</a> +</h2> +<a name="7"></a><p> The <a href="qsqlcursor.html">TQSqlCursor</a> class provides a high level interface to browsing and +editing records in SQL database tables or views without the need to +write your own SQL. +<p> TQSqlCursor can do almost everything that <a href="qsqlquery.html">TQSqlQuery</a> can, with two +exceptions. Since cursors represent tables or views within the +database, by default, <a href="qsqlcursor.html">TQSqlCursor</a> objects retrieve all the fields of +each record in the table or view whenever navigating to a new +record. If only some fields are relevant simply confine your +processing to those and ignore the others. Or, manually disable the +generation of certain fields using <a href="qsqlrecord.html#setGenerated">TQSqlRecord::setGenerated</a>(). Another +approach is to create a <tt>VIEW</tt> which only presents the fields you're +interested in; but note that some databases do not support editable +views. So if you really don't want to retrieve all the fields in the +cursor, then you should use a <a href="qsqlquery.html">TQSqlQuery</a> instead, and customize the +query to suit your needs. You can edit records using a <a href="qsqlcursor.html">TQSqlCursor</a> +providing that the table or view has a primary index that uniquely +distinguishes each record. If this condition is not met then you'll +need to use a <a href="qsqlquery.html">TQSqlQuery</a> for edits. +<p> <a href="qsqlcursor.html">TQSqlCursor</a> operates on a single record at a time. Whenever performing +an insert, update or delete using TQSqlCursor, only a single record in +the database is affected. When navigating through records in the +cursor, only one record at a time is available in application code. +In addition, TQSqlCursor maintains a separate 'edit buffer' which is +used to make changes to a single record in the database. The edit +buffer is maintained in a separate memory area, and is unnaffected by +the 'navigation buffer' which changes as the cursor moves from record +to record. +<p> Before we can use <a href="qsqlcursor.html">TQSqlCursor</a> objects we must first create and open +a database connection. Connecting is described in the <a href="#Connecting_to_Databases">Connecting to Databases</a> section +above. For the examples that follow we will assume that the +connections have been created using the createConnections() function +defined in the <a href="#create_connections">TQSqlDatabase example</a> +presented earlier. +<p> In the <a href="#Data-Aware_Widgets">data-aware widgets</a> section that +follows this one we show how to link widgets to database cursors. Once +we have a knowledge of both cursors and data-aware widgets we can +discuss <a href="#Subclassing_QSqlCursor">subclassing TQSqlCursor</a>. +<p> The <a href="qsqlcursor.html">TQSqlCursor</a> class retquires the <a href="qsqlcursor-h.html">qsqlcursor.h</a> header file. +<p> <a name="Retrieving_Records"></a> +<h3> Retrieving Records +</h3> +<a name="7-1"></a><p> + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>> + #include "../connection.h" + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv ); + + if ( createConnections() ) { + <a href="qsqlcursor.html">TQSqlCursor</a> cur( "staff" ); // Specify the table/view name + <a name="x2178"></a> cur.<a href="qsqlcursor.html#select">select</a>(); // We'll retrieve every record + while ( cur.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2179"></a> <a href="qapplication.html#qDebug">qDebug</a>( cur.<a href="qsqlquery.html#value">value</a>( "id" ).toString() + ": " + + cur.<a href="qsqlquery.html#value">value</a>( "surname" ).toString() + " " + + cur.<a href="qsqlquery.html#value">value</a>( "salary" ).toString() ); + } + } + + return 0; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-retrieve2-main-cpp.html">sql/overview/retrieve2/main.cpp</a> +</em></p> +</blockquote><p> We create the <a href="qsqlcursor.html">TQSqlCursor</a> object, specifying the table or view to use. +If we need to use a database other than the default we can specify it +in the <a href="qsqlcursor.html">TQSqlCursor</a> constructor. +<p> The SQL executed by the cur.select() call is +<p> <pre> + SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid FROM staff +</pre> + +<p> Next, we iterate through the records returned by this select statement +using cur.next(). Field values are retrieved in in a similar way to +<a href="qsqlquery.html">TQSqlQuery</a>, except that we pass field names rather than numeric indexes +to value() and setValue(). +<p> <a name="Sorting_Data"></a> +<h4> Sorting and Filtering Records +</h4> +<a name="7-1-1"></a><p> To specify a subset of records to retrieve we can pass filtering +criteria to the select() function. Each record that is returned will +meet the criteria of the filter (the filter corresponds to the SQL +statement's <tt>WHERE</tt> clause). +<p> <pre> + cur.select( "id > 100" ); +</pre> + +<p> This select() call will execute the SQL +<pre> + SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid + FROM staff WHERE staff.id > 100 +</pre> + +<p> This will retrieve only those staff whose <tt>id</tt> is greater than 100. +<p> In addition to retrieving selected records we often want to specify a +sort order for the returned records. This is achieved by creating a <a href="qsqlindex.html">TQSqlIndex</a> object which contains the names of the field(s) we wish to +sort by and pass this object to the select() call. +<p> <pre> + <a href="qsqlcursor.html">TQSqlCursor</a> cur( "staff" ); + <a href="qsqlindex.html">TQSqlIndex</a> nameIndex = cur.<a href="qsqlcursor.html#index">index</a>( "surname" ); + cur.<a href="qsqlcursor.html#select">select</a>( nameIndex ); +</pre> + +<p> Here we create a <a href="qsqlindex.html">TQSqlIndex</a> object with one field, "surname". When +we call the select() function we pass the index object, which +specifies that the records should be returned sorted by +staff.surname. Each field in the index object is used in the ORDER BY +clause of the select statement. The SQL executed here is +<pre> + SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid + FROM staff ORDER BY staff.surname ASC +</pre> + +<p> Combining the retrieval of a subset of records and ordering the results +is straightforward. +<p> <pre> + cur.select( "staff.surname LIKE 'A%'", nameIndex ); +</pre> + +<p> We pass in a filter string (the <tt>WHERE</tt> clause), and the <a href="qsqlindex.html">TQSqlIndex</a> +object to sort by (the <tt>ORDER BY</tt> clause). This produces +<p> <pre> + SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid + FROM staff WHERE staff.surname LIKE 'A%' ORDER BY staff.surname ASC +</pre> + +<p> To sort by more than one field, an index can be created which contains +multiple fields. Ascending and descending order can be set using +<a href="qsqlindex.html#setDescending">TQSqlIndex::setDescending</a>(); the default is ascending. +<p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> cur( "staff" ); + <a href="qstringlist.html">TQStringList</a> fields = TQStringList() << "surname" << "forename"; + <a name="x2181"></a> <a href="qsqlindex.html">TQSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( fields ); + <a name="x2182"></a> cur.<a href="qsqlcursor.html#select">select</a>( order ); + <a name="x2183"></a> while ( cur.<a href="qsqlquery.html#next">next</a>() ) { +</pre><blockquote><p align="center"><em> From <a href="sql-overview-order1-main-cpp.html">sql/overview/order1/main.cpp</a> +</em></p> +</blockquote><p> Here we create a string list containing the fields we wish to sort by, +in the order they are to be used. Then we create a <a href="qsqlindex.html">TQSqlIndex</a> object +based on these fields, finally executing the select() call using this +index. This executes +<pre> + SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid + FROM staff ORDER BY staff.surname ASC, staff.forename ASC +</pre> + +<p> If we need to retrieve records with fields that match specific criteria we +can create a filter based on an index. +<p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> cur( "staff" ); + <a href="qstringlist.html">TQStringList</a> fields = TQStringList() << "id" << "forename"; + <a name="x2184"></a> <a href="qsqlindex.html">TQSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( fields ); + <a href="qsqlindex.html">TQSqlIndex</a> filter = cur.<a href="qsqlcursor.html#index">index</a>( "surname" ); + <a name="x2186"></a> cur.<a href="qsqlrecord.html#setValue">setValue</a>( "surname", "Bloggs" ); + <a name="x2185"></a> cur.<a href="qsqlcursor.html#select">select</a>( filter, order ); + while ( cur.<a href="qsqlquery.html#next">next</a>() ) { +</pre><blockquote><p align="center"><em> From <a href="sql-overview-order2-main-cpp.html">sql/overview/order2/main.cpp</a> +</em></p> +</blockquote><p> This executes +<pre> + SELECT staff.id, staff.forename, staff.surname, staff.salary, staff.statusid + FROM staff WHERE staff.surname='Bloggs' ORDER BY staff.id ASC, staff.forename ASC +</pre> + +<p> The "order" <a href="qsqlindex.html">TQSqlIndex</a> contains two fields, "id" and "forename" +which are used to order the results. The "filter" <a href="qsqlindex.html">TQSqlIndex</a> +contains a single field, "surname". When an index is passed as a +filter to the select() function, for each field in the filter, a +<em>fieldname=value</em> subclause is created where the value +is taken from the current cursor's value for that field. We use +setValue() to ensure that the value used is the one we want. +<p> <a name="Extracting_Data"></a> +<h4> Extracting Data +</h4> +<a name="7-1-2"></a><p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> cur( "creditors" ); + <a href="qstringlist.html">TQStringList</a> orderFields = TQStringList() << "surname" << "forename"; + <a name="x2188"></a> <a href="qsqlindex.html">TQSqlIndex</a> order = cur.<a href="qsqlcursor.html#index">index</a>( orderFields ); + + <a href="qstringlist.html">TQStringList</a> filterFields = TQStringList() << "surname" << "city"; + <a href="qsqlindex.html">TQSqlIndex</a> filter = cur.<a href="qsqlcursor.html#index">index</a>( filterFields ); + <a name="x2190"></a> cur.<a href="qsqlrecord.html#setValue">setValue</a>( "surname", "Chirac" ); + cur.<a href="qsqlrecord.html#setValue">setValue</a>( "city", "Paris" ); + + <a name="x2189"></a> cur.<a href="qsqlcursor.html#select">select</a>( filter, order ); + + while ( cur.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2191"></a> int id = cur.<a href="qsqlquery.html#value">value</a>( "id" ).toInt(); + <a href="qstring.html">TQString</a> name = cur.<a href="qsqlquery.html#value">value</a>( "forename" ).toString() + " " + + cur.<a href="qsqlquery.html#value">value</a>( "surname" ).toString(); + <a name="x2193"></a> <a href="qapplication.html#qDebug">qDebug</a>( TQString::<a href="qstring.html#number">number</a>( id ) + ": " + name ); + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-extract-main-cpp.html">sql/overview/extract/main.cpp</a> +</em></p> +</blockquote><p> In this example we begin by creating a cursor on the creditors table. +We create two <a href="qsqlindex.html">TQSqlIndex</a> objects. The first, "order", is created +from the "orderFields" string list. The second, "filter", is created +from the "filterFields" string list. We set the values of the two +fields used in the filter, "surname" and "city", to the values we're +interested in. Now we call select() which generates and executes the +following SQL: +<pre> + SELECT creditors.city, creditors.surname, creditors.forename, creditors.id + FROM creditors + WHERE creditors.surname = 'Chirac' AND creditors.city = 'Paris' + ORDER BY creditors.surname ASC, creditors.forename ASC +</pre> + +The filter fields are used in the <tt>WHERE</tt> clause. Their values are +taken from the cursor's current values for those fields; we set these +values ourselves with the setValue() calls. The order fields are used +in the <tt>ORDER BY</tt> clause. +<p> Now we iterate through each matching record (if any). We retrieve the +contents of the id, forename and surname fields and pass them on to +some processing function, in this example a simple <a href="qapplication.html#qDebug">qDebug</a>() call. +<p> <a name="Manipulating_Records"></a> +<h3> Manipulating Records +</h3> +<a name="7-2"></a><p> Records can be inserted, updated or deleted in a table or view using a +<a href="qsqlcursor.html">TQSqlCursor</a> providing that the table or view has a primary index +that uniquely distinguishes each record. If this is not the case a <a href="qsqlquery.html">TQSqlQuery</a> must be used instead. (Note that not all databases support +editable views.) +<p> Each cursor has an internal 'edit buffer' which is used by all the +edit operations (insert, update and delete). The editing process is +the same for each operation: actquire a pointer to the relevant buffer; +call setValue() to <a href="primes.html#prime">prime</a> the buffer with the values you want; call +insert() or update() or del() to perform the desired operation. For +example, when inserting a record using a cursor, you call +primeInsert() to get a pointer to the edit buffer and then call +setValue() on this buffer to set each field's value. Then you call +TQSQlCursor::insert() to insert the contents of the edit buffer into +the database. Similarly, when updating (or deleting) a record, the +values of the fields in the edit buffer are used to update (or delete) +the record in the database. The 'edit buffer' is unaffected by any +<a href="#Navigating_Result_Sets">cursor navigation</a> functions. +Note that if you pass a string value to setValue() any single quotes +will be escaped (turned into a pair of single quotes) since a single +quote is a special character in SQL. +<p> The primeInsert(), primeUpdate() and primeDelete() methods all return +a pointer to the internal edit buffer. Each method can potentially +perform different operations on the edit buffer before returning it. +By default, <a href="qsqlcursor.html#primeInsert">TQSqlCursor::primeInsert</a>() clears all the field values in +the edit buffer (see <a href="qsqlrecord.html#clearValues">TQSqlRecord::clearValues</a>()). Both <a href="qsqlcursor.html#primeUpdate">TQSqlCursor::primeUpdate</a>() and <a href="qsqlcursor.html#primeDelete">TQSqlCursor::primeDelete</a>() initialize the +edit buffer with the current contents of the cursor before returning +it. All three of these functions are virtual, so you can redefine the +behavior (for example, reimplementing primeInsert() to auto-number +fields in the edit buffer). Data-aware user-interface controls emit +signals, e.g. primeInsert(), that you can connect to; these pass a +pointer to the appropriate buffer so subclassing may not be necessary. +See <a href="#Subclassing_QSqlCursor">subclassing TQSqlCursor</a> for +more information on subclassing; see the <a href="designer-manual.html">TQt +Designer</a> manual for more on connecting to the primeInsert() +signal. +<p> When insert(), update() or del() is called on a cursor, it will be +invalidated and will no longer be positioned on a valid record. If you +need to move to another record after performing an insert(), update() +or del() you must make a fresh select() call. This ensures that +changes to the database are accurately reflected in the cursor. +<p> <a name="Inserting_Records"></a> +<h4> Inserting Records +</h4> +<a name="7-2-1"></a><p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> cur( "prices" ); + <a href="qstringlist.html">TQStringList</a> names = TQStringList() << + "Screwdriver" << "Hammer" << "Wrench" << "Saw"; + int id = 20; + <a name="x2197"></a> for ( TQStringList::Iterator name = names.<a href="qvaluelist.html#begin">begin</a>(); + <a name="x2198"></a> name != names.<a href="qvaluelist.html#end">end</a>(); ++name ) { + <a name="x2195"></a> <a href="qsqlrecord.html">TQSqlRecord</a> *buffer = cur.<a href="qsqlcursor.html#primeInsert">primeInsert</a>(); + <a name="x2196"></a> buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "id", id ); + buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "name", *name ); + buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "price", 100.0 + (double)id ); + <a name="x2194"></a> count += cur.<a href="qsqlcursor.html#insert">insert</a>(); + id++; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-insert-main-cpp.html">sql/overview/insert/main.cpp</a> +</em></p> +</blockquote><p> In this example we create a cursor on the "prices" table. Next we +create a list of product names which we iterate over. For each +iteration we call the cursor's primeInsert() method. This method +returns a pointer to a <a href="qsqlrecord.html">TQSqlRecord</a> buffer in which all the fields +are set to <tt>NULL</tt>. (Note that <a href="qsqlcursor.html#primeInsert">TQSqlCursor::primeInsert</a>() is virtual, +and can be customized by derived classes. See <a href="qsqlcursor.html">TQSqlCursor</a>). Next we +call setValue() for each field that retquires a value. Finally we call +insert() to insert the record. The insert() call returns the number of +rows inserted. +<p> We obtained a pointer to a <a href="qsqlrecord.html">TQSqlRecord</a> object from the primeInsert() +call. <a href="qsqlrecord.html">TQSqlRecord</a> objects can hold the data for a single record plus some +meta-data about the record. In practice most interaction with a +TQSqlRecord consists of simple value() and setValue() calls as shown in +this and the following example. +<p> <a name="Updating_Records"></a> +<h4> Updating Records +</h4> +<a name="7-2-2"></a><p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> cur( "prices" ); + <a name="x2200"></a> cur.<a href="qsqlcursor.html#select">select</a>( "id=202" ); + if ( cur.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2199"></a> <a href="qsqlrecord.html">TQSqlRecord</a> *buffer = cur.<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>(); + <a name="x2204"></a> double price = buffer-><a href="qsqlrecord.html#value">value</a>( "price" ).toDouble(); + double newprice = price * 1.05; + <a name="x2203"></a> buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "price", newprice ); + <a name="x2201"></a> cur.<a href="qsqlcursor.html#update">update</a>(); + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-update-main-cpp.html">sql/overview/update/main.cpp</a> +</em></p> +</blockquote><p> This example begins with the creation of a cursor over the prices table. +We select the record we wish to update with the select() call and +move to it with the next() call. We call primeUpdate() to get a <a href="qsqlrecord.html">TQSqlRecord</a> pointer to a buffer which is populated with the contents of +the current record. We retrieve the value of the price field, calculate +a new price, and set the the price field to the newly calculated value. +Finally we call update() to update the record. The update() call returns +the number of rows updated. +<p> If many identical updates need to be performed, for example increasing +the price of every item in the price list, using a single SQL statement +with <a href="qsqlquery.html">TQSqlQuery</a> is more efficient, e.g. +<p> <pre> + <a href="qsqlquery.html">TQSqlQuery</a> query( "UPDATE prices SET price = price * 1.05" ); +</pre> + +<p> <a name="Deleting_Records"></a> +<h4> Deleting Records +</h4> +<a name="7-2-3"></a><p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> cur( "prices" ); + <a name="x2207"></a> cur.<a href="qsqlcursor.html#select">select</a>( "id=999" ); + <a name="x2208"></a> if ( cur.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2206"></a> cur.<a href="qsqlcursor.html#primeDelete">primeDelete</a>(); + <a name="x2205"></a> cur.<a href="qsqlcursor.html#del">del</a>(); +</pre><blockquote><p align="center"><em> From <a href="sql-overview-delete-main-cpp.html">sql/overview/delete/main.cpp</a> +</em></p> +</blockquote><p> To delete records, select the record to be deleted and navigate to it. +Then call primeDelete() to populate the cursor with the primary key +of the selected record, (in this example, the <tt>prices.id</tt> field), and +then call <a href="qsqlcursor.html#del">TQSqlCursor::del</a>() to delete it. +<p> As with update(), if multiple deletions need to be made with some common +criteria it is more efficient to do so using a single SQL statement, +e.g. +<p> <pre> + <a href="qsqlquery.html">TQSqlQuery</a> query( "DELETE FROM prices WHERE id >= 2450 AND id <= 2500" ); +</pre> + +<p> <a name="Data-Aware_Widgets"></a> +<h2> Data-Aware Widgets +</h2> +<a name="8"></a><p> Data-Aware Widgets provide a simple yet powerful means of connecting +databases to TQt user interfaces. The easiest way of creating and +manipulating data-aware widgets is with <a href="designer-manual.html">TQt +Designer</a>. For those who prefer a purely programmatic approach +the following examples and explanations provide an introduction. Note +that the "Creating Database Applications" chapter of the <a href="designer-manual.html">TQt Designer</a> manual and its accompanying +examples provides additional information. +<p> <a name="Data-Aware_Tables"></a> +<h3> Data-Aware Tables +</h3> +<a name="8-1"></a><p> + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>> + #include <<a href="qdatatable-h.html">qdatatable.h</a>> + #include "../connection.h" + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv ); + + if ( createConnections() ) { + <a href="qsqlcursor.html">TQSqlCursor</a> staffCursor( "staff" ); + <a href="qdatatable.html">TQDataTable</a> *staffTable = new <a href="qdatatable.html">TQDataTable</a>( &staffCursor, TRUE ); + app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( staffTable ); + <a name="x2211"></a> staffTable-><a href="qdatatable.html#refresh">refresh</a>(); + staffTable-><a href="qwidget.html#show">show</a>(); + + return app.<a href="qapplication.html#exec">exec</a>(); + } + + return 0; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-table1-main-cpp.html">sql/overview/table1/main.cpp</a> +</em></p> +</blockquote><p> Data-Aware tables retquire the <a href="qdatatable-h.html">qdatatable.h</a> and <a href="qsqlcursor-h.html">qsqlcursor.h</a> header +files. We create our application object, call createConnections() and +create the cursor. We create the <a href="qdatatable.html">TQDataTable</a> passing it a pointer to +the cursor, and set the autoPopulate flag to TRUE. Next we make our <a href="qdatatable.html">TQDataTable</a> the main widget and call refresh() to populate it with data +and call show() to make it visible. +<p> The autoPopulate flag tells the <a href="qdatatable.html">TQDataTable</a> whether or nor it should +create columns based on the cursor. autoPopulate does not affect the +loading of data into the table; that is achieved by the refresh() +function. +<p> + +<pre> <a href="qsqlcursor.html">TQSqlCursor</a> staffCursor( "staff" ); + <a href="qdatatable.html">TQDataTable</a> *staffTable = new <a href="qdatatable.html">TQDataTable</a>( &staffCursor ); + + app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( staffTable ); + + <a name="x2214"></a> staffTable-><a href="qdatatable.html#addColumn">addColumn</a>( "forename", "Forename" ); + staffTable-><a href="qdatatable.html#addColumn">addColumn</a>( "surname", "Surname" ); + staffTable-><a href="qdatatable.html#addColumn">addColumn</a>( "salary", "Annual Salary" ); + + <a href="qstringlist.html">TQStringList</a> order = TQStringList() << "surname" << "forename"; + <a name="x2216"></a> staffTable-><a href="qdatatable.html#setSort">setSort</a>( order ); + + <a name="x2215"></a> staffTable-><a href="qdatatable.html#refresh">refresh</a>(); + staffTable-><a href="qwidget.html#show">show</a>(); +</pre><blockquote><p align="center"><em> From <a href="sql-overview-table2-main-cpp.html">sql/overview/table2/main.cpp</a> +</em></p> +</blockquote><p> We create an empty <a href="qdatatable.html">TQDataTable</a> which we make into our main widget and +then we manually add the columns we want in the order we wish them to +appear. For each column we specify the field name and optionally a +display label. +<p> We have also opted to sort the rows in the table; this could also have +been achieved by applying the sort to the cursor itself. +<p> Once everything is set up we call refresh() to load the data from the +database and show() to make the widget visible. +<p> TQDataTables only retrieve visible rows which (depending on the driver) +allows even large tables to be displayed very tquickly with minimal +memory cost. +<p> <a name="Creating_Forms"></a> +<h3> Creating Data-Aware Forms +</h3> +<a name="8-2"></a><p> Creating data-aware forms is more involved than using data-aware +tables because we must take care of each field individually. Most of +the code below can be automatically generated by <a href="designer-manual.html">TQt Designer</a>. See the <a href="designer-manual.html">TQt Designer</a> manual for more details. +<p> <a name="Displaying_a_Record"></a> +<h4> Displaying a Record +</h4> +<a name="8-2-1"></a><p> + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qdialog-h.html">qdialog.h</a>> + #include <<a href="qlabel-h.html">qlabel.h</a>> + #include <<a href="qlayout-h.html">qlayout.h</a>> + #include <<a href="qlineedit-h.html">qlineedit.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>> + #include <<a href="qsqlform-h.html">qsqlform.h</a>> + #include "../connection.h" + + class FormDialog : public <a href="qdialog.html">TQDialog</a> + { + public: + FormDialog(); + }; + + FormDialog::FormDialog() + { + <a href="qlabel.html">TQLabel</a> *forenameLabel = new <a href="qlabel.html">TQLabel</a>( "Forename:", this ); + <a href="qlabel.html">TQLabel</a> *forenameDisplay = new <a href="qlabel.html">TQLabel</a>( this ); + <a href="qlabel.html">TQLabel</a> *surnameLabel = new <a href="qlabel.html">TQLabel</a>( "Surname:", this ); + <a href="qlabel.html">TQLabel</a> *surnameDisplay = new <a href="qlabel.html">TQLabel</a>( this ); + <a href="qlabel.html">TQLabel</a> *salaryLabel = new <a href="qlabel.html">TQLabel</a>( "Salary:", this ); + <a href="qlineedit.html">TQLineEdit</a> *salaryEdit = new <a href="qlineedit.html">TQLineEdit</a>( this ); + + <a href="qgridlayout.html">TQGridLayout</a> *grid = new <a href="qgridlayout.html">TQGridLayout</a>( this ); + <a name="x2221"></a> grid-><a href="qgridlayout.html#addWidget">addWidget</a>( forenameLabel, 0, 0 ); + grid-><a href="qgridlayout.html#addWidget">addWidget</a>( forenameDisplay, 0, 1 ); + grid-><a href="qgridlayout.html#addWidget">addWidget</a>( surnameLabel, 1, 0 ); + grid-><a href="qgridlayout.html#addWidget">addWidget</a>( surnameDisplay, 1, 1 ); + grid-><a href="qgridlayout.html#addWidget">addWidget</a>( salaryLabel, 2, 0 ); + grid-><a href="qgridlayout.html#addWidget">addWidget</a>( salaryEdit, 2, 1 ); + <a name="x2222"></a> grid-><a href="qlayout.html#activate">activate</a>(); + + <a href="qsqlcursor.html">TQSqlCursor</a> staffCursor( "staff" ); + staffCursor.<a href="qsqlcursor.html#select">select</a>(); + staffCursor.<a href="qsqlquery.html#next">next</a>(); + + <a href="qsqlform.html">TQSqlForm</a> sqlForm( this ); + <a name="x2227"></a><a name="x2223"></a> sqlForm.<a href="qsqlform.html#setRecord">setRecord</a>( staffCursor.<a href="qsqlcursor.html#primeUpdate">primeUpdate</a>() ); + <a name="x2225"></a> sqlForm.<a href="qsqlform.html#insert">insert</a>( forenameDisplay, "forename" ); + sqlForm.<a href="qsqlform.html#insert">insert</a>( surnameDisplay, "surname" ); + sqlForm.<a href="qsqlform.html#insert">insert</a>( salaryEdit, "salary" ); + <a name="x2226"></a> sqlForm.<a href="qsqlform.html#readFields">readFields</a>(); + } + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv ); + + if ( ! createConnections() ) return 1; + + FormDialog *formDialog = new FormDialog(); + <a name="x2220"></a> formDialog-><a href="qdialog.html#show">show</a>(); + app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( formDialog ); + + return app.<a href="qapplication.html#exec">exec</a>(); + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-form1-main-cpp.html">sql/overview/form1/main.cpp</a> +</em></p> +</blockquote><p> We include the header files for the widgets that we need. We also +include <a href="qsqldatabase-h.html">qsqldatabase.h</a> and <a href="qsqlcursor-h.html">qsqlcursor.h</a> as usual, but we now add +<a href="qsqlform-h.html">qsqlform.h</a>. +<p> The form will be presented as a dialog so we subclass <a href="qdialog.html">TQDialog</a> with +our own FormDialog class. We use a <a href="qlineedit.html">TQLineEdit</a> for the salary so that +the user can change it. All the widgets are laid out using a grid. +<p> We create a cursor on the staff table, select all records and move to +the first record. +<p> Now we create a <a href="qsqlform.html">TQSqlForm</a> object and set the <a href="qsqlform.html">TQSqlForm</a>'s record buffer +to the cursor's update buffer. For each widget that we wish to make +data-aware we insert a pointer to the widget and the associated field +name into the <a href="qsqlform.html">TQSqlForm</a>. Finally we call readFields() to populate the +widgets with data from the database via the cursor's buffer. +<p> <a name="Displaying_a_Record_in_a_DataForm"></a> +<h4> Displaying a Record in a Data Form +</h4> +<a name="8-2-2"></a><p> <a href="qdataview.html">TQDataView</a> is a Widget that can hold a read-only <a href="qsqlform.html">TQSqlForm</a>. In +addition to <a href="qsqlform.html">TQSqlForm</a> it offers the slot refresh( <a href="qsqlrecord.html">TQSqlRecord</a> * ) so it +can easily be linked together with a <a href="qdatatable.html">TQDataTable</a> to display a detailed +view of a record: +<p> <pre> + connect( myDataTable, SIGNAL( currentChanged( <a href="qsqlrecord.html">TQSqlRecord</a>* ) ), + myDataView, SLOT( refresh( <a href="qsqlrecord.html">TQSqlRecord</a>* ) ) ); +</pre> + +<p> <a name="Editing_a_Record"></a> +<h4> Editing a Record +</h4> +<a name="8-2-3"></a><p> This example is similar to the previous one so we will focus on the +differences. +<p> + +<pre> class FormDialog : public <a href="qdialog.html">TQDialog</a> + { + <a href="metaobjects.html#Q_OBJECT">Q_OBJECT</a> + public: + FormDialog(); + ~FormDialog(); + public slots: + void save(); + private: + <a href="qsqlcursor.html">TQSqlCursor</a> staffCursor; + <a href="qsqlform.html">TQSqlForm</a> *sqlForm; + <a href="qsqlindex.html">TQSqlIndex</a> idIndex; + }; +</pre><blockquote><p align="center"><em> From <a href="sql-overview-form2-main-h.html">sql/overview/form2/main.h</a> +</em></p> +</blockquote><p> The save slot will be used for a button that the user can press to +confirm their update. We also hold pointers to the <a href="qsqlcursor.html">TQSqlCursor</a> and the +<a href="qsqlform.html">TQSqlForm</a> since they will need to be accessed outside the constructor. +<p> + +<pre> staffCursor.setTrimmed( "forename", TRUE ); + staffCursor.setTrimmed( "surname", TRUE ); +</pre> +<p> We call setTrimmed() on the text fields so that any spaces used to +right pad the fields are removed when the fields are retrieved. +<p> Properties that we might wish to apply to fields, such as alignment +and validation are achieved in the conventional way, for example, by +calling <a href="qlineedit.html#setAlignment">TQLineEdit::setAlignment</a>() and <a href="qlineedit.html#setValidator">TQLineEdit::setValidator</a>(). +<p> <pre> <a href="qlineedit.html">TQLineEdit</a> *forenameEdit = new <a href="qlineedit.html">TQLineEdit</a>( this ); +</pre> +<p> <pre> <a href="qpushbutton.html">TQPushButton</a> *saveButton = new <a href="qpushbutton.html">TQPushButton</a>( "&Save", this ); + <a href="qobject.html#connect">connect</a>( saveButton, SIGNAL(<a href="qbutton.html#clicked">clicked</a>()), this, SLOT(save()) ); +</pre> +<p> The FormDialog constructor is similar to the one in the previous +example. We have changed the forename and surname widgets to +<a href="qlineedit.html">TQLineEdit</a>s to make them editable and have added a <a href="qpushbutton.html">TQPushButton</a> +the user can click to save their updates. +<p> <pre> <a name="x2230"></a> grid-><a href="qgridlayout.html#addWidget">addWidget</a>( saveButton, 3, 0 ); +</pre> +<p> We add an extra row to the grid containing the save button. +<p> <pre> idIndex = staffCursor.index( "id" ); + staffCursor.select( idIndex ); + staffCursor.first(); +</pre> +<p> We create a <a href="qsqlindex.html">TQSqlIndex</a> object and then execute a select() using the +index. We then move to the first record in the result set. +<p> <pre> sqlForm = new <a href="qsqlform.html">TQSqlForm</a>( this ); + <a name="x2233"></a> sqlForm-><a href="qsqlform.html#setRecord">setRecord</a>( staffCursor.primeUpdate() ); +</pre> +<p> We create a new <a href="qsqlform.html">TQSqlForm</a> object and set it's record buffer to the +cursor's update buffer. +<p> <pre> <a name="x2231"></a> sqlForm-><a href="qsqlform.html#insert">insert</a>( forenameEdit, "forename" ); + sqlForm-><a href="qsqlform.html#insert">insert</a>( surnameEdit, "surname" ); + sqlForm-><a href="qsqlform.html#insert">insert</a>( salaryEdit, "salary" ); + <a name="x2232"></a> sqlForm-><a href="qsqlform.html#readFields">readFields</a>(); +</pre> +<p> Now we link the buffer's fields to the <a href="qlineedit.html">TQLineEdit</a> controls. (In the +previous example we linked the cursor's fields.) The edit controls are +populated by the readFields() call as before. +<p> <pre> FormDialog::~FormDialog() + { + + } +</pre> +<p> In the destructor we don't have to worry about the widgets or TQSqlForm +since they are children of the form and will be deleted by TQt at the +right time. +<p> <pre> void FormDialog::save() + { + <a name="x2234"></a> sqlForm-><a href="qsqlform.html#writeFields">writeFields</a>(); + staffCursor.update(); + staffCursor.select( idIndex ); + staffCursor.first(); + } +</pre> +<p> Finally we add the save functionality for when the user presses the +save button. We write back the data from the widgets to the <a href="qsqlrecord.html">TQSqlRecord</a> buffer with the writeFields() call. Then we update the +database with the updated version of the record with the cursor's +update() function. At this point the cursor is no longer positioned at +a valid record so we reissue the select() call using our <a href="qsqlindex.html">TQSqlIndex</a> +and move to the first record. +<p> <a href="qdatabrowser.html">TQDataBrowser</a> and <a href="qdataview.html">TQDataView</a> are widgets which provide a great deal of +the above functionality. <a href="qdatabrowser.html">TQDataBrowser</a> provides a data form which +allows editing of and navigation through a cursor's records. <a href="qdataview.html">TQDataView</a> provides a read only form for data in a cursor or database +record. See the class documentation or the <a href="designer-manual.html">TQt Designer</a> manual for more information on using these +widgets. +<p> Link to <a href="sql.html">sql/overview/form2/main.cpp</a> +<p> <a name="Custom_Editor_Widgets"></a> +<h3> Custom Editor Widgets +</h3> +<a name="8-3"></a><p> <a href="qsqlform.html">TQSqlForm</a> uses <a href="qsqlpropertymap.html">TQSqlPropertyMap</a> to handle the transfer of data between +widgets and database fields. Custom widgets can also be used in a form +by installing a property map that contains information about the +properties of the custom widget which should be used to transfer the +data. +<p> This example is based on the form2 example in the previous section so +we will only cover the differences here. The full source is in <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> +<p> + +<pre> class CustomEdit : public <a href="qlineedit.html">TQLineEdit</a> + { + Q_OBJECT + Q_PROPERTY( TQString upperLine READ upperLine WRITE setUpperLine ) + public: + CustomEdit( <a href="qwidget.html">TQWidget</a> *parent=0, const char *name=0 ); + <a href="qstring.html">TQString</a> upperLine() const; + void setUpperLine( const <a href="qstring.html">TQString</a> &line ); + public slots: + void changed( const <a href="qstring.html">TQString</a> &line ); + private: + <a href="qstring.html">TQString</a> upperLineText; + }; +</pre> +<p> We've created a simple subclass of <a href="qlineedit.html">TQLineEdit</a> and added a property, +upperLineText, which will hold an uppercase version of the text. We +also created a slot, changed(). +<p> <pre> <a href="qsqlpropertymap.html">TQSqlPropertyMap</a> *propMap; +</pre> +<p> We will be using a property map so we add a pointer to a property map +to our FormDialog's private data. +<p> + +<pre> CustomEdit::CustomEdit( <a href="qwidget.html">TQWidget</a> *parent, const char *name ) : + <a href="qlineedit.html">TQLineEdit</a>( parent, name ) + { + <a href="qobject.html#connect">connect</a>( this, SIGNAL(<a href="qlineedit.html#textChanged">textChanged</a>(const <a href="qstring.html">TQString</a> &)), + this, SLOT(changed(const <a href="qstring.html">TQString</a> &)) ); + } +</pre> +<p> In the CustomEdit constructor we use the TQLineEdit constructor and add +a connection between the textChanged signal and our own changed slot. +<p> <pre> void CustomEdit::changed( const <a href="qstring.html">TQString</a> &line ) + { + setUpperLine( line ); + } +</pre> +<p> The changed() slot calls our setUpperLine() function. +<p> <pre> void CustomEdit::setUpperLine( const <a href="qstring.html">TQString</a> &line ) + { + <a name="x2240"></a> upperLineText = line.<a href="qstring.html#upper">upper</a>(); + setText( upperLineText ); + } +</pre> +<p> The setUpperLine() function places an uppercase copy of the text in the +upperLineText buffer and then sets the text of the widget to this text. +<p> Our CustomEdit class ensures that the text entered is always uppercase +and provides a property that can be used with a property map to link +CustomEdit instances directly to database fields. +<p> <pre> CustomEdit *forenameEdit = new CustomEdit( this ); +</pre> +<p> <pre> CustomEdit *surnameEdit = new CustomEdit( this ); +</pre> +<p> We use the same FormDialog as we did before, but this time replace two +of the <a href="qlineedit.html">TQLineEdit</a> widgets with our own CustomEdit widgets. +<p> Laying out the grid and setting up the cursor is the same as before. +<p> <pre> propMap = new <a href="qsqlpropertymap.html">TQSqlPropertyMap</a>; + <a name="x2239"></a><a name="x2235"></a> propMap-><a href="qsqlpropertymap.html#insert">insert</a>( forenameEdit-><a href="qobject.html#className">className</a>(), "upperLine" ); +</pre> +<p> We create a new property map on the heap and register our CustomEdit +class and its upperLine property with the property map. +<p> <pre> sqlForm = new <a href="qsqlform.html">TQSqlForm</a>( this ); + <a name="x2238"></a><a name="x2236"></a> sqlForm-><a href="qsqlform.html#setRecord">setRecord</a>( staffCursor-><a href="qsqlcursor.html#primeUpdate">primeUpdate</a>() ); + <a name="x2237"></a> sqlForm-><a href="qsqlform.html#installPropertyMap">installPropertyMap</a>( propMap ); +</pre> +<p> The final change is to install the property map into the <a href="qsqlform.html">TQSqlForm</a> once +the TQSqlForm has been created. This passes responsibility for the +property map's memory to TQSqlForm which itself is owned by the +FormDialog, so TQt will delete them at the right time. +<p> The behaviour of this example is identical to the previous one except +that the forename and surname fields will be uppercase since they use +our CustomEdit widget. +<p> <a name="Custom_Editor_Widgets_for_Tables"></a> +<h4> Custom Editor Widgets for Tables +</h4> +<a name="8-3-1"></a><p> We must reimpliment <a href="qsqleditorfactory.html">TQSqlEditorFactory</a> to use custom editor widgets in +tables. In the following example we will create a custom editor based +on <a href="qcombobox.html">TQComboBox</a> and a TQSqlEditorFactory subclass to show how a <a href="qdatatable.html">TQDataTable</a> +can use a custom editor. +<p> + +<pre> class StatusPicker : public <a href="qcombobox.html">TQComboBox</a> + { + Q_OBJECT + Q_PROPERTY( int statusid READ statusId WRITE setStatusId ) + public: + StatusPicker( <a href="qwidget.html">TQWidget</a> *parent=0, const char *name=0 ); + int statusId() const; + void setStatusId( int id ); + private: + <a href="qmap.html">TQMap</a>< int, int > index2id; + }; +</pre><blockquote><p align="center"><em> From <a href="sql-overview-table3-main-h.html">sql/overview/table3/main.h</a> +</em></p> +</blockquote><p> We create a property, statusid, and define our READ and WRITE methods +for it. The statusid's in the status table will probably be different +from the combobox's indexes so we create a <a href="qmap.html">TQMap</a> to map combobox indexes +to/from the statusids that we will list in the combobox. +<p> <pre> class CustomSqlEditorFactory : public <a href="qsqleditorfactory.html">TQSqlEditorFactory</a> + { + Q_OBJECT + public: + <a href="qwidget.html">TQWidget</a> *createEditor( <a href="qwidget.html">TQWidget</a> *parent, const <a href="qsqlfield.html">TQSqlField</a> *field ); + }; +</pre> +<p> We also need to subclass <a href="qsqleditorfactory.html">TQSqlEditorFactory</a> declaring a createEditor() +function since that is the only function we need to reimplement. +<p> + +<pre> StatusPicker::StatusPicker( <a href="qwidget.html">TQWidget</a> *parent, const char *name ) + : <a href="qcombobox.html">TQComboBox</a>( parent, name ) + { + <a href="qsqlcursor.html">TQSqlCursor</a> cur( "status" ); + <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" ) ); + + int i = 0; + while ( cur.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2243"></a> <a href="qcombobox.html#insertItem">insertItem</a>( cur.<a href="qsqlquery.html#value">value</a>( "name" ).toString(), i ); + index2id[i] = cur.<a href="qsqlquery.html#value">value</a>( "id" ).toInt(); + i++; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-table3-main-cpp.html">sql/overview/table3/main.cpp</a> +</em></p> +</blockquote><p> In the StatusPicker's constructor we create a cursor over the status +table indexed by the name field. We then iterate over each record in the +status table inserting each name into the combobox. We store the +statusid for each name in the index2id <a href="qmap.html">TQMap</a> using the same TQMap index as +the combobox index. +<p> <pre> int StatusPicker::statusId() const + { + return index2id[ currentItem() ]; + } +</pre> +<p> The statusid property READ function simply involves looking up the +combobox's index for the currently selected item in the index2id TQMap +which maps combobox indexes to statusids. +<p> <pre> void StatusPicker::setStatusId( int statusid ) + { + TQMap<int,int>::Iterator it; + for ( it = index2id.begin(); it != index2id.end(); ++it ) { + if ( it.data() == statusid ) { + setCurrentItem( it.key() ); + break; + } + } + } +</pre> +<p> The statusId() function implements the statusid property's WRITE +function. We create an iterator over a TQMap and iterate over the +index2id TQMap. We compare each index2id element's data (statusid) to +the id parameter's value. If we have a match we set the combobox's +current item to the index2id element's key (the combobox index), and +leave the loop. +<p> When the user edits the status field in the <a href="qdatatable.html">TQDataTable</a> they will be +presented with a combobox of valid status names taken from the status +table. However the status displayed is still the raw statusid. To +display the status name when the field isn't being edited retquires us +to subclass TQDataTable and reimplement the paintField() function. +<p> + +<pre> class CustomTable : public <a href="qdatatable.html">TQDataTable</a> + { + Q_OBJECT + public: + CustomTable( + <a href="qsqlcursor.html">TQSqlCursor</a> *cursor, bool autoPopulate = FALSE, + <a href="qwidget.html">TQWidget</a> * parent = 0, const char * name = 0 ) : + <a href="qdatatable.html">TQDataTable</a>( cursor, autoPopulate, parent, name ) {} + void paintField( + <a href="qpainter.html">TQPainter</a> * p, const <a href="qsqlfield.html">TQSqlField</a>* field, const <a href="qrect.html">TQRect</a> & cr, bool ); + + }; +</pre><blockquote><p align="center"><em> From <a href="sql-overview-table4-main-h.html">sql/overview/table4/main.h</a> +</em></p> +</blockquote><p> We simply call the original <a href="qdatatable.html">TQDataTable</a> constructor without changing +anything. We also declare the paintField function. +<p> + +<pre> <a name="x2245"></a>void CustomTable::<a href="qdatatable.html#paintField">paintField</a>( <a href="qpainter.html">TQPainter</a> * p, const <a href="qsqlfield.html">TQSqlField</a>* field, + const <a href="qrect.html">TQRect</a> & cr, bool b) + { + if ( !field ) + return; + <a name="x2249"></a> if ( field-><a href="qsqlfield.html#name">name</a>() == "statusid" ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT name FROM status WHERE id=" + + <a name="x2250"></a> field-><a href="qsqlfield.html#value">value</a>().toString() ); + <a href="qstring.html">TQString</a> text; + if ( query.<a href="qsqlquery.html#next">next</a>() ) { + <a name="x2252"></a> text = query.<a href="qsqlquery.html#value">value</a>( 0 ).toString(); + } + p-><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 ); + } + else { + TQDataTable::<a href="qdatatable.html#paintField">paintField</a>( p, field, cr, b) ; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-table4-main-cpp.html">sql/overview/table4/main.cpp</a> +</em></p> +</blockquote><p> The paintField code is based on <a href="qdatatable.html">TQDataTable</a>'s source code. We need to +make three changes. Firstly add an if clause <tt>field->name() == "statusid"</tt> and look up the textual value for the id with a +straighforward <a href="qsqlquery.html">TQSqlQuery</a>. Secondly call the superclass to handle other +fields. The last change is in our main function where we change +staffTable from being a TQDataTable to being a CustomTable. +<p> <a name="Subclassing_QSqlCursor"></a> +<h2> Subclassing <a href="qsqlcursor.html">TQSqlCursor</a> +</h2> +<a name="9"></a><p> + +<pre> #include <<a href="qapplication-h.html">qapplication.h</a>> + #include <<a href="qsqldatabase-h.html">qsqldatabase.h</a>> + #include <<a href="qsqlcursor-h.html">qsqlcursor.h</a>> + #include <<a href="qdatatable-h.html">qdatatable.h</a>> + #include "../connection.h" + + int main( int argc, char *argv[] ) + { + <a href="qapplication.html">TQApplication</a> app( argc, argv ); + + if ( createConnections() ) { + <a href="qsqlcursor.html">TQSqlCursor</a> invoiceItemCursor( "invoiceitem" ); + + <a href="qdatatable.html">TQDataTable</a> *invoiceItemTable = new <a href="qdatatable.html">TQDataTable</a>( &invoiceItemCursor ); + + app.<a href="qapplication.html#setMainWidget">setMainWidget</a>( invoiceItemTable ); + + <a name="x2255"></a> invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "pricesid", "PriceID" ); + invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "quantity", "Quantity" ); + invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "paiddate", "Paid" ); + + <a name="x2256"></a> invoiceItemTable-><a href="qdatatable.html#refresh">refresh</a>(); + invoiceItemTable-><a href="qwidget.html#show">show</a>(); + + return app.<a href="qapplication.html#exec">exec</a>(); + } + + return 1; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass1-main-cpp.html">sql/overview/subclass1/main.cpp</a> +</em></p> +</blockquote><p> This example is very similar to the table1 example presented earlier. We +create a cursor, add the fields and their display labels to a <a href="qdatatable.html">TQDataTable</a>, +call refresh() to load the data and call show() to show the widget. +<p> Unfortunately this example is unsatisfactory. It is tedious to set the +table name and any custom characteristics for the fields every time we +need a cursor over this table. And it would be far better if we +displayed the name of the product rather than its pricesid. Since we +know the price of the product and the quantity we could also show the +product cost and the cost of each invoiceitem. Finally it would be +useful (or even essential for primary keys) if we could default some of +the values when the user adds a new record. +<p> + +<pre> class InvoiceItemCursor : public <a href="qsqlcursor.html">TQSqlCursor</a> + { + public: + InvoiceItemCursor(); + }; +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass2-main-h.html">sql/overview/subclass2/main.h</a> +</em></p> +</blockquote><p> We have created a separate header file and subclassed <a href="qsqlcursor.html">TQSqlCursor</a>. +<p> + +<pre> InvoiceItemCursor::InvoiceItemCursor() : + <a href="qsqlcursor.html">TQSqlCursor</a>( "invoiceitem" ) + { + // NOOP + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass2-main-cpp.html">sql/overview/subclass2/main.cpp</a> +</em></p> +</blockquote><p> In our class's constructor we call the TQSqlCursor constructor with the +name of the table. We don't have any other characteristics to add at +this stage. +<p> <pre> InvoiceItemCursor invoiceItemCursor; +</pre> +<p> Whenever we retquire a cursor over the invoiceitem table we can create +an InvoiceItemCursor instead of a generic TQSqlCursor. +<p> We still need to show the product name rather than the pricesid. +<p> + +<pre> protected: + <a href="qvariant.html">TQVariant</a> calculateField( const <a href="qstring.html">TQString</a> & name ); +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass3-main-h.html">sql/overview/subclass3/main.h</a> +</em></p> +</blockquote><p> The change in the header file is minimal: we simply add the signature +of the calculateField() function since we will be reimplementing it. +<p> + +<pre> InvoiceItemCursor::InvoiceItemCursor() : + <a href="qsqlcursor.html">TQSqlCursor</a>( "invoiceitem" ) + { + <a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> productName( "productname", TQVariant::String ); + <a href="qsqlcursor.html#append">append</a>( productName ); + <a name="x2260"></a> <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE ); + } + + <a name="x2259"></a>TQVariant InvoiceItemCursor::<a href="qsqlcursor.html#calculateField">calculateField</a>( const <a href="qstring.html">TQString</a> & name ) + { + if ( name == "productname" ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT name FROM prices WHERE id=" + + <a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() ); + if ( query.<a href="qsqlquery.html#next">next</a>() ) + <a name="x2262"></a> return query.<a href="qsqlquery.html#value">value</a>( 0 ); + } + + return TQVariant( <a href="qstring.html#TQString-null">TQString::null</a> ); + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass3-main-cpp.html">sql/overview/subclass3/main.cpp</a> +</em></p> +</blockquote><p> We have changed the InvoiceItemCursor constructor. We now create a new +<a href="qsqlfield.html">TQSqlField</a> called productname and append this to the +InvoiceItemCursor's set of fields. We call setCalculated() on +productname to identify it as a calculated field. The first argument +to setCalculated() is the field name, the second a bool which if TRUE +signifies that calculateField() must be called to get the field's +value. +<p> <pre> <a name="x2258"></a> invoiceItemTable-><a href="qdatatable.html#addColumn">addColumn</a>( "productname", "Product" ); +</pre> +<p> We add our new fields with addColumn() which adds them to the form and +sets their display names. +<p> We have to define our own calculateField() function. In our example +database the pricesid in the invoiceitem table is a foreign key into +the prices table. We find the name of the product by executing a query +on the prices table using the pricesid. This returns the product's +name. +<p> We are now able to extend the example to include calculated fields +which perform real calculations. +<p> The header file, <a href="sql-overview-subclass4-main-h.html">sql/overview/subclass4/main.h</a>, remains unchanged +from the previous example, but the constructor and calculateField() +function retquire some simple expansion. We'll look at each in turn. +<p> + +<pre> InvoiceItemCursor::InvoiceItemCursor() : + <a href="qsqlcursor.html">TQSqlCursor</a>( "invoiceitem" ) + { + <a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> productName( "productname", TQVariant::String ); + <a href="qsqlcursor.html#append">append</a>( productName ); + <a name="x2264"></a> <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE ); + + <a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> productPrice( "price", TQVariant::Double ); + <a href="qsqlcursor.html#append">append</a>( productPrice ); + <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productPrice.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE ); + + <a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> productCost( "cost", TQVariant::Double ); + <a href="qsqlcursor.html#append">append</a>( productCost ); + <a href="qsqlcursor.html#setCalculated">setCalculated</a>( productCost.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE ); + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass4-main-cpp.html">sql/overview/subclass4/main.cpp</a> +</em></p> +</blockquote><p> We create two extra fields, price and cost, and append them to the +cursor's set of fields. Both are registered as calculated fields with +calls to setCalculated(). +<p> <pre> <a name="x2263"></a>TQVariant InvoiceItemCursor::<a href="qsqlcursor.html#calculateField">calculateField</a>( const <a href="qstring.html">TQString</a> & name ) + { + + if ( name == "productname" ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT name FROM prices WHERE id=" + + <a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() ); + <a name="x2265"></a> if ( query.<a href="qsqlquery.html#next">next</a>() ) + <a name="x2266"></a> return query.<a href="qsqlquery.html#value">value</a>( 0 ); + } + else if ( name == "price" ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT price FROM prices WHERE id=" + + <a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() ); + if ( query.<a href="qsqlquery.html#next">next</a>() ) + return query.<a href="qsqlquery.html#value">value</a>( 0 ); + } + else if ( name == "cost" ) { + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT price FROM prices WHERE id=" + + <a href="qsqlrecord.html#field">field</a>( "pricesid" )->value().toString() ); + if ( query.<a href="qsqlquery.html#next">next</a>() ) + return TQVariant( query.<a href="qsqlquery.html#value">value</a>( 0 ).toDouble() * + <a href="qsqlquery.html#value">value</a>( "quantity").toDouble() ); + } + + return TQVariant( TQString::null ); + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass4-main-cpp.html">sql/overview/subclass4/main.cpp</a> +</em></p> +</blockquote><p> The calculateField() function has expanded slightly because now we +must calculate the value of three different fields. The productname +and price fields are produced by looking up the corresponding values +in the prices table keyed by pricesid. The cost field is calculated +simply by multiplying the price by the quantity. Note that we cast the +cost to a <a href="qvariant.html">TQVariant</a> since that is the type that calculateField() must +return. +<p> We've written three separate queries rather than one to make the +example more like a real application where it is more likely that each +calculated field would be a lookup against a different table or view. +<p> The last feature that we need to add is defaulting values when the +user attempts to insert a new record. +<p> + +<pre> <a href="qsqlrecord.html">TQSqlRecord</a> *primeInsert(); +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass5-main-h.html">sql/overview/subclass5/main.h</a> +</em></p> +</blockquote><p> We declare our own primeInsert() function since we will need to +reimplement this. +<p> The constructor and the calculateField() function remain unchanged. +<p> + +<pre> <a name="x2267"></a>TQSqlRecord *InvoiceItemCursor::<a href="qsqlcursor.html#primeInsert">primeInsert</a>() + { + <a href="qsqlrecord.html">TQSqlRecord</a> *buffer = <a href="qsqlcursor.html#editBuffer">editBuffer</a>(); + <a href="qsqlquery.html">TQSqlQuery</a> query( "SELECT NEXTVAL( 'invoiceitem_seq' )" ); + <a name="x2268"></a> if ( query.<a href="qsqlquery.html#next">next</a>() ) + <a name="x2270"></a><a name="x2269"></a> buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "id", query.<a href="qsqlquery.html#value">value</a>( 0 ) ); + buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "paiddate", TQDate::currentDate() ); + buffer-><a href="qsqlrecord.html#setValue">setValue</a>( "quantity", 1 ); + + return buffer; + } +</pre><blockquote><p align="center"><em> From <a href="sql-overview-subclass5-main-cpp.html">sql/overview/subclass5/main.cpp</a> +</em></p> +</blockquote><p> We get a pointer to the internal edit buffer that the cursor uses for +inserts and updates. The id field is a unique integer that we generate +using the invoiceitem_seq. We default the value of the paiddate field +to today's date and default the quantity to 1. Finally we return a +pointer to the buffer. The rest of the code is unchanged from the +previous version. +<p> <a name="Example_Tables"></a> +<h2> The Example Tables +</h2> +<a name="10"></a><p> The example tables used can be recreated with the following standard +SQL. You may need to modify the SQL to match that used by your +particular database. +<p> <pre> +create table people (id integer primary key, name char(40)) + +create table staff (id integer primary key, forename char(40), + surname char(40), salary float, statusid integer) + +create table status (id integer primary key, name char(30)) + +create table creditors (id integer primary key, forename char(40), + surname char(40), city char(30)) + +create table prices (id integer primary key, name char(40), price float) + +create table invoiceitem (id integer primary key, + pricesid integer, quantity integer, + paiddate date) +</pre> + +<p> A sequence was used in the calculateField() example above. Note that +sequences are not supported in all databases. +<p> <pre> +create sequence invoiceitem_seq +</pre> + +<p> +<!-- eof --> +<p><address><hr><div align=center> +<table width=100% cellspacing=0 border=0><tr> +<td>Copyright © 2007 +<a href="troll.html">Trolltech</a><td align=center><a href="trademarks.html">Trademarks</a> +<td align=right><div align=right>TQt 3.3.8</div> +</table></div></address></body> +</html> |