go to post 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: Dates are stored in UTC, so use "GETUTCDATE" when trying to limit the search parameters by time. 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 :FullMessageFROM Ens.MessageHeader head, EnsLib_HL7.SearchTable, EnsLib_HL7.MessageWHERE 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.DocIdAND EnsLib_HL7.SearchTable.PropId = 6AND EnsLib_HL7.SearchTable.PropValue = :placerOrderNumAND head.MessageBodyClassName='EnsLib.HL7.Message'AND head.MessageBodyId=EnsLib_HL7.Message.%ID)ORDER BY head.ID Desc
go to post 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.
go to post 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=?
go to post 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.
go to post 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.
go to post 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.
go to post 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.
go to post 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.
go to post 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.
go to post 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'