New post
AllAccepted answers
Jonathan Anglin · Jan 10, 2022

After working with Joao Navarro in the WRC, here's what I discovered.

My initial query was based on the results of the "Show Query" button in the Management Portal Message Viewer. While that provided a good start, the SELECT clause that Navarro suggested returned what I was looking for.

A couple of notes:

  1. Dates are stored in UTC, so use "GETUTCDATE" when trying to limit the search parameters by time.
  2. RawContent contains carriage returns between the segments. You may need to remove $CHAR(13) with the appropriate function.

SELECT EnsLib_HL7.Message.RawContent AS Full_Message INTO :FullMessage
FROM Ens.MessageHeader head, EnsLib_HL7.SearchTable, EnsLib_HL7.Message
WHERE head.TimeCreated >= DATEADD(hour, -24, GETUTCDATE())
AND ((head.SourceConfigName = 'SITE ORM-ORU Inbound')
AND (head.TargetConfigName = 'SITE ORM 1 PreProcessor')
AND head.MessageBodyClassName=(('EnsLib.HL7.Message'))
AND head.MessageBodyId = EnsLib_HL7.SearchTable.DocId
AND EnsLib_HL7.SearchTable.PropId = 6
AND EnsLib_HL7.SearchTable.PropValue = :placerOrderNum
AND head.MessageBodyClassName='EnsLib.HL7.Message'
AND head.MessageBodyId=EnsLib_HL7.Message.%ID)
ORDER BY head.ID Desc

Jonathan Anglin · Jan 7, 2022

Hi Jeff

TOP...of course! Yeah that only returns one result when I run the query in the SQL window. However, I still do not get the FullMessage variable populated from the query when run in the DTL, so the "SELECT TOP 1 Full_Message INTO :FullMessage" bit is not working. 

Jonathan Anglin · Jul 13, 2021

After conferring with the WRC, it seems there really wasn't a "problem" at all, but rather that while they share the same syntax for INSERT commands,  there is a syntax difference in how HealthShare and Microsoft each handle an UPDATE command.

The following code works in HealthShare/ObjectScript:

Set tSQL = "UPDATE table_name (col1, col2, col3) VALUES ('val1', 'val2', 'val3') WHERE accession_number='"_accessionNumber_"'"
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)

And this is how Microsoft wants it:

Set tSQL = "UPDATE IRIS_Study SET col1='val1', col2='val2', col3='val3' WHERE accession_number='"_accessionNumber_"'"
Set tSC = ..Adapter.ExecuteUpdateParmArray(.nrows,tSQL,.par)

And it can still be parameterized by using:

SET col1=?, col2=?, col3=?
Jonathan Anglin · Jul 8, 2021

Odd(ish) the limitation is on the UPDATE but not on the INSERT. I will follow up with the WRC and see if I can get a definitive answer.

I am using the parameters to avoid code insertion (not that I'm terribly worried about it, but it's just good practice). If I cannot do that, then I'll have to do some kind of variable cleaning on the string, or perhaps define each as a %STRING literal or something. Whatever the best practice is for ObjectScript.

Jonathan Anglin · Jul 8, 2021

Well maybe I am hitting a limit then, because there are 48 variables.

The error is reported at execution. I see it in the interface's log. I get no errors when compiling the ObjectScript.

Jonathan Anglin · Jul 8, 2021

Standard value assignment does work for updating the record. I also looked at every open parenthesis in the code just in case something looked off, but it all seems to be fine.

It appears the issue may be the dynamic statement parameter binding. 

Jonathan Anglin · Jul 6, 2021

If I use a standard value assignment (SET mrn = '123456', lastName = 'Smith', etc.), it works just fine pulling the values from the HL7 message and writing them to the appropriate columns. I'm going through the code and adding corresponding lines for each variable I wish to store:

Set valueAssignment = valueAssignment_", site_code = '"_siteCode_"'"
...
Set tSQL = "UPDATE table_name"_valueAssignment_" WHERE accession_number='"_accessionNumber_"'"

I'm hoping that there may not be an issue with the parameter binding at all and it's just some odd value in one of the fields, although everything looks fine. Once I get through added that line for all of the variables in a day or two, I'll know for sure. So far, it's nothing in MSH, PID, or PV1.

I'll reply back once I know.

Jonathan Anglin · Jul 1, 2021

I have not, but that's a good suggestion. I may also rework the script to not use a parameterized (is that a word) update query and see if that works.

Jonathan Anglin · Jun 30, 2021

SQL requires comma's between column names and the question mark placeholders, and patient_id is not the first column to be written. The sqlColumns and sqlValues strings are seeded with "updated" and "?" before new columns are added.

Jonathan Anglin · Jun 30, 2021

As expected, tSQL looks like:

UPDATE table_name (updated,order_system_name,site_code,patient_id,patient_alternate_id,patient_last_name,patient_first_name,patient_middle_name,patient_dob,patient_sex,patient_address_1,patient_address_2,patient_city,patient_state,patient_zip,patient_account_number,patient_ssn,visit_class,visit_unit,visit_facility,attending_doctor_id,attending_doctor_last_name,attending_doctor_first_name,referring_doctor_id,referring_doctor_last_name,referring_doctor_first_name,consulting_doctor_id,consulting_doctor_last_name,consulting_doctor_first_name,admitting_doctor_id,admitting_doctor_last_name,admitting_doctor_first_name,visit_number,admit_datetime,discharge_datetime,accession_number,status_code,order_completed_datetime,placer_order_number,filler_order_number,procedure_code,procedure_description,ordering_doctor_id,ordering_doctor_last_name,ordering_doctor_first_name,procedure_modality,order_priority,reason_for_exam) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) WHERE accession_number='123456789'