B4J Question SQLite Transaction

[ThRuST]

I want you guys to analyze and comment this SQLite block of code

Is it the proper way to use SQLite with B4J? What about .TransactionSuccessful vs Commit?

The idea is to have these pasted as complete working and commented code blocks in my project Athena.

' Declares variable for SQLite connection
Private SQLiteCon As SQL

' Connect to SQLite database
SQLiteCon.InitializeSQLite( PathToDatabase , "myDatabase.sqlite", False)

' Inserts data into SQLite database
SQLiteCon.ExecNonQuery2("INSERT INTO myTable VALUES(?, ?)", Array As Object(Null, myString)) ' id, data

' Inserts multiple items into SQLite database
SQLiteCon.BeginTransaction
SQLiteCon.ExecNonQuery2("INSERT INTO myTable VALUES(?, ?)", Array As Object(Null, myString1)) ' id, data
SQLiteCon.ExecNonQuery2("INSERT INTO myTable VALUES(?, ?)", Array As Object(Null, myString2)) ' id, data
SQLiteCon.TransactionSuccessful

' Update data in SQLite database
SQLiteCon.ExecNonQuery2("UPDATE myTable SET myRecord=? WHERE id=?",Array As Object(myString, 1))

' Update multiple items in SQLite database
SQLiteCon.BeginTransaction
SQLiteCon.ExecNonQuery2("UPDATE myTable SET myRecord=? WHERE id=?",Array As Object(myString1, 1))
SQLiteCon.ExecNonQuery2("UPDATE myTable SET myRecord=? WHERE id=?",Array As Object(myString2, 2))
SQLiteCon.TransactionSuccessful

' Load from SQLite database
Dim myVariable As String
Dim RS1 As ResultSet = SQLiteCon.ExecQuery("SELECT myRecord FROM myTable WHERE id=1")
myVariable = RS1.GetString("myRecord")
RS1.Close

' Delete data from SQLite database
SQLiteCon.ExecNonQuery("DELETE FROM myTable WHERE id = 1")

Please share your comments and suggestions on this. Thanks

 

[Erel]

1. No need to write Array As Object. Array is enough.

2. Why not create a single transaction here?

Overall it looks correct.

 

[ThRuST]

Thanks I will make the neccessary corrections.

I'm not sure I understand this

2. Why not create a single transaction here?

 

[ThRuST]

I assume you meant why I didn't use Transaction for all of them?

Well someone adviced me to only use transaction for multiple calls and not needed for single ones, so that's why I left it out.

 

[Erel]

Well someone adviced me to only use transaction for multiple calls and not needed for single ones, so that's why I left it out.

If you only need to make a single change then you can let the SQL engine start and close the transaction for you. In this case there is no reason not to include all of them in a single transaction (though your code is also fine).

 

[KMatle]

Transactions are good for multiple changes (speed) and if you have a logical unit of work. Example: 4 tables are updated due to one event (e.g. a customer buys an item and pays for it) have to be consistent.