[Solved] Clarification of server mode conversion?

[AndyBlubaugh]

Hello all, and thanks in advance to anyone that can shed some light on the HSQLDB server mode situation for me.

Background:

I've made a database for a company I work for, and I would like to put it onto the company's server computer so that all five employees can access it. My questions are about the particulars of running HSQLDB in server mode, of configuring a .ODB file to work as a server, and of whether these two are, in fact, the same thing). I have read several of the forum posts and tutorials on the topic, and I've found statements that seem to be contradictory (though I think this stems from my own misunderstanding of the situation)

The database itself has been split using the excellent template and tutorial provided by DACM at this post: viewtopic.php?f=83&t=17567 All of my forms were designed in Writer and then connected to Base, and currently reside in a folder within the folder called "Dedicated Split Database" that DACM recommends making as part of the "splitting" process. The "server" computer that we use is actually the desktop computer of our office manager, with a power backup. That is to say, it is not a stripped down machine, and I have full access to it if I need to install any additional software. We are all using OpenOffice 4.0.1 on Macs running OSX version 10.8.5.

Questions:

1) Is making HSQLDB run in server mode something that is done from within the Base application, or something that is achieved through Java commands in the OSX terminal?

2) Is making HSQLDB run in server mode a change that affects only the specific .ODB file that is running? Or is it something that is done to HSQLDB across the entire system?

3) How is a specific .ODB "configured" to run as a server? Is this a different process than making HSQLDB run in server mode? Does it refer to the process of "splitting" the database? Does it refer to the process of adding the JRE class path and driver class? Or are these different configurations altogether?

4) Does running a Base database in server mode mean that there are actually TWO .ODB files running simultaneously that reference one another (such as 'mydb.server.odb' and mydb_wizard.odb')? Or instead should the content be transferred from the .ODB file that isn't "configured" as a server to the one that is?

Steps Taken So Far:

I've attempted to use the instructions provided on the Base wiki at https://wiki.openoffice.org/wiki/FAQ_(Base), but I get tripped up at the fourth step:

"Ensure HSQLDB is running in server-mode either locally or on a network computer with an accessible database folder."

Following the hyperlink in the above sentence takes me to a forum question about the topic of making HSQLDB run in server mode, which in turn points to the forum tutorial on how to split a database (which I've already done, thank you again DACM), and a link back, full circle, to the open office wiki where I started. There are also some links to Windows-specific

The forum question itself contains a sentence by DACM:

"a split-database is essentially a 'client-server' configuration. There's no inherent distinction…"

which seems to suggest that, by splitting my database, it is already functioning in server mode. However, in a different forum post about multiple databases on a single server (found at this link: viewtopic.php?f=13&t=64052) DACM writes:

"The Base template (Split_HSQLDB_Wizard_v3c.odb) is setup for single-user file-mode access only. Since that template is macro-enhanced, you'll have to disable the macro in order to setup server-mode access, "

which suggests that something must be done to the actual .ODB file to make it function in server mode.

Then, looking back at the tutorial on the open office wiki, there is a template file called "mydb.server.odb", which is pre-configured to run in server mode. I downloaded the file and tried opening it from various locations on my computer (the folder called "downloads", where it originally landed, and the folder called "dedicated split database" which I created to hold all of my database files). In neither situation did this new template connect to the database I have already made. Instead, I got the error message.

"The connection to the data source "mydb.server" could not be established. The driver class 'org.hsqldb.jdbcDriver' could not be loaded"

I've also tried looking at the documentation of HSQLDB itself, at this link: http://hsqldb.org/doc/guide/ch01.html#N10148. These instructions contain command line code which is quite new to me, and want to make sure that it is necessary before I begin sussing it out.

DACM, if you're reading this, I hope it doesn't seem like I'm picking on you, as it's quite the opposite: you have SO MUCH helpful writing that I'm just trying to understand it all.

Again, thanks in advance to anyone that can assist!

Best,
Andy

[DACM]

Andy,

Great job on your database project and on articulating the difficulties you're having with server-mode setup! The user-community has been supporting users running extracted/split-HSQL databases in both single- or multi-user modes for a number of years now. But it's a moving-target, because we're stumbling onto better ways to provide that support, and the documentation takes time and effort to reconcile with the changing technologies and associated steps. The documentation is quite current on 'splitting' a legacy 'embedded' database, or simply using a template to create a new 'split HSQL database' running in single-user 'file mode.' But the new-found ease and automation does not apply to 'server mode' for a number of reasons, largely because it's difficult to predict and automate the variables associated with multi-user environments. With that said, I hope we can clarify some things as we move through your questions...

HSQLDB server management: (starting and stopping the HSQL server application on the host computer using a batch/script file)

• 1) Is making HSQLDB run in server mode something that is done from within the Base application, or something that is achieved through Java commands in the OSX terminal?

  While a Base .odb file is used to start/stop the HSQLDB engine in single-user file mode, Base is not involved with starting HSQLDB in server mode. We typically employ batch or script files to issue the necessary start-up and shutdown [Java] commands from a command-line console/terminal window. You can also use javascript for this task. For OS-X or Linux, you may need to adapt the Windows batch files (server.START.bat and server.STOP.bat). You basically need one command string to start the HSQLDB server application, and another to shutdown the server.
  

  2) Is making HSQLDB run in server mode a change that affects only the specific .ODB file that is running? Or is it something that is done to HSQLDB across the entire system?

  Once you start HSQLDB in server mode using a batch/script, it begins listening for database access requests on a port. You could theoretically run multiple HSQLDB servers on different ports, but you should first exhaust the multi-database capabilities of a single server-mode instance. The Base .odb file must be configured to access the HSQLDB server either locally (on the same machine as the running HSQLDB server) or remotely using the IP address or computer-name of the HSQLDB host computer. On a LAN, the IP address of the host-server will typically be assigned by the router (subject to change each time the router is reset). You can setup a static IP address for the host-server machine, or it may be easier to use the host-server's computer-name in the Datasource URL (as outlined below).

Base .odb file configuration: (a Base .odb file must be setup for server-mode access)

• 3) How is a specific .ODB "configured" to run as a server? Is this a different process than making HSQLDB run in server mode? Does it refer to the process of "splitting" the database? Does it refer to the process of adding the JRE class path and driver class? Or are these different configurations altogether?

  A Base .odb file is configured for HSQLDB server mode access using the 'Edit > Database > Properties... > Datasource URL' setting.
  NOTE: This Datasource URL setup process within a Base .odb file (front-end connectivity) is distinct from starting HSQLDB in server mode (back-end database server management tasks) as discussed above.
  
  • mydb.server.odb is configured to access a running HSQLDB database running in server mode, with both applications running on the same machine (in separate processes). In other words, the Datasource URL stored within the .odb file contains only 'localhost' (mydb.server.odb) which means it looks no farther than the local machine for the HSQLDB host-server.
    
    Same machine access using 'localhost':

    Code: Select all

    hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

    LAN access from client computer using the network computer-name of the host-server machine (recommended):

    Code: Select all

    hsqldb:hsql://db_host_computer_network_name/;default_schema=true;get_column_name=false

    LAN access from client computer using the IP address of the host-server machine (ultimately requires a static IP address on the host-server machine):

    Code: Select all

    hsqldb:hsql://192.168.1.123/;default_schema=true;get_column_name=false

Additional *Office (AOO/LO) setup: (exclusively for server-mode database access)

• A Base .odb file used for 'split HSQL database' access may be setup for either file-mode or server-mode access. You can switch a Base (.odb) file between the two access modes by following the steps below, while adapting the available Windows batch files into OS-X shell-scripts to start/stop the HSQLDB in server mode on the host-server, and configuring each AOO/LibO client for appropriate HSQLDB (hsqldb.jar) driver access.
  
  • (1) Disable (or changing) the autorun macro as applicable (such as with a portable template),
    • Tools > Customize... > Events tab > Open Document [event] > Remove macro [button]
    
    (2) Change the Datasource URL stored within the Base .odb file as outlined above,
    • Edit > Database > Properties... > Datasource URL
    
    (3) Set the GLOBAL Class Path in AOO/LibO to a local copy (same version) of hsqldb.jar on every computer used for server mode access,
    • Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > hsqldb.jar
      
      This step is performed on each client, as well as, the host-server computer.
      
      Perhaps simply download an appropriate engine/driver version among these internally licensed files:
      • HSQLDB 1.8.0.10: hsqldb.jar (as bundled with AOO/LibO)
        HSQLDB 2.3.1: hsqldb.jar and sqltool.jar (see the CAUTION below associated with HSQLDB 2.x)
    
    (4) The host-server computer should contain a copy of SqlTool (sqltool.jar) for shutdown purposes when running HSQLDB 2.x in server mode (HSQLDB 1.8 includes the necessary classes found in sqltool.jar); reference server.STOP.bat mentioned above.
    
    (5) Modify server.START.bat and server.STOP.bat as necessary to manage the HSQLDB server. You'll need physical access to the host-server machine in order to run these start/stop scripts (unless running remote desktop software). See also the importance of routine CHECKPOINT maintenance on a host-server running continually (in a 24/7 database availability environment).
    
    (6) Ensure the database folder on the host-server offers read/write access from each client.
  
  
  CAUTION: Step 3 will corrupt "embedded database" files if opened with the global Class Path set to HSQLDB 2.x. Actually, since a 'global' Class Path overrides even a macro-derived 'session' Class Path,' all HSQL databases will be affected by a global Class Path setup in AOO/LibO. This setting affects both "embedded databases" and "split HSQL databases" because these database files are subject to automatic upgrade when specifying a newer version of HSQLDB in the global Class Path. An upgraded "embedded database" file becomes unusable! An upgraded "split HSQL database" is normally fine, but there can be some acute upgrade-compatibility issues, and you'll then need to add a copy of the upgraded engine to your dedicated split-database folder for use without a global Class Path setup. Needless to say, global Class Path settings are a potential problem for HSQL database users. The plan is to eventually eliminate this issue with a macro-derived 'session' Class Path even for server mode operations, but I haven't taken the time to test this assumption thoroughly. Simultaneous access to "split HSQLDB 2.x databases" and "embedded databases" requires an empty GLOBAL Class Path, as well as, a macro-enhanced split-database template (.odb) -- which is currently limited to single-user mode/environments. Otherwise, use this 'global' Class Path setting sparingly, and only to support server-mode operations under normal circumstances.
  
  The copy of hsqldb.jar on each client is used for driver functions only. In other words, hsqldb.jar includes many 'classes' including the necessary JDBC driver along with the classes necessary to implement the SQL database engine; the same copy of hsqldb.jar can provide both functions simultaneously. But the database engine is run in server mode only on the host-server computer.

Splitting an Embedded database:

• For the sake of clarity, the above URL settings and HSQLDB server management tasks are distinct from the process of 'splitting' an embedded database. "Splitting" a database refers to the process of extracting an "embedded database" from a Base .odb file permanently. Base actually extracts these legacy "embedded databases" automatically, each time it runs ("opens") one. But problems arise as Base attempts to re-package the files back into a single zip-archive at file-closing, particularly while deleting the extracted session files. So we simply extract the files permanently for reliable access, as a dedicated split-database folder. Our "split database" terminology has been adopted in order to make a proper distinction from a legacy "embedded database." A "split database" refers to the separation of front-end and back-end components at the file level based on the role of these distinct applications (Base front-end; HSQLDB back-end). A "split database" may be run in single-user file mode, or in multi-user server mode, although not simultaneously. A "split database" is essentially a client-server setup due to the separation of roles (Base GUI front-end accessing a database hosted by an RDBMS back-end). But client-server tends to infer multiple users, and perhaps distinct memory processes/threads, so the terminology is best reserved for server mode environments.

File location considerations: (a Base .odb file may be located on the database host computer or on each client)

• 4) Does running a Base database in server mode mean that there are actually TWO .ODB files running simultaneously that reference one another (such as 'mydb.server.odb' and mydb_wizard.odb')? Or instead should the content be transferred from the .ODB file that isn't "configured" as a server to the one that is?

  There's only one .odb file involved, and it can be stored and shared on the host-server, if you don't require custom copies for each user. You only need to change the Datasource URL to convert a single-user (standalone file mode) .odb file to multi-user (server mode) access.

Miscellaneous Q&A:

• "Ensure HSQLDB is running in server-mode either locally or on a network computer with an accessible database folder."

  "a split-database is essentially a 'client-server' configuration. There's no inherent distinction…"

  which seems to suggest that, by splitting my database, it is already functioning in server mode. However, in a different forum post about multiple databases on a single server, DACM writes:

  "The Base template (Split_HSQLDB_Wizard_v3c.odb) is setup for single-user file-mode access only. Since that template is macro-enhanced, you'll have to disable the macro in order to setup server-mode access, "

  which suggests that something must be done to the actual .ODB file to make it function in server mode.

  Sorry for the circular hyperlinking and lack of clarity. I'll work on that over time.
  

  Then, looking back at the tutorial on the open office wiki, there is a template file called "mydb.server.odb", which is pre-configured to run in server mode. I downloaded the file and tried opening it from various locations on my computer ... I got the error message.

  "The connection to the data source "mydb.server" could not be established. The driver class 'org.hsqldb.jdbcDriver' could not be loaded"

  I suspect your immediate problems with server mode (using mydb.server.odb) are attributed to step 3 above. But if you're already running in file mode using a split-database template (.odb), then simply make a copy of that file and configure it for server-mode access as outlined above.

[AndyBlubaugh]

I had to take a break from this project for a while because of other obligations, but I'm back now, and very eager to get my beautiful database working on my server. Looks like my first step will be to adapt that windows batch file into a shell script, something I know nothing about, but that hasn't stopped me before. I'm kind of shocked that with so many users on this forum, nobody has tried to run base as a server on an OSX system (or at least, if they have, they haven't shared their shell script with us all). Rest assured, when I get mine written, I will post for all to use.

However, I have a question: how do I know if my shell script is running correctly? Will there be some kind of verification in the command line?

[F3K Total]

Hi,
starting the database in Windows gives e.g. this respond:

Code: Select all

[Server@a6d51e]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@a6d51e]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@a6d51e]: Startup sequence initiated from main() method
[Server@a6d51e]: Loaded properties from [C:\Users\...\server.properties]
[Server@a6d51e]: Initiating startup sequence...
[Server@a6d51e]: Server socket opened successfully in 110 ms.
[Server@a6d51e]: Database [index=0, id=0, db=file:INV, alias=] opened sucessfull
y in 374 ms.
[Server@a6d51e]: Startup sequence completed in 484 ms.
[Server@a6d51e]: 2014-04-10 22:20:23.146 HSQLDB server 1.8.0 is online
[Server@a6d51e]: To close normally, connect and execute SHUTDOWN SQL
[Server@a6d51e]: From command line, use [Ctrl]+[C] to abort abruptly

and with Linux exectued in a Terminal:

Code: Select all

[Server@84e3f9]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@84e3f9]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@84e3f9]: Startup sequence initiated from main() method
[Server@84e3f9]: Loaded properties from [/home/.../server.properties]
[Server@84e3f9]: Initiating startup sequence...
[Server@84e3f9]: Server socket opened successfully in 62 ms.
[Server@84e3f9]: Database [index=0, id=0, db=file:/home/..., alias=] opened sucessfully in 839 ms.
[Server@84e3f9]: Startup sequence completed in 911 ms.
[Server@84e3f9]: 2014-04-10 22:30:18.841 HSQLDB server 1.8.0 is online
[Server@84e3f9]: To close normally, connect and execute SHUTDOWN SQL
[Server@84e3f9]: From command line, use [Ctrl]+[C] to abort abruptly

[AndyBlubaugh]

Thanks for the fast reply, F3K! But, wow, that looks completely different from the "server.START.bat" file that DACM provides. Is that what I'm looking at? A batch file? I had hoped that adapting a batch file into a shell script would be a matter of changing a few commands, but I see that may not be the case. So, is what you've listed there in the linux example what I should use, since OSX is Unix based?

[F3K Total]

...that looks completely different from the "server.START.bat" file that DACM provides. Is that what I'm looking at? A batch file?

NO! That's the respond in the terminal window, which shows, that a HSQL DB 1.8.10 is running, after executing the batch/shell file! The linux shell commands look e.g like this, just adjust the paths to hsqldb.jar and your database files (mydb.script, mydb.properties ...)

Start_HSQLDB_Server_Linux.sh

Code: Select all

#!/bin/bash
java -cp /opt/libreoffice4.0/program/classes/hsqldb.jar org.hsqldb.Server -database.0 file:/home/.../mydb
exit 0

Stop_HSQLDB_Server_Linux.sh

Code: Select all

#!/bin/bash
java -cp /opt/libreoffice4.0/program/classes/hsqldb.jar org.hsqldb.util.ShutdownServer
exit 0

HTH R

[AndyBlubaugh]

OK, I spent a couple of hours with a friend that is a programmer, and we got some good progress. I've been comparing what F3K wrote in the post above, and the batch files that DACM has posted in the first response to my question. I'd love it if someone could verify my understandings of what this shell script is doing, and how to do it.

So as I see it, first the path to Java is set, with these two lines:

Code: Select all

set javapath=Java\jre7\bin\Java.exe
set jarpath=OpenOffice 4\program\classes\hsqldb.jar

And I should replace everything after the "=" with the actual path to my Jave and my hsqldb.jar files, correct?

Then the engine itself is started, with a line like this:

Code: Select all

#!/bin/bash
java -cp /opt/libreoffice4.0/program/classes/hsqldb.jar org.hsqldb.Server -database.0 file:/home/.../mydb
exit 0

So, I understand that the command "-cp" is used to copy files, so it looks like this is copying the jar file...somewhere. It's hard for me to tell because I'm not sure which parts of the line should remain, and which should be replaced with my particulars.

But here's my guess. I would love it if someone could verify my analysis?
java -cp
This command should be typed exactly as is.
/opt/libreoffice4.0/program/classes/hsqldb.jar
This bit should be replaced with the path to my own hsqldb.jar file.
org.hsqldb.Server
I'm unsure about this one. It doesn't seem to be a bash or java command, so I'm not sure what it does, or if it should be adapted with my own details.
-database.0
Also unsure about this one. Can't find any reference to ".0" in java or bash
file:/home/.../mydb
Not sure what the command "file" does, but it looks like it should end with a path. I'm not sure if the end of the path should be the folder that my database files are in, or something else.
exit 0
This should be typed exactly as it is, and it ends the script.

So, if someone could verify my analysis, it would be great. Thanks, everybody!

[Villeroy]

The following has not so much to do with OpenOffice.

-- Is Mac OS the platform where your Base users can find the running database server or is it Linux, Windows, BSD or anytihng else?
-- You need to know the local IP address of that machine.
-- You need to know the hsqldb.jar you want to use. Your example uses the old HSQL 1.8 shipped with OpenOffice. Nothing wrong with that but on your sever machine you may not want to install any office suite at all. Instead you could download the most recent version of HSQL and extract it somewhere (no further installation required).
-- You need to know the file path(s) where your database(s) is(are) stored.
One HyperSQL database consists of 3 files at least.
/some_path/dbName.script
/some_path/dbName.properties
/some_path/dbName.data
The files of one database have a common path and name prefix and 3 suffixes script .properties .data and sometimes other temporarily used files.

When you access this database in single-user mode (similar to opening files), the client (Base) uses this URL:
jdbc:hsqldb:file:///some_path/dbName;optionA=valueA;optionB=valueB
Base knows what jdbc is, where to find a hsqldb.jar (getting the path from the office options or stored in the odb) and it tells the hsql software to "open" a group of dbName files. The option=value pairs specify some extra settings.
In the odb-file under menu:Edit>Database>Properties there is also a JDBC driver class "org.hsqldb.jdbcDriver" which specifies a "role" or working mode. A "database driver" receives data from a database to work with it in a client software (Base) while it sends data to be stored to some database.

When you access this database in multi-user mode (similar to requesting a web-page from a http: server), the client (Base) uses this URL:
jdbc:hsqldb:hsql://192.168.1.1/dbName;optionA=valueA;optionB=valueB
Base knows what jdbc is, where to find a hsqldb.jar (getting the path from the office options or stored in the odb) and it requests a connection to an independently running hsql program on some machine with the local net IP 192.168.1.1 and some set of options. Instead of the IP one may use the well known network name of that machine ("OfficeServer" or something).
The "org.hsqldb.jdbcDriver" class to be used by the Base client is the same as above but this time there needs to be a second hsql process running on the other machine waiting for requests. Such a process waiting for requests is called a "server". Usually these processes are started automatically with the booting system or triggered by some auto-start script. The details depend on the operating system. When starting HSQL2 as a server, I use the class name "org.hsqldb.server.Server" so HSQL works in a reverse mode sending stored data to database drivers while receiving data to be stored from the database drivers.

On our WIndows network I put the following files on the server machine:
The HSQLDB package with its hsqldb.jar
A starter script set up to be triggered on log in. Under Windows it seems to be "impossible" to start script files on start up rather than log in.
A configuration file "server.properties" where HSQL finds the path-names and alias names of 4 databases, its own IP address and the path-name of a access control file.
An access control file which specifies the machines that are allowed to access this server.

[AndyBlubaugh]

Thanks for the reply, Villeroy! Before I respond to your suggestions about connecting with Open Office, I wanted to get back to the discussion about the starting the HSQLDB engine from the command line, because I think I got it working! I would love it if someone could verify that I have indeed started the engine.

Code: Select all

Last login: Fri Apr 11 10:24:06 on ttys000
server:~ mvmserver$ java -cp /__Dedicated/hsqldb.jar org.hsqldb.server.Server -database.0 file://__Dedicated/mydb
[Server@7dabdb8a]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@7dabdb8a]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@7dabdb8a]: Startup sequence initiated from main() method
[Server@7dabdb8a]: Could not load properties from file
[Server@7dabdb8a]: Using cli/default properties only
[Server@7dabdb8a]: Initiating startup sequence...
[Server@7dabdb8a]: Server socket opened successfully in 8 ms.
[Server@7dabdb8a]: Database [index=0, id=0, db=file://__Dedicated/mydb, alias=] opened sucessfully in 492 ms.
[Server@7dabdb8a]: Startup sequence completed in 501 ms.
[Server@7dabdb8a]: 2014-04-11 10:39:20.138 HSQLDB server 2.3.0 is online on port 9001
[Server@7dabdb8a]: To close normally, connect and execute SHUTDOWN SQL
[Server@7dabdb8a]: From command line, use [Ctrl]+[C] to abort abruptly

This looks very similar to what F3K said would happen a few posts back, though it looks like it still wasn't able to load properties. If anyone has any suggestions about what I might have done incorrectly there, I would appreciate it.

Now, onto Villeroy's suggestions.
You asked:

Is Mac OS the platform where your Base users can find the running database server or is it Linux, Windows, BSD or anytihng else?

All of the computers involved are Mac OSX, running version 10.8.5

You need to know the local IP address of that machine.

I have found the IP address of the machine, and it is 10.1.10.154

You need to know the hsqldb.jar you want to use.

I have done this, it is located in the same folder as the rest of the files related to this database.

He writes:

When you access this database in multi-user mode (similar to requesting a web-page from a http: server), the client (Base) uses this URL:
jdbc:hsqldb:hsql:192.168.1.1/dbName;optionA=valueA;optionB=valueB

Sounds reasonable enough, though I haven't been able to make a connection yet. One question I have is about names. You can see, Villeroy has written "dbName" right after the IP address. I understand that this should be replaced with the name of my database, but I'm not sure what the actual name of the database is when dealing with a "split" database.

I have a folder called __Dedicated
Inside of this folder are:
An openoffice file, called mydb_wizard.odb
and several other files called
mydb.backup
mydb.properties
mydb.data
mydb.script
mydb.ick
mydb.log

So, is the name of my database "__Dedicated" Or is the name of my database "mydb"? Or is the name of my database "mydb_wizard"?

I appreciate everyone's help, and I feel that I have made great progress! Plus, my boss has agreed to let me keep my job (as long as the database is up and running in a week! I think I can do it!)

[Villeroy]

I succesfully tested a server setup on my Linux machine. Until now I only used this on a Windows toy server.
Several test databases are stored under /home/andreas/hsqldb/backends/
The directory looks like this:

Code: Select all

bema_goz.data
bema_goz.properties
bema_goz.script
chargen.data
chargen.properties
chargen.script
export.csv
FilterData.data
FilterData.lck
FilterData.log
FilterData.properties
FilterData.script
FilterData.tmp
Kassenbuch.log
Kassenbuch.properties
Kassenbuch.script
patienten.csv
server.properties
TestDB.data
TestDB.properties
TestDB.script
tom.data
tom.properties
tom.script

script, data, properties of databases tom, TestDB, Kassenbuch, FilterData, chargen and bema_goz.
server.properties is the configuration file of my new hsqldb server and looks like this:

Code: Select all

server.database.0=file:/home/andreas/hsqldb/backends/FilterData
server.dbname.0=filterdata
server.database.1=file:/home/andreas/hsqldb/backends/TestDB
server.dbname.1=test
server.database.2=file:/home/andreas/hsqldb/backends/Kassenbuch
server.dbname.2=cash
server.silent=false
server.address=127.0.0.1

I included 3 databases FilterData, TestDB and Kassenbuch with alias names filterdata, test and cash. The alias names can be used as mnemonic lower case names when the actual file names are somewhat cryptic.
The server uses the "special IP" 127.0.0.1 which is this machine, the localhost. Normally, this would be an IP accessible from various machines in a local network, something like 192.168.1.1 but currently I am connected to the internet only.
All the configuration file options are documented in chapter 13 of the HSQL documentation.

On a command line I started the database server process:

Code: Select all

cd /home/andreas/hsqldb/backends/
java -cp /usr/local/lib/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server

The first line visits my backends directory where everything is stored.
In the second line sudo runs the subsequent command with system admin priviledges prompting for a password. As a normal Linux user I am not allowed to open any network ports.
The second line lets Java run the hsqldb program in server mode.
By default it will find my configuration file server.properties in the current directory so I don't have to specify anything more on the command line.
The command dumps a lot of status messages but no errors. One of the messages is that port 9001 is being used. A port number is like a radio frequency or plug number on a switchboard. The port number and IP are important when you need to allow database access through a firewall.
Now I change the connection URL of one of my Base documents FilterData.odb from ...
jdbc:hsqldb:file:///home/andreas/hsql/backends/FilterData;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false
... to ...
jdbc:hsqldb:hsql://127.0.0.1/filterdata;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;
... or to ...
jdbc:hsqldb:hsql://localhost/filterdata;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;
... referring to the special IP number 127.0.0.1 which is equivalent to the special name localhost. filterdata is the alias name in the configuration file.
I see the database tables when I click the tables container. I can open a table, edit, remove and insert records.
I can create a new Base file connected to the same jdbc: URL and edit the database from both Base files simultaniously.

Now I killed the server (Ctrl+C), closed OpenOffice and wrote this start script "hsqlstarter.sh":

Code: Select all

#!/bin/bash
cd /home/andreas/hsqldb/backends/
java -cp /usr/local/lib/hsqldb/lib/hsqldb.jar org.hsqldb.server.Server
exit 0

I made this file executable and when I run it as admin I can access my databases via hsql://localhost/ again.
If this were a server machine I would have to learn about the ways how to integrate this in the system start process so it is executed on start up without being prompted for admin password.
I would need to open the firewall on port 9001 and I would write an access control list which specifies the IP range which is allowed to connect on port 9001.
Basically this is how the all the glorious internet servers are configured on non-Windows machines. Writing configuration files, opening some hole in a firewall, starting processes which wait for requests and deliver data.

Edit: sudo removed. I can run all this as a user.

[DACM]

...I think I got it working! I would love it if someone could verify that I have indeed started the engine.

Code: Select all

[Server@7dabdb8a]: Initiating startup sequence...
[Server@7dabdb8a]: Server socket opened successfully in 8 ms.
[Server@7dabdb8a]: Database [index=0, id=0, db=file://__Dedicated/mydb, alias=] opened sucessfully in 492 ms.
[Server@7dabdb8a]: Startup sequence completed in 501 ms.
[Server@7dabdb8a]: 2014-04-11 10:39:20.138 HSQLDB server 2.3.0 is online on port 9001
[Server@7dabdb8a]: To close normally, connect and execute SHUTDOWN SQL
[Server@7dabdb8a]: From command line, use [Ctrl]+[C] to abort abruptly

Yes, the HSQLDB 2.3.0 server is running! Note the 'port' and the last two lines concerning database shutdown...

If anyone has any suggestions about what I might have done incorrectly there, I would appreciate it.

The port:

• Port 9001 is fine. It shouldn't be necessary to additionally 'open' this port in your router since the database exists on the LAN side of your router's firewall. Most router-based firewalls employ NAT to isolate the internal LAN from the external WAN/Internet. In other words, don't worry about this port for local LAN database access.

HSQLDB 2.x shutdown:

• The last two lines (above console output) offer shutdown advice.
  
  1. Avoid the use of Ctrl-C as this is the equivalent of cutting the power abruptly to the database host computer. That's not a problem under normal conditions, but it does increase the potential for data loss.
  
  2. So you can simply open the Tools > SQL console in Base on any connected client or host (logged into the database as SA or other DBA user role), and type SHUTDOWN (or SHUTDOWN COMPACT) to properly shutdown the HSQLDB server. Or you can utilize a Base macro perhaps applied to a Push Button on an admin Form.
  
  3. Now, if you want to use a script file to accomplish this task, note the following differences between HSQLDB 1.8 and HSQLDB 2.x. With HSQLDB 2.x, the necessary SHUTDOWN software (class) has been moved from the hsqldb.jar file to sqltool.jar. You should find this jar file in the same folder with hsqldb.jar (whether downloading and extracting HSQLDB from http://hsqldb.org/ -or- when using a Base template or conversion tutorial). Let's break it down (Windows batch syntax below)...
  
  • Here's an excerpt from the Windows batch file: server.STOP.bat
    

    Code: Select all

    set jarpath=C:\Users\MyName\databases\split database folder\driver\sqltool.jar
    
    :SHUTDOWN
    REM :::: Localhost server with user=SA, without password (HSQLDB 1.8 only)
    REM "%javapath%" -cp "%jarpath%" org.hsqldb.util.ShutdownServer
    
    REM :::: Issue the "shutdown;" command to hsqldb 
    set sql=--sql "shutdown;"
    
    REM :::: "shutdown compact will reduce the space required and may improve access times" - HSQLDB documentation
    REM set sql=--sql "shutdown compact;"
    
    :AUTHENTICATION
    REM :::: Localhost server with user=SA, without password
    set authentication=--inlineRC url=jdbc:hsqldb:hsql://localhost,User=SA,Password=
    
    REM :::: Localhost server with user=SA, with password prompt
    REM set authentication=--inlineRC url=jdbc:hsqldb:hsql://localhost,User=SA
    
    REM :::: Localhost serving multiple databases using alias, with password prompt
    REM set authentication=--inlineRC url=jdbc:hsqldb:hsql://localhost/mydb_alias,User=SA
    
    REM :::: Localhost server using a separate, secured, sqltool.rc file; in user home/profile folder during shutdown
    REM set authentication=localhost-sa
    
    :FINISH
    "%javapath%" -jar "%jarpath%" %sql% %authentication%

    Note that 'jarpath' must be set to 'sqltool.jar' when shutting down HSQLDB 2.x (otherwise, use hsqldb.jar when shutting down HSQLDB 1.8).
    • set jarpath=C:\Users\MyName\databases\split database folder\driver\sqltool.jar
    Also note that .ShutdownServer class is insufficient to shutdown HSQLDB 2.x:
    
    • REM :::: Localhost server with user=SA, without password (HSQLDB 1.8 only)
      REM "%javapath%" -cp "%jarpath%" org.hsqldb.util.ShutdownServer
    So to shutdown HSQLDB 2.x server using sqltool.jar we need to be more explicit:
    
    • set javapath=C:\Program Files (x86)\Java\jre7\bin\Java.exe
      set jarpath=C:\Users\MyName\split database\driver\sqltool.jar
      set sql=--sql "shutdown;"
      set authentication=--inlineRC url=jdbc:hsqldb:hsql://localhost,User=SA,Password=
      ...or in your particular case:
      set authentication=--inlineRC url=jdbc:hsqldb:hsql://10.1.10.154,User=SA,Password=
      "%javapath%" -jar "%jarpath%" %sql% %authentication%
      
      In other words, the final (Windows) shutdown command would be:

      Code: Select all

      "C:\Program Files (x86)\Java\jre7\bin\Java.exe" -jar "C:\Users\MyName\split database\driver\sqltool.jar" --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://10.1.10.154,User=SA,Password=

Further Q&A:

• I have found the IP address of the machine, and it is 10.1.10.154

  Very good. Use that IP Address for now, but keep in mind that the IP Address could change upon router reboot (such as after a power failure or routine maintenance)...assuming your router is acting as the DHCP server on your LAN. So eventually, you'll want to setup a 'static' IP address on the database host computer within the range provided by the router (so 10.1.10.154 is a safe bet). Alternatively, you can substitute the network-computer name in place of the IP Address (although I've never tested 'name' usage in the context of a Mac database server).
  

  I have [located the hsqldb.jar file], it is located in the same folder as the rest of the files related to this database.

  Very good. Make sure you're using a copy of the same hsqldb.jar file on each client to provide JDBC driver connectivity functions, as setup in step (3) above:
  
  • (3)Set the GLOBAL Class Path in AOO/LibO to a local copy (same version) of hsqldb.jar on every computer used for server mode access,
    • Tools (*Office) > Options (Preferences) > *Office > Java/Advanced > Class Path > hsqldb.jar

  Villeroy has written "dbName" right after the IP address. I understand that this should be replaced with the name of my database, but I'm not sure what the actual name of the database is when dealing with a "split" database.
  
  ...several other files called
  mydb.backup
  mydb.properties
  mydb.data
  mydb.script
  mydb.ick
  mydb.log
  
  ...is the name of my database "mydb"?

  Yes, the database is named "mydb"

Job-critical advice:

• And lastly, since your job seems to depend on your abilities to serve this database reliably in a multi-user environment, I strongly suggest you read and develop a working knowledge of this post concerning routine database maintenance in server-mode operations. Note the macro-automation of the CHECKPOINT maintenance task, which may be applied to a Push Button on a Form.

[Villeroy]

"C:\Program Files (x86)\Java\jre7\bin\Java.exe" -jar "C:\Users\MyName\split database\driver\sqltool.jar" --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://10.1.10.154,User=SA,Password=

In my above Linux scenario with 3 databases on localhost (127.0.0.1) that would be:

Code: Select all

java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1,User=SA,Password=

which fails with a "no such database alias" error. I can append the database name and shutdown all databases one by one:

Code: Select all

java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1/kassenbuch,User=SA,Password=
java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1/test,User=SA,Password=
java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1/filterdata,User=SA,Password=

The server quits when the last database has been shutdown.

[DACM]

I can append the database name and shutdown all databases one by one:

• java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1/kassenbuch,User=SA,Password=
  java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1/test,User=SA,Password=
  java -jar /usr/local/lib/hsqldb/lib/sqltool.jar --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://127.0.0.1/filterdata,User=SA,Password=

The server quits when the last database has been shutdown.

Yes, very nice. As I recall, we've had at least one other user hosting multiple databases in server mode. Multiple databases do require the dbname (or alias) as shown in the excerpt from server.STOP.bat above.
...

[AndyBlubaugh]

Hi DACM, VIlleroy, F3K, everybody else...

Again, thank you thank you thank you all for all of this help. I have made so much progress. I know that the database engine is running now, so all that remains is connecting it with my database.

I do have a few questions before I get to that though.

First, when I first got the database engine to work, part of the response I got in the terminal was this:

Code: Select all

[Server@7dabdb8a]: Could not load properties from file
[Server@7dabdb8a]: Using cli/default properties only

I attributed that to the fact that one of the files that Villeroy described in his setup wasn't present in mine: "server.properties". I created a file with that exact name, and with the exact same content as what Villeroy describes, and then tried it again, and it seemed to work: no more error message.

So my question is: did I somehow skip a step that would have automatically generated that server.properties file? And will I run into any problems by having made it by hand?

Now, onto the actual connection process!

DACM suggests three different options:

Same machine access using 'localhost':

Code: Select all

hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

LAN access from client computer using the network computer-name of the host-server machine (recommended):

Code: Select all

hsqldb:hsql://db_host_computer_network_name/;default_schema=true;get_column_name=false

LAN access from client computer using the IP address of the host-server machine (ultimately requires a static IP address on the host-server machine):

Code: Select all

hsqldb:hsql://192.168.1.123/;default_schema=true;get_column_name=false

I'm going to call these Method A, B and C.

So it appears that Method A is the connection method that one would use when trying to access the database using the computer that the server is actually hosted on, which we will indeed need to do, since the server computer is actually our business manager's computer. So here's my question about method A: Is "localhost" a placeholder? for something I should look up on my particular computer? Or do I actually type that as "localhost" verbatim? I tried the latter, and the connection failed.

Moving on, it appears that methods B and C are the methods one would use when connecting to the server from another computer, with the only difference being whether to connect using the computer name or the computer IP address. Again, neither of these have worked for me yet. With all three of the methods, I get the same error that the connection was unsuccessful.

For the record, I've tested my driver, and it is successful. It's just the connection itself. Any advice about what variables I should adjust?

Thanks again everybody!

[DACM]

Same machine access using 'localhost':

Code: Select all

hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

• localhost is correct (not a placeholder). localhost resolves to the address 127.0.0.1 on most computer systems. Make sure the database is running. While 'localhost' is fine for testing purposes on the database-host computer, it's not necessary or recommended for the universal .odb file -- which may reside centrally on the server or distributed to the clients for database access.

LAN access from client computer using the network computer-name of the host-server machine:

Code: Select all

hsqldb:hsql://db_host_computer_network_name/;default_schema=true;get_column_name=false

• If you go with this method, make sure you substitute the computer-name of the database server.

LAN access from client computer using the IP address of the host-server machine (ultimately requires a static IP address on the host-server machine):

Code: Select all

hsqldb:hsql://10.1.10.154/;default_schema=true;get_column_name=false

• Again, make sure you substitute the actual IP Address of the server machine -- and eventually setup a static IP Address on the server.

[AndyBlubaugh]

Thanks DACM. So, any idea what variables I should check, since that first method didn't make a connection?

[AndyBlubaugh]

Hi again, DACM.

I have another question, or maybe a clarification. It seems really strange to me that in this line:

Code: Select all

hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

There's nothing pointing base to the actual folder where the database files reside. Can this possibly be the case? It really seems like there should be some additional path information there. In fact, in Villeroy's version of that connection method, he writes:

Code: Select all

jdbc:hsqldb:hsql://localhost/filterdata;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;

In his example, "filterdata" is the name of his database, so it appears that his method has some additional information.

I've tried just about every combination of that "localhost" section, including:
//localhost/
//localhost/mydb
//localhost/mydb_wizard
//localhost/__Dedicated
//localhost/__Dedicated/mydb
//localhost/__Dedicated/mydb_wizard

And nothing works. I'm attaching a screenshot of my actual file structure, just in case that helps.

[DACM]

It seems really strange to me that in this line:

Code: Select all

hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

There's nothing pointing base to the actual folder where the database files reside. Can this possibly be the case?

That's the correct syntax. The JDBC connection is not looking for files. It's looking for the HSQL RDBMS server listening on port 9001 hosting a single database regardless of the name. Things do get more complex as we serve multiple databases through the HSQLDB server, but that's not applicable in your case.

• REM Edit the database name...currently "mydb"...within the server startup string below as necessary
  REM remove ';shutdown=true' from the startup string below in multi-user environments to avoid automatic shutdown upon closing all Base sessions
  "%javapath%" -cp "%jarpath%" org.hsqldb.Server -database.0 file:mydb;hsqldb.default_table_type=cached;shutdown=true
  
  ...actually, org.hsqldb.Server is compatible with HSQLDB 2.x but if you want to be technically correct and future-proof, you should use org.hsqldb.server.Server as VIlleroy mentioned. I simply use org.hsqldb.Server syntax for dual-compatibility with HSQLDB 1.8 server in the available Windows batch files for universal application. And you'll need to eliminate ;shutdown=true anytime now, because this will cause problems in a multi-user environment.
  
  So your final startup string would be something like (while substituting your Java path and your Jar path to hsqldb.jar):
  
  "%javapath%" -cp "%jarpath%" org.hsqldb.server.Server -database.0 file:mydb;hsqldb.default_table_type=cached

Code: Select all

jdbc:hsqldb:hsql://localhost/filterdata;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;

This is incorrect. The ;shutdown=true and ;hsqldb.default_table_type=cached properties are part of the HSQLDB server start-up string (as applicable, without a dedicated server.properties file). And we do want to eliminate ;shutdown=true in multi-user environments in any case.

I've tried just about every combination of that "localhost" section, including:
//localhost/
//localhost/mydb

And nothing works. I'm attaching a screenshot of my actual file structure, just in case that helps.

Well, at the moment you took the screenshot, it appears that you have a "test" database running rather than "mydb". If "test" is running in server mode, then we need to shutdown that server and start a server hosting only "mydb".

[AndyBlubaugh]

But how? I keep trashing all of these files related to the database called "test" (which, for the record, I don't ever remember making, but this process has been going for about a year now so who knows...) and every time I try the terminal command, it re makes them in that same folder. Crazy!

I recognize that these are now becoming problems that are very specific to me, so if this conversation would be more appropriate to continue over email, I'm happy to do so. Otherwise, I'm happy to have the community learn from my fumbling.

Thanks!

[DACM]

Make a few good backups of this folder and restart the computer. Then delete the "test" database files and post your HSQLDB server start-up string so we can take a look. Also post a copy of your shutdown string. We need to get you back to a running HSQLDB 2.3 server hosting "mydb" as in earlier posts shown here:

Code: Select all

Last login: Fri Apr 11 10:24:06 on ttys000
server:~ mvmserver$ java -cp /__Dedicated/hsqldb.jar org.hsqldb.server.Server -database.0 file://__Dedicated/mydb
[Server@7dabdb8a]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@7dabdb8a]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@7dabdb8a]: Startup sequence initiated from main() method
[Server@7dabdb8a]: Could not load properties from file
[Server@7dabdb8a]: Using cli/default properties only
[Server@7dabdb8a]: Initiating startup sequence...
[Server@7dabdb8a]: Server socket opened successfully in 8 ms.
[Server@7dabdb8a]: Database [index=0, id=0, db=file://__Dedicated/mydb, alias=] opened sucessfully in 492 ms.
[Server@7dabdb8a]: Startup sequence completed in 501 ms.
[Server@7dabdb8a]: 2014-04-11 10:39:20.138 HSQLDB server 2.3.0 is online on port 9001
[Server@7dabdb8a]: To close normally, connect and execute SHUTDOWN SQL
[Server@7dabdb8a]: From command line, use [Ctrl]+[C] to abort abruptly

[AndyBlubaugh]

Hi again,

I'm working on a longer response, but for now, here's what I'm getting in the terminal when I start the engine now.

Code: Select all

Andys-MacBook-Pro:~ Blubaugh$ 
Andys-MacBook-Pro:~ Blubaugh$ cd /__Dedicated
Andys-MacBook-Pro:__Dedicated Blubaugh$ java -cp /__Dedicated/hsqldb.jar org.hsqldb.server.Server -database.0 file:mydb;hsqldb.default_table_type=cached
[Server@6d8dfef8]: [Thread[main,5,main]]: checkRunning(false) entered
[Server@6d8dfef8]: [Thread[main,5,main]]: checkRunning(false) exited
[Server@6d8dfef8]: Startup sequence initiated from main() method
[Server@6d8dfef8]: Loaded properties from [/__Dedicated/server.properties]
[Server@6d8dfef8]: [Thread[main,5,main]]: start() entered
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: run() entered
[Server@6d8dfef8]: Initiating startup sequence...
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.maxdatabases=10
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.tls=false
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.trace=false
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.database.0=file:mydb
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.restart_on_shutdown=false
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.no_system_exit=false
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.silent=false
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.default_page=index.html
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.address=192.168.0.185
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.dbname.0=mydb
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.root=.
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: openServerSocket() entered
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: Got server socket: ServerSocket[addr=/192.168.0.185,port=0,localport=9001]
[Server@6d8dfef8]: Server socket opened successfully in 8 ms.
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: openServerSocket() exiting
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: openDatabases() entered
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: Opening database: [file:mydb]
[Server@6d8dfef8]: Database [index=0, id=0, db=file:mydb, alias=mydb] opened sucessfully in 453 ms.
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: openDatabases() exiting
[Server@6d8dfef8]: Startup sequence completed in 462 ms.
[Server@6d8dfef8]: 2014-04-12 16:48:17.421 HSQLDB server 2.3.0 is online on port 9001
[Server@6d8dfef8]: To close normally, connect and execute SHUTDOWN SQL
[Server@6d8dfef8]: From command line, use [Ctrl]+[C] to abort abruptly
[Server@6d8dfef8]: [Thread[main,5,main]]: start() exiting

[AndyBlubaugh]

I also somehow had lost my server.properties file. I really and truly have no idea where it went, it's not in my trash. I re-created it, once again based on Villeroy's example. It looks like that stopped the problem with making the "test" database, but I'm still having a variety of error messages when I connect, depending on certain details in the server.properties file, and the driver class. I'll post about the various error messages I'm receiving in a moment after I calm down a bit.

Here is the text of the "server.properties" file.

server.database.0=file:/__Dedicated/mydb
server.dbname.0=mydb
server.silent=false
server.address=192.168.0.185

[DACM]

I also somehow had lost my server.properties file.

Lose the file. It's not necessary and it's just another variable that may be causing problems as we try to get you up and running.

Andys-MacBook-Pro:__Dedicated Blubaugh$ java -cp /__Dedicated/hsqldb.jar org.hsqldb.server.Server -database.0 file:mydb;hsqldb.default_table_type=cached
[Server@6d8dfef8]: Initiating startup sequence...
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.database.0=file:mydb
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.address=192.168.0.185
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: server.dbname.0=mydb
[Server@6d8dfef8]: [Thread[HSQLDB Server @6d8dfef8,5,main]]: Opening database: [file:mydb]
[Server@6d8dfef8]: Database [index=0, id=0, db=file:mydb, alias=mydb] opened sucessfully in 453 ms.
[Server@6d8dfef8]: Startup sequence completed in 462 ms.
[Server@6d8dfef8]: 2014-04-12 16:48:17.421 HSQLDB server 2.3.0 is online on port 9001
[Server@6d8dfef8]: To close normally, connect and execute SHUTDOWN SQL
[Server@6d8dfef8]: From command line, use [Ctrl]+[C] to abort abruptly

I presume "mydb" is running, but the additional verbosity due to server.properties is unclear to me at the moment.

Datasource URL in your .odb:

• Same machine access using 'localhost':

  Code: Select all

  hsqldb:hsql://localhost/;default_schema=true;get_column_name=false

  LAN access from client computer using the IP address of the host-server machine (ultimately requires a static IP address on the host-server machine):

  Code: Select all

  hsqldb:hsql://192.168.0.185/;default_schema=true;get_column_name=false

[AndyBlubaugh]

I got it to work! Ditching that server.properties file seemed to make it work. I'm a little unclear about how it is supposed to work ideally, but it's working. I'm using the Datasource URL that you provided, and the driver class "org.hsqldb.jdbcDriver".

I really can't thank you all for your help. Once I get the entire system running, I'd love to consolidate all of this information into a tutorial for my fellow Mac users, and maybe post it here on the forum!

I'll let you all know if I'm able to connect clients on Monday.