Hi all,
not entirely sure whether this would count as a bug - be interested to hear
your thoughts. I've noticed that PreparedStatement give an SQLException if
prepared before a table exists, but doesn't if the table is created, the
query is cached and then the table is dropped from under the query.
i.e.
Connection conn = DriverManager.getConnection("jdbc:h2:~/h2cacheexample");
/*
* This throws SQL exception as table doesn't exist
*/
System.out.println("Before table created...");
try {
conn.prepareStatement("SELECT * FROM TEST");
}
catch( Exception e) {
System.out.println("Caught expected exception: " + e.getMessage());
}
but if a table is created, then the query is prepared, then the table
dropped and the query prepared again, no error occurs...
/*
* Create the table
*/
conn.createStatement().executeUpdate("CREATE TABLE TEST(col1 bigint, col2
varchar(255))");
/*
* Now prepare statement works correctly
*/
conn.prepareStatement("SELECT * FROM TEST");
/*
* Delete the table
*/
conn.createStatement().executeUpdate("DROP TABLE TEST");
/*
* Getting prepared statement still works, even though table no longer
exists,
*/
try {
conn.prepareStatement("SELECT * FROM TEST");
} catch (Exception e) {
// Don't see this expected exception
System.out.println("Caught expected exception: " + e.getMessage());
}
However, if query cache is switched off in the URL...
conn =
DriverManager.getConnection("jdbc:h2:~/h2cacheexample;QUERY_CACHE_SIZE=0");
The call to prepareStatement after the table is deleted does throw an
exception. I can see that the query is therefore being picked up from cache
rather than compiled from scratch, but unfortunately it's no longer valid.
I guess there are performance implications if the query cache needs to be
invalidated when the db structure changes, but that would lead to more
consistent behaviour rather than the slightly inconsistent result outlined
above.
I'd be interested to hear thoughts on whether there was an argument that
the above is a bug, or maybe indicates that some means of clearing the
cache (via an sql function?) should be provided?
(I use the behaviour above to validate user entered SQL: if the
prepareStatement call fails I display the error message for them to repair
the SQL. However, I also need to run the queries at some point, hence I
need the caching. I can "fix" in my app by having a second connection that
has query_cache_size=0 which is used for validation).
Cheers,
Ian.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
