The only DB I’ve used with Xojo has really been SQLite and as a single user DB.
When using SQLite I connect whenever the app needs to do something and disconnect …
For using Postgres with multiple uses on a separate server , is it best to connect and stay connected or connect only when the app needs to do a DB operation and then disconnect?
Also for a user to be able to log out of the DB without closing the app, would one just disconnect then set username and password to “” in the DB object or should one (after closing the connection) delete that DB instance and create a new one?
If there is only one thread, you can maintain one connection, but periodically check to make sure it’s still alive. I use something simple like SELECT 1.
Each thread should have its own connection.
You can also open, use, and close, but that takes longer in theory. (In practice, you won’t notice.)
When done with the instance, I’d nil it and get a new one.
I am back using postgress. I am using API 1 but have subclassed PostgesDB to use exceptions for errors.
From what I have been reading it is relatively expensive to bring up a new connection in terms of both CPU and memory, so opening and closing the connection each time could affect server performance with a lot of simultaneous users…
But keeping connection open too long (say user leaves app open and walks away) with a lot of simultaneous users, could clog up the server and degrade performance even if most of the connections are idle from what I have read…and there is a (settable) connection limit that could be exceeded for teh server as a whole …
From what I have read best practice is to set the max connections to the DB to least values consistent with expected usage…
So In Xojo, is it best to stay connected and test if the connection is still there and reconnect (which error would I get) if not, or open and close every time a GUI app needs to perform a DB operation?
Also my app (desktop) will be single threaded, so is there any reason to allow normal users any more than 1 connection?
If available to you, I would recommend API2 for database access. It is much cleaner and requires less code.
To solve the disconnect issue, I use a custom PostgreSQL class with all of the same methods as the normal database class. In the executeSQL method, it runs the SQL. If it fails with an error, it tries to connect and then runs the same SQL a second time. If an error occurs again, it is raised.
Basically, with this class, I never worry about connecting or disconnecting at all. It handles it when needed. If I remember correctly, the server was disconnecting idle connections.
The most recent version I have a license for is 2023R1.1, but I strongly prefer API 1 and have a lot of API 1 code (have used Xojo for over 20 years). I have seen enough things which suggest API 2 has more bugs and some slowdowns as compared to API 1.
Constantly switching between the 2 API’s IMO a is bug fest waiting to happen, and I don’t need to keep going to the language reference tag way (and the new one is a a lot use user friendly than than the old one IMO… )
I was thinking about doing that with connects Right now I am connecting very time my appends to interact with the DB. It woud amount to the same thing,
BTW I have already subclassed PostgreSQLDatabase for Connect, SQLExecute , SQLSelect and Prepare
By default Postgres does not do that, but it does have a configuration setting to allow a timeout to be set.
So for a défaut sever setup staying connected might not be teh right thing… With a server timeout that would work OK.
So is there a reason to give users more than 1 connection if you are not using threads?
In one of my major apps (medical application, +/- 100 Users, about half connected via VPN) I use a class for all postgre functions and every client has only 1 connection, some have them open for weeks.
Besides: A own class is very handy if you use the notify/listen functionality.
Every 3 minutes the client send a dummy “SELECT TRUE;” command (VPN Users only) to make sure the connection still exists. If not it tries to reconnect (with a user warning).
If a query fails I check the errormessage for the string “connection”. I save the query and try to reconnect, after that retry the query. When I remember right the errornumber xojo throws is 3, but that isn’t always regarding connections.
The postgre list of error codes, for example https://www.postgresql.org/docs/9.3/errcodes-appendix.html lists other error codes.
On server-side I never had performance problems regarding the load (Linux VM, 16GB RAM, 8 Cores).
This setup runs for years now (API1), no troubles at all.
Hope this helps a little,
Rolf