Really basic SQLite questions

[Robert Sneidar]

My experience with SQL is that there is a timeout if a connection has been idle for a certain period of time. Perhaps this is not a problem with sqLite, but as a matter of good development habits, get all the info you need from the user BEFORE opening the connection, so that there is no chance of having a user walk away from his desk while your app is wanting something from him with an open connection. Once you open the connection, do what you need to do, then close it. 

If you have anything like complex transactions where you may have to abort all your inserts and updates mid stream, use transactions. This way you can rollback midstream with no ill effects. If the connection goes stale, you will find that the user will have to sit there for 30 seconds to a minute or more waiting for the app to release so you can deal with the connection timeout. Not pretty. 

I cannot tell you the hell and havoc I have had working with different database applications where the developers figured, “What the hell, just leave the connection open,” and then something happens network wise and the application dumps to the desktop. That is crappy programming IMHO. 

Would you open an FTP session and just leave it open for convenience? How about an HTTPS session? Think about how your bank works. If you are idle for any length of time they kick you. If I am logged into a copier too long for crying out loud That will kick me out! It’s my opinion at least that every network or database connection should have some means of disconnecting when idle. It’s just good practice. 

Bob S


On Apr 24, 2014, at 9:07 PM, Peter Haworth <pete at lcsql.com> wrote:

> Hi James,
> I'd leave them open until you quit the app. You might also think about
> using the ATTACH command for the second and subsequent connections so
> they'll all be open on the same connection. This allows you to, for
> example, define JOINs across databases, although you'll need to change the
> references to anything in the attached databases.
> 
> Pretty sure you need to load extensions for each connection,  so the ATTACH
> approach will help there too.
> 
> Pete
> lcSQL Software
> On Apr 24, 2014 7:49 PM, "James Hale" <james at thehales.id.au> wrote:
> 
>> 1. Open and close database connections. When?
>> Obviously you need to open a connection to access a database, but when do
>> you need to close it?
>> Does it close without you doing anything?
>> I have the need to connect to four db's while running my app.
>> Access to a couple of these is sporadic but constant across a user session
>> with the app.
>> Can I just open a connection to them all on the app's launch and then
>> close the connection of quitting? Currently I reassert the connection on
>> most accesses. Is this a bad thing to do?
>> 
>> 2. SQLite's new ability to load a runtime extension. What does it attach
>> to?
>> I have compiled a couple of extensions to SQLite for my app. I use them in
>> two distinct db's that I connect to. Do I need to load the extension for
>> each db or only once.
>> In other words is it loaded for the engine itself or does the load only
>> apply to the specific database I am connecting to at the time?
>> 
>> Thanks in advance
>> 
>> James
>> _______________________________________________
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
>> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode