SOLVED SQLite vacuum
lazarus / 16-Databases

----------------------------------------------------------------------
xinyiman · 2011-01-13 16:16

How do I implement the command in sqlite vacuum? Thanks

----------------------------------------------------------------------
xinyiman · 2011-01-14 12:44

I can not believe no one has ever hit upon the problem of having to
compress the sqlite database to clean it and reduce it in size. Can
anyone tell me how?

----------------------------------------------------------------------
DirkS · 2011-01-14 12:51

AFAIK you just execute 'VACUUM' as any other SQL statement...

Gr.
Dirk.

----------------------------------------------------------------------
xinyiman · 2011-01-14 12:52

Using a TSqlQuery? Make me an example?

----------------------------------------------------------------------
xinyiman · 2011-01-14 13:34

Q_Vacuum.SQL.Text:='vacuum';
     Q_Vacuum.ExecSQL;

Errore:

cannot vacuum from within a transaction.



Why?

----------------------------------------------------------------------
xinyiman · 2011-01-14 14:03

I have solved my problem by removing the CommitRetaining because both
use the SQLite database in my program as a support to close the
program so he does not write data to the db and are in place. But I
want to know how to fix it for possible future projects. Thanks

----------------------------------------------------------------------
garlar27 · 2011-01-14 14:05

Have you read http://www.sqlite.org/lang_vacuum.html?

Maybe, you have a connection opened, or have something that prevents
you from doing that.

If I need to do a vacuum, I would do it like this:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLite3Connection1.ExecuteDirect('VACUUM');

----------------------------------------------------------------------
xinyiman · 2011-01-14 14:16

Well of course I have an open connection, how can I send the command
sql otherwise?

----------------------------------------------------------------------
garlar27 · 2011-01-14 19:14

You're right

----------------------------------------------------------------------
JanRoza · 2011-01-14 22:56

Using SQLitedataset I compress my database via
Code: [Select]  tblVluchten.ExecSQL('VACUUM');
Executing this SQL on just one table of the database compresses the
whole database.
Just my 5 cents of advice.

----------------------------------------------------------------------
xinyiman · 2011-01-19 10:29

Please write a function that opens the connection to run the sqlite
vacuum and closes the connection to the db. Assume that the db is
called richard and is located in the executable.

----------------------------------------------------------------------
xinyiman · 2011-01-19 15:05

Solved,

uses ..., sqlite3ds;


procedure TForm1.MyVacuum();
var
    dsLite : TSqlite3Dataset;
begin
     dsLite := TSqlite3Dataset.Create(nil);
     dsLite.FileName:='db' + SepDir + 'settaggi.s3db';
     dsLite.ExecSQL('VACUUM;');
     dsLite.Free;
end;

Thank you