<< Back to previous view

[JDBC-48] Support stored procedures with CallableStatement Created: 15/Mar/13  Updated: 15/Sep/13

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

Type: Enhancement Priority: Major
Reporter: Jeremy Heiler Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


JDBC's CallableStatement provides support for calling stored procedures. More specifically, it allows you to register OUT parameters which will become the statements (possibly many) ResultSet objects. A CallableStatement is a PreparedStatement, so I am hoping there wont be too much involved with regard to executing them. The main difference is being able to register and consume OUT parameters.

I'll be hacking on this, so patches are forthcoming. Any input is appreciated.

Comment by Sean Corfield [ 15/Mar/13 10:51 PM ]

I've never used stored procs (I don't like the complexity that I've seen them add to version control, change management and deployment) so I'm afraid I can't offer any input - but I really appreciate you taking this on! Thank you!

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

Post 0.3.0. See also JDBC-64.

[JDBC-37] Provide support for alternate transaction strategies Created: 31/Jul/12  Updated: 15/Sep/13

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

Type: Enhancement Priority: Major
Reporter: Jim Crossley Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None

Attachments: File jdbc-37.diff    


The current design of java.jdbc prevents its use as a participant in a distributed XA transaction, where transactional control is delegated to a TransactionManager. It only works with local transactions and absorbs all nested transactions into the outermost one. It'd be nice to have a clean way to override this default behavior.

Comment by Jim Crossley [ 31/Jul/12 5:49 PM ]

I'll try to work up a straw-man solution and submit a pull-request.

Comment by Sean Corfield [ 31/Jul/12 5:59 PM ]

Thanx Jim. I agree the current setup isn't ideal in that area. As for "pull-request", I assume you mean a patch attached to this JIRA ticket (since Clojure and contrib projects cannot accept pull requests). Please also make sure you get a Contributor's Agreement on file per http://clojure.org/contributing

Comment by Jim Crossley [ 31/Jul/12 6:25 PM ]

Sure thing, Sean

Comment by Jim Crossley [ 02/Aug/12 6:31 PM ]

Sean, here's a first whack introducing a new dynamic var for transaction strategy.

I know the desire is for a new API with explicit parameter passing, and when that vision congeals, I'm happy to help migrate, but I'd like to always have the option of the dynamic binding as well.

My thinking is that if a tx strategy function is passed as a parameter, it'll override whatever may be set in the dynamic var, but how it gets passed is still unclear to me. I considered adding an optional key to the db-spec, but wanted to run that by you first.

The Agreement is in the mail. I appreciate your feedback.

Comment by Sean Corfield [ 07/Apr/13 3:46 AM ]

There have been a lot of code changes lately and this patch no longer applies cleanly. Can you submit a new patch against the latest master? Thanx!

Comment by Jim Crossley [ 30/Apr/13 5:43 PM ]

Sean, I'm not sure I'm totally smitten with the new "transaction?" boolean parameter. At first glance, this seems an awkward way to define a transaction consisting of multiple statements. Can you provide an example usage with the new API of say, inserting, updating and deleting data within a single transaction? I'm hoping an example will clear up my confusion and I can propose a way of parameterizing a particular strategy for executing any transaction.

Comment by Sean Corfield [ 30/Apr/13 5:47 PM ]

See "Using Transactions" here https://github.com/clojure/java.jdbc/blob/master/doc/clojure/java/jdbc/UsingSQL.md

Comment by Jim Crossley [ 30/Apr/13 6:30 PM ]

Yes, I saw that, and it seemed to confirm that my original patch should work with minor tweaking. And then I was surprised to see the "transactional?" option in the source. I was curious how you expect it to be used.

Comment by Sean Corfield [ 30/Apr/13 6:58 PM ]

Folks have asked for the ability to run various functions without an implicit transaction inside them - in fact for some DBs, certain commands cannot be run inside a transaction which was a problem with the old API where you couldn't turn that off. It allows users to have more explicit control over transactions and it's also a convenient "implementation artifact" for nesting calls.

So, bottom line: I expect very few users to actually use it explicitly, unless they specifically need to turn off the implicit transaction wrapping.

And for most of the API that users will interact with, they don't even need to worry about it.

Does that help?

Comment by Sean Corfield [ 30/Apr/13 7:04 PM ]

Addressing your question about your patch: Clojure/core specifically wanted java.jdbc to move away from dynamically bound variables, which the new API / implementation achieves (given that all the old API that depends on dynamic-vars is deprecated now and will be completely removed before 1.0.0).

If all you need is the ability to specify how the transaction function does its job, via a HOF, then I'll have a look at what that would take in the context of the new 'world'...

Comment by Jim Crossley [ 30/Apr/13 7:33 PM ]

Regarding dynamically bound variables, I think it's very common and accepted – even canonical? – to use them (or some ThreadLocal-like variant) to implement transactions. I would hate to make the api awkward just to avoid them.

But to answer the core question, yes, I think it's important to provide an alternative to the assumptions encoded into db-transaction*, e.g. "Any nested transactions are absorbed into the outermost transaction." I might prefer a strategy in which a nested transaction suspends the current one and creates another, assuming the driver supports it.

But my primary reason for this, as you know, is to somehow inject a "null strategy" to support distributed transactions, delegating the commit/rollback choice to an external "transaction manager".

One question: what do you mean by "implementation artifact" for nesting calls?

Comment by Jim Crossley [ 01/May/13 9:46 AM ]

Sean, how do you feel about turning the :transactional? option to a function instead of a boolean? And that function represents the :tx-strategy used, which could assume the value of a dynamically bound value *tx-strategy* by default, and its value would be db-transaction* by default. And folks could set it to nil to turn off transactions, i.e. :tx-strategy nil or perhaps :tx-strategy :none would equate to :transactional? false. I think that may satisfy core's recommendation for dynamic variables not being the only way to alter behavior. Make sense at all?

Comment by Sean Corfield [ 01/May/13 10:57 AM ]

I was looking at the code again last night and came to much the same conclusion! I'll take a run at that this weekend (but I'm not adding a dynamic variable - Clojure/core were very clear about their reasons for not wanting those in code except in extremely rare situations in code that is guaranteed to be single-threaded).

Comment by Jim Crossley [ 01/May/13 11:22 AM ]

You're killing me!

Without the dynamic var, I can't see any way to transparently allow the db code to participate in a distributed transaction. Can we at least agree that transactional code is guaranteed to be effectively single-threaded? And by this I mean that a transaction must be associated with a single connection, so any thread using that connection must have exclusive access. Do you really want to force folks using distributed transactions to pass the tx strategy in with every call? I don't think adding the dynamic var and the option to override it violates the spirit of this guideline: "If you present an interface that implicitly passes a parameter via dynamic binding (e.g. db in sql), also provide an identical interface but with the parameter passed explicitly."

What was the specific feedback you received that contradicts that?

Comment by Jim Crossley [ 03/May/13 6:44 AM ]

Sean, I came up with a different solution for using java.jdbc with an XA connection that obviates this issue. So even though I think it's useful to provide both a dynamic var and a function option as a means to override the logic of db-transaction*, I no longer have a need for it.

Keep up the good work on java.jdbc!

Comment by Sean Corfield [ 03/May/13 12:40 PM ]

I like problems that go away of their own accord but I still like the idea of making the transaction strategy a function so I'll look at that anyway as a possible (breaking) change for alpha2.

Comment by Jim Crossley [ 03/May/13 12:50 PM ]

Something else you might consider: define a protocol function that encapsulates your commit/rollback/setAutoCommit logic inside db-transaction* and extend it to java.sql.Connection. That way, folks could extend their more specific types, e.g. XAConnection, to your protocol (and avoid making those calls that aren't allowed by XA).

Comment by Sean Corfield [ 15/Sep/13 9:41 PM ]

commentHaving spent some time looking at the transaction-as-function option, I don't think that's a great idea - partly because I'm not sure what alternative functions would look like. Jim's suggestion of a protocol for the internal transaction logic seems like a good one but at this point I'm not familiar enough with alternative strategies to know exactly how the protocol should look (and which parts of the internal db-transaction* logic should be implemented that way) so I'm going to punt on this for 0.3.0 but leave it open for the future.

[JDBC-99] The age of reduce is upon us Created: 31/Aug/14  Updated: 08/Sep/14

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

Type: Enhancement Priority: Major
Reporter: Kevin Downey Assignee: Sean Corfield
Resolution: Unresolved Votes: 2
Labels: None


jdbc code is pretty heavily in to resource management, you have connections, result sets, prepared statements all of which require lifetime management.

clojure.java.jdbc is built around result-set-seqs, sequences of results. but lazy-sequences provide no good way to manage the lifetime of resources behind the sequences.

clojure provides a mechanism to define a collection in terms of reduce and a growing collection of ways to manipulate and transform reducible collections.

a collection that knows how to reduce itself has a means of managing the lifetime of associated resources, the lifetime of the reduce operation.

so it seems clear that result-set-seqs should be replaced with result-set-reducibles.

Comment by Ghadi Shayban [ 08/Sep/14 1:15 PM ]

Something like this would be amenable to reduce/transduce. Used in conjunction with db-query-with-resultset

Half of the knobs on jdbc/query are to control seq realization, but instead should defer to reduce/reduced

(into [] (take 5000) (queryr "select * from foo"))

The reducible collection returned should be one-shot, cleaning up resources, and it would be an error to run more than once

[JDBC-64] Support multiple result sets? Created: 03/Jul/13  Updated: 15/Dec/14

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

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


Useful for stored procedure results:

(defn call-stored-proc [connection]
        ["{call someProc()}"]
        :as-arrays? true))

Java code to handle multiple result sets:

public static void executeProcedure(Connection con) {
       try {
          CallableStatement stmt = con.prepareCall(...);
          .....  //Set call parameters, if you have IN,OUT, or IN/OUT parameters
          boolean results = stmt.execute();
          int rsCount = 0;
          //Loop through the available result sets.
         while (results) {
               ResultSet rs = stmt.getResultSet();
               //Retrieve data from the result set.
               while (rs.next()) {
                ....// using rs.getxxx() method to retieve data
            //Check for next result set
            results = stmt.getMoreResults();
       catch (Exception e) {

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

Post 0.3.0, ideally after adding stored proc support properly (see JDBC-48).

Comment by Kyle Cordes [ 15/Sep/13 9:45 PM ]

With or without SPs, this would be an excellent addition; with some RDBMSs, use of compound statement (or SPs) with multiple result sets is relatively common.

Comment by Sean Corfield [ 02/Apr/14 6:07 PM ]

Discussion with Pieter Laeremans:

Sean: My thinking is that I would add :multi-result? to execute! and query and then arrange for them to return sequences of result sets. Unraveling the calls so multi-result? can work cleanly inside those functions would be the hard part

Pieter: That sounds fine by me. But there's something a bit more subtle I guess,
Now you can pass a function row-fn to transform rows, in the multi-resultset case it would perhaps be more appropriate
to pass on a seq of row-fns, so that a different function can be used on different rows.

Comment by Alexey Naumov [ 15/Dec/14 2:39 PM ]

Any updates on the issue?

Comment by Sean Corfield [ 15/Dec/14 2:58 PM ]

No update yet. No one has submitted a patch and I've been too busy to look at this in detail.

[JDBC-107] metadata-result leaks ResultSets Created: 12/Mar/15  Updated: 30/Jul/15

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

Type: Defect Priority: Major
Reporter: Niels van Klaveren Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None


When working with Oracle DatabaseMetaData, I noticed out of cursors exceptions (ORA-01000) when iterating over tables and requesting their .getIndexInfo and .getImportedKeys information. These errors tend to point to leaked resultsets. Investigating the metadata-result code I noticed this does happen, and the result set are just leaked silently on the other databases I tend to use.

A fix would include a default result-set-fn doall in the :or keys clause, and binding the true clause of the (if (instance? java.sql.ResultSet rs-or-value) to a new var, .close the rs-or-value, and return the new var.

Comment by Sean Corfield [ 12/Mar/15 11:57 AM ]

Thanks Niels. I'll take a look at that shortly. I may have some time next week.

Comment by Sean Corfield [ 18/May/15 11:38 AM ]

I finally got around to looking at this – apologies for the delay – and I'm not convinced metadata-result should close the ResultSet since that is passed in by the user. java.jdbc itself only ever calls metadata-result in a test so I updated that with the way I think it should be handled, by the user:

(sql/with-db-metadata [metadata db-spec]
  (with-open [result (.getTables metadata ...)]
    (let [table-info (sql/metadata-result result)]
      ... do stuff with table-info ...)))

The point here is that the user creates the ResultSet, not java.jdbc, so the user should close it correctly.

Comment by Niels van Klaveren [ 30/Jul/15 8:47 AM ]

The similarities between metadata-result and query (both having row-fn and result-set-fn) made me suppose abstracting away the connection handling of the result-set was the intention. It seems that assumption was a bit too hasty.

However, currently metadata-result IMO straddles uneasily between an implementation that just returns a result-set-seq or value, and one that can do all processing intended for abstracting away connection opening and closing, without actually doing so.

This does opens the way for a quite simple workaround that does though, a simple wrapping macro

(defmacro metadata-query
  [query & params]
  `(with-open [result# ~query]
     (j/metadata-result result# ~@params)))

which takes a java metadata call and the result set processing parameters for metadata-result, and processes the results in a with-open block according to the parameters.

(j/with-db-metadata [meta db-spec]
                    (metadata-query (.getTables meta nil nil nil (into-array String ["TABLE"]))
                                    :result-set-fn (fn [rs] (->> rs
                                                                 (filter #(= (:user db-spec) (:table_schem %)))
                                                                 (map :table_name)
Comment by Sean Corfield [ 30/Jul/15 10:59 AM ]

A new actionable suggestion has been made.

Comment by Sean Corfield [ 30/Jul/15 11:00 AM ]

That seems like a reasonable addition which would clarify the usage. Thank you.

[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


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!

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


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

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


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.

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

[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



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

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

Generated at Sun Aug 02 03:23:01 CDT 2015 using JIRA 4.4#649-r158309.