From 6d0c8825f9bee01e4962a82d78a310e11ae8d17c Mon Sep 17 00:00:00 2001 From: Robert Szefner Date: Fri, 10 Nov 2017 08:35:40 +0100 Subject: QPSQL: Add support for multiple result sets This change adds support for multiple result sets in PostgreSQL. [Important Behavior Changes] The QPSQL driver now supports multiple result sets. Since QPSQL previously did not support multiple result sets, there may be some compatibility issues with the existing code that executed several queries as one and were expecting to get the results of the last one. In this case use QSqlQuery::nextResult() to move to the last result set. [ChangeLog][QtSql][QPSQL] Added support for multiple result sets Change-Id: I2bfc91f512c4dac83116f3aa42833839a6da084c Reviewed-by: Andy Shaw --- src/plugins/sqldrivers/psql/qsql_psql.cpp | 49 ++++++++++++++++++++- 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 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)); } -- cgit v1.2.3