Connect to Open Office odb file using jdbc program

Question

I had written following code to connect to OpenOffice db .

String db = "C:\\Documents and Settings\\hkonakanchi\\Desktop\\Test.odb";
Class.forName("org.hsqldb.jdbcDriver");
Connection con =  DriverManager.getConnection("jdbc:hsqldb:file:" + db,"sa","");
Statement statement = con.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM Emp");
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"));
}
con.close();

The database contains emp table and saved some data.

But I get error message as follows.

Exception in thread "main" java.sql.SQLException: 
Table not found in statement [SELECT * FROM Emp]
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
        at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
        at Test.main(Test.java:16)

How could I resolve this. Could anyone tell me how to connect to open office db using hsqldb driver?

Answer 1

finally I found the solution. but unfortunately you have to change your db from odb to hsql.

1.rename your odb file to yourdatabasename.zip

2.extract it

3.now you can find backup,data,script,properties files in database directory under you database folder.

4.rename these files to yourdatabasename.data,yourdatabasename.backup,yourdatabasename.script,yourdatabasename.properties

5.now your connection should be like this: "jdbc:hsqldb:file:Addresstoyourdatabase/database/yourdatabasename"

6.do not forget to put " around your table name like: "SELECT * FROM \"Emp\""

Answer 2

I developed a simple (read only) JDBC driver, which basically extracts the .odb file and redirects all calls to the HSQLDB driver. Maybe it's also useful for somebody.

• GitHub repository: https://github.com/debuglevel/odb-jdbc
• https://bintray.com/debuglevel/maven/odbjdbc

Answer 3

I had a similar issue with a derby database that I was accessing locally. In my case it was the schema that was missing from my SQL statement. So I needed something like:

    select * from Emp.APP 

to get the select statement to not generate the error your seeing.

I looked at this website http://hsqldb.org/doc/1.8/guide/ch09.html#select-section and noticed that it has

table.* 

as part of the example select statement. So I'm guessing replacing the * with the database name or schema might solve your problem (or just try it with .*).