Results 1 to 8 of 8

Thread: QSSqlQuery CREATE TRIGGER not working

  1. #1
    Join Date
    Feb 2016
    Posts
    21
    Qt products
    Qt5
    Platforms
    Unix/X11

    Default QSSqlQuery CREATE TRIGGER not working

    Hallo

    I found some questions about it, but no realy solution.

    I try

    Qt Code:
    1. query.exec("CREATE TRIGGER UpdateCategoriesLastModifiedTime UPDATE OF categories_description, categories_title, categories_status ON categories BEGIN UPDATE categories SET last_modified=CURRENT_TIMESTAMP WHERE categories_id=categories_id END")
    To copy to clipboard, switch view to plain text mode 
    and get the Error:

    Qt Code:
    1. incomplete input Der Befehl konnte nicht ausgeführt werden"
    2. Function Name: static bool Database::open(bool) "cannot commit - no transaction is active Der Datensatz konnte nicht abgeholt werden"
    3. Function Name: static bool Database::open(bool) "END"
    4. Function Name: static bool Database::open(bool) "near \"END\": syntax error Der Befehl konnte nicht ausgeführt werden"
    To copy to clipboard, switch view to plain text mode 

    Can anyone help me?

    lg Chris

  2. #2
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSSqlQuery CREATE TRIGGER not working

    I think that before END You have to put char ;
    Qt Code:
    1. query.exec("CREATE TRIGGER UpdateCategoriesLastModifiedTime UPDATE OF categories_description, categories_title, categories_status ON categories "
    2. "BEGIN "
    3. " UPDATE categories SET last_modified=CURRENT_TIMESTAMP WHERE categories_id=categories_id; "
    4. "END")
    To copy to clipboard, switch view to plain text mode 

  3. #3
    Join Date
    Feb 2016
    Posts
    21
    Qt products
    Qt5
    Platforms
    Unix/X11

    Default Re: QSSqlQuery CREATE TRIGGER not working

    Quote Originally Posted by Lesiok View Post
    I think that before END You have to put char ;
    Qt Code:
    1. query.exec("CREATE TRIGGER UpdateCategoriesLastModifiedTime UPDATE OF categories_description, categories_title, categories_status ON categories "
    2. "BEGIN "
    3. " UPDATE categories SET last_modified=CURRENT_TIMESTAMP WHERE categories_id=categories_id; "
    4. "END")
    To copy to clipboard, switch view to plain text mode 
    Hallo
    Thx, but i try some thinks also with a semicolon ";" before END. But nothing works.
    But the Error is
    Qt Code:
    1. "cannot commit - no transaction is active"
    To copy to clipboard, switch view to plain text mode 

    lg Chris


    Added after 7 minutes:


    Quote Originally Posted by ckvsoft View Post
    Hallo
    Thx, but i try some thinks also with a semicolon ";" before END. But nothing works.
    But the Error is
    Qt Code:
    1. "cannot commit - no transaction is active"
    To copy to clipboard, switch view to plain text mode 

    lg Chris
    Hallo

    I see when i execute the query as multi-line the error ("cannot commit ...") is the same, but the TRIGGER was created now.

    lg Chris
    Last edited by ckvsoft; 29th March 2020 at 14:13.

  4. #4
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSSqlQuery CREATE TRIGGER not working

    I think it's time to show more code and tell you what database it is.

  5. #5
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Default Re: QSSqlQuery CREATE TRIGGER not working

    Could it be, that Qt is trying to commit transaction (autocommit !?) where there is not an open transaction ? I'm not sure, if every DDL statement causes an implicit transaction ...

    But as Lesiok already said, more details ...

  6. #6
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSSqlQuery CREATE TRIGGER not working

    Qt has nothing to do with it. Autocommit is made at the database level. The problem is with the code we can't see.

  7. #7
    Join Date
    Jan 2006
    Location
    Germany
    Posts
    12
    Thanks
    1
    Platforms
    Unix/X11

    Default Re: QSSqlQuery CREATE TRIGGER not working

    Quote Originally Posted by Lesiok View Post
    /.../ Autocommit is made at the database level.
    Not necessarily. API's like ODBC or JDBC provides methods to manage transactions independent of database engine. I mean, transactions (begin, commit/rollback, savepoints, ...) are managed by client app, not by server. Only client knows, when to begin and when to commit a transaction ...

    But perhaps this has nothing to do with the primary problem

  8. #8
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,536
    Thanked 284 Times in 279 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: QSSqlQuery CREATE TRIGGER not working

    Quote Originally Posted by smyk View Post
    Not necessarily. API's like ODBC or JDBC provides methods to manage transactions independent of database engine. I mean, transactions (begin, commit/rollback, savepoints, ...) are managed by client app, not by server. Only client knows, when to begin and when to commit a transaction ...

    But perhaps this has nothing to do with the primary problem
    If you manage the transaction via any API (calling begin / end transaction) then there is no autocommit. Probably the problem is that the author of the thread does not explicitly start the transaction and tries to complete it by calling the appropriate API function.
    We won't think of anything without seeing a larger piece of code. You may also need to look into the database server logs.

Similar Threads

  1. Replies: 0
    Last Post: 13th February 2016, 08:51
  2. Replies: 3
    Last Post: 2nd March 2015, 21:11
  3. Replies: 3
    Last Post: 27th December 2013, 15:43
  4. how to send sqlite query CREATE TRIGGER in qt application
    By abdul_moiz in forum Qt Programming
    Replies: 2
    Last Post: 10th June 2011, 07:07
  5. QComboBox as a trigger
    By ape in forum Newbie
    Replies: 8
    Last Post: 4th February 2008, 09:57

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.