Tech Articles

There were some JSON features introduced in 2017 R2, in particular the JSONGenerator and JSONParser, which were covered in a previous blog post.  2017 R3 introduces additional important JSON features, in particular JSON import/export from a DataWindow, and a JSONPackage object that is used to merge/extract JSON data sets.  Those will be covered in this blog post.

JSON Formats

Before we cover the specific new features we need to first look at the JSON formats involved in order to understand what the feature do.  Currently there are two formats the new features use:

• Simple JSON
• Standard DataWindow JSON

Simple JSON

Simple JSON is just that, the JSON you see everywhere with name/value pairs, and records and arrays.

 { "column1":1, "column2":"name", "column3":true, "column4":null... }  

Standard DataWindow JSON

Standard DataWindow JSON is JSON that has a specified structure, one that is intended to allow the transfer of data but metadata as well.   It has the following format:

 {  
 "identity": "70c86603-983b-4bd9-adbc-259436e43cbd",  
 "version":1.0,  
 "dataobject":  
      {  
      "name":"d_example",  
        "meta-columns":[COLUMN-META1, COLUMN-META2...],  
      "primary-rows":[DW-STANDARD-ROW1, DW-STANDARD-ROW2...],  
      "filter-rows":[DW-STANDARD-ROW1, DW-STANDARD-ROW2...],  
      "delete-rows":[DW-STANDARD-ROW1, DW-STANDARD-ROW2...],  
      “dwchilds":{"department_id": [SIMPLE-ROW1,SIMPLE-ROW2...], "category_id": [SIMPLE-ROW1,SIMPLE-ROW2...] ...}  
      }  
 }  

Where COLUMN-META is defined as:

 { "name": "department_id", "index": 1, "datatype": "long", "nullable": 0 }  

DW-STANDARD-ROW is defined as:

 { "row-status": 0,  "columns":  
      {"column1": [CURRENT-VALUE, COLUMN-STATUS, ORIGINAL-VALUE],   
      "column2": [CURRENT-VALUE, COUMN-STATUS, ORIGINAL-VALUE], ...   
      }   
 }  

And SIMPLE-ROW is defined as:

 { "column1":1, "column2":"name", "column3":true, "column4":null... }  

As a result, the format contains information about:

• the name of the datawindow that was the source of the data
• the column definitions in that datawindow
• the data in the primary, filter and deleted datawindow buffers
• the column status, original and current values of all columns in those buffers
• data from child datawindows referenced by the datawindow

With that information, it's possible to pass full state information via JSON, similar to the GetFullState/SetFullState methods that were used in earlier PowerBuilder distributed applications.

Now that we know what the formats are, let's take a look at the new functions.  The first thing we'll look at is exporting JSON from a DataWindow:

JSON Export

The DataWindow control and DataStore have new ExportJson methods.  There are a number of different argument forms for it, as follow:

JSON Import

Importing JSON into a DataWindow only has one form:

long dwcontrol.ImportJson( string json {, string error} {, DWBuffer dwbuffer {, long startrow {, long endrow {, long startcolumn {, long endcolumn {, long dwstartcolumn } } } } } } )

You don't need to specify the JSON format for the import, the DataWindow figures that out from the data you pass in.  There is an optional argument to get any import error information back out as a string.  You can import into a specific DataWindow buffer, and you can optionally limit the rows/columns from the data that is imported.

JSON Merge/Extract

R3 introduces a new JSONPackage object which is used to merge individual datasets into a single data set and then extract them back into separate datasets later.  The primary use is to reduce number of calls needed to pass data between client and service (i.e., batch up service calls).  It involves four steps:

1.  Set values.  The SetValue method is used to do this.  The are two forms of it:

2. Extract the merged JSON for tranmission.  There are two different methods for this:

3.  Set some merged JSON you have received into the object.  There is only one method for this:

LoadString ( JsonDaat)

4.  Extract the individual data sets from the merged data.

GetValue ( Key )

Sample Code

Let's look at some sample code that puts all this to use:

The DataWindow in the upper left is pulling data from the Booktown database I used for my PostgreSQL demos.  The dropdown and command buttons in the upper right allow us to export that data as JSON and store it in the MLE in the bottom left, and then clear the DataWindow and impot it back in.  The drop down allows us to select the format we want to use.  The Merge button in the middle right merge that book data from the DataWindow with author information from a DataStore and stores that merged JSON in the MLE.  Finally the Export button in the middle right extracts the author information from the merged data and stores just that data in the MLE.

When the drop down is changed, an instance variable called format is set with the zero based index value chosen ( 0 = Simple JSON, 1 = Standard JSON ).  The script in the export button then uses that to determine which format to export the data in.

 string          ls_json  
 ls_json = dw_1.Exportjson( ( format <> 0 ) )  
 mle_1.text = ls_json  
 cb_import.enabled = TRUE 

The import button simply clears the DataWindow, prompts the user to let them know that happened, and then imports the JSON back into the DataWindow.   The MessageBox was added because clearing the DataWindow and importing the JSON happens so fast that the user would otherwise not notice any change.

 dw_1.Reset()  
 MessageBox ( "Import", "DataWindow Cleared" )  
 dw_1.importjson( mle_1.Text )  

The Merge button, as mentioned above, merges the book and author data from the Booktown database:

 DataStore     ds  
 JSONPackage jp  
 String     ls_json  
   
 jp = create JSONPackage  
 ds = create DataStore  
 ds.DataObject = "d_authors"  
 ds.SetTransObject ( SQLCA )  
 ds.Retrieve()  
   
 jp.SetValue ( "books", dw_1 )  
 jp.SetValue ( "authors", ds )  
   
 ls_json = jp.getjsonstring( )  
 mle_1.Text = ls_json  
 cb_import.enabled = FALSE  
 cb_extract.enabled = TRUE  
   
 Destroy ds  
 Destroy jp  

And the export button grabs that merged JSON from the MLE, extracts the author info JSON from it, and then stores that back in the MLE.

 JSONPackage jp  
 String     ls_json  
   
 jp = create JSONPackage  
 jp.LoadString ( mle_1.Text )  
 ls_json = jp.GetValue ( "authors" )  
 mle_1.Text = ls_json  
 this.enabled = FALSE  
   
 Destroy jp  

Summary

The new JSON features are a crucial step in updating PowerBuilder to support modern distributed applications.  The ability to package meta data, including state information, and bundling up data sets will become particularly important when PowerBuilder 2018 is released and we will be able to create C# REST web services using PowerBuilder that can process such data.