Announcement

Collapse
No announcement yet.

SQL field showing variable name and not variable itself

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

  • SQL field showing variable name and not variable itself

    I've got everything set up properly to transfer data from HL7 flat file to SQL, but now the variable I created in the transformer comes over into sql as just the variable name.
    In the transformer the Variable is called l_name and is mapped to msg['PID']['PID.5']['XPN.1'].toString() I've tried adding it to global map and the rest, and they all still return {$l_name} to my sql table.
    here is the sql statement
    INSERT IGNORE INTO testhl7 (l_name, f_name) VALUES (${l_name}, ${f_name});
    the variables show up in Destination Mappings and I drug them over.

    Any suggestions?

    Thanks,
    Jeff

  • #2
    Re: SQL field showing variable name and not variable itself

    Can you export your channel and post here?

    Thanks!
    -Chris
    Chris Lang

    Comment


    • #3
      Re: SQL field showing variable name and not variable itself

      - <com.webreach.mirth.model.Channel>
      <id>f009f5ab-521a-4bbc-9991-c4c586c58b5c</id>
      <name>HL7TEST</name>
      <description />
      <enabled>true</enabled>
      <version>1.4.0</version>
      <revision>9</revision>
      - <sourceConnector>
      <name>sourceConnector</name>
      - <properties>
      <property name="sortAttribute">date</property>
      <property name="pollingFrequency">1000</property>
      <property name="binary">0</property>
      <property name="processBatchFiles">1</property>
      <property name="DataType">File Reader</property>
      <property name="fileAge">0</property>
      <property name="moveToPattern" />
      <property name="charsetEncoding">DEFAULT_ENCODING</property>
      <property name="checkFileAge">0</property>
      <property name="autoDelete">0</property>
      <property name="host">C:/Program Files/HL7 Inspector/samples</property>
      <property name="fileFilter">*.*</property>
      <property name="moveToDirectory" />
      </properties>
      - <transformer>
      - <steps>
      - <com.webreach.mirth.model.Step>
      <sequenceNumber>0</sequenceNumber>
      <name>l_name</name>
      <script>globalMap.put(&#039;l_name&#039;, validate(msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.1&#039;].toString(), &#039;&#039;, new Array()));</script>
      <type>Mapper</type>
      - <data class="map">
      - <entry>
      <string>Mapping</string>
      <string>msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.1&#039;].toString()</string>
      </entry>
      - <entry>
      <string>Variable</string>
      <string>l_name</string>
      </entry>
      - <entry>
      <string>RegularExpressions</string>
      <list />
      </entry>
      - <entry>
      <string>DefaultValue</string>
      <string />
      </entry>
      - <entry>
      <string>isGlobal</string>
      <string>global</string>
      </entry>
      </data>
      </com.webreach.mirth.model.Step>
      - <com.webreach.mirth.model.Step>
      <sequenceNumber>1</sequenceNumber>
      <name>f_name</name>
      <script>globalMap.put(&#039;f_name&#039;, validate(msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.2&#039;].toString(), &#039;&#039;, new Array()));</script>
      <type>Mapper</type>
      - <data class="map">
      - <entry>
      <string>Mapping</string>
      <string>msg[&#039;PID&#039;][&#039;PID.5&#039;][&#039;XPN.2&#039;].toString()</string>
      </entry>
      - <entry>
      <string>Variable</string>
      <string>f_name</string>
      </entry>
      - <entry>
      <string>RegularExpressions</string>
      <list />
      </entry>
      - <entry>
      <string>DefaultValue</string>
      <string />
      </entry>
      - <entry>
      <string>isGlobal</string>
      <string>global</string>
      </entry>
      </data>
      </com.webreach.mirth.model.Step>
      </steps>
      <inboundTemplate>MSH|^~\&|ADT-HIS||HL7INSPECTOR||20060101100000||ADT^A01|1|P|2.3 EVN|A01|20060101|| PID||||4711|Doe^John||19701024|M|||Sesamstreet 11^76137^Karlsruhe^D|||||GS|EV|||| PV1||I|S2^13^3^CHI^^21||||||||||||||||1234|||||||| |||||||||||||||||200601010930||||||||</inboundTemplate>
      <outboundTemplate />
      <inboundProtocol>HL7V2</inboundProtocol>
      <outboundProtocol>HL7V2</outboundProtocol>
      <inboundProperties />
      <outboundProperties />
      </transformer>
      - <filter>
      <rules />
      </filter>
      <transportName>File Reader</transportName>
      <mode>SOURCE</mode>
      </sourceConnector>
      - <destinationConnectors>
      - <com.webreach.mirth.model.Connector>
      <name>Destination 1</name>
      - <properties>
      <property name="username">sa</property>
      <property name="DataType">Database Writer</property>
      <property name="useScript">0</property>
      <property name="URL">jdbc:jtds:sqlserver://x.x.x.x:1433/Tier_4_test</property>
      <property name="query">INSERT IGNORE INTO testhl7 (l_name, f_name) VALUES (${l_name}, ${f_name});</property>
      <property name="script" />
      <property name="password">xxxxxx</property>
      <property name="host">query</property>
      <property name="driver">net.sourceforge.jtds.jdbc.Driver</property>
      </properties>
      - <transformer>
      <steps />
      <inboundProtocol>HL7V2</inboundProtocol>
      <outboundProtocol>HL7V2</outboundProtocol>
      </transformer>
      - <filter>
      <rules />
      </filter>
      <transportName>Database Writer</transportName>
      <mode>DESTINATION</mode>
      </com.webreach.mirth.model.Connector>
      </destinationConnectors>
      - <properties>
      <property name="initialState">started</property>
      <property name="synchronous">true</property>
      <property name="store_messages">true</property>
      <property name="encryptData">false</property>
      <property name="transactional">false</property>
      <property name="max_message_age">-1</property>
      <property name="dont_store_filtered">false</property>
      <property name="error_messages_only">false</property>
      </properties>
      <preprocessingScript>// Modify the message variable below to pre process data return message;</preprocessingScript>
      </com.webreach.mirth.model.Channel>

      Comment


      • #4
        Re: SQL field showing variable name and not variable itself

        Everything looks ok - try the connectorMap or channelMaps - do you get the same result?

        -Chris
        Chris Lang

        Comment


        • #5
          Re: SQL field showing variable name and not variable itself

          I tried all 4. Maybe I will try creating it from scratch again tomorrow.

          Comment


          • #6
            Re: SQL field showing variable name and not variable itself

            Re-Creating from scratch seemed to do the trick, not sure what the problem was.

            I did hit a snag when I re-created the channel, I forgot to rename TestDB in the connector string to my database name and after running it once it didn&#039;t matter what I changed it to it always gave me an error saying it failed to connect to TestDB. I had to re-create the channel all over again a 3rd time, where everything worked like a charm.

            Comment

            Working...
            X