The Dangerous Life of an OnDemand Grave Robber: Resurrecting Coghead Content for River

In Guerrilla Tactics for SAP’s OnDemand ‘Go to Market’ Strategy of the history of River – SAP’s new OnDemand PaaS platform, I found old documents, forum posts and other content from Coghead that provided interesting insights into River. I also found code in various forms (javascript, etc). 

One find was an old Excel Connector that allowed users to access their Coghead data via Microsoft Excel. There was also documentation for the connector.  This connector gave users the ability to read, update, and delete data in Coghead via Excel. Cool stuff. More out of curiosity than a real business need I decided to bring this connector back from its dark Internet grave. Of course, there are other ways to access your River data in Excel such as print templates (more on that feature in a later blog) but I was curious to see if I could resurrect the old code.

Bringing the Creature Back to Life

Obviously, River had changed since its Coghead origin but I decided to give it a shot – since the connector was based on VBA, I thought I could make the code changes necessary.  I was surprised at the amount of VBA code the connector contained.  What was of most interest to me was the code using the REST APIs of Coghead/River. By examining what the connector does, I was able to learn much about River’s REST APIs.

Using the VBA debugger, I found the problems (not an easy task) and made the code changes (see below for the changes) to get the connector running. The majority of the code changes concern XML data being returned from the REST APIs.

The Creature Unfinished

Creatures pulled unwillingly from the comfort of their grave often find that the world has changed. The resurrection of Coghead’s connector also revealed such changes. The old connector allowed users to create/update and delete records. River now requires a new AuthToken as a header. As much I tried I couldn’t figure out how to transfer this token between HTTP calls in VBA, I couldn’t find a solution.  The absence of this token leads to the error which is seen in the video.  Other developers more proficient in VBA might be able to get this feature working.

Note: The connector is also unstable on occasion – just like the Frankenstein monster – and has a tendency to go into endless loops with large datasets. So, use this connector at your own risk and don’t use it in productive settings (Such madness would be akin to letting the Frankenstein monster wander around in a candle-lit kindergarten)

The Lessons Learned of Dr Frankenstein

If you are interested in learning about River and how to use its REST API, look at the VBA code included in the old connector.

Although some might see the resurrection of old Coghead code as being unnatural and a slight against the current River environment (As someone recently said to me – “leave the past alone, concentrate on the future”), I’m very interested in understanding SAP’s OnDemand offerings – sometimes such explorations require getting one’s hands a little bloody.  Just as grave robbers dug up bodies to better understand human anatomy, I dug up the old Excel connector to learn about River’s internals.

VBA Code Changes

File: Collection.cls

Function CreateOrUpdateEntr

OLD:      

    If cell = “a” Then

NEW:

    If cell.Text = “a” Then

Function: GetViewColumnXPath

OLD:

GetViewColumnXPath = theViewDifinitionDOM.selectSingleNode(“ns3:ViewDefinition/ns3:SelectSpec/ns3:ReturnField[” & GetTheColumnIndex(columnHeaderStr) & “]/@fieldXPath”).Text

NEW:    

GetViewColumnXPath = theViewDifinitionDOM.selectSingleNode(“c:ViewDefinition/c:SelectSpec/nc:ReturnField[” & GetTheColumnIndex(columnHeaderStr) & “]/@fieldXPath”).Text

Function GetTheColumnIndex

OLD:

Set columnDefinitionNodeList = theViewDifinitionDOM.selectNodes(“ns3:ViewDefinition/ns3:LayoutSpec/ns3:TableDefinition/ns3:ColumnDefinition”)

NEW:

  Set columnDefinitionNodeList = theViewDifinitionDOM.selectNodes(“c:ViewDefinition/c:LayoutSpec/c:TableDefinition/c:ColumnDefinition”)

OLD:    

Dim tmpColumnHeader As String: tmpColumnHeader = tmpEle.selectSingleNode(“ns3:Name”).Text

NEW:

Dim tmpColumnHeader As String: tmpColumnHeader = tmpEle.selectSingleNode(“c:Name”).Text

File: ResponseViewData.cls

Function: GetTheRowDataAsArray

OLD:    

Set oneRowNode = xdoc.selectNodes(“ns1:GetViewContentsResponse/ns1:Row”).item(theRowIndex)

Dim eachColumnsData As IXMLDOMNodeList, documentIdNode As IXMLDOMNode

Set eachColumnsData = oneRowNode.selectNodes(“ns1:Column/ns1:Value”)

Set documentIdNode = oneRowNode.selectSingleNode(“ns1:Column/ns1:ObjectName[0]”)

NEW:   

Set oneRowNode = xdoc.selectNodes(“ns1:GetViewContentsResponse/vGVC:Row”).item(theRowIndex)

Dim eachColumnsData As IXMLDOMNodeList, documentIdNode As IXMLDOMNode

Set documentIdNode = oneRowNode.selectSingleNode(“vGVC:Column/vGVC:ObjectName[0]”)

Function: TheRowCounts

OLD:   

Set rowNodeList = xdoc.selectNodes(“ns1:GetViewContentsResponse/ns1:Row”)

NEW:

Set rowNodeList = xdoc.selectNodes(“ns1:GetViewContentsResponse/vGVC:Row”)