Dynamically updating a table

[Ujwal Parikh]

Hello,

I’m new to Clovertech and tcl as a whole.

There’s a used case in which I would like to dynamically update a table based on a value in a field of a HL7 message (ex: OBX-5).

The thing I’m trying to achieve is if the value in that field doesn’t match any of the existing values in the table, add that value to it. This way it will keep the table updated with the latest values and other threads can use the table with the most updated values.

I used tbllookup to look up an existing value in the table but is there a similar function to update the table?

Thank you for the help

Topic

[Jim Kosloskey]

With the standard Lookup Table, there is no provided function.

The .tbl file is a keyed list text file so theoretically one could construct XPM type Tcl code to change the file. However, the file is cached in memory and accessed from there by the engine. So one would need to purge caches (which does other things as well) to load the changed Table into memory – every time a change is made.

Maybe too much work to do during inflight Xlate if even possible to accomplish.

Later releases of Cloverleaf introduced SQL based Table with some functions for accessing from Xlate. Perhaps updating is possible there as well. Not sure in this case if the table is cached (I suspect not). If you are on one of those releases you could research whether the Table could be updated (via Stored Procedure perhaps).

One caution is you now become the trusted source of truth of the Table info. If the Table ends up wrong it is your issue. In my opinion it does not matter if the table is being ‘maintained’ via message flow. What about if there are entries in the Table which need to be deleted. Or the case where a table had an entry which needed correction. Maybe an early entry comes through with ‘pleeze’ in it (that ends up in the table) and a later message comes through with ‘please’ which is what is supposed to be in the Table. What you will end up with is 2 table entries – one of which is incorrect.

There are reasons lookup information should be maintained by a system of truth and not middle ware. HL/7 has transaction sets to allow a source system to send updates for ‘Master File’ information keeping Source and Destination systems in synch.

Above just my .02.

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

[Charlie Bursell]

Many years ago I presented a topic at the Cloverleaf User’s Group call “Building Dynamic Tables” as a case study from an implementation done at a customer site.

With the new DB table provisions provided in today’s Cloverleaf I am not sure how relevant it is today.  I, as well as Jim, have not looked into using the DB lookup as a dynamic table.

FWIW, here is the Tcl proc I presented way back in the 5.8 days 🙂

Attachments:

You must be logged in to view attached files.

[Peter Heggie]

We almost made a large investment in SQLite functionality, using Charlie’s proc and similar procs that could implement tables or update SQLite tables. Instead we purchased Data Integrator to gain ODBC functionality and we implemented a lot of logic using it. At the time the thought was that using a DBMS like SQL Server or Oracle would provide a lot of benefits, like allowing a business group to modify elements in tables they ultimately controlled anyway. But in six years that never happened. This year we converted to the new native SQL table functions. We had an immediate rise in deadlocks; we had to rewrite a lot of stored procedures to make them more efficient. Long story short, local tables are very attractive, for some very basic operational reasons. One aspect of using DBMS tables that always make me nervous is that Cloverleaf and the DBMS are on separate servers, making several of our interfaces vulnerable to DBMS outages, or even monthly recycles. Table maintenance and their data integrity are always top considerations when looking at new automation.

Peter Heggie

[Ujwal Parikh]

Thank you Jim, Charlie and Peter for your detailed inputs and explaining the pros and cons of updating with via HL7.

I haven’t worked with SQL based tables but will research on it now and see if they need to be purged also. It’s also good to know the dependencies of Cloverleaf and SQL servers in case we go down that route.

Also, thanks much for the tcl proc, I’ll probably use it as a work around.

 

 

• This reply was modified 4 years, 11 months ago by Ujwal Parikh.

[Rob Abbott]

You can also use the Cloverleaf API to update lookup tables in an automated fashion.  This is available in 6.2 and above.

 

Note that if you do update a local lookup table, you’ll need to execute the “purgex” command for each thread that is using the table.  Otherwise the threads won’t see the changes.

 

Rob Abbott
Cloverleaf Emeritus

[Ujwal Parikh]

We’re currently using 6.1.1.0 but eventually upgrading to 6.2 in the near future. Cloverleaf API looks promising for this use case and I’ll pass on the information. Thank you!

[Rob Abbott]

You should consider upgrading to 19.1 instead of 6.2.  This is the most current release.

Rob Abbott
Cloverleaf Emeritus

[Author]

Replies