Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Web API datastore json return

  1. How-to
0
Votes
Undo
  1. paulo gomes
    paulo gomes
  2. SnapDevelop
  3. Monday, 2 December 2019 00:32 AM UTC

Hi All,

The result set return by the datastore is a JSON format which is different when compared with the JSON string returned by the datastore.ExportPlainJson built in method: teh column names are surrounded by the back slash character \

Example is shown below (both display only part of the JSON returned):

 

datastore result set returned in JSON

{"memHistoryLoad":[{"Compute_0001":1.0,"Policy_Type_Policy_Type_Name":"Member Reservation Points",
"Contract_Resort_Id":"142510000000004867","Contract_Resort_Name":"Divi Club"

 

String datastore.ExportPlainJson: the columns are surrounded by \ (back slash) character:

 

{"MemberHistory":["[{\"Compute_0001\":1.0,\"Policy_Type_Policy_Type_Name\":\"Member Reservation Points\",
\"Contract_Resort_Id\":\"142510000000004867\",\"Contract_Resort_Name\":\"Divi Club\"

Is there any other built-in method to extract the JSON string from the datastore?

Thank you,

Paulo Gomes

Interval International LLC
pgomes@intervalintl.com
  1. API
  2. JSON
  3. PowerBuilder (Appeon)
  4. DataStore
  5. SnapDevelop
Asp.net DataStore.Retrieve(arg[]) method
Select BLOB column with embedded SQL in Snap Devel...
Responses (3)
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Monday, 2 December 2019 07:38 AM UTC
  2. SnapDevelop
  3. # 1

Hi Paulo,

Could you provide a sample project to reproduce this issue (the columns are surrounded by back slash)?

Regards,

Logan

 

Comment
There are no comments made yet.
paulo gomes
paulo gomes Accepted Answer Pending Moderation
  1. Monday, 2 December 2019 23:01 PM UTC
  2. SnapDevelop
  3. # 2

Hi Logan,

Thank you for the reply.

I have attached a Web API project as .rar file.

The project originally uses Oracle but I added the AdvWorksController and the supporting classes to access the Adventure Works SQL Server database instead.

There are two (02) methods for you to invoke from the browser in the AdvWorksController : 

1.  AdvWorksPerson_datastore 

     http://localhost:5000/api/advworks/AdvWorksPerson_datastore for IIS express in my equipment

     It returns a datastore in JSON format which is displayed in the browser

2. AdvWorksPerson_structure

    http://localhost:5000/api/advworks/AdvWorksPerson_structure for IIS express in my equipment

    It invokes the same above method and retrieve the same data. The difference is that it populates and returns an EA Server style structure with a JSON string exported from the datastore's method  ExportPlainJson().

    The array resultset_value in the structure has the JSON string.

     This string has the pairs column name : data value surrounded by back slashes, as displayed in the browser.

 

The connection string in the Startup.cs points to an ODBC data source,  AdvWorksOdbc, and needs to be changed by yours.

Thank you in advance for the help.

Paulo Gomes

P.S.: the Oracle database referenced in the Project has the schema and scripts I uploaded for Julie while ago.

Attachments (1)
PB2019WebApiSqlServer.rar
Comment
Logan Liu @Appeon
  1. Logan Liu @Appeon
  2. Tuesday, 3 December 2019 09:50 AM UTC
Hi Paulo,

it has to use the backslash character "\" because this value (Plain JSON) is a string containing double quotes.



Some characters are reserved in JSON and must be properly escaped to be used in strings, e.g.:

Double quote is replaced with \"

Backslash is replaced with \\



So you have to parse this string value extra in your client.



Regards,Logan
  1. Helpful
There are no comments made yet.
paulo gomes
paulo gomes Accepted Answer Pending Moderation
  1. Tuesday, 3 December 2019 16:21 PM UTC
  2. SnapDevelop
  3. # 3

Hi Logan,

Thank you for the explanation.

I understand that the "Plain JSON" has double quotes and it needs the escape character, OK.

However, we already have a lot of work to do to migrate the PB logic to C# Web API. This extra parsing will occurs in the Java front-end (that is the Client) and we are trying to avoid any extra work in this Project, specially if the work has to be done by other Team than mine.

Let's do a quick review:

   1- The DataPacker / DataStore returns JSON that is proven parsed in the front-end as it is with no change in the code.

   2- The web methods exposed by the API cannot just return the JSON datastore produced by a retrieve because, due to the existing logic in the front-end, it has to return an EA Server data structure (as I explained in my previous comments)

   3- This EA Server data structure has a string array that today holds as many JSON result sets are needed.

   4- These JSON result sets are generated internally by PB, converting the data stores result sets to JSON and populating the EA Server data structure

   5- The tentative was to extract the JSON result sets from the various data stores involved in the logic and to populate the EA Server data structure array with these JSON strings. 

   6- We use a C# statement like 

       " string resultSet_json = ds_any_data_store.ExportPlainJson(); " which adds the escape characters we are trying to avoid.

There are other built-in data store methods that I had not yet tested, like  ExportJson

Bottom line, the final question is:

     How can we get the JSON returned by a datastore retrieve as it is, not the Plain JSON, to be able to populate the EA Server data structure ?

Thank you.

Regards,

Paulo Gomes

pgomes@intervalintl.com

Comment
Load more comments
Logan Liu @Appeon
  1. Logan Liu @Appeon
  2. Monday, 9 December 2019 04:57 AM UTC
Hi Paulo,

Escape characters are a standard in JSON strings.

I don't know how to parse it in your front-end, but you can get the string very easily in PB:



// Parse

JSONParser lnv_JsonParser

Long ll_RootObject,ll_number_item, ll_number,ll_resultset_value_array, ll_object_item

String ls_ds_json



lnv_JsonParser = Create JSONParser



lnv_JsonParser.LoadString(ls_json)



ll_RootObject = lnv_JsonParser.GetRootItem()



// Get array

ll_resultset_value_array = lnv_JsonParser.GetItemArray(ll_RootObject, "resultset_value")



// Get the first item

ll_object_item = lnv_JsonParser.GetChildItem(ll_resultset_value_array, 1)



lnv_JsonParser.getitemtype(ll_object_item)



// Get a string of Plain JSON

ls_ds_json= lnv_jsonparser.getitemstring(ll_object_item)



Messagebox("Plain JSON", ls_ds_json)



Regards,

Logan
  1. Helpful
paulo gomes
  1. paulo gomes
  2. Monday, 9 December 2019 16:50 PM UTC
Hi Logan,

Thank you for the comment. Yes, it is very easy to parse the JSON string in PB.



The escape characters in the JSON string surprised me because the rows returned in JSON from the Primary Buffer of the Data Store object does not have escape characters.

Besides the fact that I have programmed/designed myself a number of C# REST web services along the past years with different .Net Frameworks and Visual Studio versions and none has escape characters built in.



However I already consumed some third-party REST web services that return JSON with escape characters.



So, you are right, in some situations, escape characters are embedded in JSON strings.



The good news is that it seems that our Java JSON parser is able to parse the escape characters. It is not a "final" word about it but it seems good so far.



I'll let you know by the end of the day and if it is confirmed that we can parse the strings with escape characters, we will be able to close this request.



Thank you for your time researching this.



Regards,



Paulo Gomes

pgomes@intervalintl.com
  1. Helpful
paulo gomes
  1. paulo gomes
  2. Tuesday, 10 December 2019 18:14 PM UTC
Hi Logan,

Good news: yes, the Java parser can parse the several different JSON result sets we generated so far from the REST Web API, in PB2019 and PB2019 R2 beta too.

This means that we can proceed and have this request Resolved.



Thank you.



Regards,

Paulo Gomes

pgomes@intervalintl.com
  1. Helpful
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.