MySQL, MyODBC and ACCESS 2003

[Dave]

Ok I have an interesting problem. I have written a database in Access
to connect to our web shop and update prices etc from a supplier feed.
It works almost perfectly!

The prices are doubles, I am using ADO in access to connect via myodbc
(with options don't optimize column width, Allow big results and
change bigint to int). Everything works ok except if the price is
8351.41 in the code it puts this exact price into the price field of
the table, everything is fine, but when I update the price goes to
8351.41000000001 (not sure on the exact number of 0 but it is a lot!).

I have tried converting the value to a string then to a double before
writing it to the database, to make sure there are no hidden decimals
but it's the same.

I have tried myodbc 3.51.09 and 3.51.11 and it's the same on both!
In fact if I link the table in access and manually type in 8351.41
then move to a different row it changes it to 8351.41000000001!!

Am I going nuts or is this some kind of annoying bug?

[Bill Turczyn]

How did you define that column type? Did you use float? You can alter
the table to change the precision.

mysql> create temporary table test (price float);
Query OK, 0 rows affected (0.04 sec)

mysql> desc test;
+-------+-------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------+------+-----+---------+-------+
| price | float | YES | | NULL | |
+-------+-------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table test change price price float(10,2);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| price | float(10,2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into test (price) values (8.3566677777);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test;
+-------+
| price |
+-------+
| 8.36 |
+-------+
1 row in set (0.00 sec)

[Bill Karwin]

Dave wrote:

In fact if I link the table in access and manually type in 8351.41
then move to a different row it changes it to 8351.41000000001!!

Am I going nuts or is this some kind of annoying bug?

For what it's worth, this is an inescapable characteristic of
representing floating-point numbers on computers. The inexactness of
IEEE floating point numbers comes from the difference between base-2 and
base-10. In a similar way that 2/3 is a non-repeating number in
base-10, some fractions are non-repeating when represented in binary.

Then because the computer has finite space to store them, you get
inexactness problems like you see above. Similarly, 2/3 could be stored
as 0.6666667 in base-10, but that's not exactly the same value as 2/3.

Software that needs to store floating-point values with more exactness
need to store them differently. Basically it needs to store 8351.41 as
the integer 835141, plus the value 2 as the "scale," or the number of
digits to the right of the decimal. MySQL seems to do this for you,
when you declare a scale value for the FLOAT datatype, as Bill Turczyn
described in his message.

Regards,
Bill K.

[Dave]

the price field is a double with 0,0. the wierd thing is if i set it
to a 2 decimal place i do not get the symptom mentioned above, but i
get the same problem, access will not update the record again untill i
delete the information in the price field, but if i use some mysql
admin software to change the field i can change it no problem. The
error access gives is someone else has altered the record since it was
written, which is obviously not the case as the timestamp has not
changed.

Any thoughts?

[Dave]

Also, all the information i see on the mysql site is to only use
doubles, not floats if you are using in conjunction with access (that
is why i have it as double) originally it was float, by the way, i
have the same problem when it is set to float, but for ALL records!

[Thomas Bartkus]

"Dave" <tr******@centatek.com> wrote in message
news:c8**************************@posting.google.c om...

Ok I have an interesting problem. I have written a database in Access
to connect to our web shop and update prices etc from a supplier feed.
It works almost perfectly!

The prices are doubles, I am using ADO in access to connect via myodbc
(with options don't optimize column width, Allow big results and
change bigint to int). Everything works ok except if the price is
8351.41 in the code it puts this exact price into the price field of
the table, everything is fine, but when I update the price goes to
8351.41000000001 (not sure on the exact number of 0 but it is a lot!).

I have tried converting the value to a string then to a double before
writing it to the database, to make sure there are no hidden decimals
but it's the same.

I have tried myodbc 3.51.09 and 3.51.11 and it's the same on both!
In fact if I link the table in access and manually type in 8351.41
then move to a different row it changes it to 8351.41000000001!!

Am I going nuts or is this some kind of annoying bug?

It *drive* you nuts, but it's not a bug. More like a design limitation of
modern computers. The ODBC driver and ADO are not to blame.

Contrary to popular opinion, the float type is not the most appropriate type
for dealing with dollars and cents. By their very nature, floats are only
approximations gauranteed to leave penny balancing accountants dissatisfied.
Bill Karwin explained the reasons quite well.

The best fix would be to use the DECIMAL type instead of FLOAT.

A *second* best solution, if you are *stuck* with FLOAT, is to use of the
ROUND function both when SELECTing and INSERTing dollar values. If you
specify 4 decimal places, the floating point precision of MySQL will
probably suffice to keep your pennies in line and irrelevant decimal places
suppressed. But there are no gaurantees you won't lose a penny here and
there if you do lots of arithmentic.

DECIMAL type is how you should store $ and cents.
Thomas Bartkus

[Dave]

Hi Guys, thanks for the info. I have made the change to decimal 10,2
and it seems to have worked. Just have to run through the update once
more to double check that access can still update the record.

The table is part of a web shop that we have purchased, the default
for the price fields was float, and I just presumed that they needed
to be a float for the shop to work but apparently not.

Once again thanks for the help.