<< Back to previous view

[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 Apr 19 20:07:03 CDT 2014 using JIRA 4.4#649-r158309.