How to purge records in Base (using HSQL 1.8)

English
, ,
#1

BACKGROUND:
I have a law office management database that I developed years ago using OpenOffice.org Base, which at the time used with HSQL 1.8 as the database backend. I used the “embedded” stand-alone file for a while, but as I added staff and needed more than one user to be on at a time, I switched it to a server-client model . . . which has worked great. In fact, I am maintaining the database to distribute to other small law offices.

The only problem is that there is a bug in HSQLDB: if you add new tables in the “served” version of the database, they will not auto increment; new tables in the “standalone” embedded database files autoincrement just fine. This has never been a big deal; what I would do is rename the embedded file to “database.zip”, replace the database data files with their counterparts from the served database, rename the database back to “database.odb”, make the updates, and then reverse the process. Then–because the embedded template database now contains confidential client information–I would purge the embedded database tables, resetting them to have no records. Works like a charm.

THE PROBLEM:
I just did all of this except the last step . . . and now I can’t remember what command I use to purge the tables! I spent hours doing a google search for the command to no avail. It’s a really simple thing to do, but for the life of me, I just can’t figure it out.

Anyone know?

#2

Hello,

Not exactly sure what you want.

Remove all records from within a table:

DELETE FROM "your_table_name"

or completely remove the table:

DROP TABLE "your_table_name"

Also, will check later but do not recall a problem with HSQLDB server in regard to auto incrementing.

You have not specified OS, specific LO version or HSQLDB version you are using.

Delete all data in data base?
#3

Sorry. Forgot to put the additional info. It’s Windows 10, and I use both LibreOffice 7.x and Apache OpenOffice 4.1.8 (I run the DB in AOO so that if it crashes, it doesn’t take what I had open in LibO with it.) As I said in my description, it’s HSQLDB 1.8.

I seem to recall that the DELETE FROM command still leaves the data in the file somewhere, and there’s another command you have to use to purge it. That’s the command I’m at a loss to find.

#4

@steveneshelton wrote:

I seem to recall that the DELETE FROM command still leaves the data in the file somewhere…

Not to my recollection unless you are in some manner creating a backup of the .odb. However, maybe you are thinking of this command:

DROP SCHEMA PUBLIC CASCADE

which will drop the entire structure including the data…

Also, sorry for not clarifying, the HSQLDB version I was referring to was for the server so as to reseach the auto incrementing. Did not think this would also be v1.8 as there are many better versions available. v1.8 is very old.

#5

Yeah, 1.8 is super old. I tried to update it a couple of times years ago, and it broke it every time. So, I’m sticking with 1.8 for now. :slight_smile:

What I want to do is keep the structure but wipe all the data, and have the tables start renumbering at 0.

#6

Went back to an old OS where I tested HSQLDB server. Had v2.x.x working without a problem. Auto increment works in there. Unfortunately, when attempting to switch to v1.8.x had problems as it appears to have differences in the setup. Have decided not to keep digging into as it is way too old.

Sorry could not help further. Again, don’t know of any other commands you seem to be looking for.

#7

Appreciate the insights and effort, anyway. I found out how to delete the data and reset the table keys. I’m hoping that I am remembering incorrectly about the old data still being in the document somewhere. I should be able to tell from the file size after running the purge script, I suppose.

#8

I solved this. I think. Assuming that deleted date is not still floating around in the database somewhere.

To purge the data from the tables and then reset them to start counting at “1”, I use this pair of commands:

DELETE FROM “TableName” WHERE “RecordID” > 0

ALTER TABLE “TableName” ALTER COLUMN “RecordID” RESTART WITH 1

To do the whole database, just duplicate this for all of the tables. (Saving it as an SQL script for future use.)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.