java - 为什么我的存储过程的日期时间参数被拒绝？

Question

我正在开发一个 Java 服务器应用程序，该应用程序使用 Spring 3 和 C3P0 访问 Microsoft SQL Server 2008 R2 数据库，使用 Microsoft 的 JDBC 4 驱动程序 3.0 版。

我有一个存储过程，它的输入定义如下:

@modifiedAfter datetime = NULL

我正在使用 Spring 构建对此存储过程的调用。

我正在构建一个 MapSqlParameterSource包含我的参数:

MapSqlParameterSource in = new MapSqlParameterSource()
in.addValue("modifiedAfter", "2011-01-01T00:00:00", Types.TIMESTAMP)

但是当我执行调用时:

this.sprocCall.execute(in);

我明白了:

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string

……我不知道为什么。

我尝试了一些添加参数的变体，例如将其作为 Date 传递，或者将其指定为 VARCHAR，或者不指定类型— 它们都不起作用。

我开始怀疑这个问题可能与Spring有关。我写了一个小的 Groovy 脚本来尝试隔离问题，这工作得很好:

dt = new DateTime("2012-02-01T00:00:00") // Joda DateTime
println sql.rows("exec spMySproc @modifiedAfter=${Sql.TIMESTAMP(dt.toString())}")

…但是当我尝试使用 MapSqlParameterSource 的等效方法时，我得到了上述错误。

在这一点上，我被难住了。

这是堆栈跟踪的顶部:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call spGetPoliciesCatalogPaged(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952)
    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:368)
    at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:342)
    at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:164)

…之后是我的一些类(class)，然后是:

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4762)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1682)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:955)
    at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91)
    at org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1124)
    at org.springframework.jdbc.core.JdbcTemplate.extractReturnedResults(JdbcTemplate.java:1023)
    at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:995)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:936)
    ... 68 more

我尝试更新到最新版本的 spring-jdbc，3.1.1，但没有帮助。

如果有任何帮助，我将不胜感激!

谢谢，视频

Answer 1

我的一位同事发现了这一点。事实证明，我是因为责备 Spring 和/或 Microsoft SQL Server 驱动程序而仓促行事。问题确实是在将字符串转换为日期/时间时出错——但这不是我的参数直接的问题，而是我的 sproc 生成的动态 SQL 运行的查询的问题，< em>如果我传入了这个参数。

有问题的代码看起来像这样:

IF @modifiedAfter IS NOT NULL BEGIN
    SET @SQL = @SQL + N'AND mytable.ThingLastUpdated > @modifiedAfter'
END

一旦包含在 @SQL 中的动态 SQL 被实际执行，@modifiedAfter 的内容——一个完全有效的 DATETIME，从我的 Java 代码完全没有问题 — 与 mytable.ThingLastUpdated 的内容进行了比较 — 这是一个 VARCHAR 列，这意味着 SQL Server 必须将其内容转换为 DATETIME 以便可以将它们与 @modifiedAfter 进行比较——但并非 mytable.ThingLastUpdated 中的所有值都可以成功转换，因此会引发转换错误.

我决定的修复方法是将 mytable.ThingLastUpdated 的类型更改为 DATETIME，并在插入时进行任何需要的转换，我认为这样会更有效率而不是在查询时这样做。

经验教训:如果 SQL 问题不在我的 Java 代码中，它可能在我的 SQL 中——而不是在 Spring 或 JDBC 驱动程序中。佩卡奇。