Hi Folks,
I have one database connexion, but multiple windows in my app, and they all edit some datas of the database
lest’s say it’s a postgres db, it could also be a sqlite.
I did not find how to have multiples transactions for one database . I want to be able to change datas in each window, but if the user closes
the window without saving, then the datas must not be saved.
do I have to open multiples connexions to the database in order to achieve this ?
thanks.
Use multiple connections.
Huh ? Why not one global connection, to be used by every Window / form ? Works simple from everywhere in your application and you close it as a part of the app-quit procedure.
Actually I prefer my own database-connection-wrapper class providing me with everything I need in an easy manner and taking care of connection-monitoring etc.
how do you start one transaction per opened window then ?
begin transaction must be followed by an END transaction ?
you can’t assign a transaction to a window.
I can’t see how to do it without multiple connexions to the database, one per window.
Jean-Yves, can you please tell us just a bit more about what you want to achieve with database-transactions, what application you are working on. ?
Tim is correct. If you want to maintain several simultaneous transactions, youll need one database connection per window.
This is only needed, if you keep transactions open for a certain amount of time. If on the other hand in each window you only have “Save” buttons where the transactions opens and closes within the Action event, then it is not needed.
scenario:
I open a window where I can edit an invoice. I open other windows from that invoice, for each line of the invoice, and I close it each time. beside of that, I open and edit the customer’s datas.
some possiblities :
1/ I cancel the invoice, and validate the customer datas.
2/ I validate both windows
3/ I cancel both.
4/ I validate the invoice, and cancel the customer’s datas
datas should be saved or not accordingly .
so one db connexion in the invoice window, and another in the customer window ?
One connection is sufficient if the process of updating the database is “atomic”. You open a transaction and then you roll it back or you commit, and only then you open the next transaction etc.
AFAIK all built-in Xojo database drivers are synchronous. So all you have to do is to have BEGIN TRANSACTION and ROLLBACK / COMMIT within the same method and you’re safe with one connection.
Definitely not! A single connection and one transaction, but only at the end when you need to save the data. You need to design the application so that UI, Business Logic and Data Access code are properly seperated.
James, why are you against multiple connexions ?
I’m sure he is not against multiple connections, but with your setup, done correctly, there is no need to use multiple connections. Again:
Open a transaction
Execute SQL
Rollback / Commit
If these three steps are within one method (and this method is not in a thread or timer or so) no two transaction can happen simultaneously. Each will be closed before the next opens, because no two methods on the main thread (the UI) are executed in parallel.
One reason would be to save your customers from buying a database license with unlimited connections.
If reusing or reselling my app is ever an option, I try to design it in a way where number of users equals number of connections.
James makes a good point about proper design. However, if the windows (as described in the OP, but later clarified) are actually completely independent of each other, then each should have its own connection.
I define “number of users” as the number of unique IP addresses. But that is all within a local area network. It wouldn’t work as well on the net, where you could have multiple users behind a NAT.
In my own apps, I only use one connection. But then I adhere to the scenario that James outlined. It could be that a user could have more than one instance of the app (really a suite of apps) open, but they only count as a single user, based on their LAN address.
However, the one exception I have run into is MS SQL, which basically requires you to have a separate connection for each table. I handle that behind the scenes, so my code doesn’t care if it’s talking to an sqlite database, or MySql, or Postgres, or MS SQL. The details should all be hidden behind an API.
Each desktop app opens a new connection by definition anyway (as of course you cannot use an open connection from a different app even if the same database driver is used in two instances of the same app).
You can do it that way of course, but there is no need for it.
Not at all when it is appropriate. But there are many reasons why it is not appropriate here as others have pointed out.
as the database is postgres or sqlite, this is not a problem …