Copying an in use SQLite Database File
lazarus / 16-Databases

----------------------------------------------------------------------
mrmikehicks · 2010-03-10 07:31

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 · 2010-03-10 15:17

How are you trying to move the file?

Try setting FileName to ''

----------------------------------------------------------------------
eny · 2010-03-10 15:41

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

----------------------------------------------------------------------
mrmikehicks · 2010-03-10 16:13

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 · 2010-03-10 16:36

To close a dataset call Close or Active := False

It' not necessary disable datasource

QuoteHeard 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 · 2010-03-10 16:46

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 · 2010-03-11 04:25

Quote from: mrmikehicks on March 10, 2010, 05:13:22 pmI am trying to
just copy the sqlite3   .db file
How do you (try to) do this?

----------------------------------------------------------------------
mrmikehicks · 2010-03-15 04:25

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 · 2010-03-15 04:46

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

----------------------------------------------------------------------
mrmikehicks · 2010-03-15 08:26

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 · 2010-03-15 09:13

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 · 2010-03-15 10:21

Quote from: xircon on March 15, 2010, 10:13:11 amBut (and feel free to
shoot me down) if you want to backup a single database, why not use
sqlNo need for such drastic meassures 

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

----------------------------------------------------------------------
davesimplewear · 2010-03-16 00:17

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 · 2010-03-16 00:19

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 · 2010-03-16 00:24

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

----------------------------------------------------------------------
mrmikehicks · 2010-03-16 01:01

davesimplewear, don't know why your happy, does this code actually
work on your system? I can't get it to read the database file with any
of several copy methods (except the dos cmd prompt method)

Had a thought that I want to investigate: The laz fpc sqlite3 dataset
component relies on the same sqlite3.dll that is placed in the path.
The sqlite3.dll has a funcion call for a command to backup the
database. The syntax for the command line version using sqlite3.dll
is:

.backup ?DB? FILE      Backup DB (default "main") to FILE

this cmd works like a charm, but I don't know how to get to it to
execute the command. I was looking at the dataset.executedirect() 
syntax to see if it will let me get to the inner calls of the
sqlite3.dll  (which it is already using)
anyway will let all know what happens.
     Mike

----------------------------------------------------------------------
davesimplewear · 2010-03-16 02:30

mrmikehicks, yes it worked ok for me once I changed to
dbsrc:=extractfilepath(application.exename)+'database\sqlite_test.db;
dbres:=extractfilepath(application.exename)+db_backup\sqlite_test.db;
it compiled, ran and saved to the directory. Maybe by trying to save
sqlite3.filename as a parameter it didn't work.

Regards
Dave

----------------------------------------------------------------------
mrmikehicks · 2010-03-16 02:56

Davesimplewear, that was the original problem, it will copy correctly
unless the file you're trying to copy is the database file that the
dataset is using. I don't understand how changing the filename from
sqlite3_test.db to sqlite_test.db (which doesn't exist) can work for
you.

----------------------------------------------------------------------
mrmikehicks · 2010-03-16 03:12

Problem solved, you were right davesimplewear,  I had the wrong
directory structure, and in this case I was also trying to copy to a
nonexistant directory ('db_backup' didn't exist') so created the
directory and it actually worked for me:

procedure TForm1.FileCopy(const FSrc, FDst: string);
var
  sStream,
  dStream: TFileStream;
begin
  sStream := TFileStream.Create(FSrc, fmOpenRead);
  try
    dStream := TFileStream.Create(FDst, fmCreate);
    try
      {Forget about block reads and writes, just copy
       the whole darn thing.}
      dStream.CopyFrom(sStream, 0);
    finally
      dStream.Free;
    end;
  finally
    sStream.Free;
  end;
end;

procedure TForm1.btn_cpy_dbClick(Sender: TObject);
begin
    form1.Sqlite3Dataset1.Active:=false;
    dbsource :=
extractfilepath(application.exename)+'database\sqlite3_test.db';
    dbdest :=
extractfilepath(application.exename)+'db_backup\sqlite3_test.db';
    form1.FileCopy(dbsource,dbdest);
end;

thanks to all who listen to all this and responded.
     mike

----------------------------------------------------------------------
davesimplewear · 2010-03-16 04:16

mrmikehicks, I was only using psuedo code in the reply from memory,
just check the converted project and .pas file for what I was doing,
good to see it worked for you.

Regards
Dave

----------------------------------------------------------------------
mrmikehicks · 2010-03-16 04:21

Thanks Dave. I'm glad I can put this one to bed finally.
     Mike