we have Ignition 7.9.16 running on Windows Server 2019 with OpenJDK. Now, after enjoying several hours of bug hunting, I came to interesting conclusion.
Let’s say I want to populate 2 variables:
One variable get’s populated by SQL query where the SQL query returns record from MS SQL database having datetime datatype on the MS SQL server.
Second variable gets built with system.date.getDate(…) and system.date.setTime(…) in the script. In Script Console in designer, both varialbes have <type ‘java.util.Date’> data type. Running the same script on gateway, the variables suddenly become <type ‘java.sql.Timestamp’>and <type ‘java.util.Date’> … and these two cannot be compared by .equals() (did not work for me), but can only be compared by isAfter() and isBefore() methods (that worked for me).
That has costed me several hours of time. What did I do wrong?
java.sql.Timestamp is the raw type delivered by JDBC drivers. It is a subclass of java.util.Date but with more precision (nanoseconds versus milliseconds). Serialization to go through the network (gateway => designer, or gateway => Vision client) can squash the precision since Ignition tends to only work with milliseconds.
Look at your column definition in MS SQL Server. If you specified more than 3 digits of precision you can get into the equality problem. IIRC, the default is 6 digits.
thanks for almost immediate reply and the explanation. The confusion is smaller now, but still … why would the software use one class in script console and the other one while running as a gateway script?
Pardon my ignorance here … SQL server’s [datetime] data type does not have precision, does it? Data type [datetime2] has but that one I am not using.
You're right, it doesn't. In fact, its precision is some strange fraction of seconds.
The gateway generally takes whatever JDBC gives it to avoid reprocessing. Passing that result over the network requires encoding and decoding, which can be lossy.
In general, do not use the script console for anything but the simplest tests for gateway code, or to trigger messages. Create a temporary message handler that will call your gateway code in the gateway when your script console requests it. The script console is closest to a Vision Client's environment, but even that isn't perfect.
Accuracy: Rounded to increments of .000, .003, or .007 seconds
User-defined fractional second precision: No
There's also a table in that link that gives examples of rounding in datetime. Note that MS also says:
Use the time , date , datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time , datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.
Datetime2 has precision down to 100ns, and you do have user defined precision.