I think the correct way to do it is with transactions :
B4X:
sq.BeginTransaction
Try
sq.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, 0);", Array("Sunday", "Monday"))
sq.TransactionSuccessful
Catch
If LastException.IsInitialized Then
Log(LastException.Message)
End If
End Try
and if you use connectionPool:
B4X:
Dim sq As SQL = Pool.GetConnection
sq.BeginTransaction
Try
sq.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, 0);", Array("Sunday", "Monday"))
sq.TransactionSuccessful
Catch
If LastException.IsInitialized Then
Log(LastException.Message)
End If
End Try
sq.Close
I am not sure about the second code fragment, but in the first it looks to me that EndTransaction is missing and therefore your changes are not committed to the database.
Correct me if I am wrong.
I would too include the beginTransaction in the Try block.
no, this is the way you end a transaction, there is no endTransaction.
The second method, with the connectionPool, is in my opinion the best when multiple persons connect directly to a database.
Edit:
you could write example 1 like that with a for loop for example :
B4X:
sq.BeginTransaction
Try
for i = 1 to 10
sq.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, 0);", Array(i, "Monday"))
next
sq.TransactionSuccessful
Catch
If LastException.IsInitialized Then
Log(LastException.Message)
End If
End Try
There definitely is an EndTransaction method as described in the SQL doc. and I use it all the time in my own code. What I had not realised is that TransactionSuccessful committed the changes? If you try to begin a transaction while another one is open, it 'll generate an exception.