Full message in SQL database

[Jason Puskar]

Hello,

    I am having issue with writing a transaction to an SQL database.  What I need to do is to pull some information out (MRN, Order number

Topic

[Jim Kosloskey]

I don’t think this is possible to do without some Tcl.

You might be able to minimize and generalze the Tcl so that it is more obvious what is happening based on how you approach the problem.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

Have you considered using linking on the DB side such that you send 2 messages, one is the entire HL/7 message and one is the individual fields. Then have them related to each other on the DB side?

You would need a key common to both messages I would think.

Still might need some Tcl.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jason Puskar]

Jim,

   I did think of the two transactions, but what would be the field I could use?  If I can only send the full message in a bulk copy, I”m still not sure how to pull out specific fields.  Is there a way to pull fields out of the bulk copy to send?

Jay

[Jim Kosloskey]

My first candidate would be the MSH-10 (Control ID) assuming it is unique.

How are you invoking the SP? Are you using the DB Protocol or the DB Driver addon?

If the Protocol, I don’t know if you can do anything on the full message.

The SP on the other hand should be able to easily get to the MSH-10 in the full message and you can add that field to your individual field message.

Then I suspect the SP should be able to establish the linkage.

Just some thoughts off the top of my head.

If you use some Tcl, the above may or may not be necessary.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Levy Lazarre]

Hi Jason,

I think you may be missing some steps in your setup since what you are trying to achieve can be done entirely in the Cloverleaf GUI, without any Tcl. So I will share with you the steps I took to insert a number of fields from an HL7 message + the whole message into a SQL Server database table.

1. You already have your database connection configured. The next step is to create a database schema (where you can specify the table and fields involved in your integration). Basically, this serves to create a VRL that will be used on the output side of your Xlate.

Tools —> Configuration —> Database Configurator…

Please refer to sample picture “database_schema.png” and notice the different fields that will be inserted in the database table named “Patients”. In particular, field “hl7msg” is intended to receive the whole HL7 message, so its datatype in the SQL table is “nvarchar” to be able to hold a ‘blob’. Also notice that field “ID” is of type “int identity” because it is actually a unique ID that will be returned to us by SQL Server when we insert the patient in the database.

2. In the outbound thread, you must properly configure the Database Outbound Protocol Properties. Refer to sample picture “database_outbound_protocol.png”. You must check the box labeled “Use cached pre-xlate message as whole message” in order to make the whole message available in the Xlate. Also notice in the Stored Procedure that the whole message must be referred to as . There is also an output parameter “@patdbID” to receive the unique ID that SQL Server will return to us as the result of the patient insertion in the database.

3. Configure the Xlate to use the database schema created in step 1. See sample picture “xlate_configuration.png”

4. Finally the Xlate itself where you map fields from the inbound HL7 to fields of the outbound VLR. The naming convention on the output is TableName^FieldName”. Refer to sample picture “Xlate_hl7_to_sql.png” and notice how the is copied to field “hl7msg” as if it were a constant.

[Levy Lazarre]

Last attachment. I wasn’t able to post all at once.

[Jim Kosloskey]

Levy,

Very helpful.

Thanks for the posting. I will be saving this off.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Levy Lazarre]

Thanks, Jim

Your posts and insight have always been very helpful.

For the sake of completion, I enclose the sample table (Patients) creation script and the sample stored proc. My test DB name is HL7Test.

Code:

USE [HL7Test]
GO

/****** Object:  Table [dbo].[Patients]    Script Date: 10/3/2018 1:16:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Patients](
[ID] [int] IDENTITY(1,1) NOT NULL,
[acct_num] [nvarchar](50) NULL,
[admit_date] [nvarchar](50) NULL,
[bed] [nvarchar](50) NULL,
[date_time] [nvarchar](50) NULL,
[discharge_date] [nvarchar](50) NULL,
[dob] [nvarchar](50) NULL,
[fname] [nvarchar](50) NULL,
[gender] [nvarchar](50) NULL,
[id_num] [nvarchar](50) NULL,
[lname] [nvarchar](50) NULL,
[location] [nvarchar](50) NULL,
[mname] [nvarchar](50) NULL,
[mrn] [nvarchar](50) NULL,
[room] [nvarchar](50) NULL,
[ssn] [nvarchar](50) NULL,
[visit_num] [nvarchar](50) NULL,
[hl7msg] [nvarchar](max) NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Code:

USE [HL7Test]
GO

/****** Object:  StoredProcedure [dbo].[spInsertPatient]    Script Date: 10/3/2018 1:16:52 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spInsertPatient]
         
          @acct_num nvarchar(50)       = NULL,
          @admit_date nvarchar(50)     = NULL,
          @bed nvarchar(50)            = NULL,
          @date_time nvarchar(50)      = NULL,
          @discharge_date nvarchar(50) = NULL,
          @dob nvarchar(50)            = NULL,
          @fname nvarchar(50)          = NULL,
          @gender nvarchar(50)         = NULL,
          @id_num nvarchar(50)         = NULL,
          @lname nvarchar(50)          = NULL,
          @location nvarchar(50)       = NULL,
          @mname nvarchar(50)          = NULL,
          @mrn nvarchar(50)            = NULL,
          @room nvarchar(50)           = NULL,
          @ssn nvarchar(50)            = NULL,
          @visit_num nvarchar(50)      = NULL,
          @hl7msg nvarchar(max)        = NULL,
          @patsqlID  nchar(10) OUTPUT
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

INSERT INTO [dbo].[Patients]
(
 acct_num,
 admit_date,
 bed,
 date_time,
 discharge_date,
 dob,
 fname,
 gender,
 id_num,
 lname,
 location,
 mname,
 mrn,
 room,
 ssn,
 visit_num,
 hl7msg
)          

VALUES
(
@acct_num,
@admit_date,
@bed,
@date_time,
@discharge_date,
@dob,
@fname,
@gender,
@id_num,
@lname,
@location,
@mname,
@mrn,
@room,
@ssn,
@visit_num,
@hl7msg
)    

/*Move the identity value from the newly inserted record into
 our output variable */
 
 SELECT @patsqlID = @@IDENTITY
END

GO

[Jason Puskar]

Hello,

    Thank you both for the help!!!  I see now how I could have used the in the xlate would have worked now.  I tried a few different versions of using that prior to the update you gave, but not successfully.  I am going to keep this reference for the next one I may need to do.

    I ended up taking the advice of doing it in a stored procedure.  First I did a bulkcopy to get the entire message.  I then pulled the fields that I needed separately and stored them in a Z segment (ZFM), passing this all over to a procedure.  I then created the stored procedure to pull the values from the Z segment, from the full message, and stored them all in the database.  

Thanks,

Jay

[Author]

Replies