Android Question Re: Multi table Insert / update [Sqlite]

sivakrith

Member
Licensed User
Longtime User
Hello,
Using B4A 10.5 with sqlite

Want to update multiple tables with database transactions, ie either update all or update none and return error message(s)
B4X:
' Declares variable for SQLite connection
Private SQLiteCon As SQL

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

Try
    SQLiteCon.BeginTransaction

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

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

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

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

    ' 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")
    SQLiteCon.TransactionSuccessful
Catch
    Log(Lastexception)
    SQLiteCon.Rollback
End Try

Is this the correct way to go ? Want to confirm whether TransactionSuccessful is equals to commit.

Happiness Always
BKR Sivaprakash
 
Cookies are required to use this site. You must accept them to continue using the site. Learn more…