1 /****************************************************************************
3 ** Copyright (C) 2012 Digia Plc and/or its subsidiary(-ies).
4 ** Contact: http://www.qt-project.org/legal
6 ** This file is part of the QtSql module of the Qt Toolkit.
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 Digia. For licensing terms and
14 ** conditions see http://qt.digia.com/licensing. For further information
15 ** use the contact form at http://qt.digia.com/contact-us.
17 ** GNU Lesser General Public License Usage
18 ** Alternatively, this file may be used under the terms of the GNU Lesser
19 ** General Public License version 2.1 as published by the Free Software
20 ** Foundation and appearing in the file LICENSE.LGPL included in the
21 ** packaging of this file. Please review the following information to
22 ** ensure the GNU Lesser General Public License version 2.1 requirements
23 ** will be met: http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html.
25 ** In addition, as a special exception, Digia gives you certain additional
26 ** rights. These rights are described in the Digia Qt LGPL Exception
27 ** version 1.1, included in the file LGPL_EXCEPTION.txt in this package.
29 ** GNU General Public License Usage
30 ** Alternatively, this file may be used under the terms of the GNU
31 ** General Public License version 3.0 as published by the Free Software
32 ** Foundation and appearing in the file LICENSE.GPL included in the
33 ** packaging of this file. Please review the following information to
34 ** ensure the GNU General Public License version 3.0 requirements will be
35 ** met: http://www.gnu.org/copyleft/gpl.html.
40 ****************************************************************************/
42 #include "qsqlresult.h"
47 #include "qsqlerror.h"
48 #include "qsqlfield.h"
49 #include "qsqlrecord.h"
51 #include "qsqldriver.h"
58 QHolder(const QString& hldr = QString(), int index = -1): holderName(hldr), holderPos(index) {}
59 bool operator==(const QHolder& h) const { return h.holderPos == holderPos && h.holderName == holderName; }
60 bool operator!=(const QHolder& h) const { return h.holderPos != holderPos || h.holderName != holderName; }
65 class QSqlResultPrivate
68 QSqlResultPrivate(QSqlResult* d)
69 : q(d), idx(QSql::BeforeFirstRow), active(false),
70 isSel(false), forwardOnly(false), precisionPolicy(QSql::LowPrecisionDouble), bindCount(0), binds(QSqlResult::PositionalBinding)
97 QString positionalToNamedBinding();
98 QString namedToPositionalBinding();
99 QString holderAt(int index) const;
103 QPointer<QSqlDriver> sqldriver;
110 QSql::NumericalPrecisionPolicy precisionPolicy;
113 QSqlResult::BindingSyntax binds;
115 QString executedQuery;
116 QHash<int, QSql::ParamType> types;
117 QVector<QVariant> values;
118 typedef QHash<QString, QList<int> > IndexMap;
121 typedef QVector<QHolder> QHolderVector;
122 QHolderVector holders;
125 static QString qFieldSerial(int);
127 QString QSqlResultPrivate::holderAt(int index) const
129 return holders.size() > index ? holders.at(index).holderName : qFieldSerial(index);
132 // return a unique id for bound names
133 static QString qFieldSerial(int i)
135 ushort arr[] = { ':', 'f', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
136 ushort *ptr = &arr[1];
139 *(++ptr) = 'a' + i % 16;
143 return QString(reinterpret_cast<const QChar *>(arr), int(ptr - arr) + 1);
146 static bool qIsAlnum(QChar ch)
148 uint u = uint(ch.unicode());
149 // matches [a-zA-Z0-9_]
150 return u - 'a' < 26 || u - 'A' < 26 || u - '0' < 10 || u == '_';
153 QString QSqlResultPrivate::positionalToNamedBinding()
158 result.reserve(n * 5 / 4);
159 bool inQuote = false;
162 for (int i = 0; i < n; ++i) {
163 QChar ch = sql.at(i);
164 if (ch == QLatin1Char('?') && !inQuote) {
165 result += qFieldSerial(count++);
167 if (ch == QLatin1Char('\''))
176 QString QSqlResultPrivate::namedToPositionalBinding()
182 bool inQuote = false;
187 QChar ch = sql.at(i);
188 if (ch == QLatin1Char(':') && !inQuote
189 && (i == 0 || sql.at(i - 1) != QLatin1Char(':'))
190 && (i + 1 < n && qIsAlnum(sql.at(i + 1)))) {
192 while (pos < n && qIsAlnum(sql.at(pos)))
194 QString holder(sql.mid(i, pos - i));
195 indexes[holder].append(count++);
196 holders.append(QHolder(holder, i));
197 result += QLatin1Char('?');
200 if (ch == QLatin1Char('\''))
207 values.resize(holders.size());
213 \brief The QSqlResult class provides an abstract interface for
214 accessing data from specific SQL databases.
219 Normally, you would use QSqlQuery instead of QSqlResult, since
220 QSqlQuery provides a generic wrapper for database-specific
221 implementations of QSqlResult.
223 If you are implementing your own SQL driver (by subclassing
224 QSqlDriver), you will need to provide your own QSqlResult
225 subclass that implements all the pure virtual functions and other
226 virtual functions that you need.
232 \enum QSqlResult::BindingSyntax
234 This enum type specifies the different syntaxes for specifying
235 placeholders in prepared queries.
237 \value PositionalBinding Use the ODBC-style positional syntax, with "?" as placeholders.
238 \value NamedBinding Use the Oracle-style syntax with named placeholders (e.g., ":id")
244 \enum QSqlResult::VirtualHookOperation
249 Creates a QSqlResult using database driver \a db. The object is
250 initialized to an inactive state.
252 \sa isActive(), driver()
255 QSqlResult::QSqlResult(const QSqlDriver *db)
257 d = new QSqlResultPrivate(this);
258 d->sqldriver = const_cast<QSqlDriver *>(db);
260 setNumericalPrecisionPolicy(db->numericalPrecisionPolicy());
265 Destroys the object and frees any allocated resources.
268 QSqlResult::~QSqlResult()
274 Sets the current query for the result to \a query. You must call
275 reset() to execute the query on the database.
277 \sa reset(), lastQuery()
280 void QSqlResult::setQuery(const QString& query)
286 Returns the current SQL query text, or an empty string if there
292 QString QSqlResult::lastQuery() const
298 Returns the current (zero-based) row position of the result. May
299 return the special values QSql::BeforeFirstRow or
302 \sa setAt(), isValid()
304 int QSqlResult::at() const
311 Returns true if the result is positioned on a valid record (that
312 is, the result is not positioned before the first or after the
313 last record); otherwise returns false.
318 bool QSqlResult::isValid() const
320 return d->idx != QSql::BeforeFirstRow && d->idx != QSql::AfterLastRow;
324 \fn bool QSqlResult::isNull(int index)
326 Returns true if the field at position \a index in the current row
327 is null; otherwise returns false.
331 Returns true if the result has records to be retrieved; otherwise
335 bool QSqlResult::isActive() const
341 This function is provided for derived classes to set the
342 internal (zero-based) row position to \a index.
347 void QSqlResult::setAt(int index)
354 This function is provided for derived classes to indicate whether
355 or not the current statement is a SQL \c SELECT statement. The \a
356 select parameter should be true if the statement is a \c SELECT
357 statement; otherwise it should be false.
362 void QSqlResult::setSelect(bool select)
368 Returns true if the current result is from a \c SELECT statement;
369 otherwise returns false.
374 bool QSqlResult::isSelect() const
380 Returns the driver associated with the result. This is the object
381 that was passed to the constructor.
384 const QSqlDriver *QSqlResult::driver() const
391 This function is provided for derived classes to set the internal
392 active state to \a active.
397 void QSqlResult::setActive(bool active)
399 if (active && d->executedQuery.isEmpty())
400 d->executedQuery = d->sql;
406 This function is provided for derived classes to set the last
412 void QSqlResult::setLastError(const QSqlError &error)
419 Returns the last error associated with the result.
422 QSqlError QSqlResult::lastError() const
428 \fn int QSqlResult::size()
430 Returns the size of the \c SELECT result, or -1 if it cannot be
431 determined or if the query is not a \c SELECT statement.
433 \sa numRowsAffected()
437 \fn int QSqlResult::numRowsAffected()
439 Returns the number of rows affected by the last query executed, or
440 -1 if it cannot be determined or if the query is a \c SELECT
447 \fn QVariant QSqlResult::data(int index)
449 Returns the data for field \a index in the current row as
450 a QVariant. This function is only called if the result is in
451 an active state and is positioned on a valid record and \a index is
452 non-negative. Derived classes must reimplement this function and
453 return the value of field \a index, or QVariant() if it cannot be
458 \fn bool QSqlResult::reset(const QString &query)
460 Sets the result to use the SQL statement \a query for subsequent
463 Derived classes must reimplement this function and apply the \a
464 query to the database. This function is only called after the
465 result is set to an inactive state and is positioned before the
466 first record of the new result. Derived classes should return
467 true if the query was successful and ready to be used, or false
474 \fn bool QSqlResult::fetch(int index)
476 Positions the result to an arbitrary (zero-based) row \a index.
478 This function is only called if the result is in an active state.
479 Derived classes must reimplement this function and position the
480 result to the row \a index, and call setAt() with an appropriate
481 value. Return true to indicate success, or false to signify
484 \sa isActive(), fetchFirst(), fetchLast(), fetchNext(), fetchPrevious()
488 \fn bool QSqlResult::fetchFirst()
490 Positions the result to the first record (row 0) in the result.
492 This function is only called if the result is in an active state.
493 Derived classes must reimplement this function and position the
494 result to the first record, and call setAt() with an appropriate
495 value. Return true to indicate success, or false to signify
498 \sa fetch(), fetchLast()
502 \fn bool QSqlResult::fetchLast()
504 Positions the result to the last record (last row) in the result.
506 This function is only called if the result is in an active state.
507 Derived classes must reimplement this function and position the
508 result to the last record, and call setAt() with an appropriate
509 value. Return true to indicate success, or false to signify
512 \sa fetch(), fetchFirst()
516 Positions the result to the next available record (row) in the
519 This function is only called if the result is in an active
520 state. The default implementation calls fetch() with the next
521 index. Derived classes can reimplement this function and position
522 the result to the next record in some other way, and call setAt()
523 with an appropriate value. Return true to indicate success, or
524 false to signify failure.
526 \sa fetch(), fetchPrevious()
529 bool QSqlResult::fetchNext()
531 return fetch(at() + 1);
535 Positions the result to the previous record (row) in the result.
537 This function is only called if the result is in an active state.
538 The default implementation calls fetch() with the previous index.
539 Derived classes can reimplement this function and position the
540 result to the next record in some other way, and call setAt()
541 with an appropriate value. Return true to indicate success, or
542 false to signify failure.
545 bool QSqlResult::fetchPrevious()
547 return fetch(at() - 1);
551 Returns true if you can only scroll forward through the result
552 set; otherwise returns false.
556 bool QSqlResult::isForwardOnly() const
558 return d->forwardOnly;
562 Sets forward only mode to \a forward. If \a forward is true, only
563 fetchNext() is allowed for navigating the results. Forward only
564 mode needs much less memory since results do not have to be
565 cached. By default, this feature is disabled.
567 Setting forward only to false is a suggestion to the database engine,
568 which has the final say on whether a result set is forward only or
569 scrollable. isForwardOnly() will always return the correct status of
572 \note Calling setForwardOnly after execution of the query will result
573 in unexpected results at best, and crashes at worst.
575 \sa isForwardOnly(), fetchNext(), QSqlQuery::setForwardOnly()
577 void QSqlResult::setForwardOnly(bool forward)
579 d->forwardOnly = forward;
583 Prepares the given \a query, using the underlying database
584 functionality where possible. Returns true if the query is
585 prepared successfully; otherwise returns false.
589 bool QSqlResult::savePrepare(const QString& query)
595 if (!driver()->hasFeature(QSqlDriver::PreparedQueries))
596 return prepare(query);
598 if (driver()->hasFeature(QSqlDriver::NamedPlaceholders)) {
599 // parse the query to memorize parameter location
600 d->namedToPositionalBinding();
601 d->executedQuery = d->positionalToNamedBinding();
603 d->executedQuery = d->namedToPositionalBinding();
605 return prepare(d->executedQuery);
609 Prepares the given \a query for execution; the query will normally
610 use placeholders so that it can be executed repeatedly. Returns
611 true if the query is prepared successfully; otherwise returns false.
615 bool QSqlResult::prepare(const QString& query)
617 if (d->holders.isEmpty()) {
618 int n = query.size();
620 bool inQuote = false;
624 QChar ch = query.at(i);
625 if (ch == QLatin1Char(':') && !inQuote
626 && (i == 0 || query.at(i - 1) != QLatin1Char(':'))
627 && (i + 1 < n && qIsAlnum(query.at(i + 1)))) {
629 while (pos < n && qIsAlnum(query.at(pos)))
632 QString holder(query.mid(i, pos - i));
633 d->indexes[holder].append(d->holders.size());
634 d->holders.append(QHolder(holder, i));
637 if (ch == QLatin1Char('\''))
642 d->values.resize(d->holders.size());
645 return true; // fake prepares should always succeed
649 Executes the query, returning true if successful; otherwise returns
654 bool QSqlResult::exec()
657 // fake preparation - just replace the placeholders..
658 QString query = lastQuery();
659 if (d->binds == NamedBinding) {
663 for (i = d->holders.count() - 1; i >= 0; --i) {
664 holder = d->holders.at(i).holderName;
665 val = d->values.value(d->indexes.value(holder).value(0,-1));
666 QSqlField f(QLatin1String(""), val.type());
668 query = query.replace(d->holders.at(i).holderPos,
669 holder.length(), driver()->formatValue(f));
675 for (idx = 0; idx < d->values.count(); ++idx) {
676 i = query.indexOf(QLatin1Char('?'), i);
679 QVariant var = d->values.value(idx);
680 QSqlField f(QLatin1String(""), var.type());
685 val = driver()->formatValue(f);
686 query = query.replace(i, 1, driver()->formatValue(f));
691 // have to retain the original query with placeholders
692 QString orig = lastQuery();
694 d->executedQuery = query;
701 Binds the value \a val of parameter type \a paramType to position \a index
702 in the current record (row).
706 void QSqlResult::bindValue(int index, const QVariant& val, QSql::ParamType paramType)
708 d->binds = PositionalBinding;
709 d->indexes[qFieldSerial(index)].append(index);
710 if (d->values.count() <= index)
711 d->values.resize(index + 1);
712 d->values[index] = val;
713 if (paramType != QSql::In || !d->types.isEmpty())
714 d->types[index] = paramType;
720 Binds the value \a val of parameter type \a paramType to the \a
721 placeholder name in the current record (row).
723 Values cannot be bound to multiple locations in the query, eg:
725 INSERT INTO testtable (id, name, samename) VALUES (:id, :name, :name)
727 Binding to name will bind to the first :name, but not the second.
729 \note Binding an undefined placeholder will result in undefined behavior.
731 \sa QSqlQuery::bindValue()
733 void QSqlResult::bindValue(const QString& placeholder, const QVariant& val,
734 QSql::ParamType paramType)
736 d->binds = NamedBinding;
737 // if the index has already been set when doing emulated named
738 // bindings - don't reset it
739 QList<int> indexes = d->indexes.value(placeholder);
740 foreach (int idx, indexes) {
741 if (d->values.count() <= idx)
742 d->values.resize(idx + 1);
743 d->values[idx] = val;
744 if (paramType != QSql::In || !d->types.isEmpty())
745 d->types[idx] = paramType;
750 Binds the value \a val of parameter type \a paramType to the next
751 available position in the current record (row).
755 void QSqlResult::addBindValue(const QVariant& val, QSql::ParamType paramType)
757 d->binds = PositionalBinding;
758 bindValue(d->bindCount, val, paramType);
763 Returns the value bound at position \a index in the current record
766 \sa bindValue(), boundValues()
768 QVariant QSqlResult::boundValue(int index) const
770 return d->values.value(index);
776 Returns the value bound by the given \a placeholder name in the
777 current record (row).
781 QVariant QSqlResult::boundValue(const QString& placeholder) const
783 QList<int> indexes = d->indexes.value(placeholder);
784 return d->values.value(indexes.value(0,-1));
788 Returns the parameter type for the value bound at position \a index.
792 QSql::ParamType QSqlResult::bindValueType(int index) const
794 return d->types.value(index, QSql::In);
800 Returns the parameter type for the value bound with the given \a
803 QSql::ParamType QSqlResult::bindValueType(const QString& placeholder) const
805 return d->types.value(d->indexes.value(placeholder).value(0,-1), QSql::In);
809 Returns the number of bound values in the result.
813 int QSqlResult::boundValueCount() const
815 return d->values.count();
819 Returns a vector of the result's bound values for the current
822 \sa boundValueCount()
824 QVector<QVariant>& QSqlResult::boundValues() const
830 Returns the binding syntax used by prepared queries.
832 QSqlResult::BindingSyntax QSqlResult::bindingSyntax() const
838 Clears the entire result set and releases any associated
841 void QSqlResult::clear()
847 Returns the query that was actually executed. This may differ from
848 the query that was passed, for example if bound values were used
849 with a prepared query and the underlying database doesn't support
852 \sa exec(), setQuery()
854 QString QSqlResult::executedQuery() const
856 return d->executedQuery;
859 void QSqlResult::resetBindCount()
865 Returns the name of the bound value at position \a index in the
866 current record (row).
870 QString QSqlResult::boundValueName(int index) const
872 return d->holderAt(index);
876 Returns true if at least one of the query's bound values is a \c
877 QSql::Out or a QSql::InOut; otherwise returns false.
881 bool QSqlResult::hasOutValues() const
883 if (d->types.isEmpty())
885 QHash<int, QSql::ParamType>::ConstIterator it;
886 for (it = d->types.constBegin(); it != d->types.constEnd(); ++it) {
887 if (it.value() != QSql::In)
894 Returns the current record if the query is active; otherwise
895 returns an empty QSqlRecord.
897 The default implementation always returns an empty QSqlRecord.
901 QSqlRecord QSqlResult::record() const
907 Returns the object ID of the most recent inserted row if the
908 database supports it.
909 An invalid QVariant will be returned if the query did not
910 insert any value or if the database does not report the id back.
911 If more than one row was touched by the insert, the behavior is
914 Note that for Oracle databases the row's ROWID will be returned,
915 while for MySQL databases the row's auto-increment field will
918 \sa QSqlDriver::hasFeature()
920 QVariant QSqlResult::lastInsertId() const
927 void QSqlResult::virtual_hook(int, void *)
934 Executes a prepared query in batch mode if the driver supports it,
935 otherwise emulates a batch execution using bindValue() and exec().
936 QSqlDriver::hasFeature() can be used to find out whether a driver
937 supports batch execution.
939 Batch execution can be faster for large amounts of data since it
940 reduces network roundtrips.
942 For batch executions, bound values have to be provided as lists
943 of variants (QVariantList).
945 Each list must contain values of the same type. All lists must
946 contain equal amount of values (rows).
948 NULL values are passed in as typed QVariants, for example
949 \c {QVariant(QVariant::Int)} for an integer NULL value.
953 \snippet code/src_sql_kernel_qsqlresult.cpp 0
955 Here, we insert two rows into a SQL table, with each row containing three values.
957 \sa exec(), QSqlDriver::hasFeature()
959 bool QSqlResult::execBatch(bool arrayBind)
963 QVector<QVariant> values = d->values;
964 if (values.count() == 0)
966 for (int i = 0; i < values.at(0).toList().count(); ++i) {
967 for (int j = 0; j < values.count(); ++j)
968 bindValue(j, values.at(j).toList().at(i), QSql::In);
977 void QSqlResult::detachFromResultSet()
983 void QSqlResult::setNumericalPrecisionPolicy(QSql::NumericalPrecisionPolicy policy)
985 d->precisionPolicy = policy;
990 QSql::NumericalPrecisionPolicy QSqlResult::numericalPrecisionPolicy() const
992 return d->precisionPolicy;
997 bool QSqlResult::nextResult()
1003 Returns the low-level database handle for this result set
1004 wrapped in a QVariant or an invalid QVariant if there is no handle.
1006 \warning Use this with uttermost care and only if you know what you're doing.
1008 \warning The handle returned here can become a stale pointer if the result
1009 is modified (for example, if you clear it).
1011 \warning The handle can be NULL if the result was not executed yet.
1013 The handle returned here is database-dependent, you should query the type
1014 name of the variant before accessing it.
1016 This example retrieves the handle for a sqlite result:
1018 \snippet code/src_sql_kernel_qsqlresult.cpp 1
1020 This snippet returns the handle for PostgreSQL or MySQL:
1022 \snippet code/src_sql_kernel_qsqlresult.cpp 2
1024 \sa QSqlDriver::handle()
1026 QVariant QSqlResult::handle() const