Connect Base to External HSQLDB

users
#1

Hey all,

I have HSQLDB installed here:
/home/database/back/hsqldb/lib/hsqldb.jar

I want the Base front end here:
/home/database/recipe/recipe.odb

On the LibreOffice Database Wizard:
1. Select Database
  I select the radio button: [Connect to an existing database]
  I select [JDBC] in the section box and then click [Next>>]

2. Set up JDBC connection
  I fill in [data source URL]: jdbc:[../back/hsqldb/lib/hsqldb.jar]

  What do I put in the [JDBC driver class] box?

The HSQLDB documentation suggests that there is a JDBC driver in the
hsqldb.jar file but that doesn't work.

I assume that the HSQLDB package contains a driver.jar somewhere. What
should I put in for the JDBC driver class path?

#2

(some terms below freely translated from my FR environment)

-- data source (URL)
hsqldb:file:<path to the
.odb>;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

In <path to the .odb> above do not specify the .odb extension.
Sset the defaults above as you prefer; these work for me.

-- JDBC driver class
org.hsqldb.jdbcDriver

-- A question: did you set the class path and configuration for Java?
If not, go to Tools/Options, LibreOffice / Java.
Check that "Use a Java setup" is checked, then add the Java environment
setup on the PC.

Once this is set, click Class path
In the new dialog:
(1) Add an archive
Point to the hsqldb.jar on your PC (mine is C:\Program
Files\hsqldb-2.2.8.\lib)
(2) Add a file
Point to the above \lib subdir

You should be up and running.

HTH,

#3

Hi Mark, Jean-François,

Once this is set, click Class path
In the new dialog:
(1) Add an archive
Point to the hsqldb.jar on your PC (mine is C:\Program
Files\hsqldb-2.2.8.\lib)
(2) Add a file
Point to the above \lib subdir

Note that this part didn't work for me on Mac, it provokes an error in
LO's component loader or service manager (don't remember which now), or
at least, that was the case for 4.0 and 4.1.

Alex

#4

Hello Alex,

Note that this part didn't work for me on Mac, it provokes an error in
LO's component loader or service manager (don't remember which now), or
at least, that was the case for 4.0 and 4.1.

well... this *did* work fo me under WinXP and I never had a Mac to test :frowning:

Any Apple-maniac suggestion would be fine :slight_smile:

#5

Hi Jean-François, all :

Having just re-tested with Mac OSX Mavericks, LO 4.2.0.3 and hsqldb
2.2.8, I was able to get this to work :slight_smile:

Alex

#6

I've had a lot of distractions, Olympics and Income Tax return, to get
through so I'm working on this again.

On the "Set up a connection to a JDBC database" page I have:

Datasource URL
jdbc:[/home/database/back/hsqldb/lib/hsqldb.jar]

JDBC driver class
[org.hsqldb.jdbcDriver]

When I click on the [Test Class] button I get a dialog box that says the
JDBC driver was loaded successfully.

Jumped through the first hoop.

On the "Set up the user authentication" page I have:

User Name [mlapier] with the Password required check box selected.

When I click the [Test Connection] button I get a dialog box asking for
my password. I type in my password and click the [OK] button.

I get a message box:
Connection Test
The connection could not be established.

Without the password check box I get the same result.

Does anyone see where I have gone astray?

#7

I just now noticed that you have "hsqldb:file:<path to the.odb>" in the
Datasource URL area. How is a Base, i.e. .odb, file used as the data
source when the data is stored in a standalone HSQLDB database? The
data is not stored in an .odb file but rather is managed by the
standalone HSQLDB database.

Perhaps I misunderstand your posting?

#8

Hi Mark,

I just now noticed that you have "hsqldb:file:<path to the.odb>" in the
Datasource URL area. How is a Base, i.e. .odb, file used as the data
source when the data is stored in a standalone HSQLDB database? The
data is not stored in an .odb file but rather is managed by the
standalone HSQLDB database.

The ODB file uses a JDBC connector driver included in the hsqldb
package, i.e. it merely references the actual database and uses JDBC to
access it. Any and all forms, queries, reports, macros, etc, are stored
within the ODB file, even if the actual data from the hsqldb is stored
in a separate directory pointed to by the URL. Also stored in the
separate directory is the db initialisation/creation script file and any
data backup and user/pwd configuration.

This is what we mean by a "split" hsqldb database.

Alex

#9

Hey Alex,

So what you are saying is that I can create a LO Base DB that references
tables stored in the standalone HSQLDB database with the JDBC connector,
and use that .odb file as a back end to another LO Base DB which acts as
a front end? I've read, and been told on this mail list that LO Base
can't do that. If I could do that I wouldn't need the standalone
HSQLDB, just a plain .odb file with some tables in it.

I've been trying to get this set up for months with no success. I could
have done all this and more with Microsoft Access in just a few days.
This really shouldn't be this hard.

I think I need someone to hold my hand through the creation of just one
very simple LO Base DB with just two tables in it.

#10

I think your confusion lies in equating an .odb file with an Access
database. The .odb file never contains the data. It is a sort of registry
that points to wherever the data is. Thus, Base is the front end, a real
database is the back end, and the .odb file serves as the intermediary (to
oversimplify).

John

#11

Hi John,

I think your confusion lies in equating an .odb file with an Access
database. The .odb file never contains the data. It is a sort of registry
that points to wherever the data is. Thus, Base is the front end, a real
database is the back end, and the .odb file serves as the intermediary (to
oversimplify).

I agree with everything you have written, except that, by default, if
you use the wizard to create a "standard" (and I use that term very
loosely) ODB file, LO creates an ODB file which embeds its own
hsqldb-compatible data into the file and then uses the LO-shipped
hsqldb.jar to be the db engine. This is where it gets confusing for
people wanting to split out their data from their forms, queries,
reports, etc.

Alex

#12

Hi Mark,

So what you are saying is that I can create a LO Base DB that references
tables stored in the standalone HSQLDB database with the JDBC connector,
and use that .odb file as a back end to another LO Base DB which acts as
a front end? I've read, and been told on this mail list that LO Base
can't do that. If I could do that I wouldn't need the standalone
HSQLDB, just a plain .odb file with some tables in it.

No, you've got the wrong end of the stick, what others have said is correct.

I'll try and simplify :

The file extension ODB is in reality a zipped container. If you rename
the extension to zip (or just right mouse button click on the file) and
then open up the file with file-roller, ark, tar or whatever your
favorite archiver is, then you will see it contains a number of files
and folders. The actual contents vary according to how you have set up
your database connection.

If you are using the default, embedded hsqldb engine, then all of the
data from the database is stored within the ODB container :

Configurations2/
database/
forms/
META-INF/
reports/
content.xml
mimetype
settings.xml

The database folder contains the following files :
backup
data
properties
script

The forms folder contains any forms or subforms you have designed for
that database.

The reports folder contains xml files and Object folders defining any
reports you have designed for that database.

META-INF contains a file called manifest.xml. This is a required
metadata file in order to have a valid (as in, "ODF compliant") ODB
file, so that LO will know it is trying to open an Open Document Format
database file.

As far as I understand it, content.xml is an xml representation of the
content of the database, in other words, it references the db connection
parameters, the db engine type, the different objects to be found within
the rest of the ODB container, including any queries, views, forms, etc
- without this file, your ODB file is just a blank canvas.

As far as I understand it, settings.xml is the xml configuration file
which defines how the Base window looks when you first load the ODB file.

Configurations2 seems to contain spurious other configuration settings,
e.g if you customise the toolbar for your Base file.

If macros are present, these are also stored in a separate folder.

What you are trying to achieve is to not use the embedded hsqldb engine,
so in essence, your ODB container will not have a database folder
because the content.xml file will refer to an outside linked hsqldb
database.

To give you another example, my ODB file which references my Thunderbird
addressbook only contains the following :

META-INF/
content.xml
mimetype
settings.xml

Note that there is no database/ folder because the TB addressbook is an
outside file, and furthermore, read only.

My content.xml file contains (among others) the following xml string
which references the type of db connection I have setup :

<db:data-source><db:connection-data>
<db:connection-resource xlink:href="sdbc:address:thunderbird"/><db:login
db:is-password-required="false"/>
</db:connection-data>
<db:driver-settings db:system-driver-settings="" db:base-dn=""
db:parameter-name-substitution="false"/>
<db:application-connection-settings
db:is-table-name-length-limited="false"
db:append-table-alias-name="false" db:max-row-count="100">

All of this is handled via the db creation wizard code, so usually there
is no need to fiddle with this manually (and I would advise against it,
unless you know what you're doing).

I hope that hasn't confused you any more than necessary :wink:

Alex

#13

Hi :slight_smile:
Yes, quite. So 2 different front-ends (such as Base on different
machines or for different purposes) could use the same back-end.

"Daisy chaining" one to another seems like a really bad plan.

Fairy lights on Xmas trees used to be daisy-chained which meant that
if 1 bulb went out then they all did. That made it extremely difficult
to find out which one (or more) was the problem. Nowadays most Xmas
tree lights are done in parallel so that each bulb is independent of
all the others. Now if one or more bulbs go the rest bravely shine on
and it's easy to see which need replacing.

Having a string of databases all depending on the all the rest to work
properly sounds like a nightmare!

On the other hand there is a lot of sense in a modular approach with
specific discrete chunks doing specific jobs. Base and Access each
have different modules within them. So that building a Form or Report
straight from a Table makes it all quite inflexible and liable to
problems later on. So it's generally better to build Queries, even if
the Query doesn't actually do anything except pass everything straight
through. Then in future years the table could change quite radically
without forcing all the Forms and Reports to have to be redesigned.
Just edit the Query a little so that all the Forms and Reports
continue to get all get the inputs they are expecting.

It's something that makes Base much more highly scalable than Access.
Move the back-end tables from one program to another, either to get it
smaller and lighter or to deal with a greater weight of data. perhaps
move the back-end from a stand-alone machine onto a network or up onto
a Cloud or some other place that might not even be envisaged possible
during initial design of the database program.

Even better is when Writer or Calc is used for the Forms and Reports
so that those things can be viewed by wide-eyed-end-users with no
training or understanding of database design. They just get usable
output in a familiar setting and can edit around it themselves if they
need to change font-size or formatting or write a new letter based on
the old one.

Regards from
Tom :slight_smile:

#14

Thanks, Alex, for the clarification. Since I have never used the internal
hsqldb database, I was unaware that it kept its data in the .odb file. The
last internal database I used was Adabas D in Staroffice. I quickly went to
JDBC or ODBC connectors to other external datbase engines (e.g., MySQL).
John

#15

Hi :slight_smile:
I think it's worth avoiding using the internal back-end.

Until very recently the internal one was an ancient and heavily
tweaked version of Hsqldb. I think the devs have been working at
replacing it with a more straight-forward Firebird and hopefully that
will be updated in some sort of reasonably automatic way with an
opt-out clause.

The old internal Hsqldb one was a bit warped and the new Firebird one
is a bit new.

If you get a reasonably recent version of Hsqldb to use as an external
database then apparently it's extremely good and exceedingly fast, for
small databases (such as address-books) but when using the internal
tweaked version people have had some fairly serious problems. It's
entirely written in Java apparently but even so it's allegedly quite
good.

Regards from
Tom :slight_smile: