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?

Can you confirm there is table name "Emp" inside the database ? — Shashank Kadne, Feb 13 '12 at 05:31
Moreover two or three files are added on to my desktop with names Test.odb, Test.odb.properties, — Harshavardhan Konakanchi, Feb 13 '12 at 05:45
Can you try this...statement.executeQuery("select * from \"Emp\"") ..?? — Shashank Kadne, Feb 13 '12 at 05:48
Is the odb file unzipped? http://programmaremobile.blogspot.com.es/2009/01/java-and-openoffice-base-db-through.html — fglez, May 08 '12 at 12:58

score 5 · Answer 1 · answered Feb 02 '13 at 18:04

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\""

score 2 · Answer 2 · answered Jan 26 '19 at 17:00

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

score 0 · Answer 3 · answered Jul 03 '12 at 16:14

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 .*).

Connect to Open Office odb file using jdbc program

3 Answers3

Linked