mysql :: I loose decimal places when i insert.

[jite eghagha]

When i execute this sql:

rowCount = stmt.executeUpdate("INSERT INTO markerLocations VALUES (null, 12.333333, 12.444444, 'john')");  

my table only stores 3 decimal places :: 12.333, 12.444:

my table was created like so

create table markerLocations ( 
markerId INT NOT NULL AUTO_INCREMENT, 
lat DOUBLE(17,15), 
lng DOUBLE(17,15),
 city VARCHAR(255), 
PRIMARY KEY (markerId) )  

I'd like to know why this happens, and how to fix it.

[ujjwal soni]

Hi,

Try using a column of type FLOAT rather than DOUBLE.

Recreate your table like the one specified as under ::::

create table markerLocations (    
markerId INT NOT NULL AUTO_INCREMENT,    
lat FLOAT(17,15),    
lng FLOAT(17,15),   
 city VARCHAR(255),    
PRIMARY KEY (markerId) )    

[jite eghagha]

I get the same result, float or double.

lat:lng
4.7064595222473145:7.076568603515625

becomes

lat:lng
4.705:7.076

[ujjwal soni]

MySQL docs mention "MySQL performs rounding when storing values" and I suspect this is the issue here. I duplicated your issue but changed the storage type to be FLOAT:

CREATE TABLE test (val, FLOAT);and the retrieved value did not matched the test value you provided.

My suggestion, for what it's worth, is use DECIMAL. I tried the same original test with:

CREATE TABLE test (val, DECIMAL(17,15));and it retrieved the value I gave it: 4.7064595222473145

Also, can you tell the version of mysql you are using ?

[jite eghagha]

thank you, decimal works fine.

[ujjwal soni]

Cool