Are it correct that I must explicit set jdbc types for java 8 date/time types? #{schedule.startDate, jdbcType=DATE}, #{schedule.endTime, jdbcType=TIME}, If we do not set date or time jdbcType, we get an conversion error if the java side object are null. We are using mssql-jdbc 7.4.1.jre11 and we are using Java 11 -Nino You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/db46c44d-a880-4736-99cf-f2a015686fe5%40googlegroups.com. |
Hello Nino, I don't think that is necessary. Please post the full stack trace of the 'conversion error'. And which Java 8 types do you use? LocalDate and LocalTime? Regards, Iwao On Fri, Feb 28, 2020 at 10:58 PM nino martinez wael <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2Rkm7wYT77nfCF_N6xgazMR-_qnwdMdHS0kj-u%2B8T2VLA%40mail.gmail.com. |
Yes localtime and localdate:) create sql:
JAVA:
stacktrace: ### Error updating database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query. ### The error may exist in dk/netdesign/ccadmin/orion/schedule/mapper/ScheduleMapper.xml ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: DECLARE @applicationId BIGINT; DECLARE @scheduleId BIGINT; DECLARE @actionId BIGINT; SET @applicationId=?; SET @scheduleId=?; SET @actionId=NULL; SET @actionId=?; BEGIN UPDATE Schedule SET dayOfWeek=?, startTime=?,startDate=?,endTime=?,endDate=?, description=? WHERE id=@scheduleId END IF (@actionId is null) BEGIN SELECT @actionId=FK_Action FROM ApplicationToAction WHERE FK_Schedule=@scheduleId AND FK_Application=@applicationId; DELETE FROM ApplicationToAction WHERE FK_Schedule=@scheduleId AND FK_Application=@applicationId ; DELETE FROM Action WHERE id = @actionId; SET @actionId=null; END BEGIN DECLARE @newActionId BIGINT; MERGE Action AS T USING (VALUES (?,?,?,?,?,?,?))as S(id,FK_ActionType, value, FK_Script, FK_Menu, FK_Application, useDefault) ON T.id = s.id WHEN MATCHED THEN UPDATE SET FK_ActionType=S.FK_ActionType, value=S.value, FK_Script=S.FK_Script, FK_Menu=S.FK_Menu, FK_Application=S.FK_Application, useDefault=S.useDefault WHEN NOT MATCHED THEN INSERT (FK_ActionType, value, FK_Script, FK_Menu, FK_Application, useDefault) VALUES(S.FK_ActionType, S.value, S.FK_Script, S.FK_Menu, S.FK_Application, S.useDefault); SET @newActionId = SCOPE_IDENTITY(); IF(@newActionId is not null) BEGIN INSERT INTO ApplicationToAction (FK_Application, FK_Action, FK_Schedule) VALUES(@applicationId, @newActionId, @scheduleId); SET @actionId=@newActionId; END END ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query. ; uncategorized SQLException; SQL state [S0003]; error code [257]; Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.; 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.] with root cause 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 com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:505) ~[mssql-jdbc-7.4.1.jre11.jar:?] at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.4.2.jar:?] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.2.jar:?] at jdk.internal.reflect.GeneratedMethodAccessor144.invoke(Unknown Source) ~[?:?] at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?] at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?] at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.3.jar:3.5.3] at com.sun.proxy.$Proxy150.execute(Unknown Source) ~[?:?] at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.3.jar:3.5.3] at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.3.jar:3.5.3] at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.3.jar:3.5.3] at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.3.jar:3.5.3] at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.3.jar:3.5.3] at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.3.jar:3.5.3] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?] at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?] at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?] at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.3.jar:2.0.3] at com.sun.proxy.$Proxy117.update(Unknown Source) ~[?:?] at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:287) ~[mybatis-spring-2.0.3.jar:2.0.3] at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67) ~[mybatis-3.5.3.jar:3.5.3] at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:93) ~[mybatis-3.5.3.jar:3.5.3] at com.sun.proxy.$Proxy125.updateScheduleOrActionForApplication(Unknown Source) ~[?:?] at dk.netdesign.ccadmin.orion.application.service.ApplicationServiceImpl.updateScheduleForApplication(ApplicationServiceImpl.java:69) ~[classes/:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?] at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:?] at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?] at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?] at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:888) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.servlet.FrameworkServlet.doPut(FrameworkServlet.java:920) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:663) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.3.RELEASE.jar:5.2.3.RELEASE] at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:209) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE] at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.2.1.RELEASE.jar:5.2.1.RELEASE] at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:358) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:271) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:108) ~[spring-boot-actuator-2.2.4.RELEASE.jar:2.2.4.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.3.RELEASE.jar:5.2.3.RELEASE] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:860) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1598) [tomcat-embed-core-9.0.30.jar:9.0.30] at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.30.jar:9.0.30] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) [?:?] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) [?:?] at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.30.jar:9.0.30] at java.lang.Thread.run(Thread.java:834) [?:?] fredag den 28. februar 2020 kl. 15.23.25 UTC+1 skrev Iwao AVE!:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/c0eb94e7-ee86-4e6a-82c0-695015a556a1%40googlegroups.com. |
Thanks for the code and the stack trace, Nino! I could reproduce the issue. Java types actually don't matter and the error reproduces even with the old java.util.Date or java.sql.Time. Reading the code, it seems to be a limitation in MS SQL Server engine, so I filed it as a feature request: You might have to specify jdbcType until the request is accepted/implemented. ☹️ Regards, Iwao On Fri, Feb 28, 2020 at 11:47 PM nino martinez wael <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2QXd1qZawUzy5jfh0ODy4jC%2BgLOyRmiZ19EhoEi-WBp%2BQ%40mail.gmail.com. |
Cool, thanks for the awesome work :)
-- -Nino søndag den 1. marts 2020 kl. 11.41.46 UTC+1 skrev Iwao AVE!:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/b6abe12c-e633-4e35-ab3d-2f96cc65fe4f%40googlegroups.com. |
In reply to this post by nino martinez wael
null object types almost always need explicit jdbc types
-Tim On Feb 28, 2020, at 08:58, nino martinez wael <[hidden email]> wrote: -- You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/763C3CEF-B32F-4789-AD77-EA89D3DBA6A4%40gmail.com. |
I got curious and did the same test against a few DBs. Oracle 18.3.0.0 (driver version 19.3.0.0)
MySQL 5.7.28 (connector-java 8.0.19)
PostgreSQL 11.4 (pgjdbc 42.2.10)
Db2 11.5 (driver 4.26.14)
MS SQL Server 14 (mssql-jdbc 8.2.1)
So, MS SQL Server’s unfriendly behavior seems to be an exception. ;) [1] [2] [3] [4] [5] On Sun, Mar 22, 2020 at 10:37 PM Tim Chen <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2SYGFma_Lgtte-sOhi3TYMsTZdWw-%2BATgGyUu2a-xai0w%40mail.gmail.com. |
Correction: `Types.OTHER` works for MySQL's `ENUM` and `SET` as well. On Tue, Mar 24, 2020 at 1:27 AM Iwao AVE! <[hidden email]> wrote:
You received this message because you are subscribed to the Google Groups "mybatis-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email]. To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2SDKxazrzH7n5XJ%2BgQpkC2%2BpMYg0K3pv6pwQ73fHRJ4XA%40mail.gmail.com. |
Free forum by Nabble | Edit this page |