Using Transactions with rs.Edit

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

Yes.

[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.

Matthew,

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>