Results 1 to 7 of 7

Thread: SQL stored procedure can be called only 1 time !?

  1. #1
    Join Date
    Sep 2017
    Posts
    23
    Thanks
    8
    Qt products
    Qt5
    Platforms
    Unix/X11 Windows

    Default SQL stored procedure can be called only 1 time !?

    I have the following stored procedure which is executed correctly (in the first time) from MySQL workbench and from my QT application:

    Qt Code:
    1. DROP PROCEDURE IF EXISTS mprocedure;
    2. DELIMITER $$
    3. CREATE PROCEDURE mprocedure()
    4. BEGIN
    5. DECLARE EXIT HANDLER FOR SQLEXCEPTION
    6. BEGIN
    7. ROLLBACK;
    8. SELECT 'An exception occurred';
    9. END;
    10. DECLARE EXIT HANDLER FOR SQLSTATE '23000'
    11. BEGIN
    12. ROLLBACK;
    13. SELECT 'Unique Record Duplication occured, all saved records will be deleted from all other tables';
    14. END;
    15. START TRANSACTION;
    16. INSERT INTO `My_schema`.`cus`(`Health insurance number`,`discount percentage`,`max discount`)VALUES('88520000000000','.9','.99');
    17. INSERT INTO `My_schema`.`per`(`sex`,`Socialstatus`,`firstdate`,`cus_id_cus`)values( 'male', 'single', '2018-01-01 00:00:00', LAST_INSERT_ID());
    18. commit;
    19. END;
    20. $$
    21. DELIMITER ;
    22. call mprocedure();
    To copy to clipboard, switch view to plain text mode 
    but when i tried to execute the previous again, i had 3 cases:
    1. If the call was from MySQL workbench, it gave me the error that is supposed to be received
    Qt Code:
    1. "Unique Record Duplication occurred, all saved records will be deleted from all other tables"
    To copy to clipboard, switch view to plain text mode 
    2. But if the call was from QT app, it should give me the same error but unfortunately gave me nothing.
    3. If i used
    Qt Code:
    1. call mprocedure();
    To copy to clipboard, switch view to plain text mode 
    instead of the whole previous string, it worked well.
    Any help is appreciated
    Last edited by Ahmed Abdellatif; 4th May 2018 at 16:55.

  2. #2
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: SQL stored procedure can be called only 1 time !?

    This is the same issue as your earlier post in the other forum. QSqlQuery::exec() handles single statements.
    "We can't solve problems by using the same kind of thinking we used when we created them." -- Einstein
    If you are posting code then please use [code] [/code] tags around it - makes addressing the problem easier.

  3. #3
    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: SQL stored procedure can be called only 1 time !?

    Quote Originally Posted by ChrisW67 View Post
    This is the same issue as your earlier post in the other forum. QSqlQuery::exec() handles single statements.
    I do not agree with this statement. We have many such examples in the code:
    Qt Code:
    1. QString skrypt(
    2. "ALTER TABLE rodzaje_biletow"
    3. " ADD COLUMN id_rodzb serial NOT NULL;"
    4. "ALTER TABLE rodzaje_biletow DROP CONSTRAINT rodzaje_biletow_pkey;"
    5. "ALTER TABLE rodzaje_biletow"
    6. " ADD CONSTRAINT rodzaje_biletow_pkey PRIMARY KEY(id_rodzb);"
    7. "ALTER TABLE bilety ADD COLUMN id_rodzb integer;"
    8. "UPDATE bilety "
    9. " SET id_rodzb = (SELECT id_rodzb FROM rodzaje_biletow "
    10. " WHERE bilety.kod_rb=rodzaje_biletow.kod_rb "
    11. " AND bilety.id_rozkladu=rodzaje_biletow.id_rozkladu);"
    12. "DROP VIEW bilety_dobre;"
    13. "ALTER TABLE bilety DROP COLUMN kod_rb;"
    14. "CREATE OR REPLACE VIEW bilety_dobre AS "
    15. " SELECT bilety.*"
    16. " FROM bilety WHERE bilety.dobry = 1::numeric;"
    17. "ALTER TABLE ulgi_kursow ADD COLUMN id_rodzb integer;"
    18. "UPDATE ulgi_kursow "
    19. " SET id_rodzb = (SELECT id_rodzb FROM rodzaje_biletow "
    20. " WHERE ulgi_kursow.kod_rb=rodzaje_biletow.kod_rb "
    21. " AND ulgi_kursow.id_rozkladu=rodzaje_biletow.id_rozkladu);"
    22. "ALTER TABLE ulgi_kursow DROP COLUMN kod_rb;"
    23. );
    24. if( !query.exec(skrypt) )
    25. {
    26. ......
    27. }
    To copy to clipboard, switch view to plain text mode 
    As you can see, there are several SQL commands run by a single QSqlQuery::exec.

  4. #4
    Join Date
    Aug 2015
    Posts
    20
    Thanked 3 Times in 3 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11

    Default Re: SQL stored procedure can be called only 1 time !?

    I think it would be good if you look at a good sql-book.
    Answer following questions:
    What means to you "stored"?
    Where is the procedure stored?
    How many copies of same procedure have to be stored?
    Can you reuse the stored procedure?
    Is it recommended to reuse the same procedure?
    What means to you the Mysql-Error-Message "Unique Record Duplication occured "?

    The problem that you have has nothing to do with Qt but with SQL.

  5. #5
    Join Date
    Sep 2017
    Posts
    23
    Thanks
    8
    Qt products
    Qt5
    Platforms
    Unix/X11 Windows

    Default Re: SQL stored procedure can be called only 1 time !?

    you can recommend one

  6. #6
    Join Date
    Aug 2015
    Posts
    20
    Thanked 3 Times in 3 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11

    Default Re: SQL stored procedure can be called only 1 time !?

    I do not use mysql but postgresql and the syntax is different.
    I do not understand why you need Transaction, for this short commands you can write as follows:

    QSqlQuery query;
    query.exec("INSERT INTO table1 VALUES(2250, 0, .9, .99)");
    query.exec("INSERT INTO table2 VALUES('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID())");

  7. #7
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: SQL stored procedure can be called only 1 time !?

    Quote Originally Posted by Lesiok View Post
    As you can see, there are several SQL commands run by a single QSqlQuery::exec.
    Fair enough. I have just tried something like your script with MySQL in QT 5.9 for a CREATE TABLE, some INSERTS, and COMMIT: it worked. I was basing my response on what I have seen with the Sqlite interface in the past: it seems the behaviour here has changed also or I was triggering a result like the one below.

    The OP's script in this post, and the other, includes directives for the mysql interactive SQL tools. They will not parse as SQL, causing exec() to execute only the steps up to that point. In this case I expect it drops the procedure and terminates without an error.

    In my test:
    Qt Code:
    1. QString skrypt(
    2. "CREATE TABLE xyzzy (a int); "
    3. "INSERT INTO xyzzy (a) VALUES(1); "
    4. "INSERT INTO xyzzy (a) VALUES(2); "
    5. "INSERT INTO xyzzy (a) VALUES(3); "
    6. "COMMIT; "
    7. );
    To copy to clipboard, switch view to plain text mode 
    created a table of three records, and
    Qt Code:
    1. QString skrypt(
    2. "CREATE TABLE xyzzy (a int); "
    3. "DELIMITER $$ "
    4. "INSERT INTO xyzzy (a) VALUES(1); "
    5. "INSERT INTO xyzzy (a) VALUES(2); "
    6. "INSERT INTO xyzzy (a) VALUES(3); "
    7. "COMMIT; "
    8. );
    To copy to clipboard, switch view to plain text mode 
    creates an empty table and reports no error. In the OP's script this would execute only the DROP PROCEDURE.

Similar Threads

  1. Stored procedure in Firebird
    By ozplc in forum Qt Programming
    Replies: 0
    Last Post: 9th June 2011, 22:40
  2. Qt4 Mysql stored procedure
    By lynnH in forum Qt Programming
    Replies: 2
    Last Post: 28th April 2010, 08:05
  3. Stored procedure and ODBC
    By filya in forum Qt Programming
    Replies: 2
    Last Post: 15th March 2010, 08:40
  4. problem in using stored procedure
    By zolfaghari in forum Qt Programming
    Replies: 0
    Last Post: 15th March 2010, 08:25
  5. qt how get out parameter of Stored Procedure?
    By yunpeng880 in forum Qt Programming
    Replies: 1
    Last Post: 23rd March 2009, 12:22

Tags for this Thread

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.