MysqlDataTruncation

[Aravind Prasad]

Dear Ranchers,
I'm working in JSP and MySQL. the table details of the mysql table is this

+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| id            | varchar(10)   | NO   | PRI | NULL    |       |
| file_id       | varchar(10)   | NO   | PRI | NULL    |       |
| accounts_id   | varchar(10)   | NO   | PRI | NULL    |       |
| charges_code  | varchar(100)  | NO   |     | NULL    |       |
| currency      | varchar(10)   | NO   |     | NULL    |       |
| amount        | decimal(10,2) | NO   |     | 0.00    |       |
| exchange_rate | decimal(10,2) | NO   |     | 0.00    |       |
| usd_amt       | decimal(10,2) | NO   |     | 0.00    |       |
+---------------+---------------+------+-----+---------+-------+
8 rows in set (0.02 sec)

When i try to insert the values thru the jsp page.. it is showing error..
the query running behind this is

Insert into financials (id,file_id,accounts_id,charges_code,currency,amount,exchange_rate,usd_amt) values ('00001','1','5','DOMC','USH',1855,1800,1.0305555555555554)

The error i'm getting is this

[B]
error is here com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data truncated for column 'usd_amt' at row 1 
[/B]

What may be the error.. Please help me ranchers!!

Thanks in advance..

regards
Aravind Prasad

[Paul Sturrock]

Truncation means shortening. The data type for the field usd_amt is decimal(10,2) which means you can store a floating point number with a maximum of ten digits, of which two can be right of the decimal point. The value you are trying to insert is thei: 1.0305555555555554, so MySQL would have to truncate the number to 1.03 in order to insert it, and that is why you see this exception.

[Aravind Prasad]

Thankyou Paul Sturrock,
When i tried to insert the query using JSP front end is was showing the error, but when i tried it out using the mysql prompt it was working perfectly and was inserting it like x.yy format. So the problem is with the JSP coding!!
Also when i searched in Google using the error, i got the results like, to change the jdbc connecter.?
is that possible..


Can you please fix for me this problem.

regards
Aravind Prasad

[Rama Krishna Ghanta]

Hi Aravind,

I dont understand how a connector helps you fix this exception. How does a connector help in changing the basic properties of a DATATYPE? The only ways you can fix this is, either change the data type to decimal(10,2) to decimal(somenumber,somebignumber) or Round the decimal value to two digits if your requirement permits.

Thanks,
Rama Krishna Ghanta