'Executes the given query and creates a Map that you can pass to JSONGenerator and generate JSON text.
'DBTypes - Lists the type of each column in the result set.
'Usage example: (don't forget to add a reference to the JSON library)
' Dim gen As JSONGenerator
' gen.Initialize(DBUtils.ExecuteJSON(SQL, "SELECT Id, Birthday FROM Students", Null, _
' 0, Array As String(DBUtils.DB_TEXT, DBUtils.DB_INTEGER)))
Public Sub ExecuteJSON (SQL As SQL, Query As String, StringArgs() As String, Limit As Int, DBTypes As List) As Map
Dim table As List
Dim cur As ResultSet
If StringArgs <> Null Then
cur = SQL.ExecQuery2(Query, StringArgs)
Else
cur = SQL.ExecQuery(Query)
End If
Log("ExecuteJSON: " & Query)
Dim table As List
table.Initialize
Do While cur.NextRow
Dim m As Map
m.Initialize
For i = 0 To cur.ColumnCount - 1
Select DBTypes.Get(i)
Case DB_TEXT
m.Put(cur.GetColumnName(i), cur.GetString2(i))
Case DB_INTEGER
m.Put(cur.GetColumnName(i), cur.GetLong2(i))
Case DB_REAL
m.Put(cur.GetColumnName(i), cur.GetDouble2(i))
Case Else
Log("Invalid type: " & DBTypes.Get(i))
End Select
Next
table.Add(m)
If Limit > 0 AND m.Size >= Limit Then Exit
Loop
cur.Close
Dim root As Map
root.Initialize
root.Put("root", table)
Return root
End Sub