To homepage To homepage
Solved

Adding filter for one table in OData connector

J
Rank
Badge

Hi,

I'm using the CData ADO.NET Provider for OData 2022 (22.0.8322.0) to extract Topdesk data.

Due to performance reasons Topdesk made some changes in their feed. Resulting in that, since 19th june 2023, 2 larger tables (IncidentDurationBlocks and IncidentSnapshots) cannot longer be extracted without filtering on a date to make the dataset smaller.

How can I add a filter for only one table in my extract?

Can I create an OData RSD file?

I get this error when I try to extract:

System.Data.CData.OData.ODataException (0x80004005): [500] Could not execute the specified command: Unexpected response status code: 503.  ---> rds220I.IC: Unexpected response status code: 503

icon

Best answer by Thomas Lind 4 July 2023, 12:51

View original

17 replies

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @juriaan.hensbroek 

I think you can add a filter like you would if it was a SQL table.

CData has this written about how this works with client side processing. cdn.cdata.com/help/RDH/ado/pg_advancedqueryproc.htm

Depending on how you set up the OData data source it will just give a bunch of tables when you synchronize. Each one will have an option to filter on it then.

For this issue you could maybe use incremental load mixed with history. Then you will not loose the data if you had to do a full load. You could set up a filter that only gives a years worth of data and then update it until you got all of it. Then following that using incremental load would only add new additions from these tables.

J
Rank
Badge

Hi Thomas,

The filter seems to be required in the connection string, Topdesk otherwise blocks the connection to the table. All other tables I extract work just perfect, but this is something new they did for 1 big table. If I use filtering on the existing connection I still get the error I mentioned. There is no option in the connector for advanced query processing or filtering per table. Connector needs to be created first before I can filter like with SQL.

So what I did now is creating a new connection, just for this one table, with a filter included in the url.

Basic url: https://sakura.topdesk.net/services/reporting/v2/odata

New url with filter: https://sakura.topdesk.net/services/reporting/v2/odata/IncidentDurationBlocks?$filter=start gt 2023-06-26T00:00:00Z

Now I get another error message. The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible. I'm checking this with Topdesk. Or do you recognize this error?

Thomas Lind
Rank
Userlevel 6
Badge +5

Not really, maybe something about html encoding.

J
Rank
Badge

Topdesk doesn't recognize the error and say they don't support external applications.

In the meantime I see I can retrieve data in Postman with the same url (including filter in url). Should be possible to get it working in TX right? 

In Postman I have a base url: https://sakura.topdesk.net/services/reporting/v2/odata/IncidentDurationBlocks

query params: key=filter value=start gt 2023-06-26T00:00:00Z

Use Basic authentication

Resulting in url: https://sakura.topdesk.net/services/reporting/v2/odata/IncidentDurationBlocks?$filter=start gt 2023-06-26T00:00:00Z

Where can I add the query params in the connector in TX? I keep getting the same error: The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible.

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @juriaan.hensbroek

You can add URL parameters in the Custom Url Params field.

Add this to the field to get the same.

filter=start gt 2023-06-26T00:00:00Z

J
Rank
Badge

Tried this already. But the same error: The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible. Could it be something with the datatype or date format?

I tried to load it in PowerBI. This works.

J
Rank
Badge

Would this option do something? It refers to the edm.datetime.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @juriaan.hensbroek 

Well Microsoft does not have a Time Offset option, but you can set a reset in the Other field like 

ConvertDateTimeToGMT=True

or try to do it in the field you showed with CET as the value representing Central European Time.

Maybe you even have to do both.

I would also consider turning on the Use Client Side Paging and Support Formulas options.

J
Rank
Badge

I changed it but still the same error. I also tried a boolean filter instead of a date filter. This also gave an error “Edm.Boolean” not compatible (instead of boolean AND datetime).

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @juriaan.hensbroek 

Did you try the Server Time Zone field?

It should be attempted both with and without the Other option set I think.

If it makes no difference either way.

Try to see if I could convert these data types to strings using the data type override feature.

J
Rank
Badge

Ok, I tried Server Time Zone with value UTC+2 and GMT with and without the Other field filled with ConvertDateTimeToGMT=True. This didn't do anything, still the same error.

I also added data type override to datetime and filter rows like I would do with a normal ODX connection. Looks like these overrides only is looked at after the connection is made (first a sync is required and this fails already). 

J
Rank
Badge

Just had a word with my colleague Rory Smith. He says it's notable TX doesn't generate logfiles while logging is on Verbose 3. So it's not an issue between DataSource and CData, but between CData and TimeXtender.

Could it be a bug in TX/CData OData connector that the Custom Url Params with value $filter=start gt 2023-06-26T00:00:00Z can't interpret the “gt” as a > (greater than) evaluation? This could be causing the error we get: The types 'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible. This because the field “start” is a datetime field and with “greater than” it should be evaluated as a boolean.

Do you have experience with this OData connector in which this filter function succesfully operates? 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @juriaan.hensbroek 

It should not matter it is not our program that requires this but the API.

If you add

$filter=start 2023-06-26

and then if it does not work.

$filter=start 2023-06-26T00:00:00Z

and then this.

$filter=start%202023-06-26T00:00:00Z

Is there a way to run it without the filter and does that work?

 

J
Rank
Badge

Hi Thomas,

Tried these 3 suggestions, but all give a  'Edm.Boolean’ and 'Edm.DateTimeOffset’ are not compatible error.

It's not possible to run the api for this table without a filter, because it's actively refused by the source. Then I get a 500 error - the URI is malformed. Or 503 error - unexpected response.

Something I just tried in Postman is to run the table without a filter. It seems to give results also without the filter. I made a screenshot of the headers I got. I see a date in GMT timezone. In TX using the same URL without a filter, the sync and transfer both fail with a 500 error - URI is malformed.

 

Thomas Lind
Rank
Userlevel 6
Badge +5

Hi @juriaan.hensbroek 

Can you try to connect with a normal REST provider instead? It should be possible to connect to the URI since you can do it with postman.

If you get the same error with that, can you try to see if you can generate a RSD file for this. With this you can overwrite the fields data types which should make the fields become whatever data type you want.

J
Rank
Badge

Hi Thomas,

Good news. Changing for this table to a REST API works. We will see for how long it will work, because we don't use any filtering now while we are informed by Topdesk this is obligatory for retrieving data.

Thanks for all your efforts Thomas, it's appreciated!

Kind regards, Juriaan

Thomas Lind
Rank
Userlevel 6
Badge +5

@juriaan.hensbroek 

The REST provider should be better at handling filtering, you can even use the RSD file to make it dynamic.

 

Reply


Terms and ConditionsCookie settings