<< Back to previous view

[JDBC-91] Issue with H2 "script" command Created: 20/Feb/14  Updated: 15/Apr/14

Status: In Progress
Project: java.jdbc
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Defect Priority: Major
Reporter: Mike Anderson Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


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

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" "';"))
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)

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!

[JDBC-95] Evaluate body in context of provided java.sql.Connection without closing it finally Created: 14/Apr/14  Updated: 14/Apr/14  Resolved: 14/Apr/14

Status: Resolved
Project: java.jdbc
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Enhancement Priority: Minor
Reporter: Esko Lahti Assignee: Sean Corfield
Resolution: Declined Votes: 0
Labels: None


There are situations where:

  • There is Java code calling Clojure functions that access database.
  • Java code has already opened java.sql.Connection instance that should be to access the database instead of opening new connection at the Clojure side.

The with-db-connection macro could be used for this with {:connection conn} db spec. However as it wraps the body inside a with-open macro the provided connection will be closed in the end.

Practically the following macro could fix the problem. However I don't know if the already existing with-db-connection macro should be extended instead. Also skipped the documentation as this is only an example:

(defmacro with-provided-conn
[binding & body]
`(let [~(first binding) (jdbc/add-connection {} ~(second binding))]

Comment by Sean Corfield [ 14/Apr/14 11:01 AM ]

with-db-connection is a recent addition - as a convenience for users who want to open and close a connection for a specific piece of code. The default mode of operation in java.jdbc wass for users to manage their own connection. Your macro removes useful information from the db-spec. A more useful approach would be: (jdbc/add-connection db-spec (jdbc/get-connection db-spec)) so that the db-spec is maintained in the "connected spec". However, that requires the db-spec be passed in and locks you into calling get-connection which not all code may want. What you're asking for is not provided for a reason: it isn't universally applicable to all java.jdbc users - therefore I do not think it belongs in the library.

Generated at Sat Apr 19 02:14:56 CDT 2014 using JIRA 4.4#649-r158309.