Announcement

Collapse
No announcement yet.

Passing results from a query to a javascript Array in a Transformer Step

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

  • Passing results from a query to a javascript Array in a Transformer Step

    I would like to query a sql server database and then take an int field called userid and place it into array. Once I get the array I want to pull out a random entry out unfortunately it errors out. Any ideas on how to do this in a transformer step?

    var dbConn;
    var result;
    var entryList = [];
    var mapping = '5'

    try {
    dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://hostname:1433/DBName','UserName','password');
    result = dbConn.executeCachedQuery('select userid from TableName where usertypeid = 5');
    while (result.next()) {
    entryList.push(result(1));
    }

    mapping = entryList[Math.floor(Math.random()*entryList.length)];

    }
    catch (e) {
    logger.error(e);
    mapping = '5';
    }

    finally {
    if (dbConn) {
    dbConn.close();
    }
    }

    channelMap.put('AssignedTo', validate(mapping, '', new Array()));

  • #2
    try changing
    Code:
    entryList.push(result(1));
    to
    Code:
    entryList.push(result.getInt(1));
    If that doesn't work, please post the error that you are getting.

    Comment


    • #3
      Undefined

      Now I just get undefined. Is there any documentation on how cachedRowSet works? I guess my main question is can i use this methodology in the transformer step?
      Last edited by tkurian; 03-15-2018, 05:19 AM.

      Comment


      • #4
        Since you are using a JS reader then at the end of your operation just use a return statement and it make your data available in the transformer for you to work on it.

        like
        Code:
         return list;
        Last edited by siddharth; 03-15-2018, 06:06 AM. Reason: foo
        HL7v2.7 Certified Control Specialist!

        Comment


        • #5
          Maybe try getString("userid") instead of getInt(1)? You could also add some logger.info() lines in there to check what some of the values are as it is running and see what isn't getting picked up correctly.

          What kind of database are you querying? It's possible to have the query pick the user at random and return 1 row instead of querying all of them and picking the random one in mirth. See https://www.petefreitag.com/item/466.cfm

          Comment

          Working...
          X