Using OnSQL event for logging as backup/recover

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Schlueter
Posts: 14
Joined: Thu 29 Aug 2013 06:57

Using OnSQL event for logging as backup/recover

Post by Schlueter » Wed 20 Aug 2014 20:39

Hi,
I want to use TCustomDASQLMonitor.OnSQL event for logging all INSERT, UPDATE and DELETE events. That's no problem.
But is there a simple way, using the logged sql statements for recovering after DB-failure?

At the beginning of a day I will make a backup of my DB-file.
Then I'm logging all sql statements (INSERT, UPDATE and DELETE).
If I have a DB-failure (e.g. disk crash), then I only have to copy the DB-backup from the morning and can recover the actual DB-state by executing the logged sql statements again.

Do you know a simple kind of executing the sql statements from logging?

Example:
update "TEST" set
"Testvalue" = :1
where
"Test_Id" = :2
:1(Integer,IN)=999
:2(Integer,IN)=1

Best regards
Rolf

ZEuS
Devart Team
Posts: 236
Joined: Thu 05 Apr 2012 07:32

Re: Using OnSQL event for logging as backup/recover

Post by ZEuS » Fri 22 Aug 2014 08:01

The TLiteSQLMonitor component is intended only for tracing SQL activity of database components. Therefore, in the OnSQL event handler, it shows the SQL text in the form, in which it is sent to the database server. So, to execute the logged SQL statements in future, you should preprocess them yourself in the OnSQL event handler. For example, translate the statement you provided into

Code: Select all

update "TEST" set
"Testvalue" = 999
where
"Test_Id" = 1
Then, you can execute a couple of logged statements using the TLiteScript component ( http://www.devart.com/litedac/docs/deva ... script.htm ).
Besides, in the next LiteDAC release, we plan to present a new component - TLiteBackup. The component implements the SQLite Online Backup API ( http://www.sqlite.org/backup.html ) and is designed to make frequent database backups "on the fly".

Schlueter
Posts: 14
Joined: Thu 29 Aug 2013 06:57

Re: Using OnSQL event for logging as backup/recover

Post by Schlueter » Fri 22 Aug 2014 09:18

Online Backup API sounds great :D.
I will wait for TLiteBackup. Thanks.

Post Reply