« One month till FileMa… | Home | SMTP Server Settings »

RecordSet to JSON and back


Today I want to show you two useful methods to convert from a RecordSet in Xojo to a JSON string and back. We combine BuildRecordSetMBS function (in MBS Xojo SQL Plugin) with JSONMBS class (from MBS Xojo Util Plugin) and the new Convert method helps a lot!

Here the function RecordSetToJSON:

Function RecordSetToJSON(r as RecordSet) As String // adds data in recordset to a JSON Dictionary Dim FieldNames() As String Dim Values() As String // Query list of field names Dim c As Integer = r.FieldCount For i As Integer = 1 To c Dim d As DatabaseField = r.IdxField(i) FieldNames.Append d.Name Next // get all fields from all records into values array While Not r.EOF For i As Integer = 1 To c Values.Append r.IdxField(i).StringValue Next r.MoveNext Wend // make new Dictionary with the values and field names Dim j As New Dictionary j.Value("FieldNames") = FieldNames j.Value("Values") = Values // Convert to JSON Dim v As Variant = j Dim json As JSONMBS = JSONMBS.Convert(v) // return as JSON string Return json.toString End Function

And the conversion back:

Function JSONToRecords(JSON as string) As RecordSet // turns JSON back to RecordSet // parse it Dim j As JSONMBS = New JSONMBS(json) // convert to Dictionary/Array/Variant Dim d As Dictionary = j.Convert // query our arrays Dim vFieldNames() As Variant = d.Value("FieldNames") Dim vValues() As Variant = d.Value("Values") // convert to String arrays Dim FieldNames() As String Dim Values() As String For Each FieldName As Variant In vFieldNames FieldNames.Append FieldName Next For Each Value As Variant In vValues Values.Append Value Next // Build recordset based on arrays Return BuildRecordSetMBS(FieldNames, Values) End Function

Here an example on how to use it:

EventHandler Sub Test() // open a Database Dim ordersDB As New SQLiteDatabase ordersDB.DatabaseFile = GetFolderItem("Orders.sqlite") If Not ordersDB.Connect Then MsgBox("Database Error: " + Str(OrdersDB.ErrorCode) + EndOfLine + EndOfLine + OrdersDB.ErrorMessage) Return End If // make a query Dim r As RecordSet = ordersDB.SQLSelect("SELECT * FROM Customers") // convert to JSON Dim json As String = RecordSetToJSON(r) // and back Dim rr As RecordSet = JSONToRecords(json) Break // inspect in debugger End EventHandler

We hope you enjoy this. Please try it and send us questions if you need more help.

06 07 19 - 07:36