Recent

Author Topic: Emptying a SQLite table  (Read 13365 times)

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Emptying a SQLite table
« Reply #15 on: June 09, 2014, 06:14:11 pm »
Caravelle: the way I understand it, sqlite dbs are locked during sqldb transactions between start and commit. So make sure the transactions are as short as possible.

Problem solved... I hope?!?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Caravelle

  • New Member
  • *
  • Posts: 46
Re: Emptying a SQLite table
« Reply #16 on: June 09, 2014, 07:02:49 pm »
Quote
So make sure the transactions are as short as possible.

Problem solved... I hope?!?

In my application I have no control over the transactions which the commercial software creates to insert received data into the database table.  It never stops, constantly locking the database, but seldom for more than milliseconds at a time.  What works is a try loop to read data (or set off a transaction of my own) - if the database responds "BUSY", wait a few milliseconds and try again.  This is s.o.p. and is not an issue, as we do not want to hold up the data collection process.  I just haven't worked out how to do it in Lazarus yet  ::)   I'm not very good at Error catching procedures, which I think must be the way forward - to stop Lazarus from stopping with an error message whenever the db is BUSY.   

Thanks for taking  an interest.

Caravelle

taazz

  • Hero Member
  • *****
  • Posts: 5365
Re: Emptying a SQLite table
« Reply #17 on: June 09, 2014, 07:13:50 pm »
Let me add at this point that there are multi access databases that do not require a server like Microsoft's access borland's BDE (paradox, dbase, etc) to name a couple those have a very fine grained locking mechanism that can lock the database (Exclusive access) the table, a single record.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

Caravelle

  • New Member
  • *
  • Posts: 46
Re: Emptying a SQLite table
« Reply #18 on: June 09, 2014, 07:27:02 pm »
To comment on the original question from Uncle Reg:
Quote
Is there an easy way to empty the tables while maintaining the table structure (field definitions)?


The usual recommendation from sqlite.org is to DROP the table and CREATE it again.   If you don't have an sql statement which will create the table and can't work out what it should be (I imagine this is unlikely, but just in case) get hold of Sqlite Expert (personal version free at http://www.sqliteexpert.com/) open your table and hit the "DDL" tab.  This bit of magic will give you a full CREATE TABLE statement ready to cut and paste, though you may have to tinker with quote characters.   Embed this in your code as a string ready to be used as a statement when needed.  Dropping and then creating the table ensures that no baggage is left over, you start with a guaranteed clear slate.

Hope that helps

Caravelle   

kapibara

  • Hero Member
  • *****
  • Posts: 532
Re: Emptying a SQLite table
« Reply #19 on: June 10, 2014, 03:24:14 am »
@Caravelle: If you use the ZEOS database components, I think you can have multiple simultaneous readers and one simultaneous writer to SQLite:

http://sourceforge.net/projects/zeoslib/files/latest/download?source=files
Lazarus trunk / fpc 3.0.4 / Debian 10 - 64 bit

mse

  • Sr. Member
  • ****
  • Posts: 286
Re: Emptying a SQLite table
« Reply #20 on: June 10, 2014, 08:10:59 am »
If you need to access a Sqlite3 DB from different processes use the original tsqlite3connection from MSEgui - it uses implicit transactions by default. Also as written before don't forget to set packagerecords of the query components to -1, the default in MSEgui, otherwise a pending SELECT operation will lock all other transactions. ZEOS AFAIK also uses implicit transactions by default.

Caravelle

  • New Member
  • *
  • Posts: 46
Re: Emptying a SQLite table
« Reply #21 on: June 10, 2014, 04:47:03 pm »
Thank you mse.  I have tried to read the wiki page http://wiki.freepascal.org/MSEide_%26_MSEgui and it is way above my head.  I am only just transferring from Delphi 7 to Lazarus.  It is simply not a practical proposition for me to struggle to understand this.  Rather like having to build a new toolshed just to get a new hammer.

Thank you kapibara.  I did try installing the ZEOS components when I first installed Lazarus.  It all went horribly wrong somewhere and I ended up having to reinstall Lazarus from scratch.  I haven't dared touch Zeos since.  I guess I'll have to have another go.

Caravelle

mse

  • Sr. Member
  • ****
  • Posts: 286
Re: Emptying a SQLite table
« Reply #22 on: June 10, 2014, 08:21:19 pm »
Suggestion: Comment all the execesql() calls in the tsqlite3connection transaction procedures.
Code: [Select]
function TSQLite3Connection.Commit(trans: TSQLHandle): boolean;
begin
//  execsql('COMMIT'); <<<<<--
  result:= true;
end;

function TSQLite3Connection.RollBack(trans: TSQLHandle): boolean;
begin
//  execsql('ROLLBACK'); <<<<<--
  result:= true;
end;

function TSQLite3Connection.StartdbTransaction(trans: TSQLHandle;
               aParams: string): boolean;
begin
//  execsql('BEGIN'); <<<<<--
  result:= true;
end;

procedure TSQLite3Connection.CommitRetaining(trans: TSQLHandle);
begin
  commit(trans); 
//  execsql('BEGIN'); <<<<<--
end;

procedure TSQLite3Connection.RollBackRetaining(trans: TSQLHandle);
begin
  rollback(trans);
//  execsql('BEGIN'); <<<<<--
end;
File packages/fcl-db/src/sqldb/sqlite/sqlite3conn.pp, not tested.

 

TinyPortal © 2005-2018