Results 1 to 12 of 12

Thread: QSqlQuery / SQL2005, stored procedure does not return string

  1. #1
    Join Date
    Mar 2010
    Location
    Capelle aan den IJssel, Netherlands
    Posts
    24
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows Symbian S60

    Default QSqlQuery / SQL2005, stored procedure does not return string

    Hello,

    I have a stored procedure declared in a SQL Server 2005 database, declared as followed:

    Qt Code:
    1. CREATE PROCEDURE insertCustomer
    2. @CustomerMap varchar(32),
    3. @Result int OUT,
    4. @Message nvarchar(255) OUT AS
    5.  
    6. SELECT * FROM Customers WHERE MapName = @CustomerMap
    7. IF @@ROWCOUNT <> 0 BEGIN
    8. SET @Message='Er bestaat al een klantmap met deze naam...'
    9. SET @Result=1
    10. RETURN
    11. END
    12.  
    13. INSERT INTO Customers (MapName, Status) VALUES (@CustomerMap, 'N')
    14. SET @Message='OK'
    15. SET @Result=0
    16. RETURN
    To copy to clipboard, switch view to plain text mode 

    In Qt, I want to execute this procedure, and get my result and message back.

    I amusing the following code:

    Qt Code:
    1. OdbcDbHandler(QString OdbcDriver, QString Host, QString Database, QString User, QString Password)
    2. {
    3. m_dbDriver = OdbcDriver;
    4. m_dbHost = Host;
    5. m_dbDatabase = Database;
    6. m_dbUser = User;
    7. m_dbPass = Password;
    8.  
    9. QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    10. db.setDatabaseName("Driver={" + OdbcDriver + "};Server=" + Host + ";Database=" + Database + ";");
    11. db.setUserName(m_dbUser);
    12. db.setPassword(m_dbPass);
    13. }
    14.  
    15. bool open()
    16. {
    17. return QSqlDatabase::database().open();
    18. }
    19.  
    20. bool execProcedure (DbProcedure Procedure)
    21. {
    22. QSqlDatabase db = QSqlDatabase::database();
    23.  
    24. if (!db.isOpen())
    25. {
    26. if (!db.open())
    27. return false;
    28. }
    29.  
    30. QSqlQuery query;
    31.  
    32. query.prepare("{ CALL insertCustomer (?, ?, ?) }");
    33.  
    34. QVariant Map = "customermap";
    35. QVariant Result = -1;
    36. QVariant Message("");
    37.  
    38. query.bindValue(0, Map);
    39. query.bindValue(1, Result, QSql::Out);
    40. query.bindValue(2, Message, QSql::Out);
    41.  
    42. if(!query.exec())
    43. {
    44. QString error = query.lastError().text();
    45. return false;
    46. }
    47.  
    48. query.nextResult();
    49.  
    50. Result = query.boundValue(1);
    51. Message = query.boundValue(2);
    52.  
    53. return true;
    54. }
    To copy to clipboard, switch view to plain text mode 

    The stored procedures executes as expected and my result code is set, to 0 or 1, depending on what i put int.
    However, Message always seems to be empty, while this should be set too.

    Anyone any clue what I'm doing wrong?

  2. #2
    Join Date
    Mar 2010
    Location
    Capelle aan den IJssel, Netherlands
    Posts
    24
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows Symbian S60

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    Anyone with the same experience here or any pointer on what could be wrong?

  3. #3
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    I had a similar problem with receiving empty strings from QSqlRecord values using a simply QSqlQuery even though numeric types populate and I can insert and update the table values just fine. Unfortunately I have not figured out a way to get it working yet. I believe it is a collation problem specific to QODBC which may be dependent on what driver you are using. I'm using freeTDS version 8.0 on Ubuntu ... what are you using?

  4. #4
    Join Date
    Mar 2010
    Location
    Capelle aan den IJssel, Netherlands
    Posts
    24
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows Symbian S60

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    I am using two Win XP Pro stations. I use QODBC and set the driver to the SQL Native Client. Could be it conflicts right there. Rather odd it would give numbers but interprets strings wrong still though =/

  5. #5
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    Hey I think I just found your problem while I was reading more about ODBC support!

    ODBC Stored Procedure Support

    With Microsoft SQL Server the result set returned by a stored procedure that uses the return statement, or returns multiple result sets, will be accessible only if you set the query's forward only mode to forward using QSqlQuery::setForwardOnly().
    So in your code add the following call before your prepare statement:
    Qt Code:
    1. QSqlQuery query(db);
    2. query.setForwardOnly(true);
    3. query.prepare("{ CALL insertCustomer (?, ?, ?) }");
    To copy to clipboard, switch view to plain text mode 

    Now if only I could figure out my problem :cry:

  6. #6
    Join Date
    Mar 2010
    Location
    Capelle aan den IJssel, Netherlands
    Posts
    24
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows Symbian S60

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    Yes, I had found that solution, but it turns out, that option is set by default anyway and hence, does not solve the problem, unfortunately

  7. #7
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    Quote Originally Posted by _Stefan View Post
    Yes, I had found that solution, but it turns out, that option is set by default anyway and hence, does not solve the problem, unfortunately
    It's set to false by default though ... have you tried explicitly setting it true? If it still doesn't work I'd check if query.lastError().isValid() returns true ... if so check that error text() for better clues.

  8. #8
    Join Date
    Mar 2010
    Location
    Capelle aan den IJssel, Netherlands
    Posts
    24
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows Symbian S60

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    Yup tried setting it explicitly, same result.
    It says it has no errors at all, which would make sense, since it did actually execute the query.
    Just strings are not filled in =/

  9. #9
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    I solved my problem with empty strings it was because the qodbc driver was compiled with unicode support and I forgot unix/linux only does ASCII ... can check my thread here in case it's related to your problem:

    http://www.qtcentre.org/threads/2960...953#post138953

  10. #10
    Join Date
    Mar 2010
    Location
    Capelle aan den IJssel, Netherlands
    Posts
    24
    Thanks
    1
    Thanked 1 Time in 1 Post
    Qt products
    Qt4 Qt/Embedded
    Platforms
    Unix/X11 Windows Symbian S60

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    I tried including that define, still gave me nothing.
    But I also do not run this on Linux, but XP Pro.

    Still a weird problem. Could still be in conversion somewhere, I just do not have a clue on how you would solve that ;p

  11. #11
    Join Date
    Apr 2010
    Location
    United States
    Posts
    16
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    Bummer ... yeah that would be a unix only problem with the unicode. Does your stored procedure behave as expected when you run it from query analyzer or another app (like osql from the commandline)? If you remove the QT layer entirely and it works you'd at least be certain it's something in your QT code or with the QODBC sqldriver. Try throwing a break point into your app and following it into QT to see what's happening in the QSqlDriver when it's executed as that's what narrowed it down for me.

  12. #12
    Join Date
    Apr 2014
    Posts
    1
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSqlQuery / SQL2005, stored procedure does not return string

    I had a similar problem on Linux, what worked for me was to initialize the variable that's expecting the return string with enough spaces to hold the longest message.
    So instead of
    QVariant Message("");
    use
    QVariant Message(" ");

    I hope this helps someone, I wasted a lot of time trying to figure this out!

    Randy

Similar Threads

  1. Stored procedure and ODBC
    By filya in forum Qt Programming
    Replies: 2
    Last Post: 15th March 2010, 09:40
  2. Problem calling stored procedure.. Help!
    By triperzonak in forum Qt Programming
    Replies: 4
    Last Post: 15th March 2010, 09:34
  3. problem in using stored procedure
    By zolfaghari in forum Qt Programming
    Replies: 0
    Last Post: 15th March 2010, 09:25
  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

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.