Send JSONItem to PAW

General
#1

I have a console app that will be used to replace a web service API using PAW to test.

I successfully accept, parse the path and URL parameters and the get info from a database.

I use this Method ‘SelectSQLProcess’ with a return type = Dictionary. This Dictionary is sent back to PAW where the Response contains JSON info.

Var JSONItem_Array As New JSONItem("[]")

// See if there is data (RowSet) to be returned...
If Not data.AfterLastRow Then
  
  // Loop over each row...
  While Not data.AfterLastRow
    
    // We'll treat each record as a dictionary.
    Dim Record As New Dictionary
    
    // For each column...
    For i As Integer = 0 To data.LastColumnIndex
      // Add the column name / value pair as a dictionary element.
      Record.Value( data.ColumnAt(i).Name.DefineEncoding(Encodings.UTF8)) = data.ColumnAt(i).StringValue.DefineEncoding(Encodings.UTF8)
    Next
    
    // Add the record to the JSON object.
    JSONItem_Array.Add(Record)
    
    // Go to the next row.
    data.MoveToNextRow
    
  Wend
  
  // Close data RowSet
  data.Close
  
  // Convert array of JSON to Dictionary
  Var json As Dictionary = ParseJSON( JSONItem_Array.ToString )
  // The above throws a cast error: Array cannot be cast to Dictionary 
  
  // Return Dictionary
  Response = json

I have attempted several ways to convert to a Dictionary but have not been successful. Just in case here is a snippet of the JSONItem array.

[{"VRCNUM":"15354796","Last_Name":"ALASTNAME","First_Name":"AFIRSTNAME","Middle_Name":"H","Suffix":"","House_Number":"10","Street":"STREET DR","Apartment":"","Half_Address":"0","Res_Addr_Line2":"","Res_Addr_Line3":"","City":"SOMECIRTY,"State":"SOMESTATE","Zipcode":"11111","Zip4":"","Telephone":"(111)271-1423","Email_str":"","Mail_Address1":"","Mail_Address2":"","Mail_Address3":"","Mail_Address4":"","Mail_City":"","Mail_State":"","Mail_Zipcode":"","Mail_Zip4":"","Party":"DEM","Gender":"F","Date_of_Birth":"1938-11-02","L_T":"57","Election_District":"8","LD":"15","AD":"135","SD":"55","CD":"25","CC_WD_Vil":"","Town_Code":"060","Last_Update":"2012-04-29 00:00:00","Original_Reg_Date":"1960-01-01 00:00:00","Statevid":"SOMESTATE000000000023111777","Sep_14":"N","Nov_14":"Y","Sep_15":"J","Nov_15":"Y","Apr_16":"Y","Sep_16":"J","Nov_16":"Y","Sep_17":"J","Nov_17":"Y","Jun_18":"Y","Sep_18":"Y","Nov_18":"Y"},{"VRCNUM":"100538","Last_Name":"ANOTHERLASTNAME","First_Name":"ANOTHERFIRSTNAME","Middle_Name":"P","Suffix":"","House_Number":"7","Street":"ACORN DR","Apartment":"","Half_Address":"0","Res_Addr_Line2":"","Res_Addr_Line3":"","City":"SOMECITY","State":"SOMESTATE","Zipcode":"11111","Zip4":"","Telephone":"(111)889-0610","Email_str":"","Mail_Address1":"","Mail_Address2":"","Mail_Address3":"","Mail_Address4":"","Mail_City":"","Mail_State":"","Mail_Zipcode":"","Mail_Zip4":"","Party":"DEM","Gender":"F","Date_of_Birth":"1986-01-29","L_T":"57","Election_District":"8","LD":"15","AD":"135","SD":"55","CD":"25","CC_WD_Vil":"","Town_Code":"060","Last_Update":"2017-03-24 00:00:00","Original_Reg_Date":"2004-02-06 00:00:00","Statevid":"SOMESTATE000000000023783022","Sep_14":"N","Nov_14":"Y","Sep_15":"J","Nov_15":"Y","Apr_16":"Y","Sep_16":"J","Nov_16":"Y","Sep_17":"J","Nov_17":"Y","Jun_18":"Y","Sep_18":"Y","Nov_18":"Y"},{"VRCNUM":"156217","Last_Name":"LASTNAME2","First_Name":"FIRSTNAME2","Middle_Name":"G","Suffix":"","House_Number":"7","Street":"ACORN DR","Apartment":"","Half_Address":"0","Res_Addr_Line2":"","Res_Addr_Line3":"","City":"SOMECITY","State":"SOMESTATE","Zipcode":"11111","Zip4":"2601","Telephone":"","Email_str":"","Mail_Address1":"","Mail_Address2":"","Mail_Address3":"","Mail_Address4":"","Mail_City":"","Mail_State":"","Mail_Zipcode":"","Mail_Zip4":"","Party":"DEM","Gender":"M","Date_of_Birth":"1985-02-25","L_T":"57","Election_District":"8","LD":"15","AD":"135","SD":"55","CD":"25","CC_WD_Vil":"","Town_Code":"060","Last_Update":"2018-12-06 00:00:00","Original_Reg_Date":"2005-07-08 00:00:00","Statevid":"SOMESTATE000000000023379133","Sep_14":"J","Nov_14":"Y","Sep_15":"J","Nov_15":"N","Apr_16":"J","Sep_16":"N","Nov_16":"Y","Sep_17":"J","Nov_17":"N","Jun_18":"J","Sep_18":"N","Nov_18":"Y"},{"VRCNUM":"9181","Last_Name":"LASTNAME1","First_Name":"FIRSTNAME1","Middle_Name":"E","Suffix":"","House_Number":"95","Street":"ANGELS PATH","Apartment":"","Half_Address":"0","Res_Addr_Line2":"","Res_Addr_Line3":"","City":"ANOTHERCITY","State":"SOMESTATE","Zipcode":"11111","Zip4":"","Telephone":"","Email_str":"","Mail_Address1":"","Mail_Address2":"","Mail_Address3":"","Mail_Address4":"","Mail_City":"","Mail_State":"","Mail_Zipcode":"","Mail_Zip4":"","Party":"DEM","Gender":"F","Date_of_Birth":"1960-07-06","L_T":"57","Election_District":"34","LD":"15","AD":"135","SD":"55","CD":"25","CC_WD_Vil":"","Town_Code":"060","Last_Update":"2014-09-04 00:00:00","Original_Reg_Date":"2000-03-14 00:00:00","Statevid":"SOMESTATE000000000023774498","Sep_14":"N","Nov_14":"N","Sep_15":"J","Nov_15":"N","Apr_16":"Y","Sep_16":"J","Nov_16":"Y","Sep_17":"J","Nov_17":"N","Jun_18":"N","Sep_18":"N","Nov_18":"Y"}]

I include a pic of PAW with output that simulates what I am attempting to receive. I am using XOJO 2021r2.1

image
image976×994 102 KB

#2

A dictionary cannot also be an array. That’s part of what makes JSONItem special.

Instead of:

Dim Record As New Dictionary

Try

Dim Record as New JSONItem
#3

That change created another type cast problem but you got me thinking.

So I changed to return parameter to JSONItem and then set the Request.Response.Content = to that returned value adding .ToString. Then when the Headers, Content-Type etc were set and full Response prepared I received what I needed in PAW.

The upstream Method was changed as well to this

// Get the JSONItem from SQLPrepared statement.
Var myJSON As JSONItem = SelectSQLProcess(my_str)

// Set the response content to a decoded version of the JSON object.
// Grab RowSet set to JSONItem object then convert to String then set Response.Content
// Response.Content will contain the JSONItem array and then be sent ot requestor
Request.Response.Content = myJSON.ToString

Return myJSON

Here is the updated Method - SelectSQLProcess

Var JSONItem_Array As New JSONItem("[]")

// See if there is data (RowSet) to be returned…
If Not data.AfterLastRow Then

// Loop over each row…
While Not data.AfterLastRow

// We'll treat each record as a dictionary.
Dim Record As New Dictionary

// For each column...
For i As Integer = 0 To data.LastColumnIndex
  // Add the column name / value pair as a dictionary element.
  Record.Value( data.ColumnAt(i).Name.DefineEncoding(Encodings.UTF8)) = data.ColumnAt(i).StringValue.DefineEncoding(Encodings.UTF8)
Next

// Add the record to the JSON object.
JSONItem_Array.Add(Record)

// Go to the next row.
data.MoveToNextRow

Wend

// Close data RowSet
data.Close

// Return JSONItem
Response = JSONItem_Array

Return Response

That returns a JSONItem for further work before sending to PAW

When I compare them side by side with the same solution using LUNA in 2019 XOJO this XOJO 2021 ALOE Express solution works much faster. In both cases LUNA and this modified ALOE Express return 6157 Index’s below Root containing 51 items each. The old LUNA returns that data in 97.62 sec and the updated ALOE Express returns the same data in 2.49 sec. I will take that.

I am certain if I looked more into LUNA I could speed that up but since the code in there is marked for deprecation there is no reason to do that.

Thanks for your response.