MS SQL SERVER Transactions

Databases
1

Hi all,

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

I’ve found only this conversation: https://forum.xojo.com/25507-ms-sql-server-and-begin-transaction/0

can someone help me?
thank you.

2

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.

3

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.