HSQLDB-BER User Guide

Blaine Simpson

$Date: 2005/05/20 17:16:52 $


Table of Contents

1. Introduction
Available formats for this document
Purpose of HSQLDB-BER
License and Documentation
2. How to Install and Run the sample HSQLDB Server and Perl Script
3. Using HSQLDB-BER
Using Java JDBC clients
Using Perl scripts
A. Resources

List of Tables

1.1. Alternate formats of this document

List of Examples

2.1. Running a HSQLDB Server + BerServer
2.2. Managing HSQLDB with SqlTool
2.3. Managing HSQLDB with DatabaseManager
3.1. Sample HSQLDB BER connect string

Chapter 1. Introduction

If you notice any mistakes in this document, please email me at blaine.simpson@admc.com so that I can correct them. You can also email me if you have problems with the procedures explained herein, or if you have questions, comments, suggestions or complaints.

Available formats for this document

This document is available in several formats.

You may be reading this document right now at http://hsqldb-ber.sourceforge.net/HSQLDB-BER, or in a distribution somewhere else. I hereby call the document distribution from which you are reading this, your current distro.

http://hsqldb-ber.sourceforge.net/HSQLDB-BER hosts the latest versions of all available formats. If you want a different format of the same version of the document you are reading now, then you should try your current distro. If you want the latest version, you should try http://hsqldb-ber.sourceforge.net/HSQLDB-BER.

Sometimes, distributions other than http://hsqldb-ber.sourceforge.net/HSQLDB-BER do not host all available formats. So, if you can't access the format that you want in your current distro, you have no choice but to use the newest version at http://hsqldb-ber.sourceforge.net/HSQLDB-BER.

Table 1.1. Alternate formats of this document

formatyour distroat http://hsqldb-ber.sourceforge.net/HSQLDB-BER
Chunked HTML index.htmlhttp://hsqldb-ber.sourceforge.net/HSQLDB-BER/index.html
All-in-one HTML HSQLDB-BER.htmlhttp://hsqldb-ber.sourceforge.net/HSQLDB-BER/HSQLDB-BER.html
PDF HSQLDB-BER.pdfhttp://hsqldb-ber.sourceforge.net/HSQLDB-BER/HSQLDB-BER.pdf

Purpose of HSQLDB-BER

HSQLDB can be used with Perl via the DBI and DBD::JDBC modules, without this HSQLDB-BER product. You don't need HSQLDB-BER to access a fantastic database (HSQLDB) by coding perl to the DBI API. And, even though the DBD::JDBC documentation implies otherwise, you can even run HSQLDB database instances directly in the DBD::JDBC-supplied proxy server (by just using "mem", "file", or "resource" HSQLDB JDBC URLs). The purpose of HSQLDB-BER is to add the following important features.

Unique features of HSQLDB-BER

Ready-to-run yet Ultimately Extensible
You get a fully functional SQL database ready-to-run out of the box. Just extract the distribution zip file, run one command to start the preconfigured database server, and one command to run the sample perl script. Then, if you wish, you can then customize the database all you want (change persistence methods, import data, serve different protocols, embed other applications, etc.), and write all of the perl scripts you want (perhaps starting with the supplied sample perl script as a template).
No Proxy Server to Configure
You get the most inuitive-to-configure SQL database in existence (for anybody who isn't afraid to edit a couple simple properties files), without needing to learn how to configure and administer Vizcom's proxy server (which comes with DBD::JDBC). Unlike the Vizcom server, HSQLDB-BER's BerServer is administered exactly the same way as a traditional HSQLDB Server.

HSQLDB is better than any other database for use with DBD::JDBC because there will be no proxying involved. HSQLDB is already known for being the fastest of the fast databases. Whether you use the DBD::JDBC-supplied server or HSQLDB-BER, you can access HSQLDB directly from your perl scripts without the performance and resource cost of a proxy server.

The same HSQLDB JVM can serve the normal "hsql" protocol, the "ber" protocol for Perl clients, "hsqls", "http", "https", etc., all at the same time. This package comes with utilities which make it easy to run any of these combinations and to embed your own Java applications if you wish.

The auto-of-the-box configuration runs one Java JVM that serves both "hsql" JDBC requests, and "ber" perl requests. Client perl code just uses DBI with a resource locator string beginning with ber:hsqldb:. Java client code just uses JDBC with a JDBC URLs beginning with jdbc:hsqldb:hsql:.

License and Documentation

This project may be distributed according to the terms of the GNU GPL or the Perl Artistic License, with some portions also being covered by the HSQLDB license. The text of both licenses reside in distributed with HSQLDB-BER in the same directory as this file.

This document you are reading is the primary documentation for HSQLDB-BER. Documentation for the component technologies is not duplcated here. If you want documentation for HSQLDB, the DBD::JDBC or DBI perl modules, or perl programming, see the documentation for that product. See the Resources appendix below. You're better off to go by this document than the README.txt in the doc subdirectory of the HSQLDB-BER distribution, because this is the one that will be maintained most vigorously.

Chapter 2.  How to Install and Run the sample HSQLDB Server and Perl Script

Download the binary distribution with name like hsqldb-ber-0.1a.zip to the computer to house your database server. Unzip the zip file to any directory. Everything will expand into a new directory named something like hsqldb-ber-0.1a. We hereafter call this the HSQLDB-BER installation directory.

You need to have Perl and some specific Perl modules installed on the servers that will execute your perl scripts. If this is your first install, then I suggest that you hold off on that and install Perl and the Perl modules on the database server computer. This makes it extremely easy to run the sample configuration and give yourself a warm fuzzy.

On the computers to host your Perl scripts, install the Perl modules DBI, Convert::BER, and DBD::JDBC if any of them are not already installed.

Tip

Before you run make for DBD::JDBC, edit the file JDBC.pm and comment out the two lines containing SQL_BIGINT. These lines are obsolete and will cause things to fail.

I recommend that you don't bother running the supplied tests. The results aren't worth the entire pain-in-the-ass to get the test itself to run. When I have time, I'll try to track down the author of DBD::JDBC and get the SQL_BIGINT problem taken care of at the source.

The documentation of DBD::JDBC will say that the module is for use with a Java proxy server. Disregard that-- It is true when using the DBD::JDBC-supplied proxy-server, but you will be using a direct HSQLDB database server (which the main point of the HSQLDB-BER product).

There will be a subdirectory of your HSQLDB-BER installation directory named samples. Cd into that directory and run

Example 2.1. Running a HSQLDB Server + BerServer

    java -jar ../hsqldb-ber.jar org.hsqldb.Server org.hsqldb.BerServer
This will start a "hsql" protocol JDBC Server, and a BER protocol Server which are serving two memory-only database instances. If you want to run only a BER Server, then just run
java -cp path/to/hsqldb-ber.jar:path/to/hsqldb.jar:path/to/dbd_jdbc.jar \
    org.hsqldb.BerServer
(Windows users user ";" in place of ":"). Let the Server continue to run. Put it in the background or get another shell so you can run the sample perl script against this Server. (It is incredibly easy to change the database instances to be persistent. The only reason I don't have it persisting by default is so that this distribution can be run from read-only media like CDs. Just change the occurrences of mem: in the server.properties and berserver.properties to file:.)

Once again, cd to the samples directory under your hsqld-ber installation diretory. Execute the perl script sample.pl. (You can make this script "executable". It is only delivered non-executable due to a portability constraint of the build tool which we use).

You can manage your database on the command-line by running

Example 2.2. Managing HSQLDB with SqlTool

    java -jar ../hsqldb.jar --rcfile sqltool.rc playdb-hsql
from the same directory; or

Example 2.3. Managing HSQLDB with DatabaseManager

java -cp ../hsqldb.jar --rcfile sqltool.rc org.hsqldb.util.DatabaseManagerSwing playdb-hsql
for a GUI.

Chapter 3. Using HSQLDB-BER

Using Java JDBC clients

Use a JDBC URL like jdbc:hsqldb:hsql://localhost:9010/play, as determined by the settings in your server.properties file. If you want a command-line tool, then I recommend SqlTool, as documented in the HSQLDB User Guide.

Using Perl scripts

Use DBI/DBD::JDBC according to the POD/man-page documentation, except in place of a JDBC url, use a HSQLDB BER resource locator beginning with ber:hsqldb:, like

Example 3.1. Sample HSQLDB BER connect string

    dbi:JDBC:hostname=localhost:9111;url=ber:hsqldb:play

The connect string must consist of ber:hsldb: + the dbname specified in your berserver.properties file. For the sample configuration, the berserver.properties file contains the line

    server.dbname.0     play

Appendix A. Resources