[JDBC-91] Issue with H2 "script" command Created: 20/Feb/14 Updated: 15/Apr/14
|Reporter:||Mike Anderson||Assignee:||Sean Corfield|
While trying to execute an H2 "script" statement as follows:
(defn script 
I get the following error message:
org.h2.jdbc.JdbcSQLException: Method is not allowed for a query. Use execute or executeQuery instead of executeUpdate; SQL statement:
It's possible that I'm just doing something stupid as I don't really knwo the internals of java.jdbc, but it looks like this is a bug with the type of query being constructed in "db_do_prepared"?
|Comment by Sean Corfield [ 28/Feb/14 1:44 AM ]|
Mike, since "script to file.txt;" is not a query or update, I would expect db-do-commands to be the right function to call here, not execute! - can you try that and let me know if it works?
|Comment by Mike Anderson [ 14/Apr/14 10:27 PM ]|
Hi Sean, I still seem to get an error:
(j/db-do-commands db (str "SCRIPT TO '" "dbscript.txt" "';"))
|Comment by Mike Anderson [ 14/Apr/14 10:33 PM ]|
Interestingly, this seems to work:
(j/query db [(str "SCRIPT TO '" "dbscript.txt" "';")])
So I have a good workaround at least. Not sure what the right overall solution is - maybe some clearer error messages or docstrings regarding what is / isn't allowed with each type of query would be good?
|Comment by Sean Corfield [ 14/Apr/14 11:03 PM ]|
Well, I don't have much control over what the JDBC driver itself accepts or doesn't accept for various commands but perhaps introducing a variant of db-do-commands that allows you to specify exactly what method on the PreparedStatement gets called would be helpful here. Or maybe you could just create a PrepatedStatement object - there's an API for that - and run non-standard commands thru that directly?
|Comment by Mike Anderson [ 15/Apr/14 11:02 PM ]|
Well I'm not a JDBC expert so don't want to give too strong an opinion on API design
My feeling is that some kind of variant of `db-do-commends`, perhaps with keyword arguments (which override a sensible default) would be useful. But that is only a hunch... and I'm not sure if it conflicts with any other assumptions in the design. And I certainly wouldn't want to complicate the API design if this is just a corner case / H2 quirk.
Perhaps just docstring changes that make clear the usage of executeQuery vs. execute vs. executeBatch etc. would be sufficient? I think that was what was fundamentally causing me some confusion.
Anyway I have a good workaround now. So feel free to close / re-purpose this issue as you wish!