Expose Odata feed via API gateway for Excel consumption

[nick_lomax24 (Nick Lomax)]

Product/components used and version/fix level:

API Gateway Advanced Edition 11.0.0.0.2185 (Free forever edition)

Detailed explanation of the problem:

I am looking to expose the Microsoft Odata sample feed via an API gateway and consume the Odata feed using excel. This is to demonstrate what logging / processing I can do to an odata feed in the API gateway.

I am using northwind as my sample odata feed.

https://services.odata.org/northwind/northwind.svc

I am using Excel 2016 to preview and load an entity from the odata feed this works fine.

When I expose the odata feed via my API gateway in the cloud, I get a 406 error (Not acceptable)

Details: “OData: Request failed: The remote server returned an error: (406) Not Acceptable. (API Gateway encountered an error. Error Message: Not acceptable. Request Details: Service - northwind.svc, Operation - null, Invocation Time:10:24:46 AM, Date:Jun 6, 2024, Client IP - 80.x.x.x, User - Default and Application:null)”

When I run fiddler, to look at what Excel is sending the API gateway and I can see excel sends 3 request. The first 2 are successful. These are both requests for json.

The third, I see the 406 error

This is the header

GET /gateway/northwind.svc/1 HTTP/1.1
User-Agent: Microsoft.Data.Mashup (Power Query documentation - Power Query | Microsoft Learn)
MaxDataServiceVersion: 3.0
Accept: application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7
Accept-Encoding: gzip, deflate
Host: envxxxxxx.apigw-aw-eu.webmethods.io

When I use postman to emulate it, it appears that the API gateway will not accept this format. Should API gateway accept the format request? application/xml. This appears to be a valid Odata request and the backend sample odata feed will accept it.

So it works with application/json:

But not with application/xml

Also in the output, I see

"odata.metadata": "https://services.odata.org/northwind/Northwind.svc/$metadata",

I think this should need to be transformed to http://envxxxxxx.apigw-aw-eu.webmethods.io/gateway/northwind.svc/1/$metadata.

I have seen other threads suggest to use Response transformation to call another service, however this does not appear to be an option for the odata policy in API gateway

Compared, to a non odata API policy, where there is an option of a response transformation.

Error messages / full error message screenshot / log file:

Question related to a free trial, or to a production (customer) instance?

This is using the free trial. But looking to implement in a production customer instance, where they are looking to expose an odata feed via an API gateway, to use in Excel.

[reamon]

@nick_lomax24

This appears to be a valid Odata request and the backend sample odata feed will accept it.

Can you confirm that by checking the “native response” returned by the OData server?

[nick_lomax24 (Nick Lomax)]

Hi Reamon,

Here’s the native success from postman response when requesting ‘Accept application/xml’

Also when emulating Excel’s header

Accept application/atomsvc+xml;q=0.8,application/atom+xml;q=0.8,application/xml;q=0.7,text/plain;q=0.7

Many thanks,

Nick

[reamon]

Thanks for that. But what do the API GW logs show when recording the native request and response? I’m trying to confirm: 1) which system is complaining (the 406) and; 2) is API GW not forwarding something it needs to forward.

[reamon]

@nick_lomax24

I think this should need to be transformed to http://envxxxxxx.apigw-aw-eu.webmethods.io/gateway/northwind.svc/1/$metadata.

Yes, URL rewriting in response bodies is one of the fun side-effects of proxying such calls. One approach to consider is to route to an IS service (the IS hosting API GW or a separate IS) instead, call the OData server from there, and do the URL rewriting in IS where there is more flexibility in modifying the payload. Beware of trying to do that at the string level – parse the response string to an IS document, modify, then recreate the string to be returned to the caller.

[nick_lomax24 (Nick Lomax)]

requests.zip (1.5 KB)

Attached is a request trace. From the GUI, I just see the incoming request being rejected, and no native call.

As if the API gateway does not want to offer an application/xml format.

Many thanks,

Nick

[reamon]

Thanks for sharing great info!

This is indeed odd and I agree that it appears that API GW is making assumptions about supported content types for OData. I don’t see a setting in the API config that would control this. Nor extended settings. If you can, perhaps contacting support is appropriate here.

[nick_lomax24 (Nick Lomax)]

Hi Reamon,

Part of the idea is to expose an SQL database as an Odata service. Then expose the new Odata service through an API gateway. So 2 different Integration servers.

When doing it for the end client, they will have SoftwareAg support so will raise the issue with handling the Accept Application/XML header.

However, as I will be in developing in IS, I was trying to see if I could update the URLs in the response body in the IS server that calls the database and generates the Odata response.

I am updating the retrieve flow, which gets called when it does a get on the Odata resource and I can see the data come back in the form of the document in the pipeline, but I can’t see where I can update the url. IS just seems to convert it do the format required by the request, with the extra Urls.

If I was to do the transformation as an a service being called out in the API Gateway, I think I would need the API Gateway package in my service designer. Is that a matter of just copying the package the IS in my designer from the API gateway runtime.

My trial Service designer does not have that package.

I looked online for my webMethods IO, and this does not seem to have the API gateway IS Response Specification too.

Many thanks for your help,

Nick

[reamon]

@nick_lomax24

update the URLs in the response body in the IS server that calls the database and generates the Odata response.

Cool, so you’re already configuring API GW to route the call to the IS-hosted service. Perfect.

@nick_lomax24

data come back in the form of the document in the pipeline, but I can’t see where I can update the url. IS just seems to convert it do the format required by the request, with the extra Urls.

I have not used OData with IS so do not have the background. But it seems odd that the document would have “extra” elements added to it later. Can you show the other tabs of the _retrieve service? Tree and Input/Output. (I find the Layout view most useless – good for demos of the tool but not much else.)

@nick_lomax24

to do the transformation as an a service being called out in the API Gateway,

Per your post earlier, API GW does not support that. So can’t do it as a transform on the response. And if you could, that would run on the API GW IS, not the IS you have the OData services hosted.

@nick_lomax24

I think I would need the API Gateway package in my service designer. Is that a matter of just copying the package the IS in my designer from the API gateway runtime.

No, don’t do that. No need. If you need to do a callout to an IS Service within GW policy, that will be on the API GW IS, not any other. That is the only place where the IS Response Specification should be. So with Designer, you’d connect to the API GW IS and define the callout service(s) there.

[nick_lomax24 (Nick Lomax)]

Hi Reamon,

So here’s the input/output tab for the _retrieve service.

Here’s the tree tab

Here’s the output from postman. You can see the structure of the xml returned

Below is the debug of the pipeline at the end of the service. IS looks like it transforming the IS document in accordance with what type of message the request asked for.

Many thanks,

Nick

Iserver console output for request.txt (7.4 KB)

[reamon]

Seems that the input/output tab was posted twice. Can you post the Tree tab showing the FLOW steps?

The Postman output does not match what is shown in pipeline screen shot. Pipeline shows an array, the Postman output shows a single entry – and what look to be “envelope” type fields.

When you’re running the service directly in Designer, how are you doing that? The difference in what you’re seeing may be due to the differences in how the invocation is done – call via HTTP has headers and such… Running the service in Designer does not, unless you created a wrapper test service to mimic what an HTTP call would provide.

For example, in the log you shared, the Accept header was shown as /, not the atom type you show in the Postman request.

Since I’ve never used IS to support OData, my guesses likely are not all that helpful but I’m hoping some of this will be useful.

Side trip: beware sharing the server log without masking sensitive info such as passwords or basic auth headers.