Can I use the SQLExecute('BEGIN TRANSACTION") statement when using rs.Edit and rs.Update, instead of using SQL statements? Or is that only used when using SQL code.
For instance, can I do this after getting a successful SQLite query for RecordSet rs:
If rs <> Nil
SQLExecute('BEGIN TRANSACTION")
While Not rs.EOF
rs.Edit
rs.Field("SomeColumn").IntegerValue = [some value based on a calculation]
rs.Update
If db.Error Then
MsgBox db.ErrorMessage
db.RollBack
Return
End If
rs.MoveNext
Wend
End If
db.Commit
[quote=353736:@Ralph Alvy]Can I use the SQLExecute('BEGIN TRANSACTION") statement when using rs.Edit and rs.Update, instead of using SQL statements? Or is that only used when using SQL code.
For instance, can I do this after getting a successful SQLite query for RecordSet rs:
If rs <> Nil
SQLExecute('BEGIN TRANSACTION")
While Not rs.EOF
rs.Edit
rs.Field("SomeColumn").IntegerValue = [some value based on a calculation]
rs.Update
If db.Error Then
MsgBox db.ErrorMessage
db.RollBack
Return
End If
rs.MoveNext
Wend
End If
db.Commit[/quote]
SQLExecute('BEGIN TRANSACTION") is not needed here. The RecordSet instance methods (Edit, Field, Update, RollBack) all handle the 'unseen SQL queries not being seen.
Manually, one would have to do something in the order of:
db.SQLExecute('BEGIN TRANSACTION")
db.SQLExecute("UPDATE table_nameSET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];")
if db.Error then
//Handle Errors
else
db.Comit
//etc.
You can, technically use it if you wish, but it’s not needed here if a transaction has already been started elsewhere. Comit closes the transaction, so if you re-wrote to the database, the transaction should be reopened ‘for integrity of the database’. It depends how you’re using it.
I’m trying to do a series of updates of the entire RecordSet and have them all Rollback if any of them fail. Thus the Transaction. Does that change your reply?
Not sure about other Database Plugins but for PostgreSQLDatabase each Recordset.Update command issues an implicit BEGIN TRANSACTION before the UPDATE. You have to manually Commit or Rollback afterwards. See <https://xojo.com/issue/35835>