CODEWITHSUNDEEP
javasql-serverspringstored-proceduresjdbc
Avi Flax
Avi Flax

Reputation: 51829

Why is my datetime parameter for a stored procedure being rejected?

I’m working on a Java server app which uses Spring 3 and C3P0 to access a Microsoft SQL Server 2008 R2 database, using version 3.0 of Microsoft’s JDBC 4 driver.

I have a sproc which has an input defined like so:

@modifiedAfter datetime = NULL

I’m using Spring to construct a call to this sproc.

I’m constructing a MapSqlParameterSource to contain my parameters:

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

But when I execute the call:

this.sprocCall.execute(in);

I get this:

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

…and I have no idea why.

I’ve tried a few variations on adding the parameter, such as passing it in as a Date, or specifying it as a VARCHAR, or not specifying the type — none of them work.

I’m beginning to suspect that the problem might be related to Spring. I wrote a little Groovy script to try to isolate the problem, and this works just fine:

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

…but when I try the equivalent approach with MapSqlParameterSource, I get the above error.

At this point, I’m stumped.

Here’s the top of the stacktrace:

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)

… which is followed by some of my classes, then this:

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

I tried updating to the latest version of spring-jdbc, 3.1.1, but it did not help.

I’d appreciate any help at all!

Thanks, Avi

Upvotes: 3

Views: 5823

Answers (4)

sathish_at_madison
sathish_at_madison

Reputation: 833

Had a similar problem in and was looking @ MSDN and looks like Sql-Server 2005 had a bug with precision conversion. Supposedly the version you are using has bumped up the precision 100ns. Will it be possible verify if the time-stamp precision. Here is the link for the discussion: Sql Server Conversion error

Upvotes: 1

Avi Flax
Avi Flax

Reputation: 51829

A co-worker of mine figured this out. It turns out that I had jumped the gun by blaming Spring and/or the Microsoft SQL Server driver. The problem was indeed an error when converting a character string to a date/time — but it wasn’t a problem directly with my parameter, it was a problem with a query that was being run by the dynamic SQL generated by my sproc, if I passed in this parameter.

The problematic code looks something like this:

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

Once the dynamic SQL contained in @SQL was actually executed, the contents of @modifiedAfter — a perfectly valid DATETIME, sent in from my Java code with no issues at all — were compared with the contents of mytable.ThingLastUpdated — which was a VARCHAR column which meant that SQL Server had to convert its contents to DATETIME so that they could be compared to @modifiedAfter — but not all values in mytable.ThingLastUpdated could be successfully converted, hence the conversion error being thrown.

The fix I decided on was to change the type of mytable.ThingLastUpdated to DATETIME, and do any needed conversion at insertion time, which I think will be more efficient than doing so at query time.

Lesson learned: if a SQL problem isn’t in my Java code, it’s probably in my SQL — not in Spring or a JDBC driver. PEBKAC.

Upvotes: 2

Virus
Virus

Reputation: 3415

This error is defenitely not a SQL problem bacause it converts that string "2011-01-01T00:00:00" to datetime successfully. The sql parameter type you have declared is of type TIMESTAMP and you are passing a string. I think that is where it fails to convert.

Try declaring the sql parameter type as string or varchar instead of TimeStamp.

Upvotes: 1

Bhabani Sankar Mishra
Bhabani Sankar Mishra

Reputation: 523

How about passing the input as String/varchar and then converting it to datetime in the sproc?

Upvotes: 1

Related Questions