10 Replies Latest reply on Jan 6, 2016 2:17 PM by Ramesh Reddy

    Disable System Schemas for oData access

    José Antonio Tarifa Lorenzo Newbie

      Hello,

       

      I'm checking if Teiid is a good solution for MySQL - Salesforce data integration via oData.  For that, I'm trying the dockerized image of Teiid (Quickstart example with Dockerized Teiid) and configuring Mysql as the DataSource for oData Access.  And everything goes well, but when I'm importing the feed into Salesforce, I have an error about the incompatibility of the binary type :

      The Entity Data Model type "Collection(Edm.Binary)" isn't supported.

       

      It's a Salesforce constraint, I know, but that 'binary types' come from the SYS and SYSADMIN schemas published by Teiid, not from my working schema named 'accounts'.

       

      Is there any way to disable the exporting of these schemas and only export my accounts schema?

       

      Regards.

        • 1. Re: Disable System Schemas for oData access
          Steven Hawkins Master

          > Is there any way to disable the exporting of these schemas and only export my accounts schema?

           

          Currently no, but that should be an option.  Can you log something for that?

          1 of 1 people found this helpful
          • 2. Re: Disable System Schemas for oData access
            Ramesh Reddy Master

            BTW, when you use OData V4 interface on Teiid, the $metadata is rooted at each schema (model) level, so by default your "accounts" $metdata will not contain any of the SYS or SYSADMIN models. To use OData V4, try URL like

             

            http://host:port/odata4/vdb-name.vdb-version/model-name/tablename

             

            Ramesh..

            • 3. Re: Disable System Schemas for oData access
              José Antonio Tarifa Lorenzo Newbie

              Thanks for your replies Steven and Ramesh.

               

              Ramesh, I did what you said and I used odata4 URL, and SYS and SYSTEM schemas were not shown.  But once I imported the feed into Salesforce and started to use the model, i.e. when I did an UPDATE, an exception was thrown by teiid:

               

              16:47:55,783 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/odata4].[odata4]] (http-/0.0.0.0:8080-1)  JBWEB000236: Servlet.service() for servlet odata4 threw exception: java.lang.ClassNotFoundException: com.fasterxml.aalto.stax.InputFactoryImpl from [Module "org.apache.olingo:main" from local module loader @6636e83b (finder: local module finder @1ec8adea (roots: /opt/jboss/teiid-8.12.0.Final/modules,/opt/jboss/teiid-8.12.0.Final/modules/system/layers/dv,/opt/jboss/teiid-8.12.0.Final/modules/system/layers/base))]

                at org.jboss.modules.ModuleClassLoader.findClass(ModuleClassLoader.java:213) [jboss-modules.jar:1.3.6.Final-redhat-1]

                at org.jboss.modules.ConcurrentClassLoader.performLoadClassUnchecked(ConcurrentClassLoader.java:459) [jboss-modules.jar:1.3.6.Final-redhat-1]

                at org.jboss.modules.ConcurrentClassLoader.performLoadClassChecked(ConcurrentClassLoader.java:408) [jboss-modules.jar:1.3.6.Final-redhat-1]

                at org.jboss.modules.ConcurrentClassLoader.performLoadClass(ConcurrentClassLoader.java:389) [jboss-modules.jar:1.3.6.Final-redhat-1]

                at org.jboss.modules.ConcurrentClassLoader.loadClass(ConcurrentClassLoader.java:134) [jboss-modules.jar:1.3.6.Final-redhat-1]

                at org.apache.olingo.server.core.ODataImpl.createDeserializer(ODataImpl.java:121)

                at org.apache.olingo.server.core.requests.DataRequest$EntityRequest.getEntityFromClient(DataRequest.java:338)

                at org.apache.olingo.server.core.requests.DataRequest$EntityRequest.execute(DataRequest.java:325)

                at org.apache.olingo.server.core.requests.DataRequest.execute(DataRequest.java:247)

                at org.apache.olingo.server.core.ServiceDispatcher.execute(ServiceDispatcher.java:117)

                at org.apache.olingo.server.core.OData4HttpHandler.process(OData4HttpHandler.java:65)

                at org.teiid.olingo.web.ODataServlet.service(ODataServlet.java:44) [teiid-olingo-8.12.0.Final.jar:8.12.0.Final]

                at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.2.Final-redhat-1.jar:1.0.2.Final-redhat-1]

                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.teiid.olingo.web.ODataFilter.doFilter(ODataFilter.java:179) [teiid-olingo-8.12.0.Final.jar:8.12.0.Final]

                at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:512) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169) [jboss-as-web-7.5.0.Final-redhat-15.jar:7.5.0.Final-redhat-15]

                at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926) [jbossweb-7.5.3.Final.jar:7.5.3.Final]

                at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_65]

               

              Can you help me?

               

              Regards.

              • 4. Re: Disable System Schemas for oData access
                Ramesh Reddy Master

                in the file "<eap>/modules/system/layers/dv/org/apache/olingo/main/module.xml" can you add the following in the dependencies and restart the server?

                 

                    <module name="com.fasterxml.aalto-xml"/>
                

                 

                Ramesh..

                1 of 1 people found this helpful
                • 5. Re: Disable System Schemas for oData access
                  José Antonio Tarifa Lorenzo Newbie

                  Done and it's working, but now it seems queries and updates are fine, but not when I update or create a record.

                   

                  17:55:06,270 INFO  [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (http-/0.0.0.0:8080-1) WkOlPGbmAemQ IJ000311: Throwable from unregister connection: java.lang.IllegalStateException: Trying to return an unknown connection2! org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6@4767c283

                    at org.jboss.jca.core.connectionmanager.ccm.CachedConnectionManagerImpl.unregisterConnection(CachedConnectionManagerImpl.java:347)

                    at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.connectionClosed(TxConnectionListener.java:460)

                    at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.closeHandle(BaseWrapperManagedConnection.java:584)

                    at org.jboss.jca.adapters.jdbc.WrappedConnection.close(WrappedConnection.java:265)

                    at org.teiid.translator.jdbc.JDBCExecutionFactory.closeConnection(JDBCExecutionFactory.java:281) [translator-jdbc-8.12.0.Final.jar:8.12.0.Final]

                    at org.teiid.translator.jdbc.JDBCExecutionFactory.closeConnection(JDBCExecutionFactory.java:68) [translator-jdbc-8.12.0.Final.jar:8.12.0.Final]

                    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.close(ConnectorWorkItem.java:253)

                    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_65]

                    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_65]

                    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_65]

                    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_65]

                    at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

                    at com.sun.proxy.$Proxy48.close(Unknown Source)

                    at org.teiid.dqp.internal.process.DataTierTupleSource.fullyCloseSource(DataTierTupleSource.java:327)

                    at org.teiid.dqp.internal.process.DataTierTupleSource.closeSource(DataTierTupleSource.java:357)

                    at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:395)

                    at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:278)

                    at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

                    at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

                    at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

                    at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

                    at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

                    at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:462)

                    at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:344)

                    at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

                    at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:271)

                    at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:306)

                    at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:238)

                    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_65]

                    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_65]

                    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_65]

                    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_65]

                    at org.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:121) [teiid-api-8.12.0.Final.jar:8.12.0.Final]

                    at org.teiid.jboss.TransportService$2.invoke(TransportService.java:241)

                    at com.sun.proxy.$Proxy20.executeRequest(Unknown Source)

                    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_65]

                    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_65]

                    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_65]

                    at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_65]

                    at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:180)

                    at java.util.concurrent.FutureTask.run(FutureTask.java:262) [rt.jar:1.7.0_65]

                    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

                    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)

                    at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:178)

                    at com.sun.proxy.$Proxy20.executeRequest(Unknown Source)

                    at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:670)

                    at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:536)

                    at org.teiid.jdbc.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:260)

                    at org.teiid.jdbc.PreparedStatementImpl.executeQuery(PreparedStatementImpl.java:73)

                    at org.teiid.olingo.service.LocalClient.executeSQL(LocalClient.java:229)

                    at org.teiid.olingo.service.TeiidServiceHandler.createEntity(TeiidServiceHandler.java:452)

                    at org.apache.olingo.server.core.requests.DataRequest$EntityRequest.execute(DataRequest.java:331)

                    at org.apache.olingo.server.core.requests.DataRequest.execute(DataRequest.java:247)

                    at org.apache.olingo.server.core.ServiceDispatcher.execute(ServiceDispatcher.java:117)

                    at org.apache.olingo.server.core.OData4HttpHandler.process(OData4HttpHandler.java:65)

                    at org.teiid.olingo.web.ODataServlet.service(ODataServlet.java:44)

                    at javax.servlet.http.HttpServlet.service(HttpServlet.java:847)

                    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)

                    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)

                    at org.teiid.olingo.web.ODataFilter.doFilter(ODataFilter.java:179)

                    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)

                    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)

                    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231)

                    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)

                    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:512)

                    at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)

                    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:150)

                    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)

                    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)

                    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:344)

                    at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:854)

                    at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:653)

                    at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926)

                    at java.lang.Thread.run(Thread.java:745) [rt.jar:1.7.0_65]

                   

                  As I said in my first post, I'm evaluating the solution to integrate Salesforce with on-premise JDBC database, so with further testing, I have other problem with filters.  Anyway, thank you very much for your help.

                   

                  This query for a listing, done by Salesforce, fails:

                  http://82.223.168.116:443/odata4/Portfolio/accounts/CUSTOMER?%24top=201&%24filter=%20indexof%28FIRSTNAME%2C%27J%27%29%20ne%20-1&%24orderby=SSN&%24count=true&%24select=FIRSTNAME%2CSSN

                   

                  but if I remove the space after filter=, it works:

                  http://82.223.168.116:443/odata4/Portfolio/accounts/CUSTOMER?%24top=201&%24filter=indexof%28FIRSTNAME%2C%27J%27%29%20ne%20-1&%24orderby=SSN&%24count=true&%24select=FIRSTNAME%2CSSN

                   

                  It's a bug o there is a configuration way to override the problem?

                   

                  Regards.

                  • 6. Re: Disable System Schemas for oData access
                    Ramesh Reddy Master

                    What is the error you are seeing with "$filter" ? I think there was a additional space in your URL, which is in-correct. try

                     

                    http://82.223.168.116:443/odata4/Portfolio/accounts/CUSTOMER?$top=201&$filter=indexof(FIRSTNAME,'J') ne -1&$orderby=SSN&$count=true&$select=FIRSTNAME,SSN
                    

                     

                    Still looking for the reason for the exception during the update

                     

                    Ramesh..

                    • 7. Re: Disable System Schemas for oData access
                      José Antonio Tarifa Lorenzo Newbie

                      The error was:

                       

                      {"error":{"code":null,"message":"The URI is malformed."}}
                      
                      

                       

                      So putting a space between 'filter=' and 'indexof' is wrong in the URL format?  I was reading the conventions of oData4 (OData Version 4.0 Part 2: URL Conventions Plus Errata 02) and I didn't see anything about permitting spaces or not, so I guessed it was correct.

                       

                      If you consider that doing a trim on the filter value is worthless, because is not standard, then the error will be related to Salesforce, because that query is automatically generated by their system.  IMHO coding the trim on the filter value would remove problems with integrations like ours with Salesforce, so I beg to you to consider do the trim in the code.

                       

                      thank you Ramesh.

                       

                      Regards

                      • 8. Re: Disable System Schemas for oData access
                        Ramesh Reddy Master

                        The code is above Teiid in Olingo framework, so change needs to be done there. The OData V4 ABNF rules are here http://docs.oasis-open.org/odata/odata/v4.0/errata02/os/complete/abnf/odata-abnf-construction-rules.txt

                         

                        I am not sure it is legal URL syntax or not, I would have to do some research. I will aslo ask in Olingo Developer forums.

                         

                        What interface from Salesforce generating this URL?

                         

                        Ramesh..