SQL stored procedure can be called only 1 time !?

[Ahmed Abdellatif]

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

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An exception occurred';
END;
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
ROLLBACK;
SELECT 'Unique Record Duplication occured, all saved records will be deleted from all other tables';
END;
START TRANSACTION;
INSERT INTO `My_schema`.`cus`(`Health insurance number`,`discount percentage`,`max discount`)VALUES('88520000000000','.9','.99'); 
INSERT INTO `My_schema`.`per`(`sex`,`Socialstatus`,`firstdate`,`cus_id_cus`)values( 'male', 'single', '2018-01-01 00:00:00', LAST_INSERT_ID());
commit;
END;
$$
DELIMITER ;
call mprocedure();

DROP PROCEDURE IF EXISTS mprocedure;
DELIMITER $$
CREATE PROCEDURE mprocedure()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An exception occurred';
END;
DECLARE EXIT HANDLER FOR SQLSTATE '23000'
BEGIN
ROLLBACK;
SELECT 'Unique Record Duplication occured, all saved records will be deleted from all other tables';
END;
START TRANSACTION;
INSERT INTO `My_schema`.`cus`(`Health insurance number`,`discount percentage`,`max discount`)VALUES('88520000000000','.9','.99'); 
INSERT INTO `My_schema`.`per`(`sex`,`Socialstatus`,`firstdate`,`cus_id_cus`)values( 'male', 'single', '2018-01-01 00:00:00', LAST_INSERT_ID());
commit;
END;
$$
DELIMITER ;
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

"Unique Record Duplication occurred, all saved records will be deleted from all other tables"

"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

call mprocedure();

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

[ChrisW67]

This is the same issue as your earlier post in the other forum. QSqlQuery::exec() handles single statements.

[Lesiok]

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:

QString skrypt(
        "ALTER TABLE rodzaje_biletow"
        "   ADD COLUMN id_rodzb serial NOT NULL;"
        "ALTER TABLE rodzaje_biletow DROP CONSTRAINT rodzaje_biletow_pkey;"
        "ALTER TABLE rodzaje_biletow"
        "  ADD CONSTRAINT rodzaje_biletow_pkey PRIMARY KEY(id_rodzb);"
        "ALTER TABLE bilety ADD COLUMN id_rodzb integer;"
        "UPDATE bilety "
        "    SET id_rodzb = (SELECT id_rodzb FROM rodzaje_biletow "
        "                        WHERE bilety.kod_rb=rodzaje_biletow.kod_rb "
        "                              AND bilety.id_rozkladu=rodzaje_biletow.id_rozkladu);"
        "DROP VIEW bilety_dobre;"
        "ALTER TABLE bilety DROP COLUMN kod_rb;"
        "CREATE OR REPLACE VIEW bilety_dobre AS "
        " SELECT bilety.*"
        "   FROM bilety WHERE bilety.dobry = 1::numeric;"
        "ALTER TABLE ulgi_kursow ADD COLUMN id_rodzb integer;"
        "UPDATE ulgi_kursow "
        "    SET id_rodzb = (SELECT id_rodzb FROM rodzaje_biletow "
        "                        WHERE ulgi_kursow.kod_rb=rodzaje_biletow.kod_rb "
        "                              AND ulgi_kursow.id_rozkladu=rodzaje_biletow.id_rozkladu);"
        "ALTER TABLE ulgi_kursow DROP COLUMN kod_rb;"
        );
    if( !query.exec(skrypt) )
    {
        ......
    }

QString skrypt(
        "ALTER TABLE rodzaje_biletow"
        "   ADD COLUMN id_rodzb serial NOT NULL;"
        "ALTER TABLE rodzaje_biletow DROP CONSTRAINT rodzaje_biletow_pkey;"
        "ALTER TABLE rodzaje_biletow"
        "  ADD CONSTRAINT rodzaje_biletow_pkey PRIMARY KEY(id_rodzb);"
        "ALTER TABLE bilety ADD COLUMN id_rodzb integer;"
        "UPDATE bilety "
        "    SET id_rodzb = (SELECT id_rodzb FROM rodzaje_biletow "
        "                        WHERE bilety.kod_rb=rodzaje_biletow.kod_rb "
        "                              AND bilety.id_rozkladu=rodzaje_biletow.id_rozkladu);"
        "DROP VIEW bilety_dobre;"
        "ALTER TABLE bilety DROP COLUMN kod_rb;"
        "CREATE OR REPLACE VIEW bilety_dobre AS "
        " SELECT bilety.*"
        "   FROM bilety WHERE bilety.dobry = 1::numeric;"
        "ALTER TABLE ulgi_kursow ADD COLUMN id_rodzb integer;"
        "UPDATE ulgi_kursow "
        "    SET id_rodzb = (SELECT id_rodzb FROM rodzaje_biletow "
        "                        WHERE ulgi_kursow.kod_rb=rodzaje_biletow.kod_rb "
        "                              AND ulgi_kursow.id_rozkladu=rodzaje_biletow.id_rozkladu);"
        "ALTER TABLE ulgi_kursow DROP COLUMN kod_rb;"
        );
    if( !query.exec(skrypt) )
    {
        ......
    }

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.

[kbsk007]

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.

[Ahmed Abdellatif]

you can recommend one

[kbsk007]

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())");

[ChrisW67]

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:

QString skrypt(
        "CREATE TABLE xyzzy (a int); "
        "INSERT INTO xyzzy (a) VALUES(1); "
        "INSERT INTO xyzzy (a) VALUES(2); "
        "INSERT INTO xyzzy (a) VALUES(3); "
        "COMMIT; "
        );

QString skrypt(
        "CREATE TABLE xyzzy (a int); "
        "INSERT INTO xyzzy (a) VALUES(1); "
        "INSERT INTO xyzzy (a) VALUES(2); "
        "INSERT INTO xyzzy (a) VALUES(3); "
        "COMMIT; "
        );

To copy to clipboard, switch view to plain text mode 

created a table of three records, and

QString skrypt(
        "CREATE TABLE xyzzy (a int); "
        "DELIMITER $$ "
        "INSERT INTO xyzzy (a) VALUES(1); "
        "INSERT INTO xyzzy (a) VALUES(2); "
        "INSERT INTO xyzzy (a) VALUES(3); "
        "COMMIT; "
        );

QString skrypt(
        "CREATE TABLE xyzzy (a int); "
        "DELIMITER $$ "
        "INSERT INTO xyzzy (a) VALUES(1); "
        "INSERT INTO xyzzy (a) VALUES(2); "
        "INSERT INTO xyzzy (a) VALUES(3); "
        "COMMIT; "
        );

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.