Begin Transaction / Commit Transaction

[ajmcgee]

Has anyone had any success using SQLite Begin Transaction / Commit Transaction commands? What format should I use?


Alan

[kokenge]

It's a essential part of all database engines.
Very useful if you have a long series of transactions. For example a purchase order can be spread over a series of transactions such as entering the ship to / sold to, several item details, payment methods, and other information. You can use a Begin transaction to begin the work and for some reason, if the order is aborted, you can back out all the transactions with the Rollback Transaction, or complete it with the Commit Transaction

Format:
#handle execute("BEGIN TRANSACTION")
#handle execute("ROLLBACK TRANSACTION")
#handle execute("COMMIT TRANSACTION")

[ajmcgee]

Thanks for the info. Does RB recognize SQLite error messages so I can automatically roll back a transaction?

Should I use disparate connect and disconnect statements as below?

call connect
#handle execute("BEGIN TRANSACTION")
call disconnect
call connect
#handle execute("Insert / Update Statement 1")
call disconnectcall connect
#handle execute("Insert / Update Statement 2")
call disconnect
call connect
#handle execute("COMMIT TRANSACTION"
call disconnect



Thanks,

Alan

[kokenge]

Alan.
Here is a quick test.
The DB has 3 fields..
The first 2 inserts work.
The 3rd insert fails because it tries to insert 4 fields into the 3 field table.
The on error causes it to go the [rollback]
It gets the number of rows inserted, and correctly reports 2.
It executes the ROLLBACK
Now when it gets the rows inserted it is 0. That's because the BEGIN TRANSACTION surrounded all the inserts..
Hope this helps..

sqliteconnect #mem, ":memory:"
mem$ = "CREATE TABLE tempTbl (aa text,bb text,cc text);"
#mem execute(mem$)

#mem execute("BEGIN TRANSACTION")
on error goto [rollback]
mem$ = "INSERT INTO tempTbl VALUES('a1','b1','c1')"
#mem execute(mem$)
print "Insert ok:";mem$                              ' first insert works
mem$ = "INSERT INTO tempTbl VALUES('a2','b2','c2')"
#mem execute(mem$)                                   ' second insert works
print "Insert ok:";mem$
mem$ = "INSERT INTO tempTbl VALUES('a4','b3','c3','d3')"
#mem execute(mem$)                                   ' this fails and trips the on error
print "Insert ok:";mem$

#mem execute("COMMIT TRANSACTION")
print "commited"
wait

[rollback]
#mem execute("SELECT * FROM tempTbl")
rows = #mem ROWCOUNT()
print "Rolls inserted:";rows
#mem execute("ROLLBACK TRANSACTION")
print "Roll back"
#mem execute("SELECT * FROM tempTbl")
rows = #mem ROWCOUNT()
print "Rolls inserted:";rows
wait


[jerry]

What a wonderful example.

Than you from 8 years ago!