SQL field showing variable name and not variable itself

[oakwood]

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

[chrisl]

Re: SQL field showing variable name and not variable itself

Can you export your channel and post here?

Thanks!
-Chris

[oakwood]

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>

[chrisl]

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

[oakwood]

Re: SQL field showing variable name and not variable itself

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

[oakwood]

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.