Using Classic ASP – SQL – JSON

I’m trying to serialize a SQL query in Classic ASP to a json object and I have found some great code that helps me achieve that but where my issue comes in is I then need to manipulate the output that is returned by the QueryToJSON(cmd, arParams).Flush

I need to take the value and add some string / text to it in order for me then to post the returned JSON to API.

I was thinking simply create a variable add the results (QueryToJSON(cmd, arParams).Flush) to that, then I can manipulate / do what I need with it. However you can’t seem to bind QueryToJSON(cmd, arParams).Flush to a variable. It just ignores that, it appears that I can only print the output to screen and do nothing with it. The script is using Microsoft NorthWind sample DB but is easy to adapt to any database/table, I have added a test DB script/data below.

Below is all the source that I have found here https://www.mikesdotnetting.com/article/98/ajax-with-classic-asp-using-jquery and https://code.google.com/archive/p/aspjson/

Using this gets me so close to where I need to be. Any advice would be great, thanks for looking.

<% @LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>
<!--#include file="JSON_2.0.4.asp"-->
<%
Function QueryToJSON(dbcomm, params)
        Dim rs, jsa
        Set rs = dbcomm.Execute(,params,1)
        Set jsa = jsArray()
        Do While Not (rs.EOF Or rs.BOF)
                Set jsa(Null) = jsObject()
                For Each col In rs.Fields
                        jsa(Null)(col.Name) = col.Value
                Next
        rs.MoveNext
        Loop
        Set QueryToJSON = jsa
        rs.Close
End Function
%>
<% 
  strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=LOCALHOST;UID=sa;PWD=*******;DATABASE=NorthWind;"
  Set conn = Server.CreateObject("ADODB.Connection")
  conn.Open strConn
  query = "SELECT * FROM Customers WHERE CustomerID = ?"
  CustomerID = "ALFKI" 'Request.QueryString("CustomerID")
  arParams = array(CustomerID)
  Set cmd = Server.CreateObject("ADODB.Command")
  cmd.CommandText = query
  Set cmd.ActiveConnection = conn
 ' QueryToJSON(cmd, arParams).Flush


  'MY CODE TO MANIPULATE OUTPUT
  mystring = QueryToJSON(cmd, arParams).Flush

  response.write(mystring)


  conn.Close : Set Conn = Nothing
%>

Here is the latest JSON_2.0.4.asp

        End If
        Set Collection(p) = v
    End Property

    Public Default Property Get Pair(p)
        If IsNull(p) Then p = Count - 1
        If IsObject(Collection(p)) Then
            Set Pair = Collection(p)
        Else
            Pair = Collection(p)
        End If
    End Property
    ' -- pair
    Public Sub Clean
        Collection.RemoveAll
    End Sub

    Public Sub Remove(vProp)
        Collection.Remove vProp
    End Sub
    ' data maluplation

    ' encoding
    Function jsEncode(str)
        Dim charmap(127), haystack()
        charmap(8)  = "b"
        charmap(9)  = "t"
        charmap(10) = "n"
        charmap(12) = "f"
        charmap(13) = "r"
        charmap(34) = """"
        charmap(47) = "/"
        charmap(92) = "\"

        Dim strlen : strlen = Len(str) - 1
        ReDim haystack(strlen)

        Dim i, charcode
        For i = 0 To strlen
            haystack(i) = Mid(str, i + 1, 1)

            charcode = AscW(haystack(i)) And 65535
            If charcode < 127 Then
                If Not IsEmpty(charmap(charcode)) Then
                    haystack(i) = charmap(charcode)
                ElseIf charcode < 32 Then
                    haystack(i) = "u" & Right("000" & Hex(charcode), 4)
                End If
            Else
                haystack(i) = "u" & Right("000" & Hex(charcode), 4)
            End If
        Next

        jsEncode = Join(haystack, "")
    End Function

    ' converting
    Public Function toJSON(vPair)
        Select Case VarType(vPair)
            Case 0  ' Empty
                toJSON = "null"
            Case 1  ' Null
                toJSON = "null"
            Case 7  ' Date
                ' toJSON = "new Date(" & (vPair - CDate(25569)) * 86400000 & ")"    ' let in only utc time
                toJSON = """" & CStr(vPair) & """"
            Case 8  ' String
                toJSON = """" & jsEncode(vPair) & """"
            Case 9  ' Object
                Dim bFI,i 
                bFI = True
                If vPair.Kind Then toJSON = toJSON & "[" Else toJSON = toJSON & "{"
                For Each i In vPair.Collection
                    If bFI Then bFI = False Else toJSON = toJSON & ","

                    If vPair.Kind Then 
                        toJSON = toJSON & toJSON(vPair(i))
                    Else
                        If QuotedVars Then
                            toJSON = toJSON & """" & i & """:" & toJSON(vPair(i))
                        Else
                            toJSON = toJSON & i & ":" & toJSON(vPair(i))
                        End If
                    End If
                Next
                If vPair.Kind Then toJSON = toJSON & "]" Else toJSON = toJSON & "}"
            Case 11
                If vPair Then toJSON = "true" Else toJSON = "false"
            Case 12, 8192, 8204
                toJSON = RenderArray(vPair, 1, "")
            Case Else
                toJSON = Replace(vPair, ",", ".")
        End select
    End Function

    Function RenderArray(arr, depth, parent)
        Dim first : first = LBound(arr, depth)
        Dim last : last = UBound(arr, depth)

        Dim index, rendered
        Dim limiter : limiter = ","

        RenderArray = "["
        For index = first To last
            If index = last Then
                limiter = ""
            End If 

            On Error Resume Next
            rendered = RenderArray(arr, depth + 1, parent & index & "," )

            If Err = 9 Then
                On Error GoTo 0
                RenderArray = RenderArray & toJSON(Eval("arr(" & parent & index & ")")) & limiter
            Else
                RenderArray = RenderArray & rendered & "" & limiter
            End If
        Next
        RenderArray = RenderArray & "]"
    End Function

    Public Property Get jsString
        jsString = toJSON(Me)
    End Property

    Sub Flush
        If TypeName(Response) <> "Empty" Then 
            Response.Write(jsString)
        ElseIf WScript <> Empty Then 
            WScript.Echo(jsString)
        End If
    End Sub

    Public Function Clone
        Set Clone = ColClone(Me)
    End Function

    Private Function ColClone(core)
        Dim jsc, i
        Set jsc = new jsCore
        jsc.Kind = core.Kind
        For Each i In core.Collection
            If IsObject(core(i)) Then
                Set jsc(i) = ColClone(core(i))
            Else
                jsc(i) = core(i)
            End If
        Next
        Set ColClone = jsc
    End Function

End Class

Function jsObject
    Set jsObject = new jsCore
    jsObject.Kind = JSON_OBJECT
End Function

Function jsArray
    Set jsArray = new jsCore
    jsArray.Kind = JSON_ARRAY
End Function

Function toJSON(val)
    toJSON = (new jsCore).toJSON(val)
End Function
%>

SQL DB/TABLE/SAMPLE DATA

USE Test

CREATE TABLE "Customers" (
    "CustomerID" nchar (5) NOT NULL ,
    "CompanyName" nvarchar (40) NOT NULL ,
    "ContactName" nvarchar (30) NULL ,
    "ContactTitle" nvarchar (30) NULL ,
    "Address" nvarchar (60) NULL ,
    "City" nvarchar (15) NULL ,
    "Region" nvarchar (15) NULL ,
    "PostalCode" nvarchar (10) NULL ,
    "Country" nvarchar (15) NULL ,
    "Phone" nvarchar (24) NULL ,
    "Fax" nvarchar (24) NULL ,
    CONSTRAINT "PK_Customers" PRIMARY KEY  CLUSTERED 
    (
        "CustomerID"
    )
)
GO

INSERT "Customers" VALUES('ALFKI','Alfreds Futterkiste','Maria Anders','Sales Representative','Obere Str. 57','Berlin',NULL,'12209','Germany','030-0074321','030-0076545')
INSERT "Customers" VALUES('ANATR','Ana Trujillo Emparedados y helados','Ana Trujillo','Owner','Avda. de la Constitución 2222','México D.F.',NULL,'05021','Mexico','(5) 555-4729','(5) 555-3745')
INSERT "Customers" VALUES('ANTON','Antonio Moreno Taquería','Antonio Moreno','Owner','Mataderos  2312','México D.F.',NULL,'05023','Mexico','(5) 555-3932',NULL)
INSERT "Customers" VALUES('AROUT','Around the Horn','Thomas Hardy','Sales Representative','120 Hanover Sq.','London',NULL,'WA1 1DP','UK','[phone]','[phone]')
INSERT "Customers" VALUES('BERGS','Berglunds snabbköp','Christina Berglund','Order Administrator','Berguvsvägen  8','Luleå',NULL,'S-958 22','Sweden','0921-12 34 65','0921-12 34 67')
INSERT "Customers" VALUES('BLAUS','Blauer See Delikatessen','Hanna Moos','Sales Representative','Forsterstr. 57','Mannheim',NULL,'68306','Germany','0621-08460','0621-08924')
INSERT "Customers" VALUES('BLONP','Blondesddsl père et fils','Frédérique Citeaux','Marketing Manager','24, place Kléber','Strasbourg',NULL,'67000','France','88.60.15.31','88.60.15.32')
INSERT "Customers" VALUES('BOLID','Bólido Comidas preparadas','Martín Sommer','Owner','C/ Araquil, 67','Madrid',NULL,'28023','Spain','(91) 555 22 82','(91) 555 91 99')
INSERT "Customers" VALUES('BONAP','Bon app''','Laurence Lebihan','Owner','12, rue des Bouchers','Marseille',NULL,'13008','France','91.24.45.40','91.24.45.41')
INSERT "Customers" VALUES('BOTTM','Bottom-Dollar Markets','Elizabeth Lincoln','Accounting Manager','23 Tsawassen Blvd.','Tsawassen','BC','T2F 8M4','Canada','[phone]','[phone]')
Go

Answer

1. After start lines

   <% @LANGUAGE="VBSCRIPT" CODEPAGE="65001" %>
   <!--#include file="JSON_2.0.4.asp"-->
   

add line

<% Response.AddHeader "Content-Type","application/json;charset=utf-8" %>

2. Use set command

   Set Variable = QueryToJSON(cmd, arParams)
   

3. Add/Edit some values to/in Variable

   Variable(0).("ContactName") = "No one"
   Variable(1).("NewColumn") = "Secret Info"
   

( 0 – index of first element of Array. 1 – second… ect

Variable.Count - 1 = last index )

4. Flush Variable

   Variable.Flush
   

( Flush is jsCore method )

UPDATE

Replace in your script lines

  ' QueryToJSON(cmd, arParams).Flush

  'MY CODE TO MANIPULATE OUTPUT
   mystring = QueryToJSON(cmd, arParams).Flush

   response.write(mystring)

with this one chunk

    Set Variable = QueryToJSON(cmd, arParams)
    Variable(0).("ContactName") = "Changed value"
    Variable(0).("NewColumn") = "Secret Info"
    Variable.Flush