(Solved) Cannot open Form - "Document frm_xxx could not be opened" Error Message Box
1. My (embedded) database has over 10,000 images (stored externally of course). How are these handled in the migration to a split database?
I presume your database has a column that stores relative file-path information to locate your images. After you ‘split your database, you will end up with a folder containing your .data. script .properties etc files (i.e. your split DB ‘suite’ of files) and probably also your NEW .odb file (which contains all your report, queries, forms but NOT your tables.) This .obt file can actually go anywhere, but I usually recommend keeping it together in the same folder as the rest of the ‘suite’. So as long as you maintain the location of your image folder relative to your .odb file, everything should work as before. (Most probably you would keep your image folder inside your ‘suite’ folder and because it contains everything it’s ‘portable’ and to backup your data you can just copy/duplicate your entire ‘suite’ folder.)
2. Should I be concerned about doing this split and then find out that Base has moved to Firebird?
Well, I imagine there will be a well-defined set of steps needed to translate existing ‘embedded’ or ‘split’ HSQLDB-based DBs over to Firebird. Maybe the devs will build in some kind of automated process to do this. I don’t know what the plans are really, but in my opinion I would just proceed to split your DB so you have a more stable, less corruption-prone system (and you also might be able to retrieve your ‘lost’ records in the process.)
Make sure you keep working BACKUPS of your original embedded database thoughout!
hsqldb.jar is distinct from your HSQL database files. hsqldb.jar is the database-engine, available as single file-download from the provided link. Your operating system (OS) may identify the file as a zip archive. That’s because a jar file is a zip-archive. But do not extract this file. Simply place it in your split database folder.
On the other hand, your HSQL database files must be extracted from your legacy .odb file into the split database folder. These files must be renamed with a common prefix such as mydb.script, mydb.data, mydb.properties, and if present mydb.backup.
If you’ve followed steps A thru E of the tutorial, then you should get a prompt to allow macros upon opening mydb_wizard.odb in Base. If you can otherwise open and access tables in an embedded HSQL database (thereby indicating proper Java JRE support), then any error upon opening mydb_wizard.odb located within your split database folder indicates a missed step.
You can use any consistent prefix such as db.script, db.data, db.properties, and if present db.backup. And you can rename mydb_wizard.odb while maintaining the .odb extension such as my_database.odb. You can also rename or move the folder as desired.
| Attachment | Size |
|---|---|
| Create New Database Window.png | 18.29 KB |
| Database Folder.png | 30.98 KB |
| SplitDBStampCollection Folder.png | 52.02 KB |
Create a new folder.
Rename the contents of the current ‘database’ subfolder using a different prefix like: db.data, db.script, etc. This is to differentiate from the new database (mydb.* ) that you created with the wizard. Move these renamed (db.* ) files to the new folder you created above.
Your newly created split database folder should contain only (no subfolders):
StampCollectionWorking.odb
mydb_wizard.odb
hsqldb.jar
db.data
db.script
db.properties
db.backup (if present)
db.log (if present)
You’ll eventually be able to eliminate StampCollectionWorking.odb from the folder and then rename mydb_wizard.odb to StampCollection.odb as desired. But wait until you’ve finished moving the forms, queries, reports and macros over to mydb_wizard.odb.
As frofa mentioned, you’re first priority is splitting your existing ‘embedded database’ into a split-database configuration for data durability. The default database in Base is largely irrelevant. The devs have chosen to eliminate Java dependencies from the default function of LibreOffice, but they are committed to maintaining Java Database Connectivity (JDBC). Since a split HSQL database requires a JDBC connection, this becomes the most important aspect of Base relative to your project going forward.
No reason to adopt Firebird:
There’s no reason to migrate your data to Firebird, although LibreOffice is already showing a certain disdain for Java Virtual Machine (JRE) support. That’s not to say they’re dropping support. Like I mentioned above, they’re quite committed to JDBC support as a distinct feature for the life of the product. But without offering JRE installation during LibreOffice installation, the task becomes more difficult for the end-user, perhaps particularly due to the associated LibreOffice setup process. Most computers today have a JRE installed, but LibreOffice no longer bundles or installs a proper JRE for the end-user as has been the practice. So it’s up to the user to install a proper 32-bit JRE as applicable (Windows and Mac) while registering that installation in Tools > Options > Advanced > Use a Java Runtime Environment (checkbox) > select an installed version (radio button selection).
Firebird database management:
While Firebird is a fine database in professional circles, it’s not well-suited for an end-user product like Base. Actually, as Base is tuned for Firebird over a number of years the end-user experience will slowly improve. But relative to a modern database engine like HSQLDB 2.x, or even the bundled HSQLDB 1.8, you’ll find that Firebird is much harder to use out-of-the-box. Firebird bears the marks of a bygone era in database history. Only the most advanced database management software is able to mask the ensuing difficulties otherwise laid upon the end-user. Such ‘difficulty’ is a boon for job-security among database administrators; after all, Firebird is a “full-scale” database product capable of supporting online banking, etc. So the scalability of a Firebird project is virtually unlimited.
That’s great news, but a typical Base user will never scale a project beyond a small office with under 100 simultaneous users, where HSQLDB shines (not to mention PostgreSQL and MySQL/MariaDB as full-scale alternatives). Unfortunately, Base users will find themselves wrestling with outdated DDL conventions to use Firebird. That is, unless we see a dramatic change in the philosophy behind the rudimentary (buggy and inadequate) database-management features in Base. The modern design of HSQLDB tends to masks these inadequacies in Base to a degree. For example, to add an auto-incrementing column (such as an AutoValue primary key) to a modern database is a simple matter of issuing an SQL (DDL) command. Likewise, that’s easy to automate through a checkbox in a GUI. In Firebird, that task involves creating a “generator” on the column, followed by writing a “trigger” (coding). You’ll find some examples on the internet, but you will need to modify the syntax for each auto-increment column as appropriate. One glance at this process should send any Base user running back to modern alternatives. Since HSQLDB is arguably the most modern, SQL-standard, easiest to use, full-featured, cross-platform, open source RDBMS available — and seamless with Base in single-user mode — I think we know where the average user will turn.
So all things considered, Java (JRE) installation is nothing compared to the difficulties users will encounter with a relic like Firebird.
That’s just my opinion, and your mileage may vary depending on the degree to which the Base developers smooth the rough edges of Firebird for the end-user. But that may take years as database management (schema development) has always been the Achilles-heel of Base.
Embedded Firebird DB limitations and data-corruption risks:
But one thing is certain, you will not be able to utilize a more advanced database manager on an embedded Firebird/HSQL database due to the proprietary Base .odb file design current lack of support for the odb file-type. And your data will remain susceptible to corruption by Base in the ‘embedded database’ packaging, due to the current implementation. So you’ll be forced to split your Firebird databases as well for data-reliability and alternative-management purposes.
In other words, the current default database option featuring single-file portability must be re-designed before it can be deemed reliable. This redesign is a simple matter of adopting a portable, split-database architecture for normal use, while offering a “Pack & Go” menu item to generate a copy of the split-database folder for single-file distribution purposes (by exception). An auto-extracting feature will round-out the redesign.
As you may know, we (the user-community) have already implemented a proof-of-concept in the form of Base templates. These “templates” require macro-support enabled in LibreOffice, but they allow us to bypass the default database engine (HSQLDB/Firebird) and the associated database creation wizards, while generating a portable, split HSQL 2.3.x database in seconds. As mentioned in the embedded database migration tutorial, a split-database folder may be moved, renamed or zipped for single-file distribution, as necessary.
So I predict you’ll have a much better user-experience with HSQLDB (perhaps particularly HSQLDB 2.x) going forward than with Firebird.
Perhaps add your split database specs to your signature (split HSQL 1.8 database).
I’m so thankful for frofa, oweng and many others in these forums.
Upgrading is up to you. I only caution folks to take it one step at a time during the embedded-to-split migration process, so we can isolate any issues. Date math, custom aggregates, BLOB/CLOB support, stored procedures and triggers come to mind as reasons to consider the upgrade. Make a backup of your working split database folder and give it a go. A link to the necessary HSQLDB 2.3.x file (hsqldb.jar and perhaps sqltool.jar) is included in the embedded database migration tutorial. See the NOTE under ‘Option 1: step 1.’ Make sure each of your fields have a defined length [ie VARCHAR (100) as opposed to VARCHAR alone] and the upgrade process should be automatic and trouble-free. Actually, you can run all of your databases (embedded, split, and upgraded) side-by-side to compare data and function as needed. That’s an exclusive feature available only when using macro-enhanced Base templates in support of portable split-database folders such as you’ve setup.
One quick question; where do I find confirmation of what HSQLDB version it is within Base?
Also, as an encouragement to all Base users; since I went to the split database, it has behaved flawlessly (I am now up to around 15,000 images). I particularly like the portability issue – when I travel, I just copy my split database folder to an old laptop.
Let’s just say I am not a naturally gifted software guru; so if I can build a very useful database I would say just about anyone could.
One quick question; where do I find confirmation of what HSQLDB version it is within Base?
Try this SQL code (use ‘Create query in SQL View’ and then paste-in the code below and Save as ‘Get HSQL version’ or whatever). NOTE: This code works only in HSQLDB versions 2.x
Select TOP 1 DATABASE_VERSION() as "HSQL Version"
From INFORMATION_SCHEMA.SYSTEM_TABLES;
It should work….
Select DISTINCT DATABASE_VERSION() as "HSQL Version" From INFORMATION_SCHEMA.SYSTEM_TABLES
which returns 2.3.2 as my HSQL version.
Thanks again
but need to issue sql
ALTER TABLE "Tabelle" ALTER COLUMN "ID" INT GENERATED BY DEFAULT AS
IDENTITY(START WITH 0)
Is this what other people have experienced?
Are you familiar with:
https://forum.openoffice.org/en/forum/viewtopic.php?p=271700#p271700
In short, HSQLDB 2.3.x includes a workaround for this Base bug. The bug is not apparent when running HSQLDB 1.8.0.10, but it becomes a problem when Base is paired with HSQLDB 2.x. If you’re using a Base template, then these settings are preset within the provided Base document (.odb). Otherwise, when using HSQLDB 2.3.x with Base, it is helpful to add these settings manually to your Base front-end (.odb) as outlined in the above link.
See THIS THREAD for some tips – especially DACM’s comments at the end – i.e. you might trying ‘splitting’ your ‘embedded’ files to see if you can get the tables to show up. You might use the information provided in DACM’s tutorial. If this procedure works, at least would would have a much more stable ‘split’ database at the end.
Also see here for some rather technical discussion about one user’s efforts to rescue a file. Luckily you have backups.