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

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());
                 }