Android Question SQLite vs. B4A SQL Object

[Claudio Oliveira]

Hi everybody,

SQLite implements the SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION", "ROLLBACK TRANSACTION", "END TRANSACTION", "SAVEPOINT", etc...
Are there any recommendations on wich is the best way to manage database transacions?
I mean, which is better: using B4A SQL object methods, or native SQL commands implemented by SQLite itself?
Sometimes it gets a bit confusing to me, as SQL object has no "rollback" method, which leads me to use an SQLite command to rollback a transaction.
Same thing when one needs to create nested transactions, which is quite common on database application development, and as far as I know is supported by SQLite "SAVEPOINT" command, but not by B4A SQL Object...

Thanks and regards!
Claudio

 

[Erel]

B4A SQL object is based on the native API. Eventually the same SQL commands are executed.

The recommended code is:

SQL1.BeginTransaction
Try
    'block of statements like:
    For i = 1 to 1000
        SQL1.ExecNonQuery("INSERT INTO table1 VALUES(...)
    Next
    SQL1.TransactionSuccessful
Catch
    Log(LastException.Message) 'no changes will be made
End Try
SQL1.EndTransaction

If you call EndTransaction without calling TransactionSuccessful then the transaction will be rollbacked.

 

[Claudio Oliveira]

Great!

Thanks Erel!!!

 

[Mahares]

@Erel: Where is the SQL1.BeginTransaction. Did you forget it in your code or did you leave it out by design in post #2?

 

[Erel]

Fixed. I copied it from here: http://www.b4x.com/android/help/sql.html#sql_begintransaction and missed the first line...