Export from Forms Admin doesn't produce a zip on SQL Server

[avernet]

It looks like a time zone issue to me, but I haven't quite gotten to the bottom of it. A few data points:

• Through JDBC we use java.sql.Timestamp.
• In other related places we use java.time.Instant, which is a UTC time.
• We convert the Instant to a Timestamp with Timestamp.from(...).
• The top answers to this Stack Overflow question have some interesting tidbits.

+1 from customer

[avernet]

It isn't a time zone issue, but the problem with timestamp precision. I am reproducing this with standalone code:

val stmt1 = conn.createStatement();
val stmt2 = conn.prepareStatement("SELECT count(*) FROM orbeon_form_data WHERE last_modified_time = ?");
val rs1 = stmt1.executeQuery("SELECT last_modified_time FROM orbeon_form_data")
while (rs1.next()) {
    val timestamp = rs1.getTimestamp(1)
    stmt2.setTimestamp(1, timestamp)
    val rs2 = stmt2.executeQuery();
    rs2.next();
    println(s"Count: ${rs2.getInt(1)}, timestampSource: ${timestamp}")
}

With the data I have right now in SQL Server, this produces the following output:

Count: 1, timestampSource: 2024-04-27 21:52:50.78
Count: 1, timestampSource: 2024-04-28 13:54:54.63
Count: 0, timestampSource: 2024-04-28 15:58:53.273
Count: 1, timestampSource: 2024-04-29 10:59:08.4
Count: 0, timestampSource: 2024-04-29 10:59:36.517
Count: 1, timestampSource: 2024-04-29 11:03:50.41
Count: 1, timestampSource: 2024-04-29 11:05:32.51
Count: 1, timestampSource: 2024-04-29 11:06:33.01

From microsoft/mssql-jdbc#1843 (comment):

Comparing a java Timestamp to a datetime column on sql server will not have accurate results.

From microsoft/mssql-jdbc#680 (comment):

Due to the data handling accuracy "improvements" in 2016, datetime values are made more "accurate" when converting to datetime2 since datetime is actually granular to 1/300 of a second.

From: microsoft/mssql-jdbc#1843 (comment):

When you read value A the server will give the driver a time value which for example ends in .003 seconds. When you query the database against this same column we then pass back that value which ends in .003 seconds, however when the server makes the comparison it will compare the value we passed with the stored value of .00<1 third of a millisecond>. Which will result in an inequality since 0.003 != 0.0033333333...

I think that explains what the issue is. I see two possible fixes, and suggest we implement both:

• Adding datetimeParameterType=datetime to the connection string, per Fixes #1590 - [FEATURE REQUEST] Ability to explicitly specify conversion to datetime or datetime2  microsoft/mssql-jdbc#1687.
• Using DATETIME2(3) instead of DATETIME2, the latter defaulting to (7).