DB Query within Translation

Homepage Clovertech Forums Read Only Archives Cloverleaf Cloverleaf DB Query within Translation

  • Creator
    Topic
  • #55840
    Robbie Parker
    Participant

    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

Viewing 4 reply threads
  • Author
    Replies
    • #86660
      Robbie Parker
      Participant

      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

    • #86661
      Jim Kosloskey
      Participant

      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

    • #86662
      Rob Lindsey
      Participant

      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.

    • #86663
      Jeff Dinsmore
      Participant

      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

    • #86664
      Dustin Sayes
      Participant

      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.

      http://clovertech.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.

Viewing 4 reply threads
  • The forum ‘Cloverleaf’ is closed to new topics and replies.

Forum Statistics

Registered Users
5,053
Forums
28
Topics
9,236
Replies
34,175
Topic Tags
273