You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1674 lines
94 KiB
1674 lines
94 KiB
<!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="ntqsql.html">TQSql</a>
|
|
<td valign="top"><a href="ntqsqlcursor.html">TQSqlCursor</a>
|
|
<td valign="top"><a href="ntqsqldatabase.html">TQSqlDatabase</a>
|
|
<td valign="top"><a href="ntqsqldriver.html">TQSqlDriver</a>
|
|
<td valign="top"><a href="ntqsqldriverplugin.html">TQSqlDriverPlugin</a>
|
|
<tr bgcolor="#d0d0d0">
|
|
<td valign="top"><a href="ntqsqleditorfactory.html">TQSqlEditorFactory</a>
|
|
<td valign="top"><a href="ntqsqlerror.html">TQSqlError</a>
|
|
<td valign="top"><a href="ntqsqlfield.html">TQSqlField</a>
|
|
<td valign="top"><a href="qsqlfieldinfo.html">TQSqlFieldInfo</a>
|
|
<td valign="top"><a href="ntqsqlform.html">TQSqlForm</a>
|
|
<tr bgcolor="#f0f0f0">
|
|
<td valign="top"><a href="ntqsqlindex.html">TQSqlIndex</a>
|
|
<td valign="top"><a href="ntqsqlpropertymap.html">TQSqlPropertyMap</a>
|
|
<td valign="top"><a href="ntqsqlquery.html">TQSqlQuery</a>
|
|
<td valign="top"><a href="ntqsqlrecord.html">TQSqlRecord</a>
|
|
<td valign="top"><a href="qsqlrecordinfo.html">TQSqlRecordInfo</a>
|
|
<tr bgcolor="#d0d0d0">
|
|
<td valign="top"><a href="ntqsqlresult.html">TQSqlResult</a>
|
|
<td valign="top"><a href="ntqsqlselectcursor.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="ntqsqlcursor.html">TQSqlCursor</a> class provides an
|
|
interface to database browsing and editing that does not <em>require</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="ntqsqldatabase.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="ntqsqlquery.html">TQSqlQuery</a>.
|
|
<p> <a href="#Using_QSqlCursor">Using Cursors</a>. This section explains
|
|
how to use the <a href="ntqsqlcursor.html">TQSqlCursor</a> class which provides a simpler API than the
|
|
raw SQL used with <a href="ntqsqlquery.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="ntqdatatable.html">TQDataTable</a>, <a href="ntqsqlform.html">TQSqlForm</a>, <a href="ntqsqlpropertymap.html">TQSqlPropertyMap</a> and <a href="ntqsqleditorfactory.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="ntqdatabrowser.html">TQDataBrowser</a> and <a href="ntqdataview.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="ntqsqlcursor.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="ntqsqleditorfactory.html">TQSqlEditorFactory</a>, <a href="ntqsqlform.html">TQSqlForm</a>, <a href="ntqsqlpropertymap.html">TQSqlPropertyMap</a>, <a href="ntqdatatable.html">TQDataTable</a>, <a href="ntqdatabrowser.html">TQDataBrowser</a> and <a href="ntqdataview.html">TQDataView</a>.
|
|
<p> <em>SQL API Layer.</em> These classes provide access to databases.
|
|
Connections are made using the <a href="ntqsqldatabase.html">TQSqlDatabase</a> class. Database
|
|
interaction is achieved either by using the <a href="ntqsqlquery.html">TQSqlQuery</a> class and
|
|
executing SQL commands directly or by using the higher level <a href="ntqsqlcursor.html">TQSqlCursor</a> class which composes SQL commands automatically. In
|
|
addition to <a href="ntqsqldatabase.html">TQSqlDatabase</a>, <a href="ntqsqlcursor.html">TQSqlCursor</a> and <a href="ntqsqlquery.html">TQSqlQuery</a>, the SQL
|
|
API layer is supported by <a href="ntqsqlerror.html">TQSqlError</a>, <a href="ntqsqlfield.html">TQSqlField</a>, <a href="qsqlfieldinfo.html">TQSqlFieldInfo</a>,
|
|
<a href="ntqsqlindex.html">TQSqlIndex</a>, <a href="ntqsqlrecord.html">TQSqlRecord</a> and <a href="qsqlrecordinfo.html">TQSqlRecordInfo</a>.
|
|
<p> <em>Driver Layer.</em> This comprises three classes, <a href="ntqsqlresult.html">TQSqlResult</a>, <a href="ntqsqldriver.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="ntqsqldatabase.html">TQSqlDatabase</a> (see <a href="ntqsqldatabase.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="ntqsqlquery.html">TQSqlQuery</a> or <a href="ntqsqlcursor.html">TQSqlCursor</a> classes can be used.
|
|
<p> If the application only needs a single database connection, the <a href="ntqsqldatabase.html">TQSqlDatabase</a> class can create a connection which is used by default
|
|
for all SQL operations. If multiple database connections are required
|
|
these can easily be set up.
|
|
<p> <a href="ntqsqldatabase.html">TQSqlDatabase</a> requires the <a href="qsqldatabase-h.html">ntqsqldatabase.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">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include "../connection.h"
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv, FALSE );
|
|
|
|
<a name="x2141"></a> <a href="ntqsqldatabase.html">TQSqlDatabase</a> *defaultDB = TQSqlDatabase::<a href="ntqsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER );
|
|
<a name="x2143"></a> defaultDB-><a href="ntqsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME );
|
|
<a name="x2146"></a> defaultDB-><a href="ntqsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER );
|
|
<a name="x2145"></a> defaultDB-><a href="ntqsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD );
|
|
<a name="x2144"></a> defaultDB-><a href="ntqsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST );
|
|
|
|
<a name="x2142"></a> if ( defaultDB-><a href="ntqsqldatabase.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="ntqsqldatabase.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="ntqsqldatabase.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="ntqsqldatabase.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">ntqsqldatabase.h</a>>
|
|
#include "connection.h"
|
|
|
|
bool createConnections()
|
|
{
|
|
|
|
<a name="x2147"></a> <a href="ntqsqldatabase.html">TQSqlDatabase</a> *defaultDB = TQSqlDatabase::<a href="ntqsqldatabase.html#addDatabase">addDatabase</a>( DB_SALES_DRIVER );
|
|
<a name="x2150"></a> defaultDB-><a href="ntqsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_SALES_DBNAME );
|
|
<a name="x2153"></a> defaultDB-><a href="ntqsqldatabase.html#setUserName">setUserName</a>( DB_SALES_USER );
|
|
<a name="x2152"></a> defaultDB-><a href="ntqsqldatabase.html#setPassword">setPassword</a>( DB_SALES_PASSWD );
|
|
<a name="x2151"></a> defaultDB-><a href="ntqsqldatabase.html#setHostName">setHostName</a>( DB_SALES_HOST );
|
|
<a name="x2149"></a> if ( ! defaultDB-><a href="ntqsqldatabase.html#open">open</a>() ) {
|
|
<a name="x2148"></a> <a href="ntqapplication.html#qWarning">tqWarning</a>( "Failed to open sales database: " + defaultDB-><a href="ntqsqldatabase.html#lastError">lastError</a>().text() );
|
|
return FALSE;
|
|
}
|
|
|
|
<a href="ntqsqldatabase.html">TQSqlDatabase</a> *oracle = TQSqlDatabase::<a href="ntqsqldatabase.html#addDatabase">addDatabase</a>( DB_ORDERS_DRIVER, "ORACLE" );
|
|
oracle-><a href="ntqsqldatabase.html#setDatabaseName">setDatabaseName</a>( DB_ORDERS_DBNAME );
|
|
oracle-><a href="ntqsqldatabase.html#setUserName">setUserName</a>( DB_ORDERS_USER );
|
|
oracle-><a href="ntqsqldatabase.html#setPassword">setPassword</a>( DB_ORDERS_PASSWD );
|
|
oracle-><a href="ntqsqldatabase.html#setHostName">setHostName</a>( DB_ORDERS_HOST );
|
|
if ( ! oracle-><a href="ntqsqldatabase.html#open">open</a>() ) {
|
|
<a href="ntqapplication.html#qWarning">tqWarning</a>( "Failed to open orders database: " + oracle-><a href="ntqsqldatabase.html#lastError">lastError</a>().text() );
|
|
return FALSE;
|
|
}
|
|
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> q(TQString::null, defaultDB);
|
|
<a name="x2154"></a> q.<a href="ntqsqlquery.html#exec">exec</a>("create table people (id integer primary key, name char(40))");
|
|
q.<a href="ntqsqlquery.html#exec">exec</a>("create table staff (id integer primary key, forename char(40), "
|
|
"surname char(40), salary float, statusid integer)");
|
|
q.<a href="ntqsqlquery.html#exec">exec</a>("create table status (id integer primary key, name char(30))");
|
|
q.<a href="ntqsqlquery.html#exec">exec</a>("create table creditors (id integer primary key, forename char(40), "
|
|
"surname char(40), city char(30))");
|
|
q.<a href="ntqsqlquery.html#exec">exec</a>("create table prices (id integer primary key, name char(40), price float)");
|
|
q.<a href="ntqsqlquery.html#exec">exec</a>("create table invoiceitem (id integer primary key, "
|
|
"pricesid integer, quantity integer, paiddate date)");
|
|
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> q2(TQString::null, oracle);
|
|
q2.<a href="ntqsqlquery.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">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include "../connection.h"
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv, FALSE );
|
|
|
|
if ( createConnections() ) {
|
|
// Databases successfully opened; get pointers to them:
|
|
<a name="x2155"></a> <a href="ntqsqldatabase.html">TQSqlDatabase</a> *oracledb = TQSqlDatabase::<a href="ntqsqldatabase.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="ntqsqldatabase.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="ntqsqldatabase.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="ntqsqldatabase.html#close">TQSqlDatabase::close</a>(), and
|
|
then remove it using the static function
|
|
<a href="ntqsqldatabase.html#removeDatabase">TQSqlDatabase::removeDatabase</a>().
|
|
<p> <a name="Executing_SQL_commands"></a>
|
|
<h2> Executing SQL Commands Using <a href="ntqsqlquery.html">TQSqlQuery</a>
|
|
</h2>
|
|
<a name="6"></a><p> The <a href="ntqsqlquery.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="ntqsqlcursor.html">TQSqlCursor</a> class described in the next section inherits from <a href="ntqsqlquery.html">TQSqlQuery</a> and provides a higher level interface that composes SQL
|
|
commands for us. <a href="ntqsqlcursor.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="ntqsqlcursor.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="ntqsqldriver.html#hasFeature">TQSqlDriver::hasFeature</a>( TQSqlDriver::Transactions ) will return TRUE.
|
|
You can use <a href="ntqsqldatabase.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="ntqsqldatabase.html#commit">TQSqlDatabase::commit</a>() or
|
|
<a href="ntqsqldatabase.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">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include <<a href="qsqlquery-h.html">ntqsqlquery.h</a>>
|
|
#include "../connection.h"
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv, FALSE );
|
|
|
|
if ( createConnections() ) {
|
|
<a name="x2156"></a> <a href="ntqsqldatabase.html">TQSqlDatabase</a> *oracledb = TQSqlDatabase::<a href="ntqsqldatabase.html#database">database</a>( "ORACLE" );
|
|
// Copy data from the oracle database to the ODBC (default)
|
|
// database
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> target;
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT id, name FROM people", oracledb );
|
|
<a name="x2158"></a> if ( query.<a href="ntqsqlquery.html#isActive">isActive</a>() ) {
|
|
<a name="x2159"></a> while ( query.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2157"></a> target.<a href="ntqsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " +
|
|
<a name="x2160"></a> query.<a href="ntqsqlquery.html#value">value</a>(0).toString() +
|
|
", '" + query.<a href="ntqsqlquery.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">ntqsqlquery.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="ntqsqlquery.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="ntqsqlquery.html#isActive">isActive</a>() ) {
|
|
<a name="x2163"></a> while ( query.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2161"></a> target.<a href="ntqsqlquery.html#exec">exec</a>( "INSERT INTO people ( id, name ) VALUES ( " +
|
|
<a name="x2165"></a> query.<a href="ntqsqlquery.html#value">value</a>(0).toString() +
|
|
", '" + query.<a href="ntqsqlquery.html#value">value</a>(1).toString() + "' )" );
|
|
if ( target.<a href="ntqsqlquery.html#isActive">isActive</a>() )
|
|
<a name="x2164"></a> count += target.<a href="ntqsqlquery.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">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include <<a href="qsqlquery-h.html">ntqsqlquery.h</a>>
|
|
#include "../connection.h"
|
|
|
|
bool createConnections();
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv, FALSE );
|
|
|
|
int rows = 0;
|
|
|
|
if ( createConnections() ) {
|
|
<a href="ntqsqlquery.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="ntqsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="ntqsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
|
|
|
|
<a name="x2166"></a> query.<a href="ntqsqlquery.html#exec">exec</a>( "UPDATE staff SET salary=60000 WHERE id=1155" );
|
|
if ( query.<a href="ntqsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="ntqsqlquery.html#numRowsAffected">numRowsAffected</a>() ;
|
|
|
|
query.<a href="ntqsqlquery.html#exec">exec</a>( "DELETE FROM staff WHERE id=1155" );
|
|
if ( query.<a href="ntqsqlquery.html#isActive">isActive</a>() ) rows += query.<a href="ntqsqlquery.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="ntqsqlquery.html">TQSqlQuery</a> constructor the default database is used. <a href="ntqsqlquery.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="ntqsqlquery.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="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT id, name FROM people ORDER BY name" );
|
|
<a name="x2171"></a> if ( ! query.<a href="ntqsqlquery.html#isActive">isActive</a>() ) return 1; // Query failed
|
|
int i;
|
|
<a name="x2174"></a> i = query.<a href="ntqsqlquery.html#size">size</a>(); // In this example we have 9 records; i == 9.
|
|
<a name="x2170"></a> query.<a href="ntqsqlquery.html#first">first</a>(); // Moves to the first record.
|
|
<a name="x2169"></a> i = query.<a href="ntqsqlquery.html#at">at</a>(); // i == 0
|
|
<a name="x2172"></a> query.<a href="ntqsqlquery.html#last">last</a>(); // Moves to the last record.
|
|
i = query.<a href="ntqsqlquery.html#at">at</a>(); // i == 8
|
|
<a name="x2173"></a> query.<a href="ntqsqlquery.html#seek">seek</a>( query.<a href="ntqsqlquery.html#size">size</a>() / 2 ); // Moves to the middle record.
|
|
i = query.<a href="ntqsqlquery.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="ntqsqldatabase.html">TQSqlDatabase</a>* defaultDB = TQSqlDatabase::<a href="ntqsqldatabase.html#database">database</a>();
|
|
if ( defaultDB-><a href="ntqsqldatabase.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="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT id, surname FROM staff" );
|
|
<a name="x2175"></a> if ( query.<a href="ntqsqlquery.html#isActive">isActive</a>() ) {
|
|
<a name="x2176"></a> while ( query.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a href="ntqapplication.html#qDebug">tqDebug</a>( query.<a href="ntqsqlquery.html#value">value</a>(0).toString() + ": " +
|
|
<a name="x2177"></a> query.<a href="ntqsqlquery.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="ntqsqlcursor.html">TQSqlCursor</a>
|
|
</h2>
|
|
<a name="7"></a><p> The <a href="ntqsqlcursor.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="ntqsqlquery.html">TQSqlQuery</a> can, with two
|
|
exceptions. Since cursors represent tables or views within the
|
|
database, by default, <a href="ntqsqlcursor.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="ntqsqlrecord.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="ntqsqlquery.html">TQSqlQuery</a> instead, and customize the
|
|
query to suit your needs. You can edit records using a <a href="ntqsqlcursor.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="ntqsqlquery.html">TQSqlQuery</a> for edits.
|
|
<p> <a href="ntqsqlcursor.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="ntqsqlcursor.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="ntqsqlcursor.html">TQSqlCursor</a> class requires the <a href="qsqlcursor-h.html">ntqsqlcursor.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">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include <<a href="qsqlcursor-h.html">ntqsqlcursor.h</a>>
|
|
#include "../connection.h"
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv );
|
|
|
|
if ( createConnections() ) {
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> cur( "staff" ); // Specify the table/view name
|
|
<a name="x2178"></a> cur.<a href="ntqsqlcursor.html#select">select</a>(); // We'll retrieve every record
|
|
while ( cur.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2179"></a> <a href="ntqapplication.html#qDebug">tqDebug</a>( cur.<a href="ntqsqlquery.html#value">value</a>( "id" ).toString() + ": " +
|
|
cur.<a href="ntqsqlquery.html#value">value</a>( "surname" ).toString() + " " +
|
|
cur.<a href="ntqsqlquery.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="ntqsqlcursor.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="ntqsqlcursor.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="ntqsqlquery.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="ntqsqlindex.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="ntqsqlcursor.html">TQSqlCursor</a> cur( "staff" );
|
|
<a href="ntqsqlindex.html">TQSqlIndex</a> nameIndex = cur.<a href="ntqsqlcursor.html#index">index</a>( "surname" );
|
|
cur.<a href="ntqsqlcursor.html#select">select</a>( nameIndex );
|
|
</pre>
|
|
|
|
<p> Here we create a <a href="ntqsqlindex.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="ntqsqlindex.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="ntqsqlindex.html#setDescending">TQSqlIndex::setDescending</a>(); the default is ascending.
|
|
<p>
|
|
|
|
<pre> <a href="ntqsqlcursor.html">TQSqlCursor</a> cur( "staff" );
|
|
<a href="ntqstringlist.html">TQStringList</a> fields = TQStringList() << "surname" << "forename";
|
|
<a name="x2181"></a> <a href="ntqsqlindex.html">TQSqlIndex</a> order = cur.<a href="ntqsqlcursor.html#index">index</a>( fields );
|
|
<a name="x2182"></a> cur.<a href="ntqsqlcursor.html#select">select</a>( order );
|
|
<a name="x2183"></a> while ( cur.<a href="ntqsqlquery.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="ntqsqlindex.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="ntqsqlcursor.html">TQSqlCursor</a> cur( "staff" );
|
|
<a href="ntqstringlist.html">TQStringList</a> fields = TQStringList() << "id" << "forename";
|
|
<a name="x2184"></a> <a href="ntqsqlindex.html">TQSqlIndex</a> order = cur.<a href="ntqsqlcursor.html#index">index</a>( fields );
|
|
<a href="ntqsqlindex.html">TQSqlIndex</a> filter = cur.<a href="ntqsqlcursor.html#index">index</a>( "surname" );
|
|
<a name="x2186"></a> cur.<a href="ntqsqlrecord.html#setValue">setValue</a>( "surname", "Bloggs" );
|
|
<a name="x2185"></a> cur.<a href="ntqsqlcursor.html#select">select</a>( filter, order );
|
|
while ( cur.<a href="ntqsqlquery.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="ntqsqlindex.html">TQSqlIndex</a> contains two fields, "id" and "forename"
|
|
which are used to order the results. The "filter" <a href="ntqsqlindex.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="ntqsqlcursor.html">TQSqlCursor</a> cur( "creditors" );
|
|
<a href="ntqstringlist.html">TQStringList</a> orderFields = TQStringList() << "surname" << "forename";
|
|
<a name="x2188"></a> <a href="ntqsqlindex.html">TQSqlIndex</a> order = cur.<a href="ntqsqlcursor.html#index">index</a>( orderFields );
|
|
|
|
<a href="ntqstringlist.html">TQStringList</a> filterFields = TQStringList() << "surname" << "city";
|
|
<a href="ntqsqlindex.html">TQSqlIndex</a> filter = cur.<a href="ntqsqlcursor.html#index">index</a>( filterFields );
|
|
<a name="x2190"></a> cur.<a href="ntqsqlrecord.html#setValue">setValue</a>( "surname", "Chirac" );
|
|
cur.<a href="ntqsqlrecord.html#setValue">setValue</a>( "city", "Paris" );
|
|
|
|
<a name="x2189"></a> cur.<a href="ntqsqlcursor.html#select">select</a>( filter, order );
|
|
|
|
while ( cur.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2191"></a> int id = cur.<a href="ntqsqlquery.html#value">value</a>( "id" ).toInt();
|
|
<a href="ntqstring.html">TQString</a> name = cur.<a href="ntqsqlquery.html#value">value</a>( "forename" ).toString() + " " +
|
|
cur.<a href="ntqsqlquery.html#value">value</a>( "surname" ).toString();
|
|
<a name="x2193"></a> <a href="ntqapplication.html#qDebug">tqDebug</a>( TQString::<a href="ntqstring.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="ntqsqlindex.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="ntqapplication.html#qDebug">tqDebug</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="ntqsqlcursor.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="ntqsqlquery.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: acquire 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="ntqsqlcursor.html#primeInsert">TQSqlCursor::primeInsert</a>() clears all the field values in
|
|
the edit buffer (see <a href="ntqsqlrecord.html#clearValues">TQSqlRecord::clearValues</a>()). Both <a href="ntqsqlcursor.html#primeUpdate">TQSqlCursor::primeUpdate</a>() and <a href="ntqsqlcursor.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="ntqsqlcursor.html">TQSqlCursor</a> cur( "prices" );
|
|
<a href="ntqstringlist.html">TQStringList</a> names = TQStringList() <<
|
|
"Screwdriver" << "Hammer" << "Wrench" << "Saw";
|
|
int id = 20;
|
|
<a name="x2197"></a> for ( TQStringList::Iterator name = names.<a href="ntqvaluelist.html#begin">begin</a>();
|
|
<a name="x2198"></a> name != names.<a href="ntqvaluelist.html#end">end</a>(); ++name ) {
|
|
<a name="x2195"></a> <a href="ntqsqlrecord.html">TQSqlRecord</a> *buffer = cur.<a href="ntqsqlcursor.html#primeInsert">primeInsert</a>();
|
|
<a name="x2196"></a> buffer-><a href="ntqsqlrecord.html#setValue">setValue</a>( "id", id );
|
|
buffer-><a href="ntqsqlrecord.html#setValue">setValue</a>( "name", *name );
|
|
buffer-><a href="ntqsqlrecord.html#setValue">setValue</a>( "price", 100.0 + (double)id );
|
|
<a name="x2194"></a> count += cur.<a href="ntqsqlcursor.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="ntqsqlrecord.html">TQSqlRecord</a> buffer in which all the fields
|
|
are set to <tt>NULL</tt>. (Note that <a href="ntqsqlcursor.html#primeInsert">TQSqlCursor::primeInsert</a>() is virtual,
|
|
and can be customized by derived classes. See <a href="ntqsqlcursor.html">TQSqlCursor</a>). Next we
|
|
call setValue() for each field that requires a value. Finally we call
|
|
insert() to insert the record. The insert() call returns the number of
|
|
rows inserted.
|
|
<p> We obtained a pointer to a <a href="ntqsqlrecord.html">TQSqlRecord</a> object from the primeInsert()
|
|
call. <a href="ntqsqlrecord.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="ntqsqlcursor.html">TQSqlCursor</a> cur( "prices" );
|
|
<a name="x2200"></a> cur.<a href="ntqsqlcursor.html#select">select</a>( "id=202" );
|
|
if ( cur.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2199"></a> <a href="ntqsqlrecord.html">TQSqlRecord</a> *buffer = cur.<a href="ntqsqlcursor.html#primeUpdate">primeUpdate</a>();
|
|
<a name="x2204"></a> double price = buffer-><a href="ntqsqlrecord.html#value">value</a>( "price" ).toDouble();
|
|
double newprice = price * 1.05;
|
|
<a name="x2203"></a> buffer-><a href="ntqsqlrecord.html#setValue">setValue</a>( "price", newprice );
|
|
<a name="x2201"></a> cur.<a href="ntqsqlcursor.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="ntqsqlrecord.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="ntqsqlquery.html">TQSqlQuery</a> is more efficient, e.g.
|
|
<p> <pre>
|
|
<a href="ntqsqlquery.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="ntqsqlcursor.html">TQSqlCursor</a> cur( "prices" );
|
|
<a name="x2207"></a> cur.<a href="ntqsqlcursor.html#select">select</a>( "id=999" );
|
|
<a name="x2208"></a> if ( cur.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2206"></a> cur.<a href="ntqsqlcursor.html#primeDelete">primeDelete</a>();
|
|
<a name="x2205"></a> cur.<a href="ntqsqlcursor.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="ntqsqlcursor.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="ntqsqlquery.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">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include <<a href="qsqlcursor-h.html">ntqsqlcursor.h</a>>
|
|
#include <<a href="qdatatable-h.html">ntqdatatable.h</a>>
|
|
#include "../connection.h"
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv );
|
|
|
|
if ( createConnections() ) {
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> staffCursor( "staff" );
|
|
<a href="ntqdatatable.html">TQDataTable</a> *staffTable = new <a href="ntqdatatable.html">TQDataTable</a>( &staffCursor, TRUE );
|
|
app.<a href="ntqapplication.html#setMainWidget">setMainWidget</a>( staffTable );
|
|
<a name="x2211"></a> staffTable-><a href="ntqdatatable.html#refresh">refresh</a>();
|
|
staffTable-><a href="ntqwidget.html#show">show</a>();
|
|
|
|
return app.<a href="ntqapplication.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 require the <a href="qdatatable-h.html">ntqdatatable.h</a> and <a href="qsqlcursor-h.html">ntqsqlcursor.h</a> header
|
|
files. We create our application object, call createConnections() and
|
|
create the cursor. We create the <a href="ntqdatatable.html">TQDataTable</a> passing it a pointer to
|
|
the cursor, and set the autoPopulate flag to TRUE. Next we make our <a href="ntqdatatable.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="ntqdatatable.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="ntqsqlcursor.html">TQSqlCursor</a> staffCursor( "staff" );
|
|
<a href="ntqdatatable.html">TQDataTable</a> *staffTable = new <a href="ntqdatatable.html">TQDataTable</a>( &staffCursor );
|
|
|
|
app.<a href="ntqapplication.html#setMainWidget">setMainWidget</a>( staffTable );
|
|
|
|
<a name="x2214"></a> staffTable-><a href="ntqdatatable.html#addColumn">addColumn</a>( "forename", "Forename" );
|
|
staffTable-><a href="ntqdatatable.html#addColumn">addColumn</a>( "surname", "Surname" );
|
|
staffTable-><a href="ntqdatatable.html#addColumn">addColumn</a>( "salary", "Annual Salary" );
|
|
|
|
<a href="ntqstringlist.html">TQStringList</a> order = TQStringList() << "surname" << "forename";
|
|
<a name="x2216"></a> staffTable-><a href="ntqdatatable.html#setSort">setSort</a>( order );
|
|
|
|
<a name="x2215"></a> staffTable-><a href="ntqdatatable.html#refresh">refresh</a>();
|
|
staffTable-><a href="ntqwidget.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="ntqdatatable.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 quickly 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">ntqapplication.h</a>>
|
|
#include <<a href="qdialog-h.html">ntqdialog.h</a>>
|
|
#include <<a href="qlabel-h.html">ntqlabel.h</a>>
|
|
#include <<a href="qlayout-h.html">ntqlayout.h</a>>
|
|
#include <<a href="qlineedit-h.html">ntqlineedit.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include <<a href="qsqlcursor-h.html">ntqsqlcursor.h</a>>
|
|
#include <<a href="qsqlform-h.html">ntqsqlform.h</a>>
|
|
#include "../connection.h"
|
|
|
|
class FormDialog : public <a href="ntqdialog.html">TQDialog</a>
|
|
{
|
|
public:
|
|
FormDialog();
|
|
};
|
|
|
|
FormDialog::FormDialog()
|
|
{
|
|
<a href="ntqlabel.html">TQLabel</a> *forenameLabel = new <a href="ntqlabel.html">TQLabel</a>( "Forename:", this );
|
|
<a href="ntqlabel.html">TQLabel</a> *forenameDisplay = new <a href="ntqlabel.html">TQLabel</a>( this );
|
|
<a href="ntqlabel.html">TQLabel</a> *surnameLabel = new <a href="ntqlabel.html">TQLabel</a>( "Surname:", this );
|
|
<a href="ntqlabel.html">TQLabel</a> *surnameDisplay = new <a href="ntqlabel.html">TQLabel</a>( this );
|
|
<a href="ntqlabel.html">TQLabel</a> *salaryLabel = new <a href="ntqlabel.html">TQLabel</a>( "Salary:", this );
|
|
<a href="ntqlineedit.html">TQLineEdit</a> *salaryEdit = new <a href="ntqlineedit.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="ntqlayout.html#activate">activate</a>();
|
|
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> staffCursor( "staff" );
|
|
staffCursor.<a href="ntqsqlcursor.html#select">select</a>();
|
|
staffCursor.<a href="ntqsqlquery.html#next">next</a>();
|
|
|
|
<a href="ntqsqlform.html">TQSqlForm</a> sqlForm( this );
|
|
<a name="x2227"></a><a name="x2223"></a> sqlForm.<a href="ntqsqlform.html#setRecord">setRecord</a>( staffCursor.<a href="ntqsqlcursor.html#primeUpdate">primeUpdate</a>() );
|
|
<a name="x2225"></a> sqlForm.<a href="ntqsqlform.html#insert">insert</a>( forenameDisplay, "forename" );
|
|
sqlForm.<a href="ntqsqlform.html#insert">insert</a>( surnameDisplay, "surname" );
|
|
sqlForm.<a href="ntqsqlform.html#insert">insert</a>( salaryEdit, "salary" );
|
|
<a name="x2226"></a> sqlForm.<a href="ntqsqlform.html#readFields">readFields</a>();
|
|
}
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv );
|
|
|
|
if ( ! createConnections() ) return 1;
|
|
|
|
FormDialog *formDialog = new FormDialog();
|
|
<a name="x2220"></a> formDialog-><a href="ntqdialog.html#show">show</a>();
|
|
app.<a href="ntqapplication.html#setMainWidget">setMainWidget</a>( formDialog );
|
|
|
|
return app.<a href="ntqapplication.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">ntqsqldatabase.h</a> and <a href="qsqlcursor-h.html">ntqsqlcursor.h</a> as usual, but we now add
|
|
<a href="qsqlform-h.html">ntqsqlform.h</a>.
|
|
<p> The form will be presented as a dialog so we subclass <a href="ntqdialog.html">TQDialog</a> with
|
|
our own FormDialog class. We use a <a href="ntqlineedit.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="ntqsqlform.html">TQSqlForm</a> object and set the <a href="ntqsqlform.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="ntqsqlform.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="ntqdataview.html">TQDataView</a> is a Widget that can hold a read-only <a href="ntqsqlform.html">TQSqlForm</a>. In
|
|
addition to <a href="ntqsqlform.html">TQSqlForm</a> it offers the slot refresh( <a href="ntqsqlrecord.html">TQSqlRecord</a> * ) so it
|
|
can easily be linked together with a <a href="ntqdatatable.html">TQDataTable</a> to display a detailed
|
|
view of a record:
|
|
<p> <pre>
|
|
connect( myDataTable, TQ_SIGNAL( currentChanged( <a href="ntqsqlrecord.html">TQSqlRecord</a>* ) ),
|
|
myDataView, TQ_SLOT( refresh( <a href="ntqsqlrecord.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="ntqdialog.html">TQDialog</a>
|
|
{
|
|
<a href="metaobjects.html#TQ_OBJECT">TQ_OBJECT</a>
|
|
public:
|
|
FormDialog();
|
|
~FormDialog();
|
|
public slots:
|
|
void save();
|
|
private:
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> staffCursor;
|
|
<a href="ntqsqlform.html">TQSqlForm</a> *sqlForm;
|
|
<a href="ntqsqlindex.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="ntqsqlcursor.html">TQSqlCursor</a> and the
|
|
<a href="ntqsqlform.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="ntqlineedit.html#setAlignment">TQLineEdit::setAlignment</a>() and <a href="ntqlineedit.html#setValidator">TQLineEdit::setValidator</a>().
|
|
<p> <pre> <a href="ntqlineedit.html">TQLineEdit</a> *forenameEdit = new <a href="ntqlineedit.html">TQLineEdit</a>( this );
|
|
</pre>
|
|
<p> <pre> <a href="ntqpushbutton.html">TQPushButton</a> *saveButton = new <a href="ntqpushbutton.html">TQPushButton</a>( "&Save", this );
|
|
<a href="ntqobject.html#connect">connect</a>( saveButton, TQ_SIGNAL(<a href="ntqbutton.html#clicked">clicked</a>()), this, TQ_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="ntqlineedit.html">TQLineEdit</a>s to make them editable and have added a <a href="ntqpushbutton.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="ntqsqlindex.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="ntqsqlform.html">TQSqlForm</a>( this );
|
|
<a name="x2233"></a> sqlForm-><a href="ntqsqlform.html#setRecord">setRecord</a>( staffCursor.primeUpdate() );
|
|
</pre>
|
|
<p> We create a new <a href="ntqsqlform.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="ntqsqlform.html#insert">insert</a>( forenameEdit, "forename" );
|
|
sqlForm-><a href="ntqsqlform.html#insert">insert</a>( surnameEdit, "surname" );
|
|
sqlForm-><a href="ntqsqlform.html#insert">insert</a>( salaryEdit, "salary" );
|
|
<a name="x2232"></a> sqlForm-><a href="ntqsqlform.html#readFields">readFields</a>();
|
|
</pre>
|
|
<p> Now we link the buffer's fields to the <a href="ntqlineedit.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="ntqsqlform.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="ntqsqlrecord.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="ntqsqlindex.html">TQSqlIndex</a>
|
|
and move to the first record.
|
|
<p> <a href="ntqdatabrowser.html">TQDataBrowser</a> and <a href="ntqdataview.html">TQDataView</a> are widgets which provide a great deal of
|
|
the above functionality. <a href="ntqdatabrowser.html">TQDataBrowser</a> provides a data form which
|
|
allows editing of and navigation through a cursor's records. <a href="ntqdataview.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="ntqsqlform.html">TQSqlForm</a> uses <a href="ntqsqlpropertymap.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="ntqlineedit.html">TQLineEdit</a>
|
|
{
|
|
TQ_OBJECT
|
|
TQ_PROPERTY( TQString upperLine READ upperLine WRITE setUpperLine )
|
|
public:
|
|
CustomEdit( <a href="ntqwidget.html">TQWidget</a> *parent=0, const char *name=0 );
|
|
<a href="ntqstring.html">TQString</a> upperLine() const;
|
|
void setUpperLine( const <a href="ntqstring.html">TQString</a> &line );
|
|
public slots:
|
|
void changed( const <a href="ntqstring.html">TQString</a> &line );
|
|
private:
|
|
<a href="ntqstring.html">TQString</a> upperLineText;
|
|
};
|
|
</pre>
|
|
<p> We've created a simple subclass of <a href="ntqlineedit.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="ntqsqlpropertymap.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="ntqwidget.html">TQWidget</a> *parent, const char *name ) :
|
|
<a href="ntqlineedit.html">TQLineEdit</a>( parent, name )
|
|
{
|
|
<a href="ntqobject.html#connect">connect</a>( this, TQ_SIGNAL(<a href="ntqlineedit.html#textChanged">textChanged</a>(const <a href="ntqstring.html">TQString</a> &)),
|
|
this, TQ_SLOT(changed(const <a href="ntqstring.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="ntqstring.html">TQString</a> &line )
|
|
{
|
|
setUpperLine( line );
|
|
}
|
|
</pre>
|
|
<p> The changed() slot calls our setUpperLine() function.
|
|
<p> <pre> void CustomEdit::setUpperLine( const <a href="ntqstring.html">TQString</a> &line )
|
|
{
|
|
<a name="x2240"></a> upperLineText = line.<a href="ntqstring.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="ntqlineedit.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="ntqsqlpropertymap.html">TQSqlPropertyMap</a>;
|
|
<a name="x2239"></a><a name="x2235"></a> propMap-><a href="ntqsqlpropertymap.html#insert">insert</a>( forenameEdit-><a href="ntqobject.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="ntqsqlform.html">TQSqlForm</a>( this );
|
|
<a name="x2238"></a><a name="x2236"></a> sqlForm-><a href="ntqsqlform.html#setRecord">setRecord</a>( staffCursor-><a href="ntqsqlcursor.html#primeUpdate">primeUpdate</a>() );
|
|
<a name="x2237"></a> sqlForm-><a href="ntqsqlform.html#installPropertyMap">installPropertyMap</a>( propMap );
|
|
</pre>
|
|
<p> The final change is to install the property map into the <a href="ntqsqlform.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="ntqsqleditorfactory.html">TQSqlEditorFactory</a> to use custom editor widgets in
|
|
tables. In the following example we will create a custom editor based
|
|
on <a href="ntqcombobox.html">TQComboBox</a> and a TQSqlEditorFactory subclass to show how a <a href="ntqdatatable.html">TQDataTable</a>
|
|
can use a custom editor.
|
|
<p>
|
|
|
|
<pre> class StatusPicker : public <a href="ntqcombobox.html">TQComboBox</a>
|
|
{
|
|
TQ_OBJECT
|
|
TQ_PROPERTY( int statusid READ statusId WRITE setStatusId )
|
|
public:
|
|
StatusPicker( <a href="ntqwidget.html">TQWidget</a> *parent=0, const char *name=0 );
|
|
int statusId() const;
|
|
void setStatusId( int id );
|
|
private:
|
|
<a href="ntqmap.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="ntqmap.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="ntqsqleditorfactory.html">TQSqlEditorFactory</a>
|
|
{
|
|
TQ_OBJECT
|
|
public:
|
|
<a href="ntqwidget.html">TQWidget</a> *createEditor( <a href="ntqwidget.html">TQWidget</a> *parent, const <a href="ntqsqlfield.html">TQSqlField</a> *field );
|
|
};
|
|
</pre>
|
|
<p> We also need to subclass <a href="ntqsqleditorfactory.html">TQSqlEditorFactory</a> declaring a createEditor()
|
|
function since that is the only function we need to reimplement.
|
|
<p>
|
|
|
|
<pre> StatusPicker::StatusPicker( <a href="ntqwidget.html">TQWidget</a> *parent, const char *name )
|
|
: <a href="ntqcombobox.html">TQComboBox</a>( parent, name )
|
|
{
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> cur( "status" );
|
|
<a name="x2242"></a><a name="x2241"></a> cur.<a href="ntqsqlcursor.html#select">select</a>( cur.<a href="ntqsqlcursor.html#index">index</a>( "name" ) );
|
|
|
|
int i = 0;
|
|
while ( cur.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2243"></a> <a href="ntqcombobox.html#insertItem">insertItem</a>( cur.<a href="ntqsqlquery.html#value">value</a>( "name" ).toString(), i );
|
|
index2id[i] = cur.<a href="ntqsqlquery.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="ntqmap.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="ntqdatatable.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 requires us
|
|
to subclass TQDataTable and reimplement the paintField() function.
|
|
<p>
|
|
|
|
<pre> class CustomTable : public <a href="ntqdatatable.html">TQDataTable</a>
|
|
{
|
|
TQ_OBJECT
|
|
public:
|
|
CustomTable(
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> *cursor, bool autoPopulate = FALSE,
|
|
<a href="ntqwidget.html">TQWidget</a> * parent = 0, const char * name = 0 ) :
|
|
<a href="ntqdatatable.html">TQDataTable</a>( cursor, autoPopulate, parent, name ) {}
|
|
void paintField(
|
|
<a href="ntqpainter.html">TQPainter</a> * p, const <a href="ntqsqlfield.html">TQSqlField</a>* field, const <a href="ntqrect.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="ntqdatatable.html">TQDataTable</a> constructor without changing
|
|
anything. We also declare the paintField function.
|
|
<p>
|
|
|
|
<pre> <a name="x2245"></a>void CustomTable::<a href="ntqdatatable.html#paintField">paintField</a>( <a href="ntqpainter.html">TQPainter</a> * p, const <a href="ntqsqlfield.html">TQSqlField</a>* field,
|
|
const <a href="ntqrect.html">TQRect</a> & cr, bool b)
|
|
{
|
|
if ( !field )
|
|
return;
|
|
<a name="x2249"></a> if ( field-><a href="ntqsqlfield.html#name">name</a>() == "statusid" ) {
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT name FROM status WHERE id=" +
|
|
<a name="x2250"></a> field-><a href="ntqsqlfield.html#value">value</a>().toString() );
|
|
<a href="ntqstring.html">TQString</a> text;
|
|
if ( query.<a href="ntqsqlquery.html#next">next</a>() ) {
|
|
<a name="x2252"></a> text = query.<a href="ntqsqlquery.html#value">value</a>( 0 ).toString();
|
|
}
|
|
p-><a href="ntqpainter.html#drawText">drawText</a>( 2,2, cr.<a href="ntqrect.html#width">width</a>()-4, cr.<a href="ntqrect.html#height">height</a>()-4, fieldAlignment( field ), text );
|
|
}
|
|
else {
|
|
TQDataTable::<a href="ntqdatatable.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="ntqdatatable.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="ntqsqlquery.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="ntqsqlcursor.html">TQSqlCursor</a>
|
|
</h2>
|
|
<a name="9"></a><p>
|
|
|
|
<pre> #include <<a href="qapplication-h.html">ntqapplication.h</a>>
|
|
#include <<a href="qsqldatabase-h.html">ntqsqldatabase.h</a>>
|
|
#include <<a href="qsqlcursor-h.html">ntqsqlcursor.h</a>>
|
|
#include <<a href="qdatatable-h.html">ntqdatatable.h</a>>
|
|
#include "../connection.h"
|
|
|
|
int main( int argc, char *argv[] )
|
|
{
|
|
<a href="ntqapplication.html">TQApplication</a> app( argc, argv );
|
|
|
|
if ( createConnections() ) {
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a> invoiceItemCursor( "invoiceitem" );
|
|
|
|
<a href="ntqdatatable.html">TQDataTable</a> *invoiceItemTable = new <a href="ntqdatatable.html">TQDataTable</a>( &invoiceItemCursor );
|
|
|
|
app.<a href="ntqapplication.html#setMainWidget">setMainWidget</a>( invoiceItemTable );
|
|
|
|
<a name="x2255"></a> invoiceItemTable-><a href="ntqdatatable.html#addColumn">addColumn</a>( "pricesid", "PriceID" );
|
|
invoiceItemTable-><a href="ntqdatatable.html#addColumn">addColumn</a>( "quantity", "Quantity" );
|
|
invoiceItemTable-><a href="ntqdatatable.html#addColumn">addColumn</a>( "paiddate", "Paid" );
|
|
|
|
<a name="x2256"></a> invoiceItemTable-><a href="ntqdatatable.html#refresh">refresh</a>();
|
|
invoiceItemTable-><a href="ntqwidget.html#show">show</a>();
|
|
|
|
return app.<a href="ntqapplication.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="ntqdatatable.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="ntqsqlcursor.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="ntqsqlcursor.html">TQSqlCursor</a>.
|
|
<p>
|
|
|
|
<pre> InvoiceItemCursor::InvoiceItemCursor() :
|
|
<a href="ntqsqlcursor.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 require 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="ntqvariant.html">TQVariant</a> calculateField( const <a href="ntqstring.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="ntqsqlcursor.html">TQSqlCursor</a>( "invoiceitem" )
|
|
{
|
|
<a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> productName( "productname", TQVariant::String );
|
|
<a href="ntqsqlcursor.html#append">append</a>( productName );
|
|
<a name="x2260"></a> <a href="ntqsqlcursor.html#setCalculated">setCalculated</a>( productName.<a href="qsqlfieldinfo.html#name">name</a>(), TRUE );
|
|
}
|
|
|
|
<a name="x2259"></a>TQVariant InvoiceItemCursor::<a href="ntqsqlcursor.html#calculateField">calculateField</a>( const <a href="ntqstring.html">TQString</a> & name )
|
|
{
|
|
if ( name == "productname" ) {
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT name FROM prices WHERE id=" +
|
|
<a href="ntqsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
|
|
if ( query.<a href="ntqsqlquery.html#next">next</a>() )
|
|
<a name="x2262"></a> return query.<a href="ntqsqlquery.html#value">value</a>( 0 );
|
|
}
|
|
|
|
return TQVariant( <a href="ntqstring.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="ntqsqlfield.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="ntqdatatable.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 require some simple expansion. We'll look at each in turn.
|
|
<p>
|
|
|
|
<pre> InvoiceItemCursor::InvoiceItemCursor() :
|
|
<a href="ntqsqlcursor.html">TQSqlCursor</a>( "invoiceitem" )
|
|
{
|
|
<a href="qsqlfieldinfo.html">TQSqlFieldInfo</a> productName( "productname", TQVariant::String );
|
|
<a href="ntqsqlcursor.html#append">append</a>( productName );
|
|
<a name="x2264"></a> <a href="ntqsqlcursor.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="ntqsqlcursor.html#append">append</a>( productPrice );
|
|
<a href="ntqsqlcursor.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="ntqsqlcursor.html#append">append</a>( productCost );
|
|
<a href="ntqsqlcursor.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="ntqsqlcursor.html#calculateField">calculateField</a>( const <a href="ntqstring.html">TQString</a> & name )
|
|
{
|
|
|
|
if ( name == "productname" ) {
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT name FROM prices WHERE id=" +
|
|
<a href="ntqsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
|
|
<a name="x2265"></a> if ( query.<a href="ntqsqlquery.html#next">next</a>() )
|
|
<a name="x2266"></a> return query.<a href="ntqsqlquery.html#value">value</a>( 0 );
|
|
}
|
|
else if ( name == "price" ) {
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT price FROM prices WHERE id=" +
|
|
<a href="ntqsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
|
|
if ( query.<a href="ntqsqlquery.html#next">next</a>() )
|
|
return query.<a href="ntqsqlquery.html#value">value</a>( 0 );
|
|
}
|
|
else if ( name == "cost" ) {
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT price FROM prices WHERE id=" +
|
|
<a href="ntqsqlrecord.html#field">field</a>( "pricesid" )->value().toString() );
|
|
if ( query.<a href="ntqsqlquery.html#next">next</a>() )
|
|
return TQVariant( query.<a href="ntqsqlquery.html#value">value</a>( 0 ).toDouble() *
|
|
<a href="ntqsqlquery.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="ntqvariant.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="ntqsqlrecord.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="ntqsqlcursor.html#primeInsert">primeInsert</a>()
|
|
{
|
|
<a href="ntqsqlrecord.html">TQSqlRecord</a> *buffer = <a href="ntqsqlcursor.html#editBuffer">editBuffer</a>();
|
|
<a href="ntqsqlquery.html">TQSqlQuery</a> query( "SELECT NEXTVAL( 'invoiceitem_seq' )" );
|
|
<a name="x2268"></a> if ( query.<a href="ntqsqlquery.html#next">next</a>() )
|
|
<a name="x2270"></a><a name="x2269"></a> buffer-><a href="ntqsqlrecord.html#setValue">setValue</a>( "id", query.<a href="ntqsqlquery.html#value">value</a>( 0 ) );
|
|
buffer-><a href="ntqsqlrecord.html#setValue">setValue</a>( "paiddate", TQDate::currentDate() );
|
|
buffer-><a href="ntqsqlrecord.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>
|