OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Base and HSQL together but not mixed? How to do it?
Goto page 1, 2, 3, 4, 5, 6  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Brother Damian
General User
General User


Joined: 04 Nov 2008
Posts: 27

PostPosted: Wed Jun 23, 2010 11:39 pm    Post subject: Base and HSQL together but not mixed? How to do it? Reply with quote

Hello,

In general I am happy working with Base and the embedded HSQL. They seem to work well together and Base can quickly develop quite sophisticated forms and reports to use with HSQL.

But then we have the instabilities. Recently, some primary key numbers in my database were "magically" changed, requiring me to deduce the changes by (painfully) reviewing the data and correcting the corruption directly in the tables. I also got help in this forum to fix the fact that the application was trying to insert an auto-generated primary key that it had already granted, rendering the application (temporarily) useless.

I do not know why these things happened but I am hoping that they could be avoided by following the procedure suggested by fredt here:

http://www.oooforum.org/forum/viewtopic.phtml?t=97522&postdays=0&postorder=asc&start=15

Which, if I understand well, allows having the Base file separated from the HSQL files that hold the database, but still working together.

In trying this, do I still need the .bat files? would this work with a portable version of OO.o? Does this works only with OO.o 3.2?

fredt states that his method is not about running a server but then directs to the instructions to connecting to a server. I admit that this is a bit beyond me but I would really like to experiment with this configuration.

Is there someone who could provide a set of step by step instructions like those offered by dacm?

I would be very grateful to anyone who could help me with my questions and/or the request of detailed instructions.

Thank you in advance,
_________________
Brother Damian
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Jun 24, 2010 1:16 pm    Post subject: Reply with quote

To answer some questions...

The issues you've experienced may be due to data corruption. This is all too common with "Embedded databases" created by default with Base.

BACKGROUND: An “embedded database” is actually a zip-archive (.odb) file which includes all database-application components including: (1) database connection information, (2) front-end elements such as Forms, Queries, Reports and Macros, and (3) back-end database structures and user-data such as Tables, Indexes, Views, etc. This has some distinct advantages such as portability of the database. But Base, itself, is only a front-end which connects to a back-end database engine (RDBMS). Therefore, Base ships with a default (HyperSQL) database engine. Base can connect to a variety of RDBMS's but the single-file “embedded database” is designed to work exclusive with the built-in version of HSQLDB 1.8.0.x. Even the newer versions of HSQLDB (1.8.1.x and 2.x) do not support this feature.

So far so good, but consider that in the 'embedded database' configuration, all data storage-and-retrieval must go through OOo, because it serves as the 'file-host' of the (.odb) zip-container file. If you've ever experienced an OOo crash, then you're already scratching your head and wondering how this might effect your data storage – especially during database-engine writes! Now further understand that a Base (.odb) file is compressed using the popular zip-compression format. So there's an added layer of complexity as OOo performs zip-translation during all file input/output. This is the likely culprit as any crash of OOo during a write-operation can corrupt the Base (.odb) file, leaving you with an unrecoverable database file (when using a single-file "embedded database" with OOo).

There may be other factors, but they are minor compared to this zip-file-host issue.
    (1) For instance, OOo runs the HSQLDB engine “in-process” within the same computer memory allocation, so an OOo crash halts the database engine as well. This prevents an orderly shutdown, but HSQLDB does employ robust data protection-and-recovery mechanisms to avoid data corruption in the event of a crash. So this is a normal risk involved with running any database-engine "in-process" (embedded-mode), but the OOo crash-rate perhaps increases that risk.
    (2) In addition, OOo sets [overrides] the database write-delay to 60 seconds by default. This is excessive because 1 second or less is normally sufficient to pool data and improve write-performance even in high-concurrency environments where hard-drive mechanics would otherwise slow the write process (such as waiting for the physical drive platter to rotate to the write sector many times). I can scarcely imagine a scenario where an “embedded database” would require a write-delay of more than one-tenth of a second. This is a slight factor because an OOo crash while running HSQLDB “in-process” as with the “embedded database” design, loses all data held-up in the write-delay function.
    (3) And finally, the zip-translation processing is an asynchronous process, meaning it's done in chunks, so the data is periodically “flushed” from memory to persistent storage. This is normally transparent and trouble-free, but when advanced users open Forms using macros, the “flush” algorithm may be bypassed in some cases (link1, link2). This can also leave the 'modified=no' flag unchanged, so the 'log' file data (persistent session data) is dumped rather than incorporated into the database upon restart. So these users report loss of all 'session data' after closing and re-opening a database fed by such macro-loaded Forms.

SOLUTION: The "file mode" solution offered by fredt (and others in this forum) stabilizes the situation considerably. It allows HSQLDB to manage it's own files separately from the Base odb file, so OOo is no longer the file-container host for the data. That alone solves the data-corruption issues associated with OpenOffice (and should clear up the macro-loaded form "flush" issue mentioned above). Among solutions, this one is particularly attractive because it allows seamless migration for existing data/projects AND you don't have to start the database engine separately like you would with most alternatives (except H2). So this solution is compatible, convenient (built-in) and seamless.

However, as fredt mentioned, it is actually best to start the database engine as a separate process using the stand-alone "server mode" (as opposed to the 'file' mode solution above). In this configuration, the database engine remains running even when OOo crashes. This contributes to a robust data environment among other advantages (enhanced database portability, mutli-user capable, etc.). Of course, any other glitch (hardware, software, or power-related) can render both solutions equivalent -- relying primarily on the database engine's "abnormal shutdown recovery mechanisms." These mechanisms work well, but any unwritten data (such as data held-up by the write-delay setting) is lost or "rolled-back". And keep in mind that you control the write-delay setting, which can be effective well under 1 second (say "100 MILLIS" or 1-tenth-of-a-second normally suffices). For the casual user, server-mode is considerably more technical to setup and use on a daily basis. So while the server mode solution is technically superior, the file mode solution is more seamless and fully sufficient to address the data corruption issues caused by the Base default configuration. Either way, you can start with the file mode solution now, and simply switch to server-mode using the existing files when you're ready, as needed, if needed.


*click here* for the "Quick Fix" setup options: automated installers and optional manual procedures...


And lastly, there's no need to start/stop the HSQLDB server with this "file mode" solution because the engine is managed "seamlessly" by OpenOffice. So there's no need to setup script (.bat) files for this purpose. Yes, this and all solutions work with OOo Portable 3.x and OpenOffice 3.x (including the latest 3.3). It also works with the new HSQLDB 2.x release.


Last edited by dacm on Tue Jul 05, 2011 8:04 pm; edited 99 times in total
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Thu Jun 24, 2010 8:16 pm    Post subject: Reply with quote

Oh!
I've got error "Class org.hsqldb.jdbcDriver not loaded"


/opt/openoffice.org/basis3.1/program/libjdbcli.so exists.

What the trouble?
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Thu Jun 24, 2010 8:43 pm    Post subject: Reply with quote

BigAndy wrote:
Oh!
I've got error "Class org.hsqldb.jdbcDriver not loaded"
...What the trouble?

Go to Edit>Database>Properties
Then Test Class
If the Driver won't load you have a problem with steps 1-3 and you must correct this before continuing.
NOTE: the JDBC driver class setting in step 6 does not tolerate a space at the end -- although this would be invisible unless you place your cursor there and delete it as necessary.

Then Test Connection
If you get an error here, then your problem is the Datasource URL or write priviledges specific to your configuration:
Focus specifically on the file path syntax. I've given the syntax for Windows which has been tested in Win 7 64-bit (my own tests), Vista (Greengiant224 tested below), and XP (tested by sliderule).
Code:
hsqldb:file:C:\users\database\mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

NOTE: Windows 7 defaults require the 'users' folder specification as included above due to write-restrictions for non-administrator accounts. Actually, many different paths will work including relative paths. A relative path is relative to the OpenOffice>Program folder.

Some systems will require a double-slash or forward-slashes as indicated in fredt's URL:
Code:
hsqldb:file:c://users/database/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

NOTE: the Datasource URL may not tolerate a space at the end.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'


Last edited by dacm on Tue Jun 29, 2010 1:53 pm; edited 3 times in total
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Thu Jun 24, 2010 9:55 pm    Post subject: Reply with quote

Quote:
Go to Edit>Database>Properties
Then Test Class

Was done before my previous post.

Quote:
NOTE: the JDBC driver class setting in step 6 does not tolerate a space at the end -- although this would be invisible unless you place your cursor there and delete it as necessary.

I know about this behavior and checked before posting previous

Quote:
If you get an error here, then your problem is the Datasource URL or write priviledges specific to your configuration:
Some systems will require a double-slash or forward-slashes as indicated in fredt's URL:


Of course Ive tried following strings (using linux)

Code:
file:home/user/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
file:/home/user/separate_db;...
file://home/user/separate_db;...
file:///home/user/separate_db;...

file:/home/user/separate_db.odb;...
file://home/user/separate_db.odb;...
file:///home/user/separate_db.odb;...



Quote:
the Datasource URL may not tolerate a space at the end.

Of course i checked absence of leading and following spaces[/quote]
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Jun 25, 2010 4:59 am    Post subject: Reply with quote

I'm stumped.

So which Test causes the error...?
Test Class or Test Connection?

Did you try it without the "file:" prefix to the string. That's not necessary on Windows (as far as I can tell).

I presume the folders are created automatically in Linux as in Windows...or already exist?
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Fri Jun 25, 2010 9:19 am    Post subject: Reply with quote

dacm wrote:
I'm stumped.

I stumped twice. While invoking this process with $strace, output finishng when any java class called.

Quote:
So which Test causes the error...?
Test Class or Test Connection?

I've tried both. More. I tried make this onto different OOO distributions (sun-oracle 3.2.1, goOOO 3.1.1, from ubuntu ...

Quote:
Did you try it without the "file:" prefix to the string.
That's not necessary on Windows (as far as I can tell).
No . I'll try

all folders created by default and all rights granted 2 current user.

BTW SunReportBuilder works prperly, thats mean jre works.[/quote]
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Fri Jun 25, 2010 9:40 am    Post subject: First win Reply with quote

When i killed "quick start" and restarted OOOBase then jdbc driver loaded. (test class)
But When pressed "Test connection", got an error. "Connection not established"

Code:
A connection for the following URL was requested: A connection for the following URL was requested: jjdbc:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false


Same behaviour with
file:/home..
file://home...
file:///home...
/home..
//home...
///home...
strings
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Jun 25, 2010 10:02 am    Post subject: Reply with quote

BigAndy:

OK, just this once . . . I will offer a comment.

You said the error is:

BigAndy wrote:

A connection for the following URL was requested: A connection for the following URL was requested: jjdbc:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false


But . . . I suspect ( yes, I am doing this on a Windows XP machine, not Linux . . . BEFORE you enter the file: part . . . you need to enter . . . hsqldb:

Therefore, on the screen . . . something like:

Code:

hsqldb:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false


Sliderule
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Jun 25, 2010 10:05 am    Post subject: Reply with quote

At a glance you might take a look at the error code returned upon Test Connection:
Code:
jjdbc:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

Should be:
Code:
jdbc:hsqldb:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

Of course the "jdbc:" part is preset so you don't re-type that, since the connection is specified as JDBC in the original "Connect to an existing database" wizard.
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
Greengiant224
OOo Enthusiast
OOo Enthusiast


Joined: 05 Jun 2010
Posts: 107
Location: All Over The World

PostPosted: Sat Jun 26, 2010 7:01 am    Post subject: Reply with quote

Hi,
I have been following DACM 'tutorials' on HSQL with much vigour.
but I found in my case using vista that the datasource url has to be speciifc

Your instructions above state:
Code:

jdbc:hsqldb:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false


I found that by inserting two forward slashes thus, it worked perfectly.

Code:

jdbc:hsqldb:file:\\C:\Database\testdb;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false


I think it's just a case of trial and error so just experiment till you get it working

Keep up the good work DACM and thanks once again
_________________
Greengiant 224

Linux 2.6.38, KDE 4.9.2, LibreOffice v 3.5.x (Java 1.6.35) utilising HSQL v1.8 & v2.3.+, MySQL, PostgreSQL, SQLite
+ Blood, Sweat and Tears (Application, Determination and Perseverance)
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Sat Jun 26, 2010 7:45 am    Post subject: Reply with quote

sliderule wrote:
BigAndy:

But . . . I suspect ( yes, I am doing this on a Windows XP machine, not Linux . . . BEFORE you enter the file: part . . . you need to enter . . . hsqldb:

Yes. I ommit point in my previous posts this keyword. In OOBase keyword "hsqldb:"presents

I solved problem by reinstalling jre.

Now it works. Probably perfectly. Now i investigate HSQLDB 2.0 behaviour

Great thanks 2 all!!
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Sat Jun 26, 2010 7:48 am    Post subject: Reply with quote

[quote="dacm"]
Code:
jdbc:hsqldb:file:/home/andrew/separate_db;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

Of course the "jdbc:" part is preset
Of course!.


Works with next strings (only part that input by user)
Code:
hsqldb:file:/home/andrew/s

also
Code:
hsqldb:file://home/andrew/s

also
Code:
hsqldb:file:///home/andrew/s
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Jun 26, 2010 6:15 pm    Post subject: Reply with quote

Thank you Greengiant224 and BigAndy !

Based on your findings I'll try to narrow down to one or two universal examples for the dedicated thread. I did extensive tests with Windows 7 including system variables (to no avail), multiple slashes (to no avail), and relative paths (quite successful). I'll need to do more testing tonight.

Thanks again, this will reduce setup frustrations considerably across the Base community when applying this solution.

P.S. This reminds me of my days with 'Unison' (file synchronizer) where slashes (single vs double AND forward vs back-slash) made all the difference. Perhaps this is common among Windows/Unix-compatible command-line driven software.

P.P.S. fredt if you're monitoring, we could use a variable similar to H2's '~' which leverages the USERPROFILE system variable to determine the user path.

Likewise, OpenOffice.org developers, if you're listening, we could use default support for the \classes\hsqldb.jar (and future HSQLDB 2.0 sqltool.jar) Class Path.


Last edited by dacm on Thu Oct 21, 2010 11:33 pm; edited 5 times in total
Back to top
View user's profile Send private message
BigAndy
OOo Enthusiast
OOo Enthusiast


Joined: 03 Jan 2010
Posts: 150

PostPosted: Sun Jun 27, 2010 1:25 am    Post subject: More then simple path Reply with quote

Continuing investigating HSQLDB and OOO behavior i reached next target.
Everybody knows that when in linux set system variable
OOO_FORCE_DESKTOP to "kde" or "gnome" than OOO can open/save remote files via ssh/ftp/smb protocols via KIO/GVFS/
Simply enter in open dialog url such as
Code:
ssh://[user]:[password]@hostname_or_ip:directory


I tried to input same string into database open
(5) Datasource URL:
Code:
jdbc: hsqldb:file:ssh://myhost:~/wanted_database_name;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

Pressed "Test Connection" button and got correct username and password input dialog (if <myhost> is ommited in dialog appears one more field "Hostname".

Now we got secure connection to remote HSQL Database
Also anybody can use http://, ftp://, smb:// -type URLs.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2, 3, 4, 5, 6  Next
Page 1 of 6

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group