Announcement

Collapse
No announcement yet.

MySQL in Transformer

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

  • MySQL in Transformer

    I have 3 mappers to retrieve data from an incoming HL7 message. I'm using that data to pull information from a database to replace 2 fields in the same HL7 message. I have came up with the following code, but i'm gettin syntax errors i can't figure out. Any help would be appreciated!

    tmp=msg;
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost:3306/merge_md', '****', '*****');
    var REQEHS = "SELECT REQ FROM success_ehs WHERE PID = '$('EHS_PID')' AND OrdNum = '$('HL_ON')' AND OrderFilled = '0'";
    var result = dbConn.executeCachedQuery(REQEHS);

    tmp['OBR']['OBR.2']['OBR.2.1'] = result.getString(1);
    tmp['ORC']['ORC.2']['ORC.2.1'] = result.getString(1);
    tmp['PID']['PID.3']['PID.3.1'] = $('EHS_PID');

  • #2
    Originally posted by russellk28 View Post
    I have 3 mappers to retrieve data from an incoming HL7 message. I'm using that data to pull information from a database to replace 2 fields in the same HL7 message. I have came up with the following code, but i'm gettin syntax errors i can't figure out. Any help would be appreciated!

    tmp=msg;
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost:3306/merge_md', '****', '*****');
    var REQEHS = "SELECT REQ FROM success_ehs WHERE PID = '$('EHS_PID')' AND OrdNum = '$('HL_ON')' AND OrderFilled = '0'";
    var result = dbConn.executeCachedQuery(REQEHS);

    tmp['OBR']['OBR.2']['OBR.2.1'] = result.getString(1);
    tmp['ORC']['ORC.2']['ORC.2.1'] = result.getString(1);
    tmp['PID']['PID.3']['PID.3.1'] = $('EHS_PID');
    Your "REQEHS" query string uses double quotes at the top level, meaning that any single quotes are going to be regarded as literal. You need to make the string consistent, pick either single quotes or double quotes and stick with it. Also, any place that you want to concatenate variables, you need to use the "+" operator.
    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


    • #3
      Thanks for your help. I have made the following changes, and i'm closer, but still not working. Here is my code now.

      tmp=msg;
      var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost/merge_md', 'root', 's0gYr3Owq');
      var result = dbConn.executeCachedQuery("SELECT REQ from success_ehs WHERE PID = $('EHS_PID') + OrdNum = $('HL_ON') + OrderFilled = '0'");
      dbConn.close();
      result.next();

      tmp['OBR']['OBR.2']['OBR.2.1'] = result.getString(1);

      The error message i'm getting is as follows.

      DETAILS: Wrapped com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: FUNCTION merge_md.$ does not exist

      Comment


      • #4
        Search MySQL from Message Data

        I have the following code

        tmp=msg;
        var PIDMSG = msg['PID']['PID.3']['PID.3.1'].toString();
        var OrdNumMSG = msg['OBR']['OBR.4']['OBR.4.1'].toString()
        var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/merge_md','root','s0gYr3Owq');
        var result = dbConn.executeCachedQuery("SELECT 'REQ' FROM 'success_ehs' WHERE 'PID' = "PIDMSG.getString(1)" + 'OrderFilled' = '0'");
        dbConn.close();
        result.next();

        tmp['OBR']['OBR.2']['OBR.2.1'] = result.getString(1);
        tmp['ORC']['ORC.2']['ORC.2.1'] = result.getString(1);
        tmp['PID']['PID.2']['PID.2.1'] = PIDMSG.getString(1);

        The code will not pass validation because Mirth says "Error on line 5: missing ) after argument list"

        I'm stumped.. any help would be appreciated!

        Comment


        • #5
          Originally posted by russellk28 View Post
          Thanks for your help. I have made the following changes, and i'm closer, but still not working. Here is my code now.

          tmp=msg;
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.mysql.jdbc.Driver', 'jdbc:mysql://localhost/merge_md', 'root', 's0gYr3Owq');
          var result = dbConn.executeCachedQuery("SELECT REQ from success_ehs WHERE PID = $('EHS_PID') + OrdNum = $('HL_ON') + OrderFilled = '0'");
          dbConn.close();
          result.next();

          tmp['OBR']['OBR.2']['OBR.2.1'] = result.getString(1);

          The error message i'm getting is as follows.

          DETAILS: Wrapped com.mysql.jdbc.exceptions.MySQLSyntaxErrorExceptio n: FUNCTION merge_md.$ does not exist
          As I said, if you want to include in-line variables, you need to correctly concatenate the query string. Or, you can use getConnection() on the DatabaseConnection object to get the underlying java.sql.Connection object, and then use prepared statements instead.
          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
            Originally posted by russellk28 View Post
            I have the following code

            tmp=msg;
            var PIDMSG = msg['PID']['PID.3']['PID.3.1'].toString();
            var OrdNumMSG = msg['OBR']['OBR.4']['OBR.4.1'].toString()
            var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/merge_md','root','s0gYr3Owq');
            var result = dbConn.executeCachedQuery("SELECT 'REQ' FROM 'success_ehs' WHERE 'PID' = "PIDMSG.getString(1)" + 'OrderFilled' = '0'");
            dbConn.close();
            result.next();

            tmp['OBR']['OBR.2']['OBR.2.1'] = result.getString(1);
            tmp['ORC']['ORC.2']['ORC.2.1'] = result.getString(1);
            tmp['PID']['PID.2']['PID.2.1'] = PIDMSG.getString(1);

            The code will not pass validation because Mirth says "Error on line 5: missing ) after argument list"

            I'm stumped.. any help would be appreciated!
            That's because you have invalid JavaScript syntax on line 5. http://en.wikipedia.org/wiki/JavaScript_syntax#String
            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

            Working...
            X