Click here to Skip to main content
15,881,089 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
statement3.execute("CREATE TABLE accomodation_fare_relations (\n" +
                "  id integer,\n" +
                "  id_accomodation integer NOT NULL,\n" +
                "  id_room_fare integer NOT NULL,\n" +
                "  PRIMARY KEY (id),\n" +
                "  FOREIGN KEY (id_accomodation) REFERENCES accomodation(id)\n" +
                "                        ON DELETE CASCADE,\n" +
                "  FOREIGN KEY (id_room_fare) REFERENCES room_fare(id)\n" +
                "                        ON DELETE CASCADE\n" +
                ");");
This should create my table, and this should make a query:

        @Test
        public void testSelect() throws SQLException {
          PreparedStatement ps = conn.prepareStatement("SELECT " +
            "accomodation.type," +
            "room_fare.value," +
            "room_fare.season " +
            "FROM accomodation_fare_relations " +
            "JOIN accomodation ON accomodation.id = accomodation_fare_relations.id_accomodation " +
            "JOIN room_fare ON room_fare.id = accomodation_fare_relations.id_room_fare");
    ResultSet resultSet =  ps.executeQuery();

    while (resultSet.next()) {
        System.out.println(resultSet.getString("type"));
        System.out.println(resultSet.getDouble("value"));
        System.out.println(resultSet.getString("season"));
        System.out.println("-------------------------");
        System.out.println("-------------------------");
    }
}
However, I am getting this error:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "accomodation_fare_relations" not found; SQL statement: SELECT accomodation.type,room_fare.value,room_fare.season FROM accomodation_fare_relations JOIN accomodation ON accomodation.id = accomodation_fare_relations.id_accomodation JOIN room_fare ON room_fare.id = accomodation_fare_relations.id_room_fare [42102-200]

I have no idea how to solve this, I managed to create the tables, that have the many-to-many relations, but I do not know how to solve this.


What I have tried:

I have described the problem, and what I tried in problem description
Posted
Updated 21-Nov-21 6:44am

1 solution

Start by checking that the table create statement is executed, throws no errors, and that the table actually exists afterwards - use SSMS to check that last bit, and the debugger for the other two.

If the table exists afterwards, use the debugger to make sure that the error message is coming from the code you show - and SSMS to ensure that the table still exists immediately before stepping that executeQuery line.
Use the debugger to check that the connection refers to the right DB server, the right DB, and SSMS to check that the SQL user account has read permissions on the table.

Sorry, but we can't do any of that for you - we have no access to your DB at all!
 
Share this answer
 
Comments
Alex Darius 21-Nov-21 13:02pm    
Will it help if I give my code from the test file, it is an in-memory database after all
0x01AA 21-Nov-21 14:07pm    
One thing I learned here definitely, if I have a problem with my software... The answer other than it is '42': 'this is/was a caching issue' *lol*

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900