Announcement

Collapse
No announcement yet.

Perform Database Query

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

  • Perform Database Query

    I have created a transformer that needs to Perform a Database Query. I have tried different things but I keep getting invalid syntax when I try and deploy my channel. Does anyone have a working example. What I am trying to do is take a value that I am recieving and go to the database and replace it with a different value.

    Thanks...

  • #2
    Re: Perform Database Query

    Can you paste your query/transformer code.
    Thanks,
    -Chris
    Chris Lang

    Comment


    • #3
      Re: Perform Database Query

      this is the code from the mapping box:

      var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('sqlserver', 'HL7_Test', 'webvmc', 'webvmc'
      var result = dbConn.executeCachedQuery('Select StateID FROM State WHERE Abbreviation = SC'
      dbConn.close();
      return result;

      What I will eventually want to do is replace SC with msg['PID']['PID.11']['XAD.4'] if it will do that.

      Comment


      • #4
        Re: Perform Database Query

        So two things:

        1. You need to make sure this is in a Javascript step rather than a mapping step. While you can execute some code in the mapping steps, they will attempt to assign the result of the execution to a var (and the use of "return" is not allowed).

        2. In your database connection, you are just passing it "sqlserver". This needs to be the full JDBC connection string for the database:
        Code:
        You SQL Server connection string could be something like this:
        jdbc:jtds:sqlserver://localhost:1433;databaseName=mydatabase;domain=mydomain
        Chris Lang

        Comment


        • #5
          Re: Perform Database Query

          I have my database connection working. It is getting to the database. Now I need the value from my select statement. My select statement only returns 1 value and it is an int. 'select StateId from state where abbrv = 'SC''. My return value is 36. I am not able to get anything from the result of the executeCachedQuery. It puts it in some sort of cached resultset and I don't know how to get it out. IF anyone has an example that would be great. Or if there is a better way please let me know.

          Thanks.... bobbie

          Comment


          • #6
            Re: Perform Database Query

            use:

            Code:
            result.getInt(0)
            The 0 is the column number.
            Chris Lang

            Comment


            • #7
              Re: Perform Database Query

              Now I get an error "invalid column index"

              here is what I have in the transformer

              var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://localhost:1433/HL7_Test', 'user', 'pass'
              var result = dbConn.executeCachedQuery('Select StateId from State where abbreviation = ' + "'" + msg['PID']['PID.11']['XAD.4'].toString() + "'");
              var returnval = result.getInt(0);
              localMap.put('StateId', returnval);
              dbConn.close();

              Comment


              • #8
                Re: Perform Database Query

                Sorry it's
                Code:
                result.getInt(1)
                I forgot that they are 1-index based.
                Chris Lang

                Comment

                Working...
                X