Problem calling stored procedure.. Help!

[triperzonak]

hi,

im having trouble calling stored procedure..

sample code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `rdmssql`.`hello`$$
CREATE PROCEDURE `rdmssql`.`hello` ()
BEGIN

SELECT * FROM RD0500;

END$$

DELIMITER ;


it my code

QSqlQuery sql("Call hello()");
qDebug(sql.lastError().text());


but it return an error


PROCEDURE mydatabase.hello can't return a result set in the given context QMYSQL3: Unable to execute query


how to solve this?

im using Qt3.3.6 right now..
thanks.

[mummy]

Hello,

i use SqlQuery and SPs only with prepare, execute and ODBC driver. With some databases i have trouble without setting setForwardOnly.

For example :


QSqlQuery oQuery;

// Use ODBC with shown call syntax
oQuery.setForwardOnly( true);
oQuery.prepare( "{call hello()}");
oQuery.exec();
...



bye

[triperzonak]

using query.setforwardOnly(TRUE) is not working..

insert procedure


DELIMITER $$

DROP PROCEDURE IF EXISTS `MYDB`.`qtestproc`$$
CREATE PROCEDURE `qtestproc`(TABLENAME char(20),TABLEVALUES LONGTEXT)

BEGIN
SET @s =CONCAT("INSERT INTO ",TABLENAME," values(",TABLEVALUES,")");
PREPARE stmt FROM @s;
EXECUTE stmt;
END$$
DELIMITER ;


qt code


QSqlQuery q;
q.setForwardOnly( true);
q.prepare("Call `MYDB`.`qtestproc`("mytable","values");
q.exec();
qDebug(q.lastError().text());

still got same error : MYDB.qtestproc cant return a result set in a given context QMYSQL3: unable to execute query

im using qt3.3.6 with QMYSQL3

[triperzonak]

hi,

after searching i found out that this problem is a bug found at Qt 4.0.1 then later fixed in Qt4.1.3 (see http://trolltech.com/developer/task-tracker/index_html?method=entry&id=93292)

trolltech suggest a solution,


Patch from Trolltech:

==== //depot/qt/4.1/src/sql/drivers/mysql/qsql_mysql.cpp#7 (text) ====

@@ -947,7 +947,12 @@
if (isOpen())
close();

- unsigned int optionFlags = 0;
+ /* This is a hack to get MySQL's stored procedure support working.
+ Since a stored procedure _may_ return multiple result sets,
+ we have to enable CLIEN_MULTI_STATEMENTS here, otherwise _any_
+ stored procedure call will fail.
+ */
+ unsigned int optionFlags = CLIENT_MULTI_STATEMENTS;
const QStringList opts(connOpts.split(QLatin1Char(';'), QString::SkipEmptyParts));

// extract the real options from the string


since i found it in Qt 3 should i edit qsql_mysql.cpp too? and put the same code above?

if i dont have qsql_mysql.cpp should i configure qt again with sql support?

thanks

[zolfaghari]

try one of this :
1) mummy solution (don't forget { and })
2) use an exec statement instead of call(if you have only DDL statement int your SP).