[Solved] Base alternative

[rwwood]

I have read in a number of places that Base is not the best program to use for a "real" relational database. I'd like to hear comments as well as alternatives to be used on a stand-alone linux box.

Thanks.

[FJCC]

I think that what is to be avoided in "real" situations is using the HSQLDB database engine that is packaged with OpenOffice in the embedded mode. That is when the HSQLDB files are zipped with the Base file. This mode is prone to data loss. Using HSQLDB in server mode, so that it runs independently of the Base file is a robust arrangement. There are detailed instructions for how to set this up.

[JJJoseph]

Where do we find the "detailed instructions"?

[RoryOF]

[Tutorial] Splitting an "embedded HSQL database"

[rwwood]

So, in reading through this explanation of how to split the database, I get the impression that you have to have the tables, queries, forms, etc all set up before splitting, is that right? Once it's split, can you no longer add tables, etc. to the DB?

Thanks.

[Villeroy]

Whatever database you are using, you use dedicated tools to design your database structure (tables, relations, indices, permissions) and THEN you connect a Base document to that structure IF and only if you find it useful to use that database in the context of this office suite.

The one and only type of relational database Base can create from scratch is a Base document with an embedded HSQLDB of version 1.8 which is a caricature of a database, nevertheless useful for demos and educational purpose. This "Base database" (it is still HSQLDB in the first place) can be converted into something more useful and stable.

[DACM]

So, in reading through this explanation of how to split the database, I get the impression that you have to have the tables, queries, forms, etc all set up before splitting, is that right?

No. You can convert an existing embedded database, or you can begin with a new split-database template...

• [Wizard] Portable 'split' HSQL database template

Once it's split, can you no longer add tables, etc. to the DB?

No. You can add tables and fields using the Base Table Designer GUI in any case. But if you need to modify an existing field attribute (table structures such as field length, data type, etc.) then you'll need to consider a workaround using the Base Table Designer (such as replacing the existing field with a new field) -or- use SQL (Tools > SQL...console)

[rwwood]

I tried both of the approaches you gave above for setting up the DB, but when I ran the wizard, it hung, even with macro security on low. Any idea what's gone wrong? I'm running it on Kubuntu.

Thanks for your help.

[DACM]

... I ran the wizard, it hung, ... Any idea what's gone wrong? I'm running it on Kubuntu.

It might be your Java installation...? Can you create new 'embedded database' and tables using the default Base wizard? If so, then Java (JRE) is not the issue.

[rwwood]

No problem creating either DB or tables with the wizard.

[DACM]

No problem creating either DB or tables with the [default Base] wizard.

Hmm...I'm a little stumped. So the default embedded database configuration works fine, but the template 'hangs' as if the macro-automation isn't supported or running despite setting 'macro security' to 'medium' or 'low.'

Are you sure you only have one copy of *Office installed?

Is it a full installation with all *Office components (Writer, Calc, etc.)?

Are you sure the template downloaded successfully/completely?

What specifically do you mean by 'hung'...? Can you post the details of the error or a screen shot?

Do you see a 'driver' or 'database' sub-folder?

[Steve R.]

I have read in a number of places that Base is not the best program to use for a "real" relational database. I'd like to hear comments as well as alternatives to be used on a stand-alone linux box.

As to your question of Base alternatives. I'm currently contemplating the use of a browser (Firefox) as the front-end to the MySQL back-end. I assume that HSQLDB and Firebird can also be used as a back-end with a browser. I am currently taking some on-line courses in PHP, HTML, and CSS. At this time, I have a very primitive configuration, which demonstrates that it can be done. Whether this ultimately proves superior to Base, I do not yet know. Even if does not work-out, it will still be a valuable learning experience.

Additionally, database implementation should always (of course there are exemptions) be split between a front-end and a back-end. The primary reason is that it facilitates the ability to modify the front-end without affecting the back-end.

[rwwood]

Attached is a screenshot of the folder where I have the extracted DB and the "split" script DB. By "hang" I mean that when I try to open the Split_HSQLDB_2.3.2_Wizard_v3d.odb, I get a screen showing a DB, but clicking on any of the table, form, etc. icons does nothing. If I try to close that DB, I get an error message stating that LibreOffice has stopped responding. I've waited a considerable amount of time to see if there's any response before trying to close the DB, but the result is always the same. I've also attached the DB itself if you want to try to run it on your machine.

[rwwood]

I have read in a number of places that Base is not the best program to use for a "real" relational database. I'd like to hear comments as well as alternatives to be used on a stand-alone linux box.

I don't fully understand this. What is meant by frontend and backend?

[RoryOF]

The Backend is the actual database engine - MySQL, HSQLDB or whatever, the Frontend is Base itself and the forms and tables.

[Villeroy]

First of all, I don't understand the instabilities you are confronted with. Some time ago I used a fairly complex embedded HSQLDB without any problems during 300 working days for one hour a day. Luckily the office never crashed so I did not need my daily backups. It just worked.

All kinds of "weird problems" and instabilities with OpenOffice may be cured by resetting the OpenOffice user profile. Save your work and close the office completely (command line: killall soffice). Then rename the hidden directory ~/.openoffice/4/user/ to ~/.openoffice/4/user.old/ and restart the office. It will behave as if it were started for the first time and in most cases the weirdness is gone. If this does not help you, can shutdown the office again, delete the new profile directory and rename the "user.old" directory back to "user" in order to get back all your settings, templates, macros and stuff. If the problem has been cured, you may import most of the stuff from the backup directory into the clean one.

########################################################################################

I saved your database in ~/hsql/ which is my directory for testing such files, extracted, renamed and moved the contained backend files:

Archive --> File System
--------------------------------------
database/script --> backends/Learn.script
database/properties --> backends/Learn.properties
database/data --> backends/Learn.data

"backends" is just a subdirectory where I put all the files for all the database backends. It could be any other directory with read-write access. Some people keep one directory per database but you can all databases into one directory because they are distinguished by their names, "Learn" in this case.

Then I started my FreeHSQLDB macro:

Start the FreeHSQLDB macro and point it to the extracted *.script file in the directory which also has the corresponding *.data and *.properties. We pick "Learn.script".

The dialog shows the document location (always good to know) and the current connection URL "sdbc:embedded:hsqldb". Base is not a database program. With this special URL the embedded HSQLDB is run by a Java program hsqldb.jar in the office installation after extraction.
Button [Connect HSQL backend...] pops up a file picker dialog where I pick our backends/Learn.script file.
Glitch: Sometimes my macro complains about the database document not being a database document. In this case restart the office suite, reopen the same document and everything will be fine. I don't know how to fix it.

Here you see the resulting connection URL "jdbc:hsqldb:file:///..." which replaced "sdbc:embedded:hsqldb"
Button [Set] pops up a file picker dialog where I pick the Java program which actually runs the database. I keep my latest copy of HSQL with documentation, source code and scripting expamples in /usr/local/lib/hsqldb/ and the only file that is needed to actually run databases is lib/hsqldb.jar. sqltool.jar is a frontend for the command line.

Specify the database driver hsqldb.jar which is the Java program running your backend database.

After closing the dialog, I save the modified document, go to the tables section and open an arbitrary table.
Notice the connection URL in the status bar of the document. It indicates the database we are connected to.

The database document is connected to the extracted "Learn" database now.

[rwwood]

Apparently LibreOffice is different. There is no user file anywhere on the system <sigh>

[Villeroy]

Sorry, Ubuntu puts all(?) (most?) configuration in ~/.config and the configuration files for the LO4 shipped with Ubuntu are in ~/.config/libreoffice/4/user

[DACM]

Attached is a screenshot of the folder where I have the extracted DB...

Okay! The screenshot reveals everything we needed to know.

It appears that you're actually converting an 'embedded database' to a 'split database' in accordance with: [Tutorial] Splitting an "embedded HSQL database". The screenshot reveals that you successfully extracted the contents of the 'database' subfolder from your original .odb file (LearnToTradeTheMarket.odb). And you've successfully renamed those database files with a consistent prefix (LearnToTradeTheMarket.*).

Very nice! So far so good.

Now, I think you may have switched tutorials mid-stream because I don't see mydb_wizard.odb or hsqldb.jar (internally-licensed HSQLDB 1.8.0.10 engine/driver) in your split-database folder. But I do see Split_HSQLDB_2.3.2_Wizard_v3d.odb from a different tutorial -- which is a template designed to create a new split-database using HSQLDB 2.3.2. That's not what you want in this case, since you're actually converting an existing 'embedded database.'

So I would suggest you go back to the first tutorial and complete the steps, including downloading mydb_wizard.odb and hsqldb.jar (internally-licensed HSQLDB 1.8.0.10 engine/driver) into your split-database folder. But you'll also need to move the files from the 'database' subfolder to the main folder. So you should have a split-database folder (LearnToTradeTheMarket) with the following files (no subfolders at this point in the process):

• LearnToTradeTheMarket.script
  LearnToTradeTheMarket.properties
  LearnToTradeTheMarket.data
  LearnToTradeTheMarket.backup
  mydb_wizard.odb
  hsqldb.jar
  LearnToTradeTheMarket.odb (your original .odb file with embedded database)

Open on mydb_wizard.odb in Base with macros enabled and click on the Tables icon.

[rwwood]

Open on mydb_wizard.odb in Base with macros enabled and click on the Tables icon.

OK. I set up the folder the way you stated, and clicked on the myb_wizard.odb, and the only thing that happened is that it opened a web page and downloaded a new copy of that same file. What the heck is going on?

Thanks.

[DACM]

...clicked on the myb_wizard.odb, and the only thing that happened is that it opened a web page and downloaded a new copy of that same file. What the heck is going on?

That almost sounds like you inadvertently right-clicked on mydb_wizard.odb and got a context menu or action, instead of left-clicking on mydb_wizard.odb to open it in Base. Otherwise, that's a severe stability issue...perhaps affecting your particular installation of LibreOffice (corrupted *Office API?) or Kubuntu (corrupted .odb file-type association?). As a minimum, shutdown your computer completely and restart to try again. If that doesn't work, then try moving that final split-database folder to another computer and running myb_wizard.odb with macros enabled.

[rwwood]

None of the suggestions above seemed to work, but this one did.

Thanks for all your help.

[DACM]

Oh cool! I'm glad you got your split-database working!!!

Villeroy's extension method uses the same macro code to derive the database connection settings as the portable methods, so I'm not sure why it didn't work for you. That shouldn't be the case unless there's some difference in the *Office API on that particular distribution of LibreOffice on Kubuntu. Really weird.

[rwwood]

Actually, I uninstalled LibreOffice and installed OpenOffice, and still had problems with the install methods other than the one I mentioned that worked.