[SOLVED] Qt5 - MySQL - create trigger not working

[SGaist]

Ok,

One thing you could try is to break the query to send one command at a time.

[nando76]

[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

[nando76]

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

[nando76]

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

[nando76]

I solved it!

2 problems:

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

2. do not use @DELIMITER $$@ because it seems to be only for mysql command line client.

With these changes everything works as expected :)

Greetings

[SGaist]

Nice !

By the way, where did the original SQL query come from ?

[nando76]

[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

[SGaist]

Ok, I was wondering where the DELIMITER $$ came from so you might have used a tool to generate the queries for you

[nando76]

Yes, i took this DELIMITER $$ stuff from some documentation.
Didn't know that this is mysql-client-console specific stuff.
Thanks for your help :)

[SGaist]

Dangerous documentation :D

You're welcome !

Happy coding :)