[SOLVED] Qt5 - MySQL - create trigger not working
-
[quote author="SGaist" date="1409084557"]Hi,
Something is not clear. When you say copy/paste do you mean paste it in the sqlconsole ?[/quote]
Hi :)
yes exactly. I copy paste the generated output to the mysql console and it works.But when calling QSqlQuery exec method with a QString containg the complete stuff exec do not complain but the triggers are not created.
The _TRACE tables are created... But not the triggers...Greetings
-
Ok,
One thing you could try is to break the query to send one command at a time.
-
[quote author="SGaist" date="1409087690"]Ok,
One thing you could try is to break the query to send one command at a time.[/quote]
OK thank you.. Will try this tomorrow and the report...
Good n8 -
Hi,
i broke it down. And i can see that the following request works fine on the mysql console but not when calling QSqlQuery's exec method.@
DROP TRIGGER IF EXISTS TR_INSERT_warningLines;
DELIMITER $$
CREATE TRIGGER TR_INSERT_warningLines BEFORE INSERT ON warningLines
FOR EACH ROW
BEGIN
INSERT INTO warningLines_TRACE (warning_id,attribute,operator,value,ACTION_TIME, SYNC_VERSION)
VALUES (new.warning_id,new.attribute,new.operator,new.value,now(),0);
END $$
@Very strange because query.exec(...) does not complain.
Any idea?
Greetings
Nando -
I tried to call
@
query.prepare(request)
@and then
@
query.exec(...)
@now i get an error:
@
"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 $$
@must now check how to deal with this problem... will report.
Greetings
-
I solved it!
2 problems:
-
I had to split the @DROP TRIGGER IF EXISTS@
and the @CREATR TRIGGER@ part into two separate query executions (thank you SGaist for the tip.) -
do not use @DELIMITER $$@ because it seems to be only for mysql command line client.
With these changes everything works as expected :)
Greetings
-
-
Nice !
By the way, where did the original SQL query come from ?
-
[quote author="SGaist" date="1409129425"]Nice !
By the way, where did the original SQL query come from ?[/quote]
I (try to) generate the query (trace tables + triggers) in a generic way for all given tables of a db instance.
So the queries are generated dynamically.Greetings
Nando -
Ok, I was wondering where the DELIMITER $$ came from so you might have used a tool to generate the queries for you
-
Yes, i took this DELIMITER $$ stuff from some documentation.
Didn't know that this is mysql-client-console specific stuff.
Thanks for your help :) -
Dangerous documentation :D
You're welcome !
Happy coding :)