Vladimir, When you execute individual statements and sqlite3_step or sqlite3_exec returns an error code you should execute a 'ROLLBACK' in stead of a 'COMMIT'. So the logic is:
exec "BEGIN" perform a bunch of statements if(all statements successful) exec "COMMIT" else exec "ROLLBACK" Ideally you'd quit executing statements as soon as one fails. BTW: the 'END' statement is not needed. Gé Vladimir Zelinski wrote: > Of course, I read documentation and saw that > transaction is a supported feature. But I can't get to > work it. In order to make sure that we talk about the > same thing I understand transaction as atomic > operation. One or more data modification language > statements can be placed within transaction. Only two > legal results for transaction might have take place: > 1) all statements executed successfully OR 2) none of > them successful. It can't be situation when some > statements are successful and some failed within the > same transaction. > > I downloaded sqlite3 for Windows. My program > dynamically builds a file with sql statements like > this bellow: > > > BEGIN TRANSACTION; > > -- This SQL statement fails because it's trying > -- to insert a duplicate key. > INSERT INTO t(id) VALUES(10); > > -- This SQL statement always successful despite on > fact > -- that first SQL statement failed > UPDATE t SET end_date=20 WHERE id=9; > > END TRANSACTION; > COMMIT TRANSACTION; > > > Update statement always successful, even when INSERT > fails. So situation is such that some DML statements > are successful and some failed within the same > transaction. I could not belive that. I tried dozens > of times using syntax UPDATE OR ROLLBACK SET ..., but > I had no luck > I expect that UPDATE will never change a data on the > table if INSERT fails. > > Please, explain me what am I doing wrong? I'm a > database person, but I could not figure out what to do > in such situation. > I need to get to work number of SQL statements in > transactional mode: all or nothing succeeds. > > Is any way to check result of execution SQL statement? > Is it possible to select a system variable that would > give me status of the last SQL. > > Thank you very much, > Vladimir
