SQL Server 2012 JDBC error with int8

I have a simple script in a “Value Changed” event that collects a few tags and inserts them into a database as a single row. All was well when using MySQL however a decision was made to move to SQLServer 2012 and we now have problem. One of the tags is an int8 (its actually a timestamp in ms since 1970 whatever currently in the order of 1,494,220,442,943,758) causes an error in the runPrepUpdate() function. Hard coding the value into the script does not work but hard coding a much smaller number does, it can be a bit bigger than an int4 but not much. If the data has been written to the database with a smaller number I can then overwrite it using SSMS with the correct value so the database is configured correctly to handle large numbers (bigint).

Any suggestions on what might be wrong or how I go about debugging this error would be greatly appreciated. Ignition doesn’t seem to give me a very descriptive error. Just that there is an error executing system.db.runPrepUpdate(query,args,“IgnitionSQLServer”).

Hmmm. I’ve run into this before. It was a side effect of the fact that Jython doesn’t map Java’s Long datatype to a jython 64-bit type, but to jython’s long datatype, which is unlimited bits (Java’s BigInteger under the hood). The JDBC driver I was using was choking on the Java BigInteger even though it fit within the 64-bit column the value was destined for.
I haven’t seen this in some years, though. What Ignition version is this? Have you tried using the very latest version of SQL Server’s JDBC driver?

Have you tried using the very latest version of SQL Server’s JDBC driver?

I’d be willing to bet this is the problem – the jdbc drivers included with ignition are a little dated. Grab a new jdbc driver from microsoft’s download site and let us know if you continue to see the same issue.

Thanks for the replies. Downloaded the latest JDBC drivers (6.0) from Microsoft, seems to have fixed the problem. I’m on Ignition 7.9.1 Only downloaded and installed a couple of months ago.