Query external SQL database to use within a Production

Introduction

There is a Link Procedure Wizard option within the Management Portal (System > SQL >Wizards > Link Procedure) which I had reliability issues with so I decided to use this solution instead.

Problem

You need to query an external SQL database to use the response within a namespace. This guide is assuming that you already have a working stored procedure in SSMS although you could instead use a SQL block within the operation. Stored procedures in SSMS are preferred to maintain integrity, Embedded SQL can get very confusing if you have a complicated SQL statement.

In this example, I am using a patients identifier, querying a SQL database and getting back their GP details.

Solution

1. Create a DSN on the server your production is running on using ODBC
2. Create a new SQL Gateway connection
3. Create a message class that extends Ens.Request which will contain the fields that you will use in your SQL query
4. Create a message class that extends Ens.Response which contains the fields that you want back from the query
5. Create a operation class
6. Create a new Operation in the Management Portal and link the class created in point 5 (Interoperability > Configuration > Production Configuration)
7. Add the DSN within the Operation settings (from point 2)

1. Create a DSN on the server your production is running on using ODBC

At the end of this process, ensure that the test is successful otherwise the rest will fail. Once this is set on the server, you can use throughout your Production.

2. Create a new SQL Gateway connection

This links the server DSN to something meaningful in Management Portal.

Don't forget to check the connection.

3. Create a message class that extends Ens.Request which will contain the fields that you will use in your SQL query

Class ABCD.Messages.General.GPdataRequest Extends Ens.Request
{
Property mrn As %String;
}

This will contain the data items that you need to pass to your stored procedure/SQL code block 

4. Create a message class that extends Ens.Response which contains the fields that you want back from the query

Class ABCD.Messages.General.GPdataResponse Extends Ens.Response
{
Property NatGPCode As %String;
Property NatLocCode As %String;
Property Result As %String;
}

This will contain the response from the stored procedure/SQL code block

5. Create a operation class

Class ABCD.Operations.getGPDataByMRN Extends Ens.BusinessOperation
{

Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";
Property Adapter As EnsLib.SQL.OutboundAdapter;
Parameter INVOCATION = "Queue";
Method OnMessage(pRequest As ABCD.Messages.General.GPdataRequest, pResponse As ABCD.Messages.General.GPdataResponse) As %Status
{
	Set snapshot = ##class(EnsLib.SQL.Snapshot).%New()
	Set tSC =$$$OK
	//Build the execute statement
	Set query = " [dbo].[sp_get_GPdata_by_MRN] "
	Set query = query _ ", @MRN = '"_pRequest.IMSmrn_"'"
	//Simple trace to check the execute statement. You should be able to lift this trace and run directly in SQL server.
	$$$TRACE("Query looks like this= "_query) //example [dbo].[sp_get_GPdata_by_MRN] @MRN = '123456'
	//Execute the statement
	$$$QuitOnError(..Adapter.ExecuteQuery(snapshot,query))
	
	//Prepare to get results back, setting responses to null and "Failure" by default for error handling. A positive result will overwrite these.
	Set pResponse = ##class(ABCD.Messages.General.GPdataResponse).%New()
	Set (pResponse.NatGPCode, pResponse.NatLocCode) = ""
	Set pResponse.Result = "Failure"
		//Check the snapshot for any rows.		
        IF snapshot.RowCount>0
			{
			Set pResponse.Result = "Success"
			$$$TRACE(snapshot.Get("NationalGPCode",1))
			Set pResponse.NatGPCode  = snapshot.Get("NationalGPCode",1)
			Set pResponse.NatLocCode = snapshot.Get("NationalLocationCode",1)
			}	
	Quit tSC
}

}

6. Create a new Business Operation within the Management Portal (Interoperability > Configuration > Production Configuration) and link the Class created in point 5

7. Add the DSN within the Operation settings (from point 2)

If you use Credentials here, remember that Credentials are per Namespace.

Once I had this working, I then created a Business Process to then call this Business Operation and return back to the process to use as required.