Unable to open database

[dglass]

An oddity I'd like to understand for my general fund of knowledge...

I have an SQLite database that I am successfully connecting to and retrieving data from.

I have written an import routine that takes a tab-delimited file, parses it, builds an INSERT statement, and executes the statement, thereby inserting the data from a line in the import file into the database.

For the most part the import works, but at seemingly random points while doing the inserts it fails with 'Unable to open the database file'. In an approximately 800-line file this can happen anywhere, but usually somewhere near the middle-ish, say line 300-something, or 400-something.

After this error, attempting to rerun the import gets the error on the very first line (and every subsequent line since I'm not doing an abort).

The only way I've found to be able to continue the import is to close LC and relaunch it, and split my import file at the location it stopped.

There seems to be some kind of file-locking/inaccessibility thing going on, but I haven't a clue why.

Any thoughts?

EDIT: to correct the text of the error message.

[Mark]

Hi,

Looks like you're opening and closing the database for every transaction. Open it once, then import all data, then close. If you have a repeat loop, give LiveCode some time to do its own thing by adding "messages":

Code: Select all

repeat for each line myLine in myData with messages
  -- insert stuff here
  wait 0 millisecs with messages
end repeat

Maybe it helps.

Best,

Mark

[dglass]

Hi,

Looks like you're opening and closing the database for every transaction. Open it once, then import all data, then close. If you have a repeat loop, give LiveCode some time to do its own thing by adding "messages":

I thought I was only opening it once, when the application launched, but I'll check that, and I'll add the messages to see what happens.


Thanks!

[bobcole]

I wrote a LC script to insert 15,000 records into a SQLite database. The commands were properly formatted as INSERT statements and all were in a variable called myCommands. I connected to the database once then inserted each line, one at a time, until finished whereupon I closed the database connection. It worked but seemed slow.
In some posting, I read that SQLite can use the TRANSACTION command so I added:
put "BEGIN TRANSACTION;" & return before myCommands
and
put return & "COMMIT;" after myCommands
That allowed me to execute the whole batch as one command rather than looping through 15,000 separate INSERT statements.
By avoiding a repeat loop, the insertion process went much faster.
You might want to try this approach.
Bob