Clojure JIRAhttp://dev.clojure.org/jira/secure/IssueNavigator.jspa?reset=true&jqlQuery=project+%3D+JDBC+AND+resolution+%3D+Unresolved+AND+fixVersion+is+EMPTY+ORDER+BY+priority+DESC
An XML representation of a search requesten-us4.464925-07-2011[JDBC-37] Provide support for alternate transaction strategieshttp://dev.clojure.org/jira/browse/JDBC-37
java.jdbc<p>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.</p>JDBC-37Provide support for alternate transaction strategiesEnhancementMajorOpenUnresolvedSean CorfieldJim CrossleyTue, 31 Jul 2012 17:48:54 -0500Sun, 15 Sep 2013 21:41:32 -050000<p>I'll try to work up a straw-man solution and submit a pull-request.</p><p>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 <a href="http://clojure.org/contributing">http://clojure.org/contributing</a></p><p>Sure thing, Sean</p><p>Sean, here's a first whack introducing a new dynamic var for transaction strategy. </p>
<p>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.</p>
<p>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.</p>
<p>The Agreement is in the mail. I appreciate your feedback.</p><p>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!</p><p>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.</p><p>See "Using Transactions" here <a href="https://github.com/clojure/java.jdbc/blob/master/doc/clojure/java/jdbc/UsingSQL.md">https://github.com/clojure/java.jdbc/blob/master/doc/clojure/java/jdbc/UsingSQL.md</a></p><p>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.</p><p>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.</p>
<p>So, bottom line: I expect very few users to actually use it explicitly, unless they specifically need to turn off the implicit transaction wrapping.</p>
<p>And for most of the API that users will interact with, they don't even need to worry about it.</p>
<p>Does that help?</p><p>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 <b>dynamic-vars</b> is deprecated now and will be completely removed before 1.0.0).</p>
<p>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'...</p><p>Regarding dynamically bound variables, I think it's very common and accepted &#8211; even canonical? &#8211; to use them (or some ThreadLocal-like variant) to implement transactions. I would hate to make the api awkward just to avoid them.</p>
<p>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.</p>
<p>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".</p>
<p>One question: what do you mean by "implementation artifact" for nesting calls?</p><p>Sean, how do you feel about turning the <tt>:transactional?</tt> option to a function instead of a boolean? And that function represents the <tt>:tx-strategy</tt> used, which could assume the value of a dynamically bound value <tt>&#42;tx-strategy&#42;</tt> by default, and its value would be <tt>db-transaction*</tt> by default. And folks could set it to nil to turn off transactions, i.e. <tt>:tx-strategy nil</tt> or perhaps <tt>:tx-strategy :none</tt> would equate to <tt>:transactional? false</tt>. I think that may satisfy core's recommendation for dynamic variables not being the <b>only</b> way to alter behavior. Make sense at all? </p><p>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 <b>dynamic</b> 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).</p><p>You're killing me! <img class="emoticon" src="http://dev.clojure.org/jira/images/icons/emoticons/smile.gif" height="20" width="20" align="absmiddle" alt="" border="0"/></p>
<p>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 <b>effectively</b> 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."</p>
<p>What was the specific feedback you received that contradicts that?</p><p>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 <tt>db-transaction*</tt>, I no longer have a need for it.</p>
<p>Keep up the good work on java.jdbc!</p><p>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.</p><p>Something else you might consider: define a protocol function that encapsulates your <tt>commit/rollback/setAutoCommit</tt> logic inside <tt>db-transaction*</tt> and extend it to <tt>java.sql.Connection</tt>. That way, folks could extend their more specific types, e.g. <tt>XAConnection</tt>, to your protocol (and avoid making those calls that aren't allowed by XA). </p><p>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.</p>Global Rank[JDBC-64] Support multiple result sets?http://dev.clojure.org/jira/browse/JDBC-64
java.jdbc<p>Useful for stored procedure results:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeHeader panelHeader" style="border-bottom-width: 1px;"><b>call_proc.clj</b></div><div class="codeContent panelContent">
<pre class="code-java">(defn call-stored-proc [connection]
(jdbc/query
(myapp.db/connection)
[<span class="code-quote">"{call someProc()}"</span>]
:as-arrays? <span class="code-keyword">true</span>))</pre>
</div></div>
<p>Java code to handle multiple result sets:</p>
<div class="code panel" style="border-width: 1px;"><div class="codeHeader panelHeader" style="border-bottom-width: 1px;"><b>MultiResults.java</b></div><div class="codeContent panelContent">
<pre class="code-java"><span class="code-keyword">public</span> <span class="code-keyword">static</span> void executeProcedure(Connection con) {
<span class="code-keyword">try</span> {
CallableStatement stmt = con.prepareCall(...);
..... <span class="code-comment">//Set call parameters, <span class="code-keyword">if</span> you have IN,OUT, or IN/OUT parameters
</span>
<span class="code-object">boolean</span> results = stmt.execute();
<span class="code-object">int</span> rsCount = 0;
<span class="code-comment">//Loop through the available result sets.
</span> <span class="code-keyword">while</span> (results) {
ResultSet rs = stmt.getResultSet();
<span class="code-comment">//Retrieve data from the result set.
</span> <span class="code-keyword">while</span> (rs.next()) {
....<span class="code-comment">// using rs.getxxx() method to retieve data
</span> }
rs.close();
<span class="code-comment">//Check <span class="code-keyword">for</span> next result set
</span> results = stmt.getMoreResults();
}
stmt.close();
}
<span class="code-keyword">catch</span> (Exception e) {
e.printStackTrace();
}
}</pre>
</div></div>JDBC-64Support multiple result sets?EnhancementMajorOpenUnresolvedSean CorfieldSean CorfieldWed, 3 Jul 2013 21:38:26 -0500Mon, 15 Dec 2014 14:58:53 -060000<p>Post 0.3.0, ideally after adding stored proc support properly (see <a href="http://dev.clojure.org/jira/browse/JDBC-48" title="Support stored procedures with CallableStatement">JDBC-48</a>).</p><p>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.</p><p>Discussion with Pieter Laeremans:</p>
<p>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 <img class="emoticon" src="http://dev.clojure.org/jira/images/icons/emoticons/smile.gif" height="20" width="20" align="absmiddle" alt="" border="0"/></p>
<p>Pieter: That sounds fine by me. But there's something a bit more subtle I guess, <br/>
Now you can pass a function row-fn to transform rows, in the multi-resultset case it would perhaps be more appropriate <br/>
to pass on a seq of row-fns, so that a different function can be used on different rows.</p><p>Any updates on the issue?</p><p>No update yet. No one has submitted a patch and I've been too busy to look at this in detail.</p>Global Rank[JDBC-48] Support stored procedures with CallableStatementhttp://dev.clojure.org/jira/browse/JDBC-48
java.jdbc<p>JDBC's <tt>CallableStatement</tt> provides support for calling stored procedures. More specifically, it allows you to register OUT parameters which will become the statements (possibly many) <tt>ResultSet</tt> objects. A <tt>CallableStatement</tt> <em>is a</em> <tt>PreparedStatement</tt>, 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.</p>
<p>I'll be hacking on this, so patches are forthcoming. Any input is appreciated.</p>JDBC-48Support stored procedures with CallableStatementEnhancementMajorOpenUnresolvedSean CorfieldJeremy HeilerFri, 15 Mar 2013 13:50:23 -0500Sun, 15 Sep 2013 16:20:40 -050012<p>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!</p><p>Post 0.3.0. See also <a href="http://dev.clojure.org/jira/browse/JDBC-64" title="Support multiple result sets?">JDBC-64</a>.</p>Global Rank[JDBC-99] The age of reduce is upon ushttp://dev.clojure.org/jira/browse/JDBC-99
java.jdbc<p>jdbc code is pretty heavily in to resource management, you have connections, result sets, prepared statements all of which require lifetime management.</p>
<p>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.</p>
<p>clojure provides a mechanism to define a collection in terms of reduce and a growing collection of ways to manipulate and transform reducible collections.</p>
<p>a collection that knows how to reduce itself has a means of managing the lifetime of associated resources, the lifetime of the reduce operation.</p>
<p>so it seems clear that result-set-seqs should be replaced with result-set-reducibles.</p>JDBC-99The age of reduce is upon usEnhancementMajorOpenUnresolvedSean CorfieldKevin DowneySun, 31 Aug 2014 14:27:00 -0500Mon, 8 Sep 2014 13:15:57 -050032<p>Something like this would be amenable to reduce/transduce. Used in conjunction with db-query-with-resultset<br/>
<a href="https://gist.github.com/ghadishayban/d2f31961deba98ee4595">https://gist.github.com/ghadishayban/d2f31961deba98ee4595</a></p>
<p>Half of the knobs on jdbc/query are to control seq realization, but instead should defer to reduce/reduced</p>
<p>i.e.<br/>
(into [] (take 5000) (queryr "select * from foo"))</p>
<p>The reducible collection returned should be one-shot, cleaning up resources, and it would be an error to run more than once</p>Global Rank[JDBC-143] Make it easier to test java.jdbc in other environmentshttp://dev.clojure.org/jira/browse/JDBC-143
java.jdbc<p>Make the versions properties in <tt>pom.xml</tt> and/or <tt>project.clj</tt>, make the database names, usernames, and passwords configurable via environment variables.</p>
<p>This came up via a suggestion from the PostgreSQL community &#8211; see this pull request for guidance on what we could open up: <a href="https://github.com/clojure/java.jdbc/pull/44/files">https://github.com/clojure/java.jdbc/pull/44/files</a></p>JDBC-143Make it easier to test java.jdbc in other environmentsEnhancementMajorOpenUnresolvedSean CorfieldSean CorfieldSat, 17 Sep 2016 17:57:35 -0500Sat, 17 Sep 2016 17:57:35 -050000Global Rank