Are you are suddenly sitting at home with nothing to do, due to the corona virus COVID-19? You can’t go to the gym. You can’t go to your favorite computer store to browse all the latest hardware and plan for your next water-cooled build. You can’t go out to eat.
But what you can do is to try out some of the new functionality in HCL Notes and Domino. Today I started a little project where I try to incorporate a number of the latest and coolest functions in a simple but useful application. I hope to be able to create several blog posts about this project over the next few days.
I came up with the idea for this app when someone sent me a link to a web page where information about COVID-19 is aggregated from all US states. I thought it was a neat page, but then I noticed that they have a public API where the numbers are served up. Now things started to become much more interesting! This is what the JSON data looks like:
[
{
"state": "AK",
"positive": 6,
"negative": 400,
"pending": null,
"death": null,
"total": 406,
"lastUpdateEt": "3/18 16:30",
"checkTimeEt": "3/19 15:12"
},{
"state": "AL",
"positive": 68,
"negative": 28,
"pending": null,
"death": 0,
"total": 96,
"lastUpdateEt": "3/19 10:45",
"checkTimeEt": "3/19 15:15"
},{
"state": "AR",
"positive": 46,
"negative": 310,
"pending": 113,
"death": null,
"total": 469,
"lastUpdateEt": "3/19 11:23",
"checkTimeEt": "3/19 15:16"
},{
"state": "AS",
"positive": 0,
"negative": null,
"pending": null,
"death": 0,
"total": 0,
"lastUpdateEt": "3/14 00:00",
"checkTimeEt": "3/19 16:18"
},{
"state": "AZ",
"positive": 44,
"negative": 175,
"pending": 130,
"death": 0,
"total": 349,
"lastUpdateEt": "3/19 00:00",
"checkTimeEt": "3/19 15:18"
},{
"state": "CA",
"positive": 924,
"negative": 8787,
"pending": null,
"death": 18,
"total": 9711,
"lastUpdateEt": "3/19 14:25",
"checkTimeEt": "3/19 15:20"
}
...
So what could I do with this data? Why not bring it into a Domino database to start with, and then retrieve the data on a schedule, say every hour?
We should then be able to chart the data for each state over time. In order to not store the same data over and over again, I want to check if the data has been modified since the last time the agent ran. I will just use the lastUpdateEt date and time stamp in combination with the state abbreviation to perform a lookup. If I get any result(s) back, the data was already stored.
So how do you read the JSON from the API? In the past I would have used my own HTTP Request class, but this is not needed anymore. This is thanks to the NotesHTTPRequest class, first introduced in Domino 10 and then improved in Domino 11. In Domino 11 the wizards at HCL in Chelmsford added classes to parse JSON. The NotesJSONNavigator is the base of the parser, then you use NotesJSONArray, NotesJSONObject and NotesJSONElement to traverse through a JSON payload. When you get the hang of it, this is much easier than it maybe sounds at first.
So let’s take a look at my code. This is a scheduled agent, running once an hour:
%REM
Agent Retrieve Data
Created Mar 19, 2020 by Karl-Henry Martinsson/DBS
%END REM
Option Public
Option Declare
Sub Initialize
Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim http As NotesHTTPRequest
Dim json As NotesJSONNavigator
Dim element As NotesJSONElement
Dim stateArray As NotesJSONArray
Dim state As NotesJSONObject
Dim response As Variant
Dim url As String
Set db = session.CurrentDatabase
Set view = db.GetView("LookupExisting")
Call view.Refresh()
Set http = session.CreateHTTPRequest()
url = "https://covidtracking.com/api/states"
response = http.get(url)
Set json = session.CreateJSONNavigator(response)
Set element = json.GetFirstElement()
Do Until element Is Nothing
Set state = element.Value
Call processState(state, db, view)
Set element = json.GetNextElement()
Loop
End Sub
Function processState(state As NotesJSONObject, db As NotesDatabase, view As NotesView)
Dim doc As NotesDocument
Dim col As NotesViewEntryCollection
Dim values List As String
Dim element As NotesJSONElement
Dim key As String
Dim value As String
Dim stateName As String
Dim lastUpdate As String
Set doc = New NotesDocument(db)
doc.Form = "StateData"
Set element = state.GetFirstElement()
Do Until element Is Nothing
key = element.Name
value = element.Value
If key="state" Then
stateName = value
End If
If Right$(key,2)="Et" Then
lastUpdate = Format$(CDat(value),"mm/dd/yyyy hh:nn AM/PM")
Call doc.ReplaceItemValue("lastUpdated", lastUpdate)
Else
Call doc.ReplaceItemValue(key, value)
End If
Set element = state.GetNextElement()
Loop
Set col = view.GetAllEntriesByKey(stateName+"^"+lastUpdate)
If col.count=0 Then
Call doc.Save(True,False)
End If
End Function
This is all the code you need. Yes, I am serious. You can now consume any data on the web, served up by any system with a REST API, straight into Domino, with just a few lines for code.
The NotesHTTPRequest is very straight forward, so there is not much to say about it. But the classes used to parse JSON may need some explanation.
You start with the NotesJSONNavigator. You then use the value property of the NotesJSONElement class to get a value, an array or an object. The array or object is put into a NotesJSONArray or NotesJSONObject object, and you can then traverse down into the JSON structure. This is very powerful and useful, we have all been asking IBM for this functionality for many years. Now HCL delivers!
I created a hidden view for the lookup to avoid the same data stored multiple times. It only contains one (sorted) column, which is used by the lookup:
That is pretty much it.
I also created a view to display the data:
Soon we will do something more fun with the data. I will just let the scheduled agent run for a while and build up my database first. Keep your eyes open for the next post about this project!
