Help! Java Database programming using OOo Base

I want to try database programming using Java and OOo Base as my database. But
i don't know how. Do I need to download db drivers? Do I need to configure
ODBC driver just like programming in Windows? I don't know where to
start.

Does anyone know any link or tutorial regarding my problem.

Thanks!

To use an HSQLDB, download the HSQLDB system and copy hsqldb.jar into the JRE lib/ext folder (this jar file is also included in the HSQLDB folder). That should be the driver that you need.

I still can't make it work. Here's the code:

import java.sql.*;

public class Coffee {

    private Connection con;
    private ResultSet rs;
    private Statement st;
    
    public void createConnection() {

        try {
            System.out.print ("Connecting to database...");
            Class.forName("org.hsqldb.jdbcDriver");
            con = DriverManager.getConnection("jdbc:hsqldb:file:COFFEEBREAK", "sa", "");
            st = con.createStatement();
            System.out.println("\t[DONE]");
        } catch (Exception err) {
            System.err.println("\nERROR: failed to load HSQLDB JDBC driver.");
            err.printStackTrace();
            System.exit(1);                    
        }

    }
    
    public void retrieveRecords() {
    
        try {   
            rs = st.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
            System.out.println("COF_NAME\t\tPRICE\n");
            while(rs.next())
                System.out.println(rs.getString("COF_NAME") + "\t\t" + rs.getFloat("PRICE"));
        } catch(SQLException err) {
            System.err.println("\nERROR: failed to retrieve records.");
            err.printStackTrace();
            System.exit(1);
        }            
        
    }                               
            
    public static void main(String[] args) {
    
        Coffee app = new Coffee();
        app.createConnection();
        app.retrieveRecords();
        
    }
    
}

The program simply connect to the database COFFEEBREAK.odb using the hsqldb. The database is based on the COFEEBREAK database in the JDBC Basics Tutorial from Sun's website.

When running the program it gives the following output:

Connecting to database...       [DONE]

ERROR: failed to retrieve records.
java.sql.SQLException: Table not found in statement [SELECT * FROM COFFEES]
        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 Coffee.retrieveRecords(Coffee.java:29)
        at Coffee.main(Coffee.java:45)

It seems that no problem occured in the line method createConnection().
But in the retrieveRecords() method.

Furthermore, running java org.hsqldb.util.DatabaseManagerSwing and giving the following inputs in the dialoag box:

Type: HSQL Database Engine Standalone
Driver: org.hsqldb.jdbcDriver
URL: jdbc:hsqldb:file:COFFEEBREAK
User: sa
Password:

Clicking ok gives me access to the main frame of the Database Manager. However, typing
"SELECT * FROM COFFEES"
Will result into an error message:
"java.sql.SQLException: Table not found in statement [SELECT * FROM COFFEES]

The same error in the java program.

Anyway, when is COFFEEBREAK.odb is opened in OpenOffice.org Base (I'm using 2.0 Beta), running the java app gives this error message:

Connecting to database...
ERROR: failed to load HSQLDB JDBC driver.
java.sql.SQLException: The database is already in use by another process: org.hsqldb.persist.NIOLockFile@fa0f928f[file =/home/void/Linux/Database/CoffeeBreak/COFFEEBREAK.lck, exists=true, locked=false, valid=false, fl =null]: java.lang.Exception: checkHeartbeat(): lock file [/home/void/Linux/Database/CoffeeBreak/COFFEEBREAK.lck] is presumably locked by another process.
        at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
        at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
        at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
        at org.hsqldb.jdbcDriver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(DriverManager.java:525)
        at java.sql.DriverManager.getConnection(DriverManager.java:171)
        at Coffee.createConnection(Coffee.java:14)
        at Coffee.main(Coffee.java:44)

Which means the line:
"con = DriverManager.getConnection("jdbc:hsqldb:file:COFFEEBREAK", "sa", "");"
really pretains to the COFFEEBREAK database in /home/void/Linux/Database/CoffeeBreak/COFFEEBREAK.odb (the path of my db)

What seems to be the problem in here? How come it cannot find the tables when access from external program other than OOo Base? Is the hsqlb server that i've downloaded not applicable to OpenOffice.org 2.0? and for 1.1.x only? Or does ver 2.0 has its own way in doing all of these.

btw, I'm using OpenOffice.org 2.0 Beta, JDK 1.5 and HSQLDB 1.8.0

Thanks

I haven't actually used this driver to connect to a HSQLDB. I've made connections to a MySQL database before, but I would think it is about the same. It has also been awhile since I have done any database programming. I'm trying to remeber how to do this stuff.

Is that "file" supposed to be there? To me it looks like it shouldn't be there. idk

Originally posted by mmarkvillanueva "con = DriverManager.getConnection("jdbc:hsqldb:file:COFFEEBREAK", "sa", "");"

Should work with OOo base.

Originally written by http://hsqldb.org/ HSQLDB 1.8.0 final is out. A year of solid development since 1.7.2 brings a more powerful engine that supports data up to 8GB, has better SQL capabilities and runs with greater resilience. HSQLDB 1.8.0 is also the database engine in OpenOffice.org 2.0, soon to appear on millions of desktops around the world.

This link(pdf) might have some useful information on making connections with an hsqldb.


Hope it helps.

I've solved my problem. There's nothing wrong with the java code, but on how the database was created.
The driver, the data source and other stuffs must be provided. So instead of picking Create New Database
in the Database Wizard, pick Connect to an existing database to provide the requirements.

And also, OOo 2.0 already has hsqldb.jar located in my case at /opt/openoffice.org1.9.125/program/classes
so no need of downloading hsqldb unless you want a more updated version

The only problem left is that I can't add records in Base . But I can add records using SQL statement INSERT in org.hsqldb.util.DatabaseManagerSwing. I'm not sure if I successfully executed the macro in
dba.openoffice.org/howto/IgnoreDriverPrivileges.html. I guess I'll give it a try again.

Anyway thanks to all your replies!

I don't get it, how come whenever I delete, update or insert records in the table it doesn't make changes to the table.

For instance check this code:

import java.sql.*;

public class Coffee {

    private Connection con;
    private ResultSet rs;
    private Statement st;
    
    public void createConnection() {

        try {
            System.out.print ("\nConnecting to database...");
            Class.forName("org.hsqldb.jdbcDriver");
            con = DriverManager.getConnection("jdbc:hsqldb:file:Cafe", "sa", "");
            st = con.createStatement();
            System.out.println("\t[DONE]");
        } catch (Exception err) {
            System.err.println("\t[FAILED]\nERROR: Failed to load HSQLDB JDBC driver.");
            err.printStackTrace();
            System.exit(1);                    
        }

    }
    
    public void retrieveRecords() {
    
        try {   
            rs = st.executeQuery("SELECT * FROM COFFEES");
            System.out.println("\nCOF_NAME\tSUP_ID\tPRICE\tSALES\tTOTAL\n");       
            while(rs.next())
                System.out.println(rs.getString("COF_NAME") + "\t" + rs.getInt("SUP_ID")
                    + "\t" + rs.getFloat("PRICE") + "\t" + rs.getInt("SALES")
                    + "\t" + rs.getInt("TOTAL"));                   
        } catch(SQLException err) {
            System.err.println("\nERROR: Failed to retrieve records.");
            err.printStackTrace();
        }                                                                     

    }
    
    public void closeConnection() {
    
        try {
            System.out.print("\nClosing connection...");
            st.close();
            con.close();
            System.out.println("\t[DONE]");
        } catch(SQLException err) {
            System.err.println("\t[FAILED]\nERROR: Failed to close connection.");
            err.printStackTrace();
        }
        
    }

    public void deleteCoffee(String name) {
    
        try {
            System.out.print("\nDeleting record: " + name + "...");
            int result = st.executeUpdate("DELETE FROM COFFEES WHERE COF_NAME = '" + name + "'");
            System.out.println("[DONE]\nRecords affected: " + result);
        } catch(SQLException err) {
            System.err.println("\t[FAILED]\nERROR: Failed to update database.");
            err.printStackTrace();
        }            
        
    }
                                                                   
    public static void main(String[] args) {
    
        Coffee app = new Coffee();
        app.createConnection();
        app.retrieveRecords();
        app.deleteCoffee("Colombian");
        app.retrieveRecords();
        app.closeConnection();
    	System.out.println();
        
    }
    
}

This code simply displays the records of table COFFEES from database CAFE, deletes the record having 'Colombian' as its COF_NAME
and again displays the contents of the table showing that the record was deleted from the resultSet.

When executed here's the output:

void@navi:~/JCafe$ java Coffee          

Connecting to database...       [DONE]

COF_NAME        SUP_ID  PRICE   SALES   TOTAL

Colombian       101     7.99    0       0
Espresso        150     9.99    0       0
French_Roast    49      8.99    0       0
French_Roast_Decaf      49      9.99    0       0
Kaputsino       101     10.99   0       0

Deleting record: Colombian...[DONE]
Records affected: 1

COF_NAME        SUP_ID  PRICE   SALES   TOTAL

Espresso        150     9.99    0       0
French_Roast    49      8.99    0       0
French_Roast_Decaf      49      9.99    0       0
Kaputsino       101     10.99   0       0

Closing connection...   [DONE]

void@navi:~/JCafe$

It is evident that the record was indeed deleted from the resultSet! But it's not deleted from the database! When the program
is executed for the second time, it still shows the record 'Colombian'! I've tried to check org.hsqldb.util.DatabaseManagerSwing
and in deed the record was not deleted at all! I'm sure that this time, there's nothing wrong with the driver and my OOo Base database
because I've tried using DatabaseManagerSwing, like executing inserts, updates and deletes and changes were successfully made.
i've even close the app and re-open it again and doing a select statement shows the changes that have made. So in deed the problem is in my code

I've also tried adding this code

System.out.println("IS AUTO-COMMIT: " + con.getAutoCommit());
System.out.println("IS READ-ONLY: " + con.isReadOnly());

and the results...

IS AUTO-COMMIT: true
IS READ-ONLY: false

i've also tried disabling autocommit and manually commit any changes but it still doesn't work.
what seems to be the problem in here?

If you haven't set the proper permissions for the user your connecting to the database with, You'll need to create a new user that has the privilages to Delete, Update, Insert. You've prob just has Select Privilages