What is the best approach to insert data to an external SQL database?

Hey All!

I wanted to reach out and get some advice on the best way to interact with a external SQL database, as I'm finding the documentation quite confusing to follow. I'm new to using SQL within InterSystem, and I want to make sure I'm following the best practices.

In essence, what I am trying to achieve, is extracting certain values from a HL7 message and then insert those message field values into an external database. The ODBC, DSN and Credentials details are all setup and working correctly.

The way that I've currently achieved this is by doing the following:

• I create a new class that contains the properties that I want to save and reference when calling the stored procedure

Class JWDEV.SQLTesting.PatientDetails Extends Ens.Request 
{ 

Property name As %String; 

Property mrn As %String; 

Storage Default 
{ 
	<Data name="InsertDataDefaultData"> 
	<Subscript>"InsertData"</Subscript> 
	<Value name="1"> 
	<Value>name</Value> 
	</Value> 
	<Value name="2"> 
	<Value>mrn</Value> 
	</Value> 
	</Data> 
	<DefaultData>InsertDataDefaultData</DefaultData> 
	<Type>%Storage.Persistent</Type> 
} 

} 

• I create a new custom business process extending the "Ens.BusinessProcess" class.

  • This class takes the HL7 message, and stores relevant message fields to the PatientDetails request object.
  • Once the mrn and name values are saved to the req properties, it then passes over the req object to the "InsertPatientData" business operation

  Class JWDEV.SQLTesting.SQLBusinessProcess Extends Ens.BusinessProcess 
  { 
      Method OnRequest(request As EnsLib.HL7.Message, response As JWDEV.SQLTesting.PatientDetails) As %Status 
  { 
  
     /*Create an new instance of the request class (this is the model of the data)*/ 
     Set req = ##Class(JWDEV.SQLTesting.PatientDetails).%New() 
     /*Set the properties within the model to values located in request HL7 argument*/ 
     Set req.mrn  = request.GetValueAt("MSH:9.1") 
     Set req.name = request.GetValueAt("MSH:9.2") 
     /*Send the request to the EnsLib.SQL.Operation.ProcOperation business operation*/ 
     Set resultStatus = ..SendRequestSync("InsertPatientData", req, 30) 
     return resultStatus 
  } 
  
  Method OnResponse(pInput As C21.CUH.IEP.IEPRequest) As %Status 
  { 
      Try { 
          return $$$OK 
      } 
      Catch ex { 
          Set tSC=ex.AsStatus() 
      } 
  } 
  } 

• The business operation is configured with a request class of the "JWDEV.SQLTesting.PatientDetails", which allows me to directly access the "mrn" and "name" properties, which are passed as arguments to the stored procedure
  • 

This approach is functional and does insert the relevant fields into the database:

In other integration engines I've used, inserting into a external database wouldn't require the use of custom classes, I'd be able to directly access the HL7 message fields when needed or save global variables/properties that can be used as the stored procedure arguments, but with InterSystems I've struggled to find a similar approach. It is likely my inexperience using SQL within InterSystem, but the way I've accomplished this feels overengineered. 

Is there a more appropriate approach to inserting data into an external table than the one I've shown above? If yes, please could I get some pointers on what that is, so I can improve on what I've learnt so far.

Also, I appreciate that the actual classes shown are quite barebones/messy, I just wanted to get basic functionality working to demonstrate a POC.

Thanks,

Josh