Cannot create MySQL stored procedure / views from Qt

[pshah.mumbai]

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

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

To copy to clipboard, switch view to plain text mode 

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

[yuriry]

It looks like you forgot to associate the string with the query:

QSqlQuery me(create_tables_sql);

QSqlQuery me(create_tables_sql);

To copy to clipboard, switch view to plain text mode 

[pshah.mumbai]

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

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

To copy to clipboard, switch view to plain text mode 

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

[yuriry]

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]

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

// 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();
}

To copy to clipboard, switch view to plain text mode 

[pshah.mumbai]

//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();
}

//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();
}

To copy to clipboard, switch view to plain text mode 

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]

Now it seems to work when I remove the delimiter statements.

I will test it more and report back

[pshah.mumbai]

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";
}

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";
}

To copy to clipboard, switch view to plain text mode 

[pshah.mumbai]

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

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

me.exec(procedure);

To copy to clipboard, switch view to plain text mode 

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