Does your project need rescued? Nearly 74% of projects do. Click here to view our Guide to get your project on the right path!

JSON Delivery in Classic ASP

This post is a follow-on to https://eimagine.com/arrays-and-recordsets-in-classic-asp/ where we are introduced to some of the helper Classic ASP functions I use to retrieve and display data. The main difference between this post and the last is this one leaves the HTML table behind for JSON output, which is meant to be consumed by an AJAX client. We concentrate on delivering that JSON here.

Like the last post, we have two tables to use for source data. One has US States and some associated data and the other has cities and which state they are located in. In our last piece, we used query variables to determine which functions ran and which states were displayed. Here we will do the same thing but output JSON. Therefore, we’ll have a limited review of how the helper functions work before getting into the JSON implementation.

First, let’s go ahead and create and populate the two tables, all right in one go.

/*
Note this removes the table if it exists but assumes any object named tmpSprocDemoConCat is a table.
*/

if object_id(N'dbo.tmpTableDemo') is not null drop table dbo.[tmpTableDemo]

go

create table dbo.[tmpTableDemo]
(
	city nvarchar(100)
,	[state] nvarchar(2)
);

go

insert into dbo.[tmpTableDemo] values
('New York','NY')
,('Buffalo','NY')
,('Albany','NY')
,('Los Angeles','CA')
,('Santa Ana','CA')
,('Chico','CA')
,('Indianapolis','IN')
,('South Bend','IN')
,('Portland','ME')
,('Bar Harbor','ME');

go

/*
Note this removes the table if it exists but assumes any object named tmpSprocDemoConCat is a table.
*/

if object_id(N'dbo.tmpTableStateDemo') is not null drop table dbo.[tmpTableStateDemo]

go

create table dbo.[tmpTableStateDemo]
(
	stateAbbv nvarchar(2)
,	[stateName] nvarchar(100)
,	[stateCapital] nvarchar(100)
,	[stateLargestCity] nvarchar(100)
,	[statehoodYear] int
,	[statehoodRank] int
);

go

insert into dbo.[tmpTableStateDemo] values
('NY', 'New York','Albany','New York City',1788,11)
,('IN', 'Indiana','Indianapolis','Indianapolis',1816,19)
,('CA', 'California','Sacramento','Los Angeles',1850,31)
,('ME', 'Maine','Augusta','Portland',1821,23);

go

And now for the classic ASP.

<%@ Language=VBScript %>
<%
Server.ScriptTimeout = 720
Response.CacheControl = "no-cache" 
Response.ContentType = "application/json"
Response.Expires = -1 

function JSON_TABLE_OUT(strObjectName,arrFieldNames,arrFieldIsString,arrValues, indentAdd, rowDataOnOneLine)
	dim strOut, extraIndents
	dim fieldCount, innerCount, recordCount
	extraIndents = ExtraTabs(indentAdd)
	strOut = ""
	strOut = strOut & extraIndents & "{" & vbCrLf
	strOut = strOut & extraIndents & vbTab & chr(34) & strObjectName & chr(34) & ":"
	if isarray(arrValues) then
		strOut = strOut & vbCrLf & extraIndents & vbTab & "[" & vbCrLf
		for recordCount = 0 to ubound(arrValues,2)
			strOut = strOut & extraIndents & vbTab & vbTab & "{"
			if not rowDataOnOneLine then strOut = strOut & vbCrLf
			for fieldCount = 0 to ubound(arrFieldNames)
				if rowDataOnOneLine then
					strOut = strOut & """" & arrFieldNames(fieldCount) & """:"
				else
					strOut = strOut & extraIndents & vbTab & vbTab & vbTab & """" & arrFieldNames(fieldCount) & """:"
				end if
				if isarray(arrValues(fieldCount,recordCount)) then
					strOut = strOut & "["
					for innerCount = 0 to ubound(arrValues(fieldCount,recordCount))
						if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
						strOut = strOut & arrValues(fieldCount,recordCount)(innerCount) & ""
						if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
						if innerCount < ubound(arrValues(fieldCount,recordCount)) then strOut = strOut & ","
					next
					strOut = strOut & "]"
				else
					if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
					strOut = strOut & replace(arrValues(fieldCount,recordCount) & "",chr(34),"")
					if arrFieldIsString(fieldCount) then strOut = strOut & chr(34)
				end if
				if rowDataOnOneLine then
					if fieldCount < ubound(arrFieldNames) then strOut = strOut & ","
				else
					if fieldCount < ubound(arrFieldNames) then strOut = strOut & "," & vbCrLf
				end if
				
			next
			if rowDataOnOneLine then
				strOut = strOut & "}"
			else
				strOut = strOut & vbCrLf & extraIndents & vbTab & vbTab & "}"
			end if
			if recordCount < ubound(arrValues,2) then strOut = strOut & ","
			strOut = strOut & vbCrLf
		next
		strOut = strOut & extraIndents & vbTab & "]" & vbCrLf
	else
		strOut = strOut & "{}" & vbCrLf
	end if
	strOut = strOut & extraIndents & "}" & vbCrLf
	JSON_TABLE_OUT = strOut
end function

function ExtraTabs(numberOfIndents)
	dim strOut
	if numberOfIndents > 0 then
		strOut = space(numberOfIndents)
	else
		strOut = ""
	end if
	ExtraTabs = replace(strOut," ",vbTab)
end function

Function SqlErrorFound(dbConn,byval sourceError)
    Dim errCounter
    Dim errOut: errOut = False
    For errCounter = 0 To dbConn.Errors.Count - 1
        If dbConn.Errors(errCounter).Number <> 0 Then
            response.Write replace(sourceError,vbCrLf,"<br />",1,-1,1) & ": " & dbConn.Errors(errCounter).Description
            errOut = True
            Exit For
        End If
    Next
    SqlErrorFound = errOut
End Function

sub AllRecordsWithTitles(dbCon,strSql,byref allRecords,byref allTitles)
	dim rs
	dim adUseClient,adOpenStatic
	'on error resume next
	adUseClient = 3
	adOpenStatic = 2
	allRecords = empty
	allTitles = empty
	set rs = CreateObject("ADODB.recordset")
	rs.CursorLocation=adUseClient
	rs.CursorType=adOpenStatic
	rs.Open strSql,dbCon
	if err.number = 0 then
		do while rs.state=0 and err.number=0 and not sqlErrorFound(dbCon,strSql)
			set rs=rs.nextrecordset	
		loop
		if not rs.eof and err.number=0 then
			allRecords = rs.getrows
			for recordCounter = 0 to rs.fields.count-1
				redim preserve titles(recordCounter)
				titles(recordCounter) = rs.fields(recordCounter).name
			next
		elseif err.number = 0 then
			for recordCounter = 0 to rs.fields.count-1
				redim preserve titles(recordCounter)
				titles(recordCounter) = rs.fields(recordCounter).name
			next
		end if
	else
		response.write "Error pulling recordset System error: " & vbCrLf & err.Description & vbCrLf
		response.write "Error pulling recordset SQL statement: " & vbCrLf & strSql
	end if
	err.Clear
	rs.close
	set rs = nothing
	allTitles = titles
end sub

function SqlSafeString(byval strSql)
	strSql = replace(strSql,"'","''",1,-1,1)
	strSql = replace(strSql,vbCrLf, "" ,1,-1,1)
	strSql = replace(strSql,vbCr, "" ,1,-1,1)
	strSql = replace(strSql,vbCr, "" ,1,-1,1)
	SqlSafeString = strSql
end function

function StateName
	StateName = lcase(SqlSafeString(request.querystring("State").item))
end function

function functionName
	functionName = lcase(SqlSafeString(request.querystring("funcName").item))
end function

function AllOnOneRow
	dim strResult, bResult
	strResult = lcase(SqlSafeString(request.querystring("oneRecordOneRow").item))
	select case strResult
		case "yes", "y", "true", "t", "1" : bResult = true
		case else : bResult = false
	end select
	AllOnOneRow = bResult
end function

function TabNumber
	dim strResult
	strResult = lcase(SqlSafeString(request.querystring("tabs").item))
	if isnumeric(strResult) then
		TabNumber = strResult
	else
		TabNumber = 0
	end if
end function

function MakeAllFieldsText(arrFields)
	dim arrOut(), i
	redim arrOut(ubound(arrFields))
	for i = 0 to ubound(arrOut)
		arrOut(i) = true ' flag as text field
	next
	MakeAllFieldsText = arrOut
end function

Private sub JSONStateInfo(dbCon)
	dim strSql, allRecords, arrFields, arrIsText
	strSql = _
		"declare @state nvarchar(2);" & vbCrLf & _
		"set @state = '" & StateName & "';" & vbCrLf & _
		"select *" & vbCrLf & _
		"from dbo.[tmpTableStateDemo]" & vbCrLf & _
		"where case @state when '' then [StateAbbv] else @state end = [StateAbbv]"
	AllRecordsWithTitles dbCon,strSql,allRecords,arrFields
	arrIsText = MakeAllFieldsText(arrFields)
	response.write JSON_TABLE_OUT("CityAndStates",arrFields,arrIsText,allRecords, TabNumber, AllOnOneRow)
End sub

Private sub JSONCityStateInfo(dbCon)
	dim strSql, allRecords, arrFields, arrIsText
	strSql = _
		"declare @state nvarchar(2);" & vbCrLf & _
		"set @state = '" & StateName & "';" & vbCrLf & _
		"select *" & vbCrLf & _
		"from dbo.[tmpTableDemo]" & vbCrLf & _
		"where case @state when '' then [State] else @state end = [State]"
	AllRecordsWithTitles dbCon,strSql,allRecords,arrFields
	arrIsText = MakeAllFieldsText(arrFields)
	response.write JSON_TABLE_OUT("CityAndStates",arrFields,arrIsText,allRecords, TabNumber, AllOnOneRow)
End sub

Sub OpenDatabase (byref pConn)	
	dim connStr
	connStr = "Provider=SQLOLEDB.1;Data Source=(local)\ver2008;Initial Catalog=DBOA22;User ID=internet;Password=wwwinternet"
	Set pConn = Server.CreateObject("ADODB.Connection")
	pConn.ConnectionTimeout = 120
	pConn.CommandTimeout = 120
	
	pConn.Open connStr	
End Sub

Sub CloseDatabase (byref pConn)
	if pConn.State = 1 then  '-- if open
		pConn.close
	end if	
	Set pConn = Nothing
End Sub

OpenDatabase dbCon

select case functionName
	case "cities" : JSONCityStateInfo dbCon
	case "states" : JSONStateInfo dbCon
	case else : response.write "No function given"
end select

CloseDatabase dbcon
%>

Reviewing the purpose of Line 136’s “funcName”, this returns the value of the query variable “funcName”, which is used by the case statement down at line 215 to determine whether to return US State only data or city and state parings. Line 132, function “StateName”, should also be review as this is the query variable used to filter by state.

There are a few new functions, however. TabNumber on line 150 retrieves query variable “tabs” and is used by the new function “JSON_TABLE_OUT” to determine how many tabs of whitespace are needed to indent the JSON output. We’ll get into that. AllOnOneRow on line 140 is used to retrieve query variable “oneRecordOneRow”, and we’ll get into that, too.

Function MakeAllFieldsText on line 160 is used to take the field array returned by AllRecordsWithTitles (line 86) and make a new array the same size and populate it as TRUE. When true, this marks that particular field as being text. If a field is text, the value in JSON is enclosed in double quotes in our implementation. More information will be forthcoming.

ExtraTabs is a helper function to function JSON_TABLE_OUT and is used to implement the tab request from function TabNumber by inserting tabs into selected areas as required.

Subroutines JSONStateInfo and JSONCityStateInfo are simply renamed to reflect their use of JSON. In addition, instead of calling the function to output a table of data, these functions call the new function JSON_TABLE_OUT.

Also note on line 5 how our response type is fixed as application/json.

Finally, we get to our main function, JSON_TABLE_OUT, on line 8. This function is designed to process a table of information much like AllRecordsWithTitles would create and formulate white-spaced JSON output. Because JSON variables can be quoted or not, our routine takes this extra information as an array based on the field names array from our recordset. As mentioned before, function MakeAllFieldsText can take an array of field names and generate this text-or-not array of flags which can then be populated as needed. In our example, we treat all values as text.

JSON_TABLE_OUT also requires how much the full output should be indented from the left margin. This facilitates the output being integrated within a larger JSON set while matching that whitespace. Whitespace isn’t important for implementations but it certainly facilitates debugging and troubleshooting.

The last parameter accepted is a Boolean variable determining if row data should be on one line of output. We’ll play with this and see why this makes a difference in readability, but again this is only a formatting issue and does not affect how the JSON is ultimately read.

Assuming your file name is dummy3 on localhost, go to http://localhost/UFR_FL/dummy3.asp?funcName=cities and you should see something like this:

{
	"CityAndStates":
	[
		{
			"city":"New York",
			"state":"NY"
		},
		{
			"city":"Buffalo",
			"state":"NY"
		},
		{
			"city":"Albany",
			"state":"NY"
		},
		{
			"city":"Los Angeles",
			"state":"CA"
		},
		{
			"city":"Santa Ana",
			"state":"CA"
		},
		{
			"city":"Chico",
			"state":"CA"
		},
		{
			"city":"Indianapolis",
			"state":"IN"
		},
		{
			"city":"South Bend",
			"state":"IN"
		},
		{
			"city":"Portland",
			"state":"ME"
		},
		{
			"city":"Bar Harbor",
			"state":"ME"
		}
	]
}

Here you will see a repeating array of key-value pairs of city->state. Because there are so few columns but more than a few records, we might choose to have all records on one line. This can be facilitated by taking advantage of the query variable “oneRecordOneRow” which is read by function AllOnOneRow. Any value on line 144 is interpreted as true and will place each record on the same row. So try the following URL: http://localhost/dummy3.asp?funcName=cities&oneRecordOneRow=true

{
	"CityAndStates":
	[
		{"city":"New York","state":"NY"},
		{"city":"Buffalo","state":"NY"},
		{"city":"Albany","state":"NY"},
		{"city":"Los Angeles","state":"CA"},
		{"city":"Santa Ana","state":"CA"},
		{"city":"Chico","state":"CA"},
		{"city":"Indianapolis","state":"IN"},
		{"city":"South Bend","state":"IN"},
		{"city":"Portland","state":"ME"},
		{"city":"Bar Harbor","state":"ME"}
	]
}

That’s pretty convenient. If we want to tab this in two tab spaces, we can add the “tabs” query variable using this url: http://localhost/dummy3.asp?funcName=cities&oneRecordOneRow=true&tabs=2

Again, this might be useful if you are incorporating this JSON in a larger set of JSON. It has no affect on how it’s interpreted by the consuming JavaScript.

As you can see, it might be useful when viewing many records with few columns to have all records appear on one line. But what if you’re interested in one record with many columns?

Change the funcName value to “states” and add a query variable “state” with a value of “ny”; an example url: http://localhost /dummy3.asp?funcName=states&oneRecordOneRow=true&state=ny

{
	"CityAndStates":
	[
		{"stateAbbv":"NY","stateName":"New York","stateCapital":"Albany","stateLargestCity":"New York City","statehoodYear":"1788","statehoodRank":"11"}
	]
}

In this case, it might be nice to have all the fields appear on their own line because there are so many. Change the “oneRecordOneRow” value to false.

{
	"CityAndStates":
	[
		{
			"stateAbbv":"NY",
			"stateName":"New York",
			"stateCapital":"Albany",
			"stateLargestCity":"New York City",
			"statehoodYear":"1788",
			"statehoodRank":"11"
		}
	]
}

This is the kind of output that is easy for JavaScript using AJAX to consume. Next time, we’ll go into using this kind of data to update a page without having to post back and refresh, using JQuery.

Leave a Reply

Your email address will not be published. Required fields are marked *