Lorem ipsum dolor prumus versus lorem.

Forex & Stock

Lorem ipsum dolor prumus versus lorem.

Lorem ipsum dolor prumus versus lorem.

Java.SQL Error: Prepared Statement not in batch mode

I am using Java with backend HSQL. My prepared statement was working fine earlier. Recently, I created dependencies between department and items table and made the dep_seq_id column from auto generated seq_id to a simple column(we are updating id through java code now). After these changes, all dependent table create/update are failing with error like "statement not in batch mode".

The tables structure are like this:

CREATE TABLE DEPARTMENT ( DEP_SEQ_ID INTEGER NOT NULL, DEPT_STATE VARCHAR(6) NOT NULL, NAME VARCHAR(20) NOT NULL, YEAR VARCHAR(4), CREATE_DATE DATE, LINK VARCHAR(255), PRIMARY KEY(SEQ_ID,DEPT_STATE) ); CREATE TABLE ITEMS ( ITEM_ID INTEGER NOT NULL, ITEM_STATE VARCHAR(6) NOT NULL, NAME VARCHAR(20), ITEM_CODE VARCHAR(10), DEP_SEQ_ID INTEGER, DEPT_STATE VARCHAR(6), FOREIGN KEY(DEP_SEQ_ID,DEPT_STATE) REFERENCES DEPARTMENT(SEQ_ID, DEPT_STATE) ON DELETE CASCADE ON UPDATE CASCADE );

I do not understand what I am doing wrong here. Following is the code where I am trying to insert data in Items table, partial code is shown below. I hope it makes sense, I have changed names and columns, as I cant share the actual code.

 PreparedStatement statement = null; ResultSet generatedKeys = null; Long mappingId = null; try { statement = m_connection.prepareStatement("insert into ITEMS (ITEM_ID, ITEM_STATE, NAME, ITEM_CODE, DEP_SEQ_ID, DEPT_STATE) values(?,?,?,?,?,?)"); for(WersMapping wersMapping : wersMapList){ statement.setLong(1, item.getItemId()); statement.setInt(2, item.getState()); statement.setString(3, item.getName()); statement.setString(4, item.getItemCode()); statement.setInt(5, item.getDeptId()); statement.setString(6, item.getDeptState()); statement.addBatch(); } int[] nArray = statement.executeBatch(); int totalInserts = 0; if(nArray != null){ for(int n:nArray){ totalInserts = totalInserts + n; } LOGGER.log(Level.INFO,

"Successfully created "+totalInserts + " Mapping instances"); } return new Long(totalInserts) ;

Error Stack:

Aug 17, 2015 3:42:33 PM ItemMapDao create
SEVERE: Error inserting into Items Mapping table
java.sql.SQLException: statement is not in batch mode at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.Util.sqlException(Unknown Source) at org.hsqldb.jdbc.Util.sqlExceptionSQL(Unknown Source) at org.hsqldb.jdbc.JDBCPreparedStatement.executeBatch(Unknown Source) at com.ItemMapDao.create(ItemMapDao.java:367) at access.MaintanenceDac.updateFeatureSpecificData(MaintanenceDac.java:907) at access.MaintanenceDac.updateFeatureState(MaintanenceDac.java:649) at access.MaintanenceDac.updateCell(MaintanenceDac.java:154) at services.MaintainServices.updateSystemFeature(MaintainServices.java:156) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.apache.wink.server.internal.handlers.InvokeMethodHandler.handleRequest(InvokeMethodHandler.java:63) at org.apache.wink.server.handlers.AbstractHandler.handleRequest(AbstractHandler.java:33) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.handlers.CreateInvocationParametersHandler.handleRequest(CreateInvocationParametersHandler.java:54) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.handlers.FindResourceMethodHandler.handleSubResourceMethod(FindResourceMethodHandler.java:183) at org.apache.wink.server.internal.handlers.FindResourceMethodHandler.handleRequest(FindResourceMethodHandler.java:110) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.handlers.FindRootResourceHandler.handleRequest(FindRootResourceHandler.java:95) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.handlers.HeadMethodHandler.handleRequest(HeadMethodHandler.java:53) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.handlers.OptionsMethodHandler.handleRequest(OptionsMethodHandler.java:46) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.handlers.SearchResultHandler.handleRequest(SearchResultHandler.java:33) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.log.ResourceInvocation.handleRequest(ResourceInvocation.java:92) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.internal.log.Requests.handleRequest(Requests.java:76) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:26) at org.apache.wink.server.handlers.RequestHandlersChain.handle(RequestHandlersChain.java:22) at org.apache.wink.server.handlers.AbstractHandlersChain.doChain(AbstractHandlersChain.java:63) at org.apache.wink.server.handlers.AbstractHandlersChain.run(AbstractHandlersChain.java:48) at org.apache.wink.server.internal.RequestProcessor.handleRequestWithoutFaultBarrier(RequestProcessor.java:207) at org.apache.wink.server.internal.RequestProcessor.handleRequest(RequestProcessor.java:154) at org.apache.wink.server.internal.servlet.RestServlet.service(RestServlet.java:119) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61) at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108) at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137) at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:66) at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) at org.apache.shiro.web.servlet.AbstractShiroFilter$1.call(AbstractShiroFilter.java:365) at org.apache.shiro.subject.support.SubjectCallable.doCall(SubjectCallable.java:90) at org.apache.shiro.subject.support.SubjectCallable.call(SubjectCallable.java:83) at org.apache.shiro.subject.support.DelegatingSubject.execute(DelegatingSubject.java:383) at org.apache.shiro.web.servlet.AbstractShiroFilter.doFilterInternal(AbstractShiroFilter.java:362) at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:96) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:936) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) at java.lang.Thread.run(Thread.java:662)
Caused by: org.hsqldb.HsqlException: statement is not in batch mode at org.hsqldb.error.Error.error(Unknown Source) at org.hsqldb.error.Error.error(Unknown Source) ... 88 more


Category: Spread

Similar articles: