web stats
How to pass ' character to SQL - Mirth Community

Go Back   Mirth Community > Mirth Connect > Support

Reply
 
Thread Tools Display Modes
  #1  
Old 02-22-2012, 09:58 AM
donsls donsls is offline
OBX.2 Kenobi
 
Join Date: Feb 2010
Posts: 72
donsls is on a distinguished road
Default How to pass ' character to SQL

One of my channels in Mirth is directly writting the HL7 message into an Oracle table. I use the INSERT INTO table. statement to write to the table. My problem is when the hl7 message contains ' character, my sql statement fails. Is there away to pass the sql with this character or remnove it and pass it? Sometimes this character is in the hl7 in many places, so cannot say for sure the location of it.
Any help is greatly appreciated.
Reply With Quote
  #2  
Old 02-22-2012, 10:27 AM
upstart33 upstart33 is offline
Mirth Guru
 
Join Date: Dec 2010
Location: Chicago, IL.
Posts: 459
upstart33 is on a distinguished road
Default

You could try something like this in your Pre-Processing script section:

Code:
return message..replace(/\'/g, "'");

Or you can do it as the last transformer step:

Code:
// if you don't have on outbound template
msg = msg.toString().replace(/\' /g, "'");
// if you do have an outbound template
tmp = tmp.toString().replace(/\' /g, "'");

Last edited by upstart33; 02-22-2012 at 10:33 AM.
Reply With Quote
  #3  
Old 02-22-2012, 12:16 PM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,119
narupley is on a distinguished road
Default

You should be able to escape single quotes by adding a second single quote. For example, if you wanted to insert the value "Bob's", you would do:

INSERT INTO SOMETABLE VALUES('Bob''s')

So, for any field you want to insert, just add a .replace(/\'/g,"''"). That's .replace(/\<singlequote>/g,<doublequote><singlequote><singlequote><doublequ ote>).

You can do as upstart33 suggested and put it in the preprocessor; that's perfectly fine! Just know that if you do that, it will affect the raw data from then on. So if you have two destinations, one LLP Sender and one Database Writer, the LLP Sender will be impacted by anything you do in the preprocessor script. So if you have "O'Malley^Patrick" in PID.5, then what you'd actually be sending through your LLP Sender is "O''Malley^Patrick". If all you're using the channel for is just to update into a database, then that should be fine. Better to play it safe than sorry though...
Reply With Quote
  #4  
Old 02-27-2012, 10:17 AM
donsls donsls is offline
OBX.2 Kenobi
 
Join Date: Feb 2010
Posts: 72
donsls is on a distinguished road
Default

I tried this method, but it's not working. Not sure what the problem is. Following is the code that I use.

msg = msg.toString().replace(/\' /g, "'");
var ack_message = SerializerFactory.getHL7Serializer(false, false).fromXML(msg);
var strSQL = " Insert into tblHL7 (ID_T, msg1) values('" + sidforhl7msg + "', '" + ack_message + "')";

var result = dbConnOracle.executeCachedQuery(strSQL);
dbConnOracle.close();
Reply With Quote
  #5  
Old 02-27-2012, 11:10 AM
narupley's Avatar
narupley narupley is online now
Mirth Employee
 
Join Date: Oct 2010
Posts: 7,119
narupley is on a distinguished road
Default

Quote:
Originally Posted by donsls View Post
I tried this method, but it's not working. Not sure what the problem is. Following is the code that I use.

msg = msg.toString().replace(/\' /g, "'");
var ack_message = SerializerFactory.getHL7Serializer(false, false).fromXML(msg);
var strSQL = " Insert into tblHL7 (ID_T, msg1) values('" + sidforhl7msg + "', '" + ack_message + "')";

var result = dbConnOracle.executeCachedQuery(strSQL);
dbConnOracle.close();
Hmm, well first off, you have .replace(/\' /g, "'") instead of .replace(/\'/g,"''"). As I said, that's .replace(/\<singlequote>/g,<doublequote><singlequote><singlequote><doublequ ote>).

Also, you definitely won't want to do anything like msg = msg.toString().replace(). The variable msg is an XML object, not a string, so you'll run into issues when Mirth tries to serialize it. If you really wanted to escape all single quotes in the entire message, you would do it in the preprocessor with:

Code:
return message.replace(/\'/g,"''");
However, I would advise against using the preprocessor for doing something like this. Instead, only escape the pertinent fields, and only when you're inserting them into the database. Otherwise, as I said, if you wanted to send the encoded data through a different destination, all single quotes will have become double quotes. So let's say for example that you wanted to insert the patient's last name into the database. You would create a connector map variable like so:

Code:
$co('patientLastName',msg.PID['PID.5']['PID.5.1'].toString().replace(/\'/g,"''"));
And then, insert it into the database with something like:

Code:
INSERT INTO PATIENTS (LASTNAME) VALUES (${patientLastName});
That way, you're still making sure that your SQL is valid, and you're not modifying the raw data.

Last edited by narupley; 02-27-2012 at 11:32 AM.
Reply With Quote
  #6  
Old 02-28-2012, 05:52 AM
donsls donsls is offline
OBX.2 Kenobi
 
Join Date: Feb 2010
Posts: 72
donsls is on a distinguished road
Default

Yes, that worked. Thanks a lot. I am not worried about other destinations as my channel is writting only to the database. There are no other destinations.
Reply With Quote
  #7  
Old 09-13-2012, 05:27 PM
laidback_01 laidback_01 is offline
OBX.2 Kenobi
 
Join Date: Apr 2008
Location: Flathead Valley MT
Posts: 51
laidback_01
Default What about the database itself?

Another thing you can do is determine just how the database you are inserting or updating into handles this problem.

Postgres, for example, allows you to set your own Dollar-quoted String Constants.
http://www.postgresql.org/docs/curre...x-lexical.html

Now, I don't know much about the other databases, but I cannot assume that Postgres is the only one to allow such a thing...

Keep in mind that Mirth is not the only tool in the box.

Example of a db writer query
Code:
var dbConn = DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://192.29.109.81:5432/hl7_message_customizations','mirth_prod','notthepass');

var query = "INSERT into raw_message (msg_id, message, received_date) values ($NO_WORRIES$" + $('msg_control_id') + "$NO_WORRIES$, $NO_WORRIES$" + messageObject.getRawData() + "$NO_WORRIES$, (select current_timestamp) )";

var result = dbConn.executeUpdate(query);

dbConn.close();

Last edited by laidback_01; 09-13-2012 at 05:29 PM.
Reply With Quote
  #8  
Old 03-20-2017, 01:08 AM
bjborole bjborole is offline
Mirth Newb
 
Join Date: Mar 2013
Location: Pune, India
Posts: 16
bjborole is on a distinguished road
Send a message via Skype™ to bjborole
Default Not working

[2017-03-20 14:37:00,022] ERROR (com.mirth.connect.connectors.js.JavaScriptDispatc her:193): Error evaluating JavaScript Writer (JavaScript Writer "Destination 2" on channel 8746d7c9-ee05-4f48-8a3b-ee607df52919).
com.mirth.connect.server.MirthJavascriptTransforme rException: CHANNEL:
Tier_2_GA_CSV_PHYSCONNECTOR:
Destination 2SCRIPT SOURCE:
JavaScript WriterSOURCE CODE:
39: }40: eval('importPackage(' + Packages.java.lang.Class.forName(className).getPac kage().getName() + ')');41: }42: }43: function doScript() {44: var fname = $('fullName').replace(/\'/g,"''");45: 46: logger.debug($('fullName'));47: logger.debug(fname); 48: }LINE NUMBER:
44DETAILS:
The choice of Java method java.lang.String.replace matching JavaScript argument types (function,string) is ambiguous; candidate methods are: class java.lang.String replace(char,char) class java.lang.String replace(java.lang.CharSequence,java.lang.CharSeque nce)
at 61d41dbb-9353-4aa5-9670-564c0f9a8526:44 (doScript)
at 61d41dbb-9353-4aa5-9670-564c0f9a8526:49
at com.mirth.connect.connectors.js.JavaScriptDispatch er$JavaScriptDispatcherTask.doCall(JavaScriptDispa tcher.java:184)
at com.mirth.connect.connectors.js.JavaScriptDispatch er$JavaScriptDispatcherTask.doCall(JavaScriptDispa tcher.java:122)
at com.mirth.connect.server.util.javascript.JavaScrip tTask.call(JavaScriptTask.java:113)
at java.util.concurrent.FutureTask.run(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor.runWorker( Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run (Unknown Source)
at java.lang.Thread.run(Unknown Source)


Mirth Connect version : 3.4.2.8129
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -8. The time now is 10:16 AM.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.
Mirth Corporation