saving sqlite database to a file

sandy771 · Post by **sandy771** » Thu 20 May 2010 09:24

I have created an sqlite database and populated with data imported from another source - I now need to save the database to an sqlite .db file

How do I go about this?

bork · Post by **bork** » Thu 20 May 2010 12:22

Hello

Why you cannot change an extension of your SQLite database file to ".db" in the Windows explorer?

sandy771 · Post by **sandy771** » Thu 20 May 2010 13:07

I am creating the database in memory at run time - I want to save as a file it so I can open it later

bork · Post by **bork** » Thu 20 May 2010 14:37

Please describe what does it mean: to create the database in memory at run time? What components did you use for it? How did you create your database: by queries or some other way?

sandy771 · Post by **sandy771** » Thu 20 May 2010 15:13

I have used a TUniTable with a TUniConnected
I create a number of tables using (for example) sql = "create table dir (ID integer, parent integer, name text(255))";
I then fill the data base with data obtained from a binary file that I have decoded.

I then want to save this data to an sqlite.db file so that I can reload it using my program at a later date or load it using some third party program such as Firefox SQliteManager.

Actually saving it as *any* file type that is reasonably compact (so not XML) would be OK. I just need a fast method of saving the complete database, 5 different tables, and then reloading at a later date.

bork · Post by **bork** » Fri 21 May 2010 08:43

If you work with SQLite database then any post to a table stores data to the database file automatically. And later you can connect to this file and use the stored data. Exception is posting data to the table in transaction. If you post data to the table in transaction then data will be stored to the database file after COMMIT only. The file where data will be stored is defined in the DataBase property of TUniConnection.

sandy771 · Post by **sandy771** » Fri 21 May 2010 09:45

OK thanks, here is what I did

I created a datbase at run time and left te database property blank - so no database created anywhere on disk (I assume it is created in memory in this case). I loaded my data into the database and this took approximately 2 minutes.

I then entered c:\test.db into the database properties box in UniConnection and ran the same program. This looks like it will take more than two hours to load the same set of records.

bork · Post by **bork** » Fri 21 May 2010 10:44

This topic http://www.devart.com/forums/viewtopic. ... ght=sqlite contains a suggestion on how to speed up insert/update to an SQLite database.

sandy771 · Post by **sandy771** » Sat 22 May 2010 11:26

Thanks Bork

I still havent got an answer to my question about saving to a file.

For clarity I have the following issue

I connect a UniSQL to a UniConnection
I leave the database proprty in the uniConnection blank
I create the tableI want with a uniSQl->SQL "create table mytable..." etc.
I populate the table with data.

I then need to save this data (which currently only exists in memory) to an sqlite file. How do I do this.

bork · Post by **bork** » Tue 25 May 2010 09:57

To save your data to the SQLite database you should perform the following steps in the following order:
1. Set file name to the Database property of UniConnection
2. Create tables with query: "create table mytable..." etc.
3. Insert your data to the tables

I've described how to insert data into an SQLite database quickly in this topic: http://www.devart.com/forums/viewtopic. ... ght=sqlite