[SOLVED] Qt5 - MySQL - create trigger not working

[nando76]

Hi,
i am generating a request which should add a trigger to my database:

the generated request looks like:
@
generatedQuery = "

---

-- WARNING_TRACE

---

DROP TABLE IF EXISTS warning_TRACE;
CREATE TABLE warning_TRACE(
ID INT,
filter_id INT,
pilot_id INT,
name TEXT,
warningText TEXT,
ACTION_TIME TEXT,
SYNC_VERSION INT,
ACTION_TIME TIMESTAMP,
SYNC_VERSION INT);
DROP TRIGGER IF EXISTS TR_INSERT_warning_TRACE;
DELIMITER $$
CREATE TRIGGER TR_INSERT_warning_TRACE BEFORE INSERT ON warning_TRACE
FOR EACH ROW
BEGIN
INSERT INTO warning_TRACE (ID,filter_id,pilot_id,name,warningText,ACTION_TIME,SYNC_VERSION,ACTION_TIME, SYNC_VERSION)
VALUES (new.ID,new.filter_id,new.pilot_id,new.name,new.warningText,new.ACTION_TIME,new.SYNC_VERSION,now(),0);
END $$
"
@

If i call:
@
QSqlQuery query(cloudSqlManager->database());
@
and the execute the generated query like:
@
if(!query.exec(generatedQuery))
{
qCritical() << "SyncThread::processError: error writing error_log to database:";
qCritical() << query.lastError().text();
processError(cloudSqlManager, SyncProtocol::INTERNAL_SERVER_ERROR, query.lastError().text());
return false;
}
@

everything seems to be ok -> i get no error.

But the when i make a "SHOW TRIGGERS;" from mysql console no triggers has been created.
If i copy paste the generated query then it works and i can see the trigger when calling "SHOW TRIGGERS;" from mysql console.

I think somethin with QSqlQuery and exec() method could be the problem.

Maybe somebody has an idea what the problem could be.

Greetings
Nando

[SGaist]

Hi,

Something is not clear. When you say copy/paste do you mean paste it in the sqlconsole ?

[nando76]

[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

[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 :)