Apache OpenOffice Community Forum

I am new to LibreOffice.
Running LibreOffice v4.0.4.2 on Windows 7 Pro.

I have worked with Microsoft SQL server 2000 using Access 2000 as a front end so I am familiar with the split database concept.

I downloaded the Split_HSQLDB_Wizard_v3c.odb which I understand creates a split database.
I don't have an existing BASE file. I want to create a new one. I'm willing to create all the tables using SQL commands.

I created a separate folder called DB1. I put Split_HSQLDB_Wizard_v3c.odb into this folder.

I executed the above odb file, clicked on “enable macros” and entered the database name when prompted.
I assume it ran well, I didn't see any errors.

Q1.) I see a subfolder called “driver”. It has 2 files in it with jar extensions. Where do the actual HSQLDB files get put?

Q2.) Since I have no tables yet, what do I do now? Do I execute Split_HSQLDB_Wizard_v3c again and execute SQL commands? Each time I execute Split_HSQLDB_Wizard_v3c it asks to create a new database.
OR do I start Split_HSQLDB_Wizard_v3c and just do not click on Enable Macros?

Was I supposed to download HSQLDB from www.hsqldb.org and install it before running Split_HSQLDB_Wizard_v3c.odb?

@doncell6:

This version of the 'split' database (Split_HSQLDB_Wizard_v3c.odb) installs two folders called database which contains the
back-end files (mydb.script, mydb.properties and when you have the database up and running, mydb.data and mydb.backup)
and a folder called driver which contains the hsqldb.jar and sqltool.jar, (the database engine)
This was designed for portability between OS.
You can run the front-end *odb file from any folder on your system providing you point the url in the status
bar of the said *.odb to the folder where your back-end files are situated.

If you have created any tables, you should see them when you open the said *.odb; if not, create one now as a test
and see if the connection works. If it fails you should see an error message.

Do not enter the classpath to the hsqldb/sqltool under >>Tools>>Options>>Java>>Classpath otherwise this
Split_HSQLDB_Wizard_v3c.odb will not work.

Greengiant224

doncell6 wrote:I downloaded the Split_HSQLDB_Wizard_v3c.odb which I understand creates a split database.
I don't have an existing BASE file. I want to create a new [split database]. I'm willing to create all the tables using SQL commands.

I created a separate folder called DB1. I put Split_HSQLDB_Wizard_v3c.odb into this folder.

I executed the above odb file, clicked on “enable macros” and entered the database name when prompted.
I assume it ran well, I didn't see any errors.

Q1.) I see a subfolder called “driver”. It has 2 files in it with jar extensions. Where do the actual HSQLDB files get put?

Q2.) Since I have no tables yet, what do I do now? Do I execute Split_HSQLDB_Wizard_v3c again and execute SQL commands?

Yes, you can create a new split-database through Base using the provided SQL console -or- by utilizing the GUI Table Designer/Wizard.

We evidently missed a step in the 'Instructions for Use' for the macro-enhanced Base template. Simply open the provided template in Base and proceed to create your tables using one of the following methods...

• 1. GUI method: To access the GUI Table Designer/Wizard, click the Tables icon in Base. The 'database' subfolder and HSQL database files ( mydb.* ) are created immediately upon clicking the Tables icon in Base. You can now create your tables using the GUI Table Designer or Table Wizard. SQL and GUI workarounds only become necessary with a 'split database' if you subsequently need to modify an existing table-structure such as a field-attribute (name, datatype, default value, length, etc.). This is likely a bug or underdeveloped aspect of Base because it is possible to modify table-structures using the Base Table Designer within the context of an 'embedded database.' In any case, the Base GUI Table Designer is insufficient for many database management tasks (default values, etc.). But there's always SQL...
  
  2. SQL method: To access the SQL console, click Tools > SQL... Since you asked specifically about creating the database/tables using SQL, simply open the SQL console and begin typing SQL commands such as the CREATE TABLE command below. Press the Execute button. Then click the Tables icon (if you haven't already) and click 'View > Refresh tables' in order to see the table(s) in the Base GUI window. Behind the scenes, this initial SQL input effectively causes Base to start HSQLDB in single-user file-mode. HSQLDB then generates the 'database' subfolder and the database files ( mydb.* or otherwise ) as specified in the Datasource URL shown in the bottom status bar in Base. If the specified database ( ..\database\mydb.* ) already exists at the location specified (URL), HSQLDB will simply open the existing database by name.
  
  • Tools > SQL...

    Code: Select all

    CREATE TABLE CONTACTS
    (
    ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1),
    FIRSTNAME VARCHAR(50),
    LASTNAME VARCHAR(50),
    ADDRESS VARCHAR(255),
    CITY VARCHAR(50),
    STATE VARCHAR(50),
    ZIP VARCHAR(10)
    );

    In any case, use the Tools > SQL console to issue the following command in order to establish a 'backup' file within your 'database' subfolder as part of your split-database folder. This is necessary with new HSQL 2.x databases, but it should be possible to automate this in future template versions.

    Code: Select all

    SET FILES BACKUP INCREMENT FALSE

  3. Alternative database manager method: A split-database uniquely allows us to utilize advanced database managers. Just make sure your Base session is shutdown/closed, since only one application can access the HSQL data files in single-user file-mode operation. Think of these database managers as advanced replacements for the Tools > SQL console in Base. RazerSQL uniquely includes a GUI Table Designer, as well as, a visual SQL Query Builder, but without all the bugs and limitations of the Base equivalents. However, I'm not necessarily recommending these alternatives largely because you're on your own setting-up the connection to your HSQL database -- which requires some study and manual effort -- unlike methods 1 & 2 above which leverage the embedded macro in Split_HSQLDB_Wizard_v3c.

doncell6 wrote:do I start Split_HSQLDB_Wizard_v3c and just do not click on Enable Macros?

The macro must be allowed to run at least once in a given folder/location, and the .odb file must be subsequently saved (click 'Save' disk icon), in order to set the connection parameters for seamless [single-user file-mode] access to the associated HSQL database.

You can move the entire split-database folder ('DB1' in your case) as desired. The connection macro in the Base template (Split_HSQLDB_Wizard_v3c.odb) will handle the connection automatically (setting the current Datasource URL path and session class path). This is particularly handy when utilizing a cloud folder for automatic backup and version-history features, not the mention non-concurrent database access from multiple computers. Likewise, you can right-click the folder and 'Send To > Compressed (zipped) file' (Windows method) for distribution as a single file. So a split-database folder is truly portable and cross-platform (32-bit JRE assumed; see Java portable) when using a macro-enhanced Base template.

The macro saves the new location parameters to the template (.odb), so you can subsequently bypass or disable the macro in a static computing environment. And if you disable the macro, you can move the .odb template independent of the split-database folder -- but this is not a recommended practice in light of backup and portability considerations, *Office database path registration, and the ability to create a desktop 'shortcut' as needed.

doncell6 wrote:Was I supposed to download HSQLDB from http://www.hsqldb.org and install it before running Split_HSQLDB_Wizard_v3c.odb?

No. Everything is automatic with the Split_HSQLDB_Wizard_v3c.odb, including dedicated engine integration.

doncell6 wrote:I have worked with Microsoft SQL server 2000 using Access 2000 as a front end so I am familiar with the split database concept.

Very good. Base is not on par with MS Access in terms of ease-of-development or feature-depth. But with some effort and expertise, you can usually make a Base project function similar to Access for the end-user, and with a minimum of macros. HSQLDB 2.3.x, as integrated with this template, is much faster than MS SQL Server while providing all of the necessary desktop database function, easily up through 50 concurrent users. Beyond the desktop, HSQLDB doesn't scale well in 'stateless' web environments (comparatively) or take proper advantage of multi-proc hardware quite like SQL Server. And HSQLDB doesn't support clustering or replication. So there are situations that require a full-scale engine (and associated daily RDBMS management) such as Oracle, MS SQL Server, DB2, PostgreSQL, Firebird, or MySQL/MariaDB. But I just can't think of many of those situations that would apply to Base in a desktop/small-LAN environment -- where HSQLDB 2.x is amazingly well-suited. I mean, where else can you find a seamless, free, portable, cross-platform, split-database solution on par with Base+HSQLDB 2.3.x?