Announcement

Collapse

Mirth Connect 3.12.0 Released!

Mirth Connect 3.12.0 is now available as an appliance update and on our GitHub page. This release includes database performance improvements, improves visual HL7 representation, message pruning, keystore handling, PDF generation, community contributions, and fixes several security vulnerabilities. This release also contains many improvements to commercial extensions. See the release notes for the list of fixes and updates.

Download | See What's New | Upgrade Guide | Release Notes

For discussion on this release, see this thread.
See more
See less

single quotes in HL7

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

  • single quotes in HL7

    Anybody knows why Mirth fails when single quotes are present in the HL7 fields and how to deals with that problem??

  • #2
    Re:single quotes in HL7

    What exactly is failing? We get single quotes in several status messages, which come back as part of acknowledgments. The only issue we've run across is when you attempt to build a SQL insert/update to put that status message into a database. The single quotes need to be escaped first, or SQL will fail.

    Mark...

    Comment


    • #3
      Re:single quotes in HL7

      I am sorry I was working on other things I didn't check the forum. I didn't know somebody replied.
      Yes that is what I am talking about. When you try to insert/update it fails.
      The single quotes need to be escaped first, or SQL will fail.
      How do you do that. Is that why people build filters for their channels.

      Comment


      • #4
        Re:single quotes in HL7

        You will need to replace single quotes with 2 single quotes. and then add a single quote to the beginning and end of your variable.

        Code:
        var tempFirstName = "";
        // Doubleup single quote for names link O'Brian
        tempFirstName = tempFirstName.replace(/'/g, "''"«»);
        // SQL Server stored proc expects varchar parameters to be passed within single quotes
        tempFirstName = "'" + tempFirstName + "'";
        Reid Hospital and Healthcare

        Comment


        • #5
          You could also do a split and join combination. In the example below, I am assigning part of the MSH segment in the msg to a global variable (in the transformer), and then in the Database Writer, I'm writing the global variable into a database table using an INSERT statement.

          globalMap.put("MSH_3_1", msg['MSH']['MSH.3']['MSH.3.1'].toString().split("'").join("\\'"));

          The nice thing about this is that it actually includes the single-quote in the database field, instead of just removing it. Unlike .replace, it will work the entire string, and not just the first element it finds. I know that .replace has a global option you can pass, but I could never get it working correctly.

          Admins, if this method was covered in a different thread, my apologies.
          Craig Haddix
          Data Coordinator
          Indiana Hemophilia & Thrombosis Center

          Comment


          • #6
            When using the replace method I am getting a, cannot call method 'replace' or undefined. I'm using this on the destination tab in the javascript area...is that correct?

            Comment


            • #7
              Please, post your code. This way it'll be easier to help you.

              Comment


              • #8
                Here is my current code for the Javascript on my database writer....

                I currently do not have this channel running, and I've removed where I placed the code since it was not working. I guess in my case I'm wondering where I should place the code and how I should utilize it since I'm updating and inserting records depending on if the patient/appointment currently exists or not.

                Any help is GREATLY appreciated

                EDIT: Looks like I did leave the replace code in this ....

                Code:
                //-----------------------------------------------------------------------------------------
                //----- Library functions
                //-----------------------------------------------------------------------------------------
                
                function insertRecord( ) {
                var query = "INSERT IGNORE INTO Appointment ( " +
                " ApptSlip " +
                " , ApptENC " +
                " , ApptPOS " +
                " , ApptProvCode " +
                " , ApptDate " +
                " , ApptTime " +
                " , LName " +
                " , FName " +
                " , MRN " +
                " , ApptStatus " +
                " , ApptReason " +
                " , ApptLastVisit " +
                " , ApptRefName " +
                " , ApptINS " +
                " , ApptINSName " +
                " , ApptINSGroup " +
                " , ApptINSPolNum " +
                " , ApptINSMName " +
                " , ApptINSAddress_1 " +
                " , ApptINSAddress_2 " +
                " , ApptSubLName " +
                " , ApptSubFName " +
                " , ApptSubSSN " +
                " , ApptSecINS " +
                " , ApptSecINSName " +
                " , ApptSecINSGroup " +
                " , ApptSecINSPolNum " +
                " , ApptSecINSMName " +
                " , ApptSecINSAddress_1 " +
                " , ApptSecINSAddress_2 " +
                " , ApptSecSubLName " +
                " , ApptSecSubFName " +
                " , ApptSecSubSSN " +
                " , ApptINSContact " +
                " , ApptEmployer " +
                " , ApptSET " +
                " , ApptDateBooked " +
                " , ApptINSSet " +
                " , ApptWCFax " +
                " , ApptWCADJ " +
                " , ApptWCPhone " +
                " , ApptEmpName " +
                " , ApptEmpAddress_1 " +
                " , ApptEmpAddress_2 " +
                " , ApptEmpCity " +
                " , ApptEmpState " +
                " , ApptEmpZip " +
                " , ApptEmpPhone " +
                " , ApptPreAuth " +
                " , ApptPINSPhone " +
                " , ApptSINSPhone " +
                " , filemaker " +
                
                " ) VALUES ( "+
                " '"+( $('ApptSlip') )+"' " +
                " , '"+( $('ApptENC') )+"' " +
                " , '"+( $('ApptPOS') )+"' " +
                " , '"+( $('ApptProvCode') )+"' " +
                " , '"+( $('ApptDate') )+"' " +
                " , '"+( $('ApptTime') )+"' " +
                " , '"+( $('LName') )+"' " +
                " , '"+( $('FName') )+"' " +
                " , '"+( $('MRN') )+"' " +
                " , '"+( $('ApptStatus') )+"' " +
                " , '"+( $('ApptReason') )+"' " +
                " , '"+( $('ApptLastVisit') )+"' " +
                " , '"+( $('ApptRefName') )+"' " +
                " , '"+( $('ApptINS') )+"' " +
                " , '"+( $('ApptINSName') )+"' " +
                " , '"+( $('ApptINSGroup') )+"' " +
                " , '"+( $('ApptINSPolNum') )+"' " +
                " , '"+( $('ApptINSMName') )+"' " +
                " , '"+( $('ApptINSAddress_1') )+"' " +
                " , '"+( $('ApptINSAddress_2') )+"' " +
                " , '"+( $('ApptSubLName') )+"' " +
                " , '"+( $('ApptSubFName') )+"' " +
                " , '"+( $('ApptSubSSN') )+"' " +
                " , '"+( $('ApptSecINS') )+"' " +
                " , '"+( $('ApptSecINSName') )+"' " +
                " , '"+( $('ApptSecINSGroup') )+"' " +
                " , '"+( $('ApptSecINSPolNum') )+"' " +
                " , '"+( $('ApptSecINSMName') )+"' " +
                " , '"+( $('ApptSecINSAddress_1') )+"' " +
                " , '"+( $('ApptSecINSAddress_2') )+"' " +
                " , '"+( $('ApptSecSubLName') )+"' " +
                " , '"+( $('ApptSecSubFName') )+"' " +
                " , '"+( $('ApptSecSubSSN') )+"' " +
                " , '"+( $('ApptINSContact') )+"' " +
                " , '"+( $('ApptEmployer') )+"' " +
                " , '"+( $('ApptSET') )+"' " +
                " , '"+( $('ApptDateBooked') )+"' " +
                " , '"+( $('ApptINSSet') )+"' " +
                " , '"+( $('ApptWCFax') )+"' " +
                " , '"+( $('ApptWCADJ') )+"' " +
                " , '"+( $('ApptWCPhone') )+"' " +
                " , '"+( $('ApptEmpName') )+"' " +
                " , '"+( $('ApptEmpAddress_1') )+"' " +
                " , '"+( $('ApptEmpAddress_2') )+"' " +
                " , '"+( $('ApptEmpCity') )+"' " +
                " , '"+( $('ApptEmpState') )+"' " +
                " , '"+( $('ApptEmpZip') )+"' " +
                " , '"+( $('ApptEmpPhone') )+"' " +
                " , '"+( $('ApptPreAuth') )+"' " +
                " , '"+( $('ApptPINSPhone') )+"' " +
                " , '"+( $('ApptSINSPhone') )+"' " +
                " , '"+( $('filemaker') )+"' " +
                " );";
                var result = dbConn.executeUpdate( query );
                return result;
                }
                
                
                function updateRecord( ) {
                var query = "UPDATE Appointment SET ApptENC = '" +($('ApptENC')) +
                "' , ApptPOS = '" +($('ApptProvCode')) +
                "' , ApptDate = '" +($('ApptDate')) +
                "' , ApptTime = '" +($('ApptTime')) +
                "' , LName = '" +($('LName')) +
                "' , FName = '" +($('FName')) +
                "' , MRN = '" +($('MRN')) +
                "' , ApptStatus = '" +($('ApptStatus')) +
                "' , ApptReason = '" +($('ApptReason')) +
                "' , ApptLastVisit = '" +($('ApptLastVisit')) +
                "' , ApptRefName = '" +($('ApptRefName')) +
                "' , ApptINS = '" +($('ApptINS')) +
                "' , ApptINSName = '" +($('ApptINSName')) +
                "' , ApptINSGroup = '" +($('ApptINSGroup')) +
                "' , ApptINSPolNum = '" +($('ApptINSPolNum')) +
                "' , ApptINSMName = '" +($('ApptINSMName')) +
                "' , ApptINSAddress_1 = '" +($('ApptINSAddress_1')) +
                "' , ApptINSAddress_2 = '" +($('ApptINSAddress_2')) +
                "' , ApptSubLName = '" +($('ApptSubLName')) +
                "' , ApptSubFName = '" +($('ApptSubFName')) +
                "' , ApptSubSSN = '" +($('ApptSubSSN')) +
                "' , ApptSecINS = '" +($('ApptSecINS')) +
                "' , ApptSecINSName = '" +($('ApptSecINSName')) +
                "' , ApptSecINSGroup = '" +($('ApptSecINSGroup')) +
                "' , ApptSecINSPolNum = '" +($('ApptSecINSPolNum')) +
                "' , ApptSecINSMName = '" +($('ApptSecINSMName')) +
                "' , ApptSecINSAddress_1 = '" +($('ApptSecINSAddress_1')) +
                "' , ApptSecINSAddress_2 = '" +($('ApptSecINSAddresS_2')) +
                "' , ApptSecSubLName = '" +($('ApptSecSubLName')) +
                "' , ApptSecSubFName = '" +($('ApptSecSubFName')) +
                "' , ApptSecSubSSN = '" +($('ApptSecSubSSN')) +
                "' , ApptINSContact = '" +($('ApptINSContact')) +
                "' , ApptEmployer = '" +($('ApptEmployer')) +
                "' , ApptSET = '" +($('ApptSET')) +
                "' , ApptDateBooked = '" +($('ApptDateBooked')) +
                "' , ApptINSSet = '" +($('ApptINSSet')) +
                "' , ApptWCFax = '" +($('ApptWCFax')) +
                "' , ApptWCADJ = '" +($('ApptWCADJ')) +
                "' , ApptWCPhone = '" +($('ApptWCPhone')) +
                "' , ApptEmpName = '" +($('ApptEmpName')) +
                "' , ApptEmpAddress_1 = '" +($('ApptEmpAddress_1')) +
                "' , ApptEmpAddress_2 = '" +($('ApptEmpAddress_2')) +
                "' , ApptEmpCity = '" +($('ApptEmpCity')) +
                "' , ApptEmpState = '" +($('ApptEmpState')) +
                "' , ApptEmpZip = '" +($('ApptEmpZip')) +
                "' , ApptEmpPhone = '" +($('ApptEmpPhone')) +
                "' , ApptPreAuth = '" +($('ApptPreAuth')) +
                "' , ApptPINSPhone = '" +($('ApptPINSPhone')) +
                "' , ApptSINSPhone = '" +($('ApptSINSPhone')) +
                "' , filemaker = '" +($('filemaker')) +
                
                "' WHERE ApptSlip = " + $('ApptSlip')+";";
                var result = dbConn.executeUpdate( query );
                return result ;
                }
                
                function appointmentExists( ) {
                var cnt = 0;
                var expression = "SELECT COUNT(*) as cnt " + "FROM Appointment " + "WHERE ApptSlip = '" + $('ApptSlip')+ "'";
                var result = dbConn.executeCachedQuery(expression);
                var testint = result.next();
                var iCount = result.getInt(1);
                result.close();
                return iCount;
                }
                
                
                //-----------------------------------------------------------------------------------------
                //----- Execution code starts here
                //-----------------------------------------------------------------------------------------
                
                LName.replace(/"'"/g, "''");
                
                var pfound = appointmentExists();
                logger.info(" ");
                logger.info( " ******Start***** ");
                
                if ( pfound == 0 ) {
                logger.info( " **Add New Record** ");
                insertRecord();
                }
                
                logger.info( "Patient Name = " + $('LName') + ", " + $('FName') );
                logger.info( "ApptSlip = " + $('ApptSlip') );
                
                if ( pfound == 1 ) {
                
                logger.info( " **Update Record** ");
                updateRecord();
                }
                logger.info( " ******Stop****** ");
                
                dbConn.close();
                Last edited by dgrizzard; 09-14-2009, 10:21 AM.

                Comment

                Working...
                X