FHIR Chat · dependancies on sql server · dotnet

Stream: dotnet

Topic: dependancies on sql server

view this post on Zulip Marc Riding (Jan 25 2019 at 13:51):

Hi Guys,

I have build a fhir converter using the .net framework.

Works fine when I run locally as a windows forms application but I'm having trouble deploying to an SQL server.

When I try to register the assemblies to sqlDb, I am running into dependency issues. When trying to register hl7.fhir.STU3.Core, I get the following error msg:
Assembly 'Hl7.Fhir.STU3.Core' references assembly 'hl7.fhir.support, version=0.96.1.0, culture=neutral, publickeytoken=d706911480550fc3.', which is not present in the current database.

But I cant find hl7.fhir.support version 0.96.1.0 dll anywhere (online). I have the 0.96.0, and now have got the 1.0 version, but it appears that the Hl7.Fhir.STU3.Core.dll (version 1.0) is trying to reference the support dll version 0.96.1.0 which I don't have/cant find.

Any Ideas ?

view this post on Zulip Marc Riding (Jan 25 2019 at 14:35):

I'm now thinking the Hl7.Fhir.STU3.Core dll is referencing TWO versions of hl7.fhir.support.dll

  • I have tried using the beta version (1.1) and registering it on sql db and I get a message telling me :
    Assembly 'Hl7.Fhir.STU3.Core' references assembly 'hl7.fhir.support, version=1.1.0.0
  • However when I place this support dll into the same directory, the error changes to:
    Assembly 'Hl7.Fhir.STU3.Core' references assembly 'hl7.fhir.support, version=1.0.0.0 ....... which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch)

I'm running out of ideas

view this post on Zulip Marc Riding (Jan 25 2019 at 15:34):

I think I have solved it but I've somehow killed the sql server I was deploying to. I will post back with an update if successful

view this post on Zulip Marc Riding (Jan 27 2019 at 21:11):

UPDATE:

Ok so I got it working (btw it was a colleague of mine who took the test server down not the dll/assemblies :-) ) I will post my solution below in case anyone else finds it useful.

...
...
...

The referencing error were confusing and possibly a result of too many versions of the libraries existing on the server. Upgrading to version 1.0 across the board, clearing out the sql server fhir dll's, and starting again was my solution in the end.

  • I removed all the libraries that I had already created sql assemblies for like so:
DROP ASSEMBLY [Hl7.Fhir.STU3.Core.dll] DROP ASSEMBLY  IF EXISTS   [Hl7.Fhir.STU3.Core.dll] WITH NO DEPENDENTS
DROP ASSEMBLY [Hl7.Fhir.ElementModel.dll] DROP ASSEMBLY  IF EXISTS   [Hl7.Fhir.ElementModel.dll] WITH NO DEPENDENTS
DROP ASSEMBLY [hl7.fhir.support.dll] DROP ASSEMBLY  IF EXISTS   [hl7.fhir.support.dll] WITH NO DEPENDENTS
DROP ASSEMBLY [Hl7.Fhir.ElementModel.dll] DROP ASSEMBLY  IF EXISTS   [Hl7.Fhir.ElementModel.dll] WITH NO DEPENDENTS
DROP ASSEMBLY [Hl7.Fhir.Serialization] DROP ASSEMBLY  IF EXISTS   [Hl7.Fhir.Serialization] WITH NO DEPENDENTS
DROP ASSEMBLY [Hl7.FhirPath] DROP ASSEMBLY  IF EXISTS   [Hl7.FhirPath] WITH NO DEPENDENTS

  • I then put all the above dll's (now all version 1.0) into my dll folder I created ("c:\dll") on the server.

  • Ensured my own library source code referenced the 1.0 fhir dll's, then recompiled my library and placed it into the dll folder also.

  • Removed all other fhir dll's from the server.

  • Then I created all the fhir sql assemblies by running the below command for each dll:

CREATE ASSEMBLY [Hl7.Fhir.STU3.Core.dll] AUTHORIZATION dbo
FROM 'C:\dll\Hl7.Fhir.STU3.Core.dll' -- Replace with each dll
WITH PERMISSION_SET = UNSAFE

For some reason on the test server I was using, I had also to create assemblies for some core .net libraries:

CREATE ASSEMBLY [System.ComponentModel.DataAnnotations] AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.ComponentModel.DataAnnotations.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY [System.Runtime.Serialization.dll] AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Runtime.Serialization.dll'
WITH PERMISSION_SET = UNSAFE

CREATE ASSEMBLY [System.ValueTuple.dll] AUTHORIZATION dbo
FROM 'C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.ValueTuple.dll'
WITH PERMISSION_SET = UNSAFE

  • I then created my custom assembly (clib_Fhir_004) in the same way. My class lib (dll) is sitting in the same dll folder as the fhir ones, so sql server will look there first for referenced libraries. I had to add Newtonsoft.Json.dll in there to satisfy dependencies (this was in my vs project folder).

  • I wrapped the .net function in both a stored procedure with multiple return values, and in a simple udf function which returns xml (either valid fhir xml, or xml containing an error flag and a stack trace). Below is the code for the simple udf function:

CREATE FUNCTION udf_ConvertToFhir(@xmlIn xml) RETURNS xml
AS EXTERNAL NAME [clib_Fhir_004].[clib_Fhir_004.SQLProcs].ConvertToFhir

"clib_Fhir_004" is our custom assembly name, "SQLProcs" is the .net class name, and "ConvertToFhir" is the .net function name.

  • So now I have a SQL user-defined function that will accept our localy defined xml, and return a fhir xml bundle for transfer of care.

    I placed the following sql into the body of a trigger which fires when there is a row(s) inserted to the table.

    UPDATE [dbo].[FHIR_Messages]
    SET  [FhirXmlColumn] = [dbo].[udf_ConvertToFhir](i.[LocalXML])
    FROM Inserted i
    WHERE [dbo].[FHiR_Messages].[Message_ID] = i.[Message_ID]

...
...
...

The end result is that when a row is inserted into the FHIR_Messages table, the LocalXML column's value will be translated into fhir message bundle which will then be placed into the FhirXML column on the same row as the insert.

It took a bit of wrangling with the sql dataTypes in .net but is finally working :D

Last updated: Apr 12 2022 at 19:14 UTC


An HL7 Website -