Hi Chris, and thanks for the feedback.

I've created an external DW with the exact same columns (name and type) as in the JSON.

The lnv_restclient.retrieve(dw_1,ls_url) returns 0.

So no rows are added to the datawindow, even if I see the raw data in the JSON, and it contains three rows.

However, I'm a bit uncertain whether the format is correct or not.

Is this format OK to import using restclient.retrieve()?

[{
"supplier": {
"prognosisLastUpdated": "2018-06-01 08:45:00 +0000",
"packagingStationId": "T-126"
},
"delivery": {
"horizonId": "69FB0F04-05BC-486D-81DE-80E44D0E08BF",
"harvestDate": "2018-06-05 00:00:00 +0000"
}
}, {
"supplier": {
"prognosisLastUpdated": "2018-06-01 08:45:00 +0000",
"packagingStationId": "T-126"
},
"delivery": {
"horizonId": "4268A301-BDFD-4B07-8FF5-681396F265A7",
"harvestDate": "2018-06-06 00:00:00 +0000"
}
}, {
"supplier": {
"prognosisLastUpdated": "2018-06-01 08:45:00 +0000",
"packagingStationId": "T-126"
},
"delivery": {
"horizonId": "09FD2329-944E-464C-922A-4FB9C5C54234",
"harvestDate": "2018-06-07 00:00:00 +0000"
}
}
]

If not, I will look at the JSON Parser to see if I can manually add the rows using SetItem().

Thanks,

Bjarne

Hi Bjarne;

   No, the JSON features in PB2017R3 do not support a dynamic DWO like PB's "SyntaxFromSQL" feature. You can also create a DWO from and ANSI result set as well in the PB releases from many years ago.

I have seen a request for this before in the ticketing system under R2. A SyntaxFromJSON() method feature would be really "Kool" IMHO.

   If your  RESTful web service is returning a 2D JSON result set (ie: rows & columns - no nesting) of "simple" data types, then you can funnel the datum directly into a a DWO. Unfortunately, you will have to build a DWO (ie: external) that exactly matches the RESTful result set in order for that to work. Any deviation from and exact match (must like the DWO's ShareData feature) will result in a failed import.

   If your RESTful web service is returning a more complex structure, then you will have to use the JSON Parser object class to load the JSON result set into and then us its methods to parse out the datum while programmatically loading the DWO primary buffer using the SetItem() method.

Note that even in the JSON Parser approach, optional fields are not supported. So you would have to code even deeper to get around those.

HTH

Regards ... Chris

Hi Bjarne,

you can have a look at the RESTclient object API.

It offers SetRequestHeader to manipulate HTTP header content.

Also, the 3rd argument of Retrieve() is a reference to the TokenRequest for OAuth 2.0.

Best,

.m

Hi again.

I manage to get the raw JSON data into a string, so the client and transport is OK.

But if i try restclient.retrieve() or dw_1.importjson(), they both return 0.

I think the format might be unsupported?

The help file has a paragraph about this.

This is the JSON which is returned:

[{
"supplier": {
"prognosisLastUpdated": "2018-06-01 08:45:00 +0000",
"year": 2018,
"year": 23,
"fishSpecies": 1,
"supplierOrgNumber": 985940460,
"packagingStationId": "T-126"
},
"delivery": {
"horizonId": "69FB0F04-05BC-486D-81DE-80E44D0E08BF",
"harvestDate": "2018-06-05 00:00:00 +0000",
"serial": 1,
"localityId": "34457",
"cage": "1524",
"totalNumber": 13349.0,
"totalQuantity": 60776.57,
"averageWeight": 4.553,
"distributionQualitySuperior": 90.0,
"distributionQualityOrdinary": 5.0,
"distributionQualityProduction": 5.0,
"distributionQualityDiscards": 0.0,
"distributionSize01": 70.9,
"distributionSize02": 1601.04,
"distributionSize03": 10979.36,
"distributionSize04": 24516.69,
"distributionSize05": 18457.98,
"distributionSize06": 4729.09,
"distributionSize07": 409.57,
"distributionSize08": 11.83,
"distributionSize09": 0.1
}
}, {
"supplier": {
"prognosisLastUpdated": "2018-06-01 08:45:00 +0000",
"year": 2018,
"week": 23,
"fishSpecies": 1,
"supplierOrgNumber": 985940460,
"packagingStationId": "T-126"
},
"delivery": {
"horizonId": "4268A301-BDFD-4B07-8FF5-681396F265A7",
"harvestDate": "2018-06-06 00:00:00 +0000",
"serial": 1,
"localityId": "34457",
"cage": "1524",
"totalNumber": 17727.0,
"totalQuantity": 80703.1,
"averageWeight": 4.553,
"distributionQualitySuperior": 90.0,
"distributionQualityOrdinary": 5.0,
"distributionQualityProduction": 5.0,
"distributionQualityDiscards": 0.0,
"distributionSize01": 94.15,
"distributionSize02": 2125.97,
"distributionSize03": 14579.11,
"distributionSize04": 32554.87,
"distributionSize05": 24509.71,
"distributionSize06": 6279.6,
"distributionSize07": 543.85,
"distributionSize08": 15.71,
"distributionSize09": 0.13
}
}, {
"supplier": {
"prognosisLastUpdated": "2018-06-01 08:45:00 +0000",
"year": 2018,
"week": 23,
"fishSpecies": 1,
"supplierOrgNumber": 985940460,
"packagingStationId": "T-126"
},
"delivery": {
"horizonId": "09FD2329-944E-464C-922A-4FB9C5C54234",
"harvestDate": "2018-06-07 00:00:00 +0000",
"serial": 1,
"localityId": "34457",
"cage": "1524",
"totalNumber": 13133.0,
"totalQuantity": 59779.56,
"averageWeight": 4.552,
"distributionQualitySuperior": 90.0,
"distributionQualityOrdinary": 5.0,
"distributionQualityProduction": 5.0,
"distributionQualityDiscards": 0.0,
"distributionSize01": 69.74,
"distributionSize02": 1574.78,
"distributionSize03": 10799.25,
"distributionSize04": 24114.51,
"distributionSize05": 18155.19,
"distributionSize06": 4651.52,
"distributionSize07": 402.85,
"distributionSize08": 11.63,
"distributionSize09": 0.1
}
}
]

Perhaps the two blocks per row is a no go?

Thanks

Bjarne

Thanks.

Will check it out.

How about credentials? Our customer has provided a username and password which is needed for the web service.

Thanks

Bjarne

Hi Bjarne,

Use RESTClient rather than HTTPClient. Code is very simple:

RESTClient lnv_restclient

lnv_restclient = create RESTClient

ls_url = "https://rest.appeon.test/getemployees"

lnv_restclient.Retrieve(dw_1, ls_url)

destroy lnv_restclient

dw_1 is just a standard "external dataWindow", with only columns of the JSON keys that you want to display, those that are not in the dw definition are automatically ignored.

Best,

.m