One database, many windows = multiples transactions ?

  1. 2 weeks ago

    Jean-Yves P

    Oct 10 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    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.

  2. Tim H

    Oct 10 Pre-Release Testers Portland, OR USA

    Use multiple connections.

  3. Joost R

    Oct 10 Pre-Release Testers, Xojo Pro The Netherlands

    @Tim H 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.

  4. Jean-Yves P

    Oct 11 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    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.

  5. Joost R

    Oct 11 Pre-Release Testers, Xojo Pro The Netherlands

    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. ?

  6. Greg O

    Oct 11 Xojo Inc Somewhere near Raleigh, NC

    Tim is correct. If you want to maintain several simultaneous transactions, you’ll need one database connection per window.

  7. Eli O

    is not verified Oct 11 Europe (Berlin, Germany)

    @Jean-YvesPochez I can't see how to do it without multiple connexions to the database, one 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.

  8. Jean-Yves P

    Oct 11 Pre-Release Testers, Xojo Pro Europe (France, Besancon)
    Edited 2 weeks ago by Jean-Yves P

    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 ?

  9. Eli O

    is not verified Oct 11 Europe (Berlin, Germany)

    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.

  10. James D

    Oct 11 Pre-Release Testers, Xojo Pro Europe (Switzerland)

    @Tim H Use multiple connections.

    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.

  11. Jean-Yves P

    Oct 11 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    James, why are you against multiple connexions ?

  12. Eli O

    is not verified Oct 11 Europe (Berlin, Germany)

    @Jean-YvesPochez 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.

  13. Oliver O

    Oct 11 Pre-Release Testers, Xojo Pro https://seminar.pro

    @Jean-YvesPochez James, why are you against multiple connexions ?

    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’.

  14. Tim H

    Oct 12 Pre-Release Testers Portland, OR USA

    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.

  15. Eli O

    is not verified Oct 12 Europe (Berlin, Germany)

    @Tim H 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.

    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).

  16. Eli O

    is not verified Oct 12 Europe (Berlin, Germany)

    @Tim H ames 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.

    You can do it that way of course, but there is no need for it.

  17. James D

    Oct 12 Pre-Release Testers, Xojo Pro Europe (Switzerland)

    @Jean-YvesPochez James, why are you against multiple connexions ?

    Not at all when it is appropriate. But there are many reasons why it is not appropriate here as others have pointed out.

  18. Jean-Yves P

    Oct 12 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    @Oliver O One reason would be to save your customers from buying a database license with unlimited connections.

    as the database is postgres or sqlite, this is not a problem ...

or Sign Up to reply!