Copying an in use SQLite Database File

[mrmikehicks]

I am using sqlite3 datasets, and all is so far working again. I tried to add a backup function, and found that I can't copy the actual sqlite3 database file (in my case research.db) Every attempt to copy this file within the application as a backup fails to open the file. I can copy it all over the place while my app is open if I use windows explorer, but not from within the app. I only have datasets and datasources to close and with all the datasets set to 'active'=false, I still can't copy this file.

Is there some other database function that will let me close and disconnect from this file (from a button click or menu) so that I can copy it to a backup location? Thanks

[LuizAmérico]

How are you trying to move the file?

Try setting FileName to ''

[eny]

Are you sure all datasets are closed/not active and all updates have been applied (<ds>.ApplyUpdates)?

[mrmikehicks]

I am trying to just copy the sqlite3   .db file for backup. I can copy it from dos or windows even while the program is running, just not from within the program. I have set all 8 or so datasets to inactive, set all datasource components to not enabled. I get an error when trying to change the filename to '' after setting all to inactive:
"sqlite_ds_file: It's not allowed to change Filename in an open dataset."
If that's the case, then obviously, I don't know how to 'close' the dataset. Heard a rumor that there may be an SQL command I can execute in the dataset to backup up the database file. Not sure.

[LuizAmérico]

To close a dataset call Close or Active := False

It' not necessary disable datasource

Heard a rumor that there may be an SQL command I can execute in the dataset to backup up the database file. Not sure.

It's not rumour but is not so easy:

http://www.sqlite.org/c3ref/backup_finish.html

[mrmikehicks]

thanks for the input, but the reference you gave is for a C api, not a delphi command. Not sure how I would use that.

Since I can copy it from dos or windows while the program is running, I'm thinking about just creating a batch file or pearl script that I can just call from within lazarus to do the backup.

[eny]

I am trying to just copy the sqlite3   .db file

How do you (try to) do this?

[mrmikehicks]

Eny, I am using code to create a backup copy of the sqlite3 database file on my hard drive. This is the same database file that my program is currently using to access data. I believe it is probably opened for exclusive use, not allowing even read share. When this code executes an error code is returned telling me that the database file that was accessed, could not be opened for read. My question to the group, was aimed at finding an answer as to why my running program cannot access this file for copy (read access) and yet other programs like a dos window command, or windows explorer, can access and copy the file without problems (while my program is still running.) I don't have specific examples of the 'copy code' that I tried and which didn't work. I used about 15 different filecopy versions found in three days worth of searching on the net. Suffice it to say, that I tried every copyfile, filecopy, and other variant of copy function within delphi and lazarus including some posted 'custom' stream versions etc, and none of them could open the database file for read access to copy it. I tried every available file open mode for sharing, denying access etc without success. Some of the copy variants required PChar conversion vice string, and it made no difference. anybody out there can test this if you have an sqlite3 program running, just try to copy the database file while it's running. I finally ended up calling a dos command to copy the damn file:

    uses ..., dos, ....
    ...
    // doscmd1 does backup of db file to standard directory (./backup/xxx.db)
    doscmd1 := '/c copy /B ' + filesource + ' ' + filedest;
    Exec (GetEnv('COMSPEC'), doscmd1);
    ...

This is clunky at best as it flashes open the command window for a second or two while doing the copy. No other lazarus or delphi, or custom, copy command has been able to open the file for read access. I have to fix this since I want the program to be cross-platform, and using dos to copy a file won't work on linux or mac.

The code I'm using right now is huge. If I can get time, I will try to put together a small sample program that I can put on the forum, so you guys can have a whack at trying to copy the .db file from within the code. Meanwhile, I really do appreciate all the replies.
     Mike

[davesimplewear]

Are you trying to backup a table or the database??

[mrmikehicks]

I want to backup the whole database (it's all in one file) I have attached a zip file that shows the problem. Right now it's hardcoded with a .pas file from the directory and the copy works. If you change the source and dest filenames to the database file, it will not work, and no copy function or procedure I could find would allow me to open the database file from within the program.

[xircon]

I am just finding my feet with Lazarus/FPC.  But (and feel free to shoot me down) if you want to backup a single database, why not use sql.  I assume the commands are available? Something like:

select * from your_table into new_table

Just a thought.

Steve

[eny]

But (and feel free to shoot me down) if you want to backup a single database, why not use sql

No need for such drastic meassures 

An OS file copy is much, much quicker than a copy via SQL.

[davesimplewear]

i had it working in Linux, used your code but changed some of the file paths and names and it works ok for me, I have uploaded it, try it out.

Regards
Dave

[davesimplewear]

Also mrmikehicks, thank you, I had been looking for a way to backup sqlite3 db's, so you have helped me as well.

Regards
Dave

[davesimplewear]

OOPs, just noticed a mistake after I ran the cide in Linux, i converted back to windows but left some things in dbdest code before the semi-colon, here is corrected .pas file.

Regards
Dave