JDBC returning values using Scientific Notation

[Dar Var]

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]

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]

I am using DbVisualizer and it is nor doing any conversion

[Dar Var]

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]

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]

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]

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]

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:

public class FloatTest {
 
    public static void main(String [] args) {
float test = 126508500000000000.59061000000000000f;
System.out.println(test);
    }
 
}

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

[Dar Var]

Here is my code:

         // query database
         ResultSet resultSet = 
            statement.executeQuery( "SELECT sum(floatvalues) FROM scentific_notation" );
         
         // process query results
         StringBuffer results = new StringBuffer();
         ResultSetMetaData metaData = resultSet.getMetaData();
         int numberOfColumns = metaData.getColumnCount();
         
         for ( int i = 1; i <= numberOfColumns; i++ ) {
            results.append( metaData.getColumnName( i )
               + "\t" );
         }
         
         results.append( "\n" );
         
         while ( resultSet.next() ) {
            
            for ( int i = 1; i <= numberOfColumns; i++ ) {
               results.append( resultSet.getObject( i ) 
                  + "\t" );
            }
            
            results.append( "\n" );
         }    

The data is stored as a float in the db

[Paul Sturrock]

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:

StringBuffer result = new StringBuffer();
Float numberFromDatabase = new Float(126508500000000000.59061000000000000f);
result.append(numberFromDatabase);
 
System.out.println(result);

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]

Hi,
Did you tried using java.math.BigDecimal class?

[Paul Sturrock]

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]

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]

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 ]