CODEWITHSUNDEEP
javadatabase-connectionh2
Evgenij Reznik
Evgenij Reznik

Reputation: 18614

H2: Error on all other computers

I'm using the H2 database in my Java project (embedded mode). On my computer at home everything works, the connection can be established, but on all other computers I always receive the following error:

org.h2.jdbc.JdbcSQLException: Table "CUSTOMERS" not found; SQL statement: SELECT * FROM CUSTOMERS [42102-162]

I'm sure, that within the DB everything is alright, it should be something with the connection. But even if I import the h2-1.3.162.jar file, the error still remains.

String dbClass = "org.h2.Driver";
String dbDriver = "jdbc:h2:~/cc";
String user = "user1";
String pass = "test1";
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;

public void connect() {
    boolean done = false;
    //load driver
    try {
        Class.forName(dbClass).newInstance();
        System.out.println("driver loaded"); // This is shown in the Compiler
    } catch (Exception ex) {
        System.out.println("error while loading driver");
        System.err.println(ex);
    }
    // Connection
    try {
        conn = DriverManager.getConnection(dbDriver, user, pass);
        System.out.println("connected"); // This is shown in the Compiler
        done = true;
    } catch (SQLException ex) {
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
    }
}

public Vector select() {
    data = new Vector();
    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery("SELECT * FROM CUSTOMERS");
        while (rs.next()) {
            Vector row = new Vector();
            row.add(rs.getInt("id"));
            row.add(rs.getString("fname"));
            row.add(rs.getString("lname"));
            row.add(rs.getString("street"));
            row.add(rs.getString("city"));
            row.add(rs.getString("zip"));
            row.add(rs.getString("state"));
            row.add(rs.getString("phone"));
            row.add(rs.getString("birthday"));
            row.add(rs.getString("email"));
            row.add(rs.getInt("code"));
            data.add(row);
        }
        rs.close();
        stmt.close();
    } catch (SQLException ex) {
        System.out.println("error while selecting"); // I receive this error
        System.err.println(ex);
    }
    return data;
}

Upvotes: 0

Views: 3293

Answers (3)

Evgenij Reznik
Evgenij Reznik

Reputation: 18614

Finally I figured it out!

It had nothing to do with my tables, the database couldn't be found. When trying to connect to a database which can't be found with String dbDriver = "jdbc:h2:~/cc";, a new database with the name cc (in my case) will be created (of course an empty one with no tables) and the connection is established. That's why I haven't received any connection errors.
In the next step I tried to retrieve some data from the new created empty database and therefore received the error, that my table doesn't exist.

So I changed this line: String dbDriver = "jdbc:h2:file:lib/cc"; and copied into the lib directory of my application my old database cc.h2.db.

That's all!

PS: Here is a similiar problem: h2 (embedded mode ) database files problem

Upvotes: 3

duffymo
duffymo

Reputation: 309008

I'm sure, that within the DB everything is alright, it should be something with the connection. But even if I import the h2-1.3.162.jar file, the error still remains.

Check your assumptions. This one is incorrect.

There's nothing in the message to suggest that you couldn't connect. Either you connected to the wrong database OR the one you did connect to didn't CREATE TABLE CUSTOMERS. (Should be named CUSTOMER, not plural.)

You'll fix your error faster if you stop assuming that everything you did is correct. You should be assuming that everything is wrong.

I'd print the stack trace when you catch that exception. It'll give you more information.

Upvotes: 3

Anthony Grist
Anthony Grist

Reputation: 38345

The problem isn't with your connection as you'd receive an exception well before then if it was failing to connect to the database. The exception is pretty clear about what the issue is, as well - it can't find the CUSTOMERS table. That could be because the table doesn't exist at all, or the connection is pointing at the wrong database; try putting in the full schema information of the table, rather than just its name, and see if that works.

Upvotes: 4

Related Questions