Paging and Dynamic value REST (Rest ODX)

Hi @Leo Rogeman 

How have you set up pagination of this?

Did you see my 

• Use OAuth 2.0 client authentication to connect to Graph API with the TimeXtender REST data source Tutorial

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

 The nextlink url we get is as follows: https://{base_url}/{dynamic_value}/endpoint?aid=FIN&$skiptoken=820085

({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 [MDW_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, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] 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:

1. Dynamic Values seems to stop working when using pagination with the “Replace URL” parameter action
2. 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.

Hi @Thomas Lind 

Has there been any updates on this?

Hi @Pontus Olausson 

Unfortunately not, I have asked for updates.

I got one question though, how does the result of the SQL query look?

Or is it shown a different way?

Also when you look at the returned data does the nextLink url contain the dynamic value in the name?

Hi @Thomas Lind 

1. Yes, the result of the dynamic value sql query looks just like that. One thing to add though is that the company1_url is only the dynamic part of the endpoint url, which would look like {base_url}/{company1_url}.

2. Yes, the nextLink url is returned as {base_url}/{company1_url}?$skiptoken=xxxx (with the actual value for base_url and company1_url).

Hi @Pontus Olausson 

I got a response from the developers.

This is a bug, nice find. We will correct it during the next build cycle.

To fix the SQL query they have, they need to add ''. The {nextLink} will just return the value, it will not encase it in quotes.

So the correct sql would be:

select SUBSTRING(
'{nextLink}',
CHARINDEX('$skiptoken=', '{nextLink}') + LEN('$skiptoken='),
LEN('{nextLink}') - charindex('$skiptoken=', '{nextLink}') - LEN('$skiptoken=') + 1
) as _skiptoken

Can you try it out and confirm if it works.

The bug that is being fixed, I assume, is still the first thing, but this is a workaround for now.

Hi @Thomas Lind ,

Thanks for the confirmation.

Regarding the workaround, I’ve tried adding quotes around {nextLink} according to your response and it did solve the error message I included in my earlier response, but now I get another error:

Failed to execute endpoint 'Redovisningstransaktioner_Excel': Incorrect syntax near '%'. System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '%'.

For reference, the actual {nextLink} value will be {base_url}/Country/ODataV4/Company(‘Company%20Name%20Country%’)?$skiptoken=xxxx (italics are different for each dynamic value). The important part here is that the value includes apostrophes inside the parenthesis.

If I insert the actual value of {nextLink} into the suggested query (as per the code below) and try to run it in SSMS, I get the same error message (Incorrect syntax near ‘%’).

select SUBSTRING(
	'Country/ODataV4/Company('Company%20Name%20Country')?$skiptoken=12',
	CHARINDEX('$skiptoken=', 'Country/ODataV4/Company('Company%20Name%20Country')?$skiptoken=12')?$skiptoken=12') + LEN('$skiptoken='),
	LEN('Country/ODataV4/Company('Company%20Name%20Country')?$skiptoken=12') - charindex('$skiptoken=', 'Country/ODataV4/Company('Company%20Name%20Country')?$skiptoken=12') - LEN('$skiptoken=') + 1
) as _skiptoken

Could it be possible that the SQL Expression isn’t run in a parameterized fashion and the apostrophes in the parameter value therefore aren’t properly escaped? 

Hi @Pontus Olausson did you get a chance to try the above?

Hi @Christian Hauggaard 

Including “SET QUOTED_IDENTIFIER OFF” did indeed solve this issue, and I am now able to use pagination with query parameters alongside dynamic values correctly. This works as a workaround for us until pagination with replace URL and dynamic values works correctly (which would be a slighly more clean solution).

Thanks for your help!