Update queries for SQLite don't work with transactions?

[isr]

I have an app that involves many database operations, including select, update, and insert queries. I have followed Erel's recommendation to use BeginTransaction and EndTransaction for all "write" queries to make them run faster.

However, when I use transactions for update queries, the updates do not execute.

Here is some sample test code:

Sub Activity_Create(FirstTime As Boolean)

SQL.Initialize(File.DirDefaultExternal, "test.db", True)

SQL.ExecNonQuery("DROP TABLE IF EXISTS test")
SQL.ExecNonQuery("CREATE TABLE IF NOT EXISTS test(id INTEGER, number INTEGER)")   

Dim id As Int
Dim number As Int
number = 4444

'inserting values
For i=1 To 10
SQL.ExecNonQuery2("INSERT INTO test VALUES(?,?,?)", Array As Object(i, number))
Next

'updating a subset of values
SQL.BeginTransaction
number =55555
For i=1 To 10
If i>=6 Then
   SQL.ExecNonQuery2("UPDATE test SET number = ? WHERE id =?", Array As Object(number,i))
End If
Next 
SQL.EndTransaction

'logging values from updated table
readqry = SQL.ExecQuery("SELECT id, number FROM test")
For i=1 To readqry.RowCount
   readqry.Position = i-1
   Log("id and number " & readqry.GetInt("id") & " " & readqry.GetString("number"))
Next

End Sub

The log output is:
id and number 1 4444
id and number 2 4444
id and number 3 4444
id and number 4 4444
id and number 5 4444
id and number 6 4444
id and number 7 4444
id and number 8 4444
id and number 9 4444
id and number 10 4444

This means that rows 6-10 were not updated to the new value of "55555".

I've tried similar code using the UpdateRecord function from DBUtils, and get the same problem (no update).

The transactions are already included in the DBUtils function InsertMaps, but not in the UpdateRecord function. Does this mean that transactions cannot be used with update queries?

Are update queries not considered "write" queries by SQLite? Or am I doing something wrong?

If the update queries cannot be made to go faster, then it makes an app almost unusable if it involves more than 5 or 10 update queries at one time (in my app, a service with database operations ran more than 500 update queries with the UpdateRecord function from DBUtils and it took more than 2 minutes, freezing the activity and user interface until it was done).

Thank you for any advice or answers!

 

[klaus]

You need to add : SQL.TransactionSuccessful

SQL.BeginTransaction
number =55555
For i=1 To 10
If i>=6 Then
    SQL.ExecNonQuery2("UPDATE test SET number = ? WHERE id =?", Array As Object(number,i))
End If
Next 
SQL.TransactionSuccessful
SQL.EndTransaction

Best regards.

 

[Erel]

See the example code here: Basic4android - SQL

 

[isr]

Thank you, Klaus and Erel. Adding "TransactionSuccessful" makes everything execute. I had been including it before but then thought, mistakenly, it wasn't needed, so started excluding it.

Despite the successful transaction, however, it still takes a long time for the update queries to execute. From my further searching of the forum, it seems like I need to put the database operations service into a thread, and I'll post a question on that separately.

Thanks again!