1 /****************************************************************************
2 **
3 ** Copyright (C) 2015 The Qt Company Ltd.
4 ** Contact: http://www.qt.io/licensing/
5 **
6 ** This file is part of the QtSql module of the Qt Toolkit.
7 **
8 ** $QT_BEGIN_LICENSE:LGPL$
9 ** Commercial License Usage
10 ** Licensees holding valid commercial Qt licenses may use this file in
11 ** accordance with the commercial license agreement provided with the
12 ** Software or, alternatively, in accordance with the terms contained in
13 ** a written agreement between you and The Qt Company. For licensing terms
14 ** and conditions see http://www.qt.io/terms-conditions. For further
15 ** information use the contact form at http://www.qt.io/contact-us.
16 **
17 ** GNU Lesser General Public License Usage
18 ** Alternatively, this file may be used under the terms of the GNU Lesser
19 ** General Public License version 2.1 or version 3 as published by the Free
20 ** Software Foundation and appearing in the file LICENSE.LGPLv21 and
21 ** LICENSE.LGPLv3 included in the packaging of this file. Please review the
22 ** following information to ensure the GNU Lesser General Public License
23 ** requirements will be met: https://www.gnu.org/licenses/lgpl.html and
24 ** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
25 **
26 ** As a special exception, The Qt Company gives you certain additional
27 ** rights. These rights are described in The Qt Company LGPL Exception
28 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
29 **
30 ** GNU General Public License Usage
31 ** Alternatively, this file may be used under the terms of the GNU
32 ** General Public License version 3.0 as published by the Free Software
33 ** Foundation and appearing in the file LICENSE.GPL included in the
34 ** packaging of this file. Please review the following information to
35 ** ensure the GNU General Public License version 3.0 requirements will be
36 ** met: http://www.gnu.org/copyleft/gpl.html.
37 **
38 ** $QT_END_LICENSE$
39 **
40 ****************************************************************************/
41
42 #include "qsqlquery.h"
43
44 //#define QT_DEBUG_SQL
45
46 #include "qatomic.h"
47 #include "qsqlrecord.h"
48 #include "qsqlresult.h"
49 #include "qsqldriver.h"
50 #include "qsqldatabase.h"
51 #include "private/qsqlnulldriver_p.h"
52 #include "qvector.h"
53 #include "qmap.h"
54
55 QT_BEGIN_NAMESPACE
56
57 class QSqlQueryPrivate
58 {
59 public:
60 QSqlQueryPrivate(QSqlResult* result);
61 ~QSqlQueryPrivate();
62 QAtomicInt ref;
63 QSqlResult* sqlResult;
64
65 static QSqlQueryPrivate* shared_null();
66 };
67
68 Q_GLOBAL_STATIC_WITH_ARGS(QSqlQueryPrivate, nullQueryPrivate, (0))
Q_GLOBAL_STATIC(QSqlNullDriver,nullDriver)69 Q_GLOBAL_STATIC(QSqlNullDriver, nullDriver)
70 Q_GLOBAL_STATIC_WITH_ARGS(QSqlNullResult, nullResult, (nullDriver()))
71
72 QSqlQueryPrivate* QSqlQueryPrivate::shared_null()
73 {
74 QSqlQueryPrivate *null = nullQueryPrivate();
75 null->ref.ref();
76 return null;
77 }
78
79 /*!
80 \internal
81 */
QSqlQueryPrivate(QSqlResult * result)82 QSqlQueryPrivate::QSqlQueryPrivate(QSqlResult* result)
83 : ref(1), sqlResult(result)
84 {
85 if (!sqlResult)
86 sqlResult = nullResult();
87 }
88
~QSqlQueryPrivate()89 QSqlQueryPrivate::~QSqlQueryPrivate()
90 {
91 QSqlResult *nr = nullResult();
92 if (!nr || sqlResult == nr)
93 return;
94 delete sqlResult;
95 }
96
97 /*!
98 \class QSqlQuery
99 \brief The QSqlQuery class provides a means of executing and
100 manipulating SQL statements.
101
102 \ingroup database
103 \ingroup shared
104
105 \inmodule QtSql
106
107 QSqlQuery encapsulates the functionality involved in creating,
108 navigating and retrieving data from SQL queries which are
109 executed on a \l QSqlDatabase. It can be used to execute DML
110 (data manipulation language) statements, such as \c SELECT, \c
111 INSERT, \c UPDATE and \c DELETE, as well as DDL (data definition
112 language) statements, such as \c{CREATE} \c{TABLE}. It can also
113 be used to execute database-specific commands which are not
114 standard SQL (e.g. \c{SET DATESTYLE=ISO} for PostgreSQL).
115
116 Successfully executed SQL statements set the query's state to
117 active so that isActive() returns true. Otherwise the query's
118 state is set to inactive. In either case, when executing a new SQL
119 statement, the query is positioned on an invalid record. An active
120 query must be navigated to a valid record (so that isValid()
121 returns true) before values can be retrieved.
122
123 For some databases, if an active query that is a \c{SELECT}
124 statement exists when you call \l{QSqlDatabase::}{commit()} or
125 \l{QSqlDatabase::}{rollback()}, the commit or rollback will
126 fail. See isActive() for details.
127
128 \target QSqlQuery examples
129
130 Navigating records is performed with the following functions:
131
132 \list
133 \o next()
134 \o previous()
135 \o first()
136 \o last()
137 \o seek()
138 \endlist
139
140 These functions allow the programmer to move forward, backward
141 or arbitrarily through the records returned by the query. If you
142 only need to move forward through the results (e.g., by using
143 next()), you can use setForwardOnly(), which will save a
144 significant amount of memory overhead and improve performance on
145 some databases. Once an active query is positioned on a valid
146 record, data can be retrieved using value(). All data is
147 transferred from the SQL backend using QVariants.
148
149 For example:
150
151 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 7
152
153 To access the data returned by a query, use value(int). Each
154 field in the data returned by a \c SELECT statement is accessed
155 by passing the field's position in the statement, starting from
156 0. This makes using \c{SELECT *} queries inadvisable because the
157 order of the fields returned is indeterminate.
158
159 For the sake of efficiency, there are no functions to access a
160 field by name (unless you use prepared queries with names, as
161 explained below). To convert a field name into an index, use
162 record().\l{QSqlRecord::indexOf()}{indexOf()}, for example:
163
164 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 8
165
166 QSqlQuery supports prepared query execution and the binding of
167 parameter values to placeholders. Some databases don't support
168 these features, so for those, Qt emulates the required
169 functionality. For example, the Oracle and ODBC drivers have
170 proper prepared query support, and Qt makes use of it; but for
171 databases that don't have this support, Qt implements the feature
172 itself, e.g. by replacing placeholders with actual values when a
173 query is executed. Use numRowsAffected() to find out how many rows
174 were affected by a non-\c SELECT query, and size() to find how
175 many were retrieved by a \c SELECT.
176
177 Oracle databases identify placeholders by using a colon-name
178 syntax, e.g \c{:name}. ODBC simply uses \c ? characters. Qt
179 supports both syntaxes, with the restriction that you can't mix
180 them in the same query.
181
182 You can retrieve the values of all the fields in a single variable
183 (a map) using boundValues().
184
185 \section1 Approaches to Binding Values
186
187 Below we present the same example using each of the four
188 different binding approaches, as well as one example of binding
189 values to a stored procedure.
190
191 \bold{Named binding using named placeholders:}
192
193 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 9
194
195 \bold{Positional binding using named placeholders:}
196
197 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 10
198
199 \bold{Binding values using positional placeholders (version 1):}
200
201 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 11
202
203 \bold{Binding values using positional placeholders (version 2):}
204
205 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 12
206
207 \bold{Binding values to a stored procedure:}
208
209 This code calls a stored procedure called \c AsciiToInt(), passing
210 it a character through its in parameter, and taking its result in
211 the out parameter.
212
213 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 13
214
215 Note that unbound parameters will retain their values.
216
217 Stored procedures that uses the return statement to return values,
218 or return multiple result sets, are not fully supported. For specific
219 details see \l{SQL Database Drivers}.
220
221 \warning You must load the SQL driver and open the connection before a
222 QSqlQuery is created. Also, the connection must remain open while the
223 query exists; otherwise, the behavior of QSqlQuery is undefined.
224
225 \sa QSqlDatabase, QSqlQueryModel, QSqlTableModel, QVariant
226 */
227
228 /*!
229 Constructs a QSqlQuery object which uses the QSqlResult \a result
230 to communicate with a database.
231 */
232
QSqlQuery(QSqlResult * result)233 QSqlQuery::QSqlQuery(QSqlResult *result)
234 {
235 d = new QSqlQueryPrivate(result);
236 }
237
238 /*!
239 Destroys the object and frees any allocated resources.
240 */
241
~QSqlQuery()242 QSqlQuery::~QSqlQuery()
243 {
244 if (!d->ref.deref())
245 delete d;
246 }
247
248 /*!
249 Constructs a copy of \a other.
250 */
251
QSqlQuery(const QSqlQuery & other)252 QSqlQuery::QSqlQuery(const QSqlQuery& other)
253 {
254 d = other.d;
255 d->ref.ref();
256 }
257
258 /*!
259 \internal
260 */
qInit(QSqlQuery * q,const QString & query,QSqlDatabase db)261 static void qInit(QSqlQuery *q, const QString& query, QSqlDatabase db)
262 {
263 QSqlDatabase database = db;
264 if (!database.isValid())
265 database = QSqlDatabase::database(QLatin1String(QSqlDatabase::defaultConnection), false);
266 if (database.isValid()) {
267 *q = QSqlQuery(database.driver()->createResult());
268 }
269 if (!query.isEmpty())
270 q->exec(query);
271 }
272
273 /*!
274 Constructs a QSqlQuery object using the SQL \a query and the
275 database \a db. If \a db is not specified, or is invalid, the application's
276 default database is used. If \a query is not an empty string, it
277 will be executed.
278
279 \sa QSqlDatabase
280 */
QSqlQuery(const QString & query,QSqlDatabase db)281 QSqlQuery::QSqlQuery(const QString& query, QSqlDatabase db)
282 {
283 d = QSqlQueryPrivate::shared_null();
284 qInit(this, query, db);
285 }
286
287 /*!
288 Constructs a QSqlQuery object using the database \a db.
289 If \a db is invalid, the application's default database will be used.
290
291 \sa QSqlDatabase
292 */
293
QSqlQuery(QSqlDatabase db)294 QSqlQuery::QSqlQuery(QSqlDatabase db)
295 {
296 d = QSqlQueryPrivate::shared_null();
297 qInit(this, QString(), db);
298 }
299
300
301 /*!
302 Assigns \a other to this object.
303 */
304
operator =(const QSqlQuery & other)305 QSqlQuery& QSqlQuery::operator=(const QSqlQuery& other)
306 {
307 qAtomicAssign(d, other.d);
308 return *this;
309 }
310
311 /*!
312 Returns true if the query is \l{isActive()}{active} and positioned
313 on a valid record and the \a field is NULL; otherwise returns
314 false. Note that for some drivers, isNull() will not return accurate
315 information until after an attempt is made to retrieve data.
316
317 \sa isActive(), isValid(), value()
318 */
319
isNull(int field) const320 bool QSqlQuery::isNull(int field) const
321 {
322 if (d->sqlResult->isActive() && d->sqlResult->isValid())
323 return d->sqlResult->isNull(field);
324 return true;
325 }
326
327 /*!
328
329 Executes the SQL in \a query. Returns true and sets the query state
330 to \l{isActive()}{active} if the query was successful; otherwise
331 returns false. The \a query string must use syntax appropriate for
332 the SQL database being queried (for example, standard SQL).
333
334 After the query is executed, the query is positioned on an \e
335 invalid record and must be navigated to a valid record before data
336 values can be retrieved (for example, using next()).
337
338 Note that the last error for this query is reset when exec() is
339 called.
340
341 For SQLite, the query string can contain only one statement at a time.
342 If more than one statements is give, the function returns false.
343
344 Example:
345
346 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 34
347
348 \sa isActive(), isValid(), next(), previous(), first(), last(),
349 seek()
350 */
351
exec(const QString & query)352 bool QSqlQuery::exec(const QString& query)
353 {
354 if (d->ref != 1) {
355 bool fo = isForwardOnly();
356 *this = QSqlQuery(driver()->createResult());
357 d->sqlResult->setNumericalPrecisionPolicy(d->sqlResult->numericalPrecisionPolicy());
358 setForwardOnly(fo);
359 } else {
360 d->sqlResult->clear();
361 d->sqlResult->setActive(false);
362 d->sqlResult->setLastError(QSqlError());
363 d->sqlResult->setAt(QSql::BeforeFirstRow);
364 d->sqlResult->setNumericalPrecisionPolicy(d->sqlResult->numericalPrecisionPolicy());
365 }
366 d->sqlResult->setQuery(query.trimmed());
367 if (!driver()->isOpen() || driver()->isOpenError()) {
368 qWarning("QSqlQuery::exec: database not open");
369 return false;
370 }
371 if (query.isEmpty()) {
372 qWarning("QSqlQuery::exec: empty query");
373 return false;
374 }
375 #ifdef QT_DEBUG_SQL
376 qDebug("\n QSqlQuery: %s", query.toLocal8Bit().constData());
377 #endif
378 return d->sqlResult->reset(query);
379 }
380
381 /*!
382 Returns the value of field \a index in the current record.
383
384 The fields are numbered from left to right using the text of the
385 \c SELECT statement, e.g. in
386
387 \snippet doc/src/snippets/code/src_sql_kernel_qsqlquery.cpp 0
388
389 field 0 is \c forename and field 1 is \c
390 surname. Using \c{SELECT *} is not recommended because the order
391 of the fields in the query is undefined.
392
393 An invalid QVariant is returned if field \a index does not
394 exist, if the query is inactive, or if the query is positioned on
395 an invalid record.
396
397 \sa previous() next() first() last() seek() isActive() isValid()
398 */
399
value(int index) const400 QVariant QSqlQuery::value(int index) const
401 {
402 if (isActive() && isValid() && (index > QSql::BeforeFirstRow))
403 return d->sqlResult->data(index);
404 qWarning("QSqlQuery::value: not positioned on a valid record");
405 return QVariant();
406 }
407
408 /*!
409 Returns the current internal position of the query. The first
410 record is at position zero. If the position is invalid, the
411 function returns QSql::BeforeFirstRow or
412 QSql::AfterLastRow, which are special negative values.
413
414 \sa previous() next() first() last() seek() isActive() isValid()
415 */
416
at() const417 int QSqlQuery::at() const
418 {
419 return d->sqlResult->at();
420 }
421
422 /*!
423 Returns the text of the current query being used, or an empty
424 string if there is no current query text.
425
426 \sa executedQuery()
427 */
428
lastQuery() const429 QString QSqlQuery::lastQuery() const
430 {
431 return d->sqlResult->lastQuery();
432 }
433
434 /*!
435 Returns the database driver associated with the query.
436 */
437
driver() const438 const QSqlDriver *QSqlQuery::driver() const
439 {
440 return d->sqlResult->driver();
441 }
442
443 /*!
444 Returns the result associated with the query.
445 */
446
result() const447 const QSqlResult* QSqlQuery::result() const
448 {
449 return d->sqlResult;
450 }
451
452 /*!
453 Retrieves the record at position \a index, if available, and
454 positions the query on the retrieved record. The first record is at
455 position 0. Note that the query must be in an \l{isActive()}
456 {active} state and isSelect() must return true before calling this
457 function.
458
459 If \a relative is false (the default), the following rules apply:
460
461 \list
462
463 \o If \a index is negative, the result is positioned before the
464 first record and false is returned.
465
466 \o Otherwise, an attempt is made to move to the record at position
467 \a index. If the record at position \a index could not be retrieved,
468 the result is positioned after the last record and false is
469 returned. If the record is successfully retrieved, true is returned.
470
471 \endlist
472
473 If \a relative is true, the following rules apply:
474
475 \list
476
477 \o If the result is currently positioned before the first record or
478 on the first record, and \a index is negative, there is no change,
479 and false is returned.
480
481 \o If the result is currently located after the last record, and \a
482 index is positive, there is no change, and false is returned.
483
484 \o If the result is currently located somewhere in the middle, and
485 the relative offset \a index moves the result below zero, the result
486 is positioned before the first record and false is returned.
487
488 \o Otherwise, an attempt is made to move to the record \a index
489 records ahead of the current record (or \a index records behind the
490 current record if \a index is negative). If the record at offset \a
491 index could not be retrieved, the result is positioned after the
492 last record if \a index >= 0, (or before the first record if \a
493 index is negative), and false is returned. If the record is
494 successfully retrieved, true is returned.
495
496 \endlist
497
498 \sa next() previous() first() last() at() isActive() isValid()
499 */
seek(int index,bool relative)500 bool QSqlQuery::seek(int index, bool relative)
501 {
502 if (!isSelect() || !isActive())
503 return false;
504 int actualIdx;
505 if (!relative) { // arbitrary seek
506 if (index < 0) {
507 d->sqlResult->setAt(QSql::BeforeFirstRow);
508 return false;
509 }
510 actualIdx = index;
511 } else {
512 switch (at()) { // relative seek
513 case QSql::BeforeFirstRow:
514 if (index > 0)
515 actualIdx = index;
516 else {
517 return false;
518 }
519 break;
520 case QSql::AfterLastRow:
521 if (index < 0) {
522 d->sqlResult->fetchLast();
523 actualIdx = at() + index;
524 } else {
525 return false;
526 }
527 break;
528 default:
529 if ((at() + index) < 0) {
530 d->sqlResult->setAt(QSql::BeforeFirstRow);
531 return false;
532 }
533 actualIdx = at() + index;
534 break;
535 }
536 }
537 // let drivers optimize
538 if (isForwardOnly() && actualIdx < at()) {
539 qWarning("QSqlQuery::seek: cannot seek backwards in a forward only query");
540 return false;
541 }
542 if (actualIdx == (at() + 1) && at() != QSql::BeforeFirstRow) {
543 if (!d->sqlResult->fetchNext()) {
544 d->sqlResult->setAt(QSql::AfterLastRow);
545 return false;
546 }
547 return true;
548 }
549 if (actualIdx == (at() - 1)) {
550 if (!d->sqlResult->fetchPrevious()) {
551 d->sqlResult->setAt(QSql::BeforeFirstRow);
552 return false;
553 }
554 return true;
555 }
556 if (!d->sqlResult->fetch(actualIdx)) {
557 d->sqlResult->setAt(QSql::AfterLastRow);
558 return false;
559 }
560 return true;
561 }
562
563 /*!
564
565 Retrieves the next record in the result, if available, and positions
566 the query on the retrieved record. Note that the result must be in
567 the \l{isActive()}{active} state and isSelect() must return true
568 before calling this function or it will do nothing and return false.
569
570 The following rules apply:
571
572 \list
573
574 \o If the result is currently located before the first record,
575 e.g. immediately after a query is executed, an attempt is made to
576 retrieve the first record.
577
578 \o If the result is currently located after the last record, there
579 is no change and false is returned.
580
581 \o If the result is located somewhere in the middle, an attempt is
582 made to retrieve the next record.
583
584 \endlist
585
586 If the record could not be retrieved, the result is positioned after
587 the last record and false is returned. If the record is successfully
588 retrieved, true is returned.
589
590 \sa previous() first() last() seek() at() isActive() isValid()
591 */
next()592 bool QSqlQuery::next()
593 {
594 if (!isSelect() || !isActive())
595 return false;
596 bool b = false;
597 switch (at()) {
598 case QSql::BeforeFirstRow:
599 b = d->sqlResult->fetchFirst();
600 return b;
601 case QSql::AfterLastRow:
602 return false;
603 default:
604 if (!d->sqlResult->fetchNext()) {
605 d->sqlResult->setAt(QSql::AfterLastRow);
606 return false;
607 }
608 return true;
609 }
610 }
611
612 /*!
613
614 Retrieves the previous record in the result, if available, and
615 positions the query on the retrieved record. Note that the result
616 must be in the \l{isActive()}{active} state and isSelect() must
617 return true before calling this function or it will do nothing and
618 return false.
619
620 The following rules apply:
621
622 \list
623
624 \o If the result is currently located before the first record, there
625 is no change and false is returned.
626
627 \o If the result is currently located after the last record, an
628 attempt is made to retrieve the last record.
629
630 \o If the result is somewhere in the middle, an attempt is made to
631 retrieve the previous record.
632
633 \endlist
634
635 If the record could not be retrieved, the result is positioned
636 before the first record and false is returned. If the record is
637 successfully retrieved, true is returned.
638
639 \sa next() first() last() seek() at() isActive() isValid()
640 */
previous()641 bool QSqlQuery::previous()
642 {
643 if (!isSelect() || !isActive())
644 return false;
645 if (isForwardOnly()) {
646 qWarning("QSqlQuery::seek: cannot seek backwards in a forward only query");
647 return false;
648 }
649
650 bool b = false;
651 switch (at()) {
652 case QSql::BeforeFirstRow:
653 return false;
654 case QSql::AfterLastRow:
655 b = d->sqlResult->fetchLast();
656 return b;
657 default:
658 if (!d->sqlResult->fetchPrevious()) {
659 d->sqlResult->setAt(QSql::BeforeFirstRow);
660 return false;
661 }
662 return true;
663 }
664 }
665
666 /*!
667 Retrieves the first record in the result, if available, and
668 positions the query on the retrieved record. Note that the result
669 must be in the \l{isActive()}{active} state and isSelect() must
670 return true before calling this function or it will do nothing and
671 return false. Returns true if successful. If unsuccessful the query
672 position is set to an invalid position and false is returned.
673
674 \sa next() previous() last() seek() at() isActive() isValid()
675 */
first()676 bool QSqlQuery::first()
677 {
678 if (!isSelect() || !isActive())
679 return false;
680 if (isForwardOnly() && at() > QSql::BeforeFirstRow) {
681 qWarning("QSqlQuery::seek: cannot seek backwards in a forward only query");
682 return false;
683 }
684 bool b = false;
685 b = d->sqlResult->fetchFirst();
686 return b;
687 }
688
689 /*!
690
691 Retrieves the last record in the result, if available, and positions
692 the query on the retrieved record. Note that the result must be in
693 the \l{isActive()}{active} state and isSelect() must return true
694 before calling this function or it will do nothing and return false.
695 Returns true if successful. If unsuccessful the query position is
696 set to an invalid position and false is returned.
697
698 \sa next() previous() first() seek() at() isActive() isValid()
699 */
700
last()701 bool QSqlQuery::last()
702 {
703 if (!isSelect() || !isActive())
704 return false;
705 bool b = false;
706 b = d->sqlResult->fetchLast();
707 return b;
708 }
709
710 /*!
711 Returns the size of the result (number of rows returned), or -1 if
712 the size cannot be determined or if the database does not support
713 reporting information about query sizes. Note that for non-\c SELECT
714 statements (isSelect() returns false), size() will return -1. If the
715 query is not active (isActive() returns false), -1 is returned.
716
717 To determine the number of rows affected by a non-\c SELECT
718 statement, use numRowsAffected().
719
720 \sa isActive() numRowsAffected() QSqlDriver::hasFeature()
721 */
size() const722 int QSqlQuery::size() const
723 {
724 if (isActive() && d->sqlResult->driver()->hasFeature(QSqlDriver::QuerySize))
725 return d->sqlResult->size();
726 return -1;
727 }
728
729 /*!
730 Returns the number of rows affected by the result's SQL statement,
731 or -1 if it cannot be determined. Note that for \c SELECT
732 statements, the value is undefined; use size() instead. If the query
733 is not \l{isActive()}{active}, -1 is returned.
734
735 \sa size() QSqlDriver::hasFeature()
736 */
737
numRowsAffected() const738 int QSqlQuery::numRowsAffected() const
739 {
740 if (isActive())
741 return d->sqlResult->numRowsAffected();
742 return -1;
743 }
744
745 /*!
746 Returns error information about the last error (if any) that
747 occurred with this query.
748
749 \sa QSqlError, QSqlDatabase::lastError()
750 */
751
lastError() const752 QSqlError QSqlQuery::lastError() const
753 {
754 return d->sqlResult->lastError();
755 }
756
757 /*!
758 Returns true if the query is currently positioned on a valid
759 record; otherwise returns false.
760 */
761
isValid() const762 bool QSqlQuery::isValid() const
763 {
764 return d->sqlResult->isValid();
765 }
766
767 /*!
768
769 Returns true if the query is \e{active}. An active QSqlQuery is one
770 that has been \l{QSqlQuery::exec()} {exec()'d} successfully but not
771 yet finished with. When you are finished with an active query, you
772 can make make the query inactive by calling finish() or clear(), or
773 you can delete the QSqlQuery instance.
774
775 \note Of particular interest is an active query that is a \c{SELECT}
776 statement. For some databases that support transactions, an active
777 query that is a \c{SELECT} statement can cause a \l{QSqlDatabase::}
778 {commit()} or a \l{QSqlDatabase::} {rollback()} to fail, so before
779 committing or rolling back, you should make your active \c{SELECT}
780 statement query inactive using one of the ways listed above.
781
782 \sa isSelect()
783 */
isActive() const784 bool QSqlQuery::isActive() const
785 {
786 return d->sqlResult->isActive();
787 }
788
789 /*!
790 Returns true if the current query is a \c SELECT statement;
791 otherwise returns false.
792 */
793
isSelect() const794 bool QSqlQuery::isSelect() const
795 {
796 return d->sqlResult->isSelect();
797 }
798
799 /*!
800 Returns true if you can only scroll forward through a result set;
801 otherwise returns false.
802
803 \sa setForwardOnly(), next()
804 */
isForwardOnly() const805 bool QSqlQuery::isForwardOnly() const
806 {
807 return d->sqlResult->isForwardOnly();
808 }
809
810 /*!
811 Sets forward only mode to \a forward. If \a forward is true, only
812 next() and seek() with positive values, are allowed for navigating
813 the results.
814
815 Forward only mode can be (depending on the driver) more memory
816 efficient since results do not need to be cached. It will also
817 improve performance on some databases. For this to be true, you must
818 call \c setForwardOnly() before the query is prepared or executed.
819 Note that the constructor that takes a query and a database may
820 execute the query.
821
822 Forward only mode is off by default.
823
824 Setting forward only to false is a suggestion to the database engine,
825 which has the final say on whether a result set is forward only or
826 scrollable. isForwardOnly() will always return the correct status of
827 the result set.
828
829 \note Calling setForwardOnly after execution of the query will result
830 in unexpected results at best, and crashes at worst.
831
832 \sa isForwardOnly(), next(), seek(), QSqlResult::setForwardOnly()
833 */
setForwardOnly(bool forward)834 void QSqlQuery::setForwardOnly(bool forward)
835 {
836 d->sqlResult->setForwardOnly(forward);
837 }
838
839 /*!
840 Returns a QSqlRecord containing the field information for the
841 current query. If the query points to a valid row (isValid() returns
842 true), the record is populated with the row's values. An empty
843 record is returned when there is no active query (isActive() returns
844 false).
845
846 To retrieve values from a query, value() should be used since
847 its index-based lookup is faster.
848
849 In the following example, a \c{SELECT * FROM} query is executed.
850 Since the order of the columns is not defined, QSqlRecord::indexOf()
851 is used to obtain the index of a column.
852
853 \snippet doc/src/snippets/code/src_sql_kernel_qsqlquery.cpp 1
854
855 \sa value()
856 */
record() const857 QSqlRecord QSqlQuery::record() const
858 {
859 QSqlRecord rec = d->sqlResult->record();
860
861 if (isValid()) {
862 for (int i = 0; i < rec.count(); ++i)
863 rec.setValue(i, value(i));
864 }
865 return rec;
866 }
867
868 /*!
869 Clears the result set and releases any resources held by the
870 query. Sets the query state to inactive. You should rarely if ever
871 need to call this function.
872 */
clear()873 void QSqlQuery::clear()
874 {
875 *this = QSqlQuery(driver()->createResult());
876 }
877
878 /*!
879 Prepares the SQL query \a query for execution. Returns true if the
880 query is prepared successfully; otherwise returns false.
881
882 The query may contain placeholders for binding values. Both Oracle
883 style colon-name (e.g., \c{:surname}), and ODBC style (\c{?})
884 placeholders are supported; but they cannot be mixed in the same
885 query. See the \l{QSqlQuery examples}{Detailed Description} for
886 examples.
887
888 Portability note: Some databases choose to delay preparing a query
889 until it is executed the first time. In this case, preparing a
890 syntactically wrong query succeeds, but every consecutive exec()
891 will fail.
892
893 For SQLite, the query string can contain only one statement at a time.
894 If more than one statements are give, the function returns false.
895
896 Example:
897
898 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 9
899
900 \sa exec(), bindValue(), addBindValue()
901 */
prepare(const QString & query)902 bool QSqlQuery::prepare(const QString& query)
903 {
904 if (d->ref != 1) {
905 bool fo = isForwardOnly();
906 *this = QSqlQuery(driver()->createResult());
907 setForwardOnly(fo);
908 d->sqlResult->setNumericalPrecisionPolicy(d->sqlResult->numericalPrecisionPolicy());
909 } else {
910 d->sqlResult->setActive(false);
911 d->sqlResult->setLastError(QSqlError());
912 d->sqlResult->setAt(QSql::BeforeFirstRow);
913 d->sqlResult->setNumericalPrecisionPolicy(d->sqlResult->numericalPrecisionPolicy());
914 }
915 if (!driver()) {
916 qWarning("QSqlQuery::prepare: no driver");
917 return false;
918 }
919 if (!driver()->isOpen() || driver()->isOpenError()) {
920 qWarning("QSqlQuery::prepare: database not open");
921 return false;
922 }
923 if (query.isEmpty()) {
924 qWarning("QSqlQuery::prepare: empty query");
925 return false;
926 }
927 #ifdef QT_DEBUG_SQL
928 qDebug("\n QSqlQuery::prepare: %s", query.toLocal8Bit().constData());
929 #endif
930 return d->sqlResult->savePrepare(query);
931 }
932
933 /*!
934 Executes a previously prepared SQL query. Returns true if the query
935 executed successfully; otherwise returns false.
936
937 Note that the last error for this query is reset when exec() is
938 called.
939
940 \sa prepare() bindValue() addBindValue() boundValue() boundValues()
941 */
exec()942 bool QSqlQuery::exec()
943 {
944 d->sqlResult->resetBindCount();
945
946 if (d->sqlResult->lastError().isValid())
947 d->sqlResult->setLastError(QSqlError());
948
949 return d->sqlResult->exec();
950 }
951
952 /*! \enum QSqlQuery::BatchExecutionMode
953
954 \value ValuesAsRows - Updates multiple rows. Treats every entry in a QVariantList as a value for updating the next row.
955 \value ValuesAsColumns - Updates a single row. Treats every entry in a QVariantList as a single value of an array type.
956 */
957
958 /*!
959 \since 4.2
960
961 Executes a previously prepared SQL query in a batch. All the bound
962 parameters have to be lists of variants. If the database doesn't
963 support batch executions, the driver will simulate it using
964 conventional exec() calls.
965
966 Returns true if the query is executed successfully; otherwise
967 returns false.
968
969 Example:
970
971 \snippet doc/src/snippets/code/src_sql_kernel_qsqlquery.cpp 2
972
973 The example above inserts four new rows into \c myTable:
974
975 \snippet doc/src/snippets/code/src_sql_kernel_qsqlquery.cpp 3
976
977 To bind NULL values, a null QVariant of the relevant type has to be
978 added to the bound QVariantList; for example, \c
979 {QVariant(QVariant::String)} should be used if you are using
980 strings.
981
982 \note Every bound QVariantList must contain the same amount of
983 variants.
984
985 \note The type of the QVariants in a list must not change. For
986 example, you cannot mix integer and string variants within a
987 QVariantList.
988
989 The \a mode parameter indicates how the bound QVariantList will be
990 interpreted. If \a mode is \c ValuesAsRows, every variant within
991 the QVariantList will be interpreted as a value for a new row. \c
992 ValuesAsColumns is a special case for the Oracle driver. In this
993 mode, every entry within a QVariantList will be interpreted as
994 array-value for an IN or OUT value within a stored procedure. Note
995 that this will only work if the IN or OUT value is a table-type
996 consisting of only one column of a basic type, for example \c{TYPE
997 myType IS TABLE OF VARCHAR(64) INDEX BY BINARY_INTEGER;}
998
999 \sa prepare(), bindValue(), addBindValue()
1000 */
execBatch(BatchExecutionMode mode)1001 bool QSqlQuery::execBatch(BatchExecutionMode mode)
1002 {
1003 return d->sqlResult->execBatch(mode == ValuesAsColumns);
1004 }
1005
1006 /*!
1007 Set the placeholder \a placeholder to be bound to value \a val in
1008 the prepared statement. Note that the placeholder mark (e.g \c{:})
1009 must be included when specifying the placeholder name. If \a
1010 paramType is QSql::Out or QSql::InOut, the placeholder will be
1011 overwritten with data from the database after the exec() call.
1012 In this case, sufficient space must be pre-allocated to store
1013 the result into.
1014
1015 To bind a NULL value, use a null QVariant; for example, use
1016 \c {QVariant(QVariant::String)} if you are binding a string.
1017
1018 Values cannot be bound to multiple locations in the query, eg:
1019 \code
1020 INSERT INTO testtable (id, name, samename) VALUES (:id, :name, :name)
1021 \endcode
1022 Binding to name will bind to the first :name, but not the second.
1023
1024 \sa addBindValue(), prepare(), exec(), boundValue() boundValues()
1025 */
bindValue(const QString & placeholder,const QVariant & val,QSql::ParamType paramType)1026 void QSqlQuery::bindValue(const QString& placeholder, const QVariant& val,
1027 QSql::ParamType paramType
1028 )
1029 {
1030 d->sqlResult->bindValue(placeholder, val, paramType);
1031 }
1032
1033 /*!
1034 Set the placeholder in position \a pos to be bound to value \a val
1035 in the prepared statement. Field numbering starts at 0. If \a
1036 paramType is QSql::Out or QSql::InOut, the placeholder will be
1037 overwritten with data from the database after the exec() call.
1038 */
bindValue(int pos,const QVariant & val,QSql::ParamType paramType)1039 void QSqlQuery::bindValue(int pos, const QVariant& val, QSql::ParamType paramType)
1040 {
1041 d->sqlResult->bindValue(pos, val, paramType);
1042 }
1043
1044 /*!
1045 Adds the value \a val to the list of values when using positional
1046 value binding. The order of the addBindValue() calls determines
1047 which placeholder a value will be bound to in the prepared query.
1048 If \a paramType is QSql::Out or QSql::InOut, the placeholder will be
1049 overwritten with data from the database after the exec() call.
1050
1051 To bind a NULL value, use a null QVariant; for example, use \c
1052 {QVariant(QVariant::String)} if you are binding a string.
1053
1054 \sa bindValue(), prepare(), exec(), boundValue() boundValues()
1055 */
addBindValue(const QVariant & val,QSql::ParamType paramType)1056 void QSqlQuery::addBindValue(const QVariant& val, QSql::ParamType paramType)
1057 {
1058 d->sqlResult->addBindValue(val, paramType);
1059 }
1060
1061 /*!
1062 Returns the value for the \a placeholder.
1063
1064 \sa boundValues() bindValue() addBindValue()
1065 */
boundValue(const QString & placeholder) const1066 QVariant QSqlQuery::boundValue(const QString& placeholder) const
1067 {
1068 return d->sqlResult->boundValue(placeholder);
1069 }
1070
1071 /*!
1072 Returns the value for the placeholder at position \a pos.
1073 */
boundValue(int pos) const1074 QVariant QSqlQuery::boundValue(int pos) const
1075 {
1076 return d->sqlResult->boundValue(pos);
1077 }
1078
1079 /*!
1080 Returns a map of the bound values.
1081
1082 With named binding, the bound values can be examined in the
1083 following ways:
1084
1085 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 14
1086
1087 With positional binding, the code becomes:
1088
1089 \snippet doc/src/snippets/sqldatabase/sqldatabase.cpp 15
1090
1091 \sa boundValue() bindValue() addBindValue()
1092 */
boundValues() const1093 QMap<QString,QVariant> QSqlQuery::boundValues() const
1094 {
1095 QMap<QString,QVariant> map;
1096
1097 const QVector<QVariant> values(d->sqlResult->boundValues());
1098 for (int i = 0; i < values.count(); ++i)
1099 map[d->sqlResult->boundValueName(i)] = values.at(i);
1100 return map;
1101 }
1102
1103 /*!
1104 Returns the last query that was successfully executed.
1105
1106 In most cases this function returns the same string as lastQuery().
1107 If a prepared query with placeholders is executed on a DBMS that
1108 does not support it, the preparation of this query is emulated. The
1109 placeholders in the original query are replaced with their bound
1110 values to form a new query. This function returns the modified
1111 query. It is mostly useful for debugging purposes.
1112
1113 \sa lastQuery()
1114 */
executedQuery() const1115 QString QSqlQuery::executedQuery() const
1116 {
1117 return d->sqlResult->executedQuery();
1118 }
1119
1120 /*!
1121 \fn bool QSqlQuery::prev()
1122
1123 Use previous() instead.
1124 */
1125
1126 /*!
1127 Returns the object ID of the most recent inserted row if the
1128 database supports it. An invalid QVariant will be returned if the
1129 query did not insert any value or if the database does not report
1130 the id back. If more than one row was touched by the insert, the
1131 behavior is undefined.
1132
1133 For MySQL databases the row's auto-increment field will be returned.
1134
1135 \note For this function to work in PSQL, the table table must
1136 contain OIDs, which may not have been created by default. Check the
1137 \c default_with_oids configuration variable to be sure.
1138
1139 \sa QSqlDriver::hasFeature()
1140 */
lastInsertId() const1141 QVariant QSqlQuery::lastInsertId() const
1142 {
1143 return d->sqlResult->lastInsertId();
1144 }
1145
1146 /*!
1147
1148 Instruct the database driver to return numerical values with a
1149 precision specified by \a precisionPolicy.
1150
1151 The Oracle driver, for example, can retrieve numerical values as
1152 strings to prevent the loss of precision. If high precision doesn't
1153 matter, use this method to increase execution speed by bypassing
1154 string conversions.
1155
1156 Note: Drivers that don't support fetching numerical values with low
1157 precision will ignore the precision policy. You can use
1158 QSqlDriver::hasFeature() to find out whether a driver supports this
1159 feature.
1160
1161 Note: Setting the precision policy doesn't affect the currently
1162 active query. Call \l{exec()}{exec(QString)} or prepare() in order
1163 to activate the policy.
1164
1165 \sa QSql::NumericalPrecisionPolicy, numericalPrecisionPolicy()
1166 */
setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy precisionPolicy)1167 void QSqlQuery::setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy precisionPolicy)
1168 {
1169 d->sqlResult->setNumericalPrecisionPolicy(precisionPolicy);
1170 }
1171
1172 /*!
1173 Returns the current precision policy.
1174
1175 \sa QSql::NumericalPrecisionPolicy, setNumericalPrecisionPolicy()
1176 */
numericalPrecisionPolicy() const1177 QSql::NumericalPrecisionPolicy QSqlQuery::numericalPrecisionPolicy() const
1178 {
1179 return d->sqlResult->numericalPrecisionPolicy();
1180 }
1181
1182 /*!
1183 \since 4.3.2
1184
1185 Instruct the database driver that no more data will be fetched from
1186 this query until it is re-executed. There is normally no need to
1187 call this function, but it may be helpful in order to free resources
1188 such as locks or cursors if you intend to re-use the query at a
1189 later time.
1190
1191 Sets the query to inactive. Bound values retain their values.
1192
1193 \sa prepare() exec() isActive()
1194 */
finish()1195 void QSqlQuery::finish()
1196 {
1197 if (isActive()) {
1198 d->sqlResult->setLastError(QSqlError());
1199 d->sqlResult->setAt(QSql::BeforeFirstRow);
1200 d->sqlResult->detachFromResultSet();
1201 d->sqlResult->setActive(false);
1202 }
1203 }
1204
1205 /*!
1206 \since 4.4
1207
1208 Discards the current result set and navigates to the next if available.
1209
1210 Some databases are capable of returning multiple result sets for
1211 stored procedures or SQL batches (a query strings that contains
1212 multiple statements). If multiple result sets are available after
1213 executing a query this function can be used to navigate to the next
1214 result set(s).
1215
1216 If a new result set is available this function will return true.
1217 The query will be repositioned on an \e invalid record in the new
1218 result set and must be navigated to a valid record before data
1219 values can be retrieved. If a new result set isn't available the
1220 function returns false and the query is set to inactive. In any
1221 case the old result set will be discarded.
1222
1223 When one of the statements is a non-select statement a count of
1224 affected rows may be available instead of a result set.
1225
1226 Note that some databases, i.e. Microsoft SQL Server, requires
1227 non-scrollable cursors when working with multiple result sets. Some
1228 databases may execute all statements at once while others may delay
1229 the execution until the result set is actually accessed, and some
1230 databases may have restrictions on which statements are allowed to
1231 be used in a SQL batch.
1232
1233 \sa QSqlDriver::hasFeature() setForwardOnly() next() isSelect() numRowsAffected() isActive() lastError()
1234 */
nextResult()1235 bool QSqlQuery::nextResult()
1236 {
1237 if (isActive())
1238 return d->sqlResult->nextResult();
1239 return false;
1240 }
1241
1242 QT_END_NAMESPACE
1243