B4J Question SQLite Transaction

ThRuST

Well-Known Member
Licensed User
Longtime User
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.

B4X:
' 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
 

ThRuST

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
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).
 
Upvote 0

KMatle

Expert
Licensed User
Longtime User
Upvote 0
Cookies are required to use this site. You must accept them to continue using the site. Learn more…