Mirth (NextGen) Connect Bits #3: CRUD Database Operations Using JavaScript

Mirth (NextGen) Connect has a Database Reader in the “Source” and a Database Writer in the “Destination”. However, if you have some complex processes like getting additional information in between, you can do database operations (create, read, update and delete) anywhere that executes the JavaScript code (ex. Filter, Transformer, and Destination).

The primary code block would be a try-catch-finally code block:

var dbConn;

try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://127.0.0.1:5432/testdb,username,pass);

	
} catch(e) {
    throw "Error: " + e.toString();
} finally {
    if (dbConn) {
        dbConn.close();
    }
}

Below are what the code above do:

  • dbConn – This is a variable where the database object details and methods will be assigned.
  • DatabaseConnectionFactory.createDatabaseConnection – A Java class and method where it create database connection objects. Set your database driver, URL, username, and password here.
  • try – will try to execute the code first.
  • catch – will catch any errors thrown in the try block
  • finallywe close the database connection.

We’ll do the CRUD operations inside the try block. First, we’ll set a Java array to hold the parameters we want to add in the SQL query.

Fetching Records

On my end, I want to search for patient records with last name Rizal and has the sex of “F”.

var params = new Packages.java.util.ArrayList();
    params.add('F');
    params.add('Rizal');

The Java array handles better the data you pass as a parameter, instead of concatenating them in a SQL string.

The dbConn object can now use different database CRUD operations. To fetch records. Below is a sample code.

var sqlQuery = "SELECT first_name, last_name, sex, date_of_birth FROM patients WHERE sex = ? AND last_name ?;";

var getPatient = dbConn.executeCachedQuery(sqlQuery, params);

var resultArray = [];

while(getPatient.next()) {
    resultArray.push(
        {
            "first_name" : getPatient.getString('first_name'),
            "last_name" : getPatient.getString('last_name'),
            "dob" : getPatient.getString('date_of_birth'),
            "sex" : getPatient.getString('sex')
        });
}
  • sqlQuery – Variable with the SQL statement as a string, the parameters should have the place holder? which should match the number of parameters you set in the params variable.
  • dbConn.executeCachedQuery – Executes a query on the database and returns a CachedRowSet.
  • getPatient – variable where we loop to the cachedrowset using the .next() method.
  • getPatient.getString() – to access the returned row, add the column or alias name in the getString(”) method.
  • resultArray – is an empty list, we will push all the results in this array.

Final code would look like this:

var dbConn;

try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://127.0.0.1:5432/testdb,username,pass);

    var params = new Packages.java.util.ArrayList();
        params.add('F');
        params.add('Rizal');

    var sqlQuery = "SELECT first_name, last_name, sex, date_of_birth FROM patients WHERE sex = ? AND last_name ?;";

    var getPatient = dbConn.executeCachedQuery(sqlQuery, params);

    var resultArray = [];

    while(getPatient.next()) {
        resultArray.push(
            {
                "first_name" : getPatient.getString('first_name'),
                "last_name" : getPatient.getString('last_name'),
                "dob" : getPatient.getString('date_of_birth'),
                "sex" : getPatient.getString('sex')
            });
    }

    channelMap.put('resultArray', resultArray);
	
} catch(e) {
    throw "Error: " + e.toString();
} finally {
    if (dbConn) {
        dbConn.close();
    }
}   

Insert, Update and Delete Record

A Similar with the code for fetching records, the differences are that:

  • We’ll use dbConn.executeUpdate instead of dbConn.executeCachedQuery
  • Update the SQL string with the appropriate insert, update or delete statements.
  • Assign the return value of the SQL execution to a variable. Which returns the number of rows updated/inserted/deleted.
var params = new Packages.java.util.ArrayList();
    params.add('PID123456');

var sqlQuery = "DELETE FROM patients WHERE patient_id = ?";

var res = dbConn.executeUpdate(sqlQuery, params);

return res;

To know more about the DatabaseConnection class, you can check the Java Documentation here.

For more Mirth Connect related blog posts, check out the Mirth (NextGen) Bits tag or Health IT page.

Add a Comment

Your email address will not be published. Required fields are marked *