DMT php2hsql

An example for accessing an HSQL-database via programming with PHP.

HSQL- or HyperSQL-databases often are used in open office and libre office.

The Problem

Searching the web often leads to conclusions that accessing HSQL-databases via PHP is not possible.

ODBC

ODBC stands for: Open Database Connectivity.
DSN stands for: Data Source Name.

Some web infos tell that this can be reached with an ODBC-connection.
Unfortunately i never got an example which was working for me.
Nor could i find an linux-ODBC-driver for accessing HSQL-databases.
So i couldn't get any benefits from using the php-odbc-modules.

JDBC

JDBC stands for: Java Database Connectivity.
That's the way HQSL-databases are driven.
Therefore using a database within open office / libre office needs an installed JAVA environment.
You may find statements, that even that is not enough because of the lack of php speaking to JDBC.

The Solution: PHP-JDBC Bridge

"PHP-JDBC Bridge" is an open-source-project for connecting PHP with an HSQL-database.
It is offered e.g. as zip-download on GitHub: JCotton1123 php-jdbc-bridge.

But even with some web hints initiating a connection was not possible for me.
Therefore i worked on a solution which should be running on linux-systems (e.g. Ubuntu).

Installation

After downloading "PHP-JDBC Bridge" the contents have to be extracted in a suitable directory.
For compiling the sources you need the JAVA-commands javac and jar.
Maybe you even need to install missing packets like default-jdk.

The subdirectory lib contains the needed files pjbridge.jar and commons-daemon-1.2.2.jar.
For running "PHP-JDBC Bridge" you need thes files as well as PJBridge.php from the php-directory.
These 3 files may be copied to a working directory.

Application

Still missing are the correct values for

Starting the JAVA-service

The JAVA-service (here: "commons-daemon-1.2.2.jar") allows PHP to create a socket connection.

The start command follows this schema:
java -cp 'BRIDGE.JAR:DAEMON.JAR:JDBC_ENGINE.JAR' Server [JDBC driver entry point] [PORT]

I renamed the files for my personal needings.
The biggest problem for me was the "JDBC driver entry point" which i got accidently.
With all files in one directory the start command may look like this:
java -cp 'php-jdbc-bridge.jar:php-jdbc-daemon-1.2.2.jar:~/Dokumente/daten/database/hsqldb.jar' Server org.hsqldb.jdbcDriver 4444

The running JAVA-Service should output something like this:

notice: listening on 4444 notice: loaded org.hsqldb.jdbcDriver

Establish a PHP-connection to the HSQL-database

Your php-file needs an include of "php-jdbc-bridge.php".
This is a database-wrapper giving several commands.
For connecting via PHP a fitting "connection string" is inevitable.

DSN-less Connection

A "DSN-less Connection" means not using a "data source name"-file but giving all necessary information within one command.
So you need path and name of the database (without file suffix).
The "connection string" in PHP looks in my case as this: jdbc:hsqldb:file:~/Dokumente/daten/database/dmt.
The user name is "SA" and password is an empty string "" - in my example:

define ('DB_USER', 'SA'); define ('DB_PASS', ''); define ('CONNECTION_STRING', 'jdbc:hsqldb:file:~/Dokumente/daten/database/dmt'); require "php-jdbc-bridge.php"; $result = $gDB->connect(CONNECTION_STRING, DB_USER, DB_PASS)

Example Application

My appointments are managed within a self written libre office - application based on an HSQL-database.
Additionally i want to see my appointments in two more applications:

Performance

Formerly i exported the data with StarBasic-routines inside of my office-application.
BASIC-strings can only contain a small amount of data, so each entry had to be written to disk.
PHP comes with mostly unlimited string variables, so the contents can be gathered in ram memory and be written all at once.
Exporting 10.000 appointment-records on my Core2Duo-pc needs this amount of time:

Download

In my example the shellscript recognizes the environment (GUI/Terminal) transfering this to the php-script.
So environment-fitting feedback comes from the shellscript as well as from the php-routines:

In my example the JAVA-service is stopped after the job is done.

My hints and examples may help other people save time and nerves :)

Info for Administrators

PHP here is used locally.

Astonishingly IT-administrators even in large companies don't know about using PHP locally.

No matter using Windows, Mac or Linux: it's an enrichment of the variety of script languages.