Showing posts sorted by relevance for query JSON. Sort by date Show all posts
Showing posts sorted by relevance for query JSON. Sort by date Show all posts

Tuesday, 16 January 2018

VBA - Parse JSON safer with JSON.Parse and not Eval

A popular post on this blog is about parsing JSON in VBA. I talk about the ScriptControl and using Eval but giving a security warning about how only to run it on JSON from trusted sources. Well, today I can give a safer code sample that avoids Eval and uses a recommended library from Douglas Crockford no less. This post focuses on the security loophole not for exploitation but for defence. I AM WARNING ABOUT AND NOT RECOMMENDING EVAL.

The following code has two subs Proof_That_ScriptControl_Eval_Executes_Injected_Javascript() which proves the security loophole by using code injected JSON to write a temp file to the user's temp directory. The other sub Attempt_To_Parse_Injected_Code_With_JSON_Parse_Throws_Error() demonstrates how an attempt to inject code into JSON through JSON.Parse throws an error.

The recommended library is now standard for modern browsers. To get the library into the ScriptControl we need to download from https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js. The code below does this and we an extra shim JSON_parse() to make it callable from VBA.

For more information one can consult the following links


Option Explicit

'* Tools->References
' MSScriptControl      Microsoft Script Control 1.0    C:\Windows\SysWOW64\msscript.ocx
' Scripting            Microsoft Scripting Runtime     C:\Windows\SysWOW64\scrrun.dll
' MSXML2               Microsoft XML, v6.0             C:\Windows\SysWOW64\msxml6.dll


Function CodeInjectingJson() As String
    CodeInjectingJson = "{""foo"":""bar"", a:(function(){(new ActiveXObject('Scripting.FileSystemObject'))." & _
                        "GetSpecialFolder(2).CreateTextFile('random.txt')." & _
                        "Write('Use JSON.parse instead');})()}"
End Function

Private Sub Proof_That_ScriptControl_Eval_Executes_Injected_Javascript()

    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    If fso.FileExists(Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt") Then
        fso.DeleteFile Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt"
    End If
    
    Debug.Assert Not fso.FileExists(Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt")
    
    Dim oSC As ScriptControl
    Set oSC = SC
    
    Stop
    Dim obj As Object
    Set obj = oSC.Eval("(" & CodeInjectingJson & ")")
    Stop
    
    Debug.Assert fso.FileExists(Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt")
    End ' <- need this to kill references and garbage collect
End Sub

Private Sub Attempt_To_Parse_Injected_Code_With_JSON_Parse_Throws_Error()

    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject
    
    If fso.FileExists(Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt") Then
        fso.DeleteFile Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt"
    End If


    Debug.Assert Not fso.FileExists(Environ$("USERPROFILE") & "\AppData\Local\Temp\random.txt")
    
    Dim oSC As ScriptControl
    Set oSC = SC
    
    'Stop
    Dim objSafelyParsed As Object
    Set objSafelyParsed = SC.Run("JSON_parse", CodeInjectingJson)
   
    
    'Details of error thrown {
    ' Err.Number=5022,
    ' Err.Description="Exception thrown and not caught",
    ' Err.Source="Microsoft JScript runtime error"
    ' }
    
    '* search for '5022' on https://docs.microsoft.com/en-us/scripting/javascript/reference/javascript-run-time-errors
    '* links to  https://docs.microsoft.com/en-us/scripting/javascript/misc/exception-thrown-and-not-caught
    '* also https://referencesource.microsoft.com/#Microsoft.JScript/Microsoft/JScript/JSError.cs,a0c5ae7e7c2dd23c,references
    Stop
End Sub

Private Function SC() As ScriptControl
    Static soSC As ScriptControl
    If soSC Is Nothing Then

        Set soSC = New ScriptControl
        soSC.Language = "JScript"
        
        '* https://stackoverflow.com/questions/45015/safely-turning-a-json-string-into-an-object
        soSC.AddCode GetJavaScriptLibrary("https://raw.githubusercontent.com/douglascrockford/JSON-js/master/json2.js")
        soSC.AddCode "function JSON_parse(sJson) { return JSON.parse(sJson); } "

    End If
    Set SC = soSC
End Function

Private Function GetJavaScriptLibrary(ByVal sURL As String) As String

    Dim xHTTPRequest As MSXML2.XMLHTTP60
    Set xHTTPRequest = New MSXML2.XMLHTTP60
    xHTTPRequest.Open "GET", sURL, False
    xHTTPRequest.send
    GetJavaScriptLibrary = xHTTPRequest.responseText

End Function
on 1 comment:
Labels: , , , , ,

Thursday, 22 June 2017

Parse JSON in Excel VBA

So I did quite a lot of work on StackOverflow answering my own questions about JSON parsing. I really ought to deposit some key code here on my blog as well.


Whilst I have praised Tim Hall's excellent JSON parsing code the following leverages Microsoft's own parser. Tim Hall's is better when you want to amend the object, i.e. read-write. If you are happy with read-only then the Microsoft parser could be a better option but there is one caveat in that Microsoft's parser will execute any Javascript embedded in the JSON and this is a security breach. But if you know the provenance of the JSON and know it to be free from Javascript then the following code will be of use to you.

Use ScriptControl.Eval to parse a JSON object in Excel VBA

In following code, I have given the canonical example from http://json.org/example.html. Paste this code into VBA, add Tools->References->Microsoft Script Control 1.0

Function JsonLiteral() As String
    Const sJSON As String = "{" _
        & " ""glossary"": {" _
        & " ""title"": ""example glossary""," _
        & " ""GlossDiv"": {" _
        & " ""title"": ""S""," _
        & " ""GlossList"": {" _
        & " ""GlossEntry"": {" _
        & " ""ID"": ""SGML""," _
        & " ""SortAs"": ""SGML""," _
        & " ""GlossTerm"": ""Standard Generalized Markup Language""," _
        & " ""Acronym"": ""SGML""," _
        & " ""Abbrev"": ""ISO 8879:1986""," _
        & " ""GlossDef"": {" _
        & " ""para"": ""A meta-markup language, used to create markup languages such as DocBook.""," _
        & " ""GlossSeeAlso"": [""GML"", ""XML""]" _
        & " }," _
        & " ""GlossSee"": ""markup""" _
        & " } } } } }"

    JsonLiteral = sJSON

    '* Requires Tools->References to Microsoft Script Control 1.0
    Dim oSC As ScriptControl
    Set oSC = New ScriptControl
    
    oSC.Language = "JScript"
    Dim obj As Object
    Set obj = oSC.Eval("(" + sJSON + ")")
    
    Stop
    'Look at the Locals window to see the structure of the JSON
End Function
You'll see once evaluated then the Locals object allows you to navigate the structure.

Use VBA.CallByName and hasOwnProperty to navigate parse structure

Some more examples show how you can use CallByName to get sub-objects and also elements of an array. Also shown here is hasOwnProperty which can be used for defensive programming. 

Private Sub CallByNameAndHasOwnProperty()

    Dim oScriptEngine As ScriptControl
    Set oScriptEngine = New ScriptControl
    oScriptEngine.Language = "JScript"

    Dim sJsonString(0 To 1) As String
    sJsonString(0) = "{'key1': 'value1'  ,'key2': { 'key3': 'value3' } }"
    sJsonString(1) = "[ 1234, 2345, 3456, 4567, 5678, 6789 ]"



    Dim objJSON(0 To 1) As Object
    Set objJSON(0) = oScriptEngine.Eval("(" + sJsonString(0) + ")")
    Set objJSON(1) = oScriptEngine.Eval("(" + sJsonString(1) + ")")

    Debug.Assert objJSON(0).hasOwnProperty("key1")
    Debug.Assert objJSON(0).hasOwnProperty("key2")

    Debug.Assert CallByName(objJSON(1), "length", VbGet) = 6
    Debug.Assert CallByName(objJSON(1), "0", VbGet) = "1234"
End Sub

Caveat- Don't Overuse ScriptControl!

In addition to the warning about buried JavaScript in JSON I have another warning regarding Microsoft ScriptControl. Although it may look like a brilliant keyhole interface into the world of Javascript execution in VBA it is stuck in a time warp. I have established that Script Control is limited to Javascript EcmaScript v.3. Whilst current is version 6. Don't think you can code up some Javascript in say Microsoft Visual Studio Code and then paste into the ScriptControl with AddCode because chances are you will hit an incompatibility. Also with the ScriptControl you cannot debug code.

I experimented with ScriptControl for some time and tried to push its boundaries, initially it was exciting but it can be frustrating. Search this blog for JSON posts for any updates.

UPDATES and FURTHER READING

on No comments:
Labels: , , , , ,

Friday, 12 January 2018

M - Microsoft Power Query for Excel Formula Language

It seems that Power Query allows all manner of data sources to be queried and participate in a join. This gives the salivating prospect of joining an SQL relational query to a parsed JSON document. If true then that is truly remarkable. There is a query syntax language called Microsoft Power Query for Excel Formula Language for which a 115 page specification is available though a quick tour is also available.

But there is nothing like some examples, and I will focus here on JSON parsing examples because they arise from calling REST APIs.

Giving a JSON literal and drill into structure

So this a classic piece of JSON from http://json.org/example.html and we will drill into the structure. So below one can see the let clause and the in clause .

The let clause

The let clause is for the workings/evaluation of the logic. It is a series of one or more steps; each step is comma separated, one stores the output of a step into a variable. In the example given we can see that navigating a JSON node's child is done by using square brackets, e.g. [glossary].

The in clause
The in clause is strange because its specifies what to output! Anyway, one specifies a variable found in the let clause. Typically one would need to use the Record.ToTable() function to output something tabular.


let
    Source = Json.Document("{ ""glossary"": { ""title"": ""example glossary"", ""GlossDiv"": { ""title"": ""S"", ""GlossList"": { ""GlossEntry"": 
        { ""ID"": ""SGML"", ""SortAs"": ""SGML"", ""GlossTerm"": ""Standard Generalized Markup Language"", ""Acronym"": ""SGML"",
          ""Abbrev"": ""ISO 8879:1986"", ""GlossDef"": { ""para"": ""A meta-markup language, used to create markup languages such as DocBook."",
          ""GlossSeeAlso"": [""GML"", ""XML""] }, ""GlossSee"": ""markup"" } } } } }"),
    glossary = Source[glossary],
    GlossDiv = glossary[GlossDiv],
    GlossList = GlossDiv[GlossList],
    GlossEntry = GlossList[GlossEntry],
    ConvertedToTable = Record.ToTable(GlossEntry)
in
    ConvertedToTable

Attempting to extract sub properties

Here we extract the details of owners from a Stack Overflow REST API fragment.


let
    Source = "{""items"":[
       {""tags"":[""vba"",""permissions""],""owner"":
             {""reputation"":49,""user_id"":9073241,""user_type"":""registered"",""accept_rate"":86,""display_name"":""Kam""},
        ""is_answered"":false,""view_count"":4,""answer_count"":0,""score"":0,""question_id"":48229549},
       {""tags"":[""excel"",""vba"",""excel-vba""],""owner"": 
             {""reputation"":18,""user_id"":9057704,""user_type"":""registered"",""accept_rate"":29,""display_name"":""Gregory""},
        ""is_answered"":false,""view_count"":6,""answer_count"":0,""score"":0,""question_id"":48229590}
       ]}",
    #"Parsed JSON" = Json.Document(Source),
    items = #"Parsed JSON"[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"owner"}, {"owner"}),
    #"Expanded owner" = Table.ExpandRecordColumn(#"Expanded Column1", "owner", {"reputation", "user_id", "user_type", "display_name"}, 
       {"reputation", "user_id", "user_type", "display_name"})
in
    #"Expanded owner"

Use a live REST API as source of data

So finally we can use a live source of data.


let
    Source = Web.Contents("https://api.stackexchange.com/2.2/questions?order=desc&sort=activity&tagged=VBA&site=stackoverflow"),
    #"Parsed JSON" = Json.Document(Source),
    items = #"Parsed JSON"[items],
    #"Converted to Table" = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"owner"}, {"Column1.owner"}),
    #"Expanded Column1.owner" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.owner", {"reputation", "user_id", 
                         "user_type", "profile_image", "display_name", "link", "accept_rate"}, {"Column1.owner.reputation", 
                          "Column1.owner.user_id", "Column1.owner.user_type", "Column1.owner.profile_image", 
                          "Column1.owner.display_name", "Column1.owner.link", "Column1.owner.accept_rate"})
in
    #"Expanded Column1.owner"

Investigation Notes

Someone set a huge bounty of 500 for a SO question which seems an outsized reward for solving an HTML download formatting glitch. I put my answer in here with a solution based on HTML DOM navigation. Clearly keen to see how my answers was faring I looked at the other answers. One other answer had a syntax that was new and strange to me, it was using a query with an OLE DB Provider of Microsoft.Mashup.OleDb.1

Googling for Microsoft.Mashup.OleDb.1 turns up path names of C:\Program Files (x86)\Microsoft Power Query for Excel which I did not have; so clearly I needed an install. Instructions for install are here at The Complete Guide to Installing Power Query - Excel Campus and here is the Microsoft download page.

After installing, I had a new PowerQuery tab on my Excel Menu. Unfortunately, changes to the Excel object model look to be from Excel 2016 versions onwards. So I cannot play with this in VBA code. No matter, I can use the GUI for the time being but where to start?

on No comments:
Labels: , ,

Monday, 22 January 2018

PowerQuery (M) to drill into Google Sheets API v.3 and extract sheet name and link url

So I am indebted to a fellow StackOverflow citizen who helped me over a Power Query problem. Powerquery looks like a great technology and has been in Excel since 2013 edition but not VBA scriptable until 2016 edition. I especially like the ability to drill into JSON documents and extract information. But I find Powerquery's syntax challenging and clealy I have yet to master it.

The use case in hand is calling the Google Sheets API (version 3, I know I should move to version 4) to get the 'master' details of a Google sheets workbook and then drill in to find the sheet details. Amongst the sheet details are the sheet name and the url of the sheet data itself. I have tidied an example structure and it is given below. The sheet name is at feed.entry[x].title.$t whilst the url for the sheet data is at feed.entry[x].link[y].href where x increments and y is 0. 


{ ""feed"": {""entry"": [ 
  {     ""title"": { ""$t"": ""1 Med"" },     ""link"": [ { ""href"": 
""https//removed1...."" } ]   }, 
  {     ""title"": { ""$t"": ""2 Dent"" },     ""link"": [ { ""href"": 
""https//removed2...."" } ]   }, 
  {     ""title"": { ""$t"": ""3 Vet"" },     ""link"": [  { ""href"": 
""https//removed3...."" }]   }
] } }

Powerquery is orientated towards outputting data onto a grid of cells. I asked for a query to give the sheet name and url for each of the three sheets, yielding a 3 row 2 column matrix. So, my thanks to Mike Honey who gave a correct working answer


let
    Source = Json.Document("{ ""feed"": {""entry"": [
  {     ""title"": { ""$t"": ""1 Med"" },     ""link"": [ { ""href"": ""https//removed1...."" } ]   },
  {     ""title"": { ""$t"": ""2 Dent"" },     ""link"": [ { ""href"": ""https//removed2...."" } ]   },
  {     ""title"": { ""$t"": ""3 Vet"" },     ""link"": [  { ""href"": ""https//removed3...."" }]   }
] } }"),
    feed = Source[feed],
    entry = feed[entry],
    #"Converted to Table" = Table.FromList(entry, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"title", "link"}, {"title", "link"}),
    #"Expanded title1" = Table.ExpandRecordColumn(#"Expanded Column2", "title", {"$t"}, {"$t"}),
    #"Expanded link" = Table.ExpandListColumn(#"Expanded title1", "link"),
    #"Expanded link1" = Table.ExpandRecordColumn(#"Expanded link", "link", {"href"}, {"href"})
in
    #"Expanded link1"

Here is the output

1 Medhttps//removed1
2 Denthttps//removed2
3 Vethttps//removed3

I would like to be able to compete with PowerQuery in regards to a declarative technology that avoids stepping through using CallByName on JScriptTypeInfo.

Over the course of the weekend I wrote some VBA to implement a declarative syntax and then I rewrote it in Javascript. The results work but I am not happy with this code and would like to get the jsonpath library to work in the ScriptControl. Nevertheless, depositing the code here to pick up later.

on No comments:
Labels: , , ,

Thursday, 17 January 2019

C# - RTD - Redis - Real Time Data on the worksheet

In this article I share C# code for a Real Time Data worksheet function callable using RTD(). It will call into a local instance of Redis and will tick in real time. This article assumes you have installed Redis.

Click here for separate Youtube window

So I am very much pleased with Redis as outlined in the two articles (here and here) I wrote earlier this month. I hope you have read those previous two articles , and/or watched the videos. Redis is exactly what I have been looking for, I want a machine wide data cache, not too heavyweight but accessible to multiple instances of Excel because it would be inefficient for multiple Excel sessions to draw down duplicate data from network servers. In my humble opinion, much better to draw the data down to one executable, Redis, and then have the Excel sessions call into Redis locally.

RTD Server

There are already examples online for how to write an RTD server in C# (including the superb Learning Tree blog post here) and they share a similar pattern in that they have a timer and the single data item that they return is typically a timestamp so they can show the timestamp moving. I started from those examples and then expanded the functionality. I want multiple data items. I also want to make use of the multiple arguments, for example supplying a JSON path to drill into a JSON document.

Introducing ExcelCoin

I do have a stockbroker but I'm not going to advertise their data. Instead, I invent a fake financial security called ExcelCoin. I write the ExcelCoin price to Redis as a scalar number. I also write a JSON meta document to Redis, which gives the timestamp; for financial securities it could also give trading volume etc. The VBA code for this is given below but it does rely on the C# component given even further below. So just hang on.

I've invented ExcelCoin just to fake a real-time data feed. If you already have a real-time data feed then you can use that to write prices, headlines or whatever to Redis by re-writing the code below.

[If running the code in Word then you need the Python class from this blog post to generate a random Normal observation. If running in Excel then the code uses Excel's WorksheetFunction]. 

Option Explicit

Private vExcelCoinPrice As Variant

Private moPythonNormsInv As Object
Private moRedisSocket As Object

Public Property Get RedisSocket() As Object
    If moRedisSocket Is Nothing Then
        
        '* see next blog post for codeo this server
        Set moRedisSocket = VBA.CreateObject("RedisRTDServerLib.RedisSocket")
        moRedisSocket.Initialize
    End If
    Set RedisSocket = moRedisSocket
End Property

Public Sub DisposeRedisSocket()
    If Not moRedisSocket Is Nothing Then
        moRedisSocket.Dispose
        Set moRedisSocket = Nothing
    
    End If
End Sub


Public Property Get PythonNormsInv2() As Object
    If moPythonNormsInv Is Nothing Then
        Set moPythonNormsInv = VBA.CreateObject("SciPyInVBA.PythonNormsInv")
    End If
    Set PythonNormsInv2 = moPythonNormsInv
End Property

Sub Test_VBAPyNormStdInv()
    Debug.Print PythonNormsInv2.PyNormStdInv(0.95)
End Sub

Sub ResetCoin()
    vExcelCoinPrice = 1.2
End Sub

Sub TestOnTime()
    
    '*
    '* initialize price of ExcelCoin
    '*
    If IsEmpty(vExcelCoinPrice) Then
        vExcelCoinPrice = 1.2
    End If

    Const dDrift As Double = 1.0001
    
    Dim dRectangular As Double
    dRectangular = Rnd(1)
    
    Dim dNormal2 As Double
    If WinWordVBA() Then
        dNormal2 = PythonNormsInv2.PyNormInv(dRectangular, 0, 0.001)
    ElseIf ExcelVBA() Then
        '=NORM.INV(0.95,0,0.001)
        Dim objApp As Object
        Set objApp = Application
        dNormal2 = objApp.WorksheetFunction.NormInv(dRectangular, 0, 0.001)
    
    End If
    
    Dim dLogNormal As Double
    dLogNormal = Exp(dNormal2)
    
    vExcelCoinPrice = vExcelCoinPrice * dLogNormal * dDrift
    Debug.Print vExcelCoinPrice

    '*
    '* call Redis to update price of Excel Coin, this is just a number (double)
    '*
    Dim oRedisSocket As Object
    Set oRedisSocket = RedisSocket
    Dim sResponse As String
    sResponse = oRedisSocket.SendAndReadReponse("SET USD/ExcelCoin " & CStr(vExcelCoinPrice) & vbCrLf)
    Dim vParsed As Variant
    vParsed = oRedisSocket.Parse(sResponse)
    
    '*
    '* also write the fuller meta document that carries timestamps, source URI etc.
    '*
    Dim sJsonDoc As String
    sJsonDoc = "{ ""timestamp2"": " & CDbl(Now()) & ", ""redisId"": ""USD/ExcelCoin/meta"" ,  ""point"": " & vExcelCoinPrice & " }"
    sJsonDoc = VBA.Replace(sJsonDoc, """", "\""")
    sResponse = oRedisSocket.SendAndReadReponse("SET USD/ExcelCoin/meta """ & sJsonDoc & """" & vbCrLf)
    vParsed = oRedisSocket.Parse(sResponse)
    
    '*
    '* set up next call
    '*
    DoEvents
    Call Application.OnTime(Format(Now() + CDate("00:00:02"), "dd/mmm/yyyy hh:mm:ss"), "TestOnTime")
End Sub

Function ExcelVBA() As Boolean
    On Error GoTo QuickExit
    Dim objApp As Object
    Set objApp = Application
    
    Dim objWbs As Object
    Set objWbs = objApp.Workbooks
    
    ExcelVBA = True
QuickExit:
End Function

Function WinWordVBA() As Boolean
    On Error GoTo QuickExit
    Dim objApp As Object
    Set objApp = Application
    
    Dim objActiveDocument As Object
    Set objActiveDocument = objApp.ActiveDocument
    
    WinWordVBA = True
QuickExit:
End Function

About the C# RTD Server Code

The listing is quite large and worth talking through. The project type is a C# assembly with the 'Register for COM interop' checkbox checked (found on the Build tab of the project properties). Registering COM components requires administrator rights so run Visual Studio with admin rights. In AssemblyInfo.cs I have set [assembly: ComVisible(true)]

The RTD interfaces are defined in the Excel type library. So one must add a reference to Microsoft.Office.Interop.Excel primary interop assembly (PIA).

Also, I do some JSON processing so I have added the Newtonsoft.Json package by using the Package Manager.

Code Reuse

I do try to write object orientated code. In the listing below, there are three major classes plus some minor helper classes. The code split is intended to promote code re-use. Firstly, there is the RTDServer class which houses the RTD interface implementation. Secondly, there is the RedisSocket class which houses code for interacting with Redis via Sockets. Hopefully those two will be quite reusable.

Thirdly, the RedisRTDServer class bridges the RTDServer and RedisSocket classes. This is less likely to be reusable as it is very much idiosyncratic to this application.

DebugView and Error Handling

One potential impediment to re-use is error handling. The RTD function will smother any errors, if I want to communicate an error I need to write it to a log or call the Win32 Api OutputDebugString function and view the message using the DebugView application from SysInternals. Also, I can pass back the error message so it is written to calling cell. In the code I do both.

To facilitate this I subclass the C# Exception class to give a DebugMonitorableException class.

Error handling can be quite a personal choice so feel free to take a different view on error handling to me.

COM Interfaces

Two of the three major classes, RedisSocket and RedisRTDServer also have COM interfaces which allows them to be callable from VBA. This promotes re-use. I like to develop classes in C# whilst writing unit tests in Excel VBA. This is frequently how my classes mature and evolve.

Singleton substitute for global variable

We all know that global variables are naughty and in fact impossible in C#. However, I needed a singleton instance of the RedisSocket class to be initialised and be available thereafter. I found that creating and disposing the class every time I wanted to read a price caused the RTD server code to freeze. Moving to a singleton instance solved this.

RTDServer implements IRTDServer

There is a standard interface IRTDServer that any RTD server class must implement. One can read the Microsoft official documentation here. Actually better to read this superb Learning Tree blog post here.

In fact that Learning Tree blog post is probably a better place to begin and once you have that running correctly you can return to this blog post to see how to implement multiple topics as served by Redis.

RedisSocket encapsulates Redis Sockets interface

The RedisSocket class encapsulates a minimalistic interface to Redis. There are more comprehensive C# Redis interface libraries if you want them but for this blog this code will serve. The code uses the .NET TcpClient class to send and receive bytes via Sockets to/from Redis. The code parses the response using a simple split function. It is based on some equally simple VBA code I wrote in previous blog posts. Nevertheless it serves. I won't comment further on it here. See the previous two Redis blog posts for more details.

RedisRTDServer houses application specific code

As mentioned above, in order to make the RedisSockets and RTDServer classes as re-usable as possible I felt it necessary to offload as much as possible into a third class, RedisRTDServer, which houses all the code idiosyncratic to this application. It is called by the RTDServer class and is handed an array of strings as parameters.

The RedisRTDServer class interprets this array of strings. This is the role of the Router() method. The first string is the key; for the ExcelCoin the key is 'USD/ExcelCoin'. The second string specifies a handler which allows different processing logic, so if I want a single price then I supply "scalar" as the handler or indeed just an empty string ("").

I have added one other handler option, "json" and in this use case the key identifies a JSON document the third string is interpreted as a JSON path which allows a drill down into the JSON document.

Conceivably, in the future I could add another option "xml" and allow an XPath expression to be supplied to drill down into an Xml document.

I think you'll agree, there is plenty of scope to define a detailed interface.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Net.Sockets;
using System.Runtime.InteropServices;
using System.Timers;
using System.Text;
using System.Reflection;

// Added Reference to Microsoft Excel 15
using Microsoft.Office.Interop.Excel;
using Newtonsoft.Json.Linq;  // I used Package Manager to add Newtonsoft.Json

namespace RedisRTDServerLib
{
    [ComVisible(false)]
    static class Win32Api
    {   // this is so I can use DebugView from SysInternals
        [DllImport("kernel32.dll")]
        public static extern void OutputDebugString(string lpOutputString);
    }

    [ComVisible(false)]
    static class Singletons
    {   // this Singleton class is in lieu of a global variable
        static RedisSocket _redisSocket = null;

        static public RedisSocket RedisSocketSingleton
        {
            get
            {
                if (_redisSocket == null)
                {
                    _redisSocket = new RedisSocket();
                    _redisSocket.Initialize();
                }
                return _redisSocket;
            }
            set
            {
                // only interested in this for tidy up purposes
                if (value == null)
                {
                    _redisSocket = value;
                }
            }
        }
    }

    [ComVisible(false)]
    public class DebugMonitorableException : Exception
    {   // this class does the same as an Exception but simply prints the error message to the debug monitor
        public DebugMonitorableException()
        {
        }

        public DebugMonitorableException(string message) : base(message)
        {
            Win32Api.OutputDebugString(message);
        }

        public DebugMonitorableException(string message, Exception inner) : base(message, inner)
        {
            Win32Api.OutputDebugString(message);
        }
    }

    [ComVisible(true)]
    public interface IRedisSocket
    {
        void Initialize();
        void Dispose();
        string SendAndReadReponse(string sCommand);
        object Parse(string sRaw);
    }
    [ClassInterface(ClassInterfaceType.None), ComVisible(true)]
    [ComDefaultInterface(typeof(IRedisSocket))]
    public class RedisSocket : IRedisSocket, IDisposable
    {
        TcpClient _client;

        public void Initialize()
        {
            try
            {
                _client = new TcpClient();
                _client.Connect("127.0.0.1", 6379);
            }
            catch (Exception ex)
            {
                throw new DebugMonitorableException("Trapped error: " + ex.Message);
            }
        }

        public void Dispose()
        {
            _client.Close();

            _client.Dispose();
            _client = null;
        }

        public string SendAndReadReponse(string sCommand)
        {
            try
            {
                //TcpClient client = new TcpClient();
                NetworkStream stream;

                stream = _client.GetStream();

                Byte[] sendBytes = Encoding.UTF8.GetBytes(sCommand);
                stream.Write(sendBytes, 0, sendBytes.Length);
                stream.Flush();

                Byte[] recvBytes = new byte[_client.ReceiveBufferSize];
                stream.Read(recvBytes, 0, recvBytes.Length);

                string result = Encoding.UTF8.GetString(recvBytes);


                string result2 = result.Substring(0, result.LastIndexOf("\r\n"));
                return result2;
            }
            catch (Exception ex)
            {
                throw new DebugMonitorableException("SendAndReadReponse error: " + ex.Message);
            }
        }

        public object Parse(string sResponse)
        {
            try
            {
                Int32 lTotalLength = sResponse.Length;
                Debug.Assert(lTotalLength > 0);

                string[] vSplitResponse;
                {
                    string[] splitStrings = { "\r\n" };
                    vSplitResponse = sResponse.Split(splitStrings, StringSplitOptions.None);
                }

                Int32 lReponseLineCount = vSplitResponse.Length;

                switch (sResponse.Substring(0, 1))
                {
                    case "$":
                        if (sResponse == "$-1")
                        {
                            throw new Exception("Not found!");
                        }
                        else
                        {
                            return vSplitResponse[1];
                        }
                    case "+":
                        return vSplitResponse[0].Substring(1);
                    case ":":

                        //'* response is a numeric
                        return Double.Parse(vSplitResponse[0].Substring(1));
                    case "-":
                        //'* response is an error
                        throw new Exception(vSplitResponse[0].Substring(1));
                    case "*":
                        //'* multiple responses, build an array to return
                        Int32 lResponseCount = Int32.Parse(vSplitResponse[0].Substring(1));
                        if (lResponseCount > 0)
                        {
                            Debug.Assert(lResponseCount == (lReponseLineCount - 1) / 2);
                            List<Object> returnList = new List<Object>();
                            for (Int32 lLoop = 0; lLoop < lResponseCount; lLoop++)
                            {
                                returnList.Add(vSplitResponse[(lLoop + 1) * 2]);
                            }
                            return returnList.ToArray();
                        }
                        else
                        {
                            return false;
                        }
                    default:
                        // '* this should not happen
                        throw new Exception("Unrecognised return type '" + sResponse.Substring(0, 1) + "'");
                }
            }
            catch (Exception ex)
            {
                throw new DebugMonitorableException("Parse error: " + ex.Message);
            }
        }
    }

    [ComVisible(true)]
    public interface IRedisRTDServer
    {
        object Router(Array Strings);
        string GetJsonDocOrError(Array Strings);
        Boolean GetJSON(string sKey, string path, out string json, out string errorMsg);
        string GetKey(Array Strings);
        string GetPath(Array Strings);
        string GetHandler(Array Strings);
        object GetScalarOrError(Array Strings);
        Boolean GetScalar(string sKey, out double scalar, out string errorMsg);
    }

    [ClassInterface(ClassInterfaceType.None), ComVisible(true)]
    [ComDefaultInterface(typeof(IRedisRTDServer))]
    public class RedisRTDServer : IRedisRTDServer
    {
        public object Router(Array Strings)
        {
            try
            {
                string handler = GetHandler(Strings);
                string path = GetPath(Strings);

                switch (handler)
                {
                    case "":
                    case "scalar":
                        return GetScalarOrError(Strings);
                        
                    case "json":
                        return GetJsonDocOrError(Strings);
                        
                    default:
                        return "Failed to request with handler '" + handler + "'";
                }
            }
            catch (Exception ex)
            {
                string errMsg = this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + " error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                return errMsg;
            }
        }

        public string GetJsonDocOrError(Array Strings)
        {
            try
            {
                string sKey = GetKey(Strings);
                string path = GetPath(Strings);
                if (GetJSON(sKey, path, out string json, out string errMsg))
                {
                    return json;
                }
                else
                {
                    return errMsg;
                }
            }
            catch (Exception ex)
            {
                string errMsg = this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + " error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                return errMsg;
            }
        }

        public Boolean GetJSON(string sKey, string path, out string json, out string errorMsg)
        {

            json = "";
            errorMsg = "";
            try
            {

                object parsed = null;

                try
                {
                    RedisSocket redisSocket = null;
                    redisSocket = Singletons.RedisSocketSingleton;
                    string rawResponse = redisSocket.SendAndReadReponse("GET " + sKey + "\r\n");
                    parsed = redisSocket.Parse(rawResponse);
                }
                catch (Exception ex)
                {
                    // for the time being I'm not rethrowing this
                    errorMsg = ex.Message;
                    return false;
                }
                if (parsed is String)
                {
                    json = (string)parsed;

                    if (path.Length == 0)
                    {
                        return true;
                    }
                    else
                    {   // NewtonSoft.JSON code to drill into a JSON document with a JSON path
                        JObject o = JObject.Parse(json);
                        JToken acme = o.SelectToken(path);
                        json = acme.ToString();
                        return true;
                    }
                }
                else
                {
                    errorMsg = "Not a JSON document!";
                    return false;
                }

            }
            catch (Exception ex)
            {
                throw new DebugMonitorableException(this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + ": error\r\n" + ex.Message);
            }
        }

        public string GetKey(Array Strings)
        {
            try
            {
                string sKey;
                sKey = (string)Strings.GetValue(0);
                if (sKey.Contains(" "))
                {
                    sKey = "'" + sKey + "'";
                }
                return sKey;
            }
            catch (Exception ex)
            {
                string errMsg = this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + " error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                throw new Exception(errMsg);
            }
        }

        public string GetPath(Array Strings)
        {
            try
            {
                string path = "";
                {
                    if (Strings.Length >= 3)
                    {
                        path = (string)Strings.GetValue(2);
                    }
                }
                return path;
            }
            catch (Exception ex)
            {
                string errMsg = this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + " error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                throw new Exception(errMsg);
            }
        }

        public string GetHandler(Array Strings)
        {
            try
            {
                string handler = "";
                {
                    if (Strings.Length >= 2)
                    {
                        handler = (string)Strings.GetValue(1);
                    }
                }
                return handler;
            }
            catch (Exception ex)
            {
                string errMsg = this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + " error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                throw new Exception(errMsg);
            }
        }

        public object GetScalarOrError(Array Strings)
        {
            try
            {
                string sKey = GetKey(Strings);

                if (GetScalar(sKey, out double price, out string errMsg))
                {
                    return price;
                }
                else
                {
                    return errMsg;
                }
            }
            catch (Exception ex)
            {
                string errMsg = this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + " error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                return errMsg;
            }
        }

        public Boolean GetScalar(string sKey, out double scalar, out string errorMsg)
        {

            scalar = 0;
            errorMsg = "";
            try
            {

                object parsed = null;

                try
                {
                    RedisSocket redisSocket = null;
                    
                    redisSocket = Singletons.RedisSocketSingleton;
                    string rawResponse = redisSocket.SendAndReadReponse("GET " + sKey + "\r\n");
                    parsed = redisSocket.Parse(rawResponse);
                }
                catch (Exception ex)
                {
                    // for the time being I'm not rethrowing this
                    errorMsg = ex.Message;
                    return false;
                }
                if (parsed is String)
                {
                    string parsedString = (string)parsed;
                    if (!Double.TryParse(parsedString, out scalar))
                    {   
                        throw new DebugMonitorableException(this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + ": could not parse '" + parsedString + "' to double");
                    }
                }

                return true;
            }
            catch (Exception ex)
            {
                throw new DebugMonitorableException(this.GetType().Name + '.' + MethodBase.GetCurrentMethod() + ": error\r\n" + ex.Message);
            }
        }
    }


    public class RTDServer : IRtdServer
    {
        private Dictionary<int, Array> _topicsAndStrings = new Dictionary<int, Array>();
        private IRTDUpdateEvent m_callback;
        private Timer m_timer;
        private int m_topicId;

        int IRtdServer.ServerStart(IRTDUpdateEvent CallbackObject)
        {
            try
            {
                m_callback = CallbackObject;
                m_timer = new Timer();
                m_timer.Elapsed += new ElapsedEventHandler(TimerEventHandler);
                m_timer.Interval = 3000; // in milliseconds
                return 1;
            }
            catch (Exception ex)
            {
                Win32Api.OutputDebugString("IRtdServer.ServerStart:" + ex.Message);
                return 0;
            }
        }

        dynamic IRtdServer.ConnectData(int TopicID, ref Array Strings, ref bool GetNewValues)
        {
            try
            {
                _topicsAndStrings.Add(TopicID, Strings);

                m_topicId = TopicID;
                m_timer.Start();
                return GetTime();
            }
            catch (Exception ex)
            {
                string errMsg = "IRtdServer.ConnectData error:" + ex.Message;
                Win32Api.OutputDebugString(errMsg);
                return errMsg;
            }
        }

        private string GetTime()
        {
            return "RTD Server GetTime method: " + DateTime.Now.ToString("hh: mm:ss");
        }

        private void TimerEventHandler(object sender, EventArgs args)
        {
            try
            {
                // UpdateNotify is called to inform Excel that new data are available
                // the timer is turned off so that if Excel is busy, the TimerEventHandler is not called repeatedly

                m_timer.Stop();
                m_callback.UpdateNotify();
            }
            catch (Exception ex)
            {
                Win32Api.OutputDebugString("TimerEventHandler:" + ex.Message);
            }
        }

        Array IRtdServer.RefreshData(ref int TopicCount)
        {
            object[,] data = null;
            try
            {
                TopicCount = _topicsAndStrings.Count;
                data = new object[2, TopicCount];

                RedisRTDServer redisRtdServer = new RedisRTDServer();
                int idx = 0;
                foreach (var kvp in _topicsAndStrings)
                {
                    Array Strings = kvp.Value;
                    data[0, idx] = kvp.Key;
                    data[1, idx] = redisRtdServer.Router(kvp.Value);
                    idx++;
                }

                m_timer.Start();
                return data;
            }
            catch (Exception ex)
            {
                Win32Api.OutputDebugString("IRtdServer.RefreshData:" + ex.Message);
                return data;
            }
        }

        void IRtdServer.DisconnectData(int TopicID)
        {
            try
            {
                if (_topicsAndStrings.ContainsKey(TopicID))
                {
                    _topicsAndStrings.Remove(TopicID);
                }

                if (_topicsAndStrings.Count == 0)
                {
                    m_timer.Stop();
                    Singletons.RedisSocketSingleton = null;
                }
            }
            catch (Exception ex)
            {
                Win32Api.OutputDebugString("IRtdServer.DisconnectData:" + ex.Message);
            }
        }

        int IRtdServer.Heartbeat()
        {
            return 1;
        }

        void IRtdServer.ServerTerminate()
        {
            try
            {
                if (null != m_timer)
                {
                    m_timer.Dispose();
                    m_timer = null;
                }
                if (null != Singletons.RedisSocketSingleton)
                {
                    Singletons.RedisSocketSingleton = null;
                }
            }
            catch (Exception ex)
            {
                Win32Api.OutputDebugString("IRtdServer.ServerTerminate:" + ex.Message);
            }
        }
    }
}

Examples of calling from the worksheet

So finally, to call from the worksheet you need some cell formulas 

=RTD("redisrtdserverlib.rtdserver",,"USD/ExcelCoin","","")
=RTD("redisrtdserverlib.rtdserver",,"USD/ExcelCoin/meta","json","$.timestamp2")+0
on No comments:
Labels: , , , , , ,
Subscribe to: Posts (Atom)