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 ;

DELIMITER $$

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

	SELECT * FROM RD0500;

END$$

DELIMITER ;

To copy to clipboard, switch view to plain text mode 

it my code

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

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

To copy to clipboard, switch view to plain text mode 

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();
...

QSqlQuery oQuery;

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

To copy to clipboard, switch view to plain text mode 

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 ;

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 ;

To copy to clipboard, switch view to plain text mode 

qt code

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

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

To copy to clipboard, switch view to plain text mode 

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-...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).