Database values replace HL7 fields

[ajadams2]

I have an HL7 feed coming into Mirth via LLP
1. I would like to take a certain field from the message and use the values to look up data in a SQL 2000 database based on that value.

2. If the record exists, return data from database to the field in the HL7 message
3. If not, insert a new record into the database.
4. Send the newly encoded data out through the same channel to Cloverleaf.

I have attached my channel.
I can't see where the data is actually being replaced in the Encoded Message tab.
Any help would be appreciated.

One more thing, on the Wiki there is an entry named "Mapping HL7 fields to database values" under Examples, but there isn't an example available. If I can get this to work, then I will post this example over there.

Thanks,
Austin
TestHL7.xml (15368 bytes)

Post edited by: ajadams2, at: 06/21/2007 06:31

[ajadams2]

Reatabase values replace HL7 fields

I think I am close to getting this resolved. I am finally able to get a value from one column in my db and replace a single field within my HL7 message and send it to another interface engine(Cloverleaf) so that it will send it to other systems. Now, I am experimenting using "LIKE" in my sql.

I still need to test :
1. Bringing back more than one column
2. Bringing back more than one row and column (will be useful for repeating segments but the table data/structure will determine on how the code needs to be written.)

I'll post when I am finished....won't be today for sure.

Austin

[ajadams2]

Reatabase values replace HL7 fields

Test Message
MSH|^~\&|SE099|099|SE099|099|200706141000||MFN^M02 |18087|P|2.2|||AL|NE||||||2.2b
MFI|PRA^PRACTITIONER^HL7|SE099|UPD|20070614100013| 20070614100013|NE
MFE|MUP|100013|20070614100013|99999^^99MDR
STF|99999^^99MDR|99999^^99MDR|LAST, FIRST P.||||A||MED|(555)555-5555~(555)555-5555CF|YOUR DEPARTMENT^^CITY MO^^33333-0000|20070624
PRA|99999^^99MDR||||129|aa11111^STLIC~S99999^UPIN| ADMIT~DISCH~ORDER
HL7_DbValue_Replace.xml (12882 bytes)

[hsingh00]

Reatabase values replace HL7 fields

1. I would like to take a certain field from the message and use the values to look up data in a SQL 2000 database based on that value.

//Using the incoming Patient ID to search the database
var InComingPID 	= msg['QRD']['QRD.8']['XCN.1'].toString();

2. If the record exists, return data from database to the field in the HL7 message
3. If not, insert a new record into the database.

//Connect to DB
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:«»sqlserver://127.0.0.1:1433/ADT_DB','sa','dscp');
//Assign the HL7 fields to variables
var DateTime 	= msg['MSH']['MSH.7']['MSH.7.1'].toString();
var MessContID 	= msg['MSH']['MSH.10']['MSH.10.1'].toString();
var PatientID 	= msg['PID']['PID.3']['PID.3.1'].toString();
var LastName 	= msg['PID']['PID.5']['PID.5.1'].toString();
var LastName 	= LastName.replace(/'/g, "''"«»);
var FirstName 	= msg['PID']['PID.5']['PID.5.2'].toString();
var FirstName 	= FirstName.replace(/'/g, "''"«»);
var DOB 		= msg['PID']['PID.7']['PID.7.1'].toString();
var Sex 		= msg['PID']['PID.8']['PID.8.1'].toString();

//See if the patient ID already exists.  If it does, Select it.  If it doesn't, insert the new record.
var expression = "IF exists (SELECT [PatientID] FROM [ADT_DB].[dbo].[demographics] WHERE [PatientID] = '"+PatientID+"') SELECT ([DateTime],[MessContID],[PatientID],[LastName],[FirstName],[DOB],[Sex]) FROM [ADT_DB].[dbo].[demographics] WHERE [PatientID] = '"+PatientID+"' ELSE INSERT IGNORE INTO [ADT_DB].[dbo].[demographics] ([DateTime],[MessContID],[PatientID],[LastName],[FirstName],[DOB],[Sex]) VALUES ('"+DateTime+"','"+MessContID+"','"+PatientID+"','"+LastName+"','"+FirstName+"','"+DOB+"','"+Sex+"')";
var result = dbConn.executeUpdate(expression);

4. Send the newly encoded data out through the same channel to Cloverleaf.

I don't know what you mean by this statment. Do you want to send the newly encoded data back through the same channel you recieved it? If so you might have to create a custom ACK. But if your sending to a new destination you can use the same variables we defined earlier to create the message.

This is how I take data out of a database.

var Query = "SELECT [DateTime],[MessContID],[PatientID],[LastName],[FirstName],[DOB],[Sex] FROM [ADT_DB].[dbo].[demographics] WHERE PatientID = '"+InComingPID+"'";
var result = dbConn.executeCachedQuery(Query);

if (result.next() != '0')
	{
	var DateTime 	= result.getString(1);
	var MessContID	= result.getString(2);
	var PatientID 	= result.getString(3);
	var LastName	= result.getString(4);
	var FirstName 	= result.getString(5);
	var DOB 	= result.getString(6);
	var Sex 	= result.getString(7);
	
	tmp['MSH']['MSH.7']['MSH.7.1'] 		= DateTime;
	tmp['MSH']['MSH.10']['MSH.10.1'] 	= MessContID;
	tmp['PID']['PID.3']['PID.3.1']		= PatientID;
	tmp['PID']['PID.5']['PID.5.1']		= LastName;
	tmp['PID']['PID.5']['PID.5.2']		= FirstName;
	tmp['PID']['PID.7']['PID.7.1']		= DOB;
	tmp['PID']['PID.8']['PID.8.1']		= Sex;
	
	result.close(); 
	dbConn.close();
	}

I hope some of this made sense and helps you out.

[jmmahler]

Reatabase values replace HL7 fields

I am very new to MIRTH and even newer to trying to write code in Javascript. I have used the previous code samples in this forum to successfully query a SQL db and return those results into a message that gets passed along to another destination. I have one small Javascript question though. How can I test for the number of rows returned by an SQL query? I was looking for an operator called .count, .size, or .length, but none of them seem to be valid. Any suggestions?

[nshaik]

Reatabase values replace HL7 fields

How can I test for the number of rows returned by an SQL query? I was looking for an operator called .count, .size, or .length, but none of them seem to be valid. Any suggestions?

Try this SQL call similar to the sample above and process the return value:

SELECT Count(*) AS CurRecCount FROM Demographics

Hope this helps!