PDA

View Full Version : Cannot create MySQL stored procedure / views from Qt



pshah.mumbai
30th September 2008, 19:02
I am trying to create a stored proecure in MySQL from Qt (since i want to distribute my application)

But exec() will not create a stored procedure. I tried changing the delimiter and everything else that I could think of.

created tables and inserts the date but it does not create procedures and views.

Here is the code.



QString create_tables_sql;

create_tables_sql =
"DROP PROCEDURE IF EXISTS `test`.`GetStockTotal`;"
"CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`GetStockTotal`(IN curstockid INT(11), OUT stocktotal DECIMAL(15,2))"
"BEGIN"
"DECLARE stocktotal FLOAT DEFAULT 0;"
"SELECT opbalance INTO stocktotal from ledgers where curid = curstockid;"
"END;";

QSqlQuery me;
me.exec();



Are there any work around other than doing it directly from MySQL.

:crying:

yuriry
30th September 2008, 20:51
It looks like you forgot to associate the string with the query:


QSqlQuery me(create_tables_sql);

pshah.mumbai
1st October 2008, 09:55
yeah i did that. it does not work.

above code is incorrect. it has the me.exec(create_tables_sql)

:)

here is the correct code..



QString create_tables_sql;

create_tables_sql =
"DROP PROCEDURE IF EXISTS `test`.`GetStockTotal`;"
"CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`GetStockTotal`(IN curstockid INT(11), OUT stocktotal DECIMAL(15,2))"
"BEGIN"
"DECLARE stocktotal FLOAT DEFAULT 0;"
"SELECT opbalance INTO stocktotal from ledgers where curid = curstockid;"
"END;";

QSqlQuery me;
me.exec(create_tables_sql);



It does drop the procedure but the create procedure does not work.

yuriry
2nd October 2008, 05:17
Strange... I would expect drop procedure to be rolled back if create procedure fails, at least this is default behavior in PostgreSQL.

Have you tried to split the two commands and execute them one by one? Also, what does qDebug() << query.lastError() say?

Also, is it possible that create procedure fails because you define a local variable stocktotal with the same name as the name of the OUT parameter?

jpujolf
2nd October 2008, 08:39
Or perhaps you have to break your STORED in two sentences, managing transaction manually :



// Assuming DB is your QSqlDatabase...
if ( DB.transaction() )
{
QSqlQuery me;
me.exec ( "DROP PROCEDURE IF EXISTS `test`.`GetStockTotal`;" );

QString create_tables_sql =
"CREATE DEFINER=`root`@`localhost` PROCEDURE `test`.`GetStockTotal`(IN curstockid INT(11), OUT stocktotal DECIMAL(15,2))"
"BEGIN"
"DECLARE stocktotal FLOAT DEFAULT 0;"
"SELECT opbalance INTO stocktotal from ledgers where curid = curstockid;"
"END;";

me.exec(create_tables_sql);
DB.commit();
}

pshah.mumbai
8th October 2008, 12:19
//Main program
int main(int argc, char *argv[])
{
QApplication app(argc, argv);
QSqlDatabase db;
db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("localhost");
db.setUserName("root");
db.setPassword("root");
db.setDatabaseName("test");
if(db.open())
{
QString create_tables_sql;

create_tables_sql = "DELIMITER $$ \n\
CREATE PROCEDURE Fun() \n\
BEGIN \n\
SELECT * FROM test; \n\
END $$ \n\
DELIMITER ; ";

qDebug() << create_tables_sql;
QSqlQuery me;
if (!me.exec(create_tables_sql))
{
qDebug() << "Query exec problem";
; }
qDebug() << me.lastError(); //QSqlError(-1, "", "")
exit(1);
}
else
{
qDebug() << "Db not open";
exit(1);
}

return app.exec();
}

Query exec problem
QSqlError(1064, "QMYSQL: Unable to execute query", "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$
CREATE PROCEDURE Fun()
BEGIN
SELECT * FROM test;
END $$
DELIMI' at line 1")

pshah.mumbai
8th October 2008, 12:23
Now it seems to work when I remove the delimiter statements.

I will test it more and report back :D

pshah.mumbai
8th October 2008, 12:24
here is the code.



QString create_tables_sql;

create_tables_sql = "CREATE PROCEDURE Fun() \n\
BEGIN \n\
SELECT * FROM test; \n\
END";

qDebug() << create_tables_sql;
QSqlQuery me;
if (!me.exec(create_tables_sql))
{
qDebug() << "Query exec problem";
}

pshah.mumbai
8th October 2008, 17:22
Its working perfectly now.

1. Do not use the DELIMTER statements



QString procedure = "CREATE PROCEDURE Fun() \n\
BEGIN \n\
SELECT * FROM test; \n\
END ;";

me.exec(procedure);


2. Do not call drop procedure in the same query.