Migrating from Base+dBase to Base+HSQLDB 2.x

[DACM]

New thread spawned from bartjeman's post: http://user.services.openoffice.org/en/ ... 54#p216954

...convert my dBASE tables to HSQLDB...Is there a CSV import utilty/script for HSQL?

You might be able to drag&drop your Tables between two instances of Base ( Base+dBase and Base+HSQLDB 2.x ). Otherwise, take a look at these links and specifically those under the heading "Import the data as a native database Table:"
http://www.oooforum.org/forum/viewtopic ... 854#460854

...your multi-mode link...I'm not sure if I just need to install hsqldb2_OOo.exe or if I am also req'd to install hsqldb2_mydb.exe after that?

hsqldb2_OOo.exe is a Windows installer that installs HSQLDB 2.x (currently version 2.2.7) and configures Office to utilize the newer database engine. This disables the built-in HSQLDB 1.8 engine and therefore disables support for legacy "embedded databases".
hsqldb2_mydb.exe optionally installs a couple of Base files (.odb) to a dedicated 'databases' folder for the current user. These are pre-configured to work with HSQLDB 2.x in the various file/server modes of the HSQLDB engine, so they will save you considerable research and setup time. They will also direct HSQLDB to create a new 'multi-mode' database named 'mydb' as necessary. You may skip (de-select during installation or later delete) the file-mode version (mydb.file.odb) since you're targeting a multi-user environment. Here's some additional notes from the tutorial:
[quote="[Tutorial] Avoid data loss by avoiding "Embedded databases""]

The Quick Fix includes two, pre-configured, Base front-end files (.odb) -- one is configured for 'file mode' access and the other for 'server mode' access. So you can develop in either mode and switch modes as necessary by changing the 'Data-source URL'. This can be done using copy-&-paste of the URL string between these .odb files open in Base: Edit > Database > Properties.

'File mode' is seamless, allowing us to click directly on the Base (mydb.file.odb) file for startup. But 'server mode' requires separate HSQLDB startup for Table access. So it becomes a two-step process: (1) start HSQLDB in 'server mode' (2) and click mydb.server.odb to open the front-end in Base. This process should be automated using a script/batch file. For example, the provided Windows script file ('START.vbs') automates all aspects of 'server mode' through a single mouse click. But if you're using LibO or non-default folder-paths for OOo or Java, then the associated Windows batch files (server.start.bat and server.stop.bat) will require some tweaking to reflect your folder-paths.

Note that each computer involved in 'server mode' database-access must be configured with this 'multi-mode' HSQLDB support. This can be accomplished on Windows PC's by running the 'quick fix' (mydb.exe) or HSQLDB 2.x installer (hsqldb2_OOo.exe) as applicable, on each machine. Otherwise follow the manual steps available at the 'Quick FIx' link below.[/quote]

Be prepared to utilize some workarounds when using Base to create and adjust Table structures with a JDBC/ODBC database connection, as mentioned in the tutorial's 'related links' section:
[quote="[Tutorial] Avoid data loss by avoiding "Embedded databases""]

The Frozen 'Field Property' Workaround (adjusting Table structures with Base including AutoValue Primary Keys and Default values)[/quote]

And don't miss the following multi-user links from the tutorial's 'related links' section:
[quote="[Tutorial] Avoid data loss by avoiding "Embedded databases""]

Running a "multi-mode database" in a multi-user environment (MS Windows oriented steps with graphics)
Server Mode Startup, Shutdown and Authentication Options (Windows-specific discussion with wider application)
Client Computer Setup (when using 'server mode' with networked computers)[/quote]
...

[bartjeman]

Thanks for the detailed response DACM! I appreciate the time you have spent on this!

[bartjeman]

Hi DACM
I'm just getting around to the 2.x setup
When you say hsqldb has to be started in server mode, I am a little confused.
My server is a linux box. I would hope that I can store the tables there without requiring any software installation on the server.
I would hope that starting in server mode is something we do on the client?
Please let me know
Kind Regards

[DACM]

My server is a linux box. I would hope that I can store the tables there without requiring any software installation on the server.
I would hope that starting in server mode is something we do on the client?

Well I hate to disappoint, but the Linux box will require Java installation (preferably 1.6 version 22 for Linux), the individual 'hsqldb.jar' file to manage access to your database files -- all located on the server. You'll also want a script/batch file on the server that starts HSQLDB in server-mode. And it may be best to store your Base .odb front-end file on the server as well for central access from the clients, but that's up to you.

Your clients will require Java, OOo/LibO, and support added to Base for HSQL external databases (file/server "multi-mode databases"). This "support" can be installed automatically using the available installer for Windows clients -or- setup manually (Linux, OS X, or Windows) in Base by setting the Tools>Options>Java>Class_Path to a local copy of hsqldb.jar. Just make sure the same version of hsqldb.jar is used on the server and each client. This local "support" provides several (JDBC driver) functions which allow you to communicate with the HSQLDB server hosting your database on your Linux server. With that setup, the client would simply need a shortcut to the central Base .odb front-end file, which then starts Base locally, but with access to the central database.