Use File Reader and database reader on the same Channel

[IbrahimAlhaj9]

Hi everyone,
I have a Hl7 file is auto generated by an app, I am using file reader to read this hl7 file from mirth channel, I need to change the value of PID.7.1 whereas the value is stored in database. how I can keep reading the hl7 file from mirth and at same time read values from database.

Please advise.
Thank you

[jonbartels]

Option A:

1. Source connector is your HL7 source
2. A destination connector runs SQL based on HL7 mappings to look up the values you need
3. The next destination sends HL7. It runs and uses the values from step 2 to transform the HL7 message with data from the DB

Option B:

1. Source connector is your HL7 source
2. Use Javascript in a transformer to connect to and query your database for information. Iterate over the result rows and transform your HL7 message
3. The HL7 destination runs and sends your HL7 message

[IbrahimAlhaj9]

Thanks @jonbartels
I am using the option B but i Have the following error:
[2022-12-12 09:51:16.951] ERROR (transformer:?): JavaException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host MigrationServer, port 8002 has failed. Error: "The driver received an unexpected pre-login response. Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. This driver can be used only with SQL Server 2005 or later.". ClientConnectionId:250caf94-39b8-4c4c-9232-9d8c48c951af

JavaScript Code:

var dbConn;
var result;
var entryList = [];
var mapping = '5'

try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection("com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://MigrationServer:8002;instanceName=MigrationServer/Production9ecm;databaseName=Novo;integratedSecurity=true;",'sa','o123$');
logger.info(dbconn);
result = dbConn.executeCachedQuery("select FullName from Users where UserName = 'ihaj'");
while (result.next()) {
entryList.push(result(1));
}

mapping = entryList[Math.floor(Math.random()*entryList.length)];

}
catch (e) {
logger.error(e);
mapping = '5';
}

finally {
if (dbConn) {
dbConn.close();
}
}
logger.info(mapping);
channelMap.put('AssignedTo', validate(mapping, '', new Array()));

Connection setting:
ServerName = MigrationServer
ServerPort = 8002
Instance name = MigrationServer\Production9ecm
db name = Novo

[jonbartels]

That is a routine connection issue. Check the connection string and auth options for the JDBC URL you're using. Ensure you can ping and telnet from your MC installation to SQL Server.

[pacmano1]

@IbrahimAlhaj9 , please use code formatting when you post here, see https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks

var dbConn;
var result;
var entryList = [];
var mapping = '5'

try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection("com.microsoft.sqlserver.jdbc.SQLServerDriver", "jdbc:sqlserver://MigrationServer:8002;instanceName=MigrationServer/Production9ecm;databaseName=Novo;integratedSecurity=true;", 'sa', 'o123$');
    logger.info(dbconn);
    result = dbConn.executeCachedQuery("select FullName from Users where UserName = 'ihaj'");
    while (result.next()) {
        entryList.push(result(1));
    }

    mapping = entryList[Math.floor(Math.random() * entryList.length)];

} catch (e) {
    logger.error(e);
    mapping = '5';
} finally {
    if (dbConn) {
        dbConn.close();
    }
}
logger.info(mapping);
channelMap.put('AssignedTo', validate(mapping, '', new Array()));

[IbrahimAlhaj9]

Thanks it works now.