9 Replies Latest reply on Nov 28, 2018 9:09 AM by Marc Kusters

    Consumed webservice not showing all records

    Marc Kusters Newbie

      I consumed an OData webservice using invokeHTTP and then the procedure to get the results. This works fine, however I only get 1000 results.

      When I manually curl the webservice I get 1000 records and at the end a skiptoken.

       

      System configuration:

      JBoss Datavirtualization 6.3.8 with EAP 6.4.9.

       

      Example of procedure:

      BEGIN
          DECLARE
              STRING VARIABLES.queryString = QUERYSTRING('TableName');
              SELECT
                     parsetimestamp((A."d:startDate"
                            || '.0000'), 'yyyy-MM-dd''T''HH:mm:ss.SSSS') AS startDate
                   , A."d:userId"                                        AS userId
                   , convert(A."d:seqNumber", long)                      AS seqNumber
                   , parsetimestamp((A."d:endDate"
                            || '.0000'), 'yyyy-MM-dd''T''HH:mm:ss.SSSS')                                               AS endDate
                   , parsetimestamp(REPLACE(A."d:lastModifiedDateTime", 'Z', '.0000'), 'yyyy-MM-dd''T''HH:mm:ss.SSSS') AS lastModifiedDateTime
                   , parsetimestamp((A."d:customDate26"
                            || '.0000'), 'yyyy-MM-dd''T''HH:mm:ss.SSSS') AS customDate26
              FROM
                     (EXEC SAPSF_DSL.invokeHttp(action => 'GET', endpoint => VARIABLES.queryString, stream => 'TRUE')) AS f
                   , XMLTABLE(XMLNAMESPACES(DEFAULT 'http://www.w3.org/2005/Atom', 'http://schemas.microsoft.com/ado/2007/08/dataservices/metadata' AS m, 
      'http://schemas.microsoft.com/ado/2007/08/dataservices' AS d, 'https://urltowebserivce.com/odata/v2/' AS base), 
      '/feed/entry/content/m:properties' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS "d:startDate" string PATH 'd:startDate/text()', 
      "d:userId" string PATH 'd:userId/text()', "d:seqNumber" string PATH 'd:seqNumber/text()', "d:endDate" string PATH 'd:endDate/text()', 
      "d:lastModifiedDateTime" string PATH 'd:lastModifiedDateTime/text()', "d:customDate26" string PATH 'd:customDate26/text()') AS A
              ;
          
          END
      

       

      I Added some returns for better formatting. The OData URL is from SAP succesfactors.

       

      Example of skiptoken and elements surrounding it:

      </entry>
      <link rel="next" href="https://urltowebserivce.com/odata/v2/TableName/?$skiptoken=eyJzdGFydFJvdyI6MTAwMCwiZW5kUm93IjoyMDAwfQ%3D%3D"/>
      </feed>
      

       

      Resource adapter config:

      <resource-adapter id="SAPSF_DS">
                          <module slot="main" id="org.jboss.teiid.resource-adapter.webservice"/>
                          <transaction-support>NoTransaction</transaction-support>
                          <connection-definitions>
                              <connection-definition class-name="org.teiid.resource.adapter.ws.WSManagedConnectionFactory" jndi-name="java:/SAPSF_DS" enabled="true" use-java-context="true" pool-name="SAPSF_DS">
                                  <config-property name="RequestTimeout">
                                      30000
                                  </config-property>
                                  <config-property name="SecurityType">
                                      HTTPBasic
                                  </config-property>
                                  <config-property name="AuthPassword">
                                      password
                                  </config-property>
                                  <config-property name="EndPoint">
                                      https://urltowebserivce.com/odata/v2/
                                  </config-property>
                                  <config-property name="AuthUserName">
                                      username
                                  </config-property>
                              </connection-definition>
                          </connection-definitions>
                      </resource-adapter>
      

       

       

      Somehow Teiid doesn't pick up the skiptoken. Any idea on how to solve this, is there a option I'm missing (which might be in the resource adapter, like transaction-support?).

        • 1. Re: Consumed webservice not showing all records
          Steven Hawkins Master

          > Somehow Teiid doesn't pick up the skiptoken. Any idea on how to solve this, is there a option I'm missing (which might be in the resource adapter, like transaction-support?).

           

          There is no expectation that the ws resource adapter / translator will handle the odata protocol.  You need to use an odata translator for that.

          • 2. Re: Consumed webservice not showing all records
            Marc Kusters Newbie

            Using the OData translator just makes it throw errors.

             

            My internal test with odata translator:

            Consuming server:

            2018-11-22T15:04:28.723 | WARN  | [org.teiid.CONNECTOR] | (Worker3_QueryProcessorQueue50) | Connector worker process failed for atomic-request=8eFX+8pisVHJ.9.3.5: org.teiid.translator.TranslatorException: TEIID17013 Wrong response from source with HTTP Response Code 500: Vendor Code "ServerErrorException": With message "Internal Server Error": Detail Error null
            2018-11-22T15:04:28.726 | WARN  | [org.teiid.PROCESSOR] | (Worker2_QueryProcessorQueue51) | TEIID30020 Processing exception for request 8eFX+8pisVHJ.9 'TEIID30504 SourceModel: TEIID17013 Wrong response from source with HTTP Response Code 500: Vendor Code "ServerErrorException": With message "Internal Server Error": Detail Error null'. Originally TeiidProcessingException BaseQueryExecution.java:196. Enable more detailed logging to see the entire stacktrace.
            

             

            Producing server:

            2018-11-22T15:04:28.718 | ERROR | [org.teiid.ODATA] | (http-127.0.0.1:8080-1) | TEIID16013 Error occurred producing OData result.: org.jboss.resteasy.spi.NotFoundException: RESTEASY001185: Could not find resource for relative : /PIMS_VBL.DIM_LOCATIE_TERREIN/invokeHttp of full path: https://myawesomehost.com/odata/Parking/PIMS_VBL.DIM_LOCATIE_TERREIN/invokeHttp?action=%27GET%27&request=NULL&endpoint=%27%27&stream=true&headers=NULL

             

            Test to external server with the odata translator (which does work fine with the ws translator but only shows 1000 records):

            2018-11-22T15:07:57.585 | WARN  | [org.teiid.CONNECTOR] | (Worker122_QueryProcessorQueue202512) | Connector worker process failed for atomic-request=k9wfAXtStkcP.7.3.63246: org.teiid.translator.TranslatorException: TEIID17013 Wrong response from source with HTTP Response Code 404: Vendor Code "NotFoundException": With message "EdmEntitySet invokeHttp is not found. Action: Please check at Admin Center-->Search OData API Data Dictionary.": Detail Error null
            2018-11-22T15:07:57.593 | WARN  | [org.teiid.PROCESSOR] | (Worker122_QueryProcessorQueue202512) | TEIID30020 Processing exception for request k9wfAXtStkcP.7 'TEIID30504 SAPSF_DSL: TEIID17013 Wrong response from source with HTTP Response Code 404: Vendor Code "NotFoundException": With message "EdmEntitySet invokeHttp is not found. Action: Please check at Admin Center-->Search OData API Data Dictionary.": Detail Error null'. Originally TeiidProcessingException BaseQueryExecution.java:196. Enable more detailed logging to see the entire stacktrace.
            2018-11-22T15:07:58.024 | WARN  | [org.teiid.CONNECTOR] | (Worker122_QueryProcessorQueue202513) | Connector worker process failed for atomic-request=k9wfAXtStkcP.8.3.63247: org.teiid.translator.TranslatorException: TEIID17013 Wrong response from source with HTTP Response Code 404: Vendor Code "NotFoundException": With message "EdmEntitySet invokeHttp is not found. Action: Please check at Admin Center-->Search OData API Data Dictionary.": Detail Error null
            2018-11-22T15:07:58.030 | WARN  | [org.teiid.PROCESSOR] | (Worker122_QueryProcessorQueue202513) | TEIID30020 Processing exception for request k9wfAXtStkcP.8 'TEIID30504 SAPSF_DSL: TEIID17013 Wrong response from source with HTTP Response Code 404: Vendor Code "NotFoundException": With message "EdmEntitySet invokeHttp is not found. Action: Please check at Admin Center-->Search OData API Data Dictionary.": Detail Error null'. Originally TeiidProcessingException BaseQueryExecution.java:196. Enable more detailed logging to see the entire stacktrace.
            2018-11-22T15:07:58.916 | WARN  | [org.teiid.CONNECTOR] | (Worker122_QueryProcessorQueue202514) | Connector worker process failed for atomic-request=k9wfAXtStkcP.9.3.63248: org.teiid.translator.TranslatorException: TEIID17013 Wrong response from source with HTTP Response Code 404: Vendor Code "NotFoundException": With message "EdmEntitySet invokeHttp is not found. Action: Please check at Admin Center-->Search OData API Data Dictionary.": Detail Error null
            2018-11-22T15:07:58.922 | WARN  | [org.teiid.PROCESSOR] | (Worker122_QueryProcessorQueue202514) | TEIID30020 Processing exception for request k9wfAXtStkcP.9 'TEIID30504 SAPSF_DSL: TEIID17013 Wrong response from source with HTTP Response Code 404: Vendor Code "NotFoundException": With message "EdmEntitySet invokeHttp is not found. Action: Please check at Admin Center-->Search OData API Data Dictionary.": Detail Error null'. Originally TeiidProcessingException BaseQueryExecution.java:196. Enable more detailed logging to see the entire stacktrace.
            
            • 3. Re: Consumed webservice not showing all records
              Ramesh Reddy Master

              Make sure to use the correct version of odata translator, there is V2 and V4.

               

              The above error didn't give me any context of what is wrong. May be detail log?

               

              Ramesh.

              • 4. Re: Consumed webservice not showing all records
                Marc Kusters Newbie

                The webservice (SAP SuccessFactors) we consume is OData 2.

                 

                I didn't post the full stacktrace; I will do that now . How detailed do you want the logging? Currently the rootlogger is set at INFO, I could set it to DEBUG or TRACE?

                • 5. Re: Consumed webservice not showing all records
                  Ramesh Reddy Master

                  TRACE is good, however, do not post them, but attach the log file. If it is too large just slim it down to related portions.

                  • 6. Re: Consumed webservice not showing all records
                    Marc Kusters Newbie

                    This is still INFO log. I did not yet have the time to set it to TRACE.

                    Do note that the part that says invokeHttp was in this case the TableName. I think the error remained the same (invokeHttp is the default if you create a webservice).

                     

                    URL to the logfile:

                    STACK

                    • 7. Re: Consumed webservice not showing all records
                      Steven Hawkins Master

                      It may help to validate your setup.

                       

                      > Consuming server:

                       

                      This is a Teiid / JDV server configured with the OData translator, which is pointed at the Producing Server?

                       

                      > Producing server:

                       

                      This is a Teiid / JDV server running an OData 2 service with a VDB that exposes the invokeHTTP procedure?

                       

                      > Test to external server with the odata translator (which does work fine with the ws translator but only shows 1000 records):

                       

                      Is this setup different than the producing server?

                       

                      In general I'm confused about the setup and the metadata.  Let's separate out talking about the OData v2 production and consumption.

                       

                      Given that you have an OData v2 service.  The consumption side in Teiid would be a vdb that specifies the odata2 translator and typically allows the full metadata import from the source - that is no metadata is specified in the vdb for that source model.  Is that what your consuming vdb looks like?

                       

                       

                       

                       

                      • 8. Re: Consumed webservice not showing all records
                        Marc Kusters Newbie

                        Well after I didn't succeed in consuming the SAP SuccessFactors OData2 I tried my own setup, that is where the confusion is from I think.

                        I rather focus only on the SAP part, so we could drop the other one (which was 2 teiid servers where one was exposing a odata v2 service and the other  was consuming as a vdb, this was tried using web service source (REST) import).

                         

                        The SAP one I consume indeed using a VDB where I set the translator to OData (which in this case would mean odata v2). Well normally I would use the tool in Teiid Designer to create a new webservice. However, in our case this is quite impossible, we can only reach SAP from our acceptance and production server. SAP is exposing HR data so guidelines are very strict, we can't reach the data on our development workplace. To circumvent this I download a sample of the data and metadata and created a webservice using that (which is like the example procedure I posted in the first post).

                         

                        But to see if the import would be different I tried importing the northwind odata webservice in teiid designer. This does not work, at least for the xml part. Consuming the json northwind odata webservice does work. This is using the latest teiid designer. Is there another online odata webservice that I could consume as a test? I tried both using the file source (XML) with a remote URL and the web service source importer.

                         

                         

                        So consuming the remote file source (XML) with the importer throws:

                        org.eclipse.datatools.connectivity.oda.OdaException ;
                            java.io.IOException: Server returned HTTP response code: 415 for URL: https://services.odata.org/Northwind/Northwind.svc/Employees
                        
                        
                        at org.eclipse.datatools.enablement.oda.xml.util.ResourceLocatorUtil.getInputStream(ResourceLocatorUtil.java:57)
                        
                        
                        at org.eclipse.datatools.enablement.oda.xml.util.XMLSourceFromPath.openInputStream(XMLSourceFromPath.java:43)
                        
                        
                        at org.teiid.designer.datatools.profiles.xml.XmlUrlProfilePropertyPage$XmlURLPingJob.testXmlUrlConnection(XmlUrlProfilePropertyPage.java:233)
                        
                        
                        at org.teiid.designer.datatools.profiles.xml.XmlUrlProfilePropertyPage$XmlURLPingJob.run(XmlUrlProfilePropertyPage.java:219)
                        
                        
                        at org.eclipse.core.internal.jobs.Worker.run(Worker.java:56)
                        
                        
                        Caused by: java.io.IOException: Server returned HTTP response code: 415 for URL: https://services.odata.org/Northwind/Northwind.svc/Employees
                        
                        
                        at sun.net.www.protocol.http.HttpURLConnection.getInputStream0(HttpURLConnection.java:1894)
                        
                        
                        at sun.net.www.protocol.http.HttpURLConnection.getInputStream(HttpURLConnection.java:1492)
                        
                        
                        at sun.net.www.protocol.https.HttpsURLConnectionImpl.getInputStream(HttpsURLConnectionImpl.java:263)
                        
                        
                        at java.net.URL.openStream(URL.java:1045)
                        
                        
                        at org.eclipse.datatools.enablement.oda.xml.util.ResourceLocatorUtil.getInputStream(ResourceLocatorUtil.java:49)
                        
                        
                        ... 4 more
                        

                         

                        Using the web service source (REST) importer, returns the following payload before while importing:

                        <?xml version="1.0" encoding="utf-8"?><m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code /><m:message xml:lang="en-US">Unsupported media type requested.</m:message><m:innererror><m:message>A supported MIME type could not be found that matches the acceptable MIME types for the request. The supported type(s) 'application/atom+xml;type=feed, application/atom+xml, application/json;odata=minimalmetadata;streaming=true, application/json;odata=minimalmetadata;streaming=false, application/json;odata=minimalmetadata, application/json;odata=fullmetadata;streaming=true, application/json;odata=fullmetadata;streaming=false, application/json;odata=fullmetadata, application/json;odata=nometadata;streaming=true, application/json;odata=nometadata;streaming=false, application/json;odata=nometadata, application/json;streaming=true, application/json;streaming=false, application/json;odata=verbose, application/json' do not match any of the acceptable MIME types 'application/xml'.</m:message><m:type>Microsoft.Data.OData.ODataContentTypeException</m:type><m:stacktrace>   at Microsoft.Data.OData.MediaTypeUtils.GetContentTypeFromSettings(ODataMessageWriterSettings settings, ODataPayloadKind payloadKind, MediaTypeResolver mediaTypeResolver, MediaType&amp; mediaType, Encoding&amp; encoding)&#xD;
                           at Microsoft.Data.OData.ODataMessageWriter.EnsureODataFormatAndContentType()&#xD;
                           at Microsoft.Data.OData.ODataUtils.SetHeadersForPayload(ODataMessageWriter messageWriter, ODataPayloadKind payloadKind)&#xD;
                           at System.Data.Services.ResponseContentTypeNegotiator.DetermineResponseFormat(ODataPayloadKind payloadKind, String acceptableMediaTypes, String acceptableCharSets)</m:stacktrace></m:innererror></m:error>
                        
                        • 9. Re: Consumed webservice not showing all records
                          Marc Kusters Newbie

                          I solved it.... the correct way to do it (in my case) was to use the Teiid Connection to the server and use the datasource as to import. Then select the odata translator. Reason I thought this didn't work was because there is a fault in the schema I tried to consume. To circumvent this I created an extra datasource with the table name in the URL and set the OData translator propterties to the correct values. These values are based on the metadata and the content location of the OData table.

                          My values in this case:

                          Entity Container Name: content  (this is basically the root of the content in the table)

                          Schema Name: SFOData (found in the metadata as <Schema Namespace="SFOData" ........./>

                           

                          Found a tutorial on which I based this on here:

                          Connect to an OData source