diff options
-rw-r--r-- | src/plugins/sqldrivers/psql/qsql_psql.cpp | 49 | ||||
-rw-r--r-- | tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp | 52 |
2 files changed, 96 insertions, 5 deletions
diff --git a/src/plugins/sqldrivers/psql/qsql_psql.cpp b/src/plugins/sqldrivers/psql/qsql_psql.cpp index 6de7797c19..dfca183ff0 100644 --- a/src/plugins/sqldrivers/psql/qsql_psql.cpp +++ b/src/plugins/sqldrivers/psql/qsql_psql.cpp @@ -152,6 +152,7 @@ protected: bool fetchFirst() override; bool fetchLast() override; bool fetchNext() override; + bool nextResult() override; QVariant data(int i) override; bool isNull(int field) override; bool reset (const QString &query) override; @@ -325,6 +326,7 @@ public: void deallocatePreparedStmt(); PGresult *result; + QList<PGresult*> nextResultSets; int currentSize; bool canFetchMoreRows; StatementId stmtId; @@ -479,6 +481,8 @@ void QPSQLResult::cleanup() if (d->result) PQclear(d->result); d->result = nullptr; + while (!d->nextResultSets.isEmpty()) + PQclear(d->nextResultSets.takeFirst()); if (d->stmtId != InvalidStatementId) d->drv_d_func()->finishQuery(d->stmtId); d->stmtId = InvalidStatementId; @@ -608,6 +612,39 @@ bool QPSQLResult::fetchNext() return true; } +bool QPSQLResult::nextResult() +{ + Q_D(QPSQLResult); + if (!isActive()) + return false; + + setAt(QSql::BeforeFirstRow); + + if (isForwardOnly()) { + if (d->canFetchMoreRows) { + // Skip all rows from current result set + while (d->result && PQresultStatus(d->result) == PGRES_SINGLE_TUPLE) { + PQclear(d->result); + d->result = d->drv_d_func()->getResult(d->stmtId); + } + d->canFetchMoreRows = false; + // Check for unexpected errors + if (d->result && PQresultStatus(d->result) == PGRES_FATAL_ERROR) + return d->processResults(); + } + // Fetch first result from next result set + if (d->result) + PQclear(d->result); + d->result = d->drv_d_func()->getResult(d->stmtId); + return d->processResults(); + } + + if (d->result) + PQclear(d->result); + d->result = d->nextResultSets.isEmpty() ? nullptr : d->nextResultSets.takeFirst(); + return d->processResults(); +} + QVariant QPSQLResult::data(int i) { Q_D(const QPSQLResult); @@ -729,6 +766,11 @@ bool QPSQLResult::reset (const QString& query) setForwardOnly(d->drv_d_func()->setSingleRowMode()); d->result = d->drv_d_func()->getResult(d->stmtId); + if (!isForwardOnly()) { + // Fetch all result sets right away + while (PGresult *nextResultSet = d->drv_d_func()->getResult(d->stmtId)) + d->nextResultSets.append(nextResultSet); + } return d->processResults(); } @@ -914,6 +956,11 @@ bool QPSQLResult::exec() setForwardOnly(d->drv_d_func()->setSingleRowMode()); d->result = d->drv_d_func()->getResult(d->stmtId); + if (!isForwardOnly()) { + // Fetch all result sets right away + while (PGresult *nextResultSet = d->drv_d_func()->getResult(d->stmtId)) + d->nextResultSets.append(nextResultSet); + } return d->processResults(); } @@ -1132,6 +1179,7 @@ bool QPSQLDriver::hasFeature(DriverFeature f) const case LastInsertId: case LowPrecisionNumbers: case EventNotifications: + case MultipleResultSets: case BLOB: return true; case PreparedQueries: @@ -1141,7 +1189,6 @@ bool QPSQLDriver::hasFeature(DriverFeature f) const case NamedPlaceholders: case SimpleLocking: case FinishQuery: - case MultipleResultSets: case CancelQuery: return false; case Unicode: diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp index 664ba9a9dc..a4a8a647a3 100644 --- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp +++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp @@ -2892,8 +2892,8 @@ void tst_QSqlQuery::nextResult() QSqlDatabase db = QSqlDatabase::database( dbName ); CHECK_DATABASE( db ); const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db); - if ( !db.driver()->hasFeature( QSqlDriver::MultipleResultSets ) || !db.driver()->hasFeature( QSqlDriver::BatchOperations ) ) - QSKIP( "DBMS does not support multiple result sets or batch operations"); + if (!db.driver()->hasFeature(QSqlDriver::MultipleResultSets)) + QSKIP("DBMS does not support multiple result sets"); QSqlQuery q( db ); @@ -3006,7 +3006,10 @@ void tst_QSqlQuery::nextResult() // Stored procedure with multiple result sets const QString procName(qTableName("proc_more_res", __FILE__, db)); - q.exec( QString( "DROP PROCEDURE %1;" ).arg( procName ) ); + if (dbType == QSqlDriver::PostgreSQL) + q.exec(QString("DROP FUNCTION %1(refcursor, refcursor);").arg(procName)); + else + q.exec(QString("DROP PROCEDURE %1;").arg(procName)); if (dbType == QSqlDriver::MySqlServer) QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1()" @@ -3024,6 +3027,16 @@ void tst_QSqlQuery::nextResult() "\nOPEN cursor1;" "\nOPEN cursor2;" "\nEND p1" ).arg( procName ).arg( tableName ).arg( tableName ) ) ); + else if (dbType == QSqlDriver::PostgreSQL) + QVERIFY_SQL(q, exec(QString("CREATE FUNCTION %1(ref1 refcursor, ref2 refcursor)" + "\nRETURNS SETOF refcursor AS $$" + "\nBEGIN" + "\nOPEN ref1 FOR SELECT id, text FROM %2;" + "\nRETURN NEXT ref1;" + "\nOPEN ref2 FOR SELECT empty, num, text, id FROM %2;" + "\nRETURN NEXT ref2;" + "\nEND;" + "\n$$ LANGUAGE plpgsql").arg(procName).arg(tableName))); else QVERIFY_SQL( q, exec( QString( "CREATE PROCEDURE %1" "\nAS" @@ -3033,10 +3046,32 @@ void tst_QSqlQuery::nextResult() if (dbType == QSqlDriver::MySqlServer || dbType == QSqlDriver::DB2) { q.setForwardOnly( true ); QVERIFY_SQL( q, exec( QString( "CALL %1()" ).arg( procName ) ) ); + } else if (dbType == QSqlDriver::PostgreSQL) { + // Returning multiple result sets from PostgreSQL stored procedure: + // http://sqlines.com/postgresql/how-to/return_result_set_from_stored_procedure + QVERIFY_SQL(q, exec(QString("BEGIN;" + "SELECT %1('cur1', 'cur2');" + "FETCH ALL IN cur1;" + "FETCH ALL IN cur2;" + "COMMIT;").arg(procName))); } else { QVERIFY_SQL( q, exec( QString( "EXEC %1" ).arg( procName ) ) ); } + if (dbType == QSqlDriver::PostgreSQL) { + // First result set - start of transaction + QVERIFY(!q.isSelect()); + QCOMPARE(q.numRowsAffected(), 0); + QVERIFY(q.nextResult()); + // Second result set contains cursor names + QVERIFY(q.isSelect()); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toString(), "cur1"); + QVERIFY(q.next()); + QCOMPARE(q.value(0).toString(), "cur2"); + QVERIFY(q.nextResult()); + } + for ( int i = 0; i < 4; i++ ) { QVERIFY_SQL( q, next() ); QCOMPARE( q.value( 0 ).toInt(), i+1 ); @@ -3061,12 +3096,21 @@ void tst_QSqlQuery::nextResult() QVERIFY( !q.isSelect() ); // ... but it's not a select QCOMPARE( q.numRowsAffected(), 0 ); // ... and no rows are affected (at least not with this procedure) } + if (dbType == QSqlDriver::PostgreSQL) { + // Last result set - commit transaction + QVERIFY(q.nextResult()); + QVERIFY(!q.isSelect()); + QCOMPARE(q.numRowsAffected(), 0); + } QVERIFY( !q.nextResult() ); QVERIFY( !q.isActive() ); - q.exec( QString( "DROP PROCEDURE %1;" ).arg( procName ) ); + if (dbType == QSqlDriver::PostgreSQL) + q.exec(QString("DROP FUNCTION %1(refcursor, refcursor);").arg(procName)); + else + q.exec(QString("DROP PROCEDURE %1;").arg(procName)); } |