Mismatch Datetime When Inserting To MS SQL

Ignition
1

Ignition v8.1.39
MS SQL Server Express v16.0.1000.6

I've been doing some testing using SQL Express and I am seeing a small discrepancy and I'm not sure if it is caused by something I am doing.

Our project requires me to query Ignition Historian for values and merge them into another database table.

In my table. there are three columns I use to determine update/insert in the merge query.
Columns equipmentId [int], eventId [int], eventBegin [datetime2(3)].

The merge will update two other columns eventEnd [datetime2(3)], eventDuration [float] if a match is found in the three columns above.

I am using system.tag.queryHistory to get the data for the merge. The data is being queried using an RTP whose gateway is in a different timezone. In production, the query will be querying the local Historian, not an RTP.
I get the tstamp from historian as java.util.Date and convert it to a java.sql.Timestamp.

I do this for a mass number of values, assemble them, and send to a named query as a query string to do the merge.
The mismatch I am seeing is in the milliseconds. Any tstamp that has a leading zero in it's ms, the zero is removed and placed at the end.
Any other combination it seems to be fine.
For example:

Values to Insert
[8, 2, 2024-11-18 04:41:02.039]
[8, 5, 2024-11-18 08:32:12.095]
[8, 2, 2024-11-18 08:39:17.019]

Values In Table
equipId | eventCode | eventBegin
8       | 2         | 2024-11-18 04:41:02.390
8       | 5         | 2024-11-18 08:32:12.950
8       | 2         | 2024-11-18 08:39:17.190

I thought maybe it was because of the column type. I changed the column from datetime to datetime2(3) last week because the rounding was causing inserts that should have been updates. I did a query to see if there were any rows that had a leading zero. Out of 513000 rows, less than 1% of the rows do. However, they are all .000.
In the DB Query Browser, I can write an update query and set it with a leading 0 just fine.

1 Like
2

Hard to tell without your actual queries to look at, but this is a concern:

{My Bold}

There's almost certainly no excuse for query string NQ parameters.

3

There could be up to 1000 values to insert/update is why I used the Query String. I wanted to do it in one call instead of n separate queries.
I tried using the prepUpdate query but I could not figure out how to get it to not complain about the datetime formatting.

IF OBJECT_ID(N'tempdb..#temp_eventLog', 'U') IS NOT NULL
    DROP TABLE #temp_eventLog;
		
CREATE TABLE #temp_eventLog (
    equipment_id INT,
    event_id INT,
    event_Begin datetime2(3),
    event_End datetime2(3),
    event_Duration float
);

INSERT INTO #temp_eventLog (equipment_id, event_id, event_Begin, event_End, event_Duration) VALUES

{insertValues} 


MERGE INTO eventLog AS target
	USING #temp_eventLog AS source
	ON (target.event_Begin = source.event_Begin AND target.equipment_id = source.equipment_id AND target.event_id = source.event_id)

WHEN MATCHED THEN
	UPDATE SET
	target.event_End = source.event_End,
	target.event_Duration = source.event_Duration

WHEN NOT MATCHED BY TARGET THEN
    INSERT (equipment_id, event_id, event_Begin, event_End, event_Duration)
    VALUES (source.equipment_id, source.event_id, source.event_Begin, source.event_End, source.event_Duration);
4

Use runPrepUpdate, and assemble a query string with the right number of literal ? placeholders in the outgoing query for the number of values you expect to send. Note that MSSQL has a hardcoded, arbitrary limit on the number of parameters you can send via JDBC; I want to remember it as somewhere between 1-2 thousand.

If you're doing anything that requires formatting a datetime to a string, you've already lost.
runPrepUpdate sends a JDBC PreparedStatement, which slots in native Java objects as query parameters; thus you can losslessly send a java.util.Date in and the JDBC driver will handle marshalling that Java type into the appropriate binary format to send into your DB.

1 Like
5

MSSQL limit is 2100 parameters via JDBC. I have the value hardcoded for a lot of my data handling scripts to chunk when over that amount.

1 Like
6

For what its worth you can merge without creating a temp table each time by using

MERGE INTO TargetTable AS TARG
USING(VALUES(?,?,?,?,?), (?,?,?,?,?)) 
AS SOURCE(Column1, Column2, etc)
ON TARG.Column1 = SOURCE.Column1
7

Thank you! I’ll give runPrepUpdate another go and see what happens.
Paying close attention to keeping the datetimes as java.util.Date.
I already check the date time args that are passed into my function to see if they are str instances, and if so I use system.date.parse to get it into a Java date.

I have the insert limit set to 1000. I got an error once saying I couldn’t insert more than 1000 at a time. So I just keep the most recent 1000 and discard the rest.
Under normal circumstances, it is only inserting/updating ~10 rows.

I used the temp table just to shorten the query so I didn’t have to explicitly define all of the columns. Just merge table into table.

1 Like