Really basic SQLite questions

Mike Kerner MikeKerner at roadrunner.com
Fri Apr 25 18:04:22 CEST 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 me.com> 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 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
>
>
> _______________________________________________
> 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
>



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