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

Provided by KDAB

Privacy Policy
Learn to use CMake with our Intro Training
Find out more

source code of qtbase/src/sql/kernel/qsqlquery.cpp