User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

One of the new features added to PowerBuilder 2017 R2 is support for REST web services.  This feature isn't 100% complete, as additional REST functionality is planned for 2017 R3.  There's still a lot in the R2 release to look at though.

To make things simple for the demo, we're going to use a online REST web service 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.

Comments (7)

  1. Hani EL Dabet

Dear Mr. Armstrong,

I am trying to consume a webservice using powerbuilder 2017R2. I have consume the same service in C# and trying now to do the same in powerbuilder. I have created a WCF proxy to access the web service and able to connect. However, in order to call any function I need to add soap header for the API key.  similar to the code in c#:

 //add a soap header for API Key
MessageHeader ApiMessageHeader = MessageHeader.CreateHeader("API_KEY", "http://tempuri.org;", API_KEY);
                    System.ServiceModel.OperationContext.Current.OutgoingMessageHeaders.Add(ApiMessageHeader);


string userName = "username";
string passWord = "xxxxxx.";
string Provider = "dsadsa";
calls the authenticate user
if the authentication is successful it will assign the token variable the appropriate value         

client.AUEAuthenticateUser(userName, passWord, Provider, ref token);

--------------

How to add soap message header in  powerbuilder before calling the method AUEAuthenticateUser.

 

Thanks for your reply

 
  1. Bruce Armstrong

Please post this question in the regular forums.

 
  1. Chris Pollach

Hi Bruce;

Great article .. thanks!

Just a minor point .. the JSON returned must be formed within array brackets ... for example:

[


{
"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"
}

]

  If not, the DWO data transformation will not occur.

Regards .. Chris

 
  1. Richard Bianco

Hi Bruce,

Enjoyed the article as I prepare for a presentation will be doing for the conference.

I'm in the (mundane) process of wiring up REST endpoints to datawindows and wanted to ask if there was anything I could do to help with coding for the REST improvements being delivered for PB2018? I'm probably re-inventing the wheel doing some of the same things you might be doing as I'm trying to write my own utility that takes an endpoint address say... https://api.binance.com/api/v1/klines?symbol=ETHBTC&;interval=1m and creating my own datawindow from the actual Json returned.

The code you have was helpful as a first step. Some changes I'd make are adding support for Json when there the Json isn't surrounded by [ ] and is only surrounded by { } .

Other things I'm trying to write into an ancestor are handling of more than two levels of hierarchy, adding support for boolean, and adding support for automatically converting numbers surrounded by double quotes to be converted to number/decimal in the datawindow. Right now I load the Json into a rough datawindow, then perform transformations on data and moving row by row to another datawindow and saving to database.

I don't have much experience with extending PowerBuilder (at internal level via PBNI) but ideally it would be cool to extend RestClient to handle some of the things I talked about. I downloaded your PBNISMTP project on GitHub and trying to figure it out. If you'd be able/willing to share that code with me- I'd be more than happy to provide enhancements made so you can decide if you want to incorporate them into the tool. I would be honored to think that some of my code made it into the actual release and would expect no compensation. Though if you want to hire me as consultant I'd work for a very reasonable rate (for PB developer).

You should have access to my email via the site but will post it here anyway as this page is member only: rich@displacedguy.com or displacedguy@gmail.com I'm not the best about responding quickly but would really appreciate a response. I have some time and can surely help with any work you have.

p.s I'm also working on a wizard type window that I provide REST endpoint address (like above) and parse the Json into datawindow allowing me to set column name, data type, etc., with as much info derived as possible. Also I'd add ability to generate a database table to store result and have ideas on defining the Json fragments (complex object types) into my database so the definition of Json is data driven. Yeah I have a lot on my plate but it's fun.

Sincerely,
Rich Bianco

  Comment was last edited about 4 months ago by Richard Bianco Richard Bianco
  1. Armeen Mazda    Richard Bianco

I don't know about the feasibility if this could really cover all situations of automatically creating the DataWindow from the endpoint, but boy if you could do that it would be so cool and save time!

 
  1. Bertrand Terlat

Hi

These informations are really useful thanks a lot.

I have a question which concerns the command "http.sendrequest" I saw it was possible to call the function with the first parameter 'CONNECT' such as


http.sendrequest( 'CONNECT', 'https://jsonplaceholder.typicode.com/posts' ..... I don't understand how to use it.

Would you please have an example using the parameter value 'CONNECT' ?

I have the case it's required to autenticate to the url to use it and I also tried to use '&token_auth' without any success in my case.

The result of http.GetResponseStatusCode() gives :

<title>401 - Unauthorized: Access is denied due to invalid credentials.</title>


Thanks in advance for help!!!

Kind Regards,

Bertrand

 
  1. Bruce Armstrong    Bertrand Terlat

I don't know where you saw it was possible to call it using "CONNECT", or why you would want to. That verb is used in the initial connection to the service that is done under the covers. It's not one of the HTTP verbs you would use for a REST service.

If you need to authenticate to the service, you would do that through request headers added prior to sending the request.

Rather than follow up here, I would suggest asking your question in the regular Q&A forums and I can follow up there.

 
There are no comments posted here yet