We want to write out floats with 6 decimal places to a FLOAT field in a MySQL database using the system.db.runPrepUpdate function.
We have found that if the value is small e.g. 0.000005 the system will try to send the number in exponential format i.e. 5.0E-6. However, there must be a problem occurring when the SQL query is constructed, because the value is stored in MySQL as 5.
We have tried writing exponential format numbers to MySQL using UPDATE SQL queries and they work perfectly. It would therefore seem that the problem is occurring somewhere between Ignition and MySQL.
We have found we can work around this problem by changing the number to a string, but this would be impractical to implement across our whole system.
Just tested this myself, and I can replicate this issue. Occurs on both double and float columns in MySQL. Good find Al.
system.db.runPrepUpdate("INSERT INTO test_table (floatval,doubleval) VALUES (?,?)",[float(0.000005),float(0.000005)])
Hi,
This was caused by a problem with parsing scientific notation, introduced in 7.1.8 and fixed for 7.1.9. So, if you go grab the dev version of that, you shouldn’t run into this. We’re likely going to release 7.1.9 for real tonight or tomorrow.
On a side note, the query browser in the designer doesn’t do a good job of showing these small numbers, so make sure to verify through a different means. It just shows 0. We might be able to fix that for the final release.
Regards,
Hi Colby,
Our server is running on Ubuntu 10.04 server and is running Ignition 7.1.7 (b6048). We’ll try upgrading to 7.1.9 and see how it goes.