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

Execute procedure before execute the source code

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

  • Execute procedure before execute the source code

    Hi everyone?

    Does anyone knows how to execute something before execute the code inside of the source tab?

    Scenario:

    I need execute a stored procedure SQL Server before run my SELECT statement in the Database Reader (source tab). The stored procedure is going to prepare the data to be retrieved on the SELECT.

    I put the code to execute the stored procedure in the tab script on Deploy Event, but the code in there just execute when the mule engine is started, or just once.

    I wanna run the stored procedure each time when the channel is executed.

    Do you know how to do it?

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

    That, could be an idea for a new feature?

    To insert an option to execute something before execute the code in the source tab?
    ==================================================

    Thanks,

    Alexander

    Post edited by: Alexander, at: 04/30/2008 18:36

  • #2
    Re:Execute procedure before execute the source code

    Hi,

    then maybe the most simply way to run this is to put the select statement inside stored procedure, isn't it?.

    On th other side, I think that you can reach that writing the code in "Preprocessor" label not in deploy.

    HTH,

    Ricard Bernat

    Comment


    • #3
      Re:Execute procedure before execute the source cod

      Hi Ricard!

      Thanks for your reply!

      The problem is, to run the stored procedure you must use JS implementation. Not a problem! And to put the SELECT statement inside of the JS implementation is also not a problem, but I don't know how to retrieve the fields, or, I don't know how to make them appear in the listbox on bottom of the tab source. They appear automatically when you are using SQL and not java script.

      If you know one way to retrieve the fields like normal SELECT statetement, using JS, please, let me know. I'm also running after that information.

      I tested to run the stored procedure on "Preprocessor", and it didn't work.

      Thanks a lot.

      Alexander

      Comment


      • #4
        Re:Execute procedure before execute the source cod

        Take a look to the db writer in js mode. I suppose that is possible get a resultset and then get the message.
        To push variables to the channelmap check the map functions in a js writer. You have 3 contexts: connector,channel and global

        Comment


        • #5
          Re:Execute procedure before execute the source cod

          Hi!

          Thanks for your reply!

          I tried to do that, and it's not working.
          Do you have an example?
          Can you post it?

          Thanks,

          Alexander

          Comment


          • #6
            Re:Execute procedure before execute the source cod

            I have a problem that seems specific to Oracle, because the table I'm using to test this has a RAW type where the message is placed, but, the idea is something like:

            Channel, format: XML

            Source JS Reader, code:
            -------------------------------------------------
            var driver = "oracle.jdbc.OracleDriver";
            var username = "username";
            var password = "password";
            var url = "jdbcracle:thin:@host:1521:SID";
            var dbConn = DatabaseConnectionFactory.createDatabaseConnection (driver,url,username,password);
            var strQuery = "select raw_data from message where id='00107400-4865-4e37-b4d2-f96586843722'";
            var result = dbConn.executeCachedQuery(strQuery);
            dbConn.close();
            return result;
            -------------------------------------------------

            The returned value is processed as the message. You can connect to the database, execute the stored procedure and then do the appropiate select.
            Note that, as this code is executed every 5 secs (or period you've defined), it processes a message every time it's executed, and you must ensure that the returning value contains only one message i.e. by limiting the select statement with a LIMIT or TOP modifier.

            It seems that is unnecesary passing values to the channel/connector map.

            See you.

            Comment


            • #7
              Re:Execute procedure before execute the source cod

              Hi quimicefa!

              Thanks for your reply!

              The only different thing in my JS code is because I'm running a stored procedure, as follow below:

              var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:ser ver://myipaddress:9999/database','user','password');
              var result = dbConn.executeCachedQuery("EXEC procedure_name");
              dbConn.close();
              return result;

              is pretty much the same.

              The problem is, how to retrieve the return value from the query inside of my stored procedure? Where and how can I put the code? Inside a transformer? How the transformer should be configured? Do they need be a JavaScript, MessageBuilder or Mapper type?

              This is my main problem.

              And also, do you have an example on how create dynamic OBX segments ?

              Thanks very much for your help.

              Alexander

              Comment


              • #8
                Re:Execute procedure before execute the source cod

                Are you sure that your stored procedure is retuning only one row and field ? I think that you should check it before retuning the message. IMHO the returning value must represent exactly one message.

                I think that the correct way to reach your goal is to put all the code inside a JS Reader: first call the SP to prepare your data and then exec a select statement to retrieve the message and return that message.
                If you SP doesn't return data, you must use the methods to execute UPDATEs, because they don't return any value. But you must use executeQuery/executeCachedQuery() to exec the SELECT statements.


                After that if you want to do some additional operations you can put them on the transformer ... but I think that's unnecesary.

                Can you post the exceptions/errors that you're getting ?

                Comment


                • #9
                  Re:Execute procedure before execute the source cod

                  Hi quimicefa,

                  Thanks for your reply!

                  I'm not getting any error or exception messages.

                  So, I'm pretty sure that in this moment my stored procedure is returning more than one field, but just one row.

                  Inside my SP I'm doing cursors, creating tables, inserts, updates, and, in the end, doing a SELECT statement. Which is a normal code for stored procedure.

                  I don't know if using Mirth is possible to get the result set from a stored procedure like this. I believe so! I just don't know how.

                  Have you done something similar before? Is it possible for you to post an example?

                  I need find one way to execute the SP every time when the channel is executed and always before the SELECT.

                  I tried put the SELECT statement inside of JS implementation, and it works normal. But I still need to execute my SP before the SELECT.

                  I tried the code below:

                  var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://ipaddress:9999/database','user','password');
                  var result = dbConn.executeCachedQuery("EXEC PROC_NAME");
                  var result = dbConn.executeCachedQuery("SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE_NAME");
                  dbConn.close();
                  return result;

                  and this as well

                  var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://ipaddress:9999/database','user','password');
                  var result = dbConn.executeUpdate("EXEC PROC_NAME");
                  var result = dbConn.executeCachedQuery("SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE_NAME");
                  dbConn.close();
                  return result;

                  or put all in the same instruction

                  var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://ipaddress:9999/database','user','password');
                  var result = dbConn.executeCachedQuery("EXEC PROC_NAME SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE_NAME");
                  dbConn.close();
                  return result;

                  or like this

                  var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://ipaddress:9999/database','user','password');
                  var result = dbConn.executeUpdate("EXEC PROC_NAME SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE_NAME");
                  dbConn.close();
                  return result;


                  they are not working.


                  if you run the SELECT like the code below is working normal, but really must to execute the SP before.

                  var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://ipaddress:9999/database','user','password');
                  var result = dbConn.executeUpdate("SELECT FIELD1, FIELD2, FIELD3, FIELD4 FROM TABLE_NAME");
                  dbConn.close();
                  return result;


                  Thanks again.

                  Alexander

                  Post edited by: Alexander, at: 05/06/2008 21:39

                  Comment


                  • #10
                    Re:Execute procedure before execute the source cod

                    Hi Alexander,

                    As far as I know, the main differences between executeCachedQuery, executeUpdate ... etc are that the first is implemented to run SQL statements that returns a resultset, and the second one to run SQL that returns an updated row count or nothing.

                    I've reviewed the Java API to see which method is the best, but I believe that is unclear if CreateDatabaseConnection, executeCachedQuery ... and so are methods implemented in Java, in Javascript or in Java but instantiated from Javascript ... so i can't help you very much.

                    I suggest to try split the code in the stored procedure: letting the code that creates tables, views ... and so inside the procedure, but moving out of the SP the SQL parts that returns resultsets. This way *in theory* you can call safely the SP from an executeUpdate as long as the SP doesn't return resultsets, and then execute a regular query using the executeCachedQuery or so.

                    BTW, have you tried to use "CALL SP()" instead of "EXEC SP()" ?

                    The question about DataBaseConnectionFactory and other classes are defined is the key for me. Is not the same case if these classes are defined in JS or in Java ... etc. In the worst case I suppose you can instantiate the Java classes for DatabaseConnection, and use the createCallableStatement to call the SP.

                    I'm not too experienced in using SP's from JS/Java, so I can't help you very much with this issue ...

                    See you!

                    Comment


                    • #11
                      Re:Execute procedure before execute the source cod

                      Hi quimicefa!

                      I have noticed about the differences between executeCachedQuery and executeUpdate.
                      And also, I can see when the SP is being executed using EXEC SP, through my SQL profiler.
                      Do you think that using CALL SP instead of EXEC SP is gonna make any difference?

                      I know how to use both methods, executeCachedQuery and executeUpdate, but the problem still the same. Looks like I can not run both at the same time, and when you try to run a combination, it doesn't work as well, as I wrote to you in my previous thread.

                      Basically I just need run the procedure first then the SELECT statement. I give up already about to run the SELECT from inside of the SP, but I still need run the SP each time when the channel is being executed and always before the SELECT statement.

                      If you know one way to do that, please, let me know.

                      Thanks for your attention.

                      Alexander

                      Comment

                      Working...
                      X