Page 1 of 1

Refresh LO v6 database after conversion to Firebird

PostPosted: Tue Oct 08, 2019 5:34 pm
by ThisWas
My one-table database with unique keys has two rows out of sequence.
I've got a unique compound key: CatalogNum = integer and CatalogSfx = Varchar, and the catalog suffix is almost always blank.
My users enter the catalog numbers manually; I chose not to generate numbers for them with auto-increment.
One of the users pointed out to me that we now have records with catalog numbers:
4215, 4216, 4217, 4227, 4228, 4218, 4219, etc.
To our knowledge, only 4227 and 4228 are out of sequence.
I'd like to export or copy all 4,500 rows from LibreOffice Base, clear the catalog file, and reload it from my copy, to see if that resequences the records.
What's the easiest way to do this?

I created a Catalog database for our Historical Society three years ago.
I used an internal database, which was HSQLDB at the time.
I know that experts on this forum prefer an external database, but the internal database works reasonably well for our purposes.
We take care to update it on one PC only (a specific Windows 10 notebook), and we regularly e-mail the odb file to a few other PCs - it's currently a small 1.5MB file.
This provides us extra copies for reference use only, plus offsite backup.

I upgraded to LibreOffice 6.2 (now at 6.3.2), and migrated the HSQLDB database to Firebird, which seems faster for catalog searches.
Subsequently we had a problem where one user lost an hour's worth of work, specifically new records which she was entering.
We looked at the way she exited LibreOffice, which was different from the way the primary user exits LibreOffice, and we guessed that was our problem.
I don't remember the details (she wasn't getting the "save" prompt, I think) but now that she exits properly we haven't lost new data.
I mention this only because the out-of-sequence records may have been entered by Primary User around the same time that Unlucky User was having trouble.
When I "View Data Source as a Table" I can clearly see the out-of-sequence rows.

Anyway, can someone suggest an export/import process or file copy or SQL statement that will reorganize our Catalog file? Thanks!

Re: Refresh LO v6 database after conversion to Firebird

PostPosted: Tue Oct 08, 2019 6:36 pm
by F3K Total
Try on a copy of your .odb-file via Tools/SQL...
Code: Select all   Expand viewCollapse view
UPDATE "TABLENAME"  SET "CatalogNum" = 4220 WHERE "CatalogNum" = 4227;
UPDATE "TABLENAME"  SET "CatalogNum" = 4221 WHERE "CatalogNum" = 4228

adjust TABLENAME and Catalog Numbers to your needs.

Re: Refresh LO v6 database after conversion to Firebird

PostPosted: Tue Oct 08, 2019 6:56 pm
by ThisWas
Thank you for spelling out the process for me!
It's not a bad workaround... to change the columns to "Last Used Catalog Number" + 1 and "Last Used Catalog Number" + 2.
However, we'd have to change the catalog numbers inked on the physical items. Which is doable.

Twenty years ago when I used DB2/400 I could Display File Description to check that a file was defined the way I thought it should be, with respect to keys etc.
I could Reorganize a file.
I could Copy one file to another with field (column) mapping.
Obviously I need to learn more about SQL, to update my skills from the 19th century to the 20th.