SQLite connection drops occasionally

Creating desktop or client-server database solutions?

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

SQLite connection drops occasionally

Post by MichaelBluejay » Tue Oct 08, 2019 7:36 pm

Seems like about every 4-5 hours LC loses the connection to the database and returns an error. (I don't remember the text of the error.) At that point I run the openDatabase handler again and then everything's fine.

Seems like the solution is just to call openDatabase at the top of any handler that's accessing the database. Seems a little clumsy to be constantly refreshing the db connection, but I presume it will work.

Any insight into this problem?

Code: Select all

local dbID

command openDatabase
   put "/full/path/to/theData.data" into tDatabasePath
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into dbID
end openDatabase

Klaus
Posts: 13793
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite connection drops occasionally

Post by Klaus » Thu Oct 10, 2019 2:23 pm

Hi Michael,

no idea why this happens, but since connecting to a local SQLite database takes
literally one or two milliseconds in LC, I started to open and close the db before
and after every access a couple of years ago and had no problems with that ever since.


Best

Klaus

cpuandnet
Posts: 32
Joined: Thu Jan 17, 2019 6:43 am

Re: SQLite connection drops occasionally

Post by cpuandnet » Thu Oct 10, 2019 5:43 pm

Hi Michael,

The technique I often use to minimize this issue, no matter which database i use, is to have a function on the stack script that returns the database id. In that function, you can check whether or not the database is open. If the database is not open, then call the openDatabase command. This way, you don't even need to call the openDatabase during initialization because it will get called the first time you use the getDatabaseID function.

Code: Select all

local dbID

command openDatabase
   put "/full/path/to/db" into tDatabasePath
   put revOpenDatabase("sqlite", tDatabasePath, , , , ) into dbID
end openDatabase

function getDatabaseID
   if dbID is not among the items of revOpenDatabases() then
      openDatabase
   end if
   
   return dbID
end getDatabaseID

MichaelBluejay
Posts: 222
Joined: Thu Jul 01, 2010 11:50 am

Re: SQLite connection drops occasionally

Post by MichaelBluejay » Fri Oct 11, 2019 11:23 pm

Okay, thank you very much.

Post Reply

Return to “Databases”