Português Brasil

Interopway

This project aims to provide a framework to assist in the development of interoperability projects using InterSystems technologies, such as:

• Ensemble
• IRIS Data Platform
• IRIS for Health
• HealthShare Health Connect

The main goal is to encapsulate all repetitive development into configurable components.

Protocols and Technologies

In this first version, the XDBC module is available for developing interoperability with access to relational databases. This module can be used in Ensemble, IRIS, IRIS for Health and Health Connect.

All collaborations are welcome. Feel free to fork the repository and contribute.

XDBC

This module aims to streamline the development of interoperability using ODBC or JDBC. Installation is quite simple. Download the xml package Interopway_XDBC-V0.0.1.xml and import it into the desired namespace.

The import can be done in 3 ways:

1. Via Studio (Windows only);

2. Via SMP (any SO);

3. Terminal or WebTerminal (any SO)

Do $system.OBJ.Load("</file/path>/Interopway_XDBC-V0.0.1.xml","ck")

Usage is easy and almost self-explanatory. In the package cjs.interopway.samples there is a Production configured demonstrating the use of ODBC and ** interoperability JDBC**.

The class cjs.interopway.tests.InteropProduction with the test cases to be executed to validate the installation.

Below are detailed the prerequisites and necessary steps for the execution of the test cases. The tests were performed in a Windows environment on an Ensemble instance:

$ZV=Cache for Windows (x86-64) 2018.1.5 (Build 659U) Mon Mar 22 2021 07:03:57 EDT

Prerequisites to run the test cases:

• JDK 1.7 or 1.8 installed. Used by the JDBC connection;

• Systems DSN named Ensemble Samples used by ODBC connection;

• Empty directory C:\Temp\tests. Used by Test Manager;

• Credential Ensemble Samples configured in SMP in the installation namespace.

Execution of Test Cases

Once the prerequisites are met open a Terminal or WebTerminal session in the namespace where the XDBC module was installed and run the line below:

Do ##Class(%UnitTest.Manager).RunTest("tests:cjs.interopway.tests.InteropProduction", "/noload/nodelete")

At the end of running the tests, if everything went as expected, the output should look something like the image below. For a detailed execution report, access the URL that appears in the image highlight.

The URL will take you to the Test Portal where you can inspect each test case individually.

Test Case execution detail TestExecuteProcedureODBC

Understand Sample Code and Tests

We will detail the use of the Lookup Table TESTS_XDBC_CONFIG and the classes of the example package cjs.interopway.samples:

0. TESTS_XDBC_CONFIG - Lookup Table with the necessary configurations for the execution of SQL commands to be executed in external databases.

   <?xml version="1.0"?>
   <lookupTable>
   	<entry table="TESTS_XDBC_CONFIG" key="cjs.interopway.samples.company.bo.UpdateCompanyRequest">{
   		"credential":"ENSEMBLE Samples",
   		"statement":"UPDATE Sample.Company SET TaxID = ? WHERE ID = ?"
   	}
   	</entry>
   	<entry table="TESTS_XDBC_CONFIG" key="cjs.interopway.samples.person.bo.PersonSetsRequest">{
   		"credential":"ENSEMBLE Samples",
   		"statement":"? = CALL Sample.PersonSets(?,?)"
   	}
   	</entry>
   	<entry table="TESTS_XDBC_CONFIG" key="cjs.interopway.xdbc.bo.QueryRequest">{
   		"credential":"ENSEMBLE Samples",
   		"statement":"SELECT * FROM Sample.Person"
   	}
   	</entry>
   	<entry table="TESTS_XDBC_CONFIG" key="cjs.interopway.xdbc.bo.QueryStreamRequest">{
   		"credential":"ENSEMBLE Samples",	
   		"statement":"SELECT * FROM Sample.Employee"
   	}
   	</entry>
   </lookupTable>

The Lookup Table above is used by all classes that appear in the key attribute of the lookup, more specifically in the constructor that is defined in the base class of the request [cjs.interopway.xdbc.bo.BaseRequest](/src/cjs/interopway/ xdbc/bo/BaseRequest.cls):

Method %OnNew(lookupTable As %String = "XDBC") As %Status
{
	#Dim exception	As %Exception.General = ""
	#Dim statusCode	As %Status            = $System.Status.OK()
	Try 
	{
		#Dim configString	As %String = ##Class(Ens.Util.FunctionSet).Lookup(lookupTable, $ClassName())
		If (configString = "")
		{
			Return statusCode
		}
		#Dim config As %DynamicObject = {}.%FromJSON(configString)
		If (config.credential '= "" && '##Class(Ens.Config.Credentials).%ExistsId(config.credential))
		{
			Return $System.Status.Error(5001, "Credential does not exists.")
		}
		If (config.credential '= "")
		{
			Set ..Credential	= config.credential
		}
		If (config.statement '= "")
		{
			Set ..Statement	= config.statement
		}
		If (config.dsn '= "")
		{
			Set ..DSN	= config.dsn
		}				
	}
	Catch (exception) 
	{
		Set statusCode = exception.AsStatus()
	}
	Return statusCode
}

1. cjs.interopway.samples.company.bo.UpdateCompanyRequest - Specific request to perform an update with parameters without describing them.

   ClassMethod GetInstance(companyId As %Integer, taxId As %String) As UpdateCompanyRequest
   {
   	#Dim updateRequest			As UpdateCompanyRequest	= ..%New("TESTS_XDBC_CONFIG")
   	//	
   	Do updateRequest.Parameters.Insert(##Class(ParameterDefinition).%New(1, taxId))
   	Do updateRequest.Parameters.Insert(##Class(ParameterDefinition).%New(2, companyId))
   	//
   	Return updateRequest
   }

2. cjs.interopway.samples.person.bo.PersonSetsRequest - Specific request to execute the procedure that returns 2 ResultsSets and describes the parameters of the call.

   ClassMethod GetInstance(name As %Integer, state As %String) As PersonSetsRequest
   {
   	#Dim procedureRequest	As UpdateCompanyRequest	= ..%New("TESTS_XDBC_CONFIG")	
   	#Dim returnParameter	As ParameterDefinition	= ##Class(ParameterDefinition).%New(1)
   	#Dim nameParameter		As ParameterDefinition	= ##Class(ParameterDefinition).%New(2, name)
   	#Dim stateParameter		As ParameterDefinition	= ##Class(ParameterDefinition).%New(3, state)
   	//
   	Set returnParameter.IOType	= returnParameter.IOTypeDisplayToLogical("OUTPUT")
   	Set nameParameter.IOType		= nameParameter.IOTypeDisplayToLogical("INPUT")
   	Set stateParameter.IOType		= stateParameter.IOTypeDisplayToLogical("INPUT")
   	Set returnParameter.SqlType	= $$$SqlInteger
   	Set nameParameter.SqlType		= $$$SqlVarchar
   	Set stateParameter.SqlType	= $$$SqlVarchar
   	//
   	Do procedureRequest.Parameters.Insert(returnParameter)
   	Do procedureRequest.Parameters.Insert(nameParameter)
   	Do procedureRequest.Parameters.Insert(stateParameter)
   	//
   	Return procedureRequest
   }

3. cjs.interopway.samples.InteropProducion.cls - Example Production class containing the components:

• EnsLib.JavaGateway.Service - Busines Service that communicates with the JVM. The path for the JDBC drivers of the external database must be configured.

• JDBC Sample Operation - Instance of the class cjs.interopway.xdbc.bo.Operation. Business Operation that communicates with the exteran database via JDBC. Here the DSN is configured, which in this case is the connection String, Credential and driver class

• ODBC Sample Operation - Instance of the class cjs.interopway.xdbc.bo.Operation. Business Operation that communicates with the exteran database via ODBC. Here the DSN and Credential are configured.

The package cjs.interopway.tests is detailed below:

4. cjs.interopway.tests.InteropProduction - Class with the test cases to be executed:

   • TestExecuteQueryODBC - Executes the SQL command SELECT * FROM Sample.Person on the ODBC connection.

   • TestExecuteQueryJDBC - Executes the SQL command SELECT * FROM Sample.Person on the JDBC connection.

   • TestExecuteQueryStreamODBC - Executes the SQL command SELECT * FROM Sample.Employee on the ODBC connection.

   • TestExecuteQueryStreamODBC - Executes the SQL command SELECT * FROM Sample.Employee on the JDBC connection.

   • TestExecuteUpdateODBC - Execute SQL command UPDATE Sample.Company SET TaxID = ? WHERE ID = ? in the ODBC connection with parameters however you describe them.

   • TestExecuteUpdateJDBC - Execute the SQL command UPDATE Sample.Company SET TaxID = ? WHERE ID = ? in JDBC connection with parameters but if you describe them

   • TestExecuteProcedureODBC - Execute SQL command ? = CALL Sample.PersonSets(?,?) on the JDBC connection with parameters and describing them.

   • TestExecuteProcedureODBC - Execute SQL command ? = CALL Sample.PersonSets(?,?) on the JDBC connection with parameters and describing them.

Basic Usage Guide

To use interopway_xdbc, just follow the simple guide below:

• Add the Busines Operation (cjs.interopway.xdbc.bo.Operation to Production;

• When using a connection via ODBC, configure the DSN in ht Operating System;

• When using a JDBC connection, add the driver's path to CLASSPATH;

• According to the need, you can configure the Credential and/or DSN in the Business Operation settings.

• If the Credential and/or DSN are not configured in the Business Operation, these settings must be passed to the Business Operation through the request messages, and can be assigned directly to the message, or can be added to the Lookup Table used in the message constructor. See: cjs.interopway.xdbc.bo.BaseRequest

• For the vast majority of databases, SQL commands that have parameters, it is not necessary to describe them through the properties SQLType, Precision and Scale of the class cjs.interopway.xdbc.bo.ParameterDefinition. See: cjs.interopway.samples.company.bo.UpdateCompanyRequest;