Output ADODB.RecordSet as JSON

Question

I'm trying to change my application so that it outputs JSON instead of HTML when it makes an AJAX request for some data. I have an ADODB RecordSet. I need to loop through it row-by-row and add/change/remove different values. Then I need to take all the modified rows and response.write them as JSON. I'm using JSON2.asp so my application already supports JSON.parse & JSON.stringify but I can't get it to spit out the multi-dimensional array as JSON.

set rs = conn.execute(strQuery)
if Not rs.EOF Then
    rsArray = rs.GetRows() 'This pulls in all the results of the RecordSet as a 2-dimensional array
    columnCount = ubound(rsArray,1)
    rowCount = ubound(rsArray,2)
    For rowIndex = 0 to rowCount 'Loop through rows as the outer loop
        rsArray(3,0) = "somethingelse"
    Next 'Move on to next row if there is one

    response.write JSON.stringify(rsArray) & " _______ "
End If

I just need to be able to go through the results of my database query, modify the results, and then output the modified results in JSON format. What's the right way to do this?

Answer 1

The JSON2.asp implementation doesn't have a "Load From Database" function which means you will have to implement something to convert the ADODB.Recordset to a JSON structure yourself.

If you are willing to use a different script there is an implementation by RCDMK on GitHub that does have a LoadRecordset() method, it's called JSON object class 3.5.3.

This makes loading data from an ADODB.Recordset really straightforward.

<!-- #include virtual="/jsonObject.class.asp" -->
<%
Response.LCID = 2057
'...
Dim rs: Set rs = conn.execute(strQuery)

Dim JSON: Set JSON = New JSONobject
Call JSON.LoadRecordset(rs)
Call Response.Clear()
Response.ContentType = "application/json"
Call JSON.Write()
%>

^{Code has been tested using a disconnected recordset, the ... here denote assumed code to setup your recordset, connection etc}

It's worth noting you could write this yourself, it's not a huge leap to loop through an ADODB.Recordset and build a JSON string. However, I would argue against for a few reasons;

1. It is a time-consuming exercise.
2. Very easy to miss something (like checking for numeric data types, when generating output).
3. Depending on how it is coded can make it awkward to maintain (For example, if not injecting property names directly from the recordset and choosing to "hardcode" them instead).
4. Why reinvent the wheel ? There are a lot of public implementations in the wild that deal with the issues raised here. Admittedly, some are better than others, but it takes five minutes to include them and give it a try.

---

Just for completeness here is my local test code using a disconnected recordset

<!-- #include virtual="/jsonObject.class.asp" -->
<%
Call init()

Sub init()
  Dim fields: fields = Array(Array("title", adVarChar, 50), Array("firstname", adVarChar, 50), Array("lastname", adVarChar, 50), Array("age", adInteger, 4))
  Dim rs: Set rs = Server.CreateObject("ADODB.Recordset")
  Call InsertRow(rs, fields, Array("Mr", "Joe", "Bloggs", 31))
  Call InsertRow(rs, fields, Array("Mr", "John", "Smith", 42))

  Response.LCID = 2057

  Dim JSON: Set JSON = New JSONobject
  Call JSON.LoadRecordset(rs)
  Call Response.Clear()
  Response.ContentType = "application/json"
  Call JSON.Write()
End Sub

Sub InsertRow(ByVal rs, fields, values)
  With rs
    If rs.State <> adStateOpen Then
      For Each fld In fields
        Call .Fields.Append(fld(0), fld(1), fld(2))
      Next

      .CursorLocation = adUseClient
      .CursorType = adOpenDynamic
      Call .Open()
    End If
    Call .AddNew()
    For i = 0 To UBound(fields, 1)
      .Fields(fields(i)(0)).Value = values(i)
    Next
    Call .Update()
    Call .MoveFirst()
  End With
End Sub
%>

Output:

{"data":[{"title":"Mr","firstname":"Joe","lastname":"Bloggs","age":31},{"title":"Mr","firstname":"John","lastname":"Smith","age":42}]}

Answer 2

try setting content-type to "application/json" on top of your asp page.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Option Explicit
Response.Buffer=True
Response.ContentType="application/json"
Response.Charset="utf-8"
'' rest of your code.. your db operations
'' response write your json
%>

Answer 3

Here ya go. This works for me.

set rs = conn.execute(strQuery)
c=0
Response.write "["
Do Until rs.eof 

    'Assign variables here with whatever you need to change 
    title = rs(0)
    fName = rs(1)
    lName = rs(2)
    empID = rs(3)

    With Response
        if c > 0 then .write ", "
        .write "{" & chr(34) & "Title" & chr(34) & " : " & chr(34) & title & chr(34) & ", " & chr(34) & "FirstName" & chr(34) & " : " & chr(34) & fName & chr(34) & ", "
        .write       chr(34) & "LastName" & chr(34) & " : " & chr(34) & lName & chr(34) & ", " & chr(34) & "EmpID" & chr(34) & " : " & chr(34) & empID & chr(34) & "}"
    End With

    c = c + 1
    rs.MoveNext
Loop
Response.write "]"

This will write your JSON object directly to the page.