How do you coerce float values in MySQL for classic ASP scripts?

Question

I have been charged with maintaining a legacy classic ASP application. The application uses an ODBC system DSN to connect to a MySQL database.

We had to recently update the servers to satisfy some licencing requirements. We were on Windows, with MySQL 4.x and the 3.51 ODBC driver. We moved to a Linux machine running MySQL 5.1.43 and are running the 5.1.6 ODBC driver on the new IIS server.

Users almost instantly started reporting errors as such:

Row cannot be located for updating. Some values may have been changed since it was last read.

This is a ghost error, and the same data changes, on the same record, at a different time won't always produce the error. It is also intermittent between different records, as in, sometimes, no matter what values I plug in, I haven't been able to repro the defect on all records.

It is happening across 70 of about 120 scripts, many over 1,000 lines long.

The only consistency I can find is that on all of the scripts that fail, they are all reading/writing floats to the DB. Fields that have a null value don't seem to crash, but if there is a value like '19' in the database (note the no decimal places) that seems to fail, whereas, '19.00' does not. Most floats are defined as 11,2.

The scripts are using ADODB and recordsets. Updates are done with the following pattern:

• select * from table where ID = udpdated recordID
• update properties of the record from the form
• call RecordSet.Update and RecordSet.Close

The error is generated from the RecordSet.Update command.

I have created a workaround, where rather than select/copy/update I generate an SQL statement that I execute. This works flawlessly (obviously, an UPDATE statement with a where clause is more focused and doesn't consider fields not updated), so I have a pretty good feeling that it is a rounding issue with the floats that is causing a mis-match with the re-retrieval of the record on the update call.

I really would prefer NOT re-writing 100's of these instances (a grep across the source directly finds 280+ update calls).

Can anyone confirm that the issue here is related to floats/rounding?

And if so, is there a global fix I can apply?

Thanks in advance, -jc

Answer 1

Have a look at MySQL Forums :: ODBC :: Row cannot be located for updating.
They seem to have found some workaround and some explanations as well..

Answer 2

I ran into a similar issue with a VBA macro utilizing 4.1. When upgraded to 5 errors started popping up.

For me the issue was that values being returned to VBA from MySQL was in a unhandled (by VBA) decimal format.

A CAST on the numbers when querying helped to fix the issue.

So for your issue perhaps the ODBC/ASP combination is recording/reading values differently then what you might expect them to be.