<< Back to previous view

[JDBC-104] Prepare-statement should support passing an array of column names to return auto-generated keys Created: 29/Nov/14  Updated: 11/Mar/15

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

Type: Enhancement Priority: Minor
Reporter: Harri Ohra-aho Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


 Description   

https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#prepareStatement(java.lang.String,%20java.lang.String[])

This would be essential for proper Oracle integration as the current use of java.sql.Statement/RETURN_GENERATED_KEYS only returns the Oracle RowID.



 Comments   
Comment by Sean Corfield [ 11/Mar/15 1:29 PM ]

This could be supported by allowing :return-keys to be a vector (of String) – in addition to just a boolean – but I will note that you can already create the PreparedStatement directly yourself and pass whatever arguments you want and then use that object in other calls (so I'm lowering the priority). It's a good enhancement suggestion tho'!





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

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

Type: Defect Priority: Minor
Reporter: Mike Anderson Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
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"?



 Comments   
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!

Comment by Sean Corfield [ 28/Oct/14 6:48 PM ]

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





[JDBC-88] Update :arglists metadata so it no longer confuses Eastwood Created: 13/Jan/14  Updated: 16/Jan/15

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

Type: Task Priority: Minor
Reporter: Sean Corfield Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


 Description   

:arglists metadata should be valid Clojure syntax. Examples should be moved into docstrings.



 Comments   
Comment by Andy Fingerhut [ 13/Jan/14 4:02 PM ]

Eastwood's use of :arglists for :wrong-arity linter checking is definitely 'at odds' with the way java.jdbc and some other libraries use it for clearer documentation.

I would recommend leaving :arglists as-is for java.jdbc, and wait for the next version of Eastwood to add a capability to have something like an :eastwood-arglists for functions that explicitly replace their :arglists like this. Such a capability might best belong in Eastwood itself, since it is the new tool on the block.

As an example, I was considering that if a fn/macro explicitly chose to be Eastwood-friendly in this way, it could have an :arglists and :eastwood-arglists explicitly, for those few vars that replace their :arglists. When one is using Eastwood with a library that they do not want to modify in that way, there could be some extra config file for Eastwood where one could specify these :eastwood-arglists.

Sound reasonable?

Comment by Sean Corfield [ 28/Oct/14 6:47 PM ]

Given Andy's comment back in January, lowering priority to Minor.

Comment by Sean Corfield [ 16/Jan/15 1:14 PM ]

Eastwood has recently added the ability to provide configuration to override specific function :arglists and out of the box comes with java.jdbc config to override the problematic functions: https://github.com/jonase/eastwood/blob/master/resource/eastwood/config/clojure-contrib.clj#L38-64

That means, as far as Eastwood is concerned, this is a non-issue, but it would be nice to fix those functions' metadata anyway.





[JDBC-1] Provide option to return SQL generated / execution stats Created: 08/May/11  Updated: 16/Jan/15

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

Type: Enhancement Priority: Minor
Reporter: Sean Corfield Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Shantanu: Provide a mechanism to show the SQL being executed (configurable, so that it can be turned off)

Sean: Good idea. Even better, a way to access statistics about the prepared statement after execution - timing etc?

Shantanu: Yes, that would be an add-on value to show how are the queries performing.



 Comments   
Comment by Sean Corfield [ 15/Sep/13 4:21 PM ]

Post 0.3.0

Comment by Sean Corfield [ 16/Jan/15 1:17 PM ]

Sounds like not all drivers can support this but PreparedStatement.toString() returns the generated SQL for some databases: http://stackoverflow.com/questions/2683214/get-query-from-java-sql-preparedstatement





Generated at Mon Jul 06 19:39:45 CDT 2015 using JIRA 4.4#649-r158309.