To make things simple for the demo, we're going to use a online REST webservice called JSONPlaceHolder.. The service doesn't require creating an account or user authentication. While the GET (retrieve) methods are fully functional the POST ( insert ), PUT/PATCH ( update ) and DELETE (delete) methods are placeholders. They return result codes or in the case of POST the id value of the inserted row, but they don't actually modify the data.
Note that while REST web services can return data in any internet mime encodable format, the vast majority of them use JSON and JSON is the only data format supported by the REST client object in PowerBuilder 2017.
RESTClient
The first thing we'll look at is the new RESTClient. It's the primary mechanism to get the DataWindow to use a REST web service. Right now it only supports Retrieve. Support for update operations is planned for R3. Note that in order to use a DataWindow to display data from a REST web service the data set it returns will need to be a simple array. More complex nested structures would still need to be handled through the techniques we'll cover later in this article.For this sample, we're going to pull the list of posts from the JSONPlaceHolder site. The first thing we're going to need to do is create an external DataWindow object that has columns that match the data element names returned by the service. For posts, JSONPalceHolder returns data like this:
{
"userId": 1,
"id": 1,
"title": "sunt aut facere repellat provident occaecati excepturi optio reprehenderit",
"body": "quia et suscipit\nsuscipit recusandae consequuntur expedita et cum\nreprehenderit molestiae ut ut quas totam\nnostrum rerum est autem sunt rem eveniet architecto"
}
So my DataWindow object looks like this:
The code to retrieve the data from the service and then populate the DataWindow object with it is then rather trivial:
RESTClient rest
rest = create RESTClient
rest.Retrieve( dw_1, "https://jsonplaceholder.typicode.com/posts")
The results look like this:
The RESTClient has a number of other properties and functions that are of interest, though we didn't need to use them here:
| Property | Purpose |
|---|---|
| SecureProtocol | Enumerated integer value used to set the secure protocol used by the client (SSL2, SSL3, TLS 1.0, TLS 1.1 or TLS 1.2). The default ( 0 ) means to detect the protocol used by the service and match it. |
| Timeout | Long value for the timeout for the client in seconds. Defaults to 60 seconds. Setting it to 0 means no timeout. |
| Function | Purpose |
|---|---|
| {Get/Set/Clear}RequestHeader{s} | Manipulate the request headers used in the REST request. |
| GetResponseStatusCode | Returns the status code from the REST service (2xx = OK, 3xx = Redirected, 4xx = Client Error, 5xx = Server Error. Particular success codes: 200 OK 201 Created 202 Accepted |
| GetResponseStatusText | The text description for the response |
HTTPClient, JSONGenerator and JSONParser
For dealing with more complex return data structures - and for dealing with updates - we can use the HTTPClient, JSONGenerator and JSONParser. First we're going to retrieve that same data from that same REST web service, but doing it with the HTTPClient. This involves a bit more code: integer li_rc
long ll_id, ll_userid,ll_row,ll_root,ll_index,ll_child,ll_count
string ls_string,ls_result,ls_body,ls_title
httpclient http
http = create httpclient
li_rc = http.sendrequest( 'GET', 'https://jsonplaceholder.typicode.com/posts')
if li_rc = 1 and http.GetResponseStatusCode() = 200 then
http.GetResponseBody(ls_string)
end if
JSONParser json
json = create JSONParser
ls_result = json.loadstring( ls_string )
ll_root = json.getrootitem( )
ll_count = json.getchildcount( ll_root )
dw_1.Reset()
for ll_index = 1 to ll_count
ll_row = dw_1.InsertRow(0)
ll_child = json.getchilditem( ll_root, ll_index )
ll_id = json.getitemnumber( ll_child, "id" )
dw_1.setItem(ll_row, "id", ll_id)
ll_userid = json.getitemnumber( ll_child, "userid" )
dw_1.setItem(ll_row, "userid", ll_userid)
ls_title = json.getitemstring( ll_child, "title")
dw_1.setItem(ll_row, "title", ls_title)
ls_body = json.getitemstring( ll_child, "body")
dw_1.setItem(ll_row, "body", ls_body)
next
We use the HTTPClient SendRequest method to call the REST service method, check the response status code using GetResponseStatusCode and, if success, get the data in JSON format using GetResponseBody. We will then use the JSONParser to parse the JSON data, looping through it and sticking individual data elements in individual rows and columns.
Now lets see how we can perform updates through the REST web service from the DataWindow using those objects:
integer li_rc, li_rsc
long ll_index, ll_count, ll_id, ll_root
constant integer OK = 200
constant integer CREATED = 201
String ls_json, ls_result
dwItemStatus status
HttpClient hc
JSONGenerator jg
JSONParser jp
hc = create HttpClient
jg = create JSONGenerator
jp = create JSONParser
dw_1.AcceptText()
ll_count = dw_1.Rowcount( )
FOR ll_index = 1 TO ll_count
status = dw_1.GetItemStatus ( ll_index, 0, Primary! )
CHOOSE CASE status
CASE NewModified!, DataModified!
//Inserted or Modified Rows
ll_root = jg.createjsonobject( )
jg.AddItemNumber(ll_root, "userid", dw_1.GetItemNumber(ll_index, 'userid'))
jg.AddItemString(ll_root,'title',dw_1.GetItemString(ll_index,'title'))
jg.AddItemString(ll_root,'body',dw_1.GetItemString(ll_index,'body'))
IF status = NewModified! THEN
//Inserted
ls_json = jg.getjsonstring( )
li_rc = hc.sendrequest( 'POST', 'https://jsonplaceholder.typicode.com/posts', ls_json)
li_rsc = hc.GetResponseStatusCode()
IF li_rsc = CREATED THEN
//Get the response, which contains the ID value assigned to the new row
hc.GetResponseBody(ls_json)
ls_result = jp.loadstring( ls_json )
ll_root = jp.getrootitem( )
ll_id = jp.getitemnumber( ll_root, "id" )
//Set it back into the inserted row
dw_1.SetItem ( ll_index, 'id', ll_id )
ELSE
MessageBox ( parent.title, "Insert Failed" )
END IF
ELSE
//Updated
ll_id = dw_1.GetItemNumber ( ll_index, 'id' )
jg.AddItemNumber(ll_root,"id", ll_id)
ls_json = jg.getjsonstring( )
li_rc = hc.sendrequest( 'PUT', 'https://jsonplaceholder.typicode.com/posts/' + String ( ll_id ), ls_json)
li_rsc = hc.GetResponseStatusCode()
IF li_rsc <> OK THEN
MessageBox ( parent.title, "Update Failed" )
END IF
END IF
CASE ELSE
//skip this row
CONTINUE
END CHOOSE
NEXT
//Deleted Rows
ll_count = dw_1.Deletedcount( )
FOR ll_index = 1 TO ll_count
ll_id = dw_1.GetItemNumber(ll_index, 'id', Delete!, true)
li_rc = hc.sendrequest( 'DELETE', 'https://jsonplaceholder.typicode.com/posts/' + String ( ll_id ))
li_rsc = hc.GetResponseStatusCode()
IF li_rsc <> OK THEN
MessageBox ( parent.title, "Delete Failed" )
END IF
NEXT
dw_1.ResetUpdate( )
What I'm doing here is looping through the Primary DataWindow buffer where the inserts and updates are at. If I find an insert, I use the JSONGenerator to put together a JSON data string that contains the data elements except for the primary key, since that will be returned to us from the POST call. I then use the HTTPClient SendRequest method to sent the POST with the JSON data. We then check the response code using HTTPClient GetResponseStatusCode to ensure it was 201 (CREATED) and, if so, use the HTTPClient GetResponseBody to retrieve the data that was returned from the POST call, included the id value that was assigned to the new row. We then use JSONParser to parse out the id value and set that value back in the DataWindow for that row.
The approach is very similar if the row was updated rather than inserted. The main differences are that we a) include the primary key value in the JSON for this request, b) use PUT rather than POST to send the data, c) the URL we use to send the data back includes the id value, d) we check for a response value of 200 (OK) rather than 201 (CREATED) and e) we don't need the response data.
For deletes, we loop through the Delete DataWindow buffer. For deletes we don't need to send the entire row. Instead we just get the id value for the row and include that as part of the url for the DELETE call. As with PUT (Update) we check for a 200 (OK) response from the service.
At the very end of the routine, since we've handled the update of the DataWindow manually, we perform a ResetUpdate to clear the row status flags in the Primary DataWindow buffer and clear the Deleted DataWindow buffer.
As with the RESTClient, the HTTPClient has a number of additional properties and functions that will be of use in specific situations.
| Property | Purpose |
|---|---|
| SecureProtocol | Same as for RESTClient above |
| Timeout | Same as for RESTClient above |
| AutoReadData | TRUE/FALSE. Default is TRUE. FALSE would be of use primarily if the response set is very large. If so, you would use the ReadData method to read chunks of the response in a loop. |
| Function | Purpose |
|---|---|
| {Get/Set/Clear}RequestHeader{s} | Same as for RESTClient above |
| GetResponseStatusCode | Same as for RESTClient above |
| GetResponseStatusText | Same as for RESTClient above |
| PostDataStart/PostData/PostDataEnd | Used to send a large amount of data to the rest service in smaller blocks |
| ReadData | Used to read a large amount of data from the rest service in smaller blocks |
Coming Attractions
This is enough to get us operational with REST web services, but in some cases it requires a bit of coding. PowerBuilder 2017 R3 is expected to deliver additional REST functionality that would make using REST even easier. In particular:- Support of DataWindow updates directly through the RESTClient.
- Methods to import and export JSON data directly to/from the DataWindow, eliminating the need to use the JSONParser/JSONGenerator to put the JSON data together manually.
No comments:
Post a Comment