[SOLVED] Qt5 - MySQL - create trigger not working
-
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 -
Hi,
Something is not clear. When you say copy/paste do you mean paste it in the sqlconsole ?
-
[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.
-
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
-
Dangerous documentation :D
You're welcome !
Happy coding :)