Using a SQL Procedure to "Preprocess" your data

 

Outbound Processor Properties Window

Outbound Processor Properties Window

 

Adding a SQL Stored Procedure to an Outbound Processor

Adding a SQL Stored Procedure to an Outbound Processor

 

Executing a Stored Procedure (Outbound)

You can optionally configure your outbound processors to execute a SQL Stored Procedure before it scans the <prefix>_HL7Data table in your schema for outbound messages to export. If this feature is enabled it will act in concert with the database polling interval you select when creating your outbound processor profile.

Example: If you have a stored procedure called CreateMyHL7Messages in your schema database which extracts data from YOUR MS SQL Tables and then populates HL7 message data in your UltraPort SQL Schema you can configure it here. If you also selected a database polling interval of 10 seconds your processor will behave like so:

1.Every 10 seconds the processor will remotely execute the CreateMyHL7Messages (note that you can name it anything you like) stored procedure. If an error occurs it is logged. IMPORTANT NOTE: When executing your stored procedure the SQL Schema Engine will use a "Command Timeout" equal to FIVE (5) times the value of your Connection Timeout setting in your Schema Profile. This means that if your connection timeout property is set to 30 seconds your command timeout is 150 seconds. Your stored procedure MUST complete processing within that period or a timeout error will be returned to the Schema Engine.

2.THEN the outbound processor will scan the <prefix>_HL7Data table for outbound HL7 messages which are unprocessed and need to be assembled.

3.IF outbound messages are detected the processor will process them to completion by assembling and exporting the messages. It will then IMMEDIATELY and continuously repeat the process (return to item 1 above) until NO outbound messages are detected.

4.IF NO outbound messages are detected the processor will wait 10 seconds and then begin again.

Rules for your Stored Procedure: There are a few rules regarding your stored procedure, they are:

•The stored procedure must exist in your schema database and can be named whatever you like.

•The procedure must take NO parameters.

•Remember that the SQL or Domain user account used by the UltraPort MS SQL Schema Engine to connect to the database (see Database Schemas) must have the proper rights to EXECUTE the stored procedure (whether running locally or as a MS Windows Service).

 

Click to ExpandExample of a Stored Procedure (Click to Expand)

USE [YourDatabaseName]

GO

 

/****** Object:  StoredProcedure [dbo].[MakeOutBoundHL7Messages]    Script Date: 04/10/2013 08:23:21 ******/

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

-- =============================================

-- Author:      HermeTech Demo SP

-- Create date: <Create Date,,>

-- Description:   Example of a stored procedure

-- =============================================

CREATE PROCEDURE [dbo].[MakeOutBoundHL7Messages]

 

AS

BEGIN

   -- SET NOCOUNT ON added to prevent extra result sets from

   -- interfering with SELECT statements.

   SET NOCOUNT ON;

   -- @MyMessageID is the GUID we'll use as our Primary Key

   DECLARE @MyMessageID varchar(50) = (Select NEWID())

   -- @MyControlID is for MSH Field 10 (Message Control ID)

   -- Technically this is a no-no. MSH 10 should be a maximum of 20 characters

   -- BUT we're just doing it as an example so we'll do it anyway

   DECLARE @MyControlID Varchar(50) = (Select NEWID())

   -- Declare some variables to hold our data

   DECLARE @MyMessageType Varchar(10) = 'ADT'

   DECLARE @MyMessageEvent Varchar(10) = 'A08'

   DECLARE @MyMessageDate DateTime = (Select GETDATE())

    -- Step 1 Build the Message Manifest table

    -- We're doing a VERY simple message with 3 segments

    Insert Into EH1_MessageManifest 

    (MessageID,SegmentIDX,SegmentName)

    VALUES

    (@MyMessageID,1,'MSH')

    Insert Into EH1_MessageManifest 

    (MessageID,SegmentIDX,SegmentName)

    VALUES

    (@MyMessageID,2,'EVN')

    Insert Into EH1_MessageManifest 

    (MessageID,SegmentIDX,SegmentName)

    VALUES

    (@MyMessageID,3,'PID')

    -- Now we insert data into the segment data tables

    -- First the MSH table

    -- We're just inserting dummy data. The only dynamic thing is MSH_F10_C1 

    -- Where we're using the @MyControlID GUID we Created at the top of the 

    -- Procedure

    Insert Into EH1_Segment_MSH_A 

    (MessageID,IDX,MSH_F3_C1,MSH_F4_C1,MSH_F5_C1,MSH_F6_C1,MSH_F7_C1,MSH_F8_C1,MSH_F9_C1,MSH_F9_C2,MSH_F10_C1,MSH_F11_C1,MSH_F12_C1)

    VALUES

    (@MyMessageID,1,'MySendingApp','MySendingFacility','TheirApp','TheirFacility','20130315050000','NO SECURITY',@MyMessageType,@MyMessageEvent,@MyControlID,'T','2.3')

    -- Now we do the EVN table. Very Short

    Insert Into EH1_Segment_EVN_A

    (MessageID,IDX,EVN_F1_C1)

    VALUES

    (@MyMessageID,2,@MyMessageEvent)

    -- Now We Do the PID Table

    Insert Into EH1_Segment_PID_A

    (MessageID,IDX,PID_F1_C1,PID_F3_C1,PID_F5_C1,PID_F5_C2,PID_F7_C1,PID_F8_C1)

    VALUES

    (@MyMessageID,3,'1','ID12345','Escobar','Pablo','19641227','M')

    -- Now we've completely filled out our data elements for the message. All we need to 

    -- do is insert our new row into the main data table.

    -- THIS IS WHAT WILL TRIGGER THE OUTBOUND PROCESSOR TO DETECT THE MESSAGE.

    Insert Into EH1_HL7Data

    (MessageID,Inbound,Outbound,VendorName,VendorVersion,PartnerAPP,DateLoaded,LastLoaded,MsgType,MsgEvent,MsgControl,Loaded,SchemaLoaded,Processed,StatusMessage)

    VALUES

    (@MyMessageID,0,1,'Default','2.5.1',@MyMessageID,@MyMessageDate,@MyMessageDate,@MyMessageType,@MyMessageEvent,@MyControlID,1,1,0,'Created By SP')

 

END

 

GO