Receiving multiple result sets of return value in SQL server stored procedure from Qt (QSqlQuery)

[alizadeh91]

Hello there,
I'm using QODBC to connect with SQLServer. In SQLServer I've wrote a stored procedure that has 2 out params, 1 return param with a select query (test table):

USE [S4A]
GO
/****** Object:  StoredProcedure [dbo].[testOutParam]    Script Date: 11/11/2018 3:28:50 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[testOutParam]
	-- Add the parameters for the stored procedure here
	@iOut int = NULL OUTPUT,        -- out1
	@iOut2 int = NULL OUTPUT      -- out2
AS
BEGIN
	DECLARE @return int                 -- return param 1
	SET @iOut = 101;
	SET @iOut2 = 102;
	SET @return = 103;

	SELECT @iOut2 as test;               -- Select query

	return @return;
END

I can execute this procedure in SQL-Server-Management-Studio without any problem and I get some result sets of outputs, return value and also the table.
I want to query this stored procedure from Qt and be able to get all outputs, return value and also the queried test table.
Therefore, I've wrote following code:

                 SQLStatement="EXECUTE ? = testOutParam ?,?";
                 Query.prepare(SQLStatement);
                 CountReturnParam = 3;
                 for (int i=0; i<CountReturnParam; i++)
                 {
                     Query.bindValue(i, 0, QSql::Out);
                 }
                 SQLResult=Query.exec();

If there were no 'Select' query in the stored procedure, then I was able to get all the outputs and return value from this query. But, With the 'Select' query un-commented as here, the table is accessible via query but all the outputs and the return value are returned zero.

I'm guessing this is not supported in QSQLQuery right now based on Doc and this sentence:

Stored procedures that uses the return statement to return values, or return multiple result sets, are not fully supported. For specific details see SQL Database Drivers.

I want to ask if anyone could help me an appropriate solution to deal with that? Do i need to implement the driver? Or would it be easy at all?

Thanks for all....

[SGaist]

Hi,

Sorry no ready made solution however a starting point, I'd start by taking a look at the qsql_odbc.cpp file at the QODBCResult::exec method and compare it to an example of stored procedure management with ODBC. See if there's anything to change there.

[alizadeh91]

This post is deleted!

[alizadeh91]

@SGaist I'm checking the qsql_odbc.cpp file at the QODBCResult::exec, but can't find where is exactly the problem initiated.
I've found that in the method (exec) there is a switch clause that based on the boundValuesType, it calls SQLBindParameter.
In this sql function a pointer of each bound value is send to the sql.
After that switch clause, SQLExecute is called. After calling this method, all of boundedValues are filled. But If a Select query exists in the procedure the boundedValues remains 0! So the code seems right. Can't find where is the problem to fix it in this method.

[alizadeh91]

@SGaist I've also add following codes after SQLExecute() method to fetch more data:

    SQLLEN  indicator, RowCount;
    char *buf=(char*)malloc (255);

    do {
        // SQLNumResultCols() returns number of columns in result set.
        // If non zero use SQLFetch until SQL_NO_DATA returned
        retcode=SQLNumResultCols(d->hStmt, &columns);
//       	CHECK_ERROR(retcode, "SQLNumResultCols()", hstmt, SQL_HANDLE_STMT);
        if (columns > 0) {
            printf ("\nRows ...");
            while (SQLFetch(d->hStmt) != SQL_NO_DATA) {
            // Loop through the columns
            memset (buf, ' ', 255);
                printf ("\n");
                for (i = 1; i <= columns; i++) {
                    // retrieve column data as a string
                retcode = SQLGetData(d->hStmt, i, SQL_C_CHAR,
                                         buf, 255, &indicator);
                if (SQL_SUCCEEDED(retcode)) {
                    // Handle null columns
                    if (indicator == SQL_NULL_DATA)
                            strcpy (buf, "NULL");
//                        buf=rtrim(buf, ' ');
                        printf("%10s ", buf);
                }
            }
            }
            printf ("\nEnd ...");
        }
        else {
            // SQLRowCount returns number of rows affected by INSERT, UPDATE,
            // DELETE or (if supported by the driver) number of rows returned
            // by a SELECT
            retcode=SQLRowCount(d->hStmt, &RowCount);
        }

        if (columns==0) {
            printf ("\n-----------");
        }
    } while ((retcode=SQLMoreResults(d->hStmt)) == SQL_SUCCESS);

And it prints out the whole dataTable! :) But I want to access the dataTable via QSqlQuery object.

Actually with adding this code, we have all output params but the QSqlQuery has no data about table but we are taking all tableData in this code. I think I have to somehow add these tableData to the records. But don't know how.

[SGaist]

rInf seems to be where the results are stored.

[alizadeh91]

@SGaist Yes, It seems following code is saving the results:

        retcode=SQLNumResultCols(d->hStmt, &columns);

        qDebug() << "cols: " << columns;
        if (columns) {
            setSelect(true);
            for (int i = 0; i < columns; ++i) {
                d->rInf.append(qMakeFieldInfo(d, i));
            }
            d->fieldCache.resize(columns);
        } else {
            setSelect(false);
        }
        setActive(true);

It creates some FieldInfo and appends them into QSqlRecord. But It just save some data like column names not the data (rows).

Also note that when SQLMoreResults and SQLFetch is called from my newly added code, all the table data are cleared in the buffer! but the problem with return values is fixed too!, so there no table data anymore in QSqlQuery object. So confused!

What I'm seeking now is how to store the table data (that I'm now getting from buffer in new added code) into QSqlQuery, so that the developer can get it by calling QSqlQuery::value, QSqlQuery::first and other methods. It doesn't seems to be very hard, but I'm not very familiar with how those data are stored (seems they are saving in some shared memory).

[SGaist]

I'd look at nextResult which is likely the place that is going to load data from the query made.

[alizadeh91]

@SGaist Yesss! calling it solved my problem. Thanks :)

[SGaist]

I'm not sure I'm following you on that one, can you clarify what you did ?

[JonB]

Sorry, I'm a bit late to the party. But http://doc.qt.io/qt-5/qsqlquery.html#nextResult is indeed, and always been, the way you move across multiple result sets (assuming your database supports them). Even in my non-Qt work with MS SQL Server this was how it worked there too.

[alizadeh91]

@SGaist I've just called QSQLQuery::nextResult after executing and reading dataTable. Then I was able to read another result sets.

[alizadeh91]

@JonB Yes, I was not familiar with that. Also in QSqlQuery doc, there was some disappointing states that the multiple result sets might not be supported.

[SGaist]

So in the end, did you had to modify the driver for your stored procedure ?

[alizadeh91]

@SGaist Not at all.

[SGaist]

So just looping with QSqlQuery::next got you the result of the stored procedure as expected ?

[alizadeh91]

@SGaist I did it this way for reading return params:

                 Query.nextResult();        // after reading table values
                 //Get Return Parameter
                 for (int i=0; i<CountReturnParam; i++)
                 {
                     qDebug() << QString("Returnparam %1 = %2")
                                 .arg(i).arg(Query.boundValue(i).toString());
                 }