Android Question how to handle a connection in mysql

[jose luis gudino]

Hi
how should we handle the connection to mysql:
open and close the connection for each transaction

SQL1.Initialize(.......
    SQL1.ExecQuery("SELECT .....
    SQL1.Close
    
    SQL1.Initialize(.......
    SQL1.ExecQuery("SELECT .....
    SQL1.Close

or
open the connection only once and do not close it again
I have some projects where the connection remains open for days or weeks, but I don't know if it is the most convenient

SQL1.Initialize(.......
    SQL1.ExecQuery("SELECT .....
    SQL1.ExecQuery("SELECT .....
    SQL1.ExecQuery("SELECT .....
    SQL1.ExecQuery("SELECT .....

Thank

 

[MicroDrie]

There is no standard answer that always fits every application. Opening and closing a database takes a relatively long time. If the app sends almost continuous updates and queries to the database, there will come a time when opening and closing the database will affect the performance negatively.

Then there is the problem that backing up an open database is a challenge in itself. For performance reasons, the OS can decide not to write the data directly, but to put it in a buffer first. If a problem arises somewhere, the contents of such a buffer may not be written, causing data corruption or loss. For a read-only database is this no issue.

It simply applies that every choice has its own risks and you have to make the right decision for a certain situation.

 

[jose luis gudino]

There is no standard answer that always fits every application. Opening and closing a database takes a relatively long time. If the app sends almost continuous updates and queries to the database, there will come a time when opening and closing the database will affect the performance negatively.

Then there is the problem that backing up an open database is a challenge in itself. For performance reasons, the OS can decide not to write the data directly, but to put it in a buffer first. If a problem arises somewhere, the contents of such a buffer may not be written, causing data corruption or loss. For a read-only database is this no issue.

It simply applies that every choice has its own risks and you have to make the right decision for a certain situation.

Thank you for your time and your answer.
My approach is because I have some webservices that perform records every 1 minute, but they are active 365 days a year, that is why I am concerned about knowing which would be the most convenient method.
I think that I could make a routine every certain time to close and open the database, and I don't know if this is really necessary or not, that is, my connection will remain open for all this time.

 

[MicroDrie]

Adding functionality afterwards is always plastering. I understand that this is actually about a service that is/must be available 7 * 24 hours. Then you should also have some kind of backup in case your primary website goes down. Normally there is some form of synchronization between your primary and backup location. In that case you could make a backup of the secondary location and after the backup the synchronization would bring both databases back in sync. You can find more information about backing up in MySQL here

What matters is the specific irrefutable goal and the necessary means with which you can realize that one specific goal. Opening and closing a database is just a means, not a goal. It's about the strategy of keeping the service available "securely" for 7 * 24 hours and getting it back up and running as quickly as possible in case of problems, and that requires security by design from the start of the design.

 

[jose luis gudino]

Thank!!!