How to Fix org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed

Hello Java programmers, if you are using JdbcTemplate of Spring Framework to interact with the database from Java program and getting "org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed" error then you have come to the right place. Earlier, I have taught you how to use JdbcTemplate in Java and Spring, and in this article, I will teach you how to fix "org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed" error in Spring-based Java application. 

This issue comes when you try to insert or update data using Spring Framework's JdbcTempalte's update method and your data has some null values. The update method leaves PreparedStaement to guess the type of Object and most JDBC drivers have a problem while guessing the type of null object.

like in one case we had a DATE column in the database and we are passing null. so Microsoft JDBC SQL database driver has a problem with converting the null object to Date SQL type and it's trying to convert it into varbinary

and throwing the same exception:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 

bad SQL grammar [insert into EMP.dbo.Employee (EMP_ID, EMP_NAME, EMP_DOJ) values ( ?, ?, ? )]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: 

Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

at org.springframework.jdbc.support

.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:98)

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:602)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)

at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:875)

Cause and Solution of org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed Error

Now, let's see the actual cause and solution of this error 

Cause: 

we are using JdbcTemplate.update(SQL, object...) method for inserting data into tables. this method does not pass Type information to the JDBC driver passing JDBC type as SqlTypeValue.TYPE_UNKNOWN and for null values, it is using the JDBC driver metadata name to guess the type of object at runtime.

for JDBC driver's names starting with jConnect, SQLServer, and Apache Derby it was setting sqlType as Types.VARCHAR but for drivers names starting with "Microsoft SQL Server" it was called:

ps.setObject(paramIndex, null);

which was leaving JDBC driver to guess the SQL type for null values and for the Date column Microsoft SQL Server drivers was converting it considering as

VARBINARY. to be honest this issue shouldn't arrive if you provide SQL type information explicitly by wrapping your value in SqlParameterValue(Type, value) object and passing that to

JdbcTemplate.update(sql, object...)

For more details look setNull(PreparedStatement ps, int paramIndex, int sqlType, String typeName) method of org.springframework.jdbc.core.StatementCreatorUtils class.

Now, let's see the original code and modified code to fix this problem while using JdbcTemplate in Java-based Spring Application. 

Original Code which caused Exception:

Employee emp = new Employee(1, "Rakesh", new Date())
this.jdbcTemplate.update("insert into EMP.dbo.Employee 
(EMP_ID, EMP_NAME, EMP_DOJ values ( ?, ?, ?)"
                                , emp.getEmpId()
                                , emp.getEmpName()
                                , emp.getEmpDOJ() ));

             

Modified Code which fixed the error :

Employee emp = new Employee(1, "Rakesh", new Date())
this.jdbcTemplate
.update("insert into EMP.dbo.Employee (EMP_ID, EMP_NAME, EMP_DOJ values ( ?, ?, ?)"
         , new SqlParameterValue(java.sql.Types.DATE, emp.getEmpId())
         , new SqlParameterValue(java.sql.Types.DATE, emp.getEmpName())
         , new SqlParameterValue(java.sql.Types.DATE, emp.getEmpDOJ()) ));

That's all about how to fix this JdbcTemplate error in Java and Spring applications.  The " org.springframework.jdbc.BadSqlGrammarException: Implicit conversion from data type varbinary to date is not allowed" error comes because JDBC drive sends incorrect Type information for null values while inserting or updating data into tables. 

You can fix this error by explicitly specifying the type while inserting or updating records into a table using JdbcTemplate as shown in this article. This is one of the important things to keep in mind while using the JdbcTemplate.update() method to insert records into the database

Other Java Error and Exception Tutorials to Fix common Issues

• java.lang.ClassNotFoundException : org.Springframework.Web.Context.ContextLoaderListener (solution)
• java.lang.ClassNotFoundException: org.springframework.web.servlet.DispatcherServlet in Java Spring MVC Application [solution]
• How to connect to MySQL database in Java? (tutorial)
• How to fix "Error: Could not find or load main class" in Eclipse? (guide)
• java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory error (solution)
• java.sql.SQLException: No suitable driver found for 'jdbc:mysql://localhost:3306/mysql [Solution]
• How to deal with "No JVM installation found, please install 64-bit JDK" error? (solution)
• How to fix unable to find certification path error in Java SSL? (guide)
• How to avoid ConcurrentModificationException in Java? (tutorial)
• 10 common reasons for java.lang.NumberFormatException in Java? (tutorial)
• java.sql.SQLServerException: The index 58 is out of range - JDBC (solution)
• Fixing java.lang.unsupportedclassversionerror unsupported major.minor version 50.0 (solution)
• How to fix 'javac' is not recognized as an internal or external command (solution)
• How to solve "could not create the Java virtual machine" error in Java? (solution)
• Common reasons for java.lang.ArrayIndexOutOfBoundsException in Java? (solution)
• How to solve java.sql.BatchUpdateException: String or binary data would be truncated (guide)
• How to solve java.lang.ClassNotFoundException: com.mysql.jdbc.Driver error? (hint)
• 5 Reasons of NoClassDefFoundError in Java? (tutorial)
• How to fix Caused By: java.lang.NoClassDefFoundError: org/apache/log4j/Logger (solution)
• Cause of "java.lang.SecurityException: Missing required Permissions manifest attribute in main jar" [solution]
• How to fix "illegal start of expression" compile time error in Java? (tutorial)
• Cause and solution of "class, interface, or enum expected" compiler error in Java? (fix)
• How to solve "variable might not have initialized" compile-time error in Java? (answer)
• How to solve java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver? (solution)

Thanks for reading this article so far. If you find this information useful in solving your error then please share it with your friends and colleagues. If you are getting any issues or have doubts, please ask or drop a note.