Announcement

Collapse
No announcement yet.

Retrieve data from database

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

  • Retrieve data from database

    Hello.... I am trying to retrieve data from a DB using a Java script in the transformer. Everything seems to working, no errors, but when it runs instead of returning the value from the database, all I received is Undefined. Here is the script I am using. Can someone let me know if they see an issue.



    Script

    var lastname = msg['PID']['PID.5]['PID.5.1'].toString()
    var firstname = msg['PID']['PID.5]['PID.5.2'].toString()
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
    'net.sourceforge.jtds.jdbc.Driver',
    'jdbc:jtds:sqlserver://servernameort/db',
    'username',
    'password'
    );

    var sql = "SELECT MiddleName from person where lastname = 'lastname;' and firstname = 'firstname;'";

    var results = dbConn.executeCachedQuery(sql);

    while(results.next()){

    var MiddleName = results.getString('MiddleName');

    }

    dbConn.close();

    msg['PID']['PID.5]['PID.5.3'] =MiddleName;




    Result / Output
    MSH|^~\&|TEST|TEST|TEST|TEST|20160912113304||ORU^R 01||P|2.3|
    PID|1|1|||Test^Sally^undefined|||||||

  • #2
    Your MiddleName variable is local to the scope of your while loop. Declare it beforehand and get rid of the "var " when you set it inside the loop.
    Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

    Nicholas Rupley
    Work: 949-237-6069
    Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


    - How do I foo?
    - You just bar.

    Comment


    • #3
      Narupley thanks for the info, but I am not quite following you. Can you give an example

      Comment


      • #4
        You need to declare the MiddleName variable outside the scope of the while loop, or do something with the value before exiting the loop. Once you exit the while, the variable 'goes away'.

        So you could do something like:

        Script

        var lastname = msg['PID']['PID.5]['PID.5.1'].toString()
        var firstname = msg['PID']['PID.5]['PID.5.2'].toString()
        var middlename = "";
        var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
        'net.sourceforge.jtds.jdbc.Driver',
        'jdbc:jtds:sqlserver://servernameort/db',
        'username',
        'password'
        );

        var sql = "SELECT MiddleName from person where lastname = 'lastname;' and firstname = 'firstname;'";

        var results = dbConn.executeCachedQuery(sql);

        while(results.next()){

        middlename = results.getString('MiddleName');

        }

        dbConn.close();

        msg['PID']['PID.5]['PID.5.3'] =MiddleName;



        alternately, this would also work:

        Script

        var lastname = msg['PID']['PID.5]['PID.5.1'].toString()
        var firstname = msg['PID']['PID.5]['PID.5.2'].toString()
        var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
        'net.sourceforge.jtds.jdbc.Driver',
        'jdbc:jtds:sqlserver://servernameort/db',
        'username',
        'password'
        );

        var sql = "SELECT MiddleName from person where lastname = 'lastname;' and firstname = 'firstname;'";

        var results = dbConn.executeCachedQuery(sql);

        while(results.next()){

        var MiddleName = results.getString('MiddleName');
        msg['PID']['PID.5]['PID.5.3'] =MiddleName;
        }

        dbConn.close();

        Comment


        • #5
          Made the changes above, but know I get nothing in PID 5.3

          var lastname = msg['PID']['PID.5]['PID.5.1'].toString()
          var firstname = msg['PID']['PID.5]['PID.5.2'].toString()
          var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
          'net.sourceforge.jtds.jdbc.Driver',
          'jdbc:jtds:sqlserver://servernameort/db',
          'username',
          'password'
          );

          var sql = "SELECT MiddleName from person where lastname = 'lastname;' and firstname = 'firstname;'";

          var results = dbConn.executeCachedQuery(sql);
          var middlename = '';

          while(results.next()){

          MiddleName = results.getString('MiddleName');

          }

          dbConn.close();

          msg['PID']['PID.5]['PID.5.3'] =MiddleName;

          Comment


          • #6
            Now your issue is that you've changed the variable name. Note that in JavaScript, as in many programming languages, variable identifiers are case sensitive.
            Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

            Nicholas Rupley
            Work: 949-237-6069
            Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


            - How do I foo?
            - You just bar.

            Comment


            • #7
              My last post was a mistype, I have the variable names correct. Regardless I still get nothing returned and no errors. Here is current script being used.

              var lastname = msg['PID']['PID.5']['PID.5.1'].toString()
              var firstname = msg['PID']['PID.5']['PID.5.2'].toString()
              var MN = '';
              var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
              'net.sourceforge.jtds.jdbc.Driver',
              'jdbc:jtds:sqlserver://servernameort/db',
              'username',
              'password'
              );

              var sql = "SELECT MiddleName from person where LastName ='lastname;' and FirstName ='firstname;'";

              var results = dbConn.executeCachedQuery(sql);

              while(results.next())
              {

              MN = results.getString(MiddleName);
              }

              dbConn.close();

              msg['PID']['PID.5']['PID.5.3'] = MN;

              Comment


              • #8
                This time it's different still. You're now using a variable MiddleName, but that's not declared anywhere. You probably meant to keep that as a literal string so it matches the column you're selecting in your query.
                Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

                Nicholas Rupley
                Work: 949-237-6069
                Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


                - How do I foo?
                - You just bar.

                Comment


                • #9
                  Originally posted by narupley View Post
                  This time it's different still. You're now using a variable MiddleName, but that's not declared anywhere. You probably meant to keep that as a literal string so it matches the column you're selecting in your query.
                  Ok, now I am confused. I changed the variable name to MN so i would not get them mixed up. But when you say I am using a variable of MiddleName but it is not declared, I am not sure what you are referring to. Sorry for so many questions, just want to understand this for now and future builds.

                  Comment


                  • #10
                    Before you were using

                    Code:
                    results.getString('MiddleName');
                    But then after you changed it to

                    Code:
                    results.getString(MiddleName);
                    why?
                    Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

                    Nicholas Rupley
                    Work: 949-237-6069
                    Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


                    - How do I foo?
                    - You just bar.

                    Comment


                    • #11
                      ok, that makes sense. I switched it back to 'MiddleName', as I do not want it as a variable, but I still go no value in PID5.3. What else could the issue be.

                      Comment


                      • #12
                        Another issue is that you have literally "firstname;" and "lastname;" in your query with the semicolons... that can't possibly be correct right?
                        Step 1: JAVA CACHE...DID YOU CLEAR ...wait, ding dong the witch is dead?

                        Nicholas Rupley
                        Work: 949-237-6069
                        Always include what Mirth Connect version you're working with. Also include (if applicable) the code you're using and full stacktraces for errors (use CODE tags). Posting your entire channel is helpful as well; make sure to scrub any PHI/passwords first.


                        - How do I foo?
                        - You just bar.

                        Comment


                        • #13
                          Ok, I removed those as well. Now I do get something returned, but it is NULL. This is still not correct as there is a value in that column for the data in question.


                          MSH|^~\&|TEST|TEST|TEST|TEST|20160912153715||ORU^R 01||P|2.3|
                          PID|1|1|||Test^Sally^null|||||||

                          Comment


                          • #14
                            After testing I figured out that the Null value is coming from the last patient in the DB. I can not figure out why it is grabbing that patients data instead of the data for the patient on the source message. I did some searching but I believe the problem is in this part of the code.


                            while(results.next())
                            {
                            MN= results.getString(1);
                            }

                            Is the .next causing it to pull the incorrect patient data? If so can someone help me change the code so that it will pull the patient on the inbound source message.

                            Comment


                            • #15
                              Finally Solved the issue. Here is my final code. Thanks for everyones help.


                              var dbConn = DatabaseConnectionFactory.createDatabaseConnection (
                              'net.sourceforge.jtds.jdbc.Driver',
                              'jdbc:jtds:sqlserver://Server/Database',
                              'username',
                              'password'
                              );

                              var sql = "SELECT MiddleName from person where LastName ='"+$('lastname')+"' and FirstName ='"+$('firstname')+"'";
                              var MN = '';

                              var results = dbConn.executeCachedQuery(sql);

                              while(results.next())
                              {

                              MN = results.getString(1);

                              }
                              dbConn.close();


                              msg['PID']['PID.5']['PID.5.3'] = MN;


                              channelMap.put ('sqlquery',sql);
                              channelMap.put ('results', results);
                              channelMap.put ('MN', MN);

                              Comment

                              Working...
                              X