1 /****************************************************************************
3 ** Copyright (C) 2011 Nokia Corporation and/or its subsidiary(-ies).
4 ** All rights reserved.
5 ** Contact: Nokia Corporation (qt-info@nokia.com)
7 ** This file is part of the QtSql module of the Qt Toolkit.
9 ** $QT_BEGIN_LICENSE:LGPL$
10 ** GNU Lesser General Public License Usage
11 ** This file may be used under the terms of the GNU Lesser General Public
12 ** License version 2.1 as published by the Free Software Foundation and
13 ** appearing in the file LICENSE.LGPL included in the packaging of this
14 ** file. Please review the following information to ensure the GNU Lesser
15 ** General Public License version 2.1 requirements will be met:
16 ** http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
18 ** In addition, as a special exception, Nokia gives you certain additional
19 ** rights. These rights are described in the Nokia Qt LGPL Exception
20 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
22 ** GNU General Public License Usage
23 ** Alternatively, this file may be used under the terms of the GNU General
24 ** Public License version 3.0 as published by the Free Software Foundation
25 ** and appearing in the file LICENSE.GPL included in the packaging of this
26 ** file. Please review the following information to ensure the GNU General
27 ** Public License version 3.0 requirements will be met:
28 ** http://www.gnu.org/copyleft/gpl.html.
31 ** Alternatively, this file may be used in accordance with the terms and
32 ** conditions contained in a signed written agreement between you and Nokia.
40 ****************************************************************************/
45 #include "qsqlerror.h"
46 #include "qsqlfield.h"
47 #include "qsqlrecord.h"
48 #include "qsqlresult.h"
50 #include "qsqldriver.h"
56 QHolder(const QString& hldr = QString(), int index = -1): holderName(hldr), holderPos(index) {}
57 bool operator==(const QHolder& h) const { return h.holderPos == holderPos && h.holderName == holderName; }
58 bool operator!=(const QHolder& h) const { return h.holderPos != holderPos || h.holderName != holderName; }
63 class QSqlResultPrivate
66 QSqlResultPrivate(QSqlResult* d)
67 : q(d), sqldriver(0), idx(QSql::BeforeFirstRow), active(false),
68 isSel(false), forwardOnly(false), precisionPolicy(QSql::LowPrecisionDouble), bindCount(0), binds(QSqlResult::PositionalBinding)
95 QString positionalToNamedBinding();
96 QString namedToPositionalBinding();
97 QString holderAt(int index) const;
101 const QSqlDriver* sqldriver;
108 QSql::NumericalPrecisionPolicy precisionPolicy;
111 QSqlResult::BindingSyntax binds;
113 QString executedQuery;
114 QHash<int, QSql::ParamType> types;
115 QVector<QVariant> values;
116 typedef QHash<QString, int> IndexMap;
119 typedef QVector<QHolder> QHolderVector;
120 QHolderVector holders;
123 QString QSqlResultPrivate::holderAt(int index) const
125 return indexes.key(index);
128 // return a unique id for bound names
129 static QString qFieldSerial(int i)
131 ushort arr[] = { ':', 'f', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
132 ushort *ptr = &arr[1];
135 *(++ptr) = 'a' + i % 16;
139 return QString(reinterpret_cast<const QChar *>(arr), int(ptr - arr) + 1);
142 static bool qIsAlnum(QChar ch)
144 uint u = uint(ch.unicode());
145 // matches [a-zA-Z0-9_]
146 return u - 'a' < 26 || u - 'A' < 26 || u - '0' < 10 || u == '_';
149 QString QSqlResultPrivate::positionalToNamedBinding()
154 result.reserve(n * 5 / 4);
155 bool inQuote = false;
158 for (int i = 0; i < n; ++i) {
159 QChar ch = sql.at(i);
160 if (ch == QLatin1Char('?') && !inQuote) {
161 result += qFieldSerial(count++);
163 if (ch == QLatin1Char('\''))
172 QString QSqlResultPrivate::namedToPositionalBinding()
178 bool inQuote = false;
183 QChar ch = sql.at(i);
184 if (ch == QLatin1Char(':') && !inQuote
185 && (i == 0 || sql.at(i - 1) != QLatin1Char(':'))
186 && (i + 1 < n && qIsAlnum(sql.at(i + 1)))) {
188 while (pos < n && qIsAlnum(sql.at(pos)))
190 indexes[sql.mid(i, pos - i)] = count++;
191 result += QLatin1Char('?');
194 if (ch == QLatin1Char('\''))
206 \brief The QSqlResult class provides an abstract interface for
207 accessing data from specific SQL databases.
212 Normally, you would use QSqlQuery instead of QSqlResult, since
213 QSqlQuery provides a generic wrapper for database-specific
214 implementations of QSqlResult.
216 If you are implementing your own SQL driver (by subclassing
217 QSqlDriver), you will need to provide your own QSqlResult
218 subclass that implements all the pure virtual functions and other
219 virtual functions that you need.
225 \enum QSqlResult::BindingSyntax
227 This enum type specifies the different syntaxes for specifying
228 placeholders in prepared queries.
230 \value PositionalBinding Use the ODBC-style positional syntax, with "?" as placeholders.
231 \value NamedBinding Use the Oracle-style syntax with named placeholders (e.g., ":id")
232 \omitvalue BindByPosition
233 \omitvalue BindByName
239 \enum QSqlResult::VirtualHookOperation
244 Creates a QSqlResult using database driver \a db. The object is
245 initialized to an inactive state.
247 \sa isActive(), driver()
250 QSqlResult::QSqlResult(const QSqlDriver *db)
252 d = new QSqlResultPrivate(this);
255 setNumericalPrecisionPolicy(db->numericalPrecisionPolicy());
260 Destroys the object and frees any allocated resources.
263 QSqlResult::~QSqlResult()
269 Sets the current query for the result to \a query. You must call
270 reset() to execute the query on the database.
272 \sa reset(), lastQuery()
275 void QSqlResult::setQuery(const QString& query)
281 Returns the current SQL query text, or an empty string if there
287 QString QSqlResult::lastQuery() const
293 Returns the current (zero-based) row position of the result. May
294 return the special values QSql::BeforeFirstRow or
297 \sa setAt(), isValid()
299 int QSqlResult::at() const
306 Returns true if the result is positioned on a valid record (that
307 is, the result is not positioned before the first or after the
308 last record); otherwise returns false.
313 bool QSqlResult::isValid() const
315 return d->idx != QSql::BeforeFirstRow && d->idx != QSql::AfterLastRow;
319 \fn bool QSqlResult::isNull(int index)
321 Returns true if the field at position \a index in the current row
322 is null; otherwise returns false.
326 Returns true if the result has records to be retrieved; otherwise
330 bool QSqlResult::isActive() const
336 This function is provided for derived classes to set the
337 internal (zero-based) row position to \a index.
342 void QSqlResult::setAt(int index)
349 This function is provided for derived classes to indicate whether
350 or not the current statement is a SQL \c SELECT statement. The \a
351 select parameter should be true if the statement is a \c SELECT
352 statement; otherwise it should be false.
357 void QSqlResult::setSelect(bool select)
363 Returns true if the current result is from a \c SELECT statement;
364 otherwise returns false.
369 bool QSqlResult::isSelect() const
375 Returns the driver associated with the result. This is the object
376 that was passed to the constructor.
379 const QSqlDriver *QSqlResult::driver() const
386 This function is provided for derived classes to set the internal
387 active state to \a active.
392 void QSqlResult::setActive(bool active)
394 if (active && d->executedQuery.isEmpty())
395 d->executedQuery = d->sql;
401 This function is provided for derived classes to set the last
407 void QSqlResult::setLastError(const QSqlError &error)
414 Returns the last error associated with the result.
417 QSqlError QSqlResult::lastError() const
423 \fn int QSqlResult::size()
425 Returns the size of the \c SELECT result, or -1 if it cannot be
426 determined or if the query is not a \c SELECT statement.
428 \sa numRowsAffected()
432 \fn int QSqlResult::numRowsAffected()
434 Returns the number of rows affected by the last query executed, or
435 -1 if it cannot be determined or if the query is a \c SELECT
442 \fn QVariant QSqlResult::data(int index)
444 Returns the data for field \a index in the current row as
445 a QVariant. This function is only called if the result is in
446 an active state and is positioned on a valid record and \a index is
447 non-negative. Derived classes must reimplement this function and
448 return the value of field \a index, or QVariant() if it cannot be
453 \fn bool QSqlResult::reset(const QString &query)
455 Sets the result to use the SQL statement \a query for subsequent
458 Derived classes must reimplement this function and apply the \a
459 query to the database. This function is only called after the
460 result is set to an inactive state and is positioned before the
461 first record of the new result. Derived classes should return
462 true if the query was successful and ready to be used, or false
469 \fn bool QSqlResult::fetch(int index)
471 Positions the result to an arbitrary (zero-based) row \a index.
473 This function is only called if the result is in an active state.
474 Derived classes must reimplement this function and position the
475 result to the row \a index, and call setAt() with an appropriate
476 value. Return true to indicate success, or false to signify
479 \sa isActive(), fetchFirst(), fetchLast(), fetchNext(), fetchPrevious()
483 \fn bool QSqlResult::fetchFirst()
485 Positions the result to the first record (row 0) in the result.
487 This function is only called if the result is in an active state.
488 Derived classes must reimplement this function and position the
489 result to the first record, and call setAt() with an appropriate
490 value. Return true to indicate success, or false to signify
493 \sa fetch(), fetchLast()
497 \fn bool QSqlResult::fetchLast()
499 Positions the result to the last record (last row) in the result.
501 This function is only called if the result is in an active state.
502 Derived classes must reimplement this function and position the
503 result to the last record, and call setAt() with an appropriate
504 value. Return true to indicate success, or false to signify
507 \sa fetch(), fetchFirst()
511 Positions the result to the next available record (row) in the
514 This function is only called if the result is in an active
515 state. The default implementation calls fetch() with the next
516 index. Derived classes can reimplement this function and position
517 the result to the next record in some other way, and call setAt()
518 with an appropriate value. Return true to indicate success, or
519 false to signify failure.
521 \sa fetch(), fetchPrevious()
524 bool QSqlResult::fetchNext()
526 return fetch(at() + 1);
530 Positions the result to the previous record (row) in the result.
532 This function is only called if the result is in an active state.
533 The default implementation calls fetch() with the previous index.
534 Derived classes can reimplement this function and position the
535 result to the next record in some other way, and call setAt()
536 with an appropriate value. Return true to indicate success, or
537 false to signify failure.
540 bool QSqlResult::fetchPrevious()
542 return fetch(at() - 1);
546 Returns true if you can only scroll forward through the result
547 set; otherwise returns false.
551 bool QSqlResult::isForwardOnly() const
553 return d->forwardOnly;
557 Sets forward only mode to \a forward. If \a forward is true, only
558 fetchNext() is allowed for navigating the results. Forward only
559 mode needs much less memory since results do not have to be
560 cached. By default, this feature is disabled.
562 Setting forward only to false is a suggestion to the database engine,
563 which has the final say on whether a result set is forward only or
564 scrollable. isForwardOnly() will always return the correct status of
567 \note Calling setForwardOnly after execution of the query will result
568 in unexpected results at best, and crashes at worst.
570 \sa isForwardOnly(), fetchNext(), QSqlQuery::setForwardOnly()
572 void QSqlResult::setForwardOnly(bool forward)
574 d->forwardOnly = forward;
578 Prepares the given \a query, using the underlying database
579 functionality where possible. Returns true if the query is
580 prepared successfully; otherwise returns false.
584 bool QSqlResult::savePrepare(const QString& query)
590 if (!driver()->hasFeature(QSqlDriver::PreparedQueries))
591 return prepare(query);
593 if (driver()->hasFeature(QSqlDriver::NamedPlaceholders)) {
594 // parse the query to memorize parameter location
595 d->namedToPositionalBinding();
596 d->executedQuery = d->positionalToNamedBinding();
598 d->executedQuery = d->namedToPositionalBinding();
600 return prepare(d->executedQuery);
604 Prepares the given \a query for execution; the query will normally
605 use placeholders so that it can be executed repeatedly. Returns
606 true if the query is prepared successfully; otherwise returns false.
610 bool QSqlResult::prepare(const QString& query)
612 int n = query.size();
614 bool inQuote = false;
618 QChar ch = query.at(i);
619 if (ch == QLatin1Char(':') && !inQuote
620 && (i == 0 || query.at(i - 1) != QLatin1Char(':'))
621 && (i + 1 < n && qIsAlnum(query.at(i + 1)))) {
623 while (pos < n && qIsAlnum(query.at(pos)))
626 d->holders.append(QHolder(query.mid(i, pos - i), i));
629 if (ch == QLatin1Char('\''))
635 return true; // fake prepares should always succeed
639 Executes the query, returning true if successful; otherwise returns
644 bool QSqlResult::exec()
647 // fake preparation - just replace the placeholders..
648 QString query = lastQuery();
649 if (d->binds == NamedBinding) {
653 for (i = d->holders.count() - 1; i >= 0; --i) {
654 holder = d->holders.at(i).holderName;
655 val = d->values.value(d->indexes.value(holder));
656 QSqlField f(QLatin1String(""), val.type());
658 query = query.replace(d->holders.at(i).holderPos,
659 holder.length(), driver()->formatValue(f));
665 for (idx = 0; idx < d->values.count(); ++idx) {
666 i = query.indexOf(QLatin1Char('?'), i);
669 QVariant var = d->values.value(idx);
670 QSqlField f(QLatin1String(""), var.type());
675 val = driver()->formatValue(f);
676 query = query.replace(i, 1, driver()->formatValue(f));
681 // have to retain the original query with placeholders
682 QString orig = lastQuery();
684 d->executedQuery = query;
691 Binds the value \a val of parameter type \a paramType to position \a index
692 in the current record (row).
696 void QSqlResult::bindValue(int index, const QVariant& val, QSql::ParamType paramType)
698 d->binds = PositionalBinding;
699 d->indexes[qFieldSerial(index)] = index;
700 if (d->values.count() <= index)
701 d->values.resize(index + 1);
702 d->values[index] = val;
703 if (paramType != QSql::In || !d->types.isEmpty())
704 d->types[index] = paramType;
710 Binds the value \a val of parameter type \a paramType to the \a
711 placeholder name in the current record (row).
713 Values cannot be bound to multiple locations in the query, eg:
715 INSERT INTO testtable (id, name, samename) VALUES (:id, :name, :name)
717 Binding to name will bind to the first :name, but not the second.
719 \note Binding an undefined placeholder will result in undefined behavior.
721 \sa QSqlQuery::bindValue()
723 void QSqlResult::bindValue(const QString& placeholder, const QVariant& val,
724 QSql::ParamType paramType)
726 d->binds = NamedBinding;
727 // if the index has already been set when doing emulated named
728 // bindings - don't reset it
729 int idx = d->indexes.value(placeholder, -1);
731 if (d->values.count() <= idx)
732 d->values.resize(idx + 1);
733 d->values[idx] = val;
735 d->values.append(val);
736 idx = d->values.count() - 1;
737 d->indexes[placeholder] = idx;
740 if (paramType != QSql::In || !d->types.isEmpty())
741 d->types[idx] = paramType;
745 Binds the value \a val of parameter type \a paramType to the next
746 available position in the current record (row).
750 void QSqlResult::addBindValue(const QVariant& val, QSql::ParamType paramType)
752 d->binds = PositionalBinding;
753 bindValue(d->bindCount, val, paramType);
758 Returns the value bound at position \a index in the current record
761 \sa bindValue(), boundValues()
763 QVariant QSqlResult::boundValue(int index) const
765 return d->values.value(index);
771 Returns the value bound by the given \a placeholder name in the
772 current record (row).
776 QVariant QSqlResult::boundValue(const QString& placeholder) const
778 int idx = d->indexes.value(placeholder, -1);
779 return d->values.value(idx);
783 Returns the parameter type for the value bound at position \a index.
787 QSql::ParamType QSqlResult::bindValueType(int index) const
789 return d->types.value(index, QSql::In);
795 Returns the parameter type for the value bound with the given \a
798 QSql::ParamType QSqlResult::bindValueType(const QString& placeholder) const
800 return d->types.value(d->indexes.value(placeholder, -1), QSql::In);
804 Returns the number of bound values in the result.
808 int QSqlResult::boundValueCount() const
810 return d->values.count();
814 Returns a vector of the result's bound values for the current
817 \sa boundValueCount()
819 QVector<QVariant>& QSqlResult::boundValues() const
825 Returns the binding syntax used by prepared queries.
827 QSqlResult::BindingSyntax QSqlResult::bindingSyntax() const
833 Clears the entire result set and releases any associated
836 void QSqlResult::clear()
842 Returns the query that was actually executed. This may differ from
843 the query that was passed, for example if bound values were used
844 with a prepared query and the underlying database doesn't support
847 \sa exec(), setQuery()
849 QString QSqlResult::executedQuery() const
851 return d->executedQuery;
854 void QSqlResult::resetBindCount()
860 Returns the name of the bound value at position \a index in the
861 current record (row).
865 QString QSqlResult::boundValueName(int index) const
867 return d->holderAt(index);
871 Returns true if at least one of the query's bound values is a \c
872 QSql::Out or a QSql::InOut; otherwise returns false.
876 bool QSqlResult::hasOutValues() const
878 if (d->types.isEmpty())
880 QHash<int, QSql::ParamType>::ConstIterator it;
881 for (it = d->types.constBegin(); it != d->types.constEnd(); ++it) {
882 if (it.value() != QSql::In)
889 Returns the current record if the query is active; otherwise
890 returns an empty QSqlRecord.
892 The default implementation always returns an empty QSqlRecord.
896 QSqlRecord QSqlResult::record() const
902 Returns the object ID of the most recent inserted row if the
903 database supports it.
904 An invalid QVariant will be returned if the query did not
905 insert any value or if the database does not report the id back.
906 If more than one row was touched by the insert, the behavior is
909 Note that for Oracle databases the row's ROWID will be returned,
910 while for MySQL databases the row's auto-increment field will
913 \sa QSqlDriver::hasFeature()
915 QVariant QSqlResult::lastInsertId() const
922 void QSqlResult::virtual_hook(int, void *)
929 Executes a prepared query in batch mode if the driver supports it,
930 otherwise emulates a batch execution using bindValue() and exec().
931 QSqlDriver::hasFeature() can be used to find out whether a driver
932 supports batch execution.
934 Batch execution can be faster for large amounts of data since it
935 reduces network roundtrips.
937 For batch executions, bound values have to be provided as lists
938 of variants (QVariantList).
940 Each list must contain values of the same type. All lists must
941 contain equal amount of values (rows).
943 NULL values are passed in as typed QVariants, for example
944 \c {QVariant(QVariant::Int)} for an integer NULL value.
948 \snippet doc/src/snippets/code/src_sql_kernel_qsqlresult.cpp 0
950 Here, we insert two rows into a SQL table, with each row containing three values.
952 \sa exec(), QSqlDriver::hasFeature()
954 bool QSqlResult::execBatch(bool arrayBind)
956 if (driver()->hasFeature(QSqlDriver::BatchOperations)) {
957 virtual_hook(BatchOperation, &arrayBind);
959 return d->error.type() == QSqlError::NoError;
961 QVector<QVariant> values = d->values;
962 if (values.count() == 0)
964 for (int i = 0; i < values.at(0).toList().count(); ++i) {
965 for (int j = 0; j < values.count(); ++j)
966 bindValue(j, values.at(j).toList().at(i), QSql::In);
977 void QSqlResult::detachFromResultSet()
979 if (driver()->hasFeature(QSqlDriver::FinishQuery)
980 || driver()->hasFeature(QSqlDriver::SimpleLocking))
981 virtual_hook(DetachFromResultSet, 0);
986 void QSqlResult::setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy policy)
988 d->precisionPolicy = policy;
989 virtual_hook(SetNumericalPrecision, &policy);
994 QSql::NumericalPrecisionPolicy QSqlResult::numericalPrecisionPolicy() const
996 return d->precisionPolicy;
1001 bool QSqlResult::nextResult()
1003 if (driver()->hasFeature(QSqlDriver::MultipleResultSets)) {
1004 bool result = false;
1005 virtual_hook(NextResult, &result);
1012 Returns the low-level database handle for this result set
1013 wrapped in a QVariant or an invalid QVariant if there is no handle.
1015 \warning Use this with uttermost care and only if you know what you're doing.
1017 \warning The handle returned here can become a stale pointer if the result
1018 is modified (for example, if you clear it).
1020 \warning The handle can be NULL if the result was not executed yet.
1022 The handle returned here is database-dependent, you should query the type
1023 name of the variant before accessing it.
1025 This example retrieves the handle for a sqlite result:
1027 \snippet doc/src/snippets/code/src_sql_kernel_qsqlresult.cpp 1
1029 This snippet returns the handle for PostgreSQL or MySQL:
1031 \snippet doc/src/snippets/code/src_sql_kernel_qsqlresult.cpp 2
1033 \sa QSqlDriver::handle()
1035 QVariant QSqlResult::handle() const