Hi, I read in the documentation that desktop apps normally have to connect to the sqlite database file when the user starts the app, and close the connection only if you quit.
Why is it not recommended to connect sporadically?
There is no isConnected function. So if I have to check the connection every time, I must use the Connect method anyway. Does the Connect method behave like an isConnected function at that moment?
Thanks for your help.
Personally… I would reccomend a persistent connection only for a local desktop, or at worst a small lan install. Never would I suggest a persistent connection if you are using a remote hosted database
Hi Dave, Thanks for your reply. Why would you use a persistent connection? Is it an efficiency issue? It’s what I wanted to know.
Locally there is no reason not to leave it open… so yes in becomes an efficiency issue…
Remote… you want to engage the remote server as little as possible… so you would write more “robust” code to make the most out of each transaction.
I would look into mySQL or something else if you are doing remote database (including WEB)
SQLite is perfect for self-contained apps.
With regards to sqlite, how do you describe this efficiency issue, if I do not leave the file open. I say this, because the connection could close, e.g. if the computer goes to sleep, while the app is still running. At that moment, all my methods should have a db.connect condition. So where is the difference to open every time vs during startup?
On a local database I have never “lost” the connection just because the computer goes to sleep. If you app is alive so should be the connection once established. I connect at application start up and disconnect at app close. I do check the status of the DatabaseError after every transaction and deal with those accordingly.
If your concerned just put your connection code in a method, call it on application start up and call it again IF you get an error returned for a transaction that indicates you’ve lost the connection.
After connecting, I don’t think I have ever seen an error code related to losing the connection with SQLite…
OK. Thank you. One more question: You said that you verify connection checking the Error status. Supposing you get an error because you lost connection to the database, how will this affect your transactions? I mean if the database is closed, and you are trying to save or update some data. Is there no other way to check if the connection is open.
From what you said it seems that the connection is never lost, even if the computer goes to sleep and wakes after a few hours. If this is the case, then I think I should not worry…
You shouldn’t worry.
In your application startup you want to connect to the database and ensure that returns true, if it does, you’re connected. On my apps I connect at startup and if I can’t I alert the user and bail out of the app. Either the user moved the DB or something is very wrong.
You should check the status of the error after every database operation (updating, adding records, etc.). If there is an error you should alert the user with the code and description and rollback the transaction. Based on that error you could write error handling routines. If the database became disconnected (which I haven’t seen in a local usage) then trying to connect again probably won’t work, but you certainly could try. There are many errors related to SQLite, see the documentation. Some people will retry the transaction, others will just bail out and alert the user. How it is handled is up to you.
I found SQLite very easy to work with, quite robust and once connected works well.
Also, read this:
DB Transactions
I would suggest the opposite. Initiating TCP connections to databases is very time consuming and slows down your application’s response time. I would suggest a connection pool that allows multiple threads to pull an active connection from the pool as needed. Re-open the connection as needed if it fails.
If you know you have 5 users what purpose does it serve to have them opening/closing all the time?
Of course if your application is only used for a short period of time then throttling down the active connections in the pool is a good idea if your application remains open but is not actively servicing any requests.
I based my statement on the fact that most remote databases have a connection pool, and that pool is usually much much smaller that the user base… I managed a mySQL server that had 100 connections in the pool, and a user base of 500-600… so it was connect, transact, disconnect… and the response time was more than adequate…
Yes the server has a pool of client connections and some maximum threshold. If you exceed that there is a problem regardless if you open and close.
I have been working on a high performance Xojo web server for a web project that moves files in the gigabytes. I have measured this. The local web server having persistent connection pooling to the database was significantly faster than opening/closing connections.
In general, once you work with remote databases, you have to think about and implement a strategy first, Within the a business application you don’t want to deal with checks all the time, so I would suggest to encapsulate all the technical database stuff.