Integrating – AWS EC2 (MySQL) to Mirth Engine

For this post. I have purchased a personal EC2 instance in AWS environment (a free tier for one year). Specifically an Amazon-AMI Image AWS instance with Fedora Operating System.

In the remote EC2 system, MySQL is deployed and a database is created as test. Once the DB is created in the EC2 instance, you have to create a table with some sample patient demographics information.

How to Access AWS remote server?

Open the putty client and put the AWS amazon hostname in the Host Name or IP adress text box. Else you can put the elastic IP of your AWS instance there. It doesn’t always need to be a complete hostname.

1. Then select SSH on the left side window of the putty and select on Auth. 
2. Once you selected Auth on the right window pane click on the Browse button and select the private key you have downloaded from the amazon.
3. This private key will be a .ppk file. This PPK file will be necessary to establish the SSH connectivity between your putty client and the AWS system.

1. Once this is done click on the Open button on the bottom.
2. Once you open you will be prompted to enter the username. If you purchased a Linux Ubuntu System the default user name will be ubuntu.
3. If you have purchased a different system (say here I have purchased the Amazon AMI system). For the Amazon AMI system you need the password as ec2-user. 
4. you can install MySQL linux distribution based the version you use. For Amazon AMI linux it uses the Fedora System. For Fedora use this command to install the MySQL. Before executing the below command do the command sudo yum update once.

dnf install mysql-community-server

For the Debian Ubuntu distribution use the below command for the MySQL installation

• sudo apt-get update
• sudo apt-get install mysql-server

Once installation is done. Log into your MySQL database on the AWS linux box. You can do this by typing the below command in the putty client box.

mysql -u root -p

Once it is done, you will enter into mysql> type show databases; initially you will not have a new database been created at your end. you have to create a new database for yourself. use the below command and create a new database.

create database test;

This will create a new database. Now we have to use this database and create the tables in it. Use the below command to use this database;

use test;

Here, test is the database name that i’m creating in the box. you can have the name of your choice. Then create a table with the following fields as provided in the screen shot below.

1. Once you have created it, log on to your AWS web console. And open the port in the security groups on the web console.
2. Click on Edit button after selecting the security group and then add the port 3306 on TCP.
3. Only when we do this the inbound socket of your remote system will open, then only your local Mirth system can establish the communication to the system in AWS environment.

In case if you install any application server like the Apache or Tomcat you would initially want to open the specific ports which are specified in the httpd.conf or catalina.conf files. We want to go to the security group on the AWS console and then open enable those inbound ports, then only your (IP+Port) combination will work in the browser, this IP+Port combination is technically referred as Socket.

Create a channel in your local Mirth Connect. make the source to the javascript reader and keep the polling frequency of your interest to fetch the data from the DB. In the source connector area provide the following code.

var dbConn;
// AWS Mysql Credentials
var mySqlDriver = “com.mysql.jdbc.Driver”;
// 54.175.172.38 – Server IP
// 3306 – Mysql Default Port number
// test – Database in Remote server
var mySqlConnectionTemplate = “jdbc:mysql://54.175.172.38:3306/test”;
var mySqlUserName = “root”;

// Create Parent Tag <PatientDemographics>
var patientDemographicsXml = new XML(‘<PatientDemographics></PatientDemographics>’);
// Create Parent for individual patient information <IndividualPatientInformation>
var individualPatientInfoXml = new XML(‘<IndividualPatientInformation></IndividualPatientInformation>’);

try {
// MySQL connection Template
dbConn = DatabaseConnectionFactory.createDatabaseConnection(mySqlDriver, mySqlConnectionTemplate, mySqlUserName, ”);
// Select statement
// patient_information – is the table name
result = dbConn.executeCachedQuery(“select * from patient_information“);

// Loop through the resultset value
while (result.next()) {
individualPatientInfoXml[‘PatientId’] = result.getInt(“pid”);
individualPatientInfoXml[‘PatientFirstName’] = result.getString(“patient_first_name”);
individualPatientInfoXml[‘PatientLastName’] = result.getString(“patient_last_name”);
individualPatientInfoXml[‘PatientMiddleName’] = result.getString(“patient_middle_ name”);
individualPatientInfoXml[‘PatientSuffixName’] = result.getString(“patient_suffix_name”);
individualPatientInfoXml[‘PatientDateOfBirth’] = result.getString(“patient_date_of_birth”);
individualPatientInfoXml[‘PatientGender’] = result.getString(“patient_gender”);
individualPatientInfoXml[‘PatientAge’] = result.getInt(“patient_age”);
individualPatientInfoXml[‘PatientAddress1’] = result.getString(“patient_address_1”);
individualPatientInfoXml[‘PatientAddress2’] = result.getString(“patient_address_2”);
individualPatientInfoXml[‘PatientEmailAddress’] = result.getString(“patient_emailAddress”);
individualPatientInfoXml[‘PatientTelecomNumber’] = result.getString(“patient_telecom_number”);
individualPatientInfoXml[‘PatientRace’] = result.getString(“patient_race”);
individualPatientInfoXml[‘PatientEthincity’] = result.getString(“patient_ethincity”);
individualPatientInfoXml[‘PatientMaritalStatus’] = result.getString(“patient_maritalstatus”);
individualPatientInfoXml[‘PatientLanguage’] = result.getString(“patient_language”);
individualPatientInfoXml[‘PatientCountry’] = result.getString(“patient_country”);
individualPatientInfoXml[‘PatientState’] = result.getString(“patient_state”);
individualPatientInfoXml[‘PatientCity’] = result.getString(“patient_city”);
individualPatientInfoXml[‘PatientZipCode’] = result.getString(“patient_zipcode”);
individualPatientInfoXml[‘PatientSSN’] = result.getString(“patient_ssn”);
individualPatientInfoXml[‘PatientDriverLicense’] = result.getString(“patient_driver_license”);

patientDemographicsXml[‘PatientDemographics’] += individualPatientInfoXml;

individualPatientInfoXml = new XML(‘<IndividualPatientInformation></IndividualPatientInformation>’);
}

msg = patientDemographicsXml;

return msg;

} finally {
if (dbConn) {
dbConn.close();
}
}

Once the connector code is created then you will be able to fetch all the data which are available in the database as a batch, instead of single row entries. You will be able to accumulate all the data from the database on each row as shown below:

These data will be accumulated inside mirth as a XML data created in batch and the output of your mirth data will be as shown below:

<PatientDemographics>
<IndividualPatientInformation>
<PatientId>3301</PatientId>
<PatientFirstName>Jennifer</PatientFirstName>
<PatientLastName>Jaquline</PatientLastName>
<PatientMiddleName>Fernandez</PatientMiddleName>
<PatientSuffixName>III</PatientSuffixName>
<PatientDateOfBirth>1994-04-01</PatientDateOfBirth>
<PatientGender>F</PatientGender>
<PatientAge>23</PatientAge>
<PatientAddress1>No:8, washington, test drive</PatientAddress1>
<PatientAddress2>Oregan, detroit</PatientAddress2>
<PatientEmailAddress>jenn@test.com</PatientEmailAddress>
<PatientTelecomNumber>12356722</PatientTelecomNumber>
<PatientRace>2106-3</PatientRace>
<PatientEthincity>2131-5</PatientEthincity>
<PatientMaritalStatus>S</PatientMaritalStatus>
<PatientLanguage>EN</PatientLanguage>
<PatientCountry>US</PatientCountry>
<PatientState>GA</PatientState>
<PatientCity>oregan</PatientCity>
<PatientZipCode>55023</PatientZipCode>
<PatientSSN>123456789</PatientSSN>
<PatientDriverLicense>AXXW12345</PatientDriverLicense>
</IndividualPatientInformation>
<IndividualPatientInformation>
<PatientId>3302</PatientId>
<PatientFirstName>James</PatientFirstName>
<PatientLastName>Rupert</PatientLastName>
<PatientMiddleName>Jones</PatientMiddleName>
<PatientSuffixName>IV</PatientSuffixName>
<PatientDateOfBirth>1993-04-22</PatientDateOfBirth>
<PatientGender>M</PatientGender>
<PatientAge>24</PatientAge>
<PatientAddress1>4/12 Stevie Street, jj colony</PatientAddress1>
<PatientAddress2>Michigan, detroit</PatientAddress2>
<PatientEmailAddress>james@test.com</PatientEmailAddress>
<PatientTelecomNumber>12005678</PatientTelecomNumber>
<PatientRace>2028-9</PatientRace>
<PatientEthincity>2131-5</PatientEthincity>
<PatientMaritalStatus>M</PatientMaritalStatus>
<PatientLanguage>EN</PatientLanguage>
<PatientCountry>US</PatientCountry>
<PatientState>OR</PatientState>
<PatientCity>Oregan</PatientCity>
<PatientZipCode>44336</PatientZipCode>
<PatientSSN>987654321</PatientSSN>
<PatientDriverLicense>AXXE12309</PatientDriverLicense>
</IndividualPatientInformation>
</PatientDemographics>

Happy Integrations !!!!!