Announcement

Collapse
No announcement yet.

How to pass ' character to SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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 IGNORE 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.

  • #2
    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, 10:33 AM.

    Comment


    • #3
      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 IGNORE 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...
      Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

      Nicholas Rupley
      Work: 949-237-6069
      Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


      - How do I foo?
      - You just bar.

      Comment


      • #4
        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();

        Comment


        • #5
          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 IGNORE 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, 11:32 AM.
          Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

          Nicholas Rupley
          Work: 949-237-6069
          Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


          - How do I foo?
          - You just bar.

          Comment


          • #6
            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.

            Comment


            • #7
              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, 05:29 PM.

              Comment


              • #8
                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

                Comment


                • #9
                  A couple of years late... but:
                  var fname = $('fullName').toString().replace(/\'/g,"''");

                  Comment

                  Working...
                  X