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

How To: Replace HSQLDB with H2 embedded multi-user
Goto page 1, 2, 3  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Mar 14, 2010 12:16 pm    Post subject: How To: Replace HSQLDB with H2 embedded multi-user Reply with quote

The following post describes the steps necessary to replace the HSQLDB RDBMS in Base with the H2 RDBMS.

BACKGROUND: The Base integration of HSQLDB is designed for easy distribution of OpenOffice.org (OOo) data-source's and for complete database-applications packaged in a single zip file (.odb). This is quite convenient, but this default setup (1) limits database access to a single-user; (2) it creates data-versioning issues for ongoing development; (3) and it incurs significant risks to the data due to OOo instability. The quick answer is to migrate to a traditional configuration with HSQLDB which separates the database files as necessary for data robustness. This works, but H2 combines the advantages of both modes (file and server modes) -- which include seamless server startup and multi-user access -- in a single configuration. Head-to-head, H2 is compares favorably with HSQLDB to include the latest version (HSQLDB 2.x) where it generally matches HSQLDB feature-for-feature. Perhaps one notable distinction is H2's ability to utilize a variety of linked tables. And as a bonus, H2 has exceptional documentation with integrated search, and it's a portable Java application just like HSQLDB!

IN SUMMARY, Base+HSQLDB is a very convenient, out-of-the-box solution for single-file, single-user Data-sources and applications. But if you need the advantages of: client-server or database file separation, while gaining some speed advantages over enterprise RDBMS's (MySQL/Firebird/PostgreSQL), then H2 is worth a serious look. It's a one-time ~10 minute process. There's no server to start, you get separation of the RDBMS and .odb file for robustness, and you've gained multi-user access should you ever need it. A quick primer on migration from HSQLDB to H2 follows the setup steps below.


Noel L wrote:
Yes, I would like the 5 steps for creating a non-embedded database.

Okay, but please be aware that the ability to adjust Column properties through right-click-GUI steps is currently disabled in ooBase for JDBC connected RDBMS's. This issue does NOT affect the creation, copying, or deletion of Tables using the GUI / Wizards. But during table creation, GUI Column-editing becomes disabled on a column-by-column basis upon saving each column as you work. EDIT: You can effectively workaround this issue using the Table "copy" functions of the Base GUI.So the remaining GUI functions and workarounds may prove sufficient for most casual designers, and keep in mind that once you've added data to a table it's difficult to make changes that require data conversion or affect key-relationships. Otherwise, consider a professional-grade tool when creating or adjusting Table structures like: SQuirreL or SQL Workbench/J. Or there's always SQL...

See: ALTER TABLE ALTER COLUMN
See also: ALTER TABLE RENAME
Example:
ALTER TABLE "Customers" ALTER COLUMN "CustomerID" BIGINT NOT NULL IDENTITY

And Note: “Changing the dataType fails if the data can not be converted.” I've only seen one failure as I tried to convert from DECIMAL (PRECISION, SCALE) to BIGINT, which makes sense but some RDBMS's simply round for conversion in this case.

Also, make sure you're aware of ' View > Refresh Tables... ' in Base when using SQL to modify things.


Let's get started...

Here's the steps:
(1) Download H2.zip (or the "Last Stable" version) and extract the H2*.jar file from the h2/bin folder within the zip file, placing it in the folder of your choice.
Note: This multi-function file is required on all client computers as well, since it provides "Driver Class" connection functionality for the Base front-end (see Steps 2 & 3 below; specifically Step 3c).
Note on Migration from HSQLDB to H2: If you choose the ‘ classes ’ folder within the OpenOffice folder structure, then create a new sub-folder and place the file inside there (C:\Program Files\openoffice\Basis\program\classes\h2\h2.jar). This alternate sub-folder seems to enable the ability to select the h2*.jar file in Step 2c (below) without conflict with hsqldb.jar. This then allows you to perform drag & drop migration between two instances of Base, with one instance running HSQLDB (embedded or server) and H2 (any mode) in the other instance.
(2) Start Base and setup the JRE and ClassPath to the h2*.jar ‘Archive’ file:
(2a) Select ' Tools > Options > OpenOffice.org > Java '
(2b) Select a JRE installation
(2c) Select ' Class Path... > Add Archive... ' and browse for the h2*.jar file (h2-1.2.131.jar)
(2d) Close Base including the OpenOffice Quick Starter
Note: If you're using OpenOfficePortable (v3.1.1 or current), make sure you read and understand this workaround necessary to make this setting (JRE & Class Path) persistent between OOo restarts.
(3) Restart Base and “Connect to An Existing Database"
(3a) Select JDBC
(3b) Enter the Datasource url = JDBC:h2:~/mydb
Note: This creates an empty database named 'mydb.h2.db' in your %USERPROFILE% folder or equivalent. Or designate another path or relative path and/or change the database name.
Note: This 'mydb.h2.db' file contains your data and should be backed-up regularly. A closed database can be backed-up automatically using synchronization software (try: Toucan Portable). See also the 'Side Note' in this post which also covers data durability and security.
(3c) Enter the jdbc Driver Class = org.h2.Driver
(3d) Test Class with the button and if successfully loaded, Select 'Next'
(3d) Enter the User name = SA
(3e) Test Connection with the button and if successfully established, Select ‘Finish’
(3f) Save your .odb file and make a copy so you have a template for future projects.
(4) Add optional settings to your Database URL
(4a) Select Edit > Database > Properties > Datasource URL
(4b) Add the following as necessary to the Datasource url = JDBC:h2:~/mydb;IFEXISTS=TRUE;AUTO_SERVER=TRUE;MVCC=TRUE
Note: ' ;IFEXISTS=TRUE ' seems like a good idea after the empty database is created
Note: ' ;AUTO_SERVER=TRUE ' mixed-mode allows mutli-user or multi-application access
Note: ' ;MVCC=TRUE ' if serving more than ~10 high-concurrency connections, but only as necessary due to slight overhead

(4c) Save the .odb.
Note: This is your Base front-end or OpenOffice Datasource file. As a front-end, the file contains connection settings plus all forms, queries, reports and macros. As an OpenOffice Datasource, the file must be "registered" in OpenOffice for use with Writer, Calc or Impress. This file contains no data and may be distributed to network client computers for simultaneous access to the same database.
(5) Enjoy!!!

Note: With mixed-mode (AUTO_SERVER=TRUE) you immediately have multi-application access on the same computer. For multi-user access on networked computers, each client computer requires a copy of the .odb (front-end / Datasource) as well as a copy of the H2*.jar file from Step 1 above to provide driver duties. And, of course, OpenOffice and Java are also necessary on each client computer. If you create the same folder/path structure on each client (highly recommended) then you can distribute the same .odb file to all clients. Your client .odb file will require a "file share URL" which reflects the network path to your database file.
Step 4 above with a file-share URL would look something like (notice the double-slash):
JDBC: h2:\\ComputerName\Public\My Databases\h2\mydb;IFEXISTS=TRUE;AUTO_SERVER=TRUE
You may need to run through various steps above to setup/test your first client before distributing copies of the final .odb file to other clients.


-----------------------------------------

Migration primer...

Noel L wrote:
When you say I need to start from scratch, I take it that the process creates an empty non-embedded database and no Forms, Queries or Reports.

Correct. It’s a clean slate with an empty database file and empty .odb file.

Noel L wrote:
Is it possible to import the Forms, Queries and Reports from my existing .odb file?

I think so. I’m having some good success with drag & drop between instances of Base, but there's some issues. For instance, you’ll need to re-select the table(s) associated with each form/sub-form based on your newly named/created table(s) [see the SCHEMA note below]. And I’m having trouble with SQL-based Form Filters at the moment due to Schema issues. Queries migrate fine through drag & drop but you'll have to re-type the proper name in the popup Wizard (as of OOo 3.1 as sliderule has mentioned in the past). I haven’t gotten to Reports migration…yet. And macros may need adjustments after migration as well, but so far so good.

Noel L wrote:
Likewise could I import existing data into the new non-embedded database?
Well…yes…but with some extra steps. For instance, you can drag & drop but will need to step through the popup table Wizard (using ‘Next’ button) while adjusting the Table Name and individual Column properties. The Name must include the Category (database FILENAME) and Schema (PUBLIC unless you Create other Schema) in all caps separated by a dot (example: MYDB.PUBLIC.Table1). Auto-increment Primary Key Columns seem to require “ BIGINT [IDENTITY] ” dataType. That means you’ll have to change all Foreign key Columns to BIGINT [BIGINT] across all Tables to match the dataType [BIGINT] but without the auto-increment feature of a Primary Key [IDENTITY]. NUMERIC Columns with decimal precision apparently must be changed to DECIMAL dataType. And perhaps more issues, but my tables migrated successfully without additional issues/changes. Remember to setup 'Tools > Relationships' after your tables are in place.

SCHEMA NOTE: H2 implements industry standard "database schema" including the default "PUBLIC" schema, and also the "INFORMATION_SCHEMA" which is intended for internal database use. Think of database schema as a single-level folder structure nested under the database filename, where the folder names reflect schema names, and where the folders contain sets of database objects (tables, etc) which are separated in this manner for logical or security reasons. You can use SQL to CREATE SCHEMA and you may want to for more seamless migration from HSQLDB, because the default schema in HSQLDB is SA reflecting the default username. But I recommend migrating to the PUBLIC schema (folder) in H2 because it's standard and makes more sense in a client-server environment.

-------------------------------

DISCLAIMER: H2 and the latest HSQLDB 2.x have new architectures that should ensure their development longevity while providing many of the latest RDBMS features. For instance, both now provide enhanced multi-user concurrency (with MVCC) which extends their reach in high-concurrency environments. HSQLDB 2.x even sports a fully-threaded engine. But even before these latest advances, real-world applications and testing have generated sufficient confidence up through 10 high-concurrency connections, as well as, multi-Gigabyte databases with millions of rows. And keep in mind that a single, data-intensive, test connection might be the equivalent of dozens of actual users in terms of database reads/writes per second. So H2 or HSQLDB-2.x will easily support an office, school, small business, or even most web sites without breaking a sweat.

But the fact remains that popular web sites (extreme high-concurrency READS) and data warehousing applications (extreme high-concurrency WRITES) are the proven domain of enterprise-level RDBMS's, perhaps like MySQL/Firebird/PostgreSQL. And different RDBMS's deliver differing levels of security, robustness, standards-compliance, features and limitations -- not to mention licensing, deployment and support costs. MySQL is the least capable of the so-called enterprise-level RDBMS's, but many people are impressed with it's ease of setup, while others blast it for SQL incompatibilities, licensing requirements and data integrity risks. But MySQL is legitimately a great choice for web site development, if for no other reason because it is the best supported and most popular in that category. Firebird and PostgreSQL excel in more conventional RDBMS applications where transactional integrity, scalability, standards-compliance, favorable licensing and security are the top priorities.

So while H2 is particularly well-suited for seamless integration with Base, adding flexibility and maintaining top-tier speed, there are certainly some applications where you'll need to leave the current crop of Java-based RDBMS's behind. But that's rare, or involves existing databases. In most cases, when creating a database project from the ground-up with Base, you'll be hard-pressed to beat the overall advantages of H2 or HSQLDB 2.x. The speed, cross-platform support, portability (USB/cloud), SQL-compliance, and depth-of-features (MVCC, custom functions, stored procedures, encryption, LOB support, etc.) are a rare combination.


Last edited by dacm on Mon Sep 05, 2011 10:44 pm; edited 72 times in total
Back to top
View user's profile Send private message
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Mon Mar 15, 2010 12:13 pm    Post subject: Reply with quote

Thank you very much for your post! It looks so easy. Is it really that easy? And the feature comparison of H2 looks really promising: You easily get multi-user access and encryption. I must give it a try.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Mar 15, 2010 2:35 pm    Post subject: Reply with quote

I've already opened several copies of the same .odb (renamed) accessing a single database on a single machine. I'm anxious to test H2 in a network environment because I'm skeptical of the common URL claim with AUTO_SERVER; I may be mis-interpreting that feature.
[Edit 3/31/2010: Mixed-mode works on a network!!! using a "file share URL" such as:
jdbc: h2:\\ComputerName\Public\My Databases\h2\mydb;IFEXISTS=TRUE;AUTO_SERVER=TRUE
I'm testing with multiple computers with full read/write access to the database from all computers simultaneously; all computers running Windows 7 64-bit; OOo 3.1.1; Java 1.6.0_18, h2-1.2.131.jar ]


But as features go, you can replace HSQLDB 1.8 with 2.0RC today and get much closer to H2's feature set including encryption. But without an 'auto-server' function, HSQLDB is simply not the best choice for Base client-server integration [IMHO].

I'll be interested to hear from your experience with H2.


Last edited by dacm on Wed Mar 31, 2010 10:46 am; edited 1 time in total
Back to top
View user's profile Send private message
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Sat Mar 20, 2010 10:32 am    Post subject: Reply with quote

dacm wrote:
I'll be interested to hear from your experience with H2.

I gave your five steps to use H2 in OOo Base a try today. And it was really that easy as you described it! Although I needed a little more than 5 minutes.

To see if I really can establish multi user access to a H2 database I tried to connect to it from NetBeans IDE 6.7.1 Database plugin. And it worked! I entered records in a new table in OOo Base, I could see them in NetBeans IDE, I entered new records in NetBeans IDE, and I could see them (after refreshing) in OOo Base.

I can now open an ODB file using a H2 database, use the same database at the same time (or whenever I want) and I never had or have to start a database server. So it feels like an embedded database, but it allows multi user access. But it differs in the number of files you get: If you use HSQLDB in embedded mode (the OOo Base default), all your data together with all the queries, forms and reports are stored in one file, the ODB file. Using H2 as described above, you data is stored in a h2.db file and the queries, forms and reports are stored in the ODB file. In my opinion this is another benefit.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sat Mar 20, 2010 11:40 am    Post subject: Reply with quote

hol.sten wrote:
...it was really that easy as you described it! Although I needed a little more than 5 minutes.
Okay, I didn't mean to understate the time involved. It took me about 1 hour the first time but without a tutorial and I had to read some of the documentation and compare notes with HSQLDB. After making the tutorial I ran through it in about 3 minutes using the links and copy/paste, so I posted 5 minutes -- but that may be unrealistic because it would probably take me more than 5 minutes some months from now. But remember to make a copy of your .odb file so you can use it as a template for any future projects using H2 without running through the tutorial-steps again.

hol.sten wrote:
To see if I really can establish multi user access to a H2 database I tried to connect to it from NetBeans IDE 6.7.1 Database plugin. And it worked!...
Was this on the same computer or networked?

hol.sten wrote:
But it differs in the number of files you get...In my opinion this is another benefit.

Yes, I agree, a single-file Base database has some advantages but it...
Quote:
...limits database access to a single-user; it creates data-versioning issues for ongoing development; and it incurs more risks to the data due to OpenOffice stability.

As with any client-server configuration the database file(s) (.h2.db file in this case) should be backed-up regularly perhaps using Toucan's commandline support or Allway Sync-n-Go with a task scheduler.
Theoretically, you can distribute the .odb to any network computer and get seamless access to the H2 database. And as you make changes to the .odb file (forms, queries, reports, indexes, etc.), you can distribute the new .odb among clients without affecting the running H2 database.


Last edited by dacm on Wed Mar 31, 2010 10:45 am; edited 3 times in total
Back to top
View user's profile Send private message
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Sat Mar 20, 2010 12:25 pm    Post subject: Reply with quote

dacm wrote:
hol.sten wrote:
To see if I really can establish multi user access to a H2 database I tried to connect to it from NetBeans IDE 6.7.1 Database plugin. And it worked!...
Was this on the same computer or networked?

Yeah, good point. I tried it on the same computer. But I'll try again over a network connection...
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Mar 21, 2010 2:21 am    Post subject: Reply with quote

I can confirm that the backend part, connection, tables, indices, work lightning fast.
I have a too large embedded HSQLDB where I try to sync the contents of 3 databases partially. The odb file amounts to ~70MB.
My main tool set consists of some queries with lots of calculated fields, a form with lots of subforms and a small macro where I can link record sets manually (~15000 records require a little bit of "fuzzy logic").

First I had to create a schema before I was able to copy all the tables into my new H2DB and recreate the indices and relations.
The resulting h2.db amounts to 120MB. Loading the tables and queries meets my expectations perfectly. Every record set pops up within seconds and closes imediately.
In the oversized HSQLDB the tables and the whole database file need a lot of time to close. Working row by row, looking up a dozend of related rows, works at acceptable speed (1-2 seconds)

The queries did run after I loaded them into the parser for editing. Seemingly, the parser adds the Server.Schema prefix to all the table names.
Working with non-trivial forms and queries is a pain because only parsed mode queries deliver writable record sets. In direct mode everything works smoothly, in parsed mode Base stumbles around with the alias names until I write everything with maximum verbosity.

Code:
SELECT "T"."Field" AS "F"
FROM "DBNAME"."SCHEMA"."Table" AS "T"


Table alias "T" does not exist, so I use this one with many more (calculated) fields:
Code:
SELECT "DBNAME"."SCHEMA"."Table"."Field" AS "F"
FROM "DBNAME"."SCHEMA"."Table" AS "T"


Now I switch back to my old bloaty because in this H2DB Base can not deal with subforms linked through calculated fields and I need to get work done.
For instance I need to compare things like
DATEDIF('dd',"DBNAME"."SCHEMA"."Table1"."Date","DBNAME"."SCHEMA"."Table2"."Date")AS "DD",
LOWER("DBNAME"."SCHEMA"."Table2"."Surname")AS"SN" between editable form and subform and Base subforms can not link DD nor SN.

EDIT:
I used some large spreadsheets to clean up dirty csv before I import to the database.
My new H2.db does not accept spreadsheet dates from clipboard, neither ISO-strings nor formatted numbers.
Workaround: Define database ranges, connect another Base file to the spreadsheet and copy from ods-Base to h2-Base.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
hol.sten
Super User
Super User


Joined: 14 Nov 2004
Posts: 3533
Location: Hamburg, Germany

PostPosted: Sun Mar 21, 2010 6:06 am    Post subject: Reply with quote

hol.sten wrote:
dacm wrote:
hol.sten wrote:
To see if I really can establish multi user access to a H2 database I tried to connect to it from NetBeans IDE 6.7.1 Database plugin. And it worked!...
Was this on the same computer or networked?

Yeah, good point. I tried it on the same computer. But I'll try again over a network connection...

Ok, now I gave a connection over the network a try. It didn't work that easy with the Automatic Mixed Mode. As you can read in H2's feature list, both computers need access to the same files. Explicit client/server connections (using jdbc:h2:tcp://computerone for example) are not supported in Automatic Mixed Mode. So I didn't give that a try.

To get started I created a SAMBA share on computerone for the directory ~/H2/database and named it h2database. On computertwo I mounted this share through smbfs (after installing the necessary package smbfs via sudo apt-get install smbfs) with the command:

sudo mount -t smbfs -o username=MYUSER,password=MYPASSWORD -o uid=MYUID -o rw //computerone/h2database /home/MYUSER/H2

Now I had on computerone and computertwo the directory ~/H2/database pointing to the same files. I can now create on both computers H2 databases that can be accessed from the other computer. The only problem I could not solve was to get access to one H2 database from both computers at the same time. All I can do is to access a H2 database from computerone with OOo Base and NetBeans IDE and DbVisualizer at the same time. After stopping all applications on computerone I can access the same H2 database from computertwo with OOo Base and NetBeans IDE and DbVisualizer at the same time. But to access a H2 database from computerone with for example OOo Base prevents (at least on my computers) access from computertwo with for example DbVisualizer.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Mar 21, 2010 9:23 am    Post subject: Reply with quote

Thanks for an in-depth test of H2 with Base!
Villeroy wrote:
First I had to create a schema before I was able to copy all the tables into my new H2DB and recreate the indices and relations.

Was PUBLIC not available, or did you prefer to create SA for more seamless drag&drop? Or did you create a different schema?

Villeroy wrote:
The queries did run after I loaded them into the parser for editing. Seemingly, the parser adds the Server.Schema prefix to all the table names.

That's good because Base seems to ignore schema in some cases. For instance, I haven't found a work-around for SQL form filters due to incomplete schema support -- I conclude due to the error dialog popup which doesn't prefix the schema properly.

Villeroy wrote:
Working with non-trivial forms and queries is a pain because only parsed mode queries deliver writable record sets.

Is this a Base "issue" to be fixed someday? I've seen various forum threads that can't write through queries to the underlying table. Does this happen with HSQLDB embedded and/or server-mode as well?

Villeroy wrote:
In direct mode everything works smoothly, in parsed mode Base stumbles around with the alias names until I write everything with maximum verbosity.

Would you say these are expected shortcuts that H2 should support? Did you try MODE=HSQLDB in the connection URL? There's something about aliases in the compatibility mode documentation for H2 with respect to HSQLDB.

Villeroy wrote:
Now I switch back to my old bloaty because in this H2DB Base can not deal with subforms linked through calculated fields and I need to get work done.

Would this work with any client-server RDBMS or perhaps JDBC-driver specific configuration? In other words, is this an "issue" for the devs?

Villeroy wrote:
Workaround: Define database ranges, connect another Base file to the spreadsheet and copy from ods-Base to h2-Base.

I wonder if some improvements to the MODE=HSQLDB in H2 would handle these migration issues. If I'm reading this correctly there are three separate dataType issues (date format, ISO strings, and formatted fields) associated with CSV import to H2 using OOo. I'm glad to hear there is a workaround using HSQLDB-embedded import as an interim step before importing to H2.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Mar 21, 2010 9:33 am    Post subject: Reply with quote

hol.sten wrote:
As you can read in H2's feature list, both computers need access to the same files. Explicit client/server connections (using jdbc:h2:tcp://computerone for example) are not supported in Automatic Mixed Mode. So I didn't give that a try.

Thanks ! That was a good test of Automatic Mixed Mode in a network environment. I don't think the H2 author meant to imply that an indirect file or folder share setup was necessary, but perhaps he did. But, like you, I can't think of any other way for a client to find another computer without an explicit IP address or domain-name in the Datasource connection URL. I'll do some tests when I get back after Spring Break, but it may come down to starting the H2 server manually, which isn't good because you also lose the embedded speed for the first connection.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Mar 21, 2010 1:13 pm    Post subject: Reply with quote

dacm wrote:
Thanks for an in-depth test of H2 with Base!
Villeroy wrote:
First I had to create a schema before I was able to copy all the tables into my new H2DB and recreate the indices and relations.

Was PUBLIC not available, or did you prefer to create SA for more seamless drag&drop? Or did you create a different schema?

It told me that it wants SA when I copied tables into it, so I provided what it asked for.
Quote:

Villeroy wrote:
The queries did run after I loaded them into the parser for editing. Seemingly, the parser adds the Server.Schema prefix to all the table names.

That's good because Base seems to ignore schema in some cases. For instance, I haven't found a work-around for SQL form filters due to incomplete schema support -- I conclude due to the error dialog popup which doesn't prefix the schema properly.

Villeroy wrote:
Working with non-trivial forms and queries is a pain because only parsed mode queries deliver writable record sets.

Is this a Base "issue" to be fixed someday? I've seen various forum threads that can't write through queries to the underlying table. Does this happen with HSQLDB embedded and/or server-mode as well?

If you know how it is supposed to work you can edit relations across tables. The path of success if quite narrow. You have to meet quite a few conditions to get editable row sets in forms and subforms. WIth this connection type I can not get it working. Before I try to fix this by means of filtering Basic macros I'll switch back to the slowly working HSQLDB.
At the moment there is only one Base developer left. I would not bet on the future of this Base component.
Quote:

Villeroy wrote:
In direct mode everything works smoothly, in parsed mode Base stumbles around with the alias names until I write everything with maximum verbosity.

Would you say these are expected shortcuts that H2 should support? Did you try MODE=HSQLDB in the connection URL? There's something about aliases in the compatibility mode documentation for H2 with respect to HSQLDB.

MODE=HSQLDB makes no difference.
Quote:

Villeroy wrote:
Now I switch back to my old bloaty because in this H2DB Base can not deal with subforms linked through calculated fields and I need to get work done.

Would this work with any client-server RDBMS or perhaps JDBC-driver specific configuration? In other words, is this an "issue" for the devs?

Villeroy wrote:
Workaround: Define database ranges, connect another Base file to the spreadsheet and copy from ods-Base to h2-Base.

I wonder if some improvements to the MODE=HSQLDB in H2 would handle these migration issues. If I'm reading this correctly there are three separate dataType issues (date format, ISO strings, and formatted fields) associated with CSV import to H2 using OOo. I'm glad to hear there is a workaround using HSQLDB-embedded import as an interim step before importing to H2.


So this is another case of a de-facto read-only database where Base is good enough to transfer data into ODF documents and PDF without providing usable tools to edit relations.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Sun Mar 21, 2010 9:21 pm    Post subject: Reply with quote

I know this is off-topic but...
Villeroy wrote:
...Now I switch back to my old bloaty...

Have you tried to increase the performance of HSQLDB-embedded by tweaking the settings inside the individual .odb file(s)?

Script file: change the CACHED tables to MEMORY tables?
And change the Properties file to:
Code:
hsqldb.default_table_type=memory

Edit: see the following post on migration to memory tables


Or with the OpenOffice default CACHED tables...
Properties file: here's the OOo Base defaults (only ~6 MB of the .data file stored in RAM cache):
Code:
hsqldb.cache_scale=13
hsqldb.cache_size_scale=8


could be changed to the normal HSQLDB defaults (~48 MB of the .data file stored in RAM cache):
Code:
hsqldb.cache_scale=14
hsqldb.cache_size_scale=10

OR bump those HSQLDB defaults by another +1 each and you'll get up to ~192 MB of RAM cache which should be as fast as MEMORY tables for most desktop databases (<192 MB database). Although you can't jump above 64 MB of RAM cache without increasing the Java "heap" size which may not be possible when utilizing the all-inclusive, single-file database option.


Last edited by dacm on Tue Oct 05, 2010 2:36 am; edited 6 times in total
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Mar 22, 2010 1:36 am    Post subject: Reply with quote

Thank you for the suggestions.
Yes, I tweaked the Java memory settings otherwise some queries would not work. I will try memory tables later.

myself wrote:
...Now I switch back to my old bloaty...

Guess what: I found my way to use the H2 version of it.
I'm uncertain about all the options and settings, but as already mentioned, there is a lot of "fuzzy logic" and spreadsheet import involved. I need a fast desktop database for this.
My main problem is the faulty subform binding. Seemingly it has nothing to do with calculated fields (my first suspicion).
Any time I try to use more than one field, the subform can not find one.
My current work-around: Binding form and subform by means of concatenated fields "Forname|Surname|MemberNo" and more than one subform per main form.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Mon Mar 22, 2010 9:10 am    Post subject: Reply with quote

Villeroy wrote:
I will try memory tables later.

I'm not sure if it's necessary, but you might want to read this before migrating to memory tables.
Back to top
View user's profile Send private message
stanmarsh
Newbie
Newbie


Joined: 28 Dec 2009
Posts: 2

PostPosted: Fri Mar 26, 2010 6:44 pm    Post subject: Reply with quote

hi dacm,

do you know how to maintain h2 database? i looked for vacuum in h2 site but there's no vacuum command, the problem i have is the IDENTITY number keeps adding up +1 even after i deleted a record.

thanks
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  Next
Page 1 of 3

 
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