PDA

View Full Version : Catch MSSQL stored procedure Output return value



ce_nort
2nd February 2020, 19:56
Hello,
I've been banging my head against this for a while and can only seem to get a blank value returned. I have followed the advice in this (https://forum.qt.io/topic/80428/how-to-handle-the-return-values-of-sql-stored-procedures-in-qt-application-for-inserting-delete-update) thread and this (https://doc.qt.io/qt-5/qsqlquery.html#approaches-to-binding-values) documentation by binding a parameter to QSql::Out. I still seem to get a blank result, even though I know that is wrong as I can call the procedure with a result in SSMS. Am I constructing the query incorrectly? See relevant code below:

Stored procedure definition:


CREATE procedure [dbo].[usp_SetGetNextRecordID] ( @TableName varchar(50),
@RecordID int OUTPUT )
as begin
set nocount on
update mt_Tables
set LastRecordID = LastRecordID+1,
@RecordID = LastRecordID+1
where TableName = @TableName

return
end
GO


Function definition for calling stored procedure:


QString DbController::runProcedureWithOutput(QString procedure_name, QMap<int, QString> values_list)
{
QString return_value;

QString query_string = QString("{CALL " + procedure_name + " (");

QMapIterator<int, QString> i(values_list);
while (i.hasNext()) {
i.next();
if (i.hasNext() == true) {
query_string += "?,";
} else {
query_string += "?";
}
}

query_string.append(")}");

QSqlQuery query;

query.prepare(query_string);
query.setForwardOnly(true);

int bound_out_value = 0; // Default

//Bind values...
QMapIterator<int, QString> j(values_list);
while (j.hasNext()) {
j.next();
if (!j.hasNext()) {
query.bindValue(j.key(), j.value(), QSql::Out); // Bind out value if last parameter
bound_out_value = j.key();
} else {
query.bindValue(j.key(), j.value());
}
}

query.exec();

while(query.next()) {
return_value = query.value(bound_out_value).toString();
}

return return_value;
}


Usage of function:



QMap<int, QString> params;
params.insert(0, "table_name");
params.insert(1, "@RecordID");

QString result = db_controller->runProcedureWithOutput("usp_SetGetNextRecordID", params);

qDebug() << result; // Always empty string when it shouldn't be, no SQL Errors thrown.


Any thoughts? Let me know if you need more context or have any questions. Help much appreciated in advance!

d_stranz
2nd February 2020, 21:44
while(query.next()) {
return_value = query.value(bound_out_value).toString();
}


This loop replaces the return_value contents every time through the loop, so if the last bound value returned by the query is an empty string, that's what you will get as a return from your function. Maybe you want "return_value +=" instead?

ce_nort
16th February 2020, 18:49
Apologies for the delay in reply to this, I had a bunch of other projects that took priority. Unfortunately this doesn't resolve the issue. After some testing, it is clear that the query isn't returning anything at all as any code that is within
while(query.next()) {} does not get called. I know that the procedure is executing successfully because one thing it does is actually happening (incrementing a value in the database), it's just the returned value that is not happening. Since it appears that nothing is returned by the query, I can only imagine that I am calling the procedure incorrectly. If anybody has any more insight, it would be greatly appreciated!

Added after 22 minutes:

After some more trial and error, it seems that the output is returned immediately so no query.next() is required. I did:


query.exec();
return_value = query.boundValue(bound_out_value).toString();

The above successfully captured the output.