[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


  • Lifetime Qt Champion

    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


  • Lifetime Qt Champion

    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:

    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


  • Lifetime Qt Champion

    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


  • Lifetime Qt Champion

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


  • Lifetime Qt Champion

    Dangerous documentation :D

    You're welcome !

    Happy coding :)


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.