1
/****************************************************************************
3
** Copyright (C) 1992-2005 Trolltech AS. All rights reserved.
5
** This file is part of the Qt 3 compatibility classes of the Qt Toolkit.
7
** This file may be distributed under the terms of the Q Public License
8
** as defined by Trolltech AS of Norway and appearing in the file
9
** LICENSE.QPL included in the packaging of this file.
11
** This file may be distributed and/or modified under the terms of the
12
** GNU General Public License version 2 as published by the Free Software
13
** Foundation and appearing in the file LICENSE.GPL included in the
14
** packaging of this file.
16
** See http://www.trolltech.com/pricing.html or email sales@trolltech.com for
17
** information about Qt Commercial License Agreements.
18
** See http://www.trolltech.com/qpl/ for QPL licensing information.
19
** See http://www.trolltech.com/gpl/ for GPL licensing information.
21
** Contact info@trolltech.com if any conditions of this licensing are
24
** This file is provided AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE
25
** WARRANTY OF DESIGN, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
27
****************************************************************************/
29
#include <qplatformdefs.h>
30
#include "q3sqlcursor.h"
34
#include "qsqldriver.h"
35
#include "qsqlresult.h"
36
#include "qdatetime.h"
37
#include "qsqldatabase.h"
39
#include "q3sqlrecordinfo.h"
40
#include "q3sqlfieldinfo.h"
42
class Q3SqlCursorPrivate
46
Q3SqlCursorPrivate(const QString& name, QSqlDatabase sdb)
47
: lastAt(QSql::BeforeFirst), nm(name), srt(name), md(0), db(sdb), q(0)
57
q = new QSqlQuery(QString(), db);
66
QSqlIndex priIndx; //primary index
67
QSqlRecord editBuffer;
68
// the primary index as it was before the user changed the values in editBuffer
70
Q3SqlRecordInfo infoBuffer;
75
QString qOrderByClause(const QSqlIndex & i, const QString& prefix = QString())
81
str = " order by " + i.toString(prefix);
85
QString qWhereClause(const QString& prefix, QSqlField* field, const QSqlDriver* driver)
88
if (field && driver) {
89
if (!prefix.isEmpty())
90
f += prefix + QString(".");
92
if (field->isNull()) {
95
f += " = " + driver->formatValue(field);
101
QString qWhereClause(QSqlRecord* rec, const QString& prefix, const QString& sep,
102
const QSqlDriver* driver)
104
static QString blank(" ");
106
bool separator = false;
107
for (int j = 0; j < rec->count(); ++j) {
108
QSqlField f = rec->field(j);
109
if (rec->isGenerated(j)) {
111
filter += sep + blank;
112
filter += qWhereClause(prefix, &f, driver);
121
\class Q3SqlCursor q3sqlcursor.h
122
\brief The Q3SqlCursor class provides browsing and editing of SQL
127
A Q3SqlCursor is a database record (see \l QSqlRecord) that
128
corresponds to a table or view within an SQL database (see \l
129
QSqlDatabase). There are two buffers in a cursor, one used for
130
browsing and one used for editing records. Each buffer contains a
131
list of fields which correspond to the fields in the table or
134
When positioned on a valid record, the browse buffer contains the
135
values of the current record's fields from the database. The edit
136
buffer is separate, and is used for editing existing records and
137
inserting new records.
139
For browsing data, a cursor must first select() data from the
140
database. After a successful select() the cursor is active
141
(isActive() returns true), but is initially not positioned on a
142
valid record (isValid() returns false). To position the cursor on
143
a valid record, use one of the navigation functions, next(),
144
previous(), first(), last(), or seek(). Once positioned on a valid
145
record, data can be retrieved from the browse buffer using
146
value(). If a navigation function is not successful, it returns
147
false, the cursor will no longer be positioned on a valid record
148
and the values returned by value() are undefined.
153
Q3SqlCursor cur( "staff" ); // Specify the table/view name
154
cur.select(); // We'll retrieve every record
155
while ( cur.next() ) {
156
qDebug( cur.value( "id" ).toString() + ": " +
157
cur.value( "surname" ).toString() + " " +
158
cur.value( "salary" ).toString() );
162
In the above example, a cursor is created specifying a table or
163
view name in the database. Then, select() is called, which can be
164
optionally parameterised to filter and order the records
165
retrieved. Each record in the cursor is retrieved using next().
166
When next() returns false, there are no more records to process,
167
and the loop terminates.
169
For editing records (rows of data), a cursor contains a separate
170
edit buffer which is independent of the fields used when browsing.
171
The functions insert(), update() and del() operate on the edit
172
buffer. This allows the cursor to be repositioned to other
173
records while simultaneously maintaining a separate buffer for
174
edits. You can get a pointer to the edit buffer using
175
editBuffer(). The primeInsert(), primeUpdate() and primeDelete()
176
functions also return a pointer to the edit buffer and prepare it
177
for insert, update and delete respectively. Edit operations only
178
affect a single row at a time. Note that update() and del()
179
require that the table or view contain a primaryIndex() to ensure
180
that edit operations affect a unique record within the database.
185
Q3SqlCursor cur( "prices" );
186
cur.select( "id=202" );
188
QSqlRecord *buffer = cur.primeUpdate();
189
double price = buffer->value( "price" ).toDouble();
190
double newprice = price * 1.05;
191
buffer->setValue( "price", newprice );
196
To edit an existing database record, first move to the record you
197
wish to update. Call primeUpdate() to get the pointer to the
198
cursor's edit buffer. Then use this pointer to modify the values
199
in the edit buffer. Finally, call update() to save the changes to
200
the database. The values in the edit buffer will be used to
201
locate the appropriate record when updating the database (see
204
Similarly, when deleting an existing database record, first move
205
to the record you wish to delete. Then, call primeDelete() to get
206
the pointer to the edit buffer. Finally, call del() to delete the
207
record from the database. Again, the values in the edit buffer
208
will be used to locate and delete the appropriate record.
210
To insert a new record, call primeInsert() to get the pointer to
211
the edit buffer. Use this pointer to populate the edit buffer
212
with new values and then insert() the record into the database.
214
After calling insert(), update() or del(), the cursor is no longer
215
positioned on a valid record and can no longer be navigated
216
(isValid() return false). The reason for this is that any changes
217
made to the database will not be visible until select() is called
218
to refresh the cursor. You can change this behavior by passing
219
false to insert(), update() or del() which will prevent the cursor
220
from becoming invalid. The edits will still not be visible when
221
navigating the cursor until select() is called.
223
Q3SqlCursor contains virtual methods which allow editing behavior
224
to be customized by subclasses. This allows custom cursors to be
225
created that encapsulate the editing behavior of a database table
226
for an entire application. For example, a cursor can be customized
227
to always auto-number primary index fields, or provide fields with
228
suitable default values, when inserting new records. Q3SqlCursor
229
generates SQL statements which are sent to the database engine;
230
you can control which fields are included in these statements
231
using setGenerated().
233
Note that Q3SqlCursor does not inherit from QObject. This means
234
that you are responsible for destroying instances of this class
235
yourself. However if you create a Q3SqlCursor and use it in a
236
\l Q3DataTable, \l Q3DataBrowser or a \l Q3DataView these classes will
237
usually take ownership of the cursor and destroy it when they
238
don't need it anymore. The documentation for Q3DataTable,
239
Q3DataBrowser and Q3DataView explicitly states which calls take
240
ownership of the cursor.
244
\enum Q3SqlCursor::Mode
246
This enum type describes how Q3SqlCursor operates on records in the
249
\value ReadOnly the cursor can only SELECT records from the
252
\value Insert the cursor can INSERT records into the database.
254
\value Update the cursor can UPDATE records in the database.
256
\value Delete the cursor can DELETE records from the database.
258
\value Writable the cursor can INSERT, UPDATE and DELETE records
263
\fn QVariant Q3SqlCursor::value(const QString &name) const
267
Returns the value of the field named \a name.
271
\fn void Q3SqlCursor::setValue(const QString &name, const QVariant &val)
275
Sets the value for the field named \a name to \a val.
279
Constructs a cursor on database \a db using table or view \a name.
281
If \a autopopulate is true (the default), the \a name of the
282
cursor must correspond to an existing table or view name in the
283
database so that field information can be automatically created.
284
If the table or view does not exist, the cursor will not be
287
The cursor is created with an initial mode of Q3SqlCursor::Writable
288
(meaning that records can be inserted, updated or deleted using
289
the cursor). If the cursor does not have a unique primary index,
290
update and deletes cannot be performed.
292
Note that \a autopopulate refers to populating the cursor with
293
meta-data, e.g. the names of the table's fields, not with
294
retrieving data. The select() function is used to populate the
297
\sa setName() setMode()
300
Q3SqlCursor::Q3SqlCursor(const QString & name, bool autopopulate, QSqlDatabase db)
301
: QSqlRecord(), QSqlQuery(QString(), db)
303
d = new Q3SqlCursorPrivate(name, db);
305
if (!d->nm.isEmpty())
306
setName(d->nm, autopopulate);
310
Constructs a copy of \a other.
313
Q3SqlCursor::Q3SqlCursor(const Q3SqlCursor & other)
314
: QSqlRecord(other), QSqlQuery(other)
316
d = new Q3SqlCursorPrivate(other.d->nm, other.d->db);
317
d->lastAt = other.d->lastAt;
319
d->srt = other.d->srt;
320
d->ftr = other.d->ftr;
321
d->priIndx = other.d->priIndx;
322
d->editBuffer = other.d->editBuffer;
323
d->infoBuffer = other.d->infoBuffer;
324
d->q = 0; // do not share queries
325
setMode(other.mode());
329
Destroys the object and frees any allocated resources.
332
Q3SqlCursor::~Q3SqlCursor()
338
Sets the cursor equal to \a other.
341
Q3SqlCursor& Q3SqlCursor::operator=(const Q3SqlCursor& other)
343
QSqlRecord::operator=(other);
344
QSqlQuery::operator=(other);
346
d = new Q3SqlCursorPrivate(other.d->nm, other.d->db);
347
d->lastAt = other.d->lastAt;
349
d->srt = other.d->srt;
350
d->ftr = other.d->ftr;
351
d->priIndx = other.d->priIndx;
352
d->editBuffer = other.d->editBuffer;
353
d->infoBuffer = other.d->infoBuffer;
354
d->q = 0; // do not share queries
355
setMode(other.mode());
360
Sets the current sort to \a sort. Note that no new records are
361
selected. To select new records, use select(). The \a sort will
362
apply to any subsequent select() calls that do not explicitly
366
void Q3SqlCursor::setSort(const QSqlIndex& sort)
372
Returns the current sort, or an empty index if there is no current
375
QSqlIndex Q3SqlCursor::sort() const
381
Sets the current filter to \a filter. Note that no new records are
382
selected. To select new records, use select(). The \a filter will
383
apply to any subsequent select() calls that do not explicitly
386
The filter is a SQL \c WHERE clause without the keyword 'WHERE',
387
e.g. \c{name='Dave'} which will be processed by the DBMS.
389
void Q3SqlCursor::setFilter(const QString& filter)
395
Returns the current filter, or an empty string if there is no
398
QString Q3SqlCursor::filter() const
404
Sets the name of the cursor to \a name. If \a autopopulate is true
405
(the default), the \a name must correspond to a valid table or
406
view name in the database. Also, note that all references to the
407
cursor edit buffer become invalidated when fields are
408
auto-populated. See the Q3SqlCursor constructor documentation for
411
void Q3SqlCursor::setName(const QString& name, bool autopopulate)
416
d->infoBuffer = driver()->record(name);
417
*this = d->infoBuffer.toRecord();
418
d->editBuffer = *this;
419
d->priIndx = driver()->primaryIndex(name);
422
qWarning("Q3SqlCursor::setName: unable to build record, does '%s' exist?", name.latin1());
427
Returns the name of the cursor.
430
QString Q3SqlCursor::name() const
438
QString Q3SqlCursor::toString(const QString& prefix, const QString& sep) const
441
QString pfix = prefix.isEmpty() ? prefix : prefix + ".";
444
for (int i = 0; i < count(); ++i) {
445
const QString fname = fieldName(i);
446
if (isGenerated(i)) {
449
pflist += pfix + fname;
459
Assigns the record \a list.
462
QSqlRecord & Q3SqlCursor::operator=(const QSqlRecord & list)
464
return QSqlRecord::operator=(list);
468
Append a copy of field \a fieldInfo to the end of the cursor. Note
469
that all references to the cursor edit buffer become invalidated.
472
void Q3SqlCursor::append(const Q3SqlFieldInfo& fieldInfo)
474
d->editBuffer.append(fieldInfo.toField());
475
d->infoBuffer.append(fieldInfo);
476
QSqlRecord::append(fieldInfo.toField());
480
Removes all fields from the cursor. Note that all references to
481
the cursor edit buffer become invalidated.
483
void Q3SqlCursor::clear()
485
d->editBuffer.clear();
486
d->infoBuffer.clear();
492
Insert a copy of \a fieldInfo at position \a pos. If a field
493
already exists at \a pos, it is removed. Note that all references
494
to the cursor edit buffer become invalidated.
497
void Q3SqlCursor::insert(int pos, const Q3SqlFieldInfo& fieldInfo)
499
d->editBuffer.replace(pos, fieldInfo.toField());
500
d->infoBuffer[pos] = fieldInfo;
501
QSqlRecord::replace(pos, fieldInfo.toField());
505
Removes the field at \a pos. If \a pos does not exist, nothing
506
happens. Note that all references to the cursor edit buffer become
510
void Q3SqlCursor::remove(int pos)
512
d->editBuffer.remove(pos);
513
d->infoBuffer[pos] = Q3SqlFieldInfo();
514
QSqlRecord::remove(pos);
518
Sets the generated flag for the field \a name to \a generated. If
519
the field does not exist, nothing happens. Only fields that have
520
\a generated set to true are included in the SQL that is
524
void Q3SqlCursor::setGenerated(const QString& name, bool generated)
526
int pos = indexOf(name);
529
QSqlRecord::setGenerated(name, generated);
530
d->editBuffer.setGenerated(name, generated);
531
d->infoBuffer[pos].setGenerated(generated);
537
Sets the generated flag for the field \a i to \a generated.
539
void Q3SqlCursor::setGenerated(int i, bool generated)
541
if (i < 0 || i >= (int)d->infoBuffer.count())
543
QSqlRecord::setGenerated(i, generated);
544
d->editBuffer.setGenerated(i, generated);
545
d->infoBuffer[i].setGenerated(generated);
549
Returns the primary index associated with the cursor as defined in
550
the database, or an empty index if there is no primary index. If
551
\a setFromCursor is true (the default), the index fields are
552
populated with the corresponding values in the cursor's current
556
QSqlIndex Q3SqlCursor::primaryIndex(bool setFromCursor) const
559
for (int i = 0; i < d->priIndx.count(); ++i) {
560
const QString fn = d->priIndx.fieldName(i);
562
d->priIndx.setValue(i, QSqlRecord::value(fn));
569
Sets the primary index associated with the cursor to the index \a
570
idx. Note that this index must contain a field or set of fields
571
which identify a unique record within the underlying database
572
table or view so that update() and del() will execute as expected.
577
void Q3SqlCursor::setPrimaryIndex(const QSqlIndex& idx)
584
Returns an index composed of \a fieldNames, all in ASCending
585
order. Note that all field names must exist in the cursor,
586
otherwise an empty index is returned.
591
QSqlIndex Q3SqlCursor::index(const QStringList& fieldNames) const
594
for (QStringList::ConstIterator it = fieldNames.begin(); it != fieldNames.end(); ++it) {
595
QSqlField f = field((*it));
596
if (!f.isValid()) { /* all fields must exist */
608
Returns an index based on \a fieldName.
611
QSqlIndex Q3SqlCursor::index(const QString& fieldName) const
613
QStringList fl(fieldName);
618
Selects all fields in the cursor from the database matching the
619
filter criteria \a filter. The data is returned in the order
620
specified by the index \a sort. Returns true if the data was
621
successfully selected; otherwise returns false.
623
The \a filter is a string containing a SQL \c WHERE clause but
624
without the 'WHERE' keyword. The cursor is initially positioned at
625
an invalid row after this function is called. To move to a valid
626
row, use seek(), first(), last(), previous() or next().
630
Q3SqlCursor cur("Employee"); // Use the Employee table or view
631
cur.select("deptno=10"); // select all records in department 10
636
// select records in other departments, ordered by department number
637
cur.select("deptno>10", cur.index("deptno"));
641
The filter will apply to any subsequent select() calls that do not
642
explicitly specify another filter. Similarly the sort will apply
643
to any subsequent select() calls that do not explicitly specify
647
Q3SqlCursor cur("Employee");
648
cur.select("deptno=10"); // select all records in department 10
653
cur.select(); // re-selects all records in department 10
659
bool Q3SqlCursor::select(const QString & filter, const QSqlIndex & sort)
661
QString fieldList = toString(d->nm);
662
if (fieldList.isEmpty())
664
QString str= "select " + fieldList;
665
str += " from " + d->nm;
666
if (!filter.isEmpty()) {
668
str += " where " + filter;
671
if (sort.count() > 0)
672
str += " order by " + sort.toString(d->nm);
680
Selects all fields in the cursor from the database. The rows are
681
returned in the order specified by the last call to setSort() or
682
the last call to select() that specified a sort, whichever is the
683
most recent. If there is no current sort, the order in which the
684
rows are returned is undefined. The records are filtered according
685
to the filter specified by the last call to setFilter() or the
686
last call to select() that specified a filter, whichever is the
687
most recent. If there is no current filter, all records are
688
returned. The cursor is initially positioned at an invalid row. To
689
move to a valid row, use seek(), first(), last(), previous() or
692
\sa setSort() setFilter()
695
bool Q3SqlCursor::select()
697
return select(filter(), sort());
703
Selects all fields in the cursor from the database. The data is
704
returned in the order specified by the index \a sort. The records
705
are filtered according to the filter specified by the last call to
706
setFilter() or the last call to select() that specified a filter,
707
whichever is the most recent. The cursor is initially positioned
708
at an invalid row. To move to a valid row, use seek(), first(),
709
last(), previous() or next().
712
bool Q3SqlCursor::select(const QSqlIndex& sort)
714
return select(filter(), sort);
720
Selects all fields in the cursor matching the filter index \a
721
filter. The data is returned in the order specified by the index
722
\a sort. The \a filter index works by constructing a WHERE clause
723
using the names of the fields from the \a filter and their values
724
from the current cursor record. The cursor is initially positioned
725
at an invalid row. To move to a valid row, use seek(), first(),
726
last(), previous() or next(). This function is useful, for example,
727
for retrieving data based upon a table's primary index:
730
Q3SqlCursor cur("Employee");
731
QSqlIndex pk = cur.primaryIndex();
732
cur.setValue("id", 10);
733
cur.select(pk, pk); // generates "SELECT ... FROM Employee WHERE id=10 ORDER BY id"
737
In this example the QSqlIndex, pk, is used for two different
738
purposes. When used as the filter (first) argument, the field
739
names it contains are used to construct the WHERE clause, each set
740
to the current cursor value, \c{WHERE id=10}, in this case. When
741
used as the sort (second) argument the field names it contains are
742
used for the ORDER BY clause, \c{ORDER BY id} in this example.
745
bool Q3SqlCursor::select(const QSqlIndex & filter, const QSqlIndex & sort)
747
return select(toString(filter, this, d->nm, "=", "and"), sort);
751
Sets the cursor mode to \a mode. This value can be an OR'ed
752
combination of \l Q3SqlCursor::Mode values. The default mode for a
753
cursor is \c Q3SqlCursor::Writable.
756
Q3SqlCursor cur("Employee");
757
cur.setMode(Q3SqlCursor::Writable); // allow insert/update/delete
759
cur.setMode(Q3SqlCursor::Insert | Q3SqlCursor::Update); // allow inserts and updates only
761
cur.setMode(Q3SqlCursor::ReadOnly); // no inserts/updates/deletes allowed
766
void Q3SqlCursor::setMode(int mode)
772
Returns the current cursor mode.
777
int Q3SqlCursor::mode() const
783
Sets field \a name to \a calculated. If the field \a name does not
784
exist, nothing happens. The value of a calculated field is set by
785
the calculateField() virtual function which you must reimplement
786
(or the field value will be an invalid QVariant). Calculated
787
fields do not appear in generated SQL statements sent to the
793
void Q3SqlCursor::setCalculated(const QString& name, bool calculated)
795
int pos = indexOf(name);
798
d->infoBuffer[pos].setCalculated(calculated);
800
setGenerated(pos, false);
804
Returns true if the field \a name exists and is calculated;
805
otherwise returns false.
810
bool Q3SqlCursor::isCalculated(const QString& name) const
812
int pos = indexOf(name);
815
return d->infoBuffer[pos].isCalculated();
819
Sets field \a{name}'s trimmed status to \a trim. If the field \a
820
name does not exist, nothing happens.
822
When a trimmed field of type string is read from the
823
database any trailing (right-most) spaces are removed.
825
\sa isTrimmed() QVariant
828
void Q3SqlCursor::setTrimmed(const QString& name, bool trim)
830
int pos = indexOf(name);
833
d->infoBuffer[pos].setTrim(trim);
837
Returns true if the field \a name exists and is trimmed; otherwise
840
When a trimmed field of type string or cstring is read from the
841
database any trailing (right-most) spaces are removed.
846
bool Q3SqlCursor::isTrimmed(const QString& name) const
848
int pos = indexOf(name);
851
return d->infoBuffer[pos].isTrim();
855
Returns true if the cursor is read-only; otherwise returns false.
856
The default is false. Read-only cursors cannot be edited using
857
insert(), update() or del().
862
bool Q3SqlCursor::isReadOnly() const
868
Returns true if the cursor will perform inserts; otherwise returns
874
bool Q3SqlCursor::canInsert() const
876
return ((d->md & Insert) == Insert) ;
881
Returns true if the cursor will perform updates; otherwise returns
887
bool Q3SqlCursor::canUpdate() const
889
return ((d->md & Update) == Update) ;
893
Returns true if the cursor will perform deletes; otherwise returns
899
bool Q3SqlCursor::canDelete() const
901
return ((d->md & Delete) == Delete) ;
907
Returns a formatted string composed of the \a prefix (e.g. table
908
or view name), ".", the \a field name, the \a fieldSep and the
909
field value. If the \a prefix is empty then the string will begin
910
with the \a field name. This function is useful for generating SQL
914
QString Q3SqlCursor::toString(const QString& prefix, QSqlField* field, const QString& fieldSep) const
917
if (field && driver()) {
918
f = (prefix.length() > 0 ? prefix + QString(".") : QString()) + field->name();
919
f += " " + fieldSep + " ";
920
if (field->isNull()) {
923
f += driver()->formatValue(field);
930
Returns a formatted string composed of all the fields in \a rec.
931
Each field is composed of the \a prefix (e.g. table or view name),
932
".", the field name, the \a fieldSep and the field value. If the
933
\a prefix is empty then each field will begin with the field name.
934
The fields are then joined together separated by \a sep. Fields
935
where isGenerated() returns false are not included. This function
936
is useful for generating SQL statements.
939
QString Q3SqlCursor::toString(QSqlRecord* rec, const QString& prefix, const QString& fieldSep,
940
const QString& sep) const
942
static QString blank(" ");
944
bool separator = false;
945
for (int j = 0; j < count(); ++j) {
946
QSqlField f = rec->field(j);
947
if (rec->isGenerated(j)) {
949
filter += sep + blank;
950
filter += toString(prefix, &f, fieldSep);
961
Returns a formatted string composed of all the fields in the index
962
\a i. Each field is composed of the \a prefix (e.g. table or view
963
name), ".", the field name, the \a fieldSep and the field value.
964
If the \a prefix is empty then each field will begin with the field
965
name. The field values are taken from \a rec. The fields are then
966
joined together separated by \a sep. Fields where isGenerated()
967
returns false are ignored. This function is useful for generating
971
QString Q3SqlCursor::toString(const QSqlIndex& i, QSqlRecord* rec, const QString& prefix,
972
const QString& fieldSep, const QString& sep) const
975
bool separator = false;
976
for(int j = 0; j < i.count(); ++j){
977
if (rec->isGenerated(j)) {
979
filter += " " + sep + " " ;
981
QString fn = i.fieldName(j);
982
QSqlField f = rec->field(fn);
983
filter += toString(prefix, &f, fieldSep);
991
Inserts the current contents of the cursor's edit record buffer
992
into the database, if the cursor allows inserts. Returns the
993
number of rows affected by the insert. For error information, use
996
If \a invalidate is true (the default), the cursor will no longer
997
be positioned on a valid record and can no longer be navigated. A
998
new select() call must be made before navigating to a valid
1002
Q3SqlCursor cur("prices");
1003
QSqlRecord *buffer = cur.primeInsert();
1004
buffer->setValue("id", 53981);
1005
buffer->setValue("name", "Thingy");
1006
buffer->setValue("price", 105.75);
1010
In the above example, a cursor is created on the 'prices' table
1011
and a pointer to the insert buffer is aquired using primeInsert().
1012
Each field's value is set to the desired value and then insert()
1013
is called to insert the data into the database. Remember: all edit
1014
operations (insert(), update() and delete()) operate on the
1015
contents of the cursor edit buffer and not on the contents of the
1018
\sa setMode() lastError()
1021
int Q3SqlCursor::insert(bool invalidate)
1023
if ((d->md & Insert) != Insert || !driver())
1025
int k = d->editBuffer.count();
1032
// use a prepared query if the driver supports it
1033
if (driver()->hasFeature(QSqlDriver::PreparedQueries)) {
1035
bool oraStyle = driver()->hasFeature(QSqlDriver::NamedPlaceholders);
1036
for(int j = 0; j < k; ++j) {
1037
QSqlField f = d->editBuffer.field(j);
1038
if (d->editBuffer.isGenerated(j)) {
1044
vList += (oraStyle == true) ? ":f" + QString::number(cnt) : QString("?");
1053
str.append("insert into ").append(name()).append(" (").append(fList).append(") values (").append(vList). append (")");
1054
return applyPrepared(str, invalidate);
1056
for(int j = 0; j < k; ++j) {
1057
QSqlField f = d->editBuffer.field(j);
1058
if (d->editBuffer.isGenerated(j)) {
1064
vList += driver()->formatValue(&f);
1070
// no valid fields found
1074
str.append("insert into ").append(name()).append(" (").append(fList).append(") values (").append(vList). append (")");
1075
return apply(str, invalidate);
1080
Returns the current internal edit buffer. If \a copy is true (the
1081
default is false), the current cursor field values are first
1082
copied into the edit buffer. The edit buffer is valid as long as
1083
the cursor remains valid. The cursor retains ownership of the
1084
returned pointer, so it must not be deleted or modified.
1086
\sa primeInsert(), primeUpdate() primeDelete()
1089
QSqlRecord* Q3SqlCursor::editBuffer(bool copy)
1093
for(int i = 0; i < d->editBuffer.count(); i++) {
1094
if (QSqlRecord::isNull(i)) {
1095
d->editBuffer.setNull(i);
1097
d->editBuffer.setValue(i, value(i));
1101
return &d->editBuffer;
1105
This function primes the edit buffer's field values for update and
1106
returns the edit buffer. The default implementation copies the
1107
field values from the current cursor record into the edit buffer
1108
(therefore, this function is equivalent to calling editBuffer(
1109
true)). The cursor retains ownership of the returned pointer, so
1110
it must not be deleted or modified.
1112
\sa editBuffer() update()
1115
QSqlRecord* Q3SqlCursor::primeUpdate()
1117
// memorize the primary keys as they were before the user changed the values in editBuffer
1118
QSqlRecord* buf = editBuffer(true);
1119
QSqlIndex idx = primaryIndex(false);
1121
d->editIndex = toString(idx, buf, d->nm, "=", "and");
1123
d->editIndex = qWhereClause(buf, d->nm, "and", driver());
1128
This function primes the edit buffer's field values for delete and
1129
returns the edit buffer. The default implementation copies the
1130
field values from the current cursor record into the edit buffer
1131
(therefore, this function is equivalent to calling editBuffer(
1132
true)). The cursor retains ownership of the returned pointer, so
1133
it must not be deleted or modified.
1135
\sa editBuffer() del()
1138
QSqlRecord* Q3SqlCursor::primeDelete()
1140
return editBuffer(true);
1144
This function primes the edit buffer's field values for insert and
1145
returns the edit buffer. The default implementation clears all
1146
field values in the edit buffer. The cursor retains ownership of
1147
the returned pointer, so it must not be deleted or modified.
1149
\sa editBuffer() insert()
1152
QSqlRecord* Q3SqlCursor::primeInsert()
1154
d->editBuffer.clearValues();
1155
return &d->editBuffer;
1160
Updates the database with the current contents of the edit buffer.
1161
Returns the number of records which were updated.
1162
For error information, use lastError().
1164
Only records which meet the filter criteria specified by the
1165
cursor's primary index are updated. If the cursor does not contain
1166
a primary index, no update is performed and 0 is returned.
1168
If \a invalidate is true (the default), the current cursor can no
1169
longer be navigated. A new select() call must be made before you
1170
can move to a valid record. For example:
1173
Q3SqlCursor cur("prices");
1174
cur.select("id=202");
1176
QSqlRecord *buffer = cur.primeUpdate();
1177
double price = buffer->value("price").toDouble();
1178
double newprice = price * 1.05;
1179
buffer->setValue("price", newprice);
1184
In the above example, a cursor is created on the 'prices' table
1185
and is positioned on the record to be updated. Then a pointer to
1186
the cursor's edit buffer is acquired using primeUpdate(). A new
1187
value is calculated and placed into the edit buffer with the
1188
setValue() call. Finally, an update() call is made on the cursor
1189
which uses the tables's primary index to update the record in the
1190
database with the contents of the cursor's edit buffer. Remember:
1191
all edit operations (insert(), update() and delete()) operate on
1192
the contents of the cursor edit buffer and not on the contents of
1195
Note that if the primary index does not uniquely distinguish
1196
records the database may be changed into an inconsistent state.
1198
\sa setMode() lastError()
1201
int Q3SqlCursor::update(bool invalidate)
1203
if (d->editIndex.isEmpty())
1205
return update(d->editIndex, invalidate);
1211
Updates the database with the current contents of the cursor edit
1212
buffer using the specified \a filter. Returns the number of
1213
records which were updated.
1214
For error information, use lastError().
1216
Only records which meet the filter criteria are updated, otherwise
1217
all records in the table are updated.
1219
If \a invalidate is true (the default), the cursor can no longer
1220
be navigated. A new select() call must be made before you can move
1223
\sa primeUpdate() setMode() lastError()
1226
int Q3SqlCursor::update(const QString & filter, bool invalidate)
1228
if ((d->md & Update) != Update) {
1236
// use a prepared query if the driver supports it
1237
if (driver()->hasFeature(QSqlDriver::PreparedQueries)) {
1241
bool oraStyle = driver()->hasFeature(QSqlDriver::NamedPlaceholders);
1242
for(int j = 0; j < k; ++j) {
1243
QSqlField f = d->editBuffer.field(j);
1244
if (d->editBuffer.isGenerated(j)) {
1248
fList += f.name() + " = " + (oraStyle == true ? ":f" + QString::number(cnt) : QString("?"));
1256
QString str = "update " + name() + " set " + fList;
1257
if (filter.length()) {
1258
str+= " where " + filter;
1260
return applyPrepared(str, invalidate);
1262
QString str = "update " + name();
1263
str += " set " + toString(&d->editBuffer, QString(), "=", ",");
1264
if (filter.length()) {
1265
str+= " where " + filter;
1267
return apply(str, invalidate);
1272
Deletes a record from the database using the cursor's primary
1273
index and the contents of the cursor edit buffer. Returns the
1274
number of records which were deleted.
1275
For error information, use lastError().
1277
Only records which meet the filter criteria specified by the
1278
cursor's primary index are deleted. If the cursor does not contain
1279
a primary index, no delete is performed and 0 is returned. If \a
1280
invalidate is true (the default), the current cursor can no longer
1281
be navigated. A new select() call must be made before you can move
1282
to a valid record. For example:
1285
Q3SqlCursor cur("prices");
1286
cur.select("id=999");
1293
In the above example, a cursor is created on the 'prices' table
1294
and positioned to the record to be deleted. First primeDelete() is
1295
called to populate the edit buffer with the current cursor values,
1296
e.g. with an id of 999, and then del() is called to actually
1297
delete the record from the database. Remember: all edit operations
1298
(insert(), update() and delete()) operate on the contents of the
1299
cursor edit buffer and not on the contents of the cursor itself.
1301
\sa primeDelete() setMode() lastError()
1304
int Q3SqlCursor::del(bool invalidate)
1306
QSqlIndex idx = primaryIndex(false);
1308
return del(qWhereClause(&d->editBuffer, d->nm, "and", driver()), invalidate);
1309
return del(toString(primaryIndex(), &d->editBuffer, d->nm, "=", "and"), invalidate);
1315
Deletes the current cursor record from the database using the
1316
filter \a filter. Only records which meet the filter criteria are
1317
deleted. Returns the number of records which were deleted. If \a
1318
invalidate is true (the default), the current cursor can no longer
1319
be navigated. A new select() call must be made before you can move
1320
to a valid record. For error information, use lastError().
1322
The \a filter is an SQL \c WHERE clause, e.g. \c{id=500}.
1324
\sa setMode() lastError()
1327
int Q3SqlCursor::del(const QString & filter, bool invalidate)
1329
if ((d->md & Delete) != Delete)
1332
if(k == 0) return 0;
1333
QString str = "delete from " + name();
1334
if (filter.length())
1335
str+= " where " + filter;
1336
return apply(str, invalidate);
1343
int Q3SqlCursor::apply(const QString& q, bool invalidate)
1348
ar = numRowsAffected();
1349
} else if (driver()) {
1350
QSqlQuery* sql = d->query();
1351
if (sql && sql->exec(q))
1352
ar = sql->numRowsAffected();
1361
int Q3SqlCursor::applyPrepared(const QString& q, bool invalidate)
1367
sql = (QSqlQuery*)this;
1368
d->lastAt = QSql::BeforeFirst;
1375
if (invalidate || sql->lastQuery() != q) {
1376
if (!sql->prepare(q))
1381
int fieldCount = (int)count();
1382
for (int j = 0; j < fieldCount; ++j) {
1383
const QSqlField f = d->editBuffer.field(j);
1384
if (d->editBuffer.isGenerated(j)) {
1385
if (f.type() == QVariant::ByteArray)
1386
sql->bindValue(cnt, f.value(), QSql::In | QSql::Binary);
1388
sql->bindValue(cnt, f.value());
1393
ar = sql->numRowsAffected();
1399
Executes the SQL query \a sql. Returns true of the cursor is
1400
active, otherwise returns false.
1402
bool Q3SqlCursor::exec(const QString & sql)
1404
d->lastAt = QSql::BeforeFirst;
1405
QSqlQuery::exec(sql);
1410
Protected virtual function which is called whenever a field needs
1411
to be calculated. If calculated fields are being used, derived
1412
classes must reimplement this function and return the appropriate
1413
value for field \a name. The default implementation returns an
1419
QVariant Q3SqlCursor::calculateField(const QString&)
1425
Ensure fieldlist is synced with query.
1429
static QString qTrim(const QString& s)
1432
int end = result.length() - 1;
1433
while (end >= 0 && result[end].isSpace()) // skip white space from end
1435
result.truncate(end + 1);
1442
void Q3SqlCursor::sync()
1444
if (isActive() && isValid() && d->lastAt != at()) {
1448
bool haveCalculatedFields = false;
1449
for (; i < count(); ++i) {
1450
if (!haveCalculatedFields && d->infoBuffer[i].isCalculated()) {
1451
haveCalculatedFields = true;
1453
if (QSqlRecord::isGenerated(i)) {
1454
QVariant v = QSqlQuery::value(j);
1455
if ((v.type() == QVariant::String) &&
1456
d->infoBuffer[i].isTrim()) {
1457
v = qTrim(v.toString());
1459
QSqlRecord::setValue(i, v);
1460
if (QSqlQuery::isNull(j))
1461
QSqlRecord::field(i).clear();
1465
if (haveCalculatedFields) {
1466
for (i = 0; i < count(); ++i) {
1467
if (d->infoBuffer[i].isCalculated())
1468
QSqlRecord::setValue(i, calculateField(fieldName(i)));
1475
Returns the value of field number \a i.
1478
QVariant Q3SqlCursor::value(int i) const
1480
const_cast<Q3SqlCursor *>(this)->sync();
1481
return QSqlRecord::value(i);
1485
cursors should be filled with Q3SqlFieldInfos...
1487
void Q3SqlCursor::append(const QSqlField& field)
1489
append(Q3SqlFieldInfo(field));
1493
Returns true if the field \a i is NULL or if there is no field at
1494
position \a i; otherwise returns false.
1496
This is the same as calling QSqlRecord::isNull(\a i)
1498
bool Q3SqlCursor::isNull(int i) const
1500
const_cast<Q3SqlCursor *>(this)->sync();
1501
return QSqlRecord::isNull(i);
1506
Returns true if the field called \a name is NULL or if there is no
1507
field called \a name; otherwise returns false.
1509
This is the same as calling QSqlRecord::isNull(\a name)
1511
bool Q3SqlCursor::isNull(const QString& name) const
1513
const_cast<Q3SqlCursor *>(this)->sync();
1514
return QSqlRecord::isNull(name);
1518
void Q3SqlCursor::setValue(int i, const QVariant& val)
1522
qDebug("Q3SqlCursor::setValue(): This will not affect actual database values. Use primeInsert(), primeUpdate() or primeDelete().");
1524
QSqlRecord::setValue(i, val);
1528
bool Q3SqlCursor::seek(int i, bool relative)
1530
bool res = QSqlQuery::seek(i, relative);
1536
bool Q3SqlCursor::next()
1538
bool res = QSqlQuery::next();
1544
\fn Q3SqlCursor::previous()
1550
bool Q3SqlCursor::prev()
1552
bool res = QSqlQuery::previous();
1558
bool Q3SqlCursor::first()
1560
bool res = QSqlQuery::first();
1566
bool Q3SqlCursor::last()
1568
bool res = QSqlQuery::last();
added
removed
src/qt3support/sql/q3sqlcursor.cpp
