DB Query within Translation

[Robbie Parker]

I am now for the first time needing to query an outside SQL DB in real-time, and curious where/how to start.  I would like to do within tcl if at all possible.  

The need is to query an EPI system where it stores (2) enterprise IDs [ID1 ID2 for simplicity here] along with various facilites’ med rec numbers.

Example:  the receiving system generates its’ own ID, here ID2.  When the sender sends out data, ID1 is always known.  I need to check to see if ID2 exists based on ID1 similar to:

USE EPI

SELECT ID2 WHERE ID1 = ‘12345’

IF query returns null, we send PID-4 as null, and the receiving system generates the ID value.  If the query returns the ID2 value, I plug that in to PID-4, and recieving systems gets what it needs and *doesn’t* produce a duplicate chart.

Any help would be appreciated.   Thanks, -robbie, ARHS IT

Topic

[Robbie Parker]

My project’s interface resource for vendor =Allscripts took this on for me and worked it out with their native engine, so I don’t have the immediate need, but if anyone has a template for this scenario any advice would be helpful for future considerations.  Thanks, -robbie

[Jim Kosloskey]

personally I find this activity to be potentially problematic without easy answers.

If you would like to chat off line regarding my cautions and observations email me.

Years ago I spent a fair amount of time evaluating not necessarily how to do this technically but rather what are the implications and potential downfalls.

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

[Rob Lindsey]

There is one major issue with trying to do a DB call with a message in flight.  What if the DB is not available.  You have to plan for every single contingency.  

We have done this and we do not like it because of the issue that I mentioned above.  They take down the DB system for up to 12 hours.  We have to program in a queue and checks to the DB and a whole bunch of other things.

Yes it can be done in TCL and if you have CL version 6.1 and above there is the  dblookup   command.  You will need to setup a Table that the Cloverleaf system will reference via the Advanced Table lookup.

[Jeff Dinsmore]

Rob is right that you need to program around the DB being unavailable.

If you have a DB that may be down for hours, I’d say that would be a bad choice.

If, on the other hand, the DB is a mission-critical system that’s going to be available all the time, I wouldn’t hesitate to query on-the-fly.

You still need to handle the DB unavailable situation, but that should be a rare case.

One DB wait method we’ve used is to queue messages into a SQLite DB locally when the remote DB is unavailable, then process them when the DB is back online.  Almost always, the messages process realtime, but queue up when the remote DB is offline.

For each message that passes through the interface, you need to process any deferred messages first (in the order they were received), then process the current one.

Jeff Dinsmore
Chesapeake Regional Healthcare

[Dustin Sayes]

Robbie,

I would follow the advise of Jim, Rob and Jeff – they are well seasoned in our field and they know what they are talking about.

Max created a post with good info on SQL. Here is the link and it is more than enough information to get you started on working with SQL and cloverleaf.

https://usspvlclovertch2.infor.com/viewtopic.php?t=6686

You may be able to negotiate some of the drawbacks of querying external sql server if you can somehow get your data local to your CL Server, possibly in sqlite.

[Author]

Replies