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

Simple MySQL update

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

  • Simple MySQL update

    Hello. I have a simple insert channel working.
    It takes my patient demographic HL7 file and inserts it into a MySQL database.

    The next piece that has me stumped is to first have it look in the patient table for the patient id in the HL7 file. If it exists, update the demographics, if it doesn't, insert it as new.

    Some sample update type code would help me tons. Thanks.


    -D

  • #2
    Re:Simple MySQL update

    I tried following code for my MSSQL channel in one of the destination. Each code seperately works but when I put in If..else if Loop it does not. Try to play with it, logically it seems okay, so may be some minor mistake or you might have another idea. Please let me know if you have SUCESS, or anyone else does............

    Code:
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://xx.xx.xx.xx:1433/TestDB','sa','pwd');
    // YOUR CODE GOES HERE 
    var msg_type = $('msg_type');
    var ADTType = $('ADTType');
    var MRN = $('MRN');
    var fName = $('fName');
    var lName = $('lName');
    var DOB = $('DOB');
    var Bweight = '0';
    
    
    
    var sql = "SELECT COUNT(*) FROM demo WHERE MR = '"+ MRN +"'";
    var count = dbConn.executeUpdate(sql);
    
    
    if (count == 0){
    var expression = "INSERT IGNORE INTO demo values ('" + MRN + "', '" + fName + "', '" + lName + "', '" + DOB +"', '"+ Bweight + "');"; 
    var result = dbConn.executeUpdate(expression);
    }else if (count > 0){
    var expression = "UPDATE demo SET [fName]= '"+ fName + "', [lName] = '"+ lName +"', [DOB]= '"+ DOB +"' WHERE [MR] = '"+ MRN +"'";
    var result = dbConn.executeUpdate(expression);
    }
    dbConn.close();

    Thanks,

    Jaimin
    Last edited by jacobb; 04-06-2009, 09:48 AM. Reason: Added code block

    Comment


    • #3
      Re:Simple MySQL update

      I am seeing the same thing into MySQL.
      Seperately, ok. The If..Then kaks. Anyone else have any ideas?


      -D

      Comment


      • #4
        Question Bump

        Has anyone gotten a working If..then routine to add/update a MySQL database without choking.

        -D

        Comment


        • #5
          It sounds like you're close. Post your channel please.
          Jon Bartels

          Zen is hiring!!!!
          http://consultzen.com/careers/
          Talented healthcare IT professionals wanted. Engineers to sales to management.
          Good benefits, great working environment, genuinely interesting work.

          Comment


          • #6
            Simple MySQL update working.

            Ok, I finally got the loop to work. Its messy, but at least it works.

            Code:
            var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/freemeddev','xmlrpc','password');
            var query = 0;
            var msg = channelMap.get( 'msg' );
            //logger.info( "msg = " + msg.toString() );
            
            //-----------------------------------------------------------------------------------------
            //----- Library functions
            //-----------------------------------------------------------------------------------------
            
            function addslashes( str ) {
                // Handle null strings
                    if ( (str == null) || (str.toString() == '') ) {
                        return "";
                    } else { 
                        str = str.toString();
                    }
            
                // Otherwise, PHP style addslashes
                    try {
                    str = str.replace( '\\', '\\\\' );
                    str = escape( str );
                    str = str.replace( "'", '\\\'' );
                    str = str.replace( '"', '\\"' );
                    str = unescape( str );
                    } catch (e) { }
                return str;
            }
            
            function insertRecord( ) {
                var query = "INSERT IGNORE INTO patient ( " +
                        "   ptid " +
                        " , ptlname " +
                        " , ptfname " +
                        " , pthphone " +
                        " , ptwphone " +
                        " , ptaddr1 " +
                        " , ptaddr2 " +
                        " , ptcity " +
                        " , ptstate " +
                        " , ptzip " +
                        " , ptsex " +
                        " , ptdob " +
            
            
                    " ) VALUES ( "+
                        "   '"+addslashes( $('patientID')                  )+"' " +
                        " , '"+addslashes( $('patientLName')                   )+"' " +
                        " , '"+addslashes( $('patientFName')                  )+"' " +
                        " , '"+addslashes( $('patientHomePhone')                  )+"' " +
                        " , '"+addslashes( $('patientWorkPhone')                  )+"' " +
                        " , '"+addslashes( $('patientStreetAddress1')                  )+"' " +
                        " , '"+addslashes( $('patientStreetAddress2')                  )+"' " +
                        " , '"+addslashes( $('patientCity')                  )+"' " +
                        " , '"+addslashes( $('patientState')                  )+"' " +
                        " , '"+addslashes( $('patientZip')                  )+"' " +
                        " , '"+addslashes( $('patientSex')                  )+"' " +
                        " , '"+addslashes( $('patientDOB')                  )+"' " +
            
            
                    " );";
            var result = dbConn.executeUpdate( query );
            return result;
            }
            
            function updateRecord( ) {
                var query = "UPDATE patient SET ptlname = '" + addslashes($('patientLName')) + "' , ptfname = '" + addslashes($('patientFName')) + "' , pthphone = '" + addslashes($('patientHomePhone')) + "' , ptwphone = '" + addslashes($('patientWorkPhone')) + "' , ptaddr1 = '" + addslashes($('patientStreetAddress1')) + "' , ptaddr2 = '" + addslashes($('patientStreetAddress2')) + "' , ptcity = '" + addslashes($('patientCity')) + "' , ptstate = '" + addslashes($('patientState')) + "' , ptzip = '" + addslashes($('patientZip')) + "' , ptsex = '" + addslashes($('patientSex')) + "' , ptdob = '" + addslashes($('patientDOB')) + "' WHERE ptid = " + $('patientID')+";";
            var result = dbConn.executeUpdate( query );
            return result ;
            }
            
            function patientExists( ) {
            var cnt = 0;
                var expression = "SELECT COUNT(*) as cnt " + "FROM patient " + "WHERE ptid = '" + $('patientID')+ "'";
            var result = dbConn.executeCachedQuery(expression); 
            var testint = result.next();
            var iCount = result.getInt(1);
            result.close();
            return iCount;
            }
            
            
            //-----------------------------------------------------------------------------------------
            //----- Execution code starts here
            //-----------------------------------------------------------------------------------------
            
            
            var pfound = patientExists();
                logger.info(" ");
                logger.info( "  ******Start***** ");
            
            if ( pfound == 0 ) {
                logger.info( "  **Add New Record** ");
                insertRecord();
            }
            
                logger.info( "Patient Name = " + $('patientLName') + ", " + $('patientFName') );
                logger.info( "patientID = " + $('patientID') );
            
            if ( pfound == 1 ) {
            
                logger.info( "  **Update Record** ");
                updateRecord();
            }
                logger.info( "  ******Stop****** "); 
            
            dbConn.close();

            -D
            Last edited by jacobb; 04-06-2009, 09:47 AM. Reason: Added code block

            Comment


            • #7
              Just wanted to say thanks for this, with a little editing I was able to use this on one of my channels as well for updates.

              Comment


              • #8
                I second that!

                I stole your patientExists() function and it works like a charm for me. Thanks!

                Kent

                Comment


                • #9
                  SQL update

                  Here is a much simpler update code I use for SQL2005. Should work for MySQL.

                  var dbConn =

                  DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://SERVERNAME:1433/MIRTHD

                  B','sa','password');

                  var ACCT1 = $('ACCT');
                  var MR1 = $('MR');
                  var PNAME1 = $('PNAME');
                  var LOC1 = $('LOC');

                  var sql = "IF EXISTS (SELECT ACCT FROM DEMO WHERE ACCT='" + ACCT1 + "') UPDATE DEMO SET [MR]= '"+ MR1 + "', [PNAME] = '"+ PNAME1

                  +"', [LOC]= '"+ LOC1 +"' WHERE [ACCT] = '"+ ACCT1 +"' ELSE INSERT IGNORE INTO DEMO VALUES ('" + ACCT1 + "', '" + MR1 + "', '" + PNAME1 +

                  "', '" + LOC1 +"')";
                  var count = dbConn.executeUpdate(sql);
                  //var count = dbConn.executeCachedQuery(sql);


                  dbConn.close();

                  Comment


                  • #10
                    In order to accomplish this for postgresql...

                    Postgres doesn't have a MERGE or REPLACE command like mysql or sql server. They are working on it... but until then, I'm just making a quick little function as such...

                    CREATE FUNCTION upsert_patient(pid31 TEXT, pid51 TEXT, pid52 TEXT, pid53 TEXT, pid81 TEXT, pid181 TEXT, pv1441 TEXT, pv1451 TEXT) RETURNS VOID AS
                    $$
                    BEGIN
                    LOOP
                    -- okay, so first try to update the record...
                    UPDATE patient SET dischargetime = pv1451 where meditechidnumber = pid31;
                    IF found THEN
                    RETURN; -- update worked, bail out of the function
                    END IF;
                    -- if we make it to this point, the update failed, so insert a new row.
                    -- potential for unique-key failure, so some form of error checking enabled
                    -- this has the potential to create an endless loop. Watch this carefully.
                    BEGIN
                    INSERT IGNORE INTO patient (meditechidnumber, familyname, firstname, middleinitial, sex, patientaccountnum, admittime, dischargetime)
                    VALUES (pid31, pid51, pid52, pid53, pid81, pid181, pv1441, pv1451);
                    EXCEPTION WHEN unique_violation THEN
                    -- see, not sure what to do here, but doing nothing works - lets the loop try again...
                    END;
                    END LOOP;
                    END;


                    this is a one time thing, and of course it's per table - as it's customized per table.

                    So, then in the Mirth Transformer...

                    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('org.postgresql.Driver','jdbcostgresql://10.6.0.35:5432/rolling_census','rolling_census','password!');

                    var PID31 = $('PID31')
                    var PID51 = $('PID51')
                    var PID52 = $('PID52')
                    var PID53 = $('PID53')
                    var PID181 = $('PID181')
                    var PID81 = $('PID81')
                    var PV1391 = $('PV1391')
                    var PV1441 = $('PV1441')
                    var PV1451 = $('PV1451')

                    // this is a CUSTOM function in postgres - just for the patients table. Write more if you need.
                    var sql = "SELECT upsert_patient('" + PID31 + "', '" + PID51 + "', '" + PID52 + "', '" + PID53 + "', '" + PID81 + "', '" + PID181 + "', '" + PV1441 + "', '" + PV1451 + "')";

                    var count = dbConn.executeUpdate(sql);

                    dbConn.close();

                    hopefully that helps.

                    I'm using postgres 8.4 btw.
                    Last edited by laidback_01; 01-09-2010, 10:09 AM.

                    Comment

                    Working...
                    X