aspose file tools*
The moose likes JDBC and the fly likes JDBC returning values using Scientific Notation Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC returning values using Scientific Notation" Watch "JDBC returning values using Scientific Notation" New topic
Author

JDBC returning values using Scientific Notation

Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
I am selecting from the database a value using

select
sum(myTable.myColumn) col1
from myTable

The value coming back using dbaccess is: 113297618.3795
The value coming back using JBDC is: 1.1329761837946805E8

Notice the JDBC format using Expontenials e.g. E8

How do get rid of this Scientific Notation
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
JDBC provides database connectivity it doesn't reformat numbers. The program is taking the value from a JDBC recordset and printing it in scientific notation format.

You can use java.text.NumberFormat to print the value in a different format.
[ September 15, 2006: Message edited by: Scott Johnson ]
Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
I am using DbVisualizer and it is nor doing any conversion
Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
Here is my problem anyway:

1. I created a table in my Informix database:
CREATE TABLE scentific_notation(floatvalues float)

2. I inserted these values:
insert into scentific_notation values(2835121.634679258)
insert into scentific_notation values(978696.1697495114)
insert into scentific_notation values(2307377.07214382)
insert into scentific_notation values(2336955.5022556856)
insert into scentific_notation values(1888293.6396578003)
insert into scentific_notation values(2304406.572121913)

3. I do a sum of the values
SELECT sum(floatvalues)FROM scentific_notation


Result from JDBC:
1.265085059060799E7

Result from dbaccess:
12650850.59061
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

JDBC just returns a number. And numbers don't have any inherent format in Java. If you want to display it in some particular format (scientific or otherwise) then use a NumberFormat object to do that.

Also, if you don't like the way that this DbVisualizer thing is formatting your numbers, then your complaint is about DbVisualizer and not about JDBC. JDBC just returns a number, it doesn't format the number.
Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
Well I decided to create my own JDBC class and this brings it back with the Scentific notation also so its not DbVisualizer.

Could it possibly be the Informix JDBC driver?

Does a driver do any formatting of large values
Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
Further investigation shows that this is not Informix or the informix driver either this behaviour is definitely without doubt JDBC. This is the same for mySQL db.

I added the following large value to a column using Database Type float 126508500000000000.59061000000000000

Coming back using JDBC is:
1.26508500004225248E17

I'm am doing no formatting here.

[ September 18, 2006: Message edited by: Dar Var ]
[ September 18, 2006: Message edited by: Dar Var ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Further investigation shows that this is not Informix or the informix driver either this behaviour is definitely without doubt JDBC. This is the same for mySQL db.

I beg to differ, JDBC plays no part here. Re-read Paul Clapham's comment and try running this program:


How is the floating point number rendered?
[ September 18, 2006: Message edited by: Paul Sturrock ]

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
Here is my code:



The data is stored as a float in the db
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

So the getObject() method of ResultSet is probably returning a Float or a Double (you could easily test this in you code if you want to find out). And you are adding this value to a StringBuffer which you then output to the console, without imposing any formatting (i.e. just using the default formatting provided by the JRE).

So, update my test class to do this:


And see what format the number takes. Then re-read Paul Clapham's post (paying particular attention to the line "...numbers don't have any inherent format in Java") and read the JavaDocs for the NumberFormat class.
Sivaraman Lakshmanan
Ranch Hand

Joined: Aug 02, 2003
Posts: 231
Hi,
Did you tried using java.math.BigDecimal class?


Regards,
Sivaraman.L
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Sivaraman Lakshmanan:
Hi,
Did you tried using java.math.BigDecimal class?


Changing the type is not addressing the underlying problem; that numbers in Java have no inherent format. If you want a number to be displayed in a particular format, you are going to have to define that format.
Dar Var
Ranch Hand

Joined: Oct 12, 2004
Posts: 74
Okay I'll to format each value coming back. Pitty it doesn't keep the existing format in the db.

I hope formating a large number of values from the database is performant.
[ September 20, 2006: Message edited by: Dar Var ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Pitty it doesn't keep the existing format in the db.

Databases, like Java don't hold any information about the format of a number. Why should they? In mathematics the format is irrelevant.


I hope formating a large number of values from the database is performant.

I would be amazed if this had any noticable effect on the performance of your application. But then you are in the position to see - test it and find out.
[ September 20, 2006: Message edited by: Paul Sturrock ]
 
Don't get me started about those stupid light bulbs.
 
subject: JDBC returning values using Scientific Notation