problems with POST call on REST API

[Anonymous]

Hi All

I'm  having issues getting a response from the server on a POST call to the REST API of interactive brokers (IB). I have now gotten the GET calls working (which had SSL/TLS errors previously), and I can get the "same" POST call working on Swagger, which is a config provided by IB.

The IB site that implements swagger UI for their API is https://interactivebrokers.github.io/cpwebapi/swagger-ui.html

Most calls to this API are GET, however some are post, and the example below is the one which I get an error on PQ withing Power BI. The error relates to the connection being close (which some experience suggests that the call was incorrect, and the connection gets instantly closed probably for security reasons).

On the swagger implemenation per above, the CURL rendered on the specific call is below:

curl -X POST "https://localhost:5000/v1/api/iserver/secdef/search" -H "accept: application/json" -H "Content-Type: application/json" -d "{ \"symbol\": \"BHP\", \"name\": true, \"secType\": \"string\"}".

The resources I have consulted are numerous, including this forum, and other direct searches from the Web. I have "trialled and errored" various combinations from BI Accountant: https://www.thebiccountant.com/2018/06/05/easy-post-requests-with-power-bi-and-power-query-using-jso...

and Chris Webb: https://blog.crossjoin.co.uk/2014/04/19/web-services-and-post-requests-in-power-query/ 

The OPEN FIGI example works (BI Accountant), on PBI, as does the jasonplaceholder and UK Data API (bth from Chris Webb's posts). 

My code:

(Symbol as text, SymbolType as text ) =>

let
url = "localhost:5000/v1/api/iserver/secdef/search",
//cookiestr = "get the value of bm_sv cookie from browser",
headers = [#"Content-Type" = "application/json"], //, Cookie = cookiestr],
body = Json.FromValue(
//body = Text.ToBinary(
[
symbol = Symbol,
name = true
//secType = SymbolType
//exchange = "ASX"
]
),
//Content = Text.ToBinary(body),
response = Web.Contents(
url,
[
//RelativePath = "iserver/secdef/search",
Headers = headers,
Content = body
]
),
jsonResponse = Json.Document(response)
in
jsonResponse

It gives the following error:

An error occurred in the ‘’ query. DataSource.Error: The underlying connection was closed: The connection was closed unexpectedly.
Details:
localhost:5000/v1/api/iserver/secdef/search

It may be a simple as something syntactic, but I can't work out the search term that may pop up the suggestion.

All help would be appreciated.

Kind regard

Rohan

[navneet0512]

let

{
  "methodType": "details",
  "pageNum": "0",
  "pageSize": "3",
  "searchParameter": [
    {
      "key": "order_number",
      "value": "469584336"
    },
    {
      "key": "global_bu_id",
      "value": "11"
    }
  ],
  "sourceApplication": "MORC"
}

Source = Json.Document(Web.Contents("https://goss-csvc-prod.us.MAC.com/gossv3/purchase/search/"))
in
Source

Can any one help me figure out issue.  Showing Syntax error I am trying to use post api.

[lbendlin]

POST requires a Content payload.

[navneet0512]

That means

[lbendlin]

Web.Contents - PowerQuery M | Microsoft Docs

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Try explicitly using HTTPS, your query currently is not.

https://localhost:5000/v1/api/iserver/secdef/search

Regards

Phil

[Anonymous]

Hi

Thanks for the previous help (I had done a search to see a list of valid header elements, but didn't find one which looked relevant, hence the querie on syntax).

Adding and therfore making the Headers -  ... [#"Content-Type" = "application/json", Accept = "application/json"] has not worked.

I am still getting the following error:

DataSource.Error: The underlying connection was closed: The connection was closed unexpectedly.
Details:
localhost:5000/v1/api/iserver/secdef/search

I modifiied my code to make it static (i.e. not a function in M):

let
url = "localhost:5000/v1/api/iserver/secdef/search",
//cookiestr = "get the value of bm_sv cookie from browser",
headers = [#"Content-Type" = "application/json", Accept = "application/json"], //, Cookie = cookiestr],
body = Json.FromValue(
//body = Text.ToBinary(
[
symbol = "BHP",
name = false,
secType = "STK"
//exchange = "ASX"
]
),
//Content = Text.ToBinary(body),
response = Web.Contents(
url,
[
//RelativePath = "iserver/secdef/search",
Headers = headers,
Content = body
]
),
jsonResponse = Json.Document(response)
in
jsonResponse

Are there any other paths that may yield some response ?

Cheers

RT

[lbendlin]

Apart from the the missing accept header it looks benign.  Care to try adding that?

[Anonymous]

Hi

I have placed a support call to the data provider. However they do not support nor comment on Power query (or end user platforms generally). So no success there at present.

I ran the diagnostics and got the following in hte detailed PQ analytics. What I note is that the Json record for headers and content is not displayed, and outside of creating another step to interrigate these records (which I have done and it looks fine), they don't appear in the results of the diagnostics. See below:

Request:
POST http://localhost:5000/v1/api/iserver/secdef/search HTTP/1.1
<Headers placeholder>

<Content placeholder>

Response:

<Content placeholder>

The exception raised is below (but outside of my skills to understand, troubleshoot, diagnose)

Exception:

ExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxxxxxxx

Message: The underlying connection was closed: The connection was closed unexpectedly.

StackTrace:

   at System.Net.HttpWebRequest.GetResponse()

   at Microsoft.Mashup.Engine1.Library.Http.WrappingHttpWebRequest.<>c__DisplayClass119_0.<WrapResponse>b__0()

   at Microsoft.Mashup.Engine1.Library.Http.WrappingHttpWebRequest.WrapExceptionResponse[T](Func`1 getValue)

   at Microsoft.Mashup.Engine1.Library.Http.WebRequestFactory.TlsSniffingWebRequest.GetResponse()

   at Microsoft.Mashup.Engine1.Library.Http.Request.CreateResponse(ResourceCredentialCollection credentials)

   at Microsoft.Mashup.Engine1.Library.Http.Request.GetResponseCore(ResourceCredentialCollection credentials)

   at Microsoft.Mashup.Engine1.Library.Http.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)

   at Microsoft.Mashup.Engine1.Library.Http.Request.GetResponse(ResourceCredentialCollection credentials, RetryPolicy retryPolicy, SecurityExceptionCreator securityExceptionCreator, Boolean tokenRefreshed)

I am running out of any angles I can change my review/solution hunting.

Are there ways to catch the error (try... otherwise ...? ), and perhaps review more detail (or is the detail posted above all of what comes from the server that I am hitting?). I haven't any other ways that I am aware of in hitting the server to apply a POST (query ?) with the field/Value pairs ?

Kind regards

Rohan

[Anonymous]

Hi and thanks.

Can you point me to a resource which may explain that to me, or suggest how the syntax looks ?

there's nothing to drill down into at MS in this link: https://docs.microsoft.com/en-us/powerquery-m/web-contents.

In the other examples I got working, there wasn't anything that resembled an inbound header (or the like).

Cheers

Rohan

[lbendlin]

headers = [#"Content-Type" = "application/json", Accept = "application/json"]

[Anonymous]

Hi

Thanks for the efforts to help find a solution. I made many minor changes to the code in the path to find a solution, and it seems the solution was to amend "http://" to "https://". I commend the code below as the "solution" (which includes a few comments which are redundant trials, as well as some shaping to visually show me some results), and I thank all who helped.

let

url = "https://localhost:5000/v1/api/iserver/secdef/search",
//cookiestr = "get the value of bm_sv cookie from browser",
headers = [#"Content-Type" = "application/json; charset=uts-8"], //, accept = "application/json"], //, Cookie = cookiestr],
query= Json.FromValue(
[
symbol = "BHP",
name = true,
secType = "STK"
]),

body = Json.FromValue(
//body = Text.ToBinary(
[
symbol = "BHP",
name = true,
secType = "STK"
//exchange = "ASX"
]
),
//Content = Text.ToBinary(body),
//add error capture "try"
//handle it down the track
response = try Web.Contents(
url,
[
//RelativePath = "iserver/secdef/search",
Headers = headers,
Content = body
//Query = query
]
),
// Content = Text.ToBinary(body),
// add error capture "try"
// handle it down the track
Value = response[Value],
#"Imported JSON" = Json.Document(Value,65001),
#"Converted to Table" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"conid", "companyHeader", "companyName", "symbol", "description", "restricted", "fop", "opt", "war", "sections"}, {"Column1.conid", "Column1.companyHeader", "Column1.companyName", "Column1.symbol", "Column1.description", "Column1.restricted", "Column1.fop", "Column1.opt", "Column1.war", "Column1.sections"})
in
#"Expanded Column1"

thanks again.

RT