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