Database reader source XML tags case after 3.12.0

[rafreyes]

Hi,
since the update to 3.12.0 XML tags of messages in the source connector of type database reader started to follow the case of the SQL statement. Before all tags were in lowercase.
Example:
SQL statement -> SELECT e.EventID AS T_Events_EventID from T_Events e
source message before 3.12.0 -> <t_events_eventid>7269775</t_events_eventid>
source message after 3.12.0 -> <T_Events_EventID>7269775</T_Events_EventID>

This is breaking our code as mapping in the source transformer seems to be case sensitive. Something like msg['t_events_eventid'].toString() is not working anymore

wondering if anybody has found this issue as well.
Thank you

[ab-20-m]

This issue has come up in the Mirth Slack. As best I can tell, there were some changes to BasicRowProcessor in Apache Commons DbUtils 1.4, which was in pre Mirth 3.12.0 and DbUtils 3.17 which is what was updated in Mirth 3.12.0. A possible workaround might be to use a DbReceiver that runs Javascript instead of a SQL Query and modify the returned results set to force column names to lower.

As best I can tell the source level fix is to configure the BasicRowProcessor to force the mapping to lower case. Not sure if the fix for DBUTILS-91 triggered the issue or there were other changes in Commons DbUtils that changed the default mapping behavior.

[ab-20-m]

On closer inspection it looks like this is due to changes in commit 1bb4e7f. processResultList() is doing the case insensitive check but putting the case-sensitive key + entry into the map that gets passed to processRecord().

[rafreyes]

Thank you ab-20-m for your findings. further testing let me to this use case which is certainly related to my first post.

I have created a new test channel.

1. Source is a database reader, I let Mirth generate the SQL statement, which is

SELECT V_HPDA_COLLABS_TO_EXPORT.COLLAB_ID AS COLLAB_ID FROM V_HPDA_COLLABS_TO_EXPORT

2. Then in the source transformer Mirth generated an inbound message template, which is

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <result> <collab_id>value</collab_id> </result>

3. I want to create a flat file out, so, in the transformer I used a message builder step which generated the following JS code

tmp['row']['column1'] = validate(msg['collab_id'].toString(), '', new Array());

4. after deploying the channel, this message was received
   <result> <COLLAB_ID>6221715</COLLAB_ID> </result>

Due to the tags in capitals the output is empty. it looks a bug to me as almost every artifact used (sql statement, inbound message template...) was generated by Mirth itself and the result is not as expected. Before 3.12.0 this was a trivial channel configuration

I have opened a support case with nextgen.

[OmikronTheta]

Were you ever able to come up with a fix for this? I just recently updated to 3.12 and have 100s of broken channels now. I can't possible change every channel to proper case at this point. Is there any work around?

If not how do I downgrade back to 3.10?

[OmikronTheta]

I'm curious if this will be addressed in the next update? This was a major hurdle for us in upgrading to 3.12 and broke 100s of our channels. Seems like a major oversight for Database Readers.

[mkopinsky]

I'm torn on this. I don't like the current behavior (where SELECT SomeColumnName FROM table creates <somecolumnname>value</somecolumnname>) but I also don't want dozens of channels to break and have been holding off on upgrading to 3.12 as a result.

I hope that the answer isn't to revert the change but to make it configurable on the DB reader. That would let us build new channels without the annoying downcasing, and let us selectively migrate channels one by one (or just leave old channels as is).

[tonygermano]

@twest-mirthconnect @narupley This is the suggestion I was also going to make. I think it needs to be configurable on the db reader whether to lowercase the column names (for backwards compatibility) or use the same case as the query.

[twest-mirthconnect]

The team is reviewing the ticket and will update this group here and on slack as we prep for the work to be done.

[tonygermano]

Can we get a warning about this issue added to https://github.com/nextgenhealthcare/connect/wiki/3.12.0---Upgrade-Guide ASAP?

[OmikronTheta]

I don't completely disagree with you that it's odd that the case in your select is ignored in your message builder, but also seeing as SQL is case insensitive I don't see why then the message builders should be case sensitive. It's not like you can have SomeColumnName and somecolumnname In your SQL select.so I don't see what you gain by making the message builder case sensitive. There's literally no benefit at all that I can see by having any of this stuff case sensitive. Other than creating more tedious work for the end user to make sure they are matching up variable names between their source and destination. We ended up creating a preprocessor script for each channel to lowercase the RAW message, it was a ton of work and we still had to change a bunch of transformers but in the end it did seem to end up working. But it was a TON of work we shouldn't have had to do.

…

On Wed, Jan 5, 2022 at 9:42 AM Michael Kopinsky ***@***.***> wrote: I'm torn on this. I don't like the current behavior (where SELECT SomeColumnName FROM table creates <somecolumnname>value</somecolumnname>) but I also don't want dozens of channels to break and have been holding off on upgrading to 3.12 as a result. I hope that the answer isn't to revert the change but to make it configurable on the DB reader. That would let us build new channels without the annoying downcasing, and let us selectively migrate channels one by one (or just leave old channels as is). — Reply to this email directly, view it on GitHub <#4878 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABPOK4TUXYSN6Y6A3UHCOJDUURKHFANCNFSM5I2JKDEA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you commented.Message ID: ***@***.*** com>

[OmikronTheta]

Also it's kind of ironic that even in 3.12 the MessageTree in my view is still ALL LOWERCASE, so even if I drag and drop from it to my message builder, it will still be broken. Shouldn't the message tree at least match that case from the source select database reader if you're going to make my message builders case sensitive???

[pacmano1]

I wasn't arguing that this needs to be addressed of course - simply wondering what your approach was to fixing your stuff since others had the same issue. That is valuable information. It's almost a funny topic to me - since I have never used mixed case for anything in a schema since 1988 or so, but that is a different topic, lol.

Obviously another important point here is that people suffering from this problem did not:

• Test their upgrade
• Have a proper rollback plan.
• Perhaps slow migrate prod (multiple engines up migrating handfuls of channels at a time).

That's on you (all).

[OmikronTheta]

Yeah I definitely could have done more due diligence in the test process before updating. Unfortunately with 100s of channels we don't always have a way to test each and every one. Some of them being older than my tenure managing this setup. We don't have a UAT environment that even makes it possible for us to test every channel we have against production data/schema. Again not really Mirth's problem there but with 100s of channels it becomes a little difficult to maintain a 1 to 1 UAT environment for every one.

Rollback was not hard and I could have just used my backup to rollback but I really didn't want to do that if I'm being honest.

As far as mixed case in the schema, I mean it's SQL server column case isn't something that's ever really thought about since again SQL isn't case sensitive. Also we don't always control the schema's were are pulling from and I'm not about to start asking the DBA to change column names to lowercase because "that's what Mirth wants", he'd probably just laugh at me.

Also this seems like a pretty considerable change and should probably have been called out in the upgrade guide or release notes if it was an intentional change. If it was simply an unintentional bug or side effect of another change I guess that it was it is.

[ctrlaltdude]

I'm so happy I found this post!!! Thanks!
Have been searching for over 4 hours why my variables (all but 1) were empty.
Really annoying that Mirth does this automatically. I didn't notice that the XML-tags were changed to a capital first letter. But if the tag contains a dash (-) it doesn't happen.

[twest-mirthconnect]

@cturczynskyj @pladesma

Gents, cases sensitivity is resolved in 4.0 scheduled for release this month (March 29th). I see that we have a mix of additional elements in the string above but wanted to be sure those here knew we heard and are responding to at least the "big" headache. Thank you for pitching in and helping out!