RowSet - JSONItem - MySQL - ToString failure

Carl_Fitzsimmons · July 27, 2021, 3:12pm

This is a continuation of moving a Web Service API to XOJO 2021r2. However this portion has to specifically do with using a prepared MySQL statement and grabbing data from a MySQL table. That part works and I can iterate through the RowSet that was filled after db.connect and performing a SQLSelect

The part that is not working is a conversion taking the RowSet info and placing that into a JSONItem and returning that back. Then the ToString element of the JSONItem is prepared to be sent back as a response. This worked as a RecordSet but perhaps I do not see something obvious that is preventing this conversion from occurring.

I start by preparing to get data from the MySQL table

// Perform the query.
Var Records As RowSet = db.SelectSQL(SQL, 0 )

I am using the Drummers sqlite table as the model for the MySQL table and named it the same.
SQL is a String which =s SQL = "SELECT * FROM Drummers Where id > ? ORDER BY Votes DESC"

That worked so I perform the JSON conversion

// Convert the RowSet to a JSON object.
Var myJSONInfo As JSONItem = RowSetToJSONItemMySQL( Records ) 

// Set the response content to a decoded version of the JSON object.
Response.Content = myJSONInfo.ToString

myJSONInfo calls the Method RowSetToJSONItemMySQL

Next I do the following to get JSON info by calling Method RowSetToJSONItemMySQL

// Convert the RowSet to a JSON object.
Var myJSONInfo As JSONItem = RowSetToJSONItemMySQL( Records )

Here is the called Method RowSetToJSONItemMySQL with Parameters Records As RowSet, Close As Boolean = True and a Return Type of JSONItem

// Convert a RowSet to JSONItem.
Var RecordsJSON As New JSONItem

// Loop over each record...
While Not Records.AfterLastRow
  
  Var RecordJSON As New JSONItem
  
  // Loop over each column...
  For i As Integer = 0 To Records.LastColumnIndex
    
    // Add a name / value pair to the JSON record.
    Select Case Records.ColumnType(i)
    Case 2, 3, 6, 7, 11, 13, 14, 15, 16, 19
      RecordJSON.Value( Records.ColumnAt(i).Name ) = Records.ColumnAt(i).IntegerValue
    Case 4, 5, 18
      RecordJSON.Value( Records.ColumnAt(i).Name ) = Records.ColumnAt(i).StringValue
    Case 8, 9, 10
      RecordJSON.Value( Records.ColumnAt(i).Name ) = Records.ColumnAt(i).DateTimeValue
    Case 12
      RecordJSON.Value( Records.ColumnAt(i).Name ) = Records.ColumnAt(i).BooleanValue
    Case Else
      RecordJSON.Value( Records.ColumnAt(i).Name ) = Records.ColumnAt(i).Value
    End
    
    'Var myname As String = Records.ColumnAt(i).Name ' I did this just to see the data and its there
    'Var myvalue As String = Records.ColumnAt(i).Value ' I did this just to see the data and its there
    'RecordJSON.Value( Records.ColumnAt(i).Name ) = Records.ColumnAt(i).Value ' Assign data using just name and value which failed
    'RecordJSON.Value( myname ) = myvalue ' Did this using the values in myname and myvalue - still failed
    
  Next
  
  // Add the JSON record to the JSON records object.
  RecordsJSON.Append(RecordJSON)
  
  // Go to the next row.
  Records.MoveToNextRow
  
Wend

// Close the recordset.
If Close Then
  Records.Close
End If

Return RecordsJSON

When this returns to the calling Method this statement

// Set the response content to a decoded version of the JSON object.
Response.Content = myJSONInfo.ToString

fails because ToString has no data.

Now as you can see I modified RowSetToJSONItemMySQL just in case the datatype was the problem meaning the warning DatabaseColumn.Value

Notes

The type-specific properties that get and set the values are recommended over Value.
So just in case I modified that method to check ColumnType and read using the proper type-specific property.

Drummers MySQL table consists of these columns/datatype

So is there again something I missed or do I need to perform this in another manner?

Tim_Hare · July 27, 2021, 6:53pm

I think the problem was discussed in Strange .Add behaviour in new JSON implementation - #7 by Greg_O_Lone

Basically, a New JSONItem defaults to a single item, not an array. So Append fails silently. This is a change from the old functionality where an empty JSONItem would switch to either based on the first thing added: Value implies a single item, Add/Append implies an array.

To get an array, use

Var RecordsJSON As New JSONItem("[]")

Carl_Fitzsimmons · July 27, 2021, 7:44pm

Thanks - for the array you may be right. I just realized before I check on the status of the JSON Array the non array JSONItem fails to load ToString as well.

Here is a snapshot before stepping into what will be ‘id’ and its INT value

Here is a snapshot after stepping in

The same occurs with any data type.

So it appears the first failure is RecordsJSON which has no data assigned to ToString. Once this resolved the add to JSONItem array should work.

Also interestingly this var Var RecordsJSON As New JSONItem("[]") when you look at it in the debugger before performing this

// Add the JSON record to the JSON records object.
RecordsJSON.Append(RecordJSON)

The ToString Name has [ ] assigned. After stepping through the [ ] are done

Carl_Fitzsimmons · August 5, 2021, 6:30pm

This is resolved in this thread JSONItem encoding issue in 2021 releases