<< Back to previous view

[JDBC-58] get-connection returns an error Created: 24/May/13  Updated: 24/May/13  Resolved: 24/May/13

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

Type: Defect Priority: Major
Reporter: Salil Wadnerkar Assignee: Sean Corfield
Resolution: Declined Votes: 0
Labels: None
Environment:

PostgreSQL


 Description   

My db-spec is:
(def db-spec {:classname "org.postgresql.Driver"
:subprotocol "postgresql"
:subname "sd_ventures_development"
:user "sd_ventures"
:password ""})

When I query using the old API:
(defn get-devices []
(jdbc/with-connection db-spec
(jdbc/with-query-results rs ["SELECT * FROM devices"]
(doall rs))))

it works and I get the list of devices.

With the new API -
(defn get-devices []
(let [conn (jdbc/get-connection db-spec)]
(jdbc/query conn ["SELECT * FROM devices"])))
it returns an error:
Exception: java.lang.IllegalArgumentException: db-spec org.postgresql.jdbc4.Jdbc4Connection@4c54daa9 is missing a required parameter
jdbc.clj:221 clojure.java.jdbc/get-connection
jdbc.clj:628 clojure.java.jdbc/db-with-query-results*

I am completely bowled over by this error. I checked the source code. It should never reach that statement in get-connection because I am supplying both subname and subprotocol.

 Comments   
Comment by Sean Corfield [ 24/May/13 11:10 AM ]

Not a bug. You are using the API incorrectly. query takes a db-spec NOT a connection:

(jdbc/query db-spec ["SELECT * FROM devices"])




[JDBC-46] A mechanism for reading specialized types from result sets could be useful Created: 23/Jan/13  Updated: 21/May/13  Resolved: 19/Apr/13

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

Type: Enhancement Priority: Major
Reporter: Gary Fredericks Assignee: Sean Corfield
Resolution: Completed Votes: 0
Labels: None

Attachments: Text File JDBC-46-p1.patch     Text File JDBC-46-p2.patch     Text File JDBC-46-p3.patch    

 Description   

I had this idea when trying to adapt Korma to use with postgres tables with columns of array types. I had some success using Korma's transform functions to convert the java.sql.Array objects into a seq on the way out, but recent changes to Korma seem to indicate that the transform functions are not guaranteed to run before the connection is closed. This interferes with efforts to read the arrays, which I think can be their own result sets.

Presumably korma could be changed to support reading the arrays on the way out, but maybe being able to customize the read behavior within c.j.jdbc would be simpler.

The first idea I've had toward this end is to create a protocol with a single function that objects being read out of the resultset get passed through. It would default to the identity function, but users could e.g. extend it to java.sql.Array. I don't know if there are significant performance implications for an extra function call for each value coming out of the database.

I'll have a patch in this vein attached shortly.

 Comments   
Comment by Gary Fredericks [ 23/Jan/13 10:26 AM ]

Attached patch. Is there any straightforward way to test this? Or a place it should be documented?

Comment by Sean Corfield [ 23/Jan/13 10:48 AM ]

Docs: just needs good docstring since the clojure.github.com site is auto-generated from those (although it is still triggered manually it seems).

Test: mvn test

I have Derby, HSQLDB, MySQL, MS SQL Server (two drivers), MySQL. I don't have Postgres to test against. By default mvn test only tries Derby, HSQLDB and SQLite. TEST_DBS=... mvn test will test more.

Comment by Sean Corfield [ 23/Jan/13 10:49 AM ]

Would be nice to have a test with a custom conversion, mapping any String to some constant perhaps? Perhaps a separate test ns that is required and tested from test_jdbc.clj?

Comment by Gary Fredericks [ 23/Jan/13 2:38 PM ]

Oh, we have to be careful with testing because I believe any extensions to the protocol are going to be effective for all the remaining tests, right?

Comment by Gary Fredericks [ 23/Jan/13 3:53 PM ]

Attached new patch with docstrings and a test. I've only run the tests with MySQL. Let me know if there's anything else I can do.

Comment by Gary Fredericks [ 29/Jan/13 4:37 PM ]

This patch recently came in handy in an different situation, when jdbc was returning Integers instead of Longs, we had a really clean spot to do the conversion.

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

There have been a lot of code changes since you submitted the patch and right now the patch doesn't apply. Can you re-submit the patch against the latest master branch so I can try again? Thanx!

Comment by Gary Fredericks [ 11/Apr/13 9:32 PM ]

I'm getting 3 test failures on master currently (with a basic mvn test), and after applying this new patch (p3) I still get 3 failures. So hopefully that means I didn't break anything.

Comment by Sean Corfield [ 19/Apr/13 9:34 PM ]

Applied. Will be in 0.3.0-alpha2

Comment by Gary Fredericks [ 15/May/13 9:52 AM ]

I'm thinking this feature is no longer necessary due to the :row-fn option – since the only motivation in the first place was to have the opportunity to transform a result before the connection closes.

There would still be no easy way to do it from Korma, but that's Korma's fault. So maybe we should remove this before 0.3.0 is released?

Comment by Sean Corfield [ 21/May/13 7:19 PM ]

This turns out to be a very useful hook for certain database types. I used it to solve an issue with Oracle, to enable users to automatically convert Oracle-specific datatypes to regular values, in JDBC-57. So, thank you... even if you think it isn't necessary now!

Comment by Gary Fredericks [ 21/May/13 7:53 PM ]

That use case could be served by :row-fn, right? You just like that it's easier? I don't mind it if others still find it valuable, I just wanted to make sure we didn't accidentally cement redundant features.

Comment by Sean Corfield [ 21/May/13 9:40 PM ]

:row-fn is intended for whole-row transformations - it is lazily mapped over the result set - so it's really meant for post-processing your data in a general sense while the connection is open. The protocol-based column reader is intended to transform specific types of data, independent of any application semantics (e.g., Oracle returns an oracle.sql.TIMESTAMP which can be automatically transformed into a java.sql.Timestamp or Date or whatever).

And :result-set-fn is intended for whole-result-set transformation, just prior to returning the result set.




[JDBC-56] Problem with find-connection when using agents with c3p0 connection pool Created: 16/May/13  Updated: 21/May/13

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

Type: Defect Priority: Minor
Reporter: Ed O'Loughlin Assignee: Sean Corfield
Resolution: Unresolved Votes: 0
Labels: None
Environment:

OS X 10.8.3
Java 1.6.0_45
Clojure 1.5
c3p0 0.9.1.2
java.jdbc 0.2.1 (the problem also seems to be there in HEAD at the time of logging this)


 Description   

I use an agent to offload email processing and update a db on completion. If I use a c3p0 connection pool, I get an exception and a complaint that the connection isn't open (see stacktrace). The code works if I wrap the agent fn in a clojure.java.jdbc/with-connection and get a connection from the pool in the agent thread.

The in jdbc.clj, the find-connection (db-find-connection in HEAD) checks for an existing connection and tries to use it without checking if it's open. This causes an exception with a c3p0 connection that was acquired in another thread.

Here's the stacktrace I'm seeing (note the "You can't operate on a closed Connection!!!"):

2013-05-16 10:32:22,208 [clojure-agent-send-off-pool-7] DEBUG com.mchange.v2.sql.SqlUtils - Converting Throwable to SQLException...
java.lang.NullPointerException
	at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:186)
	at clojure.java.jdbc$prepare_statement.doInvoke(jdbc.clj:450)
	at clojure.lang.RestFn.invoke(RestFn.java:425)
	at clojure.lang.AFn.applyToHelper(AFn.java:163)
	at clojure.lang.RestFn.applyTo(RestFn.java:132)
	at clojure.core$apply.invoke(core.clj:621)
	at clojure.java.jdbc$with_query_results_STAR_.invoke(jdbc.clj:646)
	at clj_record.core$find_by_sql$func__3362__auto____3378.invoke(core.clj:72)
	at clj_record.core$find_by_sql.invoke(core.clj:71)
	at clj_record.core$find_records.invoke(core.clj:85)
	at clj_record.core$find_record.invoke(core.clj:91)
	at neataudio.domain.interview_participant_relationship$find_record.invoke(interview_participant_relationship.clj:18)
	at neataudio.domain.interview_participant_relationship$update_email_status.invoke(interview_participant_relationship.clj:37)
	at neataudio.controllers.participant_controller$notify$fn__5113$fn__5114$func__3362__auto____5115$fn__5116.invoke(participant_controller.clj:104)
	at clojure.java.jdbc$transaction_STAR_$fn__3162.invoke(jdbc.clj:372)
	at clojure.java.jdbc$transaction_STAR_.invoke(jdbc.clj:371)
	at neataudio.controllers.participant_controller$notify$fn__5113$fn__5114$func__3362__auto____5115.invoke(participant_controller.clj:101)
	at neataudio.controllers.participant_controller$notify$fn__5113$fn__5114.invoke(participant_controller.clj:101)
	at neataudio.controllers.participant_controller$notify$fn__5113.invoke(participant_controller.clj:101)
	at clojure.lang.AFn.applyToHelper(AFn.java:185)
	at clojure.lang.AFn.applyTo(AFn.java:151)
	at clojure.core$apply.invoke(core.clj:623)
	at clojure.core$binding_conveyor_fn$fn__4115.doInvoke(core.clj:1848)
	at clojure.lang.RestFn.applyTo(RestFn.java:146)
	at clojure.lang.Agent$Action.doRun(Agent.java:114)
	at clojure.lang.Agent$Action.run(Agent.java:163)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
	at java.lang.Thread.run(Thread.java:680)
java.sql.SQLException: You can't operate on a closed Connection!!!
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
	at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:222)
	at clojure.java.jdbc$prepare_statement.doInvoke(jdbc.clj:450)
	at clojure.lang.RestFn.invoke(RestFn.java:425)
	at clojure.lang.AFn.applyToHelper(AFn.java:163)
	at clojure.lang.RestFn.applyTo(RestFn.java:132)
	at clojure.core$apply.invoke(core.clj:621)
	at clojure.java.jdbc$with_query_results_STAR_.invoke(jdbc.clj:646)
	at clj_record.core$find_by_sql$func__3362__auto____3378.invoke(core.clj:72)
	at clj_record.core$find_by_sql.invoke(core.clj:71)
	at clj_record.core$find_records.invoke(core.clj:85)
	at clj_record.core$find_record.invoke(core.clj:91)
	at neataudio.domain.interview_participant_relationship$find_record.invoke(interview_participant_relationship.clj:18)
	at neataudio.domain.interview_participant_relationship$update_email_status.invoke(interview_participant_relationship.clj:37)
	at neataudio.controllers.participant_controller$notify$fn__5113$fn__5114$func__3362__auto____5115$fn__5116.invoke(participant_controller.clj:104)
	at clojure.java.jdbc$transaction_STAR_$fn__3162.invoke(jdbc.clj:372)
	at clojure.java.jdbc$transaction_STAR_.invoke(jdbc.clj:371)
	at neataudio.controllers.participant_controller$notify$fn__5113$fn__5114$func__3362__auto____5115.invoke(participant_controller.clj:101)
	at neataudio.controllers.participant_controller$notify$fn__5113$fn__5114.invoke(participant_controller.clj:101)
	at neataudio.controllers.participant_controller$notify$fn__5113.invoke(participant_controller.clj:101)
	at clojure.lang.AFn.applyToHelper(AFn.java:185)
	at clojure.lang.AFn.applyTo(AFn.java:151)
	at clojure.core$apply.invoke(core.clj:623)
	at clojure.core$binding_conveyor_fn$fn__4115.doInvoke(core.clj:1848)
	at clojure.lang.RestFn.applyTo(RestFn.java:146)
	at clojure.lang.Agent$Action.doRun(Agent.java:114)
	at clojure.lang.Agent$Action.run(Agent.java:163)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
	at java.lang.Thread.run(Thread.java:680)
Caused by: java.lang.NullPointerException
	at com.mchange.v2.c3p0.impl.NewProxyConnection.prepareStatement(NewProxyConnection.java:186)
	... 28 more


 Comments   
Comment by Ed O'Loughlin [ 16/May/13 4:50 AM ]

Apologies: this is a crappy bug report. I'm under a bit of pressure at the moment so I can't make a test case.

Comment by Sean Corfield [ 21/May/13 12:36 PM ]

If you use the new API, you pass the db-spec into the functions and it will take care of getting the connection, using it, and closing it - and that will all happen on the same (agent) thread. I would not expect you to be able to get a connection in one thread and use it from another thread.

When you get a chance, please post an example of both the working and non-working code, but my initial thinking is this isn't a bug, just an inherent limitation of working with connections across threads....?




[JDBC-55] More reflection warnings to investigate Created: 14/May/13  Updated: 21/May/13  Resolved: 21/May/13

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

Type: Task Priority: Major
Reporter: Sean Corfield Assignee: Sean Corfield
Resolution: Completed Votes: 0
Labels: None

Attachments: Text File jdbc-55-fix-reflection-warnings-v1.txt    

 Description   

Reflection warning, clojure/java/jdbc.clj:618:50 - reference to field executeQuery can't be resolved.
Reflection warning, clojure/java/jdbc.clj:618:33 - reference to field close can't be resolved.

 Comments   
Comment by Andy Fingerhut [ 15/May/13 10:34 AM ]

Patch jdbc-55-fix-reflection-warnings-v1.txt dated May 15 2013 adds one more type hint that eliminates the two mentioned instances of reflection.

Comment by Sean Corfield [ 21/May/13 10:08 AM ]

Patch applied. Thank you!




[JDBC-57] Better support for Oracle Created: 18/May/13  Updated: 20/May/13  Resolved: 20/May/13

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

Type: Enhancement Priority: Major
Reporter: Manish Handa Assignee: Sean Corfield
Resolution: Completed Votes: 0
Labels: None
Environment:

Oracle 10g and above

Attachments: Text File datatype.patch     Text File oracle-fix.patch    

 Description   

This is just a suggestion, and i am not sure if it is the right way to do this.

When fetching data from Oracle the oracle jdbc driver returns all the numeric type columns as BigDecimal and date/datetime columns as oracle.sql.TIMESTAMP. This causes a lot of headache as we have to remember to convert the data to the correct type.

Although, this can be handled at the application level by using a :row-fn, it would be really nice if this library handles the conversion itself.

 Comments   
Comment by Manish Handa [ 18/May/13 2:10 PM ]

Patch for handling results returned by oracle jdbc thin driver

Comment by Sean Corfield [ 19/May/13 1:44 PM ]

I think the right solution here will be to modify the result-set-read-column protocol function to be passed rsmeta and i and then Oracle users can extend the protocol to those types and perform the conversions they want. I don't believe automcatically converting BigDecimal is going to be the right choice for all users although doing the conversion for oracle.sql.TIMESTAMP is a more arguable conversion.

Comment by Manish Handa [ 20/May/13 9:57 AM ]

Agreed. I thought of this, but it requires changing the protocol to take rsmeta along with the column index. i have uploaded another patch for this change.

Also, it'll be better to leave the handling of oracle.sql.Timestamp to the application as it needs a dependency on the oracle thin driver (which we should certainly not add to java.jdbc).

So, after this updated protocol, the oracle users can extend it with something like this (much better now):

;;; Oracle users can define this in their applications
;; to extend it to BigDecimal and other oracle types
(extend-protocol IResultSetReadColumn
oracle.sql.TIMESTAMP
(result-set-read-column [x _ _] (.timestampValue x))

java.math.BigDecimal
(result-set-read-column [val rsmeta idx]
(if-not (= (.getScale rsmeta idx) 0)
(.doubleValue val) ;;; reals
(if (= (.getPrecision rsmeta idx) 1)
(= 1 (.longValue val)) ;;; booleans
(.longValue val))))) ;; integrals

Comment by Sean Corfield [ 20/May/13 10:45 AM ]

Thanx. Yes, that was where I was going with this, and while we're in alpha for 0.3.0 I'm not averse to changing the signatures of new stuff introduced in the API rewrite. I'll review the patch later and probably roll it in this week (I just got back from a web tech conference in MN and I'm busy catching up on work!).

Comment by Sean Corfield [ 20/May/13 10:52 PM ]

Patch applied. Tests updated to match. Will be in 0.3.0-alpha5.




Generated at Sat May 25 12:40:23 CDT 2013 using JIRA 4.4#649-r158309.