Granted, this is a bug in the particular JDBC Driver I am using. The 10.2.0.2 driver straight out of the box has bug 4711863 that can possibly insert bad data when you use Java 1.5. In fact, I proved it with a scenario similar to:
drop table tablea
/
drop table tableb
/
create table tablea (
record_id number(38) primary key,
price number)
/
create table tableb (
record_id number(38),
price number)
/
-- fill it
begin
for i in 1..100 loop
insert into tablea values (i, 0+(i*.0000001));
commit;
end loop;
end;
/
quit
Then, my java program sucks data from tableA and inserts it into tableB. Inside my java program, I convert a Double to BigDecimal using code like:
String insertString = "insert into tableB values (?, ?)";
PreparedStatement stmt2 = this.dbConnection.prepareCall(insertString);
stmt2.setInt(1,rs.getInt("RECORD_ID"));
stmt2.setBigDecimal(2, new BigDecimal(rs.getDouble("PRICE")));
I query the data from tableB and boy am I surprised:
SQL> select * from tableb where price <> price*1;
RECORD_ID PRICE
-------------------- --------------------
2 .00000019RS
3 .00000029RS
4 .00000039RS
5 .00000049RS
6 .00000059RS
7 .00000069RS
8 .00000079RS
9 .00000089RS
10 .00000099RS
So maybe it's a sqlplus setting or something. I try:
SQL> set numwidth 20
SQL> /
RECORD_ID PRICE
-------------------- --------------------
2 .00000019RS
3 .00000029RS
4 .00000039RS
5 .00000049RS
6 .00000059RS
7 .00000069RS
8 .00000079RS
9 .00000089RS
10 .00000099RS
Same deal.
I go back and verify my source data from tableA, and sure enough it's correct:
SQL> select * from tablea where record_id = 2;
RECORD_ID PRICE
--------------- ---------------
2 .0000002
SQL> l
1* select record_id, price, dump(price) dmp from tableb where price <> price * 1
SQL> /
RECORD_ID PRICE DMP
--------- -------------------- ----------------------------------------
2 .00000019RS Typ=2 Len=21: 189,20,246,100,100,100,100
,100,100,100,10,50,63,24,66,18,73,52,74,
72,23
3 .00000029RS Typ=2 Len=21: 189,30,246,100,100,100,100
,100,100,99,65,25,44,36,48,77,59,78,61,5
7,85
4 .00000039RS Typ=2 Len=21: 189,40,246,100,100,100,100
,100,100,99,19,100,25,48,31,36,46,4,48,4
3,46
5 .00000049RS Typ=2 Len=21: 189,50,246,100,100,100,100
,100,100,98,74,75,6,60,13,95,32,30,35,29
,7
6 .00000059RS Typ=2 Len=21: 189,60,246,100,100,100,100
,100,100,98,29,49,87,71,96,54,18,56,22,1
4,69
7 .00000069RS Typ=2 Len=21: 189,70,246,100,100,100,100
,100,100,97,84,24,68,83,79,13,4,82,8,100
,30
8 .00000079RS Typ=2 Len=21: 189,80,246,100,100,100,100
,100,100,97,38,99,49,95,61,71,91,7,95,85
,92
9 .00000089RS Typ=2 Len=21: 189,90,246,100,100,100,100
,100,100,96,93,74,31,7,44,30,77,33,82,71
,53
10 .00000099RS Typ=2 Len=21: 189,100,246,100,100,100,10
0,100,100,96,48,49,12,19,26,89,63,59,69,
57,14
OK, so JDBC isn't 100% correct when you cast between BigDecimal and Double. Big whoop. And in fact, the fix for the aforementioned bug is fixed if you apply the appropriate patch.
My problem is in the fact that Oracle (the database software) let a bad piece of data in. Oracle says the client should type the data when binding. I agree. But I also think that the server shouldn't let non-numeric data into numeric columns, regardless. I contend that this is a bug on the server side as well as a bug on the client side. This is only one specific case of a Java program inserting bad data. I have some tables that have non-numeric characters that were never touched by Java.
4 comments:
"But I also think that the server shouldn't let non-numeric data into numeric columns, regardless. I contend that this is a bug on the server side as well as a bug on the client side. "
OMG! You are kidding, right?
Please, tell me you are joking!
Mapping data through various places is inherently arbitrary. If the db engine were the place to say "no soup for you, weakly typed fiend!" people would undoubtedly find reason to complain about that. Oracle is bad enough with characterset conversions. I think it is a good thing that it lets programmers do bad things, puts the onus where it belongs.
Think on this.
And how should Oracle handle binary_double conversion of .01? Say "sorry, can't represent that perfectly?"
I cannot agree with the lose typing, Joel.
I don't know about you, but I see a slight difference between the subtleties of the representation of binary 0.01 and slapping a few random characters into what otherwise would be a number only column!
Next we are gonna relax it all and create only one type of column in a database to contain anything, just because someone thinks it's all too hard?
Heck, what is the point of doing accounting then, let's just call it all in the red and proceed in our happy way to idiocracy?
Please!
The problem Jeff has shown has to be one of the most loud clangers in a ling list of absolutely inexcusable bugs since 10g came out. Even SQL Server won't allow anyone to do this!
Joel,
No disrespect intended, but I absolutely disagree. First off, if Oracle isn't going to type check the data, you just have a 2TB Excel Spreadsheet.
Second, Oracle does type check the data. That's why you can't insert "ABC" into a number field and why you can't insert "NOW IS THE TIME FOR ALL GOOD MEN TO COME TO THE AID OF THEIR COUNTRY" into a date field.
Post a Comment