Disable System Schemas for oData access

[jatarifa]

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.

[shawkins]

> 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?

[rareddy]

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..

[jatarifa]

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.

[rareddy]

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..

[jatarifa]

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.

[rareddy]

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..

[jatarifa]

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

[rareddy]

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..

[rareddy]

Reference to the Olingo forum question here http://markmail.org/search/list:org.apache.olingo.dev#query:list%3Aorg.apache.olingo.dev+page:1+mid:5mhfjpdihwtq7zou+sta…

[rareddy]

Logged [OLINGO-846] Flexible URL parsing for System Query Options - ASF JIRA