Accessing the database as a transform step with variables #6234
-
|
Hello there - I'm working on re-writing an HL7 message I've received. The HL7 message is received as the source, and then in the Source Transformers I've created a few variables. The variable is called AccessionNumber, and it's mapped to msg['OBR']['OBR.2']['OBR.2.1'].toString() The source transformer is type Javascript, and looks like this: var dbConn;
try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.cj.jdbc.Driver','jdbc:mysql://<IPADDRESS>:<PORT>/jivex','user','pwd');
var strSQL ="SELECT * FROM jivehl7worklistentry where accessionNumber = '" + $('AccessionNumber') + "'";
var result = dbConn.executeCachedQuery(strSQL);
// You may access this result below with $('column_name')
// Replace the Patient ID fields with the correct format
msg['PID']['PID.2']['PID.2.1'] = result.getString("patientsId");
msg['PID']['PID.3']['PID.3.1'] = result.getString("patientsId");
msg['PID']['PID.4']['PID.4.1'] = result.getString("patientsId");
msg['OBR']['OBR.3']['OBR.3.1'] = result.getString("patientsId");
msg['OBR']['OBR.4']['OBR.4.1'] = result.getString("patientsId");
// Replace the patient DOB with the correct format
msg['PID']['PID.7']['PID.7.1'] = result.getString("patientsBirthDate");
return result;
} finally {
if (dbConn) {
dbConn.close();
}
}The DatabaseConnectionFactory line is modified to hide IP, username, etc The error message originally indicated I had the SQL query wrong, so I had adjusted it to what shows now. Now I'm getting the following: It seems like perhaps I'm using the retrieval of the data incorrectly, but this is based off of other examples (as far as I'm reading them). Am I missing something obvious? Thank you for any help you can provide. |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 1 reply
-
|
Please use proper code blocks and code formatting when you post here. Your code and error blocks above should look like the below. Please see https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks. The answer to your question is on the bottom of my response. try {
dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.cj.jdbc.Driver', 'jdbc:mysql://:/jivex', 'user', 'pwd');
var strSQL = "SELECT * FROM jivehl7worklistentry where accessionNumber = '" + $('AccessionNumber') + "'";
var result = dbConn.executeCachedQuery(strSQL);
// You may access this result below with $('column_name')
// Replace the Patient ID fields with the correct format
msg['PID']['PID.2']['PID.2.1'] = result.getString("patientsId");
msg['PID']['PID.3']['PID.3.1'] = result.getString("patientsId");
msg['PID']['PID.4']['PID.4.1'] = result.getString("patientsId");
msg['OBR']['OBR.3']['OBR.3.1'] = result.getString("patientsId");
msg['OBR']['OBR.4']['OBR.4.1'] = result.getString("patientsId");
// Replace the patient DOB with the correct format
msg['PID']['PID.7']['PID.7.1'] = result.getString("patientsBirthDate");
return result;
} finally {
if (dbConn) {
dbConn.close();
}
}You never called: result.next() So you get the invalid cursor problem. Go over the mirth forums, there are many examples of calling databases. I suggest you template your database calls and used shared connections, see https://github.com/pacmano1/Mirth-Snippets/blob/main/executeSharedDBStatement.js. |
Beta Was this translation helpful? Give feedback.
-
|
Thank you for your response. I've edited the message above per guidelines. I see better examples now of what you mention, using |
Beta Was this translation helpful? Give feedback.
-
|
Please fully read the code formatting article - you can use "JS" after your first three back ticks to make Github color your code appropriately.
|
Beta Was this translation helpful? Give feedback.
Please use proper code blocks and code formatting when you post here. Your code and error blocks above should look like the below. Please see https://docs.github.com/en/get-started/writing-on-github/working-with-advanced-formatting/creating-and-highlighting-code-blocks.
The answer to your question is on the bottom of my response.