SOLVED SQLite vacuum

[xinyiman]

How do I implement the command in sqlite vacuum? Thanks

[xinyiman]

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]

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

Gr.
Dirk.

[xinyiman]

Using a TSqlQuery? Make me an example?

[xinyiman]

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

Errore:

cannot vacuum from within a transaction.



Why?  

[xinyiman]

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]

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]

1. SQLite3Connection1.ExecuteDirect('VACUUM');

[xinyiman]

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

[garlar27]

You're right

[JanRoza]

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]

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]

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