This discussion is archived
3 Replies Latest reply: Nov 1, 2007 7:43 AM by 807592 RSS

Help! Java Database programming using OOo Base

807590 Newbie
Currently Being Moderated
HSQLDB Problem with OOo Base 2.0

I'm trying to make a Java Database Program using OpenOffice Base database
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
  • 1. Re: Help! Java Database programming using OOo Base
    807590 Newbie
    Currently Being Moderated
    You need to close the connection in your code to release the lock. Close the connection in the finally block and remove the System.exit() from the catch block. Using System.exit() is usually not required when exceptions are handled correctly.

    Also, you code shows the query "SELECT COF_NAME, PRICE FROM COFFEES" while the exception stack trace shows it as "SELECT * FROM COFFEES". Are you sure you are using the correct class file for your program?
  • 2. hsqldb -table not found exception
    807592 Newbie
    Currently Being Moderated
    hello,

    I am also having same error even after closing the connection.
    I am trying to use hsqldb in jboss-4.0.5.GA .I tried the following small jdbc code .


    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.ResultSetMetaData;
    import java.sql.SQLException;
    import java.sql.Statement;

    class JDBC_test
    {
    public static void main(String args[])
    {
    Connection conn=null;
    Statement select=null;
    try
    {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver loaded...");
    }
    catch(Exception e)
    {
    System.out.println("Failed to load hsql driver.");
    return;

    }
    try
    {
    conn = DriverManager.getConnection("jdbc:hsqldb:file:db/demo","sa","");
    System.out.println("connected to hsql..");
    select = conn.createStatement();
    System.out.println("after create statement..");
    }
    catch(Exception e) {System.out.println("create statement failed");}
    try
    {

    ResultSet result = select.executeQuery("SELECT custid,firstname FROM customer_details");
    System.out.println("Got results:");
    while (result.next())
    { // process results one row at a time
    int key = result.getInt(1);
    String val = result.getString(2);

    System.out.println("key = " + key);
    System.out.println("val = " + val);
    }
    } catch (Exception e) {
    e.printStackTrace();}

    finally
    {
    if(conn!=null)
    {
    try { conn.close(); }
    catch(Exception e){e.printStackTrace();}
    }
    }

    }
    }

    I have a table customer_details created with few columns in the stand alone mode.
    SELECT custid,firstname from customer_details;
    is showing me records from the database.

    But I am getting the following error while running this jdbc code

    Driver loaded...
    connected to hsql..
    after create statement..
    java.sql.SQLException: Table not found in statement [SELECT custid,firstname FRO
    M customer_details]
    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 JDBC_test.main(JDBC_test.java:36)


    Can anyone please help me out with this error?

    thanks and regards,
    java_dev.
  • 3. Re: Help! Java Database programming using OOo Base
    807592 Newbie
    Currently Being Moderated
    Gosh, there really is something called OOo. I thought it was a Hallowe'en thing.