Java - Writing a query to a database

Question

I am trying to write code to access a known database on my server to gather data from a certain table, but I keep getting an exception.

Here is my Java code so far;

public static void main (String [] args) throws FileNotFoundException
{ 
    String url = "jdbc:hsqldb:C:/workspace/database/eemdb";
    String dbName = "eem_db";
    String driver = "org.hsqldb.jdbcDriver";
    String username = "sa";
    String password = "";
    try
    {
        Class.forName(driver).newInstance();
        Connection con = DriverManager.getConnection(url+dbName, username, password);
        Statement st = con.createStatement();
        ResultSet res = st.executeQuery("SELECT * FROM DEVICE");

        con.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

and here is the exception report;

java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: DEVICE
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
    at com.davranetworks.seleniumtests.Example.main(Example.java:32)
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: DEVICE
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.SchemaManager.getTable(Unknown Source)
    at org.hsqldb.ParserDQL.readTableName(Unknown Source)
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
    at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)

For the moment all I want to do is connect to the database and call a query but it just is not happening for me. Has any got any information to help me?

It seems like the table `DEVICE` does not exist in your database or you are not allowed to access it. — Robin Krahl, Nov 19 '13 at 16:13
no it exists, I can enter it manually and I do have access to it — Dan, Nov 19 '13 at 16:14
Does that exact query work if you run it directly? My first thought would be that you need to fully qualify DEVICE in your query. — Andrew, Nov 19 '13 at 16:18
`url+dbName = jdbc:hsqldb:C:/workspace/database/eemdbeem_db` pretty sure this is not intended — Compass, Nov 19 '13 at 16:19

score 3 · Accepted Answer · edited May 23 '17 at 12:12

3

I think the error message might be misleading. Try a newer version of hsqldb (or switch to the H2 database to see what error messages should be like).

In your case, my guess is that table DEVICE does not exist. Did you create any tables in this database?

Also note that hsqldb will create an empty database if you make a typo in the URL.

The "user lacks privilege" is very unlikely since sa is the DB administrator.

To check which tables exist: How to see all the tables in an HSQLDB database?

edited May 23 '17 at 12:12

Community

1
1

answered Nov 19 '13 at 16:14

Aaron Digulla

321,842
108
597
820

+1 for H2 Recommendation. It's a drop-in replacement for HSQLDB, but is WAY better. – SnakeDoc Nov 19 '13 at 16:15
This is a company database that my colleagues use so I can't make any changes to it. The table `Device` does exist, I have tried the code with other tables but with the same result. I personally didn't create the tables – Dan Nov 19 '13 at 16:17
Run `SELECT * FROM INFORMATION_SCHEMA.TABLES` to see which tables are in the database. My guess is that you made a typo and hsqldb created a new, empty database. – Aaron Digulla Nov 19 '13 at 16:18

score 0 · Answer 2 · answered Nov 19 '13 at 16:19

0

Have you created any tables in DB prior to executing the code? and If you DEVICE then there is probability that a / is missing try it like this

 String url = "jdbc:hsqldb:C://workspace/database/eemdb";

answered Nov 19 '13 at 16:19

Shivam Dhoot

151
10

Tried it, it makes no difference – Dan Nov 19 '13 at 17:05

Java - Writing a query to a database

2 Answers2