1. Bjarne Anker
  2. PowerBuilder
  3. Sunday, 23 September 2018 07:40 AM UTC

Hi.

 

One of our customers has provided a web service (Restful API) which returns Json data.

They want us to integrate with it to get these data into our application.

Is there a way to generate a datawindow from the Json?

I guess i need to have a predefined datawindow in order to import the Json data using httpclient and so on?

The idea is to connect to the web service, import data into a datawindow/datastore and then into the application/database.

 

Regards,

 

Bjarne Anker

Maritech Systems AS

Norway

Bjarne Anker Accepted Answer Pending Moderation
  1. Tuesday, 25 September 2018 09:17 AM UTC
  2. PowerBuilder
  3. # 1

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

Comment
  1. Chris Pollach @Appeon
  2. Tuesday, 25 September 2018 14:29 PM UTC
HI Bjarne;

Yes, you can see interwoven Supplier and Delivery information throughout the JSON result set. You would need a "consistent" data stream of one or the other of those items. For this result set, you will need to use the JSON Parser.

Regards ... Chris
  1. Helpful
  1. Bjarne Anker
  2. Tuesday, 25 September 2018 14:36 PM UTC
Hi Chris.



I managed to parse the entire json using the JSONParser.



Thanks again for the great feedback.



Bjarne
  1. Helpful
  1. Chris Pollach @Appeon
  2. Tuesday, 25 September 2018 14:43 PM UTC
You are most welcome .... Excellent news Bjarne! :-)



Have you hugged your DataWindow today - LOL?
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Monday, 24 September 2018 15:28 PM UTC
  2. PowerBuilder
  3. # 2

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

Comment
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Monday, 24 September 2018 10:48 AM UTC
  2. PowerBuilder
  3. # 3

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

Comment
There are no comments made yet.
Bjarne Anker Accepted Answer Pending Moderation
  1. Sunday, 23 September 2018 16:58 PM UTC
  2. PowerBuilder
  3. # 4

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

Comment
There are no comments made yet.
Bjarne Anker Accepted Answer Pending Moderation
  1. Sunday, 23 September 2018 16:36 PM UTC
  2. PowerBuilder
  3. # 5

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

Comment
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Sunday, 23 September 2018 11:53 AM UTC
  2. PowerBuilder
  3. # 6

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

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.