JDBC.jl is truncating values while retrieving Floating point numbers from Oracle

[bmharsha]

Issue is JDBC.jl is retrieving all the floating point columns as Float32, this is resulting in values being truncated while retrieving.

Following steps can be used to reproduce this issue

julia> executeUpdate(stmt, "create table temp(z1 FLOAT(100), z3 number(38,30))")
julia> executeUpdate(stmt, "insert into temp(z1,z3) values(42893.17904199423910638, 249.13865675685786780277)")
julia> rs = executeQuery(stmt, "select * from temp")
JavaCall.JavaObject{symbol("java.sql.ResultSet")}(Ptr{Void} @0x0000000010171538)

julia> JDBC_retrieved = readtable(rs)
1x2 DataFrames.DataFrame
| Row | Z1      | Z3      |
|-----|---------|---------|
| 1   | 42893.2 | 249.139 |

julia> JDBC_retrieved[1, :Z1]
42893.18f0
julia> typeof(JDBC_retrieved[1, :Z1])
Float32

julia> JDBC_retrieved[1, :Z3]
249.13866f0
julia> typeof(JDBC_retrieved[1, :Z3])
Float32

Data is being inserted correctly because when you retreive these columns as a String, expected values show up

julia> rs = executeQuery(stmt, "select TO_CHAR(z1), TO_CHAR(z3) from temp")
JavaCall.JavaObject{symbol("java.sql.ResultSet")}(Ptr{Void} @0x0000000010171570)

julia> JDBC_retrieved = readtable(rs)
1x2 DataFrames.DataFrame
| Row | TO_CHAR_Z1_               | TO_CHAR_Z3_                |
|-----|---------------------------|----------------------------|
| 1   | "42893.17904199423910638" | "249.13865675685786780277" |

_Configuration_

This issue was reproduced on following configurations

• OS = Windows 10, 64 bit
  DBMS = Oracle Database 11g Express Edition
  Julia version 0.4.2
  JDBC.jl was upto-date with the master
• OS = CentOS 7 , 64 bit
  DBMS = Oracle Database 11g Express Edition
  Julia version 0.4.1
  JDBC.jl was upto-date with the master

[aviks]

Ah... this need FixedPoint decimal numbers. Wonder if Java uses BigDecimal for these?

[aviks]

This will need one of

• https://github.com/tinybike/Decimals.jl
• https://github.com/stevengj/DecFP.jl

[aviks]

@bmharsha is it possible to replicate this in Java, and see what values come out?

[bmharsha]

is it possible to replicate this in Java, and see what values come out?

@aviks I executed following Java program

Statement stmt = conn.createStatement();
ResultSet rset;
stmt.executeQuery("create table temp13(z1 FLOAT(100), z3 number(38,30))");
stmt.executeQuery("insert into temp13(z1,z3) values(42893.17904199423910638, 249.13865675685786780277)");     
rset = stmt.executeQuery("select * from temp13");
while (rset.next()) 
{
  System.out.println(rset.getBigDecimal(1,17));
  System.out.println(rset.getBigDecimal(2,20));
}

I did got following output (expected)

42893.17904199423910638
249.13865675685786780277

Configuration over which this test was conducted

1. JDBC driver version = 11.2.0.2.0

>java -version
java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b15)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b15, mixed mode)

1. OS = Windows 2008 R2 server, SP1
2. Oracle Database 11g Express Edition, Release 2