Conversion exception when calling a stored procedure with a datetime in-out parameter while setting a String value

[labkey-adam]

Driver version

12.6.1 and 12.6.2

SQL Server version

Microsoft SQL Server 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 22631: )

Client Operating System

Windows 11

JAVA/JVM version

17.0.10+7

Table schema

CREATE PROCEDURE dbo.CurrentTime
    @currentTimeStamp datetime = null OUTPUT
AS
BEGIN
   SET @currentTimeStamp = CURRENT_TIMESTAMP;
END

Problem description

After using the Microsoft SQL Server JDBC driver for years without issue, we hit a blocking regression with v12.6.1 and v12.6.2. In short, when calling a stored procedure with a datetime in-out parameter and setting a String value, retrieving the out value now throws a conversion exception. The failure seems to hinge on using setObject() with Timestamp.toString() instead of a Timestamp object, even though Types.TIMESTAMP is specified in setObject() and the parameter is typed as datetime in the stored procedure.

We're setting the value to Timestamp.toString() because that's the recommended workaround mentioned on this page https://learn.microsoft.com/en-us/sql/connect/jdbc/using-basic-data-types?view=sql-server-ver16 (footnote 3). The code that's failing in our system is a very general-purpose ETL that works on client-provided tables, so we don't have the ability to change column types to datetime2.

If there's another workaround, we're all ears. And if more information is needed, please let us know.

Expected behavior

These are the results of the example code below when using v12.4.2.0

SQL Server JDBC Driver 12.4.2.0
Setting in/out parameter to a Timestamp results in current time: 2024-05-28 20:37:50.7033333 (java.sql.Timestamp)
Setting in/out parameter to Timestamp.toString() results in current time: 2024-05-28 20:37:50.7533333 (java.sql.Timestamp)

Actual behavior

These are the results of the example code below when using v12.6.1 or v12.6.2

SQL Server JDBC Driver 12.6.2.0
Setting in/out parameter to a Timestamp results in current time: 2024-05-28 20:29:39.67 (java.sql.Timestamp)
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the nvarchar value to JDBC data type TIMESTAMP.
	at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:818)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3754)
	at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:250)
	at com.microsoft.sqlserver.jdbc.Parameter.getValue(Parameter.java:457)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue(SQLServerCallableStatement.java:532)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getObject(SQLServerCallableStatement.java:942)
	at MSSQLTest.main(MSSQLTest.java:34)
Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
	at java.sql/java.sql.Timestamp.valueOf(Timestamp.java:224)
	at com.microsoft.sqlserver.jdbc.DDC.convertStringToObject(DDC.java:568)
	at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:803)
	... 6 more

Error message/stack trace

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: An error occurred while converting the nvarchar value to JDBC data type TIMESTAMP.
	at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:818)
	at com.microsoft.sqlserver.jdbc.ServerDTVImpl.getValue(dtv.java:3754)
	at com.microsoft.sqlserver.jdbc.DTV.getValue(dtv.java:250)
	at com.microsoft.sqlserver.jdbc.Parameter.getValue(Parameter.java:457)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getValue(SQLServerCallableStatement.java:532)
	at com.microsoft.sqlserver.jdbc.SQLServerCallableStatement.getObject(SQLServerCallableStatement.java:942)
	at MSSQLTest.main(MSSQLTest.java:34)
Caused by: java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
	at java.sql/java.sql.Timestamp.valueOf(Timestamp.java:224)
	at com.microsoft.sqlserver.jdbc.DDC.convertStringToObject(DDC.java:568)
	at com.microsoft.sqlserver.jdbc.DDC.convertStreamToObject(DDC.java:803)
	... 6 more

Any other details that can be helpful

A simple, complete class that reproduces the problem (along with the simple stored procedure above) is below.

import java.sql.*;

public class MSSQLTest
{
    public static void main(String[] args) throws SQLException
    {
        try (Connection conn = getConnection(args))
        {
            DatabaseMetaData dbmd = conn.getMetaData();
            System.out.println("SQL Server JDBC Driver " + dbmd.getDriverVersion());

            // This succeeds on both drivers
            try (CallableStatement stmt = conn.prepareCall("{call dbo.CurrentTime(?)}"))
            {
                stmt.setObject(1, new Timestamp(new java.util.Date().getTime()), Types.TIMESTAMP);  //  filterStartTimeStamp
                stmt.registerOutParameter(1, Types.TIMESTAMP);
                stmt.execute();
                Object outParam = stmt.getObject("currentTimeStamp");
                System.out.println("Setting in/out parameter to a Timestamp results in current time: " + outParam + " (" + outParam.getClass().getName() + ")");
            }

            // This succeeds on 12.4.2 but fails on 12.6.2
            try (CallableStatement stmt = conn.prepareCall("{call dbo.CurrentTime(?)}"))
            {
                stmt.setObject(1, new Timestamp(new java.util.Date().getTime()).toString(), Types.TIMESTAMP);  //  filterStartTimeStamp
                stmt.registerOutParameter(1, Types.TIMESTAMP);
                stmt.execute();
                Object outParam = stmt.getObject("currentTimeStamp");
                System.out.println("Setting in/out parameter to Timestamp.toString() results in current time: " + outParam + " (" + outParam.getClass().getName() + ")");
            }
        }
    }

    private static Connection getConnection(String[] args) throws SQLException
    {
        return (3 == args.length ?
            DriverManager.getConnection(args[0], args[1], args[2]) :
            DriverManager.getConnection("jdbc:sqlserver://" + args[0] + ":" + args[1] + ";trustServerCertificate=true;applicationName=MSSQLTest;databaseName=" + args[2], args[3], args[4]));
    }
}

JDBC trace logs

Provide the JDBC driver trace logs. Instructions can be found here: https://docs.microsoft.com/sql/connect/jdbc/tracing-driver-operation

[tkyc]

Thanks for the repro, taking a look as to what happened...

Update 2024/06/04 - Still debugging... I'm confused how sending the string changes the timestamp format. The old code path compared to the new path is same. The driver doesn't change the format at all and so I'm confused how the format is being affected. But for sure the format is affected by the major recent CallableStatement changes.

[tkyc]

A tentative hotfix is scheduled for June 20th.

[labkey-adam]

Thanks for the fix! Will test as soon as it's available.

[labkey-adam]

@tkyc I've tested v12.6.3 and incorporated it into our system. My simple repro code now works on this version. And the production system tests that were failing with previous 12.6.x versions are now passing. Thanks again for your quick turnaround on this fix!!