Tip: Determine if you're in a transaction

Since the Xojo Database object doesn’t (yet) return notices, I came up with this to figure out if it was in a transaction. I can confirm that this works just fine with a PostgreSQL database, but have not tried others. Post here if you can confirm general compatibility.

Public Function IsInTransaction(Extends db As Database) as Boolean
  //
  // Since Xojo doesn't yet return notices, 
  // we will determine if were are in a transaction by
  // attempting to start a savepoint
  //
  // Note: SQLite will always return true
  //
  
  const kSavepointLabel = "ThisLabelWillNeverBeUsedElsewhere"
  
  db.SQLExecute "SAVEPOINT " + kSavepointLabel
  if db.Error then
    return false
  else
    db.SQLExecute "ROLLBACK TO SAVEPOINT " + kSavepointLabel
    return not db.Error
  end if
  
End Function

It’s not perfect but should work in most situations. (Except for SQLite where it doesn’t seem to be needed.)

I am missing something here…
I can see getting an error if you call this TWICE within a transaction… but not once…
Even if your transaction had set its own SAVEPOINT, you can nest savepoints
or at least in SQLite you can

MySQL always returns TRUE too…

In Postgres, you can only start a SAVEPOINT if you’re in a transaction already or you’ll get an error, so this method attempts to start a SAVEPOINT, and if there is no error, assumes it is in a transaction. It then rolls back the SAVEPOINT to leave you where you were.

SQLite and MySQL don’t return any error though so we’d have to find an alternate way to figure that out.

I’m tempted to argue that db clients shouldn’t need to know about these things.
Well, fwiw, I’ll just mention that an alternative to building super smart clients in Xojo that need to know wether or not the current connection is in a transaction is to create plpgsql functions in Postgres and put all the business logic into them, with transactions, rollbacks and whatever. This allows for the client to be real dumb about all of this, all they need to know is that these functions exist and how to call them. Another advantage is that you can fix and change stuff on the server on the fly without having to distribute new clients. Writing db functions is supported in afaik all databases Xojo developers usually use (alsoSQLite).

[quote=330255:@Maximilian Tyrtania]I’m tempted to argue that db clients shouldn’t need to know about these things.
Well, fwiw, I’ll just mention that an alternative to building super smart clients in Xojo that need to know wether or not the current connection is in a transaction is to create plpgsql functions in Postgres and put all the business logic into them, with transactions, rollbacks and whatever. This allows for the client to be real dumb about all of this, all they need to know is that these functions exist and how to call them. Another advantage is that you can fix and change stuff on the server on the fly without having to distribute new clients. Writing db functions is supported in afaik all databases Xojo developers usually use (alsoSQLite).[/quote]
Personally I’d put this in a middleware app and not the db itself
If you EVER have to move from one DB engine to another you will hate having to port all those procedures (been there done that and hated having to rewrite Sybase stored procedures for Oracle)
You retain much of the same “fix it on the back end” capability without the intimate ties of business login IN some db vendors specific extensions of sql

Why not use methods to start transaction and end them and keep a status flag in your database class?
Should be easier than doing save points.

[quote]Personally I’d put this in a middleware app and not the db itself
If you EVER have to move from one DB engine to another…[/quote]

Yeah, well, as I am usually the person who decides which db to use this has not been a concern (and I hope for me it will never be). But yes, true, that’s something to think of. PG is well respected these days, have yet to encounter somebody telling me to please move away from it.
My point is simply the modern database systems are development environments in themselves and Xojo developers should be aware of that.

They have been for many many years
A company I worked for did this way back in the 1990’s
Then had the exact issue I outlined when one very large vendors product moved from one DB to another (for very practical reasons)
Effectively they had no choice
Much pain & suffering ensued

This is really just a heads up that the architecture you decide today may live a very long time & have ramifications and having to redo it or move it is much much more painful later than building that flexibility in up front

For a feature request on that topic, you might want to add <https://xojo.com/issue/12883> to your favourites. Within that there is an alternative way to determine current transaction state for PostgreSQL given:

SELECT xact_start <> query_start AS in_transaction FROM pg_stat_activity WHERE pid = pg_backend_pid();

You can extend this technique to also detect if the current transaction is in an aborted state or current query is waiting for a lock by looking to the ‘state’ and ‘wait_event_type’ columns of that view. For this to be practical, you’ll need to use threads, a separate connection to the DB for monitoring and to store the backend PID of the main connection locally to not block yourself.

[quote=330260:@Christian Schmitz]Why not use methods to start transaction and end them and keep a status flag in your database class?
Should be easier than doing save points.[/quote]

That ties every object, even third-party or other reusable classes, to your app.

We started moving some of our business logic to PLPGSQL and calling those functions from our apps. Why? We are now using two different middleware apps, one in Xojo and the other in Javascript, and got tired of keeping the code coordinated between the two.

Believe me, this wasn’t an easy decision considering the pain we’ll be in if we ever have to switch databases, as Norman pointed out.

You have two different middleware apps you wrote - one in Xojo and one in Javascript ?
That does seems masochistic to have done that

Not purely masochistic, just mostly.

Didn’t occur to me that you might be using Xojo as middleware, I always think of it as a tool to build database clients.

Why wouldn’t you use one from both js clients & xojo clients ?
Having two forces you to have “common” business logic in yet another tier - which you’ve put in PLPGSQL

Do I remember a database called Openbase that allowed functions to be written in Realbasic?! Wasn’t even a prominent member of this forum involved in this company?

That was the ultimate plan.

Yes to both at one time
The upside to that was that you could put business logic in something that could be kind of portable (REALbasic / Xojo code)

Best laid plans of mice & men happened ? :stuck_out_tongue:

Something like that. The plan was to move the middleware from Xojo to Javascript (for good reasons), but 1) there is a LOT of code in Xojo that would have to be redone in JS, and 2) Xojo is working well so it’s hard to fix what’s not broken, and 3) each platform has advantages and, frankly, Xojo is preferred, and 4) there is a LOT of code to rewrite. (Yes, I know I said that already, but there really is a lot.)