The Setup:
I am connected to a standalone (database in a file) HSQLDB with JDBC. The connection opens when the program starts up, and closes upon exit. The user was created with ADMIN rights.
I have used the hsqldb.jar Database Manager to create a table in my database called COMPONENTS. It resides in the PUBLIC schema, and I've been able to add and subtract records from it using the Database Manager.
I am using the following code, which is called in the main method, to prove that I can query my database correctly:
public static void displayAllRows() {
String sql = "SELECT * FROM INFORMATION_SCHEMA.AUTHORIZATIONS";
try (
Statement stmt = CONN.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
System.out.println("Authorizations Table:");
while(rs.next()) {
StringBuilder bf = new StringBuilder();
bf.append(rs.getString("AUTHORIZATION_NAME")).append(": ");
bf.append(rs.getString("AUTHORIZATION_TYPE")).append(", ");
System.out.println(bf.toString());
}
} catch (SQLException ex) {
Logger.getLogger(CompManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
The output of above code is correct, based on using the SELECT * FROM INFORMATION_SCHEMA.AUTHORIZATIONS statement in the Database Manager tool.
The Problem:
I then run the simplified code below. The only changes were to the SELECT statement and I took out everything in the try block (so that nothing there could accidentally cause an error).
private static final Connection CONN = ConnectionManager.getInstance().getConnection();
public static void displayAllRows() {
String sql = "SELECT * FROM COMPONENTS";
try (
Statement stmt = CONN.createStatement();
ResultSet rs = stmt.executeQuery(sql);
) {
} catch (SQLException ex) {
Logger.getLogger(CompManager.class.getName()).log(Level.SEVERE, null, ex);
}
}
That throws an exception: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: COMPONENTS.
I have also tried with PUBLIC.COMPONENTS in the statement. When I copy/paste that statement into the Database Manager, however, it works fine (I am logged in with the same user in both places).
The Question:
I am probably doing something wrong, considering how fresh I am to databases. What could I try to make the second query successful?
I've searched SO and the internet, and have found hundreds of posts about this particular exception, but it is thrown under such broad circumstances that the solutions have not been helpful.
The Answer:
Somehow, I was accidentally connecting to a different database with my program than with my Database Manager. Solution was putting entire file path into database connection String, instead of using a relative reference
You are connecting to two different databases. Probably, your database URL specifies a file path that is relative to the directories where you start DatabaseManager and your app. Use absolute paths or paths that use the ~ symbol to indicate the user.home directory.
As you are new to databases, it would be easier to start an HSQLDB server and connect to it from your app and from the DatabaseManager using the same URL.