PDA

View Full Version : Stored procedure and ODBC



filya
1st December 2006, 23:23
I have a strange problem with QT handling stored procedures.
I’m trying to execute stored procedure on MS SQL using ODBC and QT
Assume that I already established connection to the database.

QSqlQueryModel model;
model.setQuery("{CALL test_proc}", m_Database );

if test_proc procedure is defined like

CREATE PROCEDURE TEST_PROC
AS
SELECT TOP 10 TITLE_ID, TITLE FROM TITLES

Everything is OK. The model receives data and I can display it in the table.

If test_proc procedure is defined like

CREATE PROCEDURE TEST_PROC
AS
SELECT TOP 10 TITLE_ID, TITLE FROM TITLES
RETURN 0

The model thinks that there is no data to receive and shows me that record set is empty.

I have tried to use the same procedures without using model but result was the same.
No matter if model is used or QSqlQuery as soon as I add RETURN 0 or any return for that matter I’ll receive no result.
Please do not suggest removing RETURN because this is a vendor stored procedure and can not be modified by me.
The procedure above is for demonstrating my point and is not representing actual data.

Thank you for you help

e8johan
19th January 2007, 15:12
I'm not an SQL expert - but doesn't the "RETURN 0" just return a zero? Anyway, I guess that it returns something useful in real life. The QSqlQuery documentation for the next (http://doc.trolltech.com/4.2/qsqlquery.html#next) method says "Retrieves the next record in the result, if available, and positions the query on the retrieved record. Note that the result must be in an active state and isSelect() must return true before calling this function or it will do nothing and return false.". This tells me that a non-select return value will give you the results that you are experiencing. I suggest that you bring this up in the Task Tracker (http://www.trolltech.com/developer/task-tracker).

zolfaghari
15th March 2010, 09:40
I have your problem too.
If you use a declare statement too, I think you will fail.
I searched a lot. I find that it is probably ODBC limitations.
you can not have return statement in odbc connection.
if you use a QSqlQuery and set forwardOnly param to true and call your sp with this signiture you will not fail :"{CALL test_proc()}"