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

"Update" instead of "insert" on MySQL Database

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

  • "Update" instead of "insert" on MySQL Database

    Hello everyone,

    I have the following situation:
    Mirth receives an ADT-message for an admission of a patient. If the patient doesn't exist in the DB, mirth should write the new patient and his data to the database (that works fine). But if the patient already exists in the DB (this should be checked for example by the PID), I would like that Mirth executes an Update of the patients information, leaving his PID untouched.

    My question is: How can I design the whole situation? Should I use Javascript and exactly where in mirth (There is possibility to use Javascript in the "destination" tab or on the "scripts" tab or as a filter rule). Is there a way to use Java instead of Javascript for such situations?

    Thanks for any help! I appreciate it.

    Svetlomir

  • #2
    Re:"Update" instead of "insert" on MySQL Database

    You have two options:

    * Use JavaScript to apply a Insert/update depending on the situation. This can be done in a transformer o in a destination DB. Of course, instead of JS you can use Java, but Why ?
    * Use a stored procedure with do all the db stuff. This is my recomendation.

    Post edited by: albertosaez, at: 02/17/2008 05:22

    Comment


    • #3
      Re:"Update" instead of "insert" on MySQL Database

      Hi albertosaez,

      The problem is - I don't have good javascript knowedge. Do you know how to adjust Mirth so, that I can use Java in order to access the DB and execute database manipulations?

      Should I understand under "stored procedure" many SQL statements? If yes - how can SQL support if-else statements? As far as I know, SQL doesn't support control flow.

      Thanks.
      Svetlomir

      Comment


      • #4
        Re:"Update" instead of "insert" on MySQL Database

        You can use Java in the transformer, using the import Javascript function (you can find examples of its use in the forum). But I don't recomend it. I think the JS approach is easier.

        By "Stored procedure" I mean the SQL subroutines mosth db engines allow [http://en.wikipedia.org/wiki/Stored_procedure] What db are you using ?

        Comment


        • #5
          Re:"Update" instead of "insert" on MySQL Database

          Hello,
          I use the MySQL database. I hope I can apply stored procedures to it.

          Svetlomir

          Comment


          • #6
            Re:"Update" instead of "insert" on MySQL Database

            MySql allow storage procedures for verion >=5

            But anyway, you can use JS code.

            Comment


            • #7
              Re:"Update" instead of "insert" on MySQL Database

              Hello albertosaez,

              I learned how to write stored procedures from the "MySQL-AB" manual. I face now the following problem. When I create the stored procedure in the "Destinations"-tab in Mirth and send a HL7-Message to my channel, I get the following error-mesage:

              ERROR-406: JDBC Connector error
              ERROR MESSAGE: Error writing to database:
              java.lang.IllegalArgumentException: Write statement should be an insert / update / delete sql statement
              at com.webreach.mirth.connectors.jdbc.JdbcMessageDisp atcher.doDispatch(JdbcMessageDispatcher.java:120)


              or


              ERROR-406: JDBC Connector error
              ERROR MESSAGE: Error writing to database:
              java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';

              create procedure insertUpdate(out return_val int)
              begin
              declare a, b int;
              ' at line 1 Query: INSERT IGNORE INTO patient2 (pid, firstname, lastname, status) values (?, ?, ?, 0);


              Schould I save my procedure directly in the DB (not via Mirth)? And if so - how can I call my procedures in Mirth ?

              My sql statement is:
              INSERT IGNORE INTO patient2 (pid, firstname, lastname, status) values (${pid}, ${firstname}, ${lastname}, 0);
              but I don't know how to invoke it in a stored procedure. Do you have any idea? Thanks for your help!

              Svetlomir

              Comment


              • #8
                Re:"Update" instead of "insert&quot



                Post edited by: Svetlomir Kasabov, at: 02/19/2008 15:26

                Comment


                • #9
                  Re:"Update" instead of "insert" on MySQL Database

                  Yes, you have to create a stored procedure in mysql, not in Mirth.


                  To call to a stored procedure in mysql you have to execute "CALL nsertUpdate(pid,firstname,sencondName)" (being patientUpdate the name of the procedure, an pid, firstanem... the data

                  Comment


                  • #10
                    Re:"Update" instead of "insert" on MySQL Database

                    And how can I call the stored procedure in mirth when it's stored in the db? The call procedure doesnt work in mirth...
                    Thanks
                    Svetlomir

                    Comment


                    • #11
                      Re:"Update" instead of "insert" on MySQL Database

                      I think there are a lot of similar experiences in the forum, you could search "procedure" to find a lot of examples

                      Comment


                      • #12
                        Re:"Update" instead of "insert" on MySQL Database

                        Hello,

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

                        My guestion is: is there 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


                        • #13
                          Re:"Update" instead of "insert" on MySQL Database

                          Sorry, I didn't see your message.

                          Thanks a lot for your advice, it was very helpful.

                          Svetlomir

                          Comment

                          Working...
                          X