Online Help - Region 4: [CORE HL7 MSSQL] |
Below are some examples of common scenarios and exercises you might encounter or wish an explanation for.
Exercise: Create a CORE HL7 Microsoft SQL Schema using the Truncated HL7 Definition and then import HL7 messages using the COREHL7SQLImporter and evaluate the results where you will see many examples of Schema Training in action.
What Tools Would You Use?
•For the HL7 handling you would likely want to use the COREFolderAnalyzer object to monitor the folders, and the HL7DataAnalyzer object to load the data files and get HL7 Messages out. •Each process would need a HL7MSSQLController object which will create a COREHL7MSSQLSchema object. •Each process would then use the COREHL7MSSQLSchema object to create the COREHL7SQLImporter object which would import the HL7 messages into your schema.
Why is this tricky?
We can assume that you would have these processes load their settings from a file for things like their SQL Server Connector information and their CORE SQL Schema Properties so that both processes would be using the exact same settings. The tricky part comes from Schema Training which you should review thoroughly. As both processes are working simultaneously they MAY also be making dynamic changes to the database as they go and this could present conflict.
Example: In your Schema you have a segment data column PID_F5_C1 (the patients last name in the PID segment). When both processes start this column is a Varchar(20). Process 1, examines a message where PID_F5_C1 contains 21 characters. Process 1 will initiate a training cycle to ALTER the column to a Varchar(26). At the same time Process 2 examines a message where PID_F5_C1 contains 30 characters so it will initiate a training cycle to ALTER the column to Varchar(35).
The problem arises as to what happens if there is a conflict. Now IF both processes were created using the CORE HL7 MS SQL API there shouldn't be an issue. We have gone to great pains to make sure that IF Process 2 actually executes and is committed FIRST (changing it to a Varchar(35)), the Process 1 statement will gracefully do nothing (change it to a Varchar(26)).
But what if one of the processes is actually one of our other, older products like older versions of the UltraPort SQL Schema Engine. That process could be running and it doesn't have the graceful training transactions that this API uses? How can you insure that there's no conflict?
Recommended Solution: What we would recommend in this situation is that in YOUR solution you define a global int variable MYTRAININGRECORDCOUNT when your process starts and set it using the CountTrainingRecords() method of the COREHL7SQSchemaLInfo object (or it's subclasses COREHL7SQLImporter and COREHL7SQLMaintenance). Then BEFORE your COREHL7SQLImporter imports a HL7 message call CountTrainingRecords() again, and IF the value is different from MYTRAININGRECORDCOUNT do the following:
1.Set MYTRAININGRECORDCOUNT to the new value. 2.Call the RefreshScemaInfo() method in your COREHL7SQLImporter object. 3.Proceed with your call to ImportHL7Message().
How does this help?
When any of our products make dynamic changes to a HL7 Schema all SQL scripts executed are stored in the <prefix>_MessageErrors table. As your process imports HL7 messages it will likely also be training the Schema tables from time to time. This means that IF the value of MYTRAININGRECORDCOUNT has changed since the last time you updated it SOME process has performed a Schema Training operation and YOU need to call RefreshScemaInfo() to insure that your Schema Info isn't out of date.
 Schema Training Results
|
Scenario: •You need to create a system which contains 2 different processes (windows services). •Each process will monitor a folder on the file system for HL7 message data files. •As data files appear in the data folder the process needs to load all HL7 messages into the SAME SQL Schema
What Tools Would You Use?
•For the HL7 handling you would likely want to use the COREFolderAnalyzer object to monitor the folders, and the HL7DataAnalyzer object to load the data files and get HL7 Messages out. •Each process would need a HL7MSSQLController object which will create a COREHL7MSSQLSchema object. •Each process would then use the COREHL7MSSQLSchema object to create the COREHL7SQLImporter object which would import the HL7 messages into your schema.
Why is this tricky?
We can assume that you would have these processes load their settings from a file for things like their SQL Server Connector information and their CORE SQL Schema Properties so that both processes would be using the exact same settings. The tricky part comes from Schema Training which you should review thoroughly. As both processes are working simultaneously they MAY also be making dynamic changes to the database as they go and this could present conflict.
Example: In your Schema you have a segment data column PID_F5_C1 (the patients last name in the PID segment). When both processes start this column is a Varchar(20). Process 1, examines a message where PID_F5_C1 contains 21 characters. Process 1 will initiate a training cycle to ALTER the column to a Varchar(26). At the same time Process 2 examines a message where PID_F5_C1 contains 30 characters so it will initiate a training cycle to ALTER the column to Varchar(35).
The problem arises as to what happens if there is a conflict. Now IF both processes were created using the CORE HL7 MS SQL API there shouldn't be an issue. We have gone to great pains to make sure that IF Process 2 actually executes and is committed FIRST (changing it to a Varchar(35)), the Process 1 statement will gracefully do nothing (change it to a Varchar(26)).
But what if one of the processes is actually one of our other, older products like older versions of the UltraPort SQL Schema Engine. That process could be running and it doesn't have the graceful training transactions that this API uses? How can you insure that there's no conflict?
Recommended Solution: What we would recommend in this situation is that in YOUR solution you define a global int variable MYTRAININGRECORDCOUNT when your process starts and set it using the CountTrainingRecords() method of the COREHL7SQSchemaLInfo object (or it's subclasses COREHL7SQLImporter and COREHL7SQLMaintenance). Then BEFORE your COREHL7SQLImporter imports a HL7 message call CountTrainingRecords() again, and IF the value is different from MYTRAININGRECORDCOUNT do the following:
1.Set MYTRAININGRECORDCOUNT to the new value. 2.Call the RefreshScemaInfo() method in your COREHL7SQLImporter object. 3.Proceed with your call to ImportHL7Message().
How does this help?
When any of our products make dynamic changes to a HL7 Schema all SQL scripts executed are stored in the <prefix>_MessageErrors table. As your process imports HL7 messages it will likely also be training the Schema tables from time to time. This means that IF the value of MYTRAININGRECORDCOUNT has changed since the last time you updated it SOME process has performed a Schema Training operation and YOU need to call RefreshScemaInfo() to insure that your Schema Info isn't out of date.
Schema Training Results
|
