[Solved] Upgrade HSQL Driver in Base

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

[Solved] Upgrade HSQL Driver in Base

Post by misitu »

Edited to include a review of Classpath settings.
Edit
I can see that I have classpath'd both my Live and Test database locations.
I also know that this is highly :oops: NOT :oops: recommended
However, it may provide a solution in my restricted configuration (one server, one application)

For the sake of avoiding anyone else's unnecessary exertions, I will return to the topic when I have reviewed these new opportunities.

David
Right, this works but impacts the Live instance, IOW I can't test an upgraded HSQLDB driver and leave the live instance as was.

Hence I need to find out how to apply driver versioning to the .odb package. IIRC there was a Macro solution for this, developed by Villeroy and others. Will have a look and BRB.
Hello

I have a mature AOO accounting application working with HSQLDB 2.3.0 and plan to upgrade to 2.3.4 as soon as possible. It's about time I got to grips with these upgrades anyway.

I can access the database using sqltool and sqlworkbench simply by installing hsqldb.jar and sqltool.jar in the appropriate directory.

However, "houston", I have a problem.
I cannot see how to do this for the OpenOffice Base client.

I have tried replacing the drivers in the drivers directory parallel to the database directory but this fails with a message indicating that the two driver levels do not match. This seems to indicate that I need to look inside the OO Base client.

I have read that it is possible to pack the .jar files into the .odb but the structure of that (zip container) offers no obvious clue. I think I need to do something else!

Any assistance very welcome
Thanks in Advance
David
Last edited by misitu on Fri Sep 09, 2016 1:00 am, edited 4 times in total.
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Upgrade HSQL Driver in Base

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

Thanks, Villeroy.

My connection string includes -url jdbc:hsqldb:hsql://localhost:9002/TEST
From your link it seems that this would not work, I quote "The connection features are still restricted to external jdbc:hsqldb:file: connections."

Please let me know if I am wrong here.

Alternatively(or additionally), I believe it is possible to set driver location from a startup macro. Is this true?
Thanks
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Upgrade HSQL Driver in Base

Post by Villeroy »

But you can specify the location of the hsqldb.jar for this specific Base document anyway.
Yes, you can use the auto-installer for your network connection just comment out this line:
setupBackendConnection oDocURL, oDataSource
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

Thanks, Villeroy.

I had a look at this earlier post where you and DACM introduced the macro enhanced split database concept. Thank you both very much for all your efforts in conceiving and realising this. Without it everything would have been so much more difficult if that is the word.

The needs I have described at the head of this topic are very simple: to give each .odb + its database/ folder a link to an appropriate hsqldb.jar driver.

I think I can do it like this.

First create a macro rather like your original snippet, such as

Code: Select all

Sub Snippet
	Const cPath = "/usr/local/lib/hsqldb/lib/hsqldb.jar"
	Dim oDataSource As Variant
	Dim oSettings As Variant
	sURL = ConvertToURL(cPath)
	oDataSource = ThisDatabaseComponent.DataSource '<< use when macro is embedded in db
	oSettings = oDataSource.Settings
	oSettings.JavaDriverClassPath = sURL
End Sub
where the

Code: Select all

Const cPath = "/usr/local/lib/hsqldb/lib/hsqldb.jar"
line points to my driver location

Then store the macro in the individual database macro container

Finally ASSIGN the macro to the event "Open Document".

This I think will open the database with a link to the driver I want, and if I want to separate TEST and LIVE database instances for example to scope out a new HSQLDB version* it will be a walk in the park on a sunny day.

* Example: 2.3.4 includes stricter validation than earlier versions. A good idea to find this out before applying to a live system.

Thanks in advance!
David

EDIT
I have another issue here
How can I find out the location of the database which I am just starting?
For example, in LIVE, I can pre code the above to assume it is running from the LIVE directory, and for TEST vice versa.
However, if I copy a DB from LIVE to TEST the hard coded location will be wrong. (Copying from LIVE to TEST is a frequent occurrence during development or debugging).

The startup macro needs to know if its DB is running from LIVE or TEST.

Is there an API solution for this?

Cheers
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

Right, a bit further along.
1. As above, I can nominate a local java classpath using ThisComponent.DataSource.Settings.JavaDriverClassPath. This is documented.
2. Using XRay, I find the path to the database in ThisComponent.DataSource.Name
3. If that's correct then I can set the driver class path according to the database location, i.e. LIVE, TEST, or whatever.
That's the Theory.
Just setting up another instance of TEST to verify this and have a cup of tea and will then see what can be done.
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Upgrade HSQL Driver in Base

Post by Villeroy »

Use relative paths with my code. This will even work when you move the database to another operating system.

Put all hsql frontends (*.odb) in one directory.
Put all local HSQL backends (jdbc:hsqldb:file:) into the same directory, all in one subdirectory or in one subdirectory per database.
Put the driver hsqldb.jar into the same directory or into another subdirectory.
Use my existing code (modules AutoInstall and Helpers).
Specify the paths on top of the AutoInstall module relative to the *.odb.
Specify a registration name or an empty string for no registration.
A network connection jdbc:hsqldb:hsql: is availlable by the same address from anywhere. The address does not need any adjustment which is why you should comment out the single line which starts the subroutine setupBackendConnection.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Upgrade HSQL Driver in Base, re Version control

Post by misitu »

I'm going to have a look but maybe someone can help first with this issue.

The HSQLDB version number in the .properties file.
Is it updated by any process?
If so, which?
Which processes if any are affected or cancelled by any difference between the .properties file version number and the live hsqldb version number?

I am strictly concerned with OO Base here, the other hsql stuff I can handle separately.

Cheers!!
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Upgrade HSQL Driver in Base

Post by Villeroy »

This has nothing to do with Base. HSQL works independently from OpenOffice and any other frontend you may connect to it. Even while working with an embedded HSQLDB, the embedded database will be extracted to a temporary directory before connecting the office to it. The whole embedded thing works exactly like a normal database connection. When you close the latest office input form or grid view, the extracted database will be wrapped into the odb file again.

HSQL is backward compatible. HSQL2 can handle a HSQL1 database. It will convert the old db on the fly. After the conversion you can not use the database with HSQL1. Sometimes you get some errors from the converted database but they are easy to fix.

Once the office suite is connected to the database, no OpenOffice code reads from or writes to the database. OpenOffice makes use of the independent software product HSQL like it is able to use dozends of other database products.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

No embedded! I am a bit past that now :-)
Clarification: my database is in its own directory (data, properties, script), front end in directory above that.

No embedded, no start up macros, nothing, just startup links to the odb and the server locations.

All I wanted to know is what does the hsql version number mean in the .properties file. Seems from what you are saying that it does nothing and I don't need to worry about it. Is this correct?

Thanks
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Upgrade HSQL Driver in Base

Post by Villeroy »

I think everything is just fine as long as the database runs well.
The meaning of each hsqldb file is documented. There is a HSQLDB mailing list.
When I upgraded from 2.3.something to 2.3.4, I noticed that I had to upgrade all hsqldb.jar on the server and client side. 2.3.4 seems to be incompatible with older versions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

OK, thanks, I will run that past the HSQL forum just to be as sure as is possible.
Cheers
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: [SOLVED] Upgrade HSQL Driver in Base

Post by misitu »

Yes, Thanks!
Relevant text located in HSQL Guide. New version is written into .properties after Shutdown Compact. That's All I Need To Know! [Hurrah:=) ]
David.
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

Villeroy wrote:FreeHSQLDB
I return to this without being much the wiser.

Thanks Villeroy, I have had a look at FreeHSQLDB. My attempt to reuse what I thought I understood as clear code failed miserably.

I only need something simple, which is: preset the classpath in an OO frontend.

If I have a OOO frontend called LIVE, for example, I want to identify the driver classpath individually.

I thought I could do this by creating a macro for each frontend. But that won't work, because it would be needed before opening the database (an event), and all the assign event macros must be in global.

Am now at the end of my rope, so to speak. I don't think I should need all the FreeHSQL code for the simple task as outlined. One of the issues is the databaseDocument object, which is not always instantiated.

Is it that difficult to create a line or two of code to set up one of a variety of literals simply to assign a classpath value?

Thanks
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [not Solved] Upgrade HSQL Driver in Base

Post by Villeroy »

Code: Select all

Const cPath = "C:\path\hsqldb.jar"
sURL = convertToURL(cPath)
oDSS = ThisComponent.DataSource.Settings
oDSS.JavaDriverClassPath = sURL
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Upgrade HSQL Driver in Base

Post by misitu »

Fixed, thanks. Apologies for the delay in closing this item.

I found how to start AOO Base with a specific classpath. This is by means of the special macro subroutine "Start" as shown here:

Code: Select all

REM  *****  BASIC  *****

SUB Start
	Const cPath = "C:\your path to\LIVE\driver/hsqldb.jar"
	dim oDatasource as object
	dim oSettings as object
	dim sURL as string
	sURL = ConvertToURL(cPath)
	oDatasource = ThisComponent.Datasource
	oSettings = oDatasource.Settings
	oSettings.JavaDriverClassPath = sURL
END SUB
substitute LIVE with TEST, etc., to taste.

The macro sits in Standard within the database container to which it is to apply on startup. In this case, for example, LIVE.odb.

Related Post: please see also viewtopic.php?f=40&t=84389
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
Post Reply