I am trying to use sq2o in a project. It works well under MSSQL but has issues with deleting when switched to Oracle DB 12c
public void delete(final String x, final String y) {
final String query = "DELETE FROM XXX WHERE columnx = :x AND columny = :y";
try (final Connection con = sql2o.beginTransaction();
final Query q = con.createQuery(query)) {
q.addParameter("x", x);
q.addParameter("y", y);
q.executeUpdate();
con.commit();
}
}
When I use this method I get rather enigmatic logs:
operation not allowed org.sql2o.Sql2oException: Error in executeUpdate, operation not allowed at org.sql2o.Query.executeUpdate(Query.java:527) at com.fusionrisk.authorisation.impl.AuthorisationRoleDao.delete(AuthorisationRoleDao.java:55) at com.fusionrisk.authorisation.impl.AuthorisationService.deleteUserRole(AuthorisationService.java:43) at fusionrisk.rest.AuthorisationResource.deleteRole(AuthorisationResource.java:59) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:151) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:171) at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:152) at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:104) at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:387) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:331) at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:103) at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:271) at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271) at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267) at org.glassfish.jersey.internal.Errors.process(Errors.java:315) at org.glassfish.jersey.internal.Errors.process(Errors.java:297) at org.glassfish.jersey.internal.Errors.process(Errors.java:267) at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:297) at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:254) at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1028) at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:372) at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:381) at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:344) at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:221) at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:800) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1669) at org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:83) at org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:300) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:224) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652)
at com.trmsys.cargo.shield.web.filter.PrincipalShiroFilter.doFilter(PrincipalShiroFilter.java:228) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.apache.shiro.web.servlet.AbstractShiroFilter.executeChain(AbstractShiroFilter.java:449) at com.trmsys.cargo.shield.shiro.web.ShieldFilter.access$100(ShieldFilter.java:34) at com.trmsys.cargo.shield.shiro.web.ShieldFilter$1.call(ShieldFilter.java:99) 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 com.trmsys.cargo.shield.shiro.web.ShieldFilter.doFilterInternal(ShieldFilter.java:95) at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at com.trmsys.cargo.security.cors.impl.CorsFilter.handleSimpleCORS(CorsFilter.java:345) at com.trmsys.cargo.security.cors.impl.CorsFilter.doFilter(CorsFilter.java:188) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at com.trmsys.cargo.rs.shared.impl.http.RequestLogFilter.doFilter(RequestLogFilter.java:59) at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1652) at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:585) at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:221) at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1125) at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:515) at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185) at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1059) at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:215) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) at org.eclipse.jetty.server.Server.handle(Server.java:497) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:248) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:620) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:540) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: operation not allowed at oracle.jdbc.driver.OracleStatement.getGeneratedKeys(OracleStatement.java:5174) at oracle.jdbc.driver.OracleStatementWrapper.getGeneratedKeys(OracleStatementWrapper.java:352) at org.apache.commons.dbcp.DelegatingStatement.getGeneratedKeys(DelegatingStatement.java:315) at org.apache.commons.dbcp.DelegatingStatement.getGeneratedKeys(DelegatingStatement.java:315) at org.sql2o.Query.executeUpdate(Query.java:522) ... 67 common frames omitted
It's just for delete. Insert and retrieval are working well.
I was googling the issue but the only results were resolving around closed resulSet which is not the case here.
This is not much of an answer, but I have been recently developing with sql2o under Eclipse, and I found the only way to figure out some issues was to build with the source and trace it down to where the exception was thrown. Time consuming, but less so than shooting in the dark.