Java and OpenOffice BASE db through HSQLDB jdbc

Java and OpenOffice BASE db
G.Morreale

Introduction:

A Base document can create an HSQLDB database that is stored inside of the Base document.
OOo documents are stored as zip files and Base documents are no exception.

So HSQLDB is the internal engine of OpenOffice db

In this article we see how can interact by java source code with OpenOffice base db using the hsqldb jdbc driver.

The Steps:

In order to write the example follow these steps

  • Prepare ODB database with openoffice 

    • Create a new office base database(i.e. mydb.odb).
    • Create a new table(i.e. User) into base database.
    • Make the coloumns inside the table(ID,firstname,lastname)
    • Populate it with some rows

  • Prepare HSQLDB extracting it from ODB

    • Rename the mydb.odb file in mydb.zip, extract "database" directory from it, so you can find these files:
      • backup
      • data
      • properities
      • script
    • Copy the files into c:\mydbdir\ location
    • rename all the files by putting the same prefix before the file name, example:
      • mydb.backup
      • mydb.data
      • mydb.properities
      • mydb.script

            The prefix and filename are separated by dot. These files is the HSQLDB.


  • Prepare HSQLDB JDBC API


We are ready for source code!


The Source Code

import java.text.ParseException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class Main
{

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) throws ParseException
    {
        try
        {            
            String db_file_name_prefix = "c:\\mydbdir\\mydb";
            
            Connection con = null;
            // Load the HSQL Database Engine JDBC driver
            // hsqldb.jar should be in the class path or made part of the current jar
            Class.forName("org.hsqldb.jdbcDriver");

            // connect to the database.   This will load the db files and start the
            // database if it is not alread running.
            // db_file_name_prefix is used to open or create files that hold the state
            // of the db.
            // It can contain directory names relative to the
            // current working directory
            con = DriverManager.getConnection("jdbc:hsqldb:file:" + db_file_name_prefix, // filenames
                    "sa", // username
                    "");  // password

            Statement statement = con.createStatement();
            //look at " for table name
            ResultSet rs = statement.executeQuery("SELECT * FROM \"User\"");

            //print the result set
            while (rs.next())
            {
                System.out.print("ID: " + rs.getString("ID"));
                System.out.print(" first name: " + rs.getString("firstname"));
                System.out.println(" last name: " + rs.getString("lastname"));
            }

            statement.close();
            con.close();

        } catch (SQLException ex)
        {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        } catch (ClassNotFoundException ex)
        {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}


Conclusion 

The odb is a zip file, so the beginning steps is uncomfortable because you must rename the odb in zip, rename, work on it and then recompress all in order to edit the odb again with openoffice.
In order to avoid this incovenient way it is possible to make the beginning steps automatically by using java.util.zip package:

Please leave a feedback in the comment to this post.

17 comments:

abhi said...

Very Good!! but how could i insert the in the database please suggest !!

Giuseppe Morreale said...

You can insert using, for example, openoffice.

Otherwise you can insert using jdbc through java code.

Unknown said...

So to repack in an odb file that OpenOffice can handle, you have to remove the "mydb." prefix on the database files?

Is'nt there any class that can handle odb files directly?

Giuseppe Morreale said...

when you repack leave the file with the same prefix.
So, when the odb is made you can rename the file how you want.

I don't know about class to directly handle the odb.

You can make it by unpack and repack the zip(odb) file inside you code.

Anonymous said...

Hi.

Thanks for this bit of info, but i'm having a problem.

When i first INSERT a new row and then show the whole table, it shows it.

But it doesnt seem to "remember", after the program has shut down, that i insert a row.

How do i get it to 'save' the INSERTs to the files?

Unknown said...

look for the shutdown() method in this: http://hsqldb.org/doc/guide/apb.html

Anonymous said...

Thanks.

That seems to work just fine. ^^

Anonymous said...

ooo2hsqldb.sh
---------------
#!/bin/sh

##################
# Converts in-place from OOo
##################
# to HSQLDB
# Good article - thought this might
# help others
##################
# See: hsqldb2ooo.sh for reverse
##################
# WARNING: USE AT OWN RISK
##################

TIMESTAMP=`date +%Y%m%d_%H%M%S`;

if [ ! $# == 2 ]
then
echo "Usage: ooo2hsqldb.sh <OOo file> <hsqldb directory>"
echo "e.g., ./ooo2hsqldb.sh test.odb hsqldb"
exit
fi


if [ -f $1 ];
then
echo "[ok] OOo file $1 found."
else
echo "OOo Base file $1 does not exist. Aborting operation."
exit
fi

# Check if directory exists
if [ -d $2 ]
then
echo "Backing up $2 to $2.$TIMESTAMP"
mv $2 $2.$TIMESTAMP
fi


if [ -d $2 ]
then
echo "Failed to rename existing HSQLDB directory ./$2. Exiting."
exit
fi

echo "Creating HSQLDB in directory $2"
mkdir $2

if [ -d $2 ]
then
echo "[ok] ./$2 exists."
else
echo "Failed to create directory ./$2. Exiting."
exit
fi


# Unzip
echo "Unzipping $1 to $2"
unzip $1 -d $2

# Now rename files
echo "Renaming files in directory $2."
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script

if [ -f $2/database/$2.backup ]
then
echo "[ok] $2/database/$2.backup."
else
echo "Error occured in conversion. Cannot find file $2/database/$2.backup."
exit
fi

if [ -f $2/database/$2.data ]
then
echo "[ok] $2/database/$2.data."
else
echo "Error occured in conversion. Cannot find file $2/database/$2.data."
exit
fi

if [ -f $2/database/$2.properties ]
then
echo "[ok] $2/database/$2.properties."
else
echo "Error occured in conversion. Cannot find file $2/database/$2.properties."
exit
fi

if [ -f $2/database/$2.script ]
then
echo "[ok] $2/database/$2.script."
else
echo "Error occured in conversion. Cannot find file $2/database/$2.script."
exit
fi


echo "Completed successfully."

Anonymous said...

hsqldb2ooo.sh
---------------
#!/bin/sh

##################
# Converts HSQLDB to OOo
# for HSQLDB generated using
# ooo2hsqldb.sh
##################
# Good article - thought this might
# help others
##################
# See: ooo2hsqldb.sh for more
##################
# WARNING: USE AT OWN RISK
##################

TIMESTAMP=`date +%Y%m%d_%H%M%S`;

if [ ! $# == 2 ]
then
echo "Usage: hsqldb2ooo.sh <OOo file> <hsqldb directory>"
exit
fi

# Check if directory exists
if [ ! -d $2 ]
then
echo "HSQLDB directory $2 does not exist. Exiting."
exit
fi

# Check that files exist
if [ -f $2/database/$2.backup ]
then
echo "[ok] $2/database/$2.backup."
else
echo "Cannot find file $2/database/$2.backup. Exiting"
exit
fi

if [ -f $2/database/$2.data ]
then
echo "[ok] $2/database/$2.data."
else
echo "Cannot find file $2/database/$2.data. Exiting."
exit
fi

if [ -f $2/database/$2.properties ]
then
echo "[ok] $2/database/$2.properties."
else
echo "Cannot find file $2/database/$2.properties. Exiting."
exit
fi

if [ -f $2/database/$2.script ]
then
echo "[ok] $2/database/$2.script."
else
echo "Cannot find file $2/database/$2.script. Exiting."
exit
fi


if [ -f $1 ];
then
echo "Backing up $1 to $1.$TIMESTAMP"
mv $1 $1.$TIMESTAMP
fi

if [ -f $1 ]
then
echo "Failed to back up $1. Exiting"
exit
fi


# Now rename files
mv $2/database/$2.backup $2/database/backup
mv $2/database/$2.data $2/database/data
mv $2/database/$2.properties $2/database/properties
mv $2/database/$2.script $2/database/script

# Check that files exist
if [ ! -f $2/database/backup ]
then
echo "Failed to rename $2/database/$2.backup. Exiting"
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
exit
fi

if [ ! -f $2/database/data ]
then
echo "Failed to rename $2/database/$2.data. Exiting"
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
exit
fi

if [ ! -f $2/database/properties ]
then
echo "Failed to rename $2/database/$2.properties. Exiting"
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
exit
fi

if [ ! -f $2/database/script ]
then
echo "Failed to rename $2/database/$2.script. Exiting"
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
exit
fi


# Zip
cd $2
zip -r tmp.out * -x tmp.out

if [ ! -f "tmp.out" ]
then
echo "Failed to zip HSQLDB to $2/tmp.out. Exiting"
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
exit
fi

# Move file to working directory
cd ..
mv $2/tmp.out $1


if [ ! -f $1 ]
then
echo "Failed to move zip file $HSQLDB to $1. Exiting"
mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
exit
fi


mv $2/database/backup $2/database/$2.backup
mv $2/database/data $2/database/$2.data
mv $2/database/properties $2/database/$2.properties
mv $2/database/script $2/database/$2.script
echo "Completed."

Unknown said...

you have a nice site.thanks for sharing this site. various kinds of ebooks are available here

http://feboook.blogspot.com

Yogesh gupta said...

hi can u pls explain these steps if we r using open office in linux thanx.

Giuseppe Morreale said...

In linux the way is the same you can proceed in the same manner!

sfgsar said...

Trying to use example with a more complicated select from a table I have, named "Master List". I receive error that it cannot find tablename in statement. Its there, in fact the statement produced in the error message will run when pasted into query in the database. executeQuery statement is below, along with error. Any suggestions would be appreciated.

ResultSet rs = statement.executeQuery("SELECT \"Call\" AS \"Callsign\", \"First Name\", \"Last Name\", \"License Class\" AS \"Class\",casewhen( \"WCARES Member?\", '1', NULL ) AS \"WCARES\", casewhen( \"WCARES Member?\", '1', NULL ) AS \"ARES\", CASE WHEN SUBSTR( \"Call\", 2, 1 ) >= '0' AND SUBSTR( \"Call\", 2, 1 ) <= '9' THEN SUBSTR( \"Call\", 1, 1 ) ELSE SUBSTR( \"Call\", 1, 2 ) END AS \"Prefix\", CASE WHEN SUBSTR( \"Call\", 2, 1 ) >= '0' AND SUBSTR( \"Call\", 2, 1 ) <= '9' THEN SUBSTR( \"Call\", 2, 1 ) ELSE SUBSTR( \"Call\", 3, 1 ) END AS \"Region\",CASE WHEN SUBSTR( \"Call\", 2, 1 ) >= '0' AND SUBSTR( \"Call\", 2, 1 ) <= '9' THEN SUBSTR( \"Call\", 3 ) ELSE SUBSTR( \"Call\", 4 ) END AS \"Suffix\" FROM \"Master List\" AS \"Master List\"");

java.sql.SQLException: Table not found in statement [SELECT "Call" AS "Callsign", "First Name", "Last Name", "License Class" AS "Class",casewhen( "WCARES Member?", '1', NULL ) AS "WCARES", casewhen( "WCARES Member?", '1', NULL ) AS "ARES", CASE WHEN SUBSTR( "Call", 2, 1 ) >= '0' AND SUBSTR( "Call", 2, 1 ) <= '9' THEN SUBSTR( "Call", 1, 1 ) ELSE SUBSTR( "Call", 1, 2 ) END AS "Prefix", CASE WHEN SUBSTR( "Call", 2, 1 ) >= '0' AND SUBSTR( "Call", 2, 1 ) <= '9' THEN SUBSTR( "Call", 2, 1 ) ELSE SUBSTR( "Call", 3, 1 ) END AS "Region",CASE WHEN SUBSTR( "Call", 2, 1 ) >= '0' AND SUBSTR( "Call", 2, 1 ) <= '9' THEN SUBSTR( "Call", 3 ) ELSE SUBSTR( "Call", 4 ) END AS "Suffix" FROM "Master List"]

sfgsar said...

I idnetified my own error, I had not prepended the data, script, etc file with the Database name.

ITIANS said...

very nice tutorial. I tried it but iam getting the error as

"java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MOBILES "

here MOBILES is my table name

ITIANS said...

Very nice tutorial.I follow your tutorial and tried your code. But iam getting the following error..

"java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: MOBILES"

Here Mobiles is my table name.

aQum said...

When I created my .odb file with table and data and unzipped it there was those 4 files listed in your post but I could only read data with JDBC. It created the mydb.log file after connecting with JDBC, but the file was empty.

When I created new .odb file and created table with data and created form for my database and then unzipped the .odb file there was .log file with input:

/*C1*/SET WRITE_DELAY 0
SET AUTOCOMMIT FALSE
SET AUTOCOMMIT TRUE

After doing this I was able to insert/update/delete with JDBC, as it seems to use the .log file in progress.

Posted this here if some one is having similar problem.