[Solved] Install HSQL-2.2.5

[capemayal1]

OS = Ubuntu 11.10 64-bit; 64-bit machine
OpenOffice Base 3.4

Okay, I'm going to try this once more, and I hope that forum members will allow my ignorance to prevail.

I really want to install HSQLDB-2.2.5

DACM's instructions are excellent for Windows 7, and I have been able to install on Windows 7 properly, with positive results.

However, OO on Ubuntu is still a impossibility to me.

So, if anyone out there can give me the instructions for the installation in OO 3.4, in Ubuntu 11.10, please, can you provide them?

I've searched the forums. I've googled the web, but everything, eventually points back to DACM's Quick Fix. I've tried adjusting paths, etc., to the install of OO in Ubuntu, but no luck.

I've downloaded the needed files, using the jar files from the links in DACM's Quick Fix. And, that's as far as I can go.

I'm interested in using File Mode.

Thank you in advance,
Al

[rudolfo]

Why do you want to use file mode? The only advantage that file mode gives you, simply sharing a single .odb document when you want to share your database with others is gone already if you have a newer HSQLDB version integrated. Either you use the advanced features of the new version, then others won't be able to work with your database file. Or you don't use them, but then there is nothing left that justifies the hassle that you have gone through to integrate HSQLDB 2.2.
I would go for stability and go with server mode.

[capemayal1]

Thank you for your reply.

What you're saying makes perfect sense.

I was only interested in file mode as I'm the only one that will be using it.

However, the instructions on how to do it are not sinking into my brain.

Perhaps I'm not, entirely, understanding the file structure.

Again, I'd appreciate any help.

Al

[Villeroy]

Edit: January 2013: I recommend HSQL version 2.2.8. There are several problems with Base and 2.2.9. October 2013: Everything fine with 2.3.x

File mode is easy to setup for the local machine, it is fast, provides most of the database features and it does not destroy your data just because some office component crashed.
I just tried with an embedded Base database and HSQLDB 2.2.5 reading some documentation and studying the files of my server databases.
1) JAVA+Linux+Base
I run the combination of Linux+Java+LibreOffice Base with a manually installed Java version 1.6.22 due to the well known performance problem. For anything else I use a system wide installation of a recent Java version.
2) Point the office to the right JAVA
In Tools>Options>Java I point the office to my special Java directory /usr/local/lib/java-6-sun-1.6.0.22/jre/
3) Point to the right HSQLDB engine
On the same options page I have added the archive /usr/local/lib/hsqldb-2.2.5/hsqldb/lib/hsqldb.jar
Now all hsqldb stuff refers to this manually installed hsqldb engine. I can not use the built-in hsqldb1.8 anymore. I open embedded databases with another office suite (OOo3.3). Both, the old Java version and HSQLDB don't need any installation. Just become root and extract the archives to /usr/local/ or some other apropriate location for manually installed local software. You may drop it to your home directory, but this place would be more vulnerable for hackers (if any).
4) Get some test data from an embedded database document
http://user.services.openoffice.org/en/ ... p?id=11663 is one of my embedded databases. I loaded the file into my zip tool and extracted the 4 files properties, script, backup, data in subdirectory "database" to directory /home/andreas/hsqldb-2.2.5/databases/FilterData/
After some failed experiments and comparison with my hsqldb server I found out that I need to add a prefix to the 4 file names so they read db.properties, db.script, db.backup, db.data where db is just an arbitrary name. Additionally I added a blank subdirectory db.tmp simply because it uses to be in my server databases.
5) Connect a Base document to the external database
According to the documentation all you need is a hsqldb:file:/path/dbname. Unfortunately we can not redirect an embedded HSQLDB as we can do with normal (I mean external) databases. So I created a new database connected via JDBC, driver class org.hsqldb.jdbcDriver, using the datasource URL jdbc:hsqldb:file:/home/andreas/hsqldb-2.2.5/databases/FilterData/db (the final db refers to the database name I used as file name prefix). I connect as user SA with no password since this is the user which used to be used for the same database when it was embedded.

Bingo. I can see the tables under a schema PUBLIC/PUBLIC. Don't ask me about those database schemas. I don't get it neither.
Finally I can copy over all queries, forms and reports from the embedded db to the free one. Ignore the error messages when copying a query. Somehow it tries to access the queries but there is no valid driver anymore for the embedded thing. But the copy and paste works anyway, the form works and the reports report.

[DACM]

...I created a new database connected via JDBC, driver class org.hsqldb.jdbcDriver, using the datasource URL jdbc:hsqldb:file:/home/andreas/hsqldb-2.2.5/databases/FilterData/db (the final db refers to the database name I used as file name prefix). I connect as user SA with no password...

...I can see the tables under a schema PUBLIC/PUBLIC.

hsqldb:file:/home/andreas/hsqldb-2.2.5/databases/FilterData/db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

/home/andreas/hsqldb-2.2.5/databases/FilterData/db is the full path to the database where 'db' is the actual database name; the path and a new database named 'db' would be created by HSQLDB if they don't already exist.

;default_schema=true will hide the schema structure just like the default embedded database setup

;hsqldb.default_table_type=cached will set the default for new tables to 'cached' tables just like the embedded database setup; this switch sets the new table default permanently within the script file for each new database created; an embedded database is already setup for cached tables so it will inherit this default upon migration and upgrade to HSQLDB 2.x.

;get_column_name=false is suggested by Sliderule to enhance backwards compatibility for legacy databases and associated SQL syntax.

[Villeroy]

Thank you very much. And ;shutdown=true removes the db.lck file when the connection is closed.

By the way, this problem with hsqldb 2 does not occur in file mode.

[capemayal1]

Thank you and Thank you.

I will implement your directions.

I'll let you know how it turns out.

Thanks again,
Al