Inconsistent behavior of the transformer

[rameshsr]

I am using DatabaseReader to read from my MySQL database and passing the data through a Transformer for transforming the SQL data into HL7 A28 message. For the transformation, I use Message Builder and do the following (for example):
Name: familyname
Message Segment: tmp['PID']['PID.5']['PID.5.1']
Mapping: msg['lastname'].toString()

Where 'lastname' is the SQL field which brings in the familyname. This used to work fine for sometime. But after adding and removing few other fields, the mapping entirely stopped working, even though i revereted to the earlier working version. I don't see any errors during transformation and messages are successfully sent to the FileWriter. When I look at the file written, the familyname field is blank, though my database has data.

I looked at the Mirth Dashboard and I see that the value for familyname is present in the xml data generated out of the source data. But during the transformation it is lost. Any pointers?

[trymirth]

Re:Inconsistent behavior of the transformer

I have sometimes had the need to prefix the field with the table name.

i.e. msg['mytable.lastname'].toString()

[rameshsr]

Re:Inconsistent behavior of the transformer

I tried that too, it still doesn't bring the data. Is there any other way of mapping the sql returned data to HL7? For example, how do I use javascript for this particular case? I tried the following in the Javascript for this field,

tmp['PID']['PID.5']['PID.5.1'] = msg['lastname'].toString();

and it doesn't bring the data too. What is Mirth doing? It doesn't even tell what the issue?

[albertosaez]

Re:Inconsistent behavior of the transformer

There're some differences with data retrieved from different JDBC drivers.

You can view the exact XML retrived from the DB in the dashboard: select a message, and chech the "raw" tab: you will see a XML with the names of the fields and the data retrieved from the db. If you're not sure post the XML and we'll try to help you.

[rameshsr]

Re:Inconsistent behavior of the transformer

I am using MySQL. Here is my select sql query in the Database Reader:

select pm.mrd_number as mrd_number, pm.firstname as firstname, pm.sex as sex, lm.language_name from patient_master pm
LEFT JOIN language_master lm ON ( lm.id = pm.language_id)
where pm.id=1

The source xml generated by Mirth is:

<?xml version="1.0" encoding="UTF-8"?>
<result>
<pm.mrd_number>1</pm.mrd_number>
<pm.firstname>PURUSHAM</pm.firstname>
<pm.sex>1</pm.sex>
<lm.language_name>Malayalam</lm.language_name>
</result>

Here is the Transformed Message. In my mapping, I have mapped firstname, mrd number and sex columns. But none of the mappings are available in the corresponding Transformed message fields. I am using the alias names of the sql in my mappings and everything worked fine until i introduced the lm.language_name in my select column list.

<?xml version="1.0" encoding="UTF-8"?>
<HL7Message>
<MSH>
<MSH.1>|</MSH.1>
<MSH.2>^~\&amp;</MSH.2>
<MSH.3>
<MSH.3.1>Regional MPI</MSH.3.1>
</MSH.3>
<MSH.4/>
<MSH.5>
<MSH.5.1>Master MPI</MSH.5.1>
</MSH.5>
<MSH.6>
<MSH.6.1>Alpha Hospital</MSH.6.1>
</MSH.6>
<MSH.7>
<MSH.7.1>20060501140010</MSH.7.1>
</MSH.7>
<MSH.8/>
<MSH.9>
<MSH.9.1>ADT</MSH.9.1>
<MSH.9.2>A28</MSH.9.2>
</MSH.9>
<MSH.10>
<MSH.10.1>3948375</MSH.10.1>
</MSH.10>
<MSH.11>
<MSH.11.1>P</MSH.11.1>
<MSH.11.2>T</MSH.11.2>
</MSH.11>
<MSH.12>
<MSH.12.1>2.4</MSH.12.1>
</MSH.12>
<MSH.13/>
<MSH.14/>
<MSH.15>
<MSH.15.1>ER</MSH.15.1>
</MSH.15>
</MSH>
<EVN>
<EVN.1>
<EVN.1.1>A28</EVN.1.1>
</EVN.1>
<EVN.2>
<EVN.2.1>20060501140008</EVN.2.1>
</EVN.2>
<EVN.3/>
<EVN.4/>
<EVN.5>
<EVN.5.1>000338475</EVN.5.1>
<EVN.5.2>Author</EVN.5.2>
<EVN.5.3>Arthur</EVN.5.3>
<EVN.5.4/>
<EVN.5.5/>
<EVN.5.6/>
<EVN.5.7/>
<EVN.5.8/>
<EVN.5.9>Regional MPI&amp;2.16.840.1.113883.19.201&amp;ISO</EVN.5.9>
<EVN.5.10>L</EVN.5.10>
</EVN.5>
<EVN.6>
<EVN.6.1>20060501140008</EVN.6.1>
</EVN.6>
</EVN>
<PID>
<PID.1/>
<PID.2>
<PID.2.1/>
</PID.2>
<PID.3>
<PID.3.1>000197245</PID.3.1>
<PID.3.2/>
<PID.3.3/>
<PID.3.4>NationalPN&amp;2.16.840.1.113883.19.3&amp ;ISO</PID.3.4>
<PID.3.5>PN~4532</PID.3.5>
<PID.3.6/>
<PID.3.7/>
<PID.3.8>CarefulCareClinic&amp;2.16.840.1.113883.1 9.2.400566&amp;ISO</PID.3.8>
<PID.3.9>PI~3242346</PID.3.9>
<PID.3.10/>
<PID.3.11/>
<PID.3.12>GoodmanGP&amp;2.16.840.1.113883.19.2.450 998&amp;ISO</PID.3.12>
<PID.3.13>PI</PID.3.13>
</PID.3>
<PID.4/>
<PID.5>
<PID.5.1>Patient</PID.5.1>
<PID.5.2/>
<PID.5.3/>
<PID.5.4/>
<PID.5.5/>
<PID.5.6/>
<PID.5.7>L</PID.5.7>
</PID.5>
<PID.6/>
<PID.7>
<PID.7.1>19750103</PID.7.1>
</PID.7>
<PID.8>
<PID.8.1>U</PID.8.1>
</PID.8>
<PID.9/>
<PID.10/>
<PID.11>
<PID.11.1>Randomroad 23a&amp;Randomroad&amp;23a</PID.11.1>
<PID.11.2/>
<PID.11.3>Anytown</PID.11.3>
<PID.11.4/>
<PID.11.5>1200</PID.11.5>
<PID.11.6/>
<PID.11.7>H</PID.11.7>
</PID.11>
<PID.12/>
<PID.13>
<PID.13.1>555 3542557</PID.13.1>
<PID.13.2>ORN</PID.13.2>
<PID.13.3>PH~555 3542558</PID.13.3>
<PID.13.4>ORN</PID.13.4>
<PID.13.5>FX</PID.13.5>
</PID.13>
<PID.14>
<PID.14.1>555 5557865</PID.14.1>
<PID.14.2>WPN</PID.14.2>
<PID.14.3>PH</PID.14.3>
</PID.14>
</PID>
<PV1>
<PV1.1/>
<PV1.2>
<PV1.2.1>N</PV1.2.1>
</PV1.2>
<PV1.3/>
</PV1>
</HL7Message>

[albertosaez]

Re:Inconsistent behavior of the transformer

As you get the next XML as input:

<?xml version="1.0" encoding="UTF-8"?>
<result>
  <pm.mrd_number>1</pm.mrd_number>
  <pm.firstname>PURUSHAM</pm.firstname>
  <pm.sex>1</pm.sex>
  <lm.language_name>Malayalam</lm.language_name>
</result>
<

You can access to the value of each field the next way:

 msg['pm.mrd_number'].toString();
 msg['pm.firstname'].toString();
 msg['pm.sex'].toString();
 msg['lm.language_name'].toString();

[rameshsr]

Re:Inconsistent behavior of the transformer

Thank you very much. It worked fine.