Thanks for all your thoughts so far. The plot is thickening a bit and has been split into a few different issues.
First is the “Disallowed Gateway Routing” error. It is starting to look like this is the default error Cloverleaf throws when the JDBC driver throws errors like primary key constraint issues or syntax issues in the executed SQL statement. In my situation, we were getting duplicate order numbers on the vaccination feed from Epic, one for the vaccination administration and a second for the administration cancel. Converting the SQL statement from an INSERT to a REPLACE solved that issue, but there is still a lingering issue.
I have noticed that when Cloverleaf executes the REPLACE or previously the INSERT statement, it is truncating the hl7_msg field in which we pass the entire HL7 message. Interestingly, it isn’t truncating the end of the message, but instead, it is dropping the MSH segment and writing the PID and beyond to the DB. To troubleshoot the issue, I have done the following…
1. I used the testing tool to execute the tcl proc to generate the “message” that is ultimately execute by the OB DB connection. This message is a simple VRL with a datestamp, mrn, order number and the original source message.
2. Next, I used the Database Protocol in the testing tool to send the VRL message to the OB database thread. This is a pretty handy tool as it will verify that the SQL will succeed and then seemingly rolls back the transaction so the DB write never actually happens. If it fails, it gives pretty good troubleshooting info.
3. However, because it rolls back the transaction, I can’t really tell what gets written to the DB…did the message field get truncated.
4. To resolve this, I copied the “Executed SQL” displayed by the testing tool. I then opened the sqlite command line interface and pasted this executed SQL string. It threw an error because the datestamp wasn’t quoted, nor was HL7 message. When I single or double quote these two fields in the insert statement, the entire insert happens flawlessly.
5. Emboldened by my successful test, I thought I could just quote the necessary fields in the outbound DB connection properties and Bob would be my uncle.
6. I re-ran the Database Protocol test in did in #2 above with these tweaked SQL statement and I get a SEVERE: error stating “Array index out of range: 3”. I interpreted as the quotes make the interpreter think there are more values than fields.
As I typed all this up, I realized that executing the SQL from the Sqlite command lite is not a realistic test because it doesn’t use the JDBC driver which obviously must automatically quote date and string values based on some magic.
After all this testing (and this way too long Clovertech post), I find myself back at square one trying to figure out why the JDBC driver would consistently pre-truncate the HL7 message always at the same place, just before the PID segment.
Any ideas or suggestions?
.
