I execute the following instructions in T-SQL and correctly I get the expected result only when I commit
BEGIN TRANSACTION
GO
UPDATE TABLE1 SET FIELD1 = 'Y'
GO
COMMIT
GO
in Xojo, however, it seems that the BEGIN TRANSACTION is ignored, because an automatic commit is executed and the final rollback has no effect
db.SQLExecute( "BEGIN TRANSACTION" )
If db.Error Then
Return
End If
db.SQLExecute( "UPDATE TABLE1 SET FIELD1 = 'Y' " )
If db.Error Then
Return
End If
db.Rollback
The rollback is at the wrong place and you didn’t end the transaction. Try this:
db.SQLExecute( "BEGIN TRANSACTION" )
If db.Error Then
Return
End If
db.SQLExecute( "UPDATE TABLE1 SET FIELD1 = 'Y' " )
If db.Error Then
db.Rollback
else
db.commit
End If
db.SQLExecute("END TRANSACTION")
I’ve never had an automatic commit. I’ve get always the rollback or the commit doing the transaction like the sample.
Interesting that the DB object is smart enough to have commit and rollback methods but not smart enough to have begin and end transaction without using Dbexecute.
Does this method still work with prepared statements?
If you can concatenate the sql (being mindful of the security issues) then you can just craft the whole transaction into a string using the semicolon to separate the statements and do one dB.execute ?
FWIW after trying many different things I gave up on the XOJO database stuff (for non trivial tasks) for MS SQL and used MBS. Once I did that, everything got way better.