Business Service to Query Internal IRIS database

IRIS has the likes of SQL inbound adapters for use with SQL gateways such as EnsLib.SQL.InboundAdapter to repeatedly query SQL Gateway connections. A scenario appeared as that we wanted to query an Internal database for some data but did not see an out of the box service for this. 

Have a Generic service that can poll internal SQL to work with downstream components.

What was not clear was "How do I send a result set downstream". It was not very clear as a resultset itself is not a peristent class and the object cannot be "Swizzled" an error like so 

 <METHOD DOES NOT EXIST>zNewRequestMessage+4 ^Ens.MessageHeader.1 *%GetSwizzleObject,%sqlcq.SRFT.cls535 -- logged as '-'
number - @''

The solution was using the object 

EnsLib.SQL.Snapshot

This can then be used as a business operation to send a resultset downstream in using the function Import from resultset

set result=##class(EnsLib.SQL.Snapshot).%New()
// Some SQL query here resulting in resultset where rset is the resultset object
set tSC=result.ImportFromResultSet(rset)

You can then send this on to another operation 

set tSC=..SendRequestAsync(..ForwardComponentName,result,0)  Quit:$$$ISERR(tSC)

Note in the code uploaded to open exchange available here via github . The example is you can open it up and query it. The below is the classmethod that is used to put into a html. This differs from released example slightly as is taken from a live implementation. 

ClassMethod GetDataTable(pRequest As EnsLib.SQL.Snapshot, html As %String) As %String
{
  //first html obj can be if the styling needs passed
  if $ISOBJECT(html){set html=""}
  //loop get column titles 
  set ColumnIteration=1
  set ColumnCount=pRequest.%ResultColumnCountGet()
  
  set html=html_" <table class=""tg"">"
  set html= html_ " " _"<tr>"
  set meta=pRequest.%GetMetadata() //this is like raw text of the result using it to get the column titles out
  if ColumnCount>0{
    while ColumnIteration<=ColumnCount{
      
      set html= html_ " <th>"_  meta.columns.GetAt(ColumnIteration).colName _" </th>"
      set ColumnIteration=ColumnIteration+1
    }

  }
  set html= html_ " " _"</tr>"
  //not get the data from each row. In html need a <tr> and a td. 
  set coldataiteration=1
  While pRequest.%Next() {
    set html= html_ " <tr>"
    while coldataiteration <=ColumnCount{
      set html= html_ " <td> "_pRequest.%GetData(coldataiteration) _" </td>"
      set coldataiteration=coldataiteration+1
    }
    
    set html= html_ " </tr>"
    set coldataiteration=1
  }
  set html= html_ " " _"</table>"
  $$$TRACE(html)
  return html
}

Sparkei/Internal-SQL-Service: Intersystems service that can be used to query an internal SQL table to send a snapshot downstream (github.com)