Announcement

Collapse
No announcement yet.

Calling stored procedure

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

  • Calling stored procedure

    =====================
    Environment:

    Windows Server 2000 SP4, Microsoft SQL Server 2000 SP3, Mirth HL7 v1.1

    =====================
    I get "Write statement should be an insert / update / delete sql statement" when I try to call a stored proc.

    Is there a reference code for calling stored procedures from Mirth HL7?

    Thanks in advance!


    ====================
    E.g. "spHL7AddUpdatePatient" stored proc would do an update if the patient record exists or does an insert otherwise.

    //////////////////////////////////////////////

    Channel has 2 database destinations and both call "spHL7AddUpdatePatient" using the folliwng syntax.

    DestForA04 --> SQL Statement:

    EXEC spHL7AddUpdatePatient ${patAccount}, ${patPrefix}, ${patFirstName}, ${patMiddleName}, ${patLastName}, ${patSuffix}, ${patAddress1}, ${patAddress2}, ${patCity}, ${patState}, ${patZipCode}, ${patPhone1}, ${patPhone1Type}, ${patPhone2}, ${patPhone2Type}, ${patGender}, ${patDOB}, ${patDeathDate}, ${patSSN};

    DestForA08 --> SQL Statement:

    EXEC spHL7AddUpdatePatient ${patAccount}, ${patPrefix}, ${patFirstName}, ${patMiddleName}, ${patLastName}, ${patSuffix}, ${patAddress1}, ${patAddress2}, ${patCity}, ${patState}, ${patZipCode}, ${patPhone1}, ${patPhone1Type}, ${patPhone2}, ${patPhone2Type}, ${patGender}, ${patDOB}, ${patDeathDate}, ${patSSN};

    //////////////////////////////////////////////

    Getting following runtime error:

    ********************************
    ERROR [5_destination_1.dispatcher.1] org.mule.impl.DefaultExceptionStrategy: Caught exception in Exception Strategy: Write statement should be an insert / update / delete sql statement
    java.lang.IllegalArgumentException: Write statement should be an insert / update / delete sql statement
    at org.mule.providers.jdbc.JdbcMessageDispatcher.doDi spatch(JdbcMessageDispatcher.java:84)
    at org.mule.providers.AbstractMessageDispatcher$Worke r.run(AbstractMessageDispatcher.java:257)
    at org.mule.impl.work.WorkerContext.run(WorkerContext .java:290)
    at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.runTask(ThreadPoolExecutor. java:650)
    at edu.emory.mathcs.backport.java.util.concurrent.Thr eadPoolExecutor$Worker.run(ThreadPoolExecutor.java :675)
    at java.lang.Thread.run(Unknown Source)
    ********************************

  • #2
    Re: Calling stored procedure

    Unfortunantly Mirth does not support stored procedures. If you would like this functionality, please add it to the issue tracker and we can get to work on it for a future release.

    Thank you!
    Chris Lang

    Comment


    • #3
      Re: Calling stored procedure

      Chris,

      Thank you very much for your email review feedback. I?m glad this turned out to be an ?acceptable? workaround.

      If the Mirth HL7 team feels this would be a good workaround example for Mirth users, this may be uploaded to File Repository.

      Following has been explained in detail below.

      1. Creating Channel.
      2. ?Fake Insert? SQL Statement
      3. Filter to check A01, A04, A08, A10, A28, A31 message types
      4. Transformer Step which calls stored proc with arguments
      5. spHL7AddOrUpdatePatient - Microsoft SQL Server 2000 Stored Procedure

      NOTE: Mirth throws this warning which is being ignored for now:
      WARN [6_destination_1.dispatcher.1] org.mule.providers.jdbc.JdbcMessageDispatcher: Row count for write should be 1 and not 0

      Hope it helps!

      Thank you,

      ===================================
      Workaround to call a stored procedure from Mirth is explained below. Limited testing has been done.
      ===================================

      Environment: Windows Server 2000 SP4, Microsoft SQL Server 2000 Sp3, Mirth HL7 v1.1

      ============== XXXXXXXXXXX ==============

      1. Create an LLP channel.
      Send ACK: No.

      Destination Name: PatientTable
      Connector Type: Database Writer

      ============== XXXXXXXXXXX ==============

      2. Under ?Destinations? tab in Mirth Administrator used a fake insert for SQL Statement:
      INSERT IGNORE INTO PATIENT (Account)
      SELECT 'Fake Insert' WHERE 1 = 0
      This is basically to ?trick? Mirth which expects an Insert, Update or delete for a database write.

      ============== XXXXXXXXXXX ==============
      3. Filter

      // Filter for A01, A04, A08, A10, A28, A31 to handle add or update patient.
      // Refer transformer step "Call stored proc spHL7AddOrUpdatePatient"
      globalMap.put('jdbcDriver', "net.sourceforge.jtds.jdbc.Driver");
      globalMap.put('dbAddress', "jdbc:jtds:sqlserver://TestServer:1433/TestDB;");
      globalMap.put('dbLogin', "sa");
      globalMap.put('dbPassword', "test");

      if (msg['MSH']['MSH.9']['CM_MSG.2'] == 'A01' ||
      msg['MSH']['MSH.9']['CM_MSG.2'] == 'A04' ||
      msg['MSH']['MSH.9']['CM_MSG.2'] == 'A08' ||
      msg['MSH']['MSH.9']['CM_MSG.2'] == 'A10' ||
      msg['MSH']['MSH.9']['CM_MSG.2'] == 'A28' ||
      msg['MSH']['MSH.9']['CM_MSG.2'] == 'A31&#039 {
      return true;
      }
      else {
      logger.info('Not an A01, A04, A08, A10, A28, or A31 message - Msg. ignored'
      return false;
      }

      ============== XXXXXXXXXXX ==============
      4. Transformer Step
      Name: Call stored proc spHL7AddOrUpdatePatient
      Type: JavaScript

      JavaScript Code:

      var tempAcct = "NULL";
      var tempPrefix = "NULL";
      var tempFirstName = "NULL";
      var tempLastName = "NULL";
      var tempMiddleName = "NULL";
      var tempSuffix = "NULL";
      var tempBirthDate = "NULL";
      var tempGender = "NULL";
      var tempAddress1 = "NULL";
      var tempAddress2 = "NULL";
      var tempCity = "NULL";
      var tempState = "NULL";
      var tempZipCode = "NULL";
      var tempPhone1 = "NULL";
      var tempPhone1Type = "NULL";
      var tempPhone2 = "NULL";
      var tempPhone2Type = "NULL";
      var tempDeathDate = "NULL";
      var tempModifiedSSN = "NULL";

      // External patient account number
      if (msg['PID']['PID.3']['CX.1'].text()[0] != null) {
      tempAcct = "'" + msg['PID']['PID.3']['CX.1'].text()[0] + "'";
      }
      // Patient name prefix
      if (msg['PID']['PID.5']['XPN.5'].text()[0] != null) {
      tempPrefix = "'" + msg['PID']['PID.5']['XPN.5'].text()[0] + "'";
      }
      // Patient first name
      if (msg['PID']['PID.5']['XPN.2'].text()[0] != null) {
      tempFirstName = msg['PID']['PID.5']['XPN.2'].text()[0].toString();
      // 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 + "'";
      }
      // Patient last name
      if (msg['PID']['PID.5']['XPN.1'].text()[0] != null) {
      tempLastName = msg['PID']['PID.5']['XPN.1'].text()[0].toString();
      // Doubleup single quote for names link O'Brian
      tempLastName = tempLastName.replace(/'/g, "''");
      // SQL Server stored proc expects varchar parameters to be passed within single quotes
      tempLastName = "'" + tempLastName + "'";
      }
      // Patient middle name
      if (msg['PID']['PID.5']['XPN.3'].text()[0] != null) {
      tempMiddleName = msg['PID']['PID.5']['XPN.3'].text()[0].toString();
      // Doubleup single quote for names link O'Brian
      tempMiddleName = tempMiddleName.replace(/'/g, "''");
      // SQL Server stored proc expects varchar parameters to be passed within single quotes
      tempMiddleName = "'" + tempMiddleName + "'";
      }
      // Patient name suffix
      if (msg['PID']['PID.5']['XPN.4'].text()[0] != null) {
      tempSuffix = "'" + msg['PID']['PID.5']['XPN.4'].text()[0] + "'";
      }
      // Patient birth date
      if (msg['PID']['PID.7']['TS.1'].text()[0] != null) {
      // Get birth date in mm/dd/yyyy format
      var dob = msg['PID']['PID.7']['TS.1'].text()[0];
      var ar = dob.match(/(\d\d\d\d)(\d\d)(\d\d)/);
      tempBirthDate = RegExp.$2 + '/' + RegExp.$3 + '/' + RegExp.$1;
      tempBirthDate = "'" + tempBirthDate + "'";
      }
      // Patient gender
      if (msg['PID']['PID.8'].text()[0] != null) {
      tempGender = "'" + msg['PID']['PID.8'].text()[0] + "'";
      }
      // Patient address1
      if (msg['PID']['PID.11']['XAD.1'].text()[0] != null) {
      tempAddress1 = msg['PID']['PID.11']['XAD.1'].text()[0].toString();
      // Doubleup single quote for address like St.Peter's Hospital Rd
      tempAddress1 = tempAddress1.replace(/'/g, "''");
      // SQL Server stored proc expects varchar parameters to be passed within single quotes
      tempAddress1 = "'" + tempAddress1 + "'";
      }
      // Patient address2
      if (msg['PID']['PID.11']['XAD.2'].text()[0] != null) {
      tempAddress2 = msg['PID']['PID.11']['XAD.2'].text()[0].toString();
      // Doubleup single quote for address like St.Peter's Hospital Rd
      tempAddress2 = tempAddress2.replace(/'/g, "''");
      // SQL Server stored proc expects varchar parameters to be passed within single quotes
      tempAddress2 = "'" + tempAddress2 + "'";
      }
      // Patient city
      if (msg['PID']['PID.11']['XAD.3'].text()[0] != null) {
      tempCity = msg['PID']['PID.11']['XAD.3'].text()[0].toString();
      // Doubleup single quote in case city name has a single quote
      tempCity = tempCity.replace(/'/g, "''");
      // SQL Server stored proc expects varchar parameters to be passed within single quotes
      tempCity = "'" + tempCity + "'";
      }
      // Patient state
      if (msg['PID']['PID.11']['XAD.4'].text()[0] != null) {
      tempState = "'" + msg['PID']['PID.11']['XAD.4'].text()[0] + "'";
      }

      // Patient zipcode
      if (msg['PID']['PID.11']['XAD.5'].text()[0] != null) {
      tempZipCode = "'" + msg['PID']['PID.11']['XAD.5'].text()[0] + "'";
      }

      // Get patient's phone1 w/o "( )" or "-"
      var tempPhone1AreaCode = "";
      var tempPhone1Number = "";
      // Patient phone1 - area code
      if (msg['PID']['PID.13']['XAD.6'].text()[0] != null) {
      tempPhone1AreaCode = msg['PID']['PID.13']['XTN.6'].text()[0];
      }
      // Patient phone1 - phone number
      if (msg['PID']['PID.13']['XTN.7'].text()[0] != null) {
      tempPhone1Number = msg['PID']['PID.13']['XTN.7'].text()[0];
      }
      if (tempPhone1AreaCode != "") {
      tempPhone1 = "'" + tempPhone1AreaCode + tempPhone1Number + "'";
      }
      else {
      tempPhone1 = "'" + tempPhone1Number + "'";
      }
      // Patient phone1 type
      if (msg['PID']['PID.13']['XTN.9'].text()[0] != null) {
      tempPhone1Type = "'" + msg['PID']['PID.13']['XTN.9'].text()[0] + "'";
      }

      // Get patient's phone2 w/o "( )" or "-"
      var tempPhone2AreaCode = "";
      var tempPhone2Number = "";
      // Patient phone2 - area code
      if (msg['PID']['PID.14']['XAD.6'].text()[0] != null) {
      tempPhone2AreaCode = msg['PID']['PID.14']['XTN.6'].text()[0];
      }
      // Patient phone2 - phone number
      if (msg['PID']['PID.14']['XTN.7'].text()[0] != null) {
      tempPhone2Number = msg['PID']['PID.14']['XTN.7'].text()[0];
      }
      if (tempPhone2AreaCode != "") {
      tempPhone2 = "'" + tempPhone2AreaCode + tempPhone2Number + "'";
      }
      else {
      tempPhone2 = "'" + tempPhone2Number + "'";
      }
      // Patient phone2 type
      if (msg['PID']['PID.14']['XTN.9'].text()[0] != null) {
      tempPhone2Type = "'" + msg['PID']['PID.14']['XTN.9'].text()[0] + "'";
      }

      // Get patient's death date in mm/dd/yyyy format
      if (msg['PID']['PID.29']['TS.1'].text()[0] != null) {
      var deathDate = msg['PID']['PID.29']['TS.1'].text()[0];
      if (deathDate != null) {
      var ar = deathDate.match(/(\d\d\d\d)(\d\d)(\d\d)/);
      tempDeathDate = RegExp.$2 + '/' + RegExp.$3 + '/' + RegExp.$1;
      tempDeathDate = "'" + tempDeathDate + "'";
      }
      }

      // Get patient SSN with "-" removed
      if (msg['PID']['PID.19'].text()[0] != null) {
      tempSSN = "'" + msg['PID']['PID.19'].text()[0].toString() + "'";
      tempModifiedSSN = tempSSN.replace(/-/g, "");
      }

      //DatabaseConnection
      var dbConn = DatabaseConnectionFactory.createDatabaseConnection (globalMap.get('jdbcDriver&#039, globalMap.get('dbAddress&#039, globalMap.get('dbLogin&#039, globalMap.get('dbPassword&#039);

      //Get parameters for the stored proc separated by ,
      // Stored proc call will look like "EXEC spHL7AddOrUpdatePatient '1234', 'Mr', 'TestFN', ..., '111223333'";
      var spParams = tempAcct + ', ' + tempPrefix + ', ' +
      tempFirstName + ', ' + tempMiddleName + ', ' +
      tempLastName + ', ' + tempSuffix + ', ' +
      tempAddress1 + ', ' + tempAddress2 + ', ' +
      tempCity + ', ' + tempState + ', ' +
      tempZipCode + ', ' + tempPhone1 + ', ' +
      tempPhone1Type + ', ' + tempPhone2 + ', ' +
      tempPhone2Type + ', ' + tempGender + ', ' +
      tempBirthDate + ', ' + tempDeathDate + ', ' +
      tempModifiedSSN;

      var expression = "EXEC spHL7AddOrUpdatePatient " + spParams;
      // Print the SQL statement for debugging purpose
      logger.info('SQL: ' + expression);
      var result = dbConn.executeCachedQuery(expression);
      //go to the first result
      result.next();
      //get the value from the first column as an integer
      var iReturnVal = result.getInt(1);
      //cleanup
      result.close();
      dbConn.close();
      if (iReturnVal > 0) {
      // Stored proc returns +ive no. for success
      logger.info('Processed add or update patient successfully.'
      }
      else {
      logger.info('Error: spHL7AddOrUpdatePatient failed.'
      }

      ============== XXXXXXXXXXX ==============
      5. Microsoft SQL Server 2000 Stored Procedure

      CREATE PROCEDURE spHL7AddOrUpdatePatient (
      @Account varchar (15),
      @Prefix varchar (10),
      @FirstName varchar (30),
      @MiddleName varchar (30),
      @LastName varchar (30),
      @Suffix varchar (20),
      @Address1 varchar (50),
      @Address2 varchar (50),
      @City varchar (30),
      @State varchar (3),
      @ZipCode varchar (10),
      @Phone1 varchar (30),
      @Phone1Type varchar (25),
      @Phone2 varchar (30),
      @Phone2Type varchar (25),
      @Gender varchar (1),
      @Birthdate varchar (12),
      @DeathDate varchar (12),
      @SSN varchar (9)
      )
      AS
      BEGIN
      IF EXISTS (SELECT PatientID FROM Patient WHERE Account = @Account)
      BEGIN
      -- UPDATE existing patient
      UPDATE Patient
      SET Account = @Account, Prefix = @Prefix, FirstName = @FirstName, MiddleName = @MiddleName,
      LastName = @LastName, Suffix = @Suffix, Address1 = @Address1, Address2 = @Address2,
      City = @City, State = @State, ZipCode = @ZipCode, Phone1 = @Phone1, Phone1Type = @Phone1Type,
      Phone2 = @Phone2, Phone2Type = @Phone2Type, Gender = @Gender, BirthDate = @Birthdate,
      DeathDate = @DeathDate, SSN = @SSN
      WHERE Account = @Account
      IF @@ERROR <> 0
      SELECT -1
      ELSE
      SELECT 1
      END
      ELSE
      BEGIN
      -- Add new patient
      INSERT IGNORE INTO Patient (Account, Prefix, FirstName, MiddleName, LastName, Suffix,
      Address1, Address2, City, State, ZipCode, Phone1, Phone1Type,
      Phone2, Phone2Type, Gender, BirthDate, DeathDate, SSN)
      VALUES (@Account, @Prefix, @FirstName, @MiddleName, @LastName, @Suffix,
      @Address1, @Address2, @City, @State, @ZipCode, @Phone1, @Phone1Type,
      @Phone2, @Phone2Type, @Gender, @Birthdate, @DeathDate, @SSN)
      IF @@ERROR <> 0
      SELECT -1
      ELSE
      SELECT 1
      END

      END

      Comment


      • #4
        Re: Calling stored procedure

        Was just looking at some of the old posts and had a thought ... What if I am more comfortable with the &#039;heavy lifting&#039; in SQL?

        1. Create an inbound queue table on the SQL server with all the fields needed by the incomming HL7 messages (including the message type)
        2. Use Mirth to make the simple inserts into this table
        3. Create a trigger on the table for inserts
        4. Have the trigger call my stored procedure with all the fancy insert/udpate logic

        Just an idea for those that would rather see this in SQL rather than JavaScript.

        Hope this helps somebody.
        Don

        Comment


        • #5
          Re: Calling stored procedure

          Sir,

          I was wondering if you can post a sample Insert statement? I am not that good in doing Javascript and more familiar in doing things in SQL Server. Our current setup for a channel is the Source is a file reader of HL7 messages and our Destination is a Database writer. How can me map the fields from a message into an INSERT statement for SQL Server?

          Thank you very much!

          Comment


          • #6
            Re: Calling stored procedure

            I&#039;m not sure if I understood your question correctly.

            Do you need a sample to call an INSERT statement from Mirth HL7 instead of doing the insert through a stored procedure?

            Comment


            • #7
              Re: Calling stored procedure

              Yes sir. Sorry for confusing you. We have a File Reader as our Source and Database writer for our destination. I have no idea how to get the values of the field from the message and insert it into a table.

              Thanks!

              Comment


              • #8
                Re: Calling stored procedure

                1. Please refer to "Getting Started With Mirth", Contributed by VocalEZ

                http://www.mirthproject.org/index.ph...d=45&Itemid=81

                2. Also, refer to samples in "File Repository".

                3. As regards sample INSERT statement to SQL Server, here&#039;s one:

                Hope it helps!


                ========== SQL INSERT Statement in the Destination tab ===============

                INSERT IGNORE INTO Patient (Account, Prefix, FirstName, MiddleName, LastName, Suffix, Address1, Address2, City, State, ZipCode, Phone1, Phone1Type, Phone2, Phone2Type, Gender, BirthDate, DeathDate, SSN)
                VALUES (${patAccount}, ${patPrefix}, ${patFirstName}, ${patMiddleName}, ${patLastName}, ${patSuffix}, ${patAddress1}, ${patAddress2}, ${patCity}, ${patState}, ${patZipCode}, ${patPhone1}, ${patPhone1Type}, ${patPhone2}, ${patPhone2Type}, ${patGender}, ${patDOB}, ${patDeathDate}, ${patSSN});

                ================================================== =======

                Comment


                • #9
                  Re: Calling stored procedure

                  Is SQL Server 2005 already supported by Mirth regarding drivers?

                  Thanks!

                  Comment


                  • #10
                    Re: Calling stored procedure

                    Yes, the JTDS driver should support it. You can drop any JDBC driver into the custom folder and use it from Mirth.

                    -Chris
                    Chris Lang

                    Comment


                    • #11
                      Re: Calling stored procedure

                      I added another driver for SQL Server 2005 and it&#039;s working fine.

                      Thanks!

                      Comment


                      • #12
                        Re: Calling stored procedure in 1.3.1

                        org.mozilla.javascript.EcmaError: TypeError: Cannot find function getInt. (3416a60c-6dad-4779-91cd-d197d7d25a85#39)
                        at org.mozilla.javascript.ScriptRuntime.constructErro r(ScriptRuntime.java:3226)
                        at org.mozilla.javascript.ScriptRuntime.constructErro r(ScriptRuntime.java:3216)
                        at org.mozilla.javascript.ScriptRuntime.typeError(Scr iptRuntime.java:3232)
                        at org.mozilla.javascript.ScriptRuntime.typeError1(Sc riptRuntime.java:3244)
                        at org.mozilla.javascript.ScriptRuntime.notFunctionEr ror(ScriptRuntime.java:3304)

                        I&#039;m getting this error and I was wondering if this function like .next(), .getInt(), .close() are still working. The code below is a part of my transformer.

                        var dbConn = DatabaseConnectionFactory.createDatabaseConnection (globalMap.get(&#039;jdbcDriver&#039, globalMap.get(&#039;dbAddress&#039, globalMap.get(&#039;dbLogin&#039, globalMap.get(&#039;dbPassword&#039);


                        var spParams = tmpNTE1 + &#039;, &#039; + tmpNTE2 + &#039;, &#039; + tmpNTE3
                        ;

                        var expression = "EXEC uSP_SampleTest" + spParams;

                        logger.info(&#039;SQL: &#039; + expression);
                        var result = dbConn.executeUpdate(expression);

                        result.next();

                        var iReturnVal = result.getInt(1);

                        result.close();
                        dbConn.close();


                        Thank you very much!

                        Comment


                        • #13
                          Re: Calling stored procedure

                          The result var still works, however on executeInsert(). On executeUpdate, result is an integer indicating the number of rows updated:

                          Code:
                          var iReturnVal =  dbConn.executeUpdate(expression);
                          Chris Lang

                          Comment


                          • #14
                            Re: Calling stored procedure

                            So, result.close() and result.next() are not working anymore? Did I get that right?

                            Thanks!

                            Comment


                            • #15
                              Re: Calling stored procedure

                              result.close() and result.next() work when you have a result set. Only SELECT SQL statements return result sets. If you have an insert or update, the result variable is not a result set, it is an integer (that equals the number of rows affected by the update or insert).

                              -Chris
                              Chris Lang

                              Comment

                              Working...
                              X