java.jdbc

Better support for Oracle

Details

  • Type: Enhancement Enhancement
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Completed
  • Affects Version/s: None
  • Fix Version/s: None
  • Component/s: None
  • Labels:
    None
  • Environment:
    Oracle 10g and above

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.

  1. datatype.patch
    20/May/13 9:57 AM
    2 kB
    Manish Handa
  2. oracle-fix.patch
    18/May/13 2:10 PM
    2 kB
    Manish Handa

Activity

Hide
Manish Handa added a comment -

Patch for handling results returned by oracle jdbc thin driver

Show
Manish Handa added a comment - Patch for handling results returned by oracle jdbc thin driver
Manish Handa made changes -
Field Original Value New Value
Attachment oracle-fix.patch [ 11997 ]
Hide
Sean Corfield added a comment -

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.

Show
Sean Corfield added a comment - 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.
Hide
Manish Handa added a comment -

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

Show
Manish Handa added a comment - 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
Manish Handa made changes -
Attachment datatype.patch [ 11999 ]
Hide
Sean Corfield added a comment -

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!).

Show
Sean Corfield added a comment - 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!).
Hide
Sean Corfield added a comment -

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

Show
Sean Corfield added a comment - Patch applied. Tests updated to match. Will be in 0.3.0-alpha5.
Sean Corfield made changes -
Resolution Completed [ 1 ]
Status Open [ 1 ] Resolved [ 5 ]

People

Vote (0)
Watch (0)

Dates

  • Created:
    Updated:
    Resolved: