Named Query Inserting Inaccurate Decimal Value into DB

Ignition
,
1

I found something interesting today that I thought I'd share. I don't know if it's a bug or something I'm doing wrong/missing.

I was trying to insert a value from a perspective (8.1.18) numeric entry field into a database (MSSQL server 2019), with data type of decimal(14, 4). When I created a Named Query and sent the value to the query as a parameter (float4) I would get inaccurate decimal value in database. For example, if I entered 123.2 into the numeric entry field and called the named query, using system.db.runNamedQuery, to insert the value into the database. The value in the DB would be 123.2002 or maybe even 123.1998.

I tried running the same query using runPrepUpdate in the script and this saves an accurate decimal value of 123.2000 in the database.

It's a simple insert query
system.db.runPrepUpdate("INSERT INTO Table (vId, quantity) VALUES (?,?)", [myId,MyQty])

Am I missing something? I'd prefer to use named queries so I can call it from multiple scripts.

1 Like
2

That's the problem. You are asking for seven significant digits from a format that can't do that. Use float8. It works in the prep query because jython (2.7) uses double for float values.

1 Like
3

Thank you!