MSSQL Transactions

Databases
1

Yesterday I spent many hours reading the dozens of posts on the subject, written by Bob Keeney, Norman Palardy, Jay Madren, Scott Cadillac and many others, but I have not found the solution (maybe I’m going blind?)

I am writing an application in Xojo 2019r1.1, Windows 10 environment, SQL Server Express database.

I have to perform multiple updates to three tables, and for each table I have a class that does all what is needed.

My idea is to perform a BEGIN TRANSACTION at the beginning, try the various updates and, in case one of these updates fails, perform a ROLLBACK and stop processing.

When all the updates are successful I COMMIT the transaction.

But it happens what reported by Bob: the ROLLBACK is not successful (error “The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.”)

I tried to connect to the database with both the ODBC driver and the Plugin, but the situation is the same.

Anyone know how to fix it?

Thank you all.

2

I’ve made many unsuccessful attempts in the past.
Finally, I decided to use transactions within MSSQL stored procedures.
Since then, I have no longer this problem.
Note: I use the MBS plugin, but I think even the Xojo plugin allows you to run a stored procedure.

3

Did you try to use BEGIN TRANSACTION, ROLLBACK and COMMIT outside a stored procedure, using the MBS Plugin?
Does it work?
If it works, that is my solution.
But if it works only in a stored procedure, this makes me rewrite almost everything (and I don’t want it)

4

I just remember it never well worked.
maybe for my mistakes, maybe not.
besides, the use of stored procedures allows me to avoid complex queries in the Xojo project.
using the RDBMS native features.

5

I gacve up on the MS SQL plugin and use MBS

6

Norman, I have just tried today to use MBS plugin, but it seems to me that the problem is still here.
But perhaps I don’t know much about MBS Plugin, and I am doing something wrong.
How do you deal with Begin Transaction, Rollback and Commit?
Do they work? Could you show a piece of code,?
Thanks a lot.

7

MBS has basically 3 API’s which makes it confusing
I’m afraid I cant share as this is a client project that I cant share things from (patents and such)

8

Ok, thank you, anyway.

9

I should be very glad if someone could explain me why the following code does not work properly

[code]
Function Delete(Anno As Integer, Numero As Integer) As Boolean

// Cancellazione record tabella Offerte_T
Dim SQL As String

//DB.SQLExecute(“SET ANSI_DEFAULTS OFF”)
DB.SQLExecute(“BEGIN TRANSACTION”)

// cancellazione testata offerta
SQL = "DELETE FROM Offerte_T WHERE OFT_ANNO = " + CStr(Anno)
SQL = SQL + " AND OFT_NUMERO = " + CStr(Numero)
DB.SQLExecute(SQL)
If DB.Error = True Then
DB.Rollback
Return False
End If

// cancellazione righe offerta
SQL = "DELETE FROM Offerte_R WHERE OFR_ANNO = " + CStr(Anno)
SQL = SQL + " AND OFR_NUMEROzz = " + CStr(Numero)
DB.SQLExecute(SQL)
If DB.Error = True Then
DB.Rollback
Return False
End If

// Cancellazione avvenuta regolarmente
DB.Commit
Return True

End Function[/code]

The second SQL statement is wrong because the column OFR_NUMEROzz is not present in the table.
But the line DB.Rollback does not work, because “there is no a corresponding BEGIN TRANSACTION”
I tried setting IMPLICIT_TRANSACTIONS OFF before and after the BEGIN TRANSACTION, I tried with SET ANSI_DEFAULTS OFF, but everything I do leads always to the same.

This is driving me mad!!

Please, help me!

10

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Have you switched it off? If not, then your commit does not make sense.
https://techyaz.com/sql-server/t-sql/how-to-disable-auto-commit-in-sql-server/

11

i just try (once again) to SET IMPLICIT_TRANSACTIONS ON before the BEGIN TRANSACTION, but no changes: same error.

12

Following your link, I turned off AutoCommit using Management Studio, but nothing changes….

13

Instead of three dB.executes try

Adding all the code into one sql statement and executing that.

14

[quote]Russ Lunn[/quote] It’s a hard solution, in my case.

15

With MBS Xojo SQL Plugin and either SQLDatabaseMBS or SQLConnectionMBS you should only need to set AutoCommit property to kAutoCommitOff, which should implicit start a new transaction. Do your calls and make a Commit call on the end.

But this is up to the different connectors in the plugin how they implement it.
You know, we support 15 database types including 3 ways to talk to MSSQL.

16

Hi Christian, I tried yesterday using your plugin, without success (see my last posts)

17

I saw that.

Maybe we can try together via Teamviewer in a few days?

18

would this work?

SQL =“BEGIN TRANSACTION;”

SQL =SQL + “BEGIN TRY”
SQL = SQL + "DELETE FROM Offerte_T WHERE OFT_ANNO = " + CStr(Anno)
SQL = SQL + " AND OFT_NUMERO = " + CStr(Numero)
SQL = SQL + “;”
SQL = SQL + "DELETE FROM Offerte_R WHERE OFR_ANNO = " + CStr(Anno)
SQL = SQL + " AND OFR_NUMEROzz = " + CStr(Numero)
SQL = SQL + “;”
SQL = SQL + “COMMIT TRANSACTION”

SQL = SQL + " END TRY"

SQL = SQL + " BEGIN CATCH"
SQL = SQL + " ROLLBACK TRANSACTION;"
SQL = SQL + " END CATCH;"

DB.SQLExecute(SQL)

19

The Xojo plugin for MSSQL has been a pain in the *** as long as I know. Never got fixed.
I would suggest: just don’t distribute it if you know it doesn’t work properly.
Lot’s of us wasted time on it which could have been prevented if pointed into the right direction straight away.
Like most programming languages, also Xojo has its pitfalls and it helps if you know where they are.

20

Hi Joost, coming from Visual Basic and working on Windows environment, SQL Server is my standard Database for many years now.
When I switch to Xojo I had some troubles with MSSQL plugin, so I usually prefer the ODBC driver.
As far as you know, does the ODBC driver for SQL Server work fine or there are issues with it?