Really basic SQLite questions

Mike Kerner MikeKerner at
Fri Apr 25 12:04:22 EDT 2014

Bob, I agree that in general, when dealing with a server, you are exactly
right on what you should be doing.  Since SQLite is a local file-store DB,
it's a little different.  it isn't designed for transaction loads,
multi-users, etc.  it doesn't even support all the datatypes - for
instance, yes, you can define a boolean, but it isn't a boolean.

database servers require an entirely different class of developer, imho,
otherwise all sorts of evil things will happen.

On Fri, Apr 25, 2014 at 1:22 AM, Robert Sneidar <slylabs13 at> wrote:

> 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> 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
> > approach will help there too.
> >
> > Pete
> > lcSQL Software
> > On Apr 24, 2014 7:49 PM, "James Hale" <james at> 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
> >> Please visit this url to subscribe, unsubscribe and manage your
> >> subscription preferences:
> >>
> >>
> > _______________________________________________
> > use-livecode mailing list
> > use-livecode at
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> >
> _______________________________________________
> use-livecode mailing list
> use-livecode at
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:

On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."

More information about the Use-livecode mailing list