SearchThis 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.
