Help with database-outbound <whole_message>

[Aaron Andersen]

Hello,

I am trying to figure out how to use the database-outbound protocol but I’m having an issue with inserting an entire HL7 transaction into a single column. I have configured my SQL Statement as follows:

Code:

INSERT INTO INTERFACEMESSAGE(INTERFACEMESSAGE.COMPNO, INTERFACEMESSAGE.MESSAGEID, INTERFACEMESSAGE.MESSAGE, INTERFACEMESSAGE.ENTDATE, INTERFACEMESSAGE.MESSAGETYPE) VALUES (, , , , )

In the Xlate I don’t touch the INTERFACEMESSAGE.MESSAGE field (the field whereI want the entire HL7 transaction to go into) but I notice that if I print out the the message in a post-xlate tcl file the HL7 transaction is not in that field, it is empty:

Code:

‘1’,’Q1571282944T27837740′,,sysdate,’HL7IN’

Could anyone offer any advice on what I’m doing wrong with my usage of ?

Thank you,

Aaron Andersen

Topic

[Mike Strout]

First off, let me say that I find the outbound database protocol extremely frustrating and a pain to troubleshoot. That said, here are a few tips I have learned…

1. Open your database schema and then for each table, select it and then  change the delimiter in Options>Table Schema Options to something other than a “,” as it isn’t uncommon to have a comma in an HL7 message. I usually use a # or %.

2. If you use the Cloverleaf “Configure” option to have Cloverleaf build the SQL insert statement, it adds the table name to the each of the fields in the field list. I have found that this breaks SQLite inserts. Here is an example…

Insert into table(table.field1, table.field2) values(1,2)

Sqlite needs the following…

Insert into table(field1, field2) values(1,2)

3. If you are having issues with the Testing Tool working, but the insert failing when you run messages through the tread, pop out to the command line and do an hciss -k h and then hciss -s h. Apparently some database configurations are held in the host server memory.

4. Disallowed Gateway almost always means there is an error in your SQL statement. Sadly, the only way I have found to see what SQL statement is being executed is trial and error. When you fix it, then and only then will it display in the testing tool. This is super frustrating.

[Author]

Replies