system.db.runPrepUpdate inserts wrong float (decimal sep.)

Ignition
1

I have another problem that seems to be caused by my language setting using ‘.’ instead of ‘,’.
The runPrepUpdate function inserts wrong values for floats. The statement in the following code inserts 25 and 20 instand of 2.5/2.0. The float value 98,99 is inserted as 9.899e+15.

system.db.runPrepUpdate(INSERT INTO table(id, faktor, faktor_anz) VALUES (?,?,?,?,?,?,?,?,?,?), [9998, 2.5, 2.0], , , false)

There seems to be a general problem with number formatting when using system languages that uses a comma as decimal seperator. One of this issues (http://www.inductiveautomation.com/forum/viewtopic.php?f=72&t=8804) has been solved in the last beta, another is still open (http://www.inductiveautomation.com/forum/viewtopic.php?f=72&t=8780).
I know that localisation of Ignition has no high priority, but the number format should be urgently fixed. Currently as a workaround i use a manual float to string conversion for all user input in clients, but that can not be a permanent solution.

2

I was working with a developer and initially we’re having a tough time reproducing this. To get a better idea of how to nail down this issue:

  • What OS are you using?
  • Version of Ignition?
  • Type of database?
  • In what context are you using the script (attached to event handler of component, gateway/client script, etc)?
3

Ok, thats’s interesting. The runPrepUpdate function behaves different in the gateway context.
The script

valDB=1.0
system.db.runPrepUpdate('INSERT INTO test (val) VALUES (?)', [valDB], 'energiemgmt')

inserts ‘10’ to the database when executing in designer/client (button event script) and the correct value ‘1’ when executing in a gateway timer script.

The database is mySQL 5.1, target column type is float. Ignition version 7.5.4-beta4. Java version is 6.34 (Win7) for the client and 6.35 (WinXP) for the gateway. Locale on both systems is ‘de_DE’.

Might this be related to your internal coerce function? The following script shows that a float->string->float conversion does not work and shows exactly the same behaviour:

from com.inductiveautomation.ignition.common.TypeUtilities import coerce
import java.lang
valDB=1.0
strVal=coerce(valDB, java.lang.String)
strVal2=coerce(strVal, java.lang.Float)
print strVal	# prints '1.0'
print strVal2	# prints '10'

valDB=9.0000000001
strVal=coerce(valDB, java.lang.String)
strVal2=coerce(strVal, java.lang.Float)
print strVal	# prints '9.0000000001'
print strVal2	# prints '89999998976.0'
4

Yeah, it may be possible that the coerce function isn’t handling the conversion properly, one of the developers is currently looking at it.

5

Thanks for noticing that it worked differently when the script was run from the Gateway - that was the key to figuring this out. It has been fixed, the fix should be in the first beta of 7.5.5.

6

Correction! This will make it into 7.5.4