Inserting Patient Information into a Database

The route takes HL7 formatted messages and puts the appropriate information into two databases, storing patient and address information respectively. The route is composed of a TCP server communication point, an Execute JavaScript filter, a Database communication point and a Directory communication point.

The Database communication point takes the message and executes an SQL script using information contained in the HL7 message. Some patients have multiple addresses, all of which should be put into the Address table. This is achieved by specifying that the SQL statement should iterate over all addresses in the PatientAddress field of the PID (PID.PatientAddress).

In this scenario, the patient information database has a single entry for the patient's date of birth. However, the HL7 message contains the date of birth in multiple fields (year, month, day), causing us to process the date of birth before inserting into the database. An Execute Java Script component is used to add a property to the message which contains the date of birth in "datetime" format and allows the SQL statement in the Database configuration file to get this value and put it straight into the database.

Although we're using the database communication point to insert information into the database, we can also use it to pass the original message on for further processing by specifying the mode of the communication point. If the message was only to be inserted into the database, the database communication point mode would be set to Output. However, since the message should be moved to a directory once the message has been committed to the database, the communication point must be specified as Out->In mode.

Technically, this means the communication point will wait for a response message before inserting the next message. However, as we are communicating with a database, and only inserting data, the effect is that the original message is returned and can be passed on for additional processing. If the database communication point was selecting information, then the returned message would include the selected information and not the original message.

Database Insertion

The Database communication point inserts all required information by running a number of SQL statements:

• Insert Patient - Inserts the patient identifier, name, sex, and date of birth into the 'Patient' table. This statement is executed once for each message.
• Insert Address - Inserts the patient's address into the 'Address' table. This statement is executed once for each patient address in the message.
• Insert Next of Kin - Inserts the next of kin's name, sex, relationship, and phone number into the 'NextOfKin' table. This statement is executed once for each next of kin in the message.
• Insert Next of Kin Address - Inserts the next of kin's address into the 'NextOfKinAddress' table in the database. This statement is executed once for each address of each next of kin in the message.

Inserting the Patient Details

The insert patient statement is shown in the following diagram.