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

Using stored procedures in 1.6.1

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

  • Using stored procedures in 1.6.1

    I'm using the JavaScript template to call a stored procedure in 1.6.1. I believe the procedure is being called and is returning results, but I am having a brain-fart as to how to actually access those results.

    For example one of the columns coming back is GuarantorName so how would I make GuarantorName accessible in my destination map???

    Code:
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:«»sqlserver://dbsales.asp.tripractix.net:1433/CPSLOV','sa','[email protected]');
    var result = dbConn.executeCachedQuery("EXEC cus_spGetPastDueNoticeForEXTENSION"«»);
    
    // YOUR CODE GOES HERE 
    
    dbConn.close();
    
    logger.info(result.to_s);
    
    // You may access this result with $('column_name')
    return result;
    Jon Bartels

    Zen is hiring!!!!
    http://consultzen.com/careers/
    Talented healthcare IT professionals wanted. Engineers to sales to management.
    Good benefits, great working environment, genuinely interesting work.

  • #2
    Re:Using stored procedures in 1.6.1

    It depends on the database, and I'm not sure with SQLServer, but with others, the procedure results are returned as a normal ResultSet so you can use the usual result.next() and result.getString(0...n) instructions

    Comment


    • #3
      Re:Using stored procedures in 1.6.1

      albertosaez wrote:
      It depends on the database, and I'm not sure with SQLServer, but with others, the procedure results are returned as a normal ResultSet so you can use the usual result.next() and result.getString(0...n) instructions
      I think I'm one step ahead of that. Once I have a rowset how do I put it into a map so that I can use it in a message template???
      Jon Bartels

      Zen is hiring!!!!
      http://consultzen.com/careers/
      Talented healthcare IT professionals wanted. Engineers to sales to management.
      Good benefits, great working environment, genuinely interesting work.

      Comment


      • #4
        Re:Using stored procedures in 1.6.1

        I think you can't put the full rowset into a variable, but you can map the value of each column to a variable.

        And this can be achieved by the usual code:

        Code:
         channelMap.put("variableName",result.getString(1));

        But I'm sure you allready know this. What is exactly your problem ?

        Comment


        • #5
          Re:Using stored procedures in 1.6.1

          albertosaez wrote:
          But I'm sure you allready know this. What is exactly your problem ?[/quote]

          That WAS the problem!! I forgot about channelMap.put!

          Thank you alberto. You refreshed my memory! I owe you a beer!
          Jon Bartels

          Zen is hiring!!!!
          http://consultzen.com/careers/
          Talented healthcare IT professionals wanted. Engineers to sales to management.
          Good benefits, great working environment, genuinely interesting work.

          Comment


          • #6
            Re:Using stored procedures in 1.6.1

            Hi Alberto!

            I'm just trying to use SQL Server procedure in my mirth channel. In which place should I use "channelMap.put("variableName",result.getString(1) );", creating a transformer a filter or what ?

            And how about the source declaration, can I declare the execution of the stored procedure using JS as usual?

            Is it possible you post an example?

            Cheers!

            Alexander

            Comment


            • #7
              Re:Using stored procedures in 1.6.1

              Hi Jon!

              Please, give me a clue on how to solve that.

              I guess my problem is the same that was for you.

              I'm just trying to use SQL Server procedure in my mirth channel. In which place should I use "channelMap.put("variableName",result.getString(1) );", creating a transformer a filter or what ?

              And how about the source declaration, can I declare the execution of the stored procedure using Java Script as usual and get the result in the destination tab in the ${message.encodedData} ?

              Is it possible you post an example?

              Cheers!

              Alexander

              Comment

              Working...
              X