QSqlDatabase SQLite transaction inside of QSqlQuery. How?

bogong

Hello all!
Got troubles with executing transactional SQL query in QSqlDatabase with in SQLite.
The SQL query pretty simple:

INSERT INTO log (value1,value2,value3) VALUES (1,2,3);

When I am using it in plain mode all is OK. When I am using it in transaction:

BEGIN TRANSACTION;
INSERT INTO log (value1,value2,value3) VALUES (1,2,3);
COMMIT;

It's getting failed on preparing it QSqlDatabase::prepare();
Why it's happening? What am I missing?
Is there way to use transaction from SQL Query string but not QSqlDatabase::transaction()?

KroMignon

@bogong Hi using transaction with SQLite is easy:

auto db = QSqlDatabase::database("my connection name");

if(db.transaction())
{
    QSqlQuery query(db);
    // do stuff

   if(!db.commit())
   {
        qDebug() << "Failed to commit";
        db.rollback();
   }
}
else
{
    qDebug() << "Failed to start transaction mode";
}

bogong

@KroMignon Thx for rapid reply. I know it perfectly. The question is about how to use SQLite native transactional mechanism, there is where I've got troubles.

SGaist

Hi,

begin/endTransaction are using SQLite's native mechanism.

If you want to do it by hand, then you have to make two additional queries, one for BEGIN before your other query and then one for COMMIT.

KroMignon

@bogong But this does it... calling QSqlDatabase::transaction() will insert the SQL sentence "BEGIN TRANSACTION;" and QSqlDatabase::commit() will insert the SQL sentence "COMMIT;".

If you want to use a QSqlQuery with transaction, you should do it like I show it to you in my small code extract.
What is the problem with using QSqlDatabase::transaction()/QSqlDatabase::commit()?

bogong

@KroMignon The question is about how to use SQLite native transactional mechanism. I need to be able to execute any SQL String.

KroMignon

@bogong But you can execute any SQL string you want, this will do exactly what you show as example:

auto db = QSqlDatabase::database("my connection name");

if(db.transaction())
{
    QSqlQuery query(db);
    // do stuff
    query.execute("INSERT INTO log (value1,value2,value3) VALUES (1,2,3);");

   if(!db.commit())
   {
        qDebug() << "Failed to commit";
        db.rollback();
   }
}
else
{
    qDebug() <<  "Failed to start transaction mode";
}

SGaist

@bogong said in QSqlDatabase SQLite transaction inside of QSqlQuery. How?:

@KroMignon The question is about how to use SQLite native transactional mechanism. I need to be able to execute any SQL String.

@bogong that's what both @KroMignon and I are trying to make you understand. Under the hood, the SQLite backend uses native SQLite to start and end the transaction.

bogong

@KroMignon Again - the question is about how to use SQLite native transactional mechanism. It's mean string should be

BEGIN TRANSACTION;
INSERT INTO log (value1,value2,value3) VALUES (1,2,3);
COMMIT;

And it's not working.

SGaist

@bogong These are three different requests in one string.

bogong

@SGaist The canonical SQL approach is ONE string that might be containing HUGE amount of requests. And I am trying to execute ONE string that might be containing any amount of requests. And It's not working in QT. When I am using it in directly in SQLite all is ok.

KroMignon

@bogong said in QSqlDatabase SQLite transaction inside of QSqlQuery. How?:

the question is about how to use SQLite native transactional mechanism.

I don't know how to explain it in another way:
You cannot do more than 1 SQL sentence in a QSqlQuery, so if you want to do transaction with SQLite DB and Qt, you have to use QSqlDatabase::transaction()/QSqlDatabase::commit() like I show you in my short example.

Of course you can insert as many QSqlQuery between QSqlDatabase::transaction()/QSqlDatabase::commit().

JonB

@bogong
Both @KroMignon & @SGaist are right.

Your only chance of single-line-multi-statement with SQLite will be to drop your parameter binding. Try:

BEGIN TRANSACTION; INSERT INTO log (value1,value2,value3) VALUES (1,2,3); COMMIT;

Oh, I think you are saying you have tried that (without binding parameters) and it still does not work? I think you have to execute sqlite3_exec() to get multi-statement, if Qt isn't going through that with no parameter binding then it looks like it's tricky....

bogong

@KroMignon Again - one string mean BEGIN and COMMIT is in string too. And it's not working in QT.

KroMignon

@bogong said in QSqlDatabase SQLite transaction inside of QSqlQuery. How?:

Again - one string mean BEGIN and COMMIT is in string too. And it's not working in QT.

no this can not work because the are 3 SQL sentences:

BEGIN TRANSACTION;
INSERT ...
COMMIT;

and you can only have 1 SQL sentence in a QSqlQuery::execute().

Is this clear enough to you?

JonB

@bogong
I have answered your question above, up to you whether you take heed or not (though it's always nice not to be ignored).

If you wish to pursue from Qt, take a look at https://code.woboq.org/qt5/qtbase/src/3rdparty/sqlite/sqlite3.c.html. If you search for sqlite3_exec() you'll come across

** CAPI3REF: One-Step Query Execution Interface
** METHOD: sqlite3
**
** The sqlite3_exec() interface is a convenience wrapper around
** [sqlite3_prepare_v2()], [sqlite3_step()], and [sqlite3_finalize()],
** that allows an application to run multiple statements of SQL
** without having to use a lot of C code.
...
*/
SQLITE_API int sqlite3_exec(
  sqlite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

bogong

@KroMignon This clear for me since 2014. And I've been writting it in second message that replied to you. I've been talking about all in ONE string like in SQL Specification. ONE STRING mean - it should be executed if there canonical syntax. BEGIN and COMMIT - canonical syntax and it's not working in QT. And again - I DO NOT asking about how to use Qt Transaction mechanism, I know it perfectly. I am asking how to use SQLite native mechanism in QT when I can only execute string. This two points have huge difference.

bogong

@JonB Thx. Will try to do it. But been trying to avoid plan C. looks like will not.

JonB

@bogong
It is only my understanding. But I believe you will have to go via something like that sqlite3 code interface in order to execute multi-statements at a time, which probably corresponds to your "When I am using it in directly in SQLite all is ok." finding. I think that from Qt, QSqlQuery is only going to execute a single statement for SQLite.

KroMignon

@bogong said in QSqlDatabase SQLite transaction inside of QSqlQuery. How?:

I am asking how to use SQLite native mechanism in QT when I can only execute string. This two points have huge difference.

Nope, there is no difference: using QSqlDatabase::transaction() / commit() is the Qt "sugar" to use SQLite "native mechanism" (or other Database type like mySQL for example).

There are 2 ways to do it:

use Qt way (QSqlDatabase + QSqlQuery)
do it your own way, for example by calling "sqlite" executable from command line.

Try to mixup booth is a very bad idea.
A basic rule I always try to follow is: when I try to work against the framework, then there something I am doing wrong or misusing.

Just for my personal curiosity, why do you not want to use QSqlDatabase::transation()/commit()?