Decimal data type problem

[elbiggame]

watchinthegame posted the following thread on Mar 14th, 2009 :

MYSQL field is Datatype DECIMAL(9,2)
When I update the table and set the field to 120.00 it gets populated as 1200.00

What am I doing wrong?

Thanks

Watchin
--------------------------------------------------------------------------------------
It doesn't appear this was every resolved (at least not via his post).

I'm having the same issue with mysql 5.0 database table decimal fields. I restored tables from a prior version. Backups and restores worked perfect. All my existing programs (I've been running for several years w/ no problem) are executing the same except decimal digits are moving to the left of decimal place. These programs are written in VB6. The table definitions look identical to old mysql tables. Has anyone run into this problem before?

Big

[whiteyoh]

ive just been looking up what the problem may be and it seems that there were some changes after MySql 5.0.3.

As you have mentioned MySql 5 i think you may be in the realm of the changes. Here is the url to the document

http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

hope this helps

[markchicobaby]

Can you post the sql that you're using to test this?

Have you tried running the sql directly into the db and not using code?

M

[whiteyoh]

it would make no difference if i did as im using php6. There are definite changes to functionality and syntax.

Ive just been reading the following thread:http://www.ozzu.com/programming-forum/help-how-insert-decimal-number-mysql-t76057.html

some of that simply isnt recognised. I dont use decimals ever, i always use float

[markchicobaby]

sorry not reading you. the database understands sql, not php.

so if you show me the sql, i can go from there.

M

[elbiggame]

I double checked the data (a sample of the data) that I restored from previous version of mysql database. Decimal data appears to be correct. I'm using VB6 programs to post new data to mysql 5.0 database tables. These programs have been running in production for eight years, but to older mysql database versions - not to mysql5.0. I'm using MySQL ODBC 5.1 driver in these programs. I believe this is the latest driver. I may have to rewrite these posting programs in php - one option, although a very time consuming task. I tested another option by changing the decimal fields in one table from DECIMAL(15,2) to FLOAT(15,2). The resulting data posted correctly into all fields. I've never used this data type in mysql and am not sure if I will have any problems with precision or accuracy (i.e. I read a few posts where problems with decimal rounding occurred). My current plan is to do more research and then modify about 100 table definitions - changing decimal fields to float fields.

Thanks for the input,
Big

[markchicobaby]

Ah I see. I guess if you're worried about rounding you can always increase the precision of the number (ie increase the number of allowed decimal places). If you're storing financial information it should be equivalent to Oracle NUMBER(16,5).

Interesting they used to store decimals as strings prior to MySQL 5.03. That would explain why you are having issues. If you find changing to float works then go for it, but test it hey ;)

M

[phpbeginners]

try DECIMAL(12,2)

[elbiggame]

I'll try 12,2. I also read something about restoring olding version databases to 5.0+ version databases. The artcle stated that prior version decimal data restored to 5.0 database will still be stored as string data. Resolution is to to do mysqldump on 5.0 table data and then restore table data. I have not tried this yet.