Emptying a SQLite table lazarus / 16-Databases ---------------------------------------------------------------------- Uncle Reg · 2014-06-06 16:01 I have a SQLite database with 2 tables that I will periodically need to delete all records from the tables in preparation for importing updated data. Is there an easy way to empty the tables while maintaining the table structure (field definitions)? Something similar to emptying a Dbf table using the Delphi emptytable function or the FoxPro Delete All/Pack or Zap commands. ---------------------------------------------------------------------- Leledumbo · 2014-06-06 17:26 Standard SQL: Code: [Select]TRUNCATE TABLE ; ---------------------------------------------------------------------- Uncle Reg · 2014-06-07 04:50 SQLScript1 in the procedure below is a TSQLScript from the SQLdb package. When I click the button, I get an error message that says: near "truncate": syntax error procedure TForm1.TestBtnClick(Sender: TObject); begin SQLScript1.Script.Text:='truncate albums; truncate songs'; SQLScript1.Execute; end; ---------------------------------------------------------------------- taazz · 2014-06-07 04:54 http://www.tutorialspoint.com/sqlite/sqlite_truncate_table.htm ---------------------------------------------------------------------- BigChimp · 2014-06-07 06:52 Yes, taazz, seems not everybody supports SQL2008 (if I can believe Wikipedia on that). AFAIR, Firebird doesn't support it either. ---------------------------------------------------------------------- BigChimp · 2014-06-07 07:11 So what taazz suggested: Code: [Select]procedure TForm1.TestBtnClick(Sender: TObject); begin SQLScript1.Script.Text:='delete from albums; delete from songs;'; SQLScript1.Execute; end; (code untested, don't know if it compiles) or you could use a regular sqlquery and .execsql it - once for each delete command ---------------------------------------------------------------------- Leledumbo · 2014-06-07 07:29 Quote from: taazz on June 07, 2014, 06:54:07 amhttp://www.tutorialspoint.com/sqlite/sqlite_truncate_table.htm Good finding. I never thought TRUNCATE TABLE is unsupported in SQLite3. ---------------------------------------------------------------------- Uncle Reg · 2014-06-08 03:41 Quote from: BigChimp on June 07, 2014, 09:11:18 amSo what taazz suggested: Code: [Select]procedure TForm1.TestBtnClick(Sender: TObject); begin SQLScript1.Script.Text:='delete from albums; delete from songs;'; SQLScript1.Execute; end; (code untested, don't know if it compiles) or you could use a regular sqlquery and .execsql it - once for each delete command Yes, I had already done that after reading that tutorial website, and it did compile OK, but it didn't do anything until I added another line: SQLScript1.Script.Text:='delete from albums; delete from songs;'; SQLScript1.Execute; SQLTransaction1.Commit; After I ran that, the tables appeared to be empty when I examined the database with SQLite Studio, but the file was still exactly the same size (approx 3.5Mb) as it was before all the records were deleted. Then I remembered reading on that tutorial website about using the vacuum command after using the delete from table command to clear unused space, so I changed it to this: SQLScript1.Script.Text:='delete from albums; delete from songs; vacuum;'; <--- added the vacuum command to the script SQLScript1.Execute; SQLTransaction1.Commit; That compiled OK too, but when I ran it, I got an error message: Cannot VACUUM from within a transaction. When I googled that error message, I got a hit from an earlier post on this forum that listed a solution to that error, so I added it: SQLScript1.Script.Text:='delete from albums; delete from songs;'; <--- removed the vacuum command from the script SQLScript1.Execute; SQLTransaction1.Commit; albumsConnection.ExecuteDirect('End Transaction'); albumsConnection.ExecuteDirect('vacuum'); <--- and added it back here albumsConnection.ExecuteDirect('Begin Transaction'); That worked perfectly. The file is now only 3072 bytes, the same size I get if I use SQLite Studio to erase all the data and vacuum the database. So now it's on to figuring out how to import the updated data. From what I've looked at, it looks like the TSqlite3Dataset component might be easier to use. It looks like I can just use 2 datasets (and 2 datasources) to connect directly to the tables, then parse the updated data and use Append and Post to add new records to the tables. Is it possible to mix TSqlite3Dataset and the SQLdb components that I used above? Would I have to completely disable the SQLdb components before connecting the TSqlite3Datasets, or could I connect them to the tables at design time and do the emptying procedure listed above using the SQLdb components while the tables were connected to the TSqlite3Datasets? Or even better, could I do the above table emptying procedure also with TSqlite3Datasets? It does have a SQL property, and a ExecSQL procedure, and it looks like it might even have the ExecuteDirect procedure. That would eliminate having to use the SQLdb stuff at all. Thanks to everybody for all the help so far, and I would very much appreciate any opinions that anybody might have on the above questions. ---------------------------------------------------------------------- taazz · 2014-06-08 04:40 Use either TSQLiteDataset or sqlDB not both at the same to avoid collisions. SQLite is a single access library once a file is opened can not be opened again before the first one is closed. Those two libraries know nothing of each other and they will stable one upon the other. ---------------------------------------------------------------------- BigChimp · 2014-06-08 08:09 Apart from taazz' remark Don't know about sqlite, but all dbs I know will reuse any reserved but unused space. Vacuuming/compressing/"cleaning" will remove that reserved space, yes, but if you are going to import new data, your db will expand again, making a vacuum after truncate/before import a bit useless. Perhaps vacuum after the insertion of new data is done could be more useful... if it is needed at all? ---------------------------------------------------------------------- Caravelle · 2014-06-08 20:18 QuoteSQLite is a single access library once a file is opened can not be opened again before the first one is closed. Can you explain what you mean in more detail please ? I have an sqlite database open 24/7 collecting data from a radio receiver using a commercial program. While this is going on I can read and write to active tables in the database using several additional programs, my own and general sqlite utilities like SqliteExpert. I can even do this across a network. They all access the database at the same time. It's my understanding that sqlite is not a single access library except when it is actually being written to, and then access is managed by transactions. See http://www.sqlite.org/faq.html#q5. I have several Delphi programs which take advantage of this (using Disqlite3). They have been doing this for over 5 years, day in, day out. The odd thing is that I can't seem to get the Lazarus sqlite components to work the same way. I get endless "database is locked" errors and I want to get to the bottom of this. I really wish Disqlite3 could be ported to Lazarus. I don't want to get involved in an argument, I'm just trying to convert my Delphi programs and need a better understanding of the issues, it seems. Thanks Caravelle ---------------------------------------------------------------------- LacaK · 2014-06-09 05:05 In case of sqlDB, when you commit read or write operation(s) then locks are released. So other application can access same file in same time as far as they do not access them in exactly one time. I do not know TSQLite3Dataset, so I can not say how it handles transactions. ---------------------------------------------------------------------- mse · 2014-06-09 05:53 Please don't forget to set .packetrecords to -1. ---------------------------------------------------------------------- taazz · 2014-06-09 06:53 Quote from: Caravelle on June 08, 2014, 10:18:30 pmQuoteSQLite is a single access library once a file is opened can not be opened again before the first one is closed. Can you explain what you mean in more detail please ? A single access database is one that locks the database when a client has a transactions or a connection open. In most embedded databases that is the connection ee which client opened the file first, in SQLite it seems that it is the transaction. Any way the idea behind a single access database is that you open you read the data in to memory or write the data to the database you close so other processes can have access. I do not know when the sqlite allow multiple process to read from the same database the last time I checked I got the impression that they did not allow multiple processes to open the same file. ---------------------------------------------------------------------- Caravelle · 2014-06-09 15:56 Taazz Thank you for that explanation. As the official sqlite site says at the url I quoted: QuoteSQLite allows multiple processes to have the database file open at once, and for multiple processes to read the database at once. When any process wants to write, it must lock the entire database file for the duration of its update. But that normally only takes a few milliseconds. Other processes just wait on the writer to finish then continue about their business. Other embedded SQL database engines typically only allow a single process to connect to the database at once. It has been this way at least since I started using sqlite3 in 2006. Maybe sqlite 2 was different? Multiple programs can read data from the database simultaneously without using transactions. Writing requires a lock, and if you try to read during a period of writing you get a BUSY response - the answer is to detect this response and try again after a brief delay, as many times as it takes. Components used to access sqlite need simple ways to detect and read sqlite's responses and act accordingly and reading should not require a transaction which locks up the database so there should be a simple transaction-less facility for SELECT statements. I'm sorry if this is bad news but it seems fundamental to the way sqlite3 works. I am no expert, just an amateur who writes programs for specific personal tasks but I hope this helps in some way. I keep reading in various messages here that sqlite is a single-access system but all the while this is accepted as true the Lazarus sqlite components will never be developed to take advantage of sqlite's special features - and work well with existing multi-access systems like the one I use, which is at the heart of the devices produced by http://www.kinetic-avionics.co.uk/. These feed data into the database while at the same time the tables can be edited while being displayed on screen as both text and as a virtual radar screen. 3rd party programs also interface with it - all at the same time. Caravelle Caravelle ---------------------------------------------------------------------- BigChimp · 2014-06-09 16:14 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?!? ---------------------------------------------------------------------- Caravelle · 2014-06-09 17:02 QuoteSo 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 · 2014-06-09 17:13 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. ---------------------------------------------------------------------- Caravelle · 2014-06-09 17:27 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 ---------------------------------------------------------------------- fpcoder · 2014-06-10 01:24 @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=f iles ---------------------------------------------------------------------- mse · 2014-06-10 06:10 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 · 2014-06-10 14:47 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 · 2014-06-10 18:21 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.