//test existence of PatientId in database  
 
//other drivers names are located in your 
//Mirth Directory\conf\dbdrivers.xml
//Simply use the value in the class= specification
 
var driver = "net.sourceforge.jtds.jdbc.Driver"; 
var address = "jdbc:jtds:sqlserver://srv.test.com:1433/DevMirth";  
var username = "mirth"; 
var password = "mirth"; 
 
//DatabaseConnection 
//see http://svn.sourceforge.net/viewvc/mirth/trunk/server/src/com/webreach/mirth/server/util/DatabaseConnection.java?view=markup 
var dbConn = DatabaseConnectionFactory.createDatabaseConnection(driver, address, username, password); 
 
//the query 
var patId10 = msg['PID']['PID.2']['CK.1'];  
var expression = "SELECT COUNT(*) FROM Patient WHERE PatId10 = '" + patId10 + "';";  
 
//CachedRowSetImpl 
//see http://developers.sun.com/prodtech/javatools/jscreator/reference/docs/apis/rowset/com/sun/rowset/CachedRowSetImpl.html 
var result = dbConn.executeCachedQuery(expression); 
 
 
//I am not sure if the following still works so you may want
//to just try just the result variable for a single result 
//mrslatezb
 
//go to the first result 
result.next(); 
//get the value from the first column as an integer 
var iCount = result.getInt(1);  
 
//cleanup 
 
//If you can get an EcmaError running Mirth 
//saying "Cannot find function close" comment out result.close() 
//next line. This was needed using executeUpdateQuery
//-mrslatezb
 
result.close();  
dbConn.close(); 
 
//if the PatientId doesn't exists we may do the insert 
return (iCount == 0);