Using floats in MySQL

[Nibinaear]

I'm having trouble adding floats to my database:


CREATE TABLE jobs
(
job_id int(11) auto_increment not null,
job_title varchar(100) not null,
job_details text not null,
job_hourly_rate double(2,2) null,
job_yearly_rate int(8) null,
job_hours int(3) null,
job_duration enum('permanent','temporary') not null,
job_date_added datetime not null,
job_closing_date datetime null,
job_of_the_week boolean null,
emp_name varchar(100) not null,
emp_address varchar(100) null,
emp_address2 varchar(100) null,
emp_city varchar(100) null,
emp_county varchar(100) null,
emp_postcode varchar(10) null,
emp_country varchar(100) null,
emp_phone varchar(18) null,
emp_mobile varchar(18) null,
emp_fax varchar(18) null,
emp_email_contact varchar(100) null,
PRIMARY KEY(job_id)
);



insert into jobs(job_title,job_details,job_hourly_rate,job_yearly_rate,job_hours,job_duration,job_date_added,job_closing_date,job_of_the_week,emp_name,emp_address,emp_address2,emp_city,emp_county,emp_postcode,emp_country,emp_phone,emp_mobile,emp_fax,emp_email_contact)
values("web dev","the hello world",2.22,130000,100,'temp','10-10-2006','10-10-2006',0,"performance house","aaaaaaaaaaaaaaaaa the street","bbbbbbbbbbbbb street","cty","county","p3jd3p4","eng",0000000000,3333333333333,000000000000,"a@gggggggg.com");


All fields are correct except the double field. I tried it as float and I got the same result. Originally I was getting the error:


"Out of range value adjusted for column"


But I'm using 2.22 in a field that has a length of 00.00 which should be fine shouldn't it? I looked up the above bug and was informed that the my.ini file's sql-mode should be changed out of strict to the following value.


sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"


I did this and the error went away, the data added, but the data was wrong (value of 0.99 rather than 2.22.) I've change to double as as I read elsewhere but I still get the wrong data. I don't think my sql is wrong.

P.s.
- I filed a report on this page in case this bug is affecting things:
http://bugs.mysql.com/bug.php?id=3356&thanks=3&notify=7

- This bug is also relevant regarding the out of range problem:
http://bugs.mysql.com/bug.php?id=11546

[aniskhan]

MySQL allows a non-standard syntax: FLOAT(M,D) or REAL(M,D) or DOUBLE PRECISION(M,D). Here, “(M,D)” means than values are displayed with up to M digits in total, of which D digits may be after the decimal point.

For example, a column defined as FLOAT(7,4) will look like -999.9999 when displayed. MySQL performs rounding when storing values, so if you insert 999.00009 into a FLOAT(7,4) column, the approximate result is 999.0001.

[Nibinaear]

So when I get the data back it will be in the correct format, but doesn't appear to be in the db, is that what you're saying?