[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

[Nicolas Jäger]

Hi Simon and Stephan,
Thx for your answers, I was very busy the last week and I worked
on my program only saturday and yesterday. That's why I answering only
today, but I wanted to thank you.

I had some difficulties to use `sqlite_exec()` because I'm wrapping to
C++ all Sqlite3 functions I need in the way to be able to use some C++
features...

you can found what was the problem and how I solved it on SO :

http://stackoverflow.com/questions/32415873/wrapping-a-c-function-with-a-static-callback-parameter-to-a-c-function-wich-ac


regards,
Nicolas J.

[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

[Stephan Beal]

On Mon, Aug 31, 2015 at 4:50 AM, Nicolas J?ger 
wrote:

> I'm starting to use transaction in my C++ code to delete some entries
> (tags) in my db. during execution, the first transaction (each
> transaction is contained in one string), like :
>
> BEGIN TRANSACTION;
> DELETE FROM TAGS WHERE NAME = 'loki';
> DELETE FROM TAGSMAP WHERE COLLECTION_ID = '3' AND TAG_id = '54';
> COMMIT;
>
> is executed by running `sqlite3_prepare_v2()` and the `sqlite3_stmt`
> returns `SQLITE_DONE`. So I consider the transaction made and closed.
> but if I looked in the db I still see the row/entry for 'loki'
>

Nope - you have only run the BEGIN part of the transaction. prepare()
prepares only one single statement, not multiples (you have 4 statements in
your SQL). Thus when you try to run another transaction, that BEGIN is
still open.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

[Simon Slavin]

On 31 Aug 2015, at 2:23pm, Stephan Beal  wrote:

> Nope - you have only run the BEGIN part of the transaction. prepare()
> prepares only one single statement, not multiples (you have 4 statements in
> your SQL). Thus when you try to run another transaction, that BEGIN is
> still open.

In contrast sqlite_exec() can process multiple statements, the way you were 
trying to use _prepare(),_step(),_finalize().



"run multiple statements of SQL without having to use a lot of C code."

Simon.

[sqlite] I don't understand why I get "cannot start a transaction within a transaction"

[Nicolas Jäger]

Hi,
I'm starting to use transaction in my C++ code to delete some entries
(tags) in my db. during execution, the first transaction (each
transaction is contained in one string), like :

BEGIN TRANSACTION;
DELETE FROM TAGS WHERE NAME = 'loki';
DELETE FROM TAGSMAP WHERE COLLECTION_ID = '3' AND TAG_id = '54';
COMMIT;

is executed by running `sqlite3_prepare_v2()` and the `sqlite3_stmt`
returns `SQLITE_DONE`. So I consider the transaction made and closed.
but if I looked in the db I still see the row/entry for 'loki'

If I try (during the same process) to delete another tag, or if I try to
delete the same tag again, the `sqlite3_stmt` returns an error, 

cannot start a transaction within a transaction


I have the feeling I miss a command at some point. but I didn't found
which one...

(I can upload the code for further investigation if required.)

regards,
Nicolas J.