[Solved] OpenOffice.org Base HSQLDB External Connection

[Gabriel_Donnell]

Hello,

Please let me know it there is a way to establish an OpenOffice.org Base HSQLDB External Connection. That way, I can access the OpenOffice.org Base HSQLDB without running the OpenOffice.org Base Application.

My objective is to access the OpenOffice.org Base HSQLDB from the Bourne Again Shell (BASH) Command Line Interface (CLI) to run various Standard Query Language (SQL) Statements. This will allow me to eventually implement BASH & SQL scripts to automate different procedures to access the OpenOffice.org Base HSQLDB.

I have searched the Internet. However, most of the search results provide information on how to establish External Data Source Connections from OpenOffice.org Base.

I referred to the Hsqldb User Guide in Chapter 1. Running and Using Hsqldb and Chapter 8. SqlTool to determine how to use the HSQLDB Java Archive (hsqldb.jar) Package Components & Tools to interface with the HSQLDB. Please refer to the following for more information.
1 Hsqldb User Guide: http://hsqldb.org/web/hsqlDocsFrame.html
2 Chapter 1. Running and Using Hsqldb: http://hsqldb.org/doc/guide/ch01.html
3 Chapter 8. SqlTool http://hsqldb.org/doc/guide/ch08.html

I extracted the OpenOffice.org Base (.odb) Package File to directly access the Database Directory from the BASH CLI. I renamed the files to match the HSQLDB Nomenclature. However, that did not work.

Please let me know if there is a way to perform what I am trying to accomplish. The assistance would be greatly appreciated.

[hol.sten]

Please let me know it there is a way to establish an OpenOffice.org Base HSQLDB External Connection.

As far as I know, from reading several posts in several OOo forums, there is no way to connect to an OOo Base HSQLDB file from outside OOo. The reason for this is, that OOo Base puts a HSQLDB database in a file that is not compatible with HSQLDB directly.

OK, I'm still not 100% sure and you are likewise not sure, but let's assume that there is no such way. Might it be possible to approach your problem from the opposite side? How about this:

1. Start with downloading and installing HSQLDB. I assume from what you wrote that you read already enough to get HSQLDB running.
2. Create your database with HSQLDB, but NOT with OOo Base.
3. Start OOo Base and connect to your HSQLDB database through the HSQLDB JDBC Driver.

Now you should have a HSQLDB database that you can use with and without running OOo Base.

[Gabriel_Donnell]

Hello Hol,

I am using the the Ninth Avenue Software Gemstone Accounting Package. Please refer to the following Ninth Avenue Software Web Site and corresponding Gemstone Accounting Web Page for more information.
1 Ninth Avenue Software Web Site: http://NinthAvenue.com.au
2 Gemstone Accounting Web Page: http://NinthAvenue.com.au/products/gemstone

Gemstone Accounting was designed to use the embedded OpenOffice.org Base HSQLDB Instance. That makes it self contained and simple which is a good reason. Please refer to the following OOoForum.org Gemstone accounting - an OOo database application forum messages and my corresponding message for more information.
1 http://OOoForum.org/forum/viewtopic.phtml?p=305299
2 http://OOoForum.org/forum/viewtopic.pht ... 299#305299

There was some information posted to export the embedded OOo Base HSQLDB Instance so the HSQLDB Server Instance can be used instead. However, the information was not comprehensive for me. I can work to figure out the missing pieces.

I really like your recommendation. You presented a good way for me to implement the solution. I will give that a try.

I like writing BASH, PERL, SQL, Editor (ed) and other types of scripts. I wrote a combination of all of them to implement a simple Extract Transform Load (ETL) Framework to convert my monthly Bank Statement Portable Document Format (PDF) Files to SQL Scripts that I can load into the Gemstone Accounting Database.

I want to load the monthly Bank Statement SQL Scripts without using the Graphical User Interface (GUI). Your suggestion may help me accomplish my objective. I will post my results.

[hol.sten]

I extracted the OpenOffice.org Base (.odb) Package File to directly access the Database Directory from the BASH CLI. I renamed the files to match the HSQLDB Nomenclature. However, that did not work.

I did exactly the same and got it working without a problem on Windows XP with Java 6u10 and HSQLDB 1.8.0.10. Here are my steps:

1. Download gemstone-0.4.0.zip from the Gemstone Accounting Web Page http://www.ninthavenue.com.au/products/ ... /downloads, save it to c:\gemstone and unzip it there.
2. Change into the new unzipped directory c:\gemstone\gemstone-0.4.0, copy gemstone.odb to gemstone.zip and unzip gemstone.zip.
3. Change into the new unzipped directory c:\gemstone\gemstone-0.4.0\database and rename the files from backup, data, properties and script to gemstone.backup, gemstone.data, gemstone.properties and gemstone.script. Now you have a gemstone HSQLDB database!
4. Now I wrote a little BAT script (as I said, I'm on Windows XP), to get a HSQLDB server running with the extracted gemstone HSQLDB database. Here it is:

   Code: Select all

   @echo off
   
   set JAVA_HOME=C:\Java\jdk1.6.0_10
   set PATH=%JAVA_HOME%\bin;%PATH% 
   
   set HSQLDB_HOME=C:\dev\HSQLDB\hsqldb_1_8_0_10
   set CLASSPATH=%HSQLDB_HOME%\lib\hsqldb.jar;. 
   
   cd C:\gemstone\gemstone-0.4.0\database
   java org.hsqldb.Server -database.0 file:gemstone -dbname.0 gemstone

5. Finally I wanted to access the gemstone HSQLDB database through the HSQLDB Database Manager and wrote another little BAT script:

   Code: Select all

   @echo off
   
   set JAVA_HOME=C:\Java\jdk1.6.0_10
   set PATH=%JAVA_HOME%\bin;%PATH% 
   
   set HSQLDB_HOME=C:\dev\HSQLDB\hsqldb_1_8_0_10
   set CLASSPATH=%HSQLDB_HOME%\lib\hsqldb.jar;. 
   
   java org.hsqldb.util.DatabaseManager

   After executing this script I selected in the Connect dialog of the HSQLDB Database Manager the Type HSQL Database Engine Server, the Driver org.hsqldb.jdbcDriver, the URL jdbc:hsqldb:hsql://localhost/gemstone, the User sa, and no Password. On the left hand side of the HSQLDB Database Manager I saw now all the gemstone tables. I entered and executed the SQL statement select * from "ac_accounts" for a final check.

[Gabriel_Donnell]

Hello Hol,

Yesterday, I got it to work on my Fedora 8 Laptop by setting up the HSQLDB in Server Mode. Sorry I did not let you know before you did the work yourself. I appreciate your contribution.

Before I was trying to connect to the HSQLDB in Standalone Mode. I may have been doing something wrong. However, I did not try connect to the HSQLDB in Standalone Mode again.

Now I am working on populating the database with my bank transactions.

[hol.sten]

Now I am working on populating the database with my bank transactions.

How are you working on populating the database? Doesn't extracting and renaming the files I listed in my previous post (gemstone.backup, gemstone.data, gemstone.properties and gemstone.script) suffice to populate the database? These files are already the gemstone database.

[Gabriel_Donnell]

Hello Hol,

Yesterday, I completed the procedures that were similar to what you performed. I am able to access the Gemstone HSQLDB in Server Mode.

However, the Gemstone HSQLDB has sample data for the accounting transactions and other information. I need to customize the Gemstone HSQLDB with my business accounting information.

I plan to accomplish that by purging the sample data, and populating it with the transactions from my monthly bank statements for the past 13 months. I do not intend to manually enter my bank statement transactions because it would take too long. Plus, I want to automate the procedure for future bank statements.

In the following post, I wrote what is in the proceeding quote. That is the fun part which I cannot wait to work on.
http://user.services.OpenOffice.org/en/ ... 908#p56839

I like writing BASH, PERL, SQL, Editor (ed) and other types of scripts. I wrote a combination of all of them to implement a simple Extract Transform Load (ETL) Framework to convert my monthly Bank Statement Portable Document Format (PDF) Files to SQL Scripts that I can load into the Gemstone Accounting Database.

I want to load the monthly Bank Statement SQL Scripts without using the Graphical User Interface (GUI). Your suggestion may help me accomplish my objective. I will post my results.

For now, I am playing with the following HSQLDB Tools:

• org.hsqldb.util.DatabaseManager
• org.hsqldb.util.DatabaseManagerSwing
• org.hsqldb.util.Transfer
• org.hsqldb.util.QueryTool
• org.hsqldb.util.SqlTool

Then I am going to confirm that I can connect to the Gemstone HSQLDB in Server Mode from the Gemstone OpenOffice.org Base Application.

[hol.sten]

However, the Gemstone HSQLDB has sample data for the accounting transactions and other information. I need to customize the Gemstone HSQLDB with my business accounting information.

Thanks for the clarification! Now I've got your point. And now, have a lot of fun with the Gemstone database in HSQLDB Server Mode

Then I am going to confirm that I can connect to the Gemstone HSQLDB in Server Mode from the Gemstone OpenOffice.org Base Application.

Please go then to your first post, use there the Edit button, add [Solved] to the start of the title, and use the green tick icon. It will help others finding this solution more easily.