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

How To use Output Parameter from stored procedure

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

  • How To use Output Parameter from stored procedure

    How do you use the paramList for an output parameter from the "Perform Parameterized Database Update". We need to return the value of @PatientID back to the Mirth Engine for processing in javascript.

    The "Perform Parameterized Database Update" generates:

    Code:
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('driver', 'address', 'username', 'password');
    var result = dbConn.executeUpdate('expression', paramList);
    dbConn.close();

    Our stored Proc is defined as:

    Code:
    ALTER procEDURE [dbo].[ensure_Patient] 
    	@chartNumber varchar(50) = null, 
    	@SSN varchar(50) = null,
    	@FirstName varchar(50),
    	@LastName varchar(50),
    	@MiddleName varchar(50),
    	@DOB date,
    	@UserID int = 1,
    	@PatientID bigint output
    AS

  • #2
    Re:How To use Output Parameter from stored procedure

    You don't use the parameters there for output params; they are for input params. You can just execute the query and take the result set that is returned and iterate through it. Here's some sample code.

    Code:
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('com.mysql.jdbc.Driver','jdbc:mysql://10.10.3.180:3306/mirth_data','root','pass');
    var result = dbConn.executeCachedQuery('CALL get_updated_patients()');
    
    while (result.next()) { 
               logger.error("first result column=" + result.getString(1));
               logger.error("second result column=" + result.getString(2));
    }
    
    dbConn.close();
    
    // You may access this result with $('column_name')
    return result;
    Post edited by: brendanh, at: 09/26/2008 15:06
    Brendan Haverlock | Mirth Software Engineer | Mirth Corporation

    Comment


    • #3
      Re:How To use Output Parameter from stored procedure

      Check the following link if your going to actually want to bind the parameters as output parameters. Our DB wrapper class does not handle this directly, so you may need to use the java code in here to make your call. Or, you can make the call and just get the resultSet back as I did above.

      http://www.informit.com/articles/art...26251&seqNum=6
      Brendan Haverlock | Mirth Software Engineer | Mirth Corporation

      Comment


      • #4
        Brendan,
        If you do choose to go the output variable through Java route (which I will have to) what should the return value be.

        For example, right now you are using the result from executing the cached query as follows:

        var result = dbConn.executeCachedQuery('CALL get_updated_patients()');
        ...
        ...
        return result;

        However, if i run a Java stored procedure the return type will be different than that in the Mirth database wrapper function. Please let me know if this makes sense or if I need to explain it a little better.
        Thanks
        Jason

        Comment


        • #5
          You can return a ResultSet (Java), which is what Mirth is doing. You can also return a list. I think a list of maps should allow you to simulate a result set. Each map in the list will be a message, and the map will contain the name/value xml element pairs.
          Jacob Brauer
          Director, Software Development
          NextGen Healthcare

          sigpic

          Comment


          • #6
            Not to be a pain, but is there any chance of a very high level example?

            Thanks for your help,
            Jason

            Comment


            • #7
              Always returning a 0 return value

              I am having similiar issues but I am using javascript. I am returning from the sp a 0 every time even though I know that isn't the case. If you execute the sp with parameters in SQL Server, I would get other return values in particular a -6 which tells me I need to execute a loop until I receive a 0 incrementating a time by 5 minutes. Well, I never know. Here is my code:

              dbConn = DatabaseConnectionFactory.createDatabaseConnection (...);
              tryApptQuery = "CUS_Excel_MakeAppointment "+ FacilityCode+","+ "'"+CINumber+"'"+","+ "'"+ApptStartDate+"'"+","+ Duration+","+ ResourceID+","+ "'"+lastModifiedDate+"'"+","+ "'"+createdBy+"'"+","+ "'"+lastModifiedDate+"'"+","+ "'"+LastModifiedBy+"'"+","+ "'','"+CPTCode+"'"+","+ RefPhyID+","+ "'"+WorksComp+"'"+","+"'"+DOI+"'"+","+"'"+DOA+"'"+ ","+"'"+AccidentState+"'"+","+ "'"+AccidentAuthorization+"'"+","+ "'"+ContactWC+"'"+","+ "'"+ AccessionNumber+"'"+","+ "''"+","+ "''";

              var dbReturncode = dbConn.executeUpdate(tryApptQuery) ;
              dbConn.close();
              try
              {
              dbReturncode.next();
              var returnCode = dbReturncode.getString(1);
              channelMap.put("returnCode" + segCount, returnCode);
              }
              catch(err)
              {
              //var smtpConn = (I was sending an email to myself so I would know what failed with the sp and parameters
              return dbReturncode;
              }

              Here is an example of what is returned in sql server:

              BEGIN TRANS
              PtId:1200389163
              Code =74160
              >>Found ref dr 1981

              (1 row(s) affected)

              (1 row(s) affected)
              >> ref Dr Id updated 1981
              >>Found facility 22099
              >>Found resource 22221

              (0 row(s) affected)

              (0 row(s) affected)

              (0 row(s) affected)

              (0 row(s) affected)

              (1 row(s) affected)
              ApptId:989086

              (1 row(s) affected)

              (1 row(s) affected)

              (0 row(s) affected)

              (1 row(s) affected)

              (0 row(s) affected)

              (1 row(s) affected)

              (0 row(s) affected)
              Appt Slot Avl. cnt: 0
              Appt Slot Needed: 3
              Overbooking already exists
              -6

              How can I receive the actual return value in javascript? Does anyone have an example of how to do that? In this example, I need to know the return value is a -6.

              THanks!!!!

              Comment


              • #8
                remove the output parameter from your procedure since using javascript you're not able to pass a parameter to it. The last step in your procedure [after commits] will be a select statement. For example, select @@identity or Last_insert_id() or what ever you needed to return for that output parameter your just removed from your procedure.

                Then you should have some data being returned to you.

                Comment


                • #9
                  hl7-java-guru

                  using 1.8.1, things have become very easy to manage with stored procedures. In 1.8.1, they have exposed the Connection object. You can pass this to your java code and manage all the stored procedures.

                  var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://localhost/hl7database','hl7user','password');

                  // Instantiate your java class that takes the connection object
                  var myclass = new Packages.com.demo.sqlsupport.DemoSQLSupport(dbConn .getConnection());

                  // my java class has a method to exec the SP and return the output param
                  var myOutputParam = 0;

                  // my sp takes 2 params. first one is int and the 2nd one is an output param
                  // I have channel var request_id and when I pass this to a sp,
                  myOutputParam = myclass.getNextMessageId($('request_id'));


                  This has made life much easier.. no need to clutter your js code and isolate all this to your jars.

                  Let me know, for more details and I can put the java code and an sp also.

                  Comment


                  • #10
                    Stored procedures

                    Could you please post the java code and the SP here? I am new to Mirth and am just trying to execute a SP inside Mirth.

                    Thanks.

                    Comment


                    • #11
                      Originally posted by vkolemiss View Post
                      Could you please post the java code and the SP here? I am new to Mirth and am just trying to execute a SP inside Mirth.

                      Thanks.
                      Hi all,

                      I am trying to execute a stored procedure in MS SQL that will return a value to mirth for further processing.

                      I can run simple exec statement to get the data into tables, however when it comes to returning values from SQL (i.e OUTPUT Parameters), i am struggling to understand how to receive this value and store it in a variable. Any help would be much appreciated and examples would be grateful.

                      Many Thanks

                      ################################################## #############

                      Stored Proc

                      ALTER PROCEDURE [HL7Import].[InsertMessage](
                      -- parameters
                      @DataSourceId INT,
                      @Type NVARCHAR(255),
                      @MessageId BIGINT OUTPUT
                      )
                      AS
                      BEGIN


                      ----------------------

                      I basically want to know how to call this stored poc from mirth and store the returned value in a variable.

                      Comment

                      Working...
                      X