No announcement yet.

Sourcing SQLServer2005

  • Filter
  • Time
  • Show
Clear All
new posts

  • Sourcing SQLServer2005


    I posted this in the general forum, but probably should have been in the support forum

    I have just started working with Mirth and enjoying it so far.

    I have a few question about Sourcing SQLServer2005 data.

    1. Does the sql server/sybase driver support TEXT data types? I attempted to use the following SQL in my channel source:

    SELECT MessageID, -- GUID
    MessageType, -- INT
    MessageSent, -- INT
    MessageContent -- TEXT
    FROM MessageTable

    Which did not work and logged errors in the event log ('Failed to transform message before applying the filter'). I discovered that by removing the MessageContent field from the SQL the problem went away. Is it possible to include TEXT data field types in the SQL statement.

    2. Following on from this, the MessageContent field in my database table is XML data (allowing for flexible message content). With a bit of experimenting, I managed to tweak the channel SQL to extract some of this content:

    SELECT MessageID,
    cast(m.MessageContent as xml).value('(//Patient/@PatientID)[1]', 'int') as PatientID,
    cast(m.MessageContent as xml).value('(//Patient/@Given)[1]', 'varchar(30)') as Given,
    cast(m.MessageContent as xml).value('(//Patient/@Surname)[1]', 'varchar(50)') as Surname
    FROM audit4_testing.eclipse.ORCA_MESSAGE_table_OUTBOUND as m
    WHERE MessageType=0 and MessageSent=0

    Now provided you enjoy the xpath and the xml capabilities of SQLServer2005+ this works great, but I was wondering if there is a more direct way to feed the XML content from the MessageContent data field into the channel?

    Any suggestions and comments would be greatly appreciated.

    (I am using version 1.7 of Mirth)


    Tim Freeman