Announcement

Collapse
No announcement yet.

Newbie ? re: creating variable for use in SQL Serv

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

  • Newbie ? re: creating variable for use in SQL Serv

    Hello all. New user here.

    I've successfully setup and started Mirth 1.7, created a channel to read in an HL7 text file and am building a Channel with a Transform to insert a record in a SQL Server 2000 database.

    Where I'm having difficulty is using the data in a non-XML format. My query ends up looking like:

    INSERT (?, ? ) INTO hl7test (firstname, lastname) Parameters: [<MSH.40.1>TEST1</MSH.40.1>
    <MSH.40.1>TEST2</MSH.40.1>, <MSH.40.2>TEST3</MSH.40.2>
    <MSH.40.2>TEST4</MSH.40.2>]

    ... which obviously fails.

    I'm sure I need to create some variable to hold *only* the data, but at this point it is beyond me to see how....?

    How do you create this type of variable to include only the raw data and not have it embedded within XML tags?

    Or is there an easier way to do all of this?

    Thanks in advance,
    Walter

  • #2
    Re:Newbie ? re: creating variable for use in SQL Serv

    Well, I somewhat answered my own question ...

    I re-crafted the SQL in the Destination Database Writer to read:

    INSERT IGNORE INTO hl7test (firstname, lastname) VALUES (${FirstName}, ${LastName})

    This works when the HL7 file only contains a single record.

    My further question is what if there is more than one HL7 record in the file?

    What if there are multiple MSH records inside a single file? Then the parameters are passed to the SQL string as:

    Parameters: [<MSH.40.1>TEST1</MSH.40.1>
    <MSH.40.1>TEST2</MSH.40.1>, <MSH.40.2>TEST3</MSH.40.2>
    <MSH.40.2>TEST4</MSH.40.2>]

    Ideas? Hints? Anyone?

    Post edited by: nieszhw, at: 07/16/2008 10:43

    Comment


    • #3
      Re:Newbie ? re: creating variable for use in SQL Serv

      Can you post your channel?

      It should be a simple and quick fix once we get a look at it.
      Jon Bartels

      Zen is hiring!!!!
      http://consultzen.com/careers/
      Talented healthcare IT professionals wanted. Engineers to sales to management.
      Good benefits, great working environment, genuinely interesting work.

      Comment


      • #4
        Re:Newbie ? re: creating variable for use in SQL Serv

        Not sure if this is the correct way to post a Channel or not but here goes ...

        Keep in mind this is my first attempt at a Channel, so don't laugh too loudly when you're looking at it.

        Walter

        - <com.webreach.mirth.model.Channel>
        <id>f58b4d49-5ba8-42a9-9f1f-b11f46b821e6</id>
        <name>test2</name>
        <description />
        <enabled>true</enabled>
        <version>1.7.1.3422</version>
        - <lastModified>
        <time>1216229330855</time>
        <timezone>America/New_York</timezone>
        </lastModified>
        <revision>11</revision>
        - <sourceConnector>
        <name>sourceConnector</name>
        - <properties>
        <property name="sortAttribute">date</property>
        <property name="pollingType">interval</property>
        <property name="pollingFrequency">1000</property>
        <property name="binary">0</property>
        <property name="processBatchFiles">0</property>
        <property name="DataType">File Reader</property>
        <property name="moveToErrorDirectory" />
        <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">d:/program files/mirth/incoming</property>
        <property name="fileFilter">*</property>
        <property name="moveToDirectory">d:/program files/mirth/processed</property>
        </properties>
        - <transformer>
        - <steps>
        - <com.webreach.mirth.model.Step>
        <sequenceNumber>0</sequenceNumber>
        <name>FirstName</name>
        <script>var mapping;try { mapping = msg['MSH']['MSH.40']['MSH.40.1'].toString(); }catch (e) { logger.error(e); mapping = '';}channelMap.put('FirstName', validate( mapping , '', new Array()));</script>
        <type>Mapper</type>
        - <data class="map">
        - <entry>
        <string>Variable</string>
        <string>FirstName</string>
        </entry>
        - <entry>
        <string>DefaultValue</string>
        <string />
        </entry>
        - <entry>
        <string>Mapping</string>
        <string>msg['MSH']['MSH.40']['MSH.40.1'].toString()</string>
        </entry>
        - <entry>
        <string>RegularExpressions</string>
        <list />
        </entry>
        - <entry>
        <string>isGlobal</string>
        <string>channel</string>
        </entry>
        </data>
        </com.webreach.mirth.model.Step>
        - <com.webreach.mirth.model.Step>
        <sequenceNumber>1</sequenceNumber>
        <name>LastName</name>
        <script>var mapping;try { mapping = msg['MSH']['MSH.40']['MSH.40.2']; }catch (e) { logger.error(e); mapping = '';}channelMap.put('LastName', validate( mapping , '', new Array()));</script>
        <type>Mapper</type>
        - <data class="map">
        - <entry>
        <string>Variable</string>
        <string>LastName</string>
        </entry>
        - <entry>
        <string>DefaultValue</string>
        <string />
        </entry>
        - <entry>
        <string>Mapping</string>
        <string>msg['MSH']['MSH.40']['MSH.40.2']</string>
        </entry>
        - <entry>
        <string>RegularExpressions</string>
        <list />
        </entry>
        - <entry>
        <string>isGlobal</string>
        <string>channel</string>
        </entry>
        </data>
        </com.webreach.mirth.model.Step>
        </steps>
        <inboundTemplate>MSH|^~\&|IMS|HA|RIBS|UCP|20071205 143411||ADT^A08|2007120514341100001|D|2.2||||NE||| ||MSA|AA||OK||F||ERQ||A08|IDXMRN^100000005|QAK||OK |PID|1|101474915^^^IMS|||TEST^ANESTHESIA^^^^||1967 1108|F|||123 HAPPY STREET^^CINCINNATI^OH^45206||513-555-2054|||M||101474915|||||||||||||GT1|1||TEST^ANESTH ESIA^^^^||123 HAPPY STREET^^CINCINNATI^OH^45206|513-321-2054||19671108|F||1||||||||||||||||||||||||||||||| ||||||||||||||IN1|1|881||||||||||||||TEST^ANESTHES IA^^^^||||||||||||||||||||334125461A|||||||||||||| IN2||||IN1|2|41|948|GOLDEN RULE 1|7440 WOODLAND DR^^INDIANAPOLIS^IN^46278||317-55-4189|||||20060101||||TEST^ANESTHESIA^^^^|1|||||||| |||||||||||220220330||||||||||||||IN2||||</inboundTemplate>
        <outboundTemplate />
        <inboundProtocol>HL7V2</inboundProtocol>
        <outboundProtocol>HL7V2</outboundProtocol>
        <inboundProperties />
        <outboundProperties />
        </transformer>
        - <filter>
        <rules />
        </filter>
        <transportName>File Reader</transportName>
        <mode>SOURCE</mode>
        <enabled>true</enabled>
        </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://rbosql:1433/ua</property>
        <property name="query">INSERT IGNORE INTO hl7test (firstname, lastname) VALUES (${FirstName}, ${LastName})</property>
        <property name="script" />
        <property name="password">password</property>
        <property name="host">query</property>
        <property name="driver">net.sourceforge.jtds.jdbc.Driver</property>
        </properties>
        - <transformer>
        <steps />
        <inboundTemplate>MSH|^~\&|IMS|HA|RIBS|UCP|20071205 143411||ADT^A08|2007120514341100001|D|2.2||||NE||| ||MSA|AA||OK||F||ERQ||A08|IDXMRN^100000000|QAK||OK |PID|1|101474915^^^IMS|||TEST^ANESTHESIA^^^^||1967 1108|F|||123 HAPPY STREET^^CINCINNATI^OH^45206||513-555-2054|||M||101474915|||||||||||||GT1|1||TEST^ANESTH ESIA^^^^||123 HAPPY STREET^^CINCINNATI^OH^45206|513-321-2054||19671108|F||1||||||||||||||||||||||||||||||| ||||||||||||||IN1|1|881||||||||||||||TEST^ANESTHES IA^^^^||||||||||||||||||||334125461A|||||||||||||| IN2||||IN1|2|41|948|GOLDEN RULE 1|7440 WOODLAND DR^^INDIANAPOLIS^IN^46278||317-555-4189|||||20060101||||TEST^ANESTHESIA^^^^|1|||||||| |||||||||||220220330||||||||||||||IN2||||</inboundTemplate>
        <outboundTemplate />
        <inboundProtocol>HL7V2</inboundProtocol>
        <outboundProtocol>HL7V2</outboundProtocol>
        <inboundProperties />
        <outboundProperties />
        </transformer>
        - <filter>
        <rules />
        </filter>
        <transportName>Database Writer</transportName>
        <mode>DESTINATION</mode>
        <enabled>true</enabled>
        </com.webreach.mirth.model.Connector>
        </destinationConnectors>
        - <properties>
        <property name="synchronous">true</property>
        <property name="removeNamespace">true</property>
        <property name="transactional">false</property>
        <property name="initialState">stopped</property>
        <property name="dont_store_filtered">false</property>
        <property name="error_messages_only">false</property>
        <property name="max_message_age">-1</property>
        <property name="store_messages">true</property>
        <property name="encryptData">false</property>
        </properties>
        <preprocessingScript>// Modify the message variable below to pre process data return message;</preprocessingScript>
        <postprocessingScript>// This script executes once after a message has been processed return;</postprocessingScript>
        <deployScript>// This script executes once when the mule engine is started // You only have access to the globalMap here to persist data return;</deployScript>
        <shutdownScript>// This script executes once when the mule engine is stopped // You only have access to the globalMap here to persist data return;</shutdownScript>
        </com.webreach.mirth.model.Channel>

        Comment


        • #5
          Re:Newbie ? re: creating variable for use in SQL Serv

          Instead of using javascript, you should be able to drop your template in to the transformer and pick out fields and assign them to variables. I think there is an example of this in the Channels or Download section.

          You can attach files to posts or wrap your code in the CODE bb tags.
          Jon Bartels

          Zen is hiring!!!!
          http://consultzen.com/careers/
          Talented healthcare IT professionals wanted. Engineers to sales to management.
          Good benefits, great working environment, genuinely interesting work.

          Comment


          • #6
            Re:Newbie ? re: creating variable for use in SQL Serv

            Maybe I'm asking the question wrong....

            First, I did look in those areas and downloaded the example Channels
            and took a look. I didn't see anything that I thought could directly
            help me.

            I also didn't think I was using JavaScript. I thought I was just
            mapping the fields into variables. ??? Just my inexperience shining
            through.

            Let me ask it this way:

            1. If you receive a HL7 message through a LLP, is it the standard
            practice to receive one and only one message at a time? In other
            words, do I have to build my transform logic to expect a variable
            number of patients in a single "transaction"? Or does one LLP "burst"
            signify only a single patient's information?

            2. If you receive a single text file containing multiple HL7 records,
            how do you tell Mirth that there are multiple records in it? Or does
            it just "know"? And if it intrinsically knows there are many, how do
            you tell it to perform actions against each as separate transactions?

            This is what I was trying to do with the initial question. I've got
            a file that contains a variable number of records. I would like to
            process them individually and insert them into a SQL Server DB. But
            when I try to construct the SQL, Mirth builds the T-SQL as accepting
            parameters. But it passes all the data at once in a single SQL
            statement, with the parameters in XML format.

            Is this something that I would need to write custom JavaScipt to handle?
            Or is there an easier way?

            hwn

            Comment

            Working...
            X