· Dec 8, 2023 2m read

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. 

Desired Approach

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 


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>"
  return html

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

Discussion (0)1
Log in or sign up to continue