We have a REST API where we use both Dynamic Value and Pagination (replace URL).
The issue is that the paging works fine but only considers the first Dynamic Value and skips the rest. The final result then returns all pages for the first dynamic value and no rows for the rest. Further we also use Table Flattening as well as the Exhaustive Metadata Scan Option.
How can we make it loop through all dynamic values as well as keeping the paging option?
API request:
https://{base_url}/{company_url}
Paging:
Replace URL with {nextLink} type XPath
Dynamic Value query:
SELECT company_url FROM MDW_Dev].WSysTransform].sCompany_information]
Page 1 / 1
Hi @Leo Rogeman
How have you set up pagination of this?
Did you see my
guide?
It also uses a link in the meta data.
Hi Thomas!
I am a co-worker to Leo answering on his behalf.
We have set up the pagination of this according to the guide you’ve linked, see picture below:
What differs from the guide is that while the guide has one endpoint which isn’t dynamic (users) and others which are, we only have one endpoint which is dynamic. We store the dynamic values in a sql table and fetch them with the Dynamic Value query Leo included in his post.
Hi @Pontus Olausson
I wonder if it is the specific way the dynamic query is applied that may not take the pagination part into account.
How is the nextlink url different compared to the company_url?
Hi,
The first API request we send is to the endpoint: https://{base_url}/{dynamic_value}/endpoint
({dynamic_value} here is {company_url} in Leo’s post)
Hi @Pontus Olausson
There is not a way for your setup to make getting the dynamic value from another endpoint, like I do here in my graph setup?
This setup does both add a dynamic value and run each of these with the pagination options set.
The only difference I see on this compared to yours is that I use From Endpoint Table instead of SQL query.
The dynamic values are unfortunately not available from the API, and we can’t easily add these to an endpoint. Right now the dynamic values are loaded into TimeXtender through a separate TimeXtender Excel Data Source, and then fetchet through the Dynamic Value query that Leo included in his post.
When I disable the paging for this data source, the dynamic values works as expected. It is only the combination of paging and dynamic value queries that seems to be malfunctioning.
Hi @Pontus Olausson
It should be possible to replicate this behavior with my Graph API setup. I will try it out and let you know about it.
Hi @Thomas Lind
Some more detailed context:
The API we use is Dynamics 365 Business Central, with a custom endpoint https://base_url/{company_url}. The {company_url} is a dynamic value fetched through Dynamic Value query (SELECT company_url FROM MMDW_Dev].]SysTransform].]Company_information]).
With pagination disabled, this works as expected.
The API includes a @odata.nextLink in responses which has the format “https://base_url/{company_url}?$skiptoken=xxxx”.
When we set up pagination where we extract this @odata.nextLink through xpath and use the “Replace URL” parameter action (as shown in the picture in an earlier reply), we do get pagination to work as expected, but now only the first dynamic value is fetched.
After trying some more, I’ve managed to find that if we instead configure pagination to use the “Add as query parameter” parameter action, both pagination and dynamic values seems to work as expected. The issue above therefore seems to be isolated to the “Replace URL” parameter action.
To use this approach I would have to extract the $skiptoken value from the @odata.nextLink parameter and then add this as a query parameter, but here I run into another problem. When trying to use the @odata.nextLink in an SQL query in order to extract the $skiptoken value using the configuration below, I can no longer connect to the data source.
Error message when testing connection using the above pagination configuration:
Failed to execute endpoint 'Redovisningstransaktioner_Excel': Incorrect syntax near '/'. The label 'https' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'https' has already been declared. Label names must be unique within a query batch or stored procedure.
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '/'. The label 'https' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'https' has already been declared. Label names must be unique within a query batch or stored procedure. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTablet] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTablet] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at TimeXtender.ODX.RestEngine.RestEngineHelper.ExecuteQueryOnTarget(InputDataExecutionTarget target, String query, String resolvedConnectionString) at TimeXtender.ODX.RestEngine.DefaultPaginationHandler.HasNextPage(Endpoint endpoint, PaginationConfiguration paginationConfiguration, RequestSettings requestSettings, HttpResponseMessage responseMessage, Dictionary`2 dynamicValues, XmlDocument xmlDocument, String filePathWrittenTo, Int32 currentPaginationPage, String connectionString) at TimeXtender.ODX.RestEngine.RestEngine.HandlePagination(ExecutionConfigurationContainer configContainer, HttpResponseMessage httpResponseMessage, Dictionary`2 dynamicValues, XmlDocument xmlDocument, String filePathWrittenTo) at TimeXtender.ODX.RestEngine.RestEngine.ExecutePaginationLoop(ExecutionConfigurationContainer configContainer, CallbackObject callbackObject, Dictionary`2 dynamicValues) at TimeXtender.ODX.RestEngine.RestEngine.ExecuteDynamicValuesLoop(ExecutionConfigurationContainer configContainer, CallbackObject callbackObject) at TimeXtender.ODX.RestEngine.RestEngine.<>c__DisplayClass25_0.<CreateExecutionTask>b__0() ClientConnectionId:a566b86c-d504-434c-aecc-543ba1c36a81 Error Number:102,State:1,Class:15
One possible source to this error is that the {company_url} part in the endpoint (and in the @odata.nextLink) contains apostrophes, with the format “Company(‘xxxx’)”.
So it seems we have two different issues here:
Dynamic Values seems to stop working when using pagination with the “Replace URL” parameter action
We can’t use the parameter @odata.nextLink in an SQL Expression, possibly due to it containing apostrophes
Any help is appreciated!
Hi @Pontus Olausson
I have made an internal case for the issue about mixing dynamic options from a SQL query compared to a endpoint table.
I am not sure why you need that script to pull out the value. Doesn’t this API work the same way as the Graph API. I can connect to the @Odata.nextLink with no issues.
Hi @Thomas Lind
Pulling out the value of $skiptoken from the @odata.nextLink was just an attempt to be able to use the “Add as query parameter” option instead of the “Replace URL” option in the pagination, thereby bypassing the issues with combining “Replace URL” with dynamic values (since that problem only seem to arise on my end when “Replace URL” is used).
If we get the original approach to work there would be no need to pull out the value.
I’m thankful for the help!
Hi @Pontus Olausson
OK, good. I was pretty sure I got what the issue was, but I wanted to be sure it wasn’t the current issue.
