Crosswalk Transformer From Database

[EthanKC]

I'm trying to build out a transformer that crosswalks the OBR.4.1 value. For example, if the OBR.4.1 value is 111, change it to AAA. I know how to do this hardcoded, but it's going to be a lot easier to import a csv file to the db, and use that. I've built a table in postgresql that has an input and output column. I did my first pass at the transformer, but instead of spitting out "AAA" in OBR.4.1, populated OBR.4.1 as this: com.mirth.connect.server.userutil.MirthCachedRowSet@4a619dc9

Can someone help me figure out what I've done wrong?

`var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
'org.postgresql.Driver',
'jdbc:postgresql://localhost:5432/mirthdb',
'user',
'password'
);

var input = msg['OBR']['OBR.4']['OBR.4.1'].toString()

var sql = "SELECT output FROM crosswalk WHERE input= '" + input + "'";

var output = dbConn.executeCachedQuery(sql);

if (output) {
msg['OBR']['OBR.4']['OBR.4.1'] = output;
};`

[pacmano1]

You need to call result.next().

How big is the crosswalk table though?

And stop doing inline connection building like that, it's dumb. Do something like https://github.com/pacmano1/Mirth-Snippets/blob/main/executeSharedDBStatement.js

[EthanKC]

Thanks, I'll review this and see what I figure out.

To answer your question, the crosswalk I'm testing with is like 5 lines. Not sure exactly how large the crosswalk table will be when it goes live (I'm figuring this out ahead of time) but I would expect it to be at least 100+

[pacmano1]

it is likely much better to load that table to a $g object as an object and just do the lookup directly.

var lookupTable = [
    { zip: "10001", city: "New York" },
    { zip: "90001", city: "Los Angeles" },
    { zip: "60601", city: "Chicago" },
    { zip: "77001", city: "Houston" },
    { zip: "85001", city: "Phoenix" },
    { zip: "19101", city: "Philadelphia" },
    { zip: "75201", city: "Dallas" },
    { zip: "94101", city: "San Francisco" },
    { zip: "33101", city: "Miami" },
    { zip: "30301", city: "Atlanta" }
];

var findCityByZip = (zipCode) => {
    const entry = lookupTable.find(entry => entry.zip === zipCode);
    return entry ? entry.city : null;
};

var result = findCityByZip("19101");
logger.info(result);

result = findCityByZip("11111");
logger.info(result);

You can source your table from postgres of course. I didn't use $g here but "lookupTable" could easily be a $g var.

[EthanKC]

Can you explain why the inline connection building is dumb? I tried using the code at the link you provided, but it kept giving me errors. I even simplified it all the way down to this:

var dbConn = DatabaseConnectionFactory.createDatabaseConnection(
	configurationMap.get('DBDriver'),
	configurationMap.get('DBServerDatabase'),
	configurationMap.get('DBUserName'),
	configurationMap.get('DBPassword')
);

But I still kept getting this error: "Wrapped org.postgresql.util.PSQLException: Something unusual has occurred to cause the driver to fail. Please report this exception."

[pacmano1]

Show your config map. just change the IP and password.

Show an example call of the code template.

Inline connection building:

1. You gotta open/close/try/catch/err the connection every time, the code template does that on the fly.
2. you gotta go find every single reference to the connect string if any aspect of it changes, that's rather painful
3. Obviously your SQL volume matters here, but you can always create a handful of shared $g connections.

[EthanKC]

Here is is in my deploy script:

// This script executes once when the channel is deployed
// You only have access to the globalMap and globalChannelMap here to persist data
function executeSharedDBStatement(statement, isQuery, paramList) {
    var result;
    try {
        var dbConn = globalMap.get("dbConn");
        if (dbConn == null || dbConn.getConnection().isClosed()) {
            dbConn = DatabaseConnectionFactory.createDatabaseConnection(configurationMap.get('DBDriver'), configurationMap.get('DBServerDatabase'), configurationMap.get('DBUserName'), configurationMap.get('DBPassword'));
            globalMap.put("dbConn", dbConn);
        }
        if (paramList == undefined) {
            paramList = new Packages.java.util.ArrayList();
        }
        if (isQuery) {
            result = dbConn.executeCachedQuery(statement, paramList);
        } else {
            result = dbConn.executeUpdate(statement, paramList);
        }
    } catch (e) {
        logger.error(e);
    } finally {
        return result;
    }
}

var sql = "SELECT * FROM crosswalk";

var crosswalk = executeSharedDBStatement(sql, true, paramList)

globalChannelMap.put('crosswalk', crosswalk)

return;

And here is my configuration map:

[pacmano1]

You don't understand the function, nor the SQL you are calling. You have no paramList. You are not returning a JSON blob from postgres, you are not JSON.parsing that returned string.

// This below syntax is a nice way to see your SQL formatted and not mess with lots of delimeters.
var sql = <>
select json_agg(t)
from (select * from crosswalk) t
<>.toString()

var crosswalk = executeSharedDBStatement(sql, true) // call the function.
result.next() // always needed 
$g('crosswalk', JSON.parse(result.getString(1)))