java.jdbc

Issue with H2 "script" command

Details

  • Type: Defect Defect
  • Status: Resolved Resolved
  • Priority: Minor Minor
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None

Description

While trying to execute an H2 "script" statement as follows:

(defn script []
(jdbc/execute! db [(str "script to '" "dbscript.txt" "';")]))

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:
script to 'dbscript.txt'; [90001-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
at org.h2.message.DbException.get(DbException.java:149)
at org.h2.message.DbException.get(DbException.java:138)
at org.h2.command.Prepared.update(Prepared.java:200)
at org.h2.command.CommandContainer.update(CommandContainer.java:79)
at org.h2.command.Command.executeUpdate(Command.java:253)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140)
at clojure.java.jdbc$db_do_prepared$fn__339.invoke(jdbc.clj:639)
at clojure.java.jdbc$db_transaction_STAR_.doInvoke(jdbc.clj:514)
at clojure.lang.RestFn.invoke(RestFn.java:425)
at clojure.java.jdbc$db_do_prepared.doInvoke(jdbc.clj:638)
at clojure.lang.RestFn.invoke(RestFn.java:442)
at clojure.java.jdbc$execute_BANG_$execute_helper__368.invoke(jdbc.clj:738)
at clojure.java.jdbc$execute_BANG_.doInvoke(jdbc.clj:742)

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"?

Activity

Hide
Sean Corfield added a comment -

Resolved by improving docstrings and adding official support for H2 (and testing against it!).

The specific solution for this script issue is to use (query ...) because that's what the underlying H2 driver wants!

Show
Sean Corfield added a comment - Resolved by improving docstrings and adding official support for H2 (and testing against it!). The specific solution for this script issue is to use (query ...) because that's what the underlying H2 driver wants!
Hide
Sean Corfield added a comment -

Given that Mike has a workaround, lowering priority to Minor. Probably best addressed by docstring updates at this point.

Show
Sean Corfield added a comment - Given that Mike has a workaround, lowering priority to Minor. Probably best addressed by docstring updates at this point.
Hide
Mike Anderson added a comment -

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!

Show
Mike Anderson added a comment - 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!
Hide
Sean Corfield added a comment -

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?

Show
Sean Corfield added a comment - 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?
Hide
Mike Anderson added a comment -

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?

Show
Mike Anderson added a comment - 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?
Hide
Mike Anderson added a comment -

Hi Sean, I still seem to get an error:

(j/db-do-commands db (str "SCRIPT TO '" "dbscript.txt" "';"))
=>
JdbcBatchUpdateException Method is not allowed for a query. Use execute or executeQuery instead of executeUpdate; SQL statement:
SCRIPT TO 'dbscript.txt'; [90001-175] org.h2.jdbc.JdbcStatement.executeBatch (JdbcStatement.java:672)

Show
Mike Anderson added a comment - Hi Sean, I still seem to get an error: (j/db-do-commands db (str "SCRIPT TO '" "dbscript.txt" "';")) => JdbcBatchUpdateException Method is not allowed for a query. Use execute or executeQuery instead of executeUpdate; SQL statement: SCRIPT TO 'dbscript.txt'; [90001-175] org.h2.jdbc.JdbcStatement.executeBatch (JdbcStatement.java:672)
Hide
Sean Corfield added a comment -

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?

Show
Sean Corfield added a comment - 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?

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: