SQL stored procedure can be called only 1 time !?

[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.