[SOLVED] SQLite import large data

[toaster852]

Hi all

 

I have a slight problem that I can't seem to solve and I'm hoping someone can help me out or push me in the right direction.

 

I need to have a database for UK post code lookups and I was thinking about SQLite because it is fast at reading data (no updates / inserts will be going on) and I can just upload the whole database to the server.  MySQL is out as I only have a 100MB limit and I don't want to use up half of that with postcodes (I'm only using a few postcode subsets).

 

So the plan was to create a SQLite database, populate the database with the postcode data and upload it to the server.  I have created the database and the tables without any problems.  The problem is when I tried to insert the data. I'm getting an Error 14 unable to open database.  This is not a permission problem as it created without any problems and it does insert data but falls over after around a thousand records.  I have the data in a MySQL database at the moment and I am basically reading each record from MySQL and inserting it into SQLite.  The reason I'm doing this is all the tools I have found can copy the data without any problems but PHP won't read the database created as they are the wrong version.

 

So anyway I thought maybe it's a system issue and it doesn't like all the writing to the file from PHP for some reason.  So I changed the script to have a 3 second pause after inserting 500 records.  I got more records inserted this way but still get the same error in the end.

 

Has anyone got any ideas of what could be wrong or any other way I can do this?

 

I'm using Windows Vista with WAMP 2.0, CodeIgniter as the framework on my development machine.

 

Many thanks

[toaster852]

Found out what the problem was.

 

The insert statements needed to be wrapped in a transaction.  This solved the issue and all works fine