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

calling mssql stored procedure

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

  • calling mssql stored procedure

    Does anyone has a sample channel that calls a simple mssql stored procedure successfully to update a table?
    I tried to create my own channel to do that and the error hangs/killed my mirth...
    In source, I use file reader. In Destinations, I set to database writer to write to sqlserver2005. Then I imported a 'spHL7AddOrUpdatePatient' transformer(downloaded from somewhere here) on destination.

    This is the error I am getting, and I do have @PATIENT_ID parameter in my sp file:

    FAILURE: Error evaluating transformer
    MirthJavascriptTransformerException

    CHANNEL:
    CALLING_SP
    CONNECTOR:
    PatientTable
    SCRIPT SOURCE:

    LINE NUMBER:
    126
    DETAILS:
    Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or Function 'spHL7AddOrUpdatePATIENT' expects parameter '@PATIENT_ID', which was not supplied.

    Any feedback will be great.
    thank you~

    Post edited by: julia1231, at: 06/05/2008 10:28

    Post edited by: julia1231, at: 06/05/2008 10:50

  • #2
    Re:calling mssql stored procedure

    In the destination, I set database writer and using javascript to write. In transformer, I also call the stored procedure. the error is still showing the same:

    FAILURE: Error evaluating transformer
    MirthJavascriptTransformerException

    CHANNEL:
    CALLING_SP2
    CONNECTOR:
    PatientTable
    SCRIPT SOURCE:

    LINE NUMBER:
    50
    DETAILS:
    Wrapped com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or Function 'spHL7AddOrUpdatePATIENT' expects parameter '@PATIENT_ID', which was not supplied.

    Has anyone get to call a mssql stored procedure to work successfully in mirth 1.7?

    any help will be good. thank you. calling_sp2.xml (19448 bytes)

    Post edited by: julia1231, at: 06/06/2008 12:22

    Comment


    • #3
      Re:calling mssql stored procedure

      the executeUpdate() method actually executes what is in the parentheses...so you need to include the whole thing there. For example:

      params = new Packages.java.util.ArrayList();
      params.add($('param1'));
      params.add($('param2'));

      sql = "EXEC [prcStoredProc1] @p1=?,@p2=?";
      var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds: sqlserver://srsqldev:1433/MyDB','MyUser','MyPass');

      var result = dbConn.executeUpdate(sql, params);
      dbConn.close();


      Btw, an alternative way to write the query is
      sql = "EXEC [prcStoredProc1] ?,?";

      You only have to include the actual parameter names if you plan on putting them out of order or not including some of them.

      Comment


      • #4
        I got this to work but it will not update the 1 field AlertNotes. If I run the SP outside of Mirth and pass the PatientId to the SP it updates AlertNotes:

        var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://MLT001:1105/Demo08EDI','sa','xxxx');
        var result = 0;
        var sql = "EXEC cusMirth_UpdateAlertNotes '" + $('PID') + "', 'HOLY CRAP!'";
        result = dbConn.executeUpdate(sql);
        dbConn.close();

        Any Ideas anyone as to why it wouldn't update the AlertNotes when calling the SP from Mirth.
        Last edited by MikeCaldwell; 05-11-2009, 10:14 PM.
        Mike Caldwell
        Alliance HealthCare - GE VAR
        Rocklin, CA
        Centricity PM/EMR Support - Developer - Network Engineer

        Comment


        • #5
          SQL 2005 Updates and Calling SP's on Dest Channel

          These were tested and workiing with Mirth / Centricity Demo08 CPS DB on SQL2005:

          Inbound mapping:
          PID = msg['PID']['PID.2']['PID.2.1'].toString()
          msgDateTime = msg['MSH']['MSH.7']['MSH.7.1'].toString()

          Example HL7:

          MSH|^~\&|MIK-AIG^MIK-AIG^GUID||LinkLogic^LinkLogic^GUID||20090509045836 ||ADT^A08|8868|P|2.3||||NE
          EVN|A08|20090508215836||01
          PID|1|381|381||Smith^Mason||19760123000000|M|||123 4 test^^Dallas^IL^60419||(214)874-5544|||S|||555-55-6666|||||||||||N
          PV1|1|O|^^^3^^^^^Facility||||7^Last^First^^MD^^^^& 4444&UPIN|||||||||||||Aetna

          STORE PROC USED:

          SET ANSI_NULLS ON
          GO
          SET QUOTED_IDENTIFIER ON
          GO
          -- =============================================
          -- Author: Mike Caldwell
          -- Create date: 04/09/2009
          -- Description: Mirth Will Call this SP to update the AlertNotes
          -- =============================================
          ALTER PROCEDURE cusMirth_UpdateAlertNotes @PID varchar(20), @NewNotes as varchar(50)
          AS
          BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          SET NOCOUNT ON;

          DECLARE @PPID int

          Select @PPID=PatientProfileId from PatientProfile Where [email protected]

          Update PatientProfile
          SET AlertNotes = @NewNotes,
          LastModified = getdate(),
          LastModifiedBy = 'MirthUpdate'
          Where PatientID = @PID

          END
          GO


          DEST CHANNEL 1 Use JavaScript=YES:
          //
          // Updating a SQL2005 database by called a custom SP
          // and passing it parameters fromt he HL7 file
          // Connect
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://MLT001:1105/Demo08Copy','millbrook','xxxxx');

          // Grab PatientProfileId using PID - they can be different
          var result1 = dbConn.executeCachedQuery("Select PatientProfileId From PatientProfile Where PatientId = '" + $('PID')+"'");
          result1.first();

          // Grab PV Rows using PPID
          var PVSelect = "Select PatientVisitId, Entered, PatientProfileId, FinancialClassMId, DoctorId, FacilityId From PatientVisit Where PatientProfileId = " + result1.getString(1);
          var result2 = dbConn.executeCachedQuery(PVSelect);
          // Post PVselect to Dashboard Log
          channelMap.put('PVSelect',PVSelect);
          // Move to first record
          result2.first();

          // Create PVInfo Values out
          var PVInfo = result2.getString(1)+"|"+result2.getString(2)+"|"+ result2.getString(3)+"|"+result2.getString(4);
          // Post variable to Dashboard Log
          channelMap.put('PVInfo',PVInfo);
          // Update DB Calling SP and Vriable pulled from the HL7 File
          var result3 = dbConn.executeUpdate("EXEC cusMirth_UpdateAlertNotes '" + $('PID') + "', '" + PVInfo.toString() + "'");
          dbConn.close();

          DEST CHANNEL 2 Use JavaScript=YES:
          //
          // Updating a SQL2005 database by called a custom SP
          // and passing it parameters fromt he HL7 file
          // Connect
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://MLT001:1105/Demo08Copy','millbrook','xxxxx');
          // Get info about Patient using SP to read it in to variable result1
          var result = dbConn.executeCachedQuery("Exec dbo.pGetPatientHistory " + $('PID'));
          // Move to first Record
          result.first();
          // Load Lic with results
          var PHInfo = result.getString(1)+'|'+result.getString(2)+'|'+re sult.getString(3)+'|'+result.getString(4)+'|'+resu lt.getString(5);
          // Show LIC Variable
          channelMap.put('PHInfo',PHInfo);
          var result = dbConn.executeUpdate("EXEC cusMirth_UpdateAlertNotes '" + $('PID') + "', '" + PHInfo.toString() + "'");
          // Close Channel
          dbConn.close();
          Mike Caldwell
          Alliance HealthCare - GE VAR
          Rocklin, CA
          Centricity PM/EMR Support - Developer - Network Engineer

          Comment

          Working...
          X