{"action":"created","issue":{"url":"https://api.github.com/repos/mybatis/mybatis-3/issues/681","repository_url":"https://api.github.com/repos/mybatis/mybatis-3","labels_url":"https://api.github.com/repos/mybatis/mybatis-3/issues/681/labels{/name}","comments_url":"https://api.github.com/repos/mybatis/mybatis-3/issues/681/comments","events_url":"https://api.github.com/repos/mybatis/mybatis-3/issues/681/events","html_url":"https://github.com/mybatis/mybatis-3/issues/681","id":152575577,"number":681,"title":"MsSQL Server Error with generatedKey and InsertTrigger on Table","user":{"login":"KlausDecadis","id":19152045,"avatar_url":"https://avatars.githubusercontent.com/u/19152045?v=3","gravatar_id":"","url":"https://api.github.com/users/KlausDecadis","html_url":"https://github.com/KlausDecadis","followers_url":"https://api.github.com/users/KlausDecadis/followers","following_url":"https://api.github.com/users/KlausDecadis/following{/other_user}","gists_url":"https://api.github.com/users/KlausDecadis/gists{/gist_id}","starred_url":"https://api.github.com/users/KlausDecadis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/KlausDecadis/subscriptions","organizations_url":"https://api.github.com/users/KlausDecadis/orgs","repos_url":"https://api.github.com/users/KlausDecadis/repos","events_url":"https://api.github.com/users/KlausDecadis/events{/privacy}","received_events_url":"https://api.github.com/users/KlausDecadis/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2016-05-02T15:19:33Z","updated_at":"2016-05-10T07:25:39Z","closed_at":"2016-05-10T07:23:28Z","body":"We use Ms SQL Server with JDBC4 driver. We have tables with \"after insert\" trigger and indentity columns.\r\n\r\n```sql\r\nCREATE TABLE [dbo].[MyBatisTest] (\r\n\t[Id] [int] IDENTITY(1,1) NOT NULL,\r\n\t[Name] [varchar](50) NOT NULL,\r\n\t[ChangedAt] [datetime]\r\n)\r\nGO\r\n\r\nCREATE TRIGGER [dbo].[trMyBatisTest] ON [dbo].[MyBatisTest] AFTER INSERT, UPDATE AS \r\nIF (@@ROWCOUNT = 0) \r\n    RETURN \r\nBEGIN \r\n    /* Changed setzen */\r\n    DECLARE @timestamp datetime\r\n    SET @timestamp  = CURRENT_TIMESTAMP\r\n    UPDATE \r\n      MyBatisTest\r\n    SET \r\n      ChangedAt = @timestamp\r\n    WHERE Id  IN (SELECT Id FROM INSERTED)\r\nEND\r\nGO\r\n```\r\n\r\nIn myBatis i have a mapping\r\n\r\n```xml\r\n  <insert id=\"insertUser\" parameterType=\"User\" useGeneratedKeys=\"true\" keyProperty=\"id\">\r\n    insert into MyBatisTest ([Name]) values (#{name})\r\n  </insert>\r\n```\r\n\r\nWhen i execute the code around, i get this Exception:\r\n\r\n```\r\norg.apache.ibatis.exceptions.PersistenceException: \r\n### Error updating database.  Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Die Anweisung muss ausgeführt werden, bevor Ergebnisse abgerufen werden können.\r\n### The error may involve de.decadis.mybatis.test.UserService.insertUser-Inline\r\n### The error occurred while setting parameters\r\n### SQL: insert into MyBatisTest ([Name]) values (?)\r\n### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Die Anweisung muss ausgeführt werden, bevor Ergebnisse abgerufen werden können.\r\n\tat org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)\r\n\tat org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:172)\r\n\tat org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:157)\r\n\tat de.decadis.mybatis.test.UserService.insertUser(UserService.java:42)\r\n\tat de.decadis.mybatis.test.TestMyBatis.main(TestMyBatis.java:17)\r\nCaused by: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Die Anweisung muss ausgeführt werden, bevor Ergebnisse abgerufen werden können.\r\n\tat org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:71)\r\n\tat org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processAfter(Jdbc3KeyGenerator.java:45)\r\n\tat org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:59)\r\n\tat org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:73)\r\n\tat org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49)\r\n\tat org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:115)\r\n\tat org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)\r\n\tat org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:170)\r\n\t... 3 more\r\nCaused by: com.microsoft.sqlserver.jdbc.SQLServerException: Die Anweisung muss ausgeführt werden, bevor Ergebnisse abgerufen werden können.\r\n\tat com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)\r\n\tat com.microsoft.sqlserver.jdbc.SQLServerStatement.getGeneratedKeys(SQLServerStatement.java:2040)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)\r\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.lang.reflect.Method.invoke(Method.java:601)\r\n\tat org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:78)\r\n\tat com.sun.proxy.$Proxy1.getGeneratedKeys(Unknown Source)\r\n\tat org.apache.ibatis.executor.keygen.Jdbc3KeyGenerator.processBatch(Jdbc3KeyGenerator.java:51)\r\n\t... 10 more\r\n```\r\n\r\nthe cause of the error is in the class PreparedStatementHandler. In the methode update the method are the following lines:\r\n\r\n```java\r\n    ps.execute();\r\n    int rows = ps.getUpdateCount();\r\n```\r\n\r\nIf I use instead this of the following line:\r\n\r\n```java\r\n    /*\r\n     * Works with MsSQL and Tables with AFTER INSERT TRIGGER\r\n     */\r\n   int rows = ps.executeUpdate();\r\n```\r\n\r\neverything work fine. \r\n[myBatis.zip](https://github.com/mybatis/mybatis-3/files/245355/myBatis.zip)\r\n\r\n\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/mybatis/mybatis-3/issues/comments/218079146","html_url":"https://github.com/mybatis/mybatis-3/issues/681#issuecomment-218079146","issue_url":"https://api.github.com/repos/mybatis/mybatis-3/issues/681","id":218079146,"user":{"login":"harawata","id":149268,"avatar_url":"https://avatars.githubusercontent.com/u/149268?v=3","gravatar_id":"","url":"https://api.github.com/users/harawata","html_url":"https://github.com/harawata","followers_url":"https://api.github.com/users/harawata/followers","following_url":"https://api.github.com/users/harawata/following{/other_user}","gists_url":"https://api.github.com/users/harawata/gists{/gist_id}","starred_url":"https://api.github.com/users/harawata/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/harawata/subscriptions","organizations_url":"https://api.github.com/users/harawata/orgs","repos_url":"https://api.github.com/users/harawata/repos","events_url":"https://api.github.com/users/harawata/events{/privacy}","received_events_url":"https://api.github.com/users/harawata/received_events","type":"User","site_admin":false},"created_at":"2016-05-10T07:25:39Z","updated_at":"2016-05-10T07:25:39Z","body":"Glad to know it worked. Thanks for the update!"}}