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

Retrieving resultset from Stored Procedure

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

  • Retrieving resultset from Stored Procedure

    Hi everyone!

    Does anyone know how to retrieve resultset from a stored procedure?

    When we use SELECT FIELD1, FIELD2, FIELD3 FROM Table, the fields will come up automatically in the message template. In the case of Stored Procedure how do I put resultset values into a map so that I can use it in a message template.


    Thanks

  • #2
    Re:Retrieving resultset from Stored Procedure

    If you change your DB reader to use JS code you can split your query into 2 parts:
    the first one to call th SP to prepare the data and then retrieve the data with a simple select.

    AFAIK, you cannot with actual versions of Mirth retrieve data directly from the SP, but you can call a SP with the appropiate JS.

    Hope that helps.

    Comment


    • #3
      Re:Retrieving resultset from Stored Procedure

      It depends on the db you're working with. For example: with sqlserver and informix stored procedures returns exactly the same values as if you were doing a select, but others as oracle, it doesn't.

      It's a jdbc-issue, not a Mirth one.

      Comment


      • #4
        Re:Retrieving resultset from Stored Procedure

        Hi,

        I am using MySql database. May be because of that I am not able to retrieve the values.
        Actually in Stored Procedure I am just selecting values from multiple tables.

        Or is there any way to use multiple select statements?
        The problem is, only the values from the first Select statement are coming up in the message template.


        Thanks


        Post edited by: soniaa, at: 07/08/2008 20:58

        Comment


        • #5
          Re:Retrieving resultset from Stored Procedure

          Can you use a join query to merge all the results you're interested in into a single resultset ?

          Comment


          • #6
            I was looking for the answer to this, so I thought I'd post my successful method:

            1) set up your query in javascript something like:

            var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://yourservername:1433;databasename=yourdbname;','you rlogin','youypassword');
            var sql = "exec [yourdb].[Usp_yourproc]";
            var result = dbConn.executeCachedQuery(sql);

            dbConn.close();
            // You may access this result below with $('column_name')
            return result;

            2) ensure the source connector inbound is xml

            3) set up a destination as a file writer with raw data for output to make sure the data is getting through

            4) push some messages through

            5) from the dashboard right click on the channel and view messages

            6) Find a message from the source connector and click on it and copy the xml message from the raw message tab

            7) Go back to editing te source connector in the channel

            8) edit transformer

            9) go to inbound message templates

            10) replace the contents with your copied message

            11) You should now have a message template tree to map te values from.

            Comment

            Working...
            X