How to send a mysql script from QT application?
I want to execute the following SQL code using a QT application (entered from line edit).
DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An exception occurred';
END;
START TRANSACTION;
INSERT INTO `mschema`.`table1`
(`maxbudget`, `blocked`, `d_percentage`, `max discount`)
VALUES ('2250', '0', '.9', '.99');
INSERT INTO `mschema`.`table2`
(`name`,`image`,`date`,`fKey_id`)
VALUES ('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());
COMMIT;
END;
$$
DELIMITER ;
When i try to execute it, the exec() function does not return error but the database is not affected. When i copy this query and past in MySQL workbench, it is executed successfully. When i try to execute call mprocedure(); from qt application, it is executed successfully and made the required effect on my database
Re: How to send a mysql script from QT application?
Re: How to send a mysql script from QT application?
There are two issues I can see:
- QSqlQuery::exec() executes a single SQL statement and you are trying to pass two (DROP and CREATE PROCEDURE BEGIN ... END)
- The DELIMITER lines are not SQL, they are directives to the mysql commandline tool to ensure it passes the whole procedure in one block rather than stopping at the first semicolon. The Mysql command line tool is not involved here.
You should be able to execute the DROP in one exec() call, and the CREATE PROCEDURE (with the internal semicolons as-is) in a second. If you are trying to handle a user-provided script with Mysql command line tool syntax then:
- you need to write a simple parser to split the script into statement and send them one at at time
- you need to really trust your user not to bork your database
Re: How to send a mysql script from QT application?
I know it is too late, but thanks a lot.
smallcorrection:
into statement should be into 2 statements