Problems importing an OO database into LO

users
#81

Am 20.04.2012 10:48, Ian Whitfield wrote:

There are not many users who suffer from the limited capabilities of
HSQL 1.8.
A high risk of total data loss is the main reason why one must not use
the embedded database. Getting an embedded database out of the jail is
easy enough.

OK Andreas (or anyone else) - for those of us not so good at this how
about a detailed Tutorial on doing this??

Right here....

1) Download and extract the latest HSQLDB to some place. No installation required.
2) Point the "Class Path" setting in the LibreOffice Java options to hsqldb.jar in your subdirectory "lib".
3) Extract the database folder out of your embedded .odb.
4) Rename the files:
  properties, script, backup, data
to:
  foo.properties, foo.script, foo.backup, foo.data
where "foo" is just an arbitrary name.
5) Connect a new Base document to a JDBC data source with an URL like:
jdbc:hsqldb:file:/path/to/extracted/database/foo;default_schema=true
(the "foo" refers to the same database name I used as file name prefix)
6) Copy queries, forms and reports from the old embedded .odb to the new one.

Slightly more details and links in:

http://user.services.openoffice.org/en/forum/viewtopic.php?f=61&t=46324&p=214069&hilit=hsqldb#p214049

For multi-user access you can start the hsqldb.jar in server mode and connect the .odb clients with something like
jdbc:hsqldb:hsql://192.168.0.2/foo;default_schema=true

#82

Mark:
     This will work for any database engine other than a HSQLDB 2.x
version. Once the newer hsqldb.jar has been added as a Class Path
(Options > LibreOffice > Java) to LO, that version will not open any of
the embedded Base databases. (You can not access hsqldb as an external
database until you do this.) This has to be done a different way. I have
tried to open all of my embedded databases with LO 3.4.5 after I had
added the 2.2.8 hsqldb.jar as a Class Path. All gave me an error
message. I had no problems opening them using LO 3.5.2. Since that time,
I could open these embedded databases using LO 3.4.5 as another user
(same computer same OS). Opening an embedded database in one LO version
and connecting to hsqldb in another does not allow copying and pasting
between these two. Drag and drop does not work either.
    I think that this is the problem faced by Base when hsqldb issued
new versions: no backward compatibility.
    In this case, the database document file has to be unzipped and the
files places in a new folder. The path URL for this data is
<<path to the folder>>/<<database name>>/. Files have to be renamed.
(I'm not sure how many of these require this.) They include script, log,
properties, and data. For the database named abc, these files are
renamed to abc.script, abc.log, abc.properties, and abc.data
respectively. There are more things that need to be done than this.

--Dan

#83

Miguel,
May I correct you, you do not need a license for driving a car! When my son was 10 he had no license but I let him test driving my car!
But if you do not know the features of it, e.g. how to handle the gears, then you may study the Instruction Book, the User's Manual or whatever it is called.

Just to avoid misunderstanding: I am talking about LibreOffice and it's LibreOffice Help -- not OpenOffice's.
When I was new to LibO(-Base) I spent hours on trying to learn the logic of LibreOffice Help.
Try your self:
Let us say that you need to create a form (Base) in Design View based on a query , because you need fields from two tables in one of which you have a 'birthday' field that you want to be calculated as 'age' and want that age field visible on the form.
You know how to do in MSAccess but not in LibO-Base.
Start by pushing the LibreOffice Help button (lifebuoy)
Good luck!
Pertti Rönnberg

#84

Thanks Andreas

OK
Step 1. Done (I downloaded and extracted hsqldb 2.2.8 to my 'software' folder)
Step 2. The lib folder is under root and all files are grey-ed out in Class Path??
              I then looked at my 'software' folder and found it there.
              Tried to set 'Class Path' to this but again all files are grey-ed out.

             Did a search across my whole drive and found the following 3 files
             /home/ian/software/hsqldb-2.2.8/hsqldb/lib/hsqldb.jar
             /opt/libreoffice3.5/programs/classes/hsqldb.jar
             /opt/libreoffice3.5/programs/classes/sdbc_hsqldb.jar

Where to now?

Thanks for the help!!

IanW

#85

Hi :slight_smile:
The in-built help is a bit rubbish and out-dated but the official documentation is fairly excellent. 
http://wiki.documentfoundation.org/Documentation/Publications
http://www.libreoffice.org/get-help/documentation/
The guides on the official page are the same as the ones on the wiki but the wiki has more guides and links to 3rd party documentation too.

The official website only has the guides for 3.3.x branch but the wiki has a load of things that have only recently been added for the 3.4.x branch.

The problem with the in-built help is that 2 teams each thought it was the other team's job to update.  The docs team doesn't seem to have permissions or access to the in-built help but even if they did then they are a tiny group and are a bit swamped so they have focused on the guides for now.  Anyone that wants to join the docs team is very welcome!
Regards from
Tom :slight_smile:

#86

Pertti, the problem is, that software usability today in most cases is still
far from being intuitive.

So the analogy with the car does not seem appropriate. Better take a Jumbo
Jet's cockpit. Even if you don't want to fly it, but just to roll across the
airport, you need to know awfully lots of things to move the plane.

The challenge is to use LibreOffice despite of its shortcomings :wink:

Nino

#87

I fully share this. Though some (eyes looking to Redmond) claim computing is "intuitive", I'm seeing everyday that it is *not* and that using a computer *requires* training.

#88

Hi :slight_smile:
I think that's a tad unfair.  People have grown-up learning MS's ways of doing things and so "intuitive" means doing things MS's way, ie the ways that encourages a proliferation of viruses and causes slowdowns and encourages bad-habits.

A blank page in a Writer document looks like it's going to be fairly easy to write in and it is as long as people can avoid the bad habits they have picked up and perhaps even quickly skim through some of the official documentation or look things up in it if their first few tries are not instantly successful
Regards from
Tom :slight_smile:

#89

Try "Add File" (freely translated from my FR interface) first (point to the directory where your .jar lies) then, when back to the Class Path window, use "Add Archive". You should be set now.

#90

Hi :slight_smile: I think that's a tad unfair. People have grown-up learning
MS's ways of doing things and so "intuitive" means doing things MS's
way, ie the ways that encourages a proliferation of viruses and
causes slowdowns and encourages bad-habits.

Intuitive? Hu. From Wikipedia: "Intuition is the ability to acquire knowledge without inference or the use of reason."

I do not know of *any* computer or computer program that can be run without inference or the use of reason.

Any time I hear "intuitive" within the IT context, I jump to a gun.

A blank page in a Writer document looks like it's going to be fairly
easy to write in and it is as long as people can avoid the bad habits
they have picked up and perhaps even quickly skim through some of the
official documentation or look things up in it if their first few
tries are not instantly successful

To do that, they need training. Much training. First to forget that a computer is no typewriter, then to change their (bad) habits.

#91

Hi :slight_smile:
+1
I can definitely agree with this!  Well said! :slight_smile:
Regards from
Tom :slight_smile:

#92

Hi Ian,

OK
Step 1. Done (I downloaded and extracted hsqldb 2.2.8 to my 'software'
folder)
Step 2. The lib folder is under root and all files are grey-ed out in
Class Path??

Why are you trying to put the jar in your root's lib folder, or are you
logged into the GUI as root ?

             I then looked at my 'software' folder and found it there.
             Tried to set 'Class Path' to this but again all files are
grey-ed out.

Did you change the permissions on that folder after downloading so that
it was accessible ? From what you describe, it sounds like you didn't.
Some systems do not automatically enable the execute bit attribute on
files that you download from the internet or is it perhaps your Software
folder that has write/execute limitations ? LO needs to be able to
access the jar file in a folder which it can read from.

            Did a search across my whole drive and found the following 3
files
            /home/ian/software/hsqldb-2.2.8/hsqldb/lib/hsqldb.jar

Check the permissions on your /software folder and also the permissions
on the hsqldb folder and any files in it. The jar file at least, needs
to have the executable bit set.

Alex

#93

Am 20.04.2012 14:08, Mark Stanton wrote:

Hi Ian,

The easy version is :-

1) Open your database with the HSQLDB embedded data.
2) In a separate window, create a new database with any other kind of
    data engine.
3) Drag the tables from the embedded database to the new database,
    one by one.
4) Drag any other objects from the old database to the new one, too.

I'm guessing that the important bit that you need there is missed
out, the "how to create and link up to A.N.Other database".

Indeed, this is the easy version to copy anything (spreadsheet, csv, mail address, embedded HSQL, ...) into some writable target database.
This can not convert an embedded HSQLDB to an external one because once you point the office to the external HSQL the embedded won't be accessible anymore. I described how to extract the whole embedded thing out of the database.odb and use it directly with the external database.

For the above outlined copy&paste method I use to recommend that you should have prepared the target database.
Copying the structure and data in one go, as offered by the import wizard, never creates any auto-ID fields. The wizard can automatically add some integer key, but it is not the automatically incrementing one you need in most cases. Once the tables are filled with data it is difficult (impossible?) to change the primary key to be auto-incrementing.
However, if the auto-id is predefined you can import arbitrary integer data that fit to the related integers in other tables.

First create all the tables with default values, indices, primary keys, constraints and foreign keys (relations). Then copy over the raw data into the prepared structure and in the right order. Detail tables first, then the dependent data tables where the foreign keys refer to the detail tables.

#94

Am 20.04.2012 16:48, Ian Whitfield wrote:

             /home/ian/software/hsqldb-2.2.8/hsqldb/lib/hsqldb.jar

menu:Tools>Options>Java
Button "Class Path"
Button "Add Archive" and point to the above one.

#95

+1 - The only reason computers seem intuitive is that most OS's and programs use the ideas developed at PARC and first implemented in the 80's. Some of the command groupings are not necessarily intuitive. MSO ribbon to me is not very intuitive, I can spend more hunting down a command than with older interfaces.

#96

OK - one step at a time I guess....

I checked the permissions on the three .jar files and found that the 'x' setting was not checked so I made them all wide-open. Now when I go to 'Class Path' I can "see" them and select one.

(BTW - except for the one I put in in my 'software' folder the other two must have been put there by LO). I selected the one in 'software' (ver 2.2.8 - hsqldb.jar)

As I now want to try the external-linked way of running Base I selected to "Create a New Database'. Base opens but says .....

a) I'm still using the embedded Database.
b) And I get an error box that says ... "The connection to the data source <DB Name> could not be established.
The driver class 'org.hsqldb.jdbcDriver' coud not be loaded. The additional driver class path is 'vnd.sun.star.expand:$BRAND_BASE_DIR/program/classes/hsqldb.jar
vnd.sun.star.expand:$BRAND_BASE_DIR/program/classes/sdbc_hsqldb.jar'.

Some more assistance please!!!

#97

Hi Ian,

Check the permissions on your /software folder and also the permissions
on the hsqldb folder and any files in it. The jar file at least, needs
to have the executable bit set.

You only needed to check/change the permissions on this :

/home/ian/software/hsqldb-2.2.8/hsqldb/lib/hsqldb.jar

not the others ! Those other jar files you listed are the ones that come
with LO, and you shouldn't really be fiddling around with those.

As I now want to try the external-linked way of running Base I selected
to "Create a New Database'. Base opens but says .....

a) I'm still using the embedded Database.
b) And I get an error box that says ... "The connection to the data
source <DB Name> could not be established.
The driver class 'org.hsqldb.jdbcDriver' coud not be loaded. The
additional driver class path is
'vnd.sun.star.expand:$BRAND_BASE_DIR/program/classes/hsqldb.jar
vnd.sun.star.expand:$BRAND_BASE_DIR/program/classes/sdbc_hsqldb.jar'.

1) Remove the links that you added to the Classpath parameter to :
/opt/libreoffice3.5/programs/classes/hsqldb.jar
/opt/libreoffice3.5/programs/classes/sdbc_hsqldb.jar

LO already knows where to find them. From what you describe as your
actions, you added them again to the classpath, and that is
possibly/probably what is causing LO to moan.

2) After removing the links to those files, restart LO.

3) Do not open your old ODB file in LO Base yet, i.e. the one you want
to migrate over to using the hsqldb2 engine.

4) Follow Andreas' instructions from point (3) onwards :

3) Extract the database folder out of your embedded .odb.

What Andreas means here is that you should make a copy of your existing
ODB database file, then unzip the copy. Although one can work directly
on the original ODB file if you know what you are doing, it would be
better I think in this case to work on a copy of that file.

4) Rename the files:
properties, script, backup, data
to:
foo.properties, foo.script, foo.backup, foo.data
where "foo" is just an arbitrary name.

This instruction should be clear, if not, please state what you don't
understand

5) Connect a new Base document to a JDBC data source with an URL like:
jdbc:hsqldb:file:/path/to/extracted/database/foo;default_schema=true
(the "foo" refers to the same database name I used as file name prefix)

This too, seems clear to me, but if you have a question, please ask.

6) Copy queries, forms and reports from the old embedded .odb to the new one.

This too, seems clear to me, but again, if uncertain, please ask.

Alex

#98

Thanks Alex..... but 'No Go' I'm afraid!!!

I ONLY added the ONE path to the class like you say but I removed it and re-started LO and did it again. After another re-start I STILL get the same error message when I try to start a new Database.

I'm working this way because Base screwed-up my original DB so badly (which is why I decided to make a change). The original .odb file is such a mess. In looking for an alternative I've been working with Kexi and spent days correcting and editing my table in this program. It's very good but I have discovered a few flaws - the main one being a TOTAL LACK of support!!!!

So my plan is to get Base working with the external HSQLDB2 engine, recreate my Table layout and then export my corrected table from Kexi in .csv format and import it into the new Base layout. Hope you follow me.

Thanks for the kind help.

#99

In addition to what I wrote a couple of hours back - I have just tried a couple of other things...

I linked the class path to the two files mentioned in the Error Box. LO accepted both of them OK AND popped-up a box warning me to re-start LO and then worked fine. BUT both are linked to the embedded engine!!

I tried again with the 2.2.8 version and noticed I DO NOT get the warning box to re-start LO. And it gives me the error box as reported.

I then moved a copy of the .jre file to the same location as the first two and tried again - no change!!

This leaves me wondering if the .jre file is at fault. It's new, (I downloaded it yesterday), and is 1.4Mb in size??

Does this sound like a problem??

#100

Am 21.04.2012 15:46, Ian Whitfield wrote:

This leaves me wondering if the .jre file is at fault. It's new, (I
downloaded it yesterday), and is 1.4Mb in size??

This is the md5sum of my working hsqldb-2.2.8.zip:
$ md5sum ~/download/hsqldb-2.2.8.zip
8b7668689cd208867c77f2f4d77922de /home/andreas/download/hsqldb-2.2.8.zip