Recent

Author Topic: SOLVED SQLite vacuum  (Read 20334 times)

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
SOLVED SQLite vacuum
« on: January 13, 2011, 05:16:43 pm »
How do I implement the command in sqlite vacuum? Thanks
« Last Edit: January 19, 2011, 04:05:43 pm by xinyiman »
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #1 on: January 14, 2011, 01:44:10 pm »
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?
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

DirkS

  • Sr. Member
  • ****
  • Posts: 251
Re: SQLite vacuum
« Reply #2 on: January 14, 2011, 01:51:21 pm »
AFAIK you just execute 'VACUUM' as any other SQL statement...

Gr.
Dirk.

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #3 on: January 14, 2011, 01:52:50 pm »
Using a TSqlQuery? Make me an example?
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #4 on: January 14, 2011, 02:34:49 pm »
     Q_Vacuum.SQL.Text:='vacuum';
     Q_Vacuum.ExecSQL;

Errore:

cannot vacuum from within a transaction.



Why?  :o
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #5 on: January 14, 2011, 03:03:08 pm »
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
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

garlar27

  • Hero Member
  • *****
  • Posts: 594
Re: SQLite vacuum
« Reply #6 on: January 14, 2011, 03:05:10 pm »
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

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #7 on: January 14, 2011, 03:16:16 pm »
Well of course I have an open connection, how can I send the command sql otherwise?
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

garlar27

  • Hero Member
  • *****
  • Posts: 594
Re: SQLite vacuum
« Reply #8 on: January 14, 2011, 08:14:15 pm »
You're right

JanRoza

  • Hero Member
  • *****
  • Posts: 521
    • http://www.silentwings.nl
Re: SQLite vacuum
« Reply #9 on: January 14, 2011, 11:56:12 pm »
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.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.2 FPC 3.0.4 i386-win32-win32/win64

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #10 on: January 19, 2011, 11:29:35 am »
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.
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1

xinyiman

  • Hero Member
  • *****
  • Posts: 1875
    • Lazarus and Free Pascal italian community
Re: SQLite vacuum
« Reply #11 on: January 19, 2011, 04:05:28 pm »
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
Win10, Ubuntu and Mac
Lazarus: 2.1.0
FPC: 3.3.1