No announcement yet.

database writer using SQL Question

  • Filter
  • Time
  • Show
Clear All
new posts

  • database writer using SQL Question

    Setting up a MS Server 2016 with Open JDK and Connect 3.8.1. current server is Server 2008 RS with Connect 3.2.1. Had an issue with using JavaScript when importing my channels for my database writer, went through what I found about the changes needed for it to work couldn't get it to work so I switched over to using SQL which works except that when the destination mappings carriable is blank/null instead of getting a blank/null entry in my database field I get the destination mapping variable, i.e. ${MRN}. Under JavaScript i would use the destination mapping as $('MRN') and if it was blank then it went in blank, that just gives me $('MRN') now. Am I missing something? SQL Below

    insert into epic_order (lname, fname, mi, dob, ssn, gender, slh_corp_account_id, slh_med_rec_id, sle_med_rec_id, sls_med_rec_id, sln_med_rec_id, cushing_med_rec_id, epic_med_rec_id, slh_cpi, slh_hne, facility_code, address1, address2, city, state, zip, pri_phone, epic_order_num, epic_filler_order_num, order_status, epic_procedure_code, order_phys_id, order_phys_lname, order_phys_fname, order_phys_mi, requested_datetime, order_notes, reason_for_test, updated)

  • #2
    I think you're going to need to set every value in your transformer, even if you are setting it to an empty string.

    $('MRN') returns an empty string when none of the maps contain the key.

    In most connector fields and templates, as you've noticed, ${MRN} will return the literal token if it can't find the key. These templates use Apache Velocity, and can actual do quite a bit more than basic string replacement, like conditionals and loops. Velocity has a quiet reference that looks like $!{MRN} that will return an empty string if the key isn't found.

    Unfortunately the database sql templates work a little differently from most other templates, and it won't work there, so I think you're stuck setting every value.


    • #3
      That was it, in the old JavaScript in the transformer, all I had was if it was there and not if it was not in the PID. Adjusting the transformer worked.

      Thank you, I knew it was something simple I was missing. Sometimes it just takes that extra set on eyes on the issue.


      • #4
        What was the problem with db reads/write in javascript? That's normally a simple construct.
        Mirth 3.8.0 / PostgreSQL 11 / Ubuntu 18.04
        Diridium Technologies, Inc.


        • #5
          When set to JavaScript it shows the proper dbConn and stored procedure to execute and shows SENT: Database write success. There is no DB entry on the table. When I set it to validate response. it goes to queued with a response error of: Empty or blank response received. Which makes sense since it is not actually connecting to the DB and Writing the table entry. Other than that there are no errors I can find in the logs. The connection info is the same and the SQL which works.


          • #6
            here is the javascript

            var dbConn = DatabaseConnectionFactory.createDatabaseConnection ('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sql server://','login','pasword');

            var result = dbConn.executeUpdate("InsertORM001Msg @lname='" + $('LNAME') + "', @fname='" + $('FNAME') + "', @mi='" + $('MI') + "', @dob='" + $('DOB') + "', @ssn='" + $('SSN') + "', @gender='" + $('GENDER') + "', @slh_corp_account_id='" + $('STAR_ACCT_NUM') + "', @slh_med_rec_id='" + $('SLHMR') + "', @sle_med_rec_id='" + $('SLEMR') + "', @sls_med_rec_id='" + $('SLSMR') + "', @sln_med_rec_id='" + $('SLNMR') + "', @cushing_med_rec_id='" + $('CUSHINGMR') + "', @epic_med_rec_id='" + $('EPICMR') + "', @slh_cpi='" + $('CPI') + "', @slh_hne='" + $('HNE') + "', @facility_code='" + $('LOCATION') + "', @address1='" + $('ADDRESS1') + "', @address2='" + $('ADDRESS2') + "', @city='" + $('CITY') + "', @state='" + $('STATE') + "', @zip='" + $('ZIP') + "', @pri_phone='" + $('HOMEPHONE') + "', @epic_order_num='" + $('ORDER_NUMBER') + "', @epic_filler_order_num='" + $('FILLER_ORDER_NUM') + "', @order_status='" + $('ORDER_STATUS') + "', @epic_procedure_code='" + $('PROCEDURECODE') + "', @order_phys_id='" + $('ORDERPHYSID') + "', @order_phys_lname='" + $('ORDERPHYSLNAME') + "', @order_phys_fname='" + $('ORDERPHYSFNAME') + "', @order_phys_mi='" + $('ORDERPHYSMI') + "', @requested_datetime='" + $('REQUESTED_DATE_TIME') + "', @order_notes='" + $('ORDER_NOTES') + "', @reason_for_test='" + $('REASON_FOR_TEST') + "'");


            if you click on the insert button you can filter the tables and get the columns back, so I know it can/is connecting to the SQL server. This is to only SQL Server 2016 I have and we are testing migration to it, the latest Mirth and server 2016 for the servers. I have name pipes, TCPIP and shared memory enabled.


            • #7
              A few suggestions are below - but what is happening with that code? It fails?

              Build that statement first in a variable so you can see the actual substitution:


              var statement = "update stuff set x = '" + $('var') +"'";
              $c('SQL_SomeCode', statement)
              When your channel runs you will see the actual SQL in the channel map to cut/paste and check manually if your substitutions are correct.

              Use a function for db calls:

              Assumes that you have put the db variables in the Configuration Map.

              function executeSharedDBStatement(statement, isQuery, paramList) {
              	var result;
              	try {
              		var dbConn = globalMap.get("dbConn");
              		if (dbConn == null || dbConn.getConnection().isClosed()) {
              			dbConn = DatabaseConnectionFactory.createDatabaseConnection(configurationMap.get('ms_driver'), configurationMap.get('ms_url'), configurationMap.get('ms_username'), configurationMap.get('ms_password'));
              			globalMap.put("dbConn", dbConn);
              		if (paramList == undefined) {
              			paramList = new;
              		if (isQuery) {
              			result = dbConn.executeCachedQuery(statement, paramList);
              		} else {
              			result = dbConn.executeUpdate(statement, paramList);
              	} catch (e) {
              	} finally {
              		return result;
              For the above SQL:

              var result = executeSharedDBStatement(statement, false) // first param is the sql, second if you are expecting a result, third is for paramterized SQL, that's another topic.
              A big advantage here is you can change servers/databases at will for all channels by simply changing a single variable in the configuration map.
              Mirth 3.8.0 / PostgreSQL 11 / Ubuntu 18.04
              Diridium Technologies, Inc.


              • #8
                There are benefits to using the sql readers/writers in sql mode rather than javascript. It looks much cleaner, and you aren't managing connections yourself.

                You can add a javascript step at the beginning of your transformer to initialize all of your sql parameters to empty strings at once.

                PHP Code:
                // or use connectorMap if appropriate


                • #9
                  Actually, you could put them in the globalChannelMap once in the deploy script, and then you don't need to touch your transformer or add them for each message.