Results 1 to 3 of 3

Thread: Catch MSSQL stored procedure Output return value

  1. #1
    Join Date
    Nov 2015
    Location
    Vermont
    Posts
    52
    Thanks
    15
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    MacOS X Windows

    Default Catch MSSQL stored procedure Output return value

    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 thread and this 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:
    Qt Code:
    1. CREATE procedure [dbo].[usp_SetGetNextRecordID] ( @TableName varchar(50),
    2. @RecordID int OUTPUT )
    3. as begin
    4. set nocount on
    5. update mt_Tables
    6. set LastRecordID = LastRecordID+1,
    7. @RecordID = LastRecordID+1
    8. where TableName = @TableName
    9.  
    10. return
    11. end
    12. GO
    To copy to clipboard, switch view to plain text mode 

    Function definition for calling stored procedure:
    Qt Code:
    1. QString DbController::runProcedureWithOutput(QString procedure_name, QMap<int, QString> values_list)
    2. {
    3. QString return_value;
    4.  
    5. QString query_string = QString("{CALL " + procedure_name + " (");
    6.  
    7. QMapIterator<int, QString> i(values_list);
    8. while (i.hasNext()) {
    9. i.next();
    10. if (i.hasNext() == true) {
    11. query_string += "?,";
    12. } else {
    13. query_string += "?";
    14. }
    15. }
    16.  
    17. query_string.append(")}");
    18.  
    19. QSqlQuery query;
    20.  
    21. query.prepare(query_string);
    22. query.setForwardOnly(true);
    23.  
    24. int bound_out_value = 0; // Default
    25.  
    26. //Bind values...
    27. QMapIterator<int, QString> j(values_list);
    28. while (j.hasNext()) {
    29. j.next();
    30. if (!j.hasNext()) {
    31. query.bindValue(j.key(), j.value(), QSql::Out); // Bind out value if last parameter
    32. bound_out_value = j.key();
    33. } else {
    34. query.bindValue(j.key(), j.value());
    35. }
    36. }
    37.  
    38. query.exec();
    39.  
    40. while(query.next()) {
    41. return_value = query.value(bound_out_value).toString();
    42. }
    43.  
    44. return return_value;
    45. }
    To copy to clipboard, switch view to plain text mode 

    Usage of function:
    Qt Code:
    1. QMap<int, QString> params;
    2. params.insert(0, "table_name");
    3. params.insert(1, "@RecordID");
    4.  
    5. QString result = db_controller->runProcedureWithOutput("usp_SetGetNextRecordID", params);
    6.  
    7. qDebug() << result; // Always empty string when it shouldn't be, no SQL Errors thrown.
    To copy to clipboard, switch view to plain text mode 

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

  2. #2
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,230
    Thanks
    302
    Thanked 864 Times in 851 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Catch MSSQL stored procedure Output return value

    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?
    Last edited by d_stranz; 2nd February 2020 at 22:26.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  3. #3
    Join Date
    Nov 2015
    Location
    Vermont
    Posts
    52
    Thanks
    15
    Thanked 1 Time in 1 Post
    Qt products
    Qt5
    Platforms
    MacOS X Windows

    Default Re: Catch MSSQL stored procedure Output return value

    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
    Qt Code:
    1. while(query.next()) {}
    To copy to clipboard, switch view to plain text mode 
    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:
    Qt Code:
    1. query.exec();
    2. return_value = query.boundValue(bound_out_value).toString();
    To copy to clipboard, switch view to plain text mode 
    The above successfully captured the output.
    Last edited by ce_nort; 16th February 2020 at 18:49.

  4. The following user says thank you to ce_nort for this useful post:

    d_stranz (17th February 2020)

Similar Threads

  1. view return stored procedure in table view
    By baradar in forum Qt Programming
    Replies: 5
    Last Post: 15th November 2014, 20:24
  2. Replies: 11
    Last Post: 11th April 2014, 15:11
  3. Qt4 Mysql stored procedure
    By lynnH in forum Qt Programming
    Replies: 2
    Last Post: 28th April 2010, 09:05
  4. qt how get out parameter of Stored Procedure?
    By yunpeng880 in forum Qt Programming
    Replies: 1
    Last Post: 23rd March 2009, 13:22
  5. Stored procedure return values problem
    By jgreetham in forum Qt Programming
    Replies: 7
    Last Post: 10th September 2007, 18:38

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.