File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes data truncation when using JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "data truncation when using JDBC" Watch "data truncation when using JDBC" New topic

data truncation when using JDBC

William Stafford
Ranch Hand

Joined: Dec 13, 2004
Posts: 109
I wrote a Java app to move lat,lon data from an Oracle table to a Postgres table. The app reads an Oracle row and uses the values to build an insert statement that is executed against the Postgres table. Both columns in both tables are defined as numeric.

The java code that replaces the prepared statement placeholder with an actual value is:

When I run the application values are truncated to 12 decimal digits, for example
113.26666666666667 value shown by System.out.println(rs.getDouble(columnIndex));
113.266666666667 value shown by select * from test_moveit_truncation after the move application was run

If I capture the prepared statement with System.out.println(stmt.toString()) I see an untruncated value in the statement
INSERT INTO test_moveit.test_moveit_truncation (lat,lon) VALUES (-7.766666666666667,113.26666666666667)

If I run this statement using the Postgres admin console the values are inserted without truncation.

I can't tell whats going on here, can anyone make sense of these results and figure out where the truncation is happening?

Thanks for any help or advice,

Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

If you don't want to have to deal with floating-point rounding (which is what I think might be happening) then try using BigDecimal instead of double for your Java data type.
William Stafford
Ranch Hand

Joined: Dec 13, 2004
Posts: 109
Thanks very much for the reply. Changing getDouble to getBigDecimal did the trick! I still can't figure out where the rounding was taking place but the problem is solved.

Thanks again,
I agree. Here's the link:
subject: data truncation when using JDBC
It's not a secret anymore!