REST API

Paul Gemin · March 2019

Hello Board Community,

I am trying to get data from my web-based ERP through REST APIs. I have created the data source connection using a Basic authentication mode with my credentials and a query with the parameters I used in Postman previously (witch worked). When I test the query, nothing is returned.

I looked at the BoardConnector log, and this is the error I get:

[15/03/2019 15:22:53] Impossible de charger le fichier ou l'assembly 'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' ou une de ses dépendances. La définition trouvée du manifeste de l'assembly ne correspond pas à la référence de l'assembly. (Exception de HRESULT : 0x80131040)

Which translates into:

Could not load file or assembly 'Newtonsoft.Json, Version=6.0.0.0, Culture=neutral, PublicKeyToken=30ad4fe6b2a6aeed' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)

I am using Board v.10.3.0.51220, (stand alone version, local client and server). I had 10.1.4.50129 version previously, which I uninstalled before installing the current one (installed as administrator).

I have checked that all .NET framework prerequisites have been installed, more specifically:
- .NET framework 3.5 SP1 is active as a service.
- .NET framework 4.0 and 4.5.1 are already installed (or so said the installer after I tried to install them again)
I have tried to connect to another API with no authentication (https://postman-echo.com/get ) which works fine in my browser and in Postman, but triggers the same error in Board.
I have tried to run Board client as administrator.
I have tried the same on another server (same Board version, Master license).
I saw that there are multiple versions of Newtonsoft.Json.dll on my system (4 are linked to Board, 3 of them are the same version for Client, Server and WebApi server, one is a different version in the WebApi Server\Snapshotter folder). I'm not sure if this information is relevant for my problem, though.
It seems the request doesn't reach my ERP (the log on their side shows connections from Postman, but none from Board)
I have tried to run the Board engine using the my Board Admin account instead of the local one

I'm out of ideas as to what to try next. Could someone lend me a hand?

Thank you very much!

Paul

Daniele Di Lorenzo · March 2019

Hello

I don't know if this can help, I just share my experience.

I have a web api connection set as REST API with ParamsBased authentication.

Setting it as in the instructions in Board manual was not working.

I just set the whole string in the URL and select authentiaìcation mode = unknown

https://www.xxxxxx/xxxx/xxxxxx/xxxx/awiBOARDCS.Get8Ds?psAuthKey=xxxxxxxxxxxxxx

and it works.

best regards

Paul Gemin · March 2019

Hello Daniele, and thank you for your answer!

I'm afraid I don't know how to write the whole URL (parameters and hashed key). However, I also have the same problem with the Postman API which doesn't require authentication.

Are you using the same version as me (v10.3.0)?

Are you able to get data from the Postman API? (https://postman-echo.com/get)

Thank you for your time!

Paul

Daniele Di Lorenzo · March 2019

Hi Paul Gemin

Sorry, I'm also not so expert on such setup; for my settings I received informations from the provider and I needed the help of some IT guys as well.

I'm using 10.1.2 but I'm going to migrate to last 10.3 release.

Made a try with postman, no success. How do you set the connection in that case?

I'll furtherly investigate on this as time allows, and if some other hint comes from the community.

best regards

Previous Member · March 2019

Hi Paul Gemin,

if your connection won't work in Postman, it is likely a server side problem. The error message could be something that was generated by the REST API from your ERP system.

BR, Ray

Paul Gemin · March 2019

Thank you for the feedback!

Here are the parameters I set for the Postman connexion :

Postman REST Connexion

And here is the query (no additional parameters)

Maybe I'm not using it properly, though...

Paul Gemin · March 2019

Hello Ray,

Thank you for the feddback. The connexion works fine in Postman (both for my ERP and for the simple request I gave as an example), the issue I have is that I can't do the same with Board. The request from Board doesn't even seem to reach my ERP: no log on the ERP server side, whereas there is one when using Postman (plus I get the data with Postman).

Best regards,

Paul

Daniele Di Lorenzo · March 2019

same result as yours, no data are retrieved.

but I suspect some more parameters have to be specified in the URL or in the query

Sorry, I have no other idea.

Previous Member · March 2019

Hi,

I often experience the same issue. The API works fine in Postman but I don't get any results in Board. My work around is to create an PowerShell script, this will download the data and export it the the Azure storage and use a standard ASCII data reader to import the data.

Joris van den Boogaard · March 2019

Hi Paul Gemin

I have had similar issues with retrieving API data with Board.

For a client I needed to use a source system that only provided data via api. Direct connection with Board was not possible because Board was not able to handle the json output properly.

In my case, a python-specialist designed a script that now runs scheduled and provides the data in csv.

I would be great if the future connector functionalities in Board would be more sophisticated, because we will see more and more source data to be available via API (only) in the future.

Good luck!

Paul Gemin · March 2019

Hello Robert-Jan van Kuppeveld,

Thank you for the answer, this is what I did as well. I used the Invoke-RestMethod cmdlet to do that. Unfortunately the server I want to run the script on is under SBS 2011 standard, and it seems I am stuck with PowerShell v2.0, which does not support Invoke-RestMethod...

For future reference, here is the code that works with PowerShell v5.1 (on my laptop not my server), that I automated with a .bat so I can launch it from Board. It downloads the data in .json format, converts it to .csv and stores it in the dataset folder (for example):

function Get-BasicAuthCreds {
param([string]$Username,[string]$Password)
$AuthString = "{0}:{1}" -f $Username,$Password
$AuthBytes = [System.Text.Encoding]::Ascii.GetBytes($AuthString)
return [Convert]::ToBase64String($AuthBytes)
}
$BasicCreds = Get-BasicAuthCreds -Username "insert username here" -Password "insert password here"

Invoke-RestMethod -Uri "insert Url here" -Headers @{Authorization="Basic $BasicCreds"} -Method Get -ContentType application/JSON).SyncRoot | Export-Csv -LiteralPath "insert target file path here" -NoTypeInformation

The first part builds the header I need to send because the API I'm using requires Basic authentication.

In the second part, I needed to add .SyncRoot because it is the name of the item that contains all the data I need (might not be the correct term, I'm not a developper).

For the sake of completeness, this is the contents of the .bat file :

Powershell.exe -ExecutionPolicy unrestricted -File "insert .ps file path here"

If anyone knows a workaround for PowerShell v2.0, I would be glad to hear about it (online reference seems scarce).

Thank you !

Paul

Previous Member · March 2019

Hi Paul Gemin,

this is adapted from what I found on Replace Invoke-RestMethod in PowerShell 2.0 and PowerShell 2.0 ConvertFrom-Json and ConvertTo-Json implementation - Stack Overflow

function ConvertFrom-Json20([object] $item){ 
  add-type -assembly system.web.extensions   $ps_js=new-object system.web.script.serialization.javascriptSerializer   #The comma operator is the array construction operator in PowerShell   return ,$ps_js.DeserializeObject($item) } 
function Get-BasicAuthCreds {
param([string]$Username,[string]$Password)
$AuthString = "{0}:{1}" -f $Username,$Password
$AuthBytes = [System.Text.Encoding]::Ascii.GetBytes($AuthString)
return [Convert]::ToBase64String($AuthBytes)
}
$BasicCreds = Get-BasicAuthCreds -Username "insert username here" -Password "insert password here"

$WebRequest = [System.Net.WebRequest]::Create("insert Url here")
$WebRequest.Method = "GET"
$WebRequest.Headers.Add("Authorization","Basic $BasicCreds")
$WebRequest.ContentType = "application/json"
$Response = $WebRequest.GetResponse()
$ResponseStream = $Response.GetResponseStream()
$ReadStream = New-Object System.IO.StreamReader $ResponseStream
$JSONData = $ReadStream.ReadToEnd()
$Data = ConvertFrom-Json20($JSONData)
$Data | Export-Csv -LiteralPath "insert target file path here" -NoTypeInformation

hope this helps.

BR, Ray

Paul Gemin · March 2019

Hello Ray Martens, and thank you!

I believe I'm almost there with your solution! I can run it on my laptop, I just need to figure out how to get data in the proper format in the .csv file. This looks like the same issue that led me to use the .SyncRoot property. The chart below shows what is currently written in the .csv file, instead of the actual data (there are 9 other lines that are the same as the second one). However $data contains the correct information when called in the PowerShell console, so this looks very promising.

Comparer,"Count","Keys","Values","IsReadOnly","IsFixedSize","SyncRoot","IsSynchronized"
System.Collections.Generic.GenericEqualityComparer`1[System.String],"6","System.Collections.Generic.Dictionary`2+KeyCollection[System.String,System.Object]","System.Collections.Generic.Dictionary`2+ValueCollection[System.String,System.Object]","False","False","System.Object","False"

I also need to figure out why the same script on my server "fails to connect to the remote server". I'm guessing this has to do with the firewall settings. But at least the script seems compatible with PowerShell v2.0, so thanks again!

Best regards,

Paul

Previous Member · March 2019

Ok I'm not really sure if there's a mixup or not. According to MS, SyncRoot is a property of the array Array.SyncRoot Property (System) | Microsoft Docs

And maybe, you mean it is the name of the root of your JSON object like in this example:

{SyncRoot:

[

{rowId: 1, textValue: "text1"},

{rowId: 2, textValue: "text2"},

{rowId: 3, textValue: "text3"}

]

}

And then you'd expect your CSV output to look like:

rowId textValue

1 text1

2 text2

3 text3

So I'd need to see an example of your input JSON and of the output (just something short with fake values).

Paul Gemin · March 2019

Yes that's exactly what I'm expecting, and maybe it has a name instead of SyncRoot (but I don't know which).

Here are the elements I have :

API documentation from my ERP :

API documentation

JSON format (sample) from my ERP :

JSON Sample

$data contents when I run your script in PowerShell (which means it works!) :

$data contents

.csv contents after the script (sorry, it is too wide):

Best regards,

Paul

Previous Member · March 2019

So if I understood the problem correctly, it lies with the datatype. So hopefully this trick from powershell convert array of hastables into csv - Stack Overflow will do it.

Now instead of doing (last line of the script)

$Data | Export-Csv -LiteralPath "insert target file path here" -NoTypeInformation

we'll try this:

$Data | ForEach-Object { 

  New-Object PSObject -Property $_ 

} | Export-Csv -LiteralPath "insert target file path here" -NoTypeInformation

and hope this gets better results.

Paul Gemin · March 2019

Yes, thank you very much, Ray Martens! It works fine now. I just needed to add

-Delimiter (";")

as a parameter for Export-Csv to avoid problems with commas and line returns in some other files, and I will be able to start automating as soon as my server firewall/proxy is properly configured (but that is a problem for another day / topic).

Thanks again!

Paul

Paul Gemin · March 2019

I also had to change the max JSON length in the ConvertFrom-Json20 function by adding

$ps_js.MaxJsonLength = 20971520

Which I believe would get me shot at by a real developper (but at least it works, because I had a Json file a bit above 4Mb)

Paul

REST API

Answers

Categories