#1366 - Incorrect decimal value mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

#1366 - Incorrect decimal value mysql

Post by greg7 »

Hi, i use the following query to create a table

Code: Select all

 CREATE TABLE inouts (dmid int not null AUTO_INCREMENT, proj_id smallint(8) unsigned default NULL, name VARCHAR(35),a DECIMAL(6,3),b DECIMAL(6,3),c DECIMAL(6,3),d DECIMAL(6,3),PRIMARY KEY (`dmid`,`proj_id`), FOREIGN KEY (proj_id) REFERENCES projects (proj_id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB
 
when i try to isert some values in it

Code: Select all

 INSERT INTO inouts (`dmid`,`name`,`a`,`b`,`c`,`d`) VALUES ('1' ,'name' ,'1,5' ,'0,2' ,'1,4' ,'0,35');
 
I get the #1366 - Incorrect decimal value, does anyone knows what am i doing wrong?
I've tried some tricks, like declaring the decimals as varchar at the create statement, and then updating the fields as decimals, but then i get

Code: Select all

('name' ,'1,00' , '0,00' , '1,00' , '0,00');  
I've read some threads around but i don't get it, any help would be appreciated!
Thanks in advance!
Last edited by greg7 on Fri Oct 23, 2009 5:18 am, edited 1 time in total.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: #1366 - Incorrect decimal value mysql

Post by AbraCadaver »

As far as I know, you must use a decimal . (dot, period, full stop) as the decimal separator.

INSERT INTO inouts (`project_id`,`DMUS`,`INPUT1`,`INPUT2`,`OUTPUT1`,`OUTPUT2`) VALUES ('1' ,'Brighton' ,'1.5' ,'0.2' ,'1.4' ,'0.35')

-Shawn
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
greg7
Forum Commoner
Posts: 32
Joined: Tue Oct 13, 2009 7:38 am

Re: #1366 - Incorrect decimal value mysql

Post by greg7 »

Thanks for the reply, maybe i forgot to mention before that the data is uploaded from the users with csv files. The queries before is not standard, i generate them dynamically. All i can do is to mention to them that file's fields must have the decimal format with "." separator where is required. My problem stems from my csv parser, i can't do many things with it since it imports to table row by row. If i wish all fields to be decimals i could use floatval() at every row before i insert to a table,
but i can't cause i need the second column to be varchar. Using excel files there is no problem for me because i can access every cell and not the whole row. So i need a tricky way to do it, because even the update not helped.

Thanks anyway!
--------------------------------------------
ΟΚ i solve it, i'm definitely stupid, not optimal solution, but for now does the job. Firstly i insert the data as varchar to a temp table from csv file, then i retrieve them and using floatval() i import them into a new table.
Post Reply