Question
· Jan 6, 2020

Querying EnsLib.HL7.Message via ADO/ODBC

While I can query the HL7 message class EnsLib.HL7.Message (EnsLib_HL7.Message for SQL) to my heart's content in the SQL Shell or the Management Portal's SQL page, I can't seem to SELECT anything other than ID/%Id from an ADO/ODBC client. Properties such as TimeCreated, Name, MessageTypeCategory, etc. all seem to prevent the query from ever completing EXCEPT when I provide the ID as part of the WHERE criteria.

This works fine in the Management Portal and Shell:

SELECT head.ID As HeadId, body.ID As BodyId, body.Name as BodyName
FROM Ens.MessageHeader head
INNER JOIN EnsLib_HL7.Message body
ON head.MessageBodyId = body.ID
WHERE Body.Name = 'ADT_A04'

It fails, however, when I attempt the same query over an ADO or ODBC connection.

Interestingly, this succeeds:

SELECT head.ID As HeadId,body.ID As BodyId, body.Name as BodyName
FROM Ens.MessageHeader head
INNER JOIN EnsLib_HL7.Message body
ON head.MessageBodyId = body.ID
WHERE body.ID = 5745

Is there some special juju that I need to incorporate into my client queries to get the same behavior as the Management Portal and Shell?

Discussion (7)1
Log in or sign up to continue

Thanks for the response, fellow Jeffrey!

The tables I'm currently working with are tiny, literally no more than a couple dozen message header and body records. The query in the IRIS SQL shell (and management portal SQL facility) returns results immediately.

I think we have some sort of obscure bug here. This is with IRIS for Health CE 2019.1 running on Ubuntu (not a docker image), and the client is Windows 10 using IRIS ADO.NET support. Same result when using the ODBC drivers, though.

I think you're right on reaching out to WRC ... thanks again.

Jeffrey,

The scenario that I can hypothesize that would produce these results are around the client timing out before the query finishes executing on the server. 

Query 1 fails because you have to do a join on the two large tables and your only where clause is against a property that is not indexed.

Query 2 succeeds because object IDs are "indices by nature".

Query 3(from your comment) succeeds because you are filtering out hundreds, thousands, maybe millions of Ens.MessageHeader objects in your join by using a where clause against and indexed MessageBodyClassName property.

I would recommend reaching out to InterSystems WRC to review the query cost and overall performance to confirm or dive deeper into the failures.

Not sure if you contacted the WRC or not, but if not here is what I would try.

1)  Can you use a different ODBC tool?  Our fav in support is WinSQL.  If this works then we know we are dealing with an ADO issue, if it fails then most likely we have an ODBC bug.

2)  Turn on the ODBC client logging. If you are using the ODBC Data Source Manager then there is a checkbox ODBC logging.  You will need to stop and start your ADO client to pick up the flag, reproduce the problem and then look for the irisODBC.log file in C:\Users\Public\Logs