Consumed webservice not showing all records
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?).
