I have been working with Xojo 2013 r3.3 and am migrating to Xojo 2018 r1 and have found a difference in how db.connect works inside of a transaction.
In version Xojo 2013 r3.3, I could test whether the connection to the database was active using db.connect and not disturb the current transaction.
In version Xojo 2018 r1, calling db.connect causes an error in the current transaction. I believe it kills the current transaction and starts a new connection.
If I have a method that needs to access the database while inside a transaction, I need to either assume the transaction is still active or perform the query using a new database connection.
Of course, I could set a flag while inside a transaction, but I don’t know if there is any way just to test if already connected.
Am I missing something? Isn’t this one of the major reasons for using a “transaction”… in case you lose connection, you can restart from a known point. If the connection is lost during the execution of a transaction, the whole transaction fails. So you shouldn’t bother worrying about it, until the transaction completes or fails, and then either move on (ie. no error), or rollback and try again.
Assume the transaction is still active. Prepare to fallback on error using Exceptions and verifying db.Error and continue or start things over again. Once a connection drops, doing another one does not recover you “old situation”, your old transaction should have been abandoned (rolled back).
It just seems that Xojo 2013 was more tolerant than Xojo 2018 in regards to db.connect.
In Xojo 2013, I could use db.connect to test whether a connection was still active. In Xojo 2018, the current connection is (as far as I can tell) killed and a new connection is initiated.
It’s probably not a bad thing, just different.
I was probably doing things within a transaction that I should move outside of the transaction.
This was probably a false sense of security. I seem to recall forums discussions on .Connect around a year ago and it was traced to older versions not doing it right. So I think the fact it tested as true in 2013 was a red herring.
But more to the point, as Dave points out, you should just let the transaction continue until you commit but gracefully handle exceptions and roll back instead of trying to keep testing. Besides, a transaction can fail for other reasons than a lost connection anyway.