Announcement

Collapse
No announcement yet.

Calling stored procedure

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

  • #16
    Re: Calling stored procedure

    Ok Chris, thanks!

    Comment


    • #17
      Re: Calling stored procedure

      Hope that helps!
      Chris Lang

      Comment


      • #18
        Re: Calling stored procedure

        Just an update -

        Mirth now support calling stored procedures on the Source connector and Destination connectors. You must select "use Javascript". Here you can call the Stored Procedure in your JS code.
        Chris Lang

        Comment


        • #19
          Re: Calling stored procedure

          What abt return variables. Can they be supported using this technique.

          What is the long term stance on supporting stored procedure through database writer?

          Thanks
          Karan

          Comment


          • #20
            Re: Calling stored procedure

            Anything that can be used with regards to stored procedures in Java can now be used in Mirth with the Javascript DB functions. We fully support stored procedures in a database writer Javascript step.
            Chris Lang

            Comment


            • #21
              Re:Calling stored procedure

              Hi all,

              I read in a past issue that JS database connections can manage stored procedures execution but, it will be available in a near future native support (not from JS, simply calling SP) for this kind of data retrieval?.

              On the other hand, I think that this support is necessary in both channel modes (input/output), not only at output as I can remember in mentioned issue.

              Waiting your news,

              Ricard

              Comment


              • #22
                Re:Calling stored procedure

                Previously, there was a JS workaround to call a stored procedure in the transformer/filter. As of 1.5, JS was added to the database reader and writer, which supports stored procedures and the FULL JDBC spec. The JS code is precompiled and you are using PURE Java objects so it is just as native as the non-JS db calls. The reason you can't use stored procedures in a non-JS DB connection is due to a limitation with mule under the hood. It needs to know the column and table names in order to work it's magic. When you use JS it is executing the script just like it was native, but we're bypassing Mule's db engine and letting you hit JDBC directly.
                Chris Lang

                Comment


                • #23
                  Re: Calling stored procedure

                  Hi Chris!

                  I'm trying to use Mirth with procedures in the SQL Server.
                  I developed a channel that in the source is DB Writer with some transformers.
                  In the destination I'm trying to get the values defined in the transformers into the source section.

                  I tried both ways, pure SQL statement like EXEC PROCEDURE ... ... ... or with JS. No success in both cases.
                  I created a variable to store the SQL statement like EXEC PROCEDURE param1, param2, param3 and etc, and inside the function to execute the SQL string I added the variable name.

                  Is not executing. I don't know why.

                  Do you have any example to execute a procedure with parameters in the destination retrieving the values from the transformers in the source ?

                  Thanks a lot!

                  Comment


                  • #24
                    Re: Calling stored procedure

                    Hello,

                    I read this thread and as far as I understood, I can call the stored procedure only with javascript.

                    My guestion is: is there already a way to call the stored procedure in mirth without using javacript?

                    Can you please paste some instert / update / delete statements triggering a stored procedure?

                    Thank you very much!

                    Svetlomir

                    Comment


                    • #25
                      Re: Calling stored procedure

                      Just to add a comment from a new Mirth user, but experienced MS SQL Server programmer:

                      A simple way of getting round Mirth's limitation on requiring an INSERT statement would be to use a dummy VIEW in SQL Server (2000 and later), with an INSTEAD OF INSERT trigger that calls the stored procedure(s)

                      eg (in SQL Server)

                      CREATE VIEW MIRTH_INSERT_DUMMY
                      AS
                      SELECT CONVERT(VARCHAR(255),NULL) AS MessageControlID,...
                      WHERE 0=1

                      GO

                      CREATE TRIGGER ON MIRTH_INSERT_DUMMY INSTEAD OF INSERT
                      AS
                      BEGIN
                      DECLARE @MessageControlID VARCHAR(255),...

                      SELECT @MessageControlID=MessageControlID,...
                      FROM inserted

                      -- logic steps to choose which stored proc etc...

                      EXEC MyStoredProc @[email protected],...

                      END

                      --------

                      Then in Mirth, just use

                      INSERT IGNORE INTO MIRTH_INSERT_DUMMY(MessageControlID,...)
                      VALUES (${MessageControlID},...)

                      Comment


                      • #26
                        Re: Calling stored procedure

                        Hi Colizobble!

                        The problem is, when you put a SELECT statement you'll see the result set already mapped, I meant the fields on the query, for example: SELECT FIELD1, FIELD2, FIELD3 FROM TABLE.

                        But, how to get the fields or result set from a stored procedure that are executing a lots of instructions inside and returning a SELECT statement ?

                        Thanks,

                        Alexander

                        Comment


                        • #27
                          What abt return variables. Can they be supported using this technique.

                          Comment


                          • #28
                            Originally posted by 19hostingsurabaya View Post
                            What abt return variables. Can they be supported using this technique.
                            yes, they can







                            sony z5 compact schutzh├╝lle
                            Last edited by Shakurr; 09-15-2015, 06:13 PM.
                            Wieviel Sprachen du kannst, wieviel male bist du Mensch!

                            Comment

                            Working...
                            X