LO BASE and HSQL DDL

[clh3331]

I’m a beginner with LibreOffice BASE but not a newcomer; I have years of experience in several SQL dialects and worked for a time as MSAccess application developer. But I have found my progress with BASE to be confusing with a lot of trial and error.

I read in the documentation (and I apologize for any misunderstanding) that BASE uses HyperSQL for its “native” database storage. HSQL is described as a collection of JAVA routines dedicated to database manipulation. BASE uses this to produce an .odb file much the same as an ISAM database, expanding into memory on opening and compacting back to disk on closing.

To familiarize myself I created one such HSQL database recently. My objective was to try to “import” schema, data or both from another (MSAccess .mdb) existing database. Having exported the .mdb’s table schema using mdb-tools’ utility mdb-schema I was able to recreate the two .mdb tables in the .odb file using the SQL facility under the Tools menu. I had to finesse the syntax but I succeeded eventually in constructing the proper DDL, partly thanks to the table in Appendix B of the BASE User Guide.

Now I have two empty tables in my .odb to which I would like to import (transport; insert into) existing data, but some of the data presents challenges: Some of the .mdb fields were defined as “memo”, to use the old term, which equates to “text” or “varchar” in other DDL schemes. The problem is that free-form entries in these fields employed numerous CR/LF entries as part of the field’s text, but this combination is usually the default marker for end-of-record. I exported these records using “|” as the field separator and “~” as the record separator.

Now I have to find an import utility that will allow me to 1) perform a bulk import from an external source while 2) allowing me to specify alternate field and record terminators. Mariadb has this option, but this is not a Mariadb DB. The HSQL library itself does not offer such an option, so far as I am able to determine from documentation. There is however a JAR file, SqlTool, that claims this ability. Reading that documentation, and the help files in the .jar itself, I am so far out of my knowledge that I literally have no idea how to proceed.

So here I am, asking for advice on how to proceed. I realize there are other options, some of which I have already explored and completed. My question is specific to the BASE / HSQL combination.

Sorry this is so windy… Thank you for your replies.
-CH-

[Wanderer]

@clh3331

CR/LF entries as part of the field’s text, but this combination is usually the default marker for end-of-record.

This “should” be handled by using quotes " around text for csv-files, but I have to admit, not all export/import filters can handle this. Did you try?
.
I’d usually try to connect Base to your Access-db, then drag the table to Calc. Next step would be to copy all this from Calc to your newly created HSQLDB-table.

[Villeroy]

The database which is embedded in a Base document is HSQL 1.8 (year 2006) which is still documented here: Chapter 9. SQL Syntax
About text files: Chapter 6. Text Tables
The other chapters are not relevant for embedded databases. Like all the other office documents, a Base document is a zip file. The HSQLDB resides in folder database/ within the zip file. As soon as you try to access the embedded db, it is extracted to a temp. dir. When you close an edit session, the database is zipped back into the document. This makes the whole thing prone to data loss, causes performance issues and lack of important features due to the age of that software. It’s good enough for demos and educational purposes.
It is fairly easy to extract the embedded database, and run it stand-alone with the latest version of HSQL. I like later versions of HSQL very much. I do even run it in server mode. However, the Base frontend is best adjusted to the embedded HSQLDB.

a) In order to “convert” some Access-DB, you connect a Base document to the Access-DB, connect another Base document to the target DB, then copy table icons from the source db to to the target db. This may lead to compatibility problems between the database engines. IMHO, best practice is to paste data only into ready made tables of the target database. Copy source table, select icon of target table, paste, choose “append data”, in a second step you can map the right columns to each other.

b) Sometimes, a spreadsheet comes handy as an exchange medium when dealing with simple data types (dates, times, numbers, text). Just format any dates and times according to ISO standards before copying sheet cells to a target database. A spreadsheet allows for simple consistency tests and dragging around columns.

It may be a good strategy to copy your access data into an embedded HSQL, do the fine tuning of the data definitions and convert that thing into a stand-alone HSQLDB which can be accessed in “file mode” or in server mode.
Embedded: One allmighty user “SA”:

File mode: Users and groups are supported, but only the first user has write-access…

Multi-user access to a HSQL server:

P.S. Yes, Base is simplistic and tricky, sometimes buggy. It is not a multi-billion dollar product. Base is a comparatively small addition to this office suite.

[clh3331]

“So here I am, asking for advice on how to proceed. I realize there are other options, some of which I have already explored and completed. My question is specific to the BASE / HSQL combination.”

Thank you all for your responses. As you might guess, my Linux administrative skills are rudimentary.

I have already accomplished the following: In addition to extracting the data using mdb-tools, by using BASE and a suitable driver I have connected to the .mdb, creating a “hybrid” .odb, and using dbeaver and a suitable driver I have a “connection” to the tables through that database manager. I have done the same with mariadb, creating a local server, user account and importing the data into mariadb tables which I can modify or query. I even have installed the MS Office Enterprise 2007 which i can run under WINE and access the .mdb thereby (imperfectly; there are missing references).

The challenge lies in finding a Linux form tool and report tool that equal the power and flexibility of MS Access. Say what you like (and a lot of people have a lot to say) about Access’ shortcomings, these areas of the product are quite usable; i.e. adaptable to many and varied tasks.

I have tried to find similar utility in the available Linux / Fedora seam, and settled on BASE as my best alternative (to KEXI, OpenOffice, etc.) I have been “fiddling” with this software since it was StarOffice included with RedHat Fedora 7 but as Villeroy observed it has its limitations. There is the added difficulty that Fedora has its own way of distributing software and organizing the directory structure, and a penchant for following new trends, both of which are confusing to a complete tyro such as me.

But since HSQL and JAVA are now the defacto standards for Fedora’s newest releases I am taking the admittedly difficult task of learning what I don’t know in several areas in hopes that I can make intelligent decisions going forward. HSQL syntax, SqlTool, .rc files, Java .jar files; all new to me. I’m not only looking to “get there from here”; I want to “get there frome here by this route.” Or die trying.

Thank you again for your responses.
-CH-

[fpy]

@clh3331

import utility

(sort of guessed that was your question, although not sure how it relates to the title of your post )

@clh3331

how to proceed

• Importing and Exporting Data in Text Format
• How to link a calc spreadsheet to a base database?

[Wanderer]

@clh3331

I exported these records using “|” as the field separator and “~” as the record separator.

From this situation, I would pre-process with an editor like Notepad++ and replace the existing linebreaks in the notes with a text-marker like _CRLF_, then restore your ~ as line-breaks. The result should be easily imported as csv to calc or as Text-table in HSQLDB.
.
Then replace the marker in Calc or Base.
.
In some cases on Windows I can even diferentiate between a single LF (inside a field) and CRLF as record-separator, wich makes editing easier.