[Geoserver-devel] Oracle NG - View on a table in a different schema not working via WFS-T

GeoServer GeoServer Developer
1

Hi all,

Oracle version 11.2
Windows Server 2003
Geoserver 2.02
Oracle NG datastore

We have a view in a schema which is based on a table in another schema. There is an entry in user_sdo_geom_metadata in the schema which holds the view and it is possible to update records in the view via SQL Developer or SQL Plus.

We have a data source in geoserver which connects directly to the schema with the view and the preview pages work fine and show the points in the view on an open layers map.

The problem comes when we try to update the records via WFS-T. The requests we are sending to geoserver worked fine when we referenced the table direct but now they go via the view we get the error shown at the bottom of this email.

The SRS of the table is 27700 (British National Grid) which in Oracle speak is 81989.

Should this work? Is it a bug? Am I doing something wrong? Have I missed something silly?

Any help would be appreciated.

regards,

Tom

INFO | jvm 1 | 2010/11/15 14:27:22 | 15 Nov 14:27:22 ERROR [geoserver.wfs] - Transaction failed
INFO | jvm 1 | 2010/11/15 14:27:22 | org.geoserver.wfs.WFSTransactionException: update error
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.UpdateElementHandler.execute(UpdateElementHandler.java:246)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.Transaction.execute(Transaction.java:334)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.Transaction.transaction(Transaction.java:109)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.DefaultWebFeatureService.transaction(DefaultWebFeatureService.java:163)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO | jvm 1 | 2010/11/15 14:27:22 | at java.lang.reflect.Method.invoke(Method.java:597)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.ows.util.EMFLogger.invoke(EMFLogger.java:51)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
INFO | jvm 1 | 2010/11/15 14:27:22 | at $Proxy19.transaction(Unknown Source)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO | jvm 1 | 2010/11/15 14:27:22 | at java.lang.reflect.Method.invoke(Method.java:597)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:599)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:230)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
INFO | jvm 1 | 2010/11/15 14:27:22 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
INFO | jvm 1 | 2010/11/15 14:27:22 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:124)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:174)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:249)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:73)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:183)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:47)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.Server.handle(Server.java:324)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
INFO | jvm 1 | 2010/11/15 14:27:22 | Caused by: java.lang.RuntimeException: java.sql.SQLException: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-13365: layer SRID does not match geometry SRID
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 270
INFO | jvm 1 | 2010/11/15 14:27:22 |
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.jdbc.JDBCDataStore.update(JDBCDataStore.java:1309)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.jdbc.JDBCFeatureStore.modifyFeatures(JDBCFeatureStore.java:354)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.data.store.ContentFeatureStore.modifyFeatures(ContentFeatureStore.java:349)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.vfny.geoserver.global.GeoServerFeatureStore.modifyFeatures(GeoServerFeatureStore.java:129)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.UpdateElementHandler.execute(UpdateElementHandler.java:236)
INFO | jvm 1 | 2010/11/15 14:27:22 | … 69 more
INFO | jvm 1 | 2010/11/15 14:27:22 | Caused by: java.sql.SQLException: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-13365: layer SRID does not match geometry SRID
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 270
INFO | jvm 1 | 2010/11/15 14:27:22 |
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.jdbc.JDBCDataStore.update(JDBCDataStore.java:1302)
INFO | jvm 1 | 2010/11/15 14:27:22 | … 73 more
INFO | jvm 1 | 2010/11/15 14:27:22 | 15 Nov 14:27:22 INFO [geoserver.wfs] -
INFO | jvm 1 | 2010/11/15 14:27:22 | Request: transaction
INFO | jvm 1 | 2010/11/15 14:27:22 | handle = null
INFO | jvm 1 | 2010/11/15 14:27:22 | service = WFS
INFO | jvm 1 | 2010/11/15 14:27:22 | version = 1.0.0
INFO | jvm 1 | 2010/11/15 14:27:22 | baseUrl = http://devserver:8080/geoserver/
INFO | jvm 1 | 2010/11/15 14:27:22 | providedVersion = null
INFO | jvm 1 | 2010/11/15 14:27:22 | lockId = null
INFO | jvm 1 | 2010/11/15 14:27:22 | group = [wfs:update=net.opengis.wfs.impl.UpdateElementTypeImpl@anonymised.com (filter: [ TBLPROBLEM.76 ], handle: null, inputFormat: , srsName: null, typeName: {http://SCAMBS}TBLPROBLEM)]
INFO | jvm 1 | 2010/11/15 14:27:22 | insert =
INFO | jvm 1 | 2010/11/15 14:27:22 | update = [net.opengis.wfs.impl.UpdateElementTypeImpl@anonymised.com (filter: [ TBLPROBLEM.76 ], handle: null, inputFormat: , srsName: null, typeName: {http://SCAMBS}TBLPROBLEM)]
INFO | jvm 1 | 2010/11/15 14:27:22 | delete =
INFO | jvm 1 | 2010/11/15 14:27:22 | native =
INFO | jvm 1 | 2010/11/15 14:27:22 | releaseAction = ALL

2

I didn’t even know that you could update views, I’m pretty sure in postgis you can’t. So I imagine no one has ever tried this before. I guess I’d call it an unimplemented feature. Which may be relatively easy to fix if a developer digs in to it, or may be tricky in some way. Looking at the error it says ’ ORA-13365: layer SRID does not match geometry SRID’ So I’d guess that there needs to be some code to make sure the statement passed in does that matching properly. I’d say turn up the logging level and see the exact statement passed in, then try to run that directly. If you get the same error then you just need to figure out what the proper sql should be, and then get the geotools code to write it properly. Patches definitely accepted.

C

On Mon, Nov 15, 2010 at 10:04 AM, Tom (JDi Solutions) <tom.dean@anonymised.com> wrote:

Hi all,

Oracle version 11.2
Windows Server 2003
Geoserver 2.02
Oracle NG datastore

We have a view in a schema which is based on a table in another schema. There is an entry in user_sdo_geom_metadata in the schema which holds the view and it is possible to update records in the view via SQL Developer or SQL Plus.

We have a data source in geoserver which connects directly to the schema with the view and the preview pages work fine and show the points in the view on an open layers map.

The problem comes when we try to update the records via WFS-T. The requests we are sending to geoserver worked fine when we referenced the table direct but now they go via the view we get the error shown at the bottom of this email.

The SRS of the table is 27700 (British National Grid) which in Oracle speak is 81989.

Should this work? Is it a bug? Am I doing something wrong? Have I missed something silly?

Any help would be appreciated.

regards,

Tom

INFO | jvm 1 | 2010/11/15 14:27:22 | 15 Nov 14:27:22 ERROR [geoserver.wfs] - Transaction failed
INFO | jvm 1 | 2010/11/15 14:27:22 | org.geoserver.wfs.WFSTransactionException: update error
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.UpdateElementHandler.execute(UpdateElementHandler.java:246)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.Transaction.execute(Transaction.java:334)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.Transaction.transaction(Transaction.java:109)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.DefaultWebFeatureService.transaction(DefaultWebFeatureService.java:163)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO | jvm 1 | 2010/11/15 14:27:22 | at java.lang.reflect.Method.invoke(Method.java:597)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.ows.util.EMFLogger.invoke(EMFLogger.java:51)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
INFO | jvm 1 | 2010/11/15 14:27:22 | at $Proxy19.transaction(Unknown Source)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO | jvm 1 | 2010/11/15 14:27:22 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO | jvm 1 | 2010/11/15 14:27:22 | at java.lang.reflect.Method.invoke(Method.java:597)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.ows.Dispatcher.execute(Dispatcher.java:599)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.ows.Dispatcher.handleRequestInternal(Dispatcher.java:230)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:153)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:48)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:809)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
INFO | jvm 1 | 2010/11/15 14:27:22 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
INFO | jvm 1 | 2010/11/15 14:27:22 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:487)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1093)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.vfny.geoserver.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:108)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:265)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.intercept.web.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:107)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.intercept.web.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:72)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.ui.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:124)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.providers.anonymous.AnonymousProcessingFilter.doFilter(AnonymousProcessingFilter.java:125)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.ui.basicauth.BasicProcessingFilter.doFilter(BasicProcessingFilter.java:174)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.context.HttpSessionContextIntegrationFilter.doFilter(HttpSessionContextIntegrationFilter.java:249)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:275)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterChainProxy.doFilter(FilterChainProxy.java:149)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.acegisecurity.util.FilterToBeanProxy.doFilter(FilterToBeanProxy.java:98)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.filters.LoggingFilter.doFilter(LoggingFilter.java:73)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.filters.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:183)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.filters.GZIPFilter.doFilter(GZIPFilter.java:47)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1084)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:726)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:206)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.Server.handle(Server.java:324)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:842)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:648)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:211)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:450)
INFO | jvm 1 | 2010/11/15 14:27:22 | Caused by: java.lang.RuntimeException: java.sql.SQLException: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-13365: layer SRID does not match geometry SRID
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 270
INFO | jvm 1 | 2010/11/15 14:27:22 |
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.jdbc.JDBCDataStore.update(JDBCDataStore.java:1309)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.jdbc.JDBCFeatureStore.modifyFeatures(JDBCFeatureStore.java:354)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.data.store.ContentFeatureStore.modifyFeatures(ContentFeatureStore.java:349)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.vfny.geoserver.global.GeoServerFeatureStore.modifyFeatures(GeoServerFeatureStore.java:129)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geoserver.wfs.UpdateElementHandler.execute(UpdateElementHandler.java:236)
INFO | jvm 1 | 2010/11/15 14:27:22 | … 69 more
INFO | jvm 1 | 2010/11/15 14:27:22 | Caused by: java.sql.SQLException: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-13365: layer SRID does not match geometry SRID
INFO | jvm 1 | 2010/11/15 14:27:22 | ORA-06512: at “MDSYS.SDO_INDEX_METHOD_10I”, line 270
INFO | jvm 1 | 2010/11/15 14:27:22 |
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:955)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3316)
INFO | jvm 1 | 2010/11/15 14:27:22 | at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3422)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
INFO | jvm 1 | 2010/11/15 14:27:22 | at org.geotools.jdbc.JDBCDataStore.update(JDBCDataStore.java:1302)
INFO | jvm 1 | 2010/11/15 14:27:22 | … 73 more
INFO | jvm 1 | 2010/11/15 14:27:22 | 15 Nov 14:27:22 INFO [geoserver.wfs] -
INFO | jvm 1 | 2010/11/15 14:27:22 | Request: transaction
INFO | jvm 1 | 2010/11/15 14:27:22 | handle = null
INFO | jvm 1 | 2010/11/15 14:27:22 | service = WFS
INFO | jvm 1 | 2010/11/15 14:27:22 | version = 1.0.0
INFO | jvm 1 | 2010/11/15 14:27:22 | baseUrl = http://devserver:8080/geoserver/
INFO | jvm 1 | 2010/11/15 14:27:22 | providedVersion = null
INFO | jvm 1 | 2010/11/15 14:27:22 | lockId = null
INFO | jvm 1 | 2010/11/15 14:27:22 | group = [wfs:update=net.opengis.wfs.impl.UpdateElementTypeImpl@anonymised.com (filter: [ TBLPROBLEM.76 ], handle: null, inputFormat: , srsName: null, typeName: {http://SCAMBS}TBLPROBLEM)]
INFO | jvm 1 | 2010/11/15 14:27:22 | insert =
INFO | jvm 1 | 2010/11/15 14:27:22 | update = [net.opengis.wfs.impl.UpdateElementTypeImpl@anonymised.com (filter: [ TBLPROBLEM.76 ], handle: null, inputFormat: , srsName: null, typeName: {http://SCAMBS}TBLPROBLEM)]
INFO | jvm 1 | 2010/11/15 14:27:22 | delete =
INFO | jvm 1 | 2010/11/15 14:27:22 | native =
INFO | jvm 1 | 2010/11/15 14:27:22 | releaseAction = ALL

Centralized Desktop Delivery: Dell and VMware Reference Architecture
Simplifying enterprise desktop deployment and management using
Dell EqualLogic storage and VMware View: A highly scalable, end-to-end
client virtualization framework. Read more!
http://p.sf.net/sfu/dell-eql-dev2dev

Geoserver-devel mailing list
Geoserver-devel@anonymised.comsts.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-devel

3

On Tue, Nov 16, 2010 at 12:15 AM, Chris Holmes <cholmes@anonymised.com> wrote:

I didn't even know that you could update views, I'm pretty sure in postgis
you can't. So I imagine no one has ever tried this before. I guess I'd
call it an unimplemented feature. Which may be relatively easy to fix if a
developer digs in to it, or may be tricky in some way. Looking at the error
it says ' ORA-13365: layer SRID does not match geometry SRID' So I'd guess
that there needs to be some code to make sure the statement passed in does
that matching properly. I'd say turn up the logging level and see the exact
statement passed in, then try to run that directly. If you get the same
error then you just need to figure out what the proper sql should be, and
then get the geotools code to write it properly. Patches definitely
accepted.

Updating views in Oracle is possible and it has been done (it was
actually sponsored work).

However the JDBC stores are really just not able to deal with a
multi-schema situation.
If you want to work like that, as a workaround, create a separate
store for each schema you
want to access (that applies to all JDBC stores) and eventually put
the connection pool in JNDI
so that you can share it.

For reference here is the relevant Jira:
http://jira.codehaus.org/browse/GEOT-2833

Cheers
Andrea

-----------------------------------------------------
Ing. Andrea Aime
Senior Software Engineer

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584962313
fax: +39 0584962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf

-----------------------------------------------------

4

Thanks for the info. I think the solution for us here then is to move the spatial column out of the main application table and put it in the schema we use for storing the spatial data. That way we only need the one data store in geoserver and can create the link between the datasets in the application code.

Tom

On Tue, Nov 16, 2010 at 7:38 AM, Andrea Aime <andrea.aime@anonymised.com> wrote:

On Tue, Nov 16, 2010 at 12:15 AM, Chris Holmes <cholmes@anonymised.com> wrote:

I didn’t even know that you could update views, I’m pretty sure in postgis
you can’t. So I imagine no one has ever tried this before. I guess I’d
call it an unimplemented feature. Which may be relatively easy to fix if a
developer digs in to it, or may be tricky in some way. Looking at the error
it says ’ ORA-13365: layer SRID does not match geometry SRID’ So I’d guess
that there needs to be some code to make sure the statement passed in does
that matching properly. I’d say turn up the logging level and see the exact
statement passed in, then try to run that directly. If you get the same
error then you just need to figure out what the proper sql should be, and
then get the geotools code to write it properly. Patches definitely
accepted.

Updating views in Oracle is possible and it has been done (it was
actually sponsored work).

However the JDBC stores are really just not able to deal with a
multi-schema situation.
If you want to work like that, as a workaround, create a separate
store for each schema you
want to access (that applies to all JDBC stores) and eventually put
the connection pool in JNDI
so that you can share it.

For reference here is the relevant Jira:
http://jira.codehaus.org/browse/GEOT-2833

Cheers
Andrea

Ing. Andrea Aime
Senior Software Engineer

GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy

phone: +39 0584962313
fax: +39 0584962313

http://www.geo-solutions.it
http://geo-solutions.blogspot.com/
http://www.linkedin.com/in/andreaaime
http://twitter.com/geowolf