Announcement

Collapse
No announcement yet.

Javascript writer help

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

  • Javascript writer help

    I have MySQL select statement ready with JOINING on multiple DBs and tables within those DBs including WHERE clause. I would like to use this select statement in JavaScript writer as JS script. Do I have to create DB connection for each db even all dbs are on one server? How can I do this? Thanks.

    SELECT DISTINCT t1.firstname, t1.lastname, t1.dob, t1.gender, t1.lid, t1.ssn, t2.PRIMARY_PAYER_MEDICAID_MEDICARE_ID,
    t3.organization_id, t3.consent_status
    FROM DB1.Table1 t1
    JOIN DB2.Table2 t2 ON t2.patient_id = t1.LID
    JOIN DB2.Table3 t3 ON t3.patient_id=t1.LID
    WHERE ((t1.dob=$('DOB_PID71') AND t1.gender=$('Gender_PID81') AND (t1.ssn =$('SSN_PID19') OR t2.PRIMARY_PAYER_MEDICAID_MEDICARE_ID =$('MMC_ACCT_No_PID18')))
    OR (t1.lastname=$('FamilyName_PID51') AND t1.firstname=$('GivenName_PID52') AND t1.dob=$('DOB_PID71') AND t1.gender=$('Gender_PID81')))
    AND t3.Organization_id=1 AND t3.consent_status='PERMIT'')

  • #2
    I got the MySQL query working in JS but now when I am trying to write the distinct data fetch by my query into the DB, I am getting below error message. What am I doing wrong here? Can someone please help?

    JavaScript Writer error
    ERROR MESSAGE: Error evaluating JavaScript Writer
    com.mirth.connect.server.MirthJavascriptTransforme rException:
    CHANNEL: Inbound ADT
    CONNECTOR: Destination 3
    SCRIPT SOURCE: JavaScript Writer
    SOURCE CODE:
    33:
    34: logger.info('SQL Expression = '+ result);
    35: //var result = dbConn.executeCachedQuery(expression);
    36: var PatientMatch = dbConn.executeCachedQuery(result);
    37: PatientMatch.next();
    38: var myData= result.getClob(9);
    39: logger.info(" Patient is found: "+myData);
    40: channelMap.put('Patient is found',myData);
    41:
    42: } finally {
    LINE NUMBER: 38
    DETAILS: TypeError: Cannot find function getClob in object SELECT DISTINCT t1.firstname, t1.lastname, t1.dob, t1.gender, t1.lid, t1.ssn, t2.PRIMARY_PAYER_MEDICAID_MEDICARE_ID,
    t3.organization_id, t3.consent_status
    FROM DB1.Table1 t1
    JOIN DB2.Table2 t2 ON t2.patient_id = t1.LID
    JOIN DB2.Table3 t3 ON t3.patient_id=t1.LID
    WHERE ((t1.dob=$('DOB_PID71') AND t1.gender=$('Gender_PID81') AND (t1.ssn =$('SSN_PID19') OR t2.PRIMARY_PAYER_MEDICAID_MEDICARE_ID =$('MMC_ACCT_No_PID18')))
    OR (t1.lastname=$('FamilyName_PID51') AND t1.firstname=$('GivenName_PID52') AND t1.dob=$('DOB_PID71') AND t1.gender=$('Gender_PID81')))
    AND t3.Organization_id=1 AND t3.consent_status='PERMIT'').
    at dddda446-911d-4404-8f97-ba08dd41f323:38 (doScript)
    at dddda446-911d-4404-8f97-ba08dd41f323:49
    at com.mirth.connect.connectors.js.JavaScriptDispatch er$JavaScriptDispatcherTask.call(JavaScriptDispatc her.java:184)
    at com.mirth.connect.connectors.js.JavaScriptDispatch er$JavaScriptDispatcherTask.call(JavaScriptDispatc her.java:122)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker( Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run (Unknown Source)
    at java.lang.Thread.run(Unknown Source)

    Comment


    • #3
      I ran into this once before. I believe you just need to use getString(9) instead of getClob(9)

      Comment


      • #4
        I already tried getString(9) but getting below error:

        ERROR 2015-05-08 12:41:04,859 [pool-4-thread-46] com.mirth.connect.connectors.js.JavaScriptDispatch er: Error evaluating JavaScript Writer (JavaScript Writer "Destination 3" on channel d7970bbd-8d5e-4f72-ab42-7dac10ede6ff).
        com.mirth.connect.server.MirthJavascriptTransforme rException:
        CHANNEL: ED SSON Inbound ADT-DevChannel
        CONNECTOR: Destination 3
        SCRIPT SOURCE: JavaScript Writer
        SOURCE CODE:

        33: //var result = dbConn.executeCachedQuery(expression);
        34: var PatientMatch = dbConn.executeCachedQuery(result);
        35: logger.info('SQL Expression = '+ result);
        36: PatientMatch.next();
        37: var myData= result.getString(9);
        38: logger.info(" Patient is found: "+myData);
        39: channelMap.put('Patient is found',myData);
        40:
        41: } finally {
        LINE NUMBER: 37
        DETAILS: TypeError: Cannot find function getString in object SELECT DISTINCT t1.firstname, t1.lastname, t1.dob, t1.gender,

        Comment


        • #5
          You should be using PatientMatch. That is where you are storing the results.

          Comment


          • #6
            Javascript writer help

            SELECT DISTINCT t1.firstname, t1.lastname, t1.dob, t1.gender, t1.lid, t1.ssn, t2.PRIMARY_PAYER_MEDICAID_MEDICARE_ID,
            t3.organization_id, t3.consent_status
            FROM DB1.Table1 t1
            JOIN DB2.Table2 t2 ON t2.patient_id = t1.LID
            JOIN DB2.Table3 t3 ON t3.patient_id=t1.LID
            WHERE ((t1.dob=$('DOB_PID71') AND t1.gender=$('Gender_PID81') AND (t1.ssn =$('SSN_PID19') OR t2.PRIMARY_PAYER_MEDICAID_MEDICARE_ID =$('MMC_ACCT_No_PID18')))
            OR (t1.lastname=$('FamilyName_PID51') AND t1.firstname=$('GivenName_PID52') AND t1.dob=$('DOB_PID71') AND t1.gender=$('Gender_PID81')))
            AND t3.Organization_id=1 AND t3.consent_status='PERMIT'')

            I am storing the result from above query into the PatientMatch as you corrected me. If there are multiple results (rows) displayed from select query, then how can I go thru each and every row and find best patient record with correct SSN (only one row that best matches based on ssn)? Can you help here please?

            Comment


            • #7
              Hi hrc511,

              as far as I know not all methods are supported by Mirth datasets, if you need to manage CLOB fields use java datasets. Check this forum for examples.

              Hope this helps,

              Ricard Bernat

              Comment


              • #8
                Hi Richard,

                Thank you for the clue. I don't see any forum attached in your response. Thanks.

                Comment


                • #9
                  this is the basic db loop I use.
                  Code:
                  var result = dbConn.executeCachedQuery(query);
                  while(result.next()) //loop through multiple rows
                  {
                   var smallValue=result.getString(index or column name);
                   var bigValue=result.getClob(index or column name);
                   //test value
                  }

                  Comment

                  Working...
                  X