The best way to handle ever changing tables

Carl,

There is another option if you have ODBC or JDBC and that is to query a user maintained DB which contains the entries you need (we would invoke a Stored Procedure rather than imbedding the Sql code in the engine – but your choice). We have yet to do this but I know others have.

We infrequently allow dynamic tables but when forced to we have thus far used a generic, argument-driven Tcl proc and FTP to build a Cloverleaf Table from an extract file (delimited) provided by the system which is the system of truth for the data.

You just have to remember to have a mechanism for refreshing the cache (either purge caches or stop/start the process).

We use the third option which is that we don’t do dynamic look up tables.

We insist on the source or target handle translation tables.

A few years back this was the subject of my Power of Tcl presentation at the User Group – dealing with dynamic tables.

I have implemented this for a few clients and it works very well.

Most tables can be maintained externally in an Excel spreadsheet usually by a clerk or admin type that will take the load off the analyst.  The table is exported as a CSV file to a fixed file name and location any time it is updated

A Tcl proc checks the CSV file each time it runs and gets the last mod time.  If the last mod time has changed the CSV file is read into a global or namespace array.  If no change continue using the one in memory

It takes a *LOT* of the routine maintenance of the table out of the hands of the analyst.  ODBC, sqlite, and other methods, IMHO, still require a lot of work by the analyst.

Has anyone tried this using a web service?

We use sqlite as a short term store for data that needs to be cached and also instead of tables which need to be frequently updated.

I recalled doing this for one of our integrations ( batch_056 ) and used the method mentioned by Charlie of having the owner of the table do it in Excel and extract it to a CSV file that I used for generating the cloverleaf table on an automated daily basis.

I agree with Charlie overall it was an easy way to approach it as opposed to other methods, plus we already had a TCL script that would do the heavy lifting if I just gave it some arguments.

Here is an example of the arguments and how I called the table_builder.tcl script.

Here is the calling setup ( iftp_056_build_table.ksh ) to get you a tangible example to help solidify the concept and the input args to possibly consider.

If memory serves me right, I think I have a preprocessing step somewhere that might remove the header line from the CSV file, so keep that in mind if using a CSV approach.

That proc Russ is referring to has an argument to indicate how many beginning records in the file should be treated as headers (and thus ignored). So no need to pre-process.

There are two user points of control one for the inbound side and pne for the outbound side when building the Table. This allows for adaptation of the data elements ( for example if the provided values need to have trailing characters removed – or some other manipulation).

That proc actually builds a Cloverleaf Table file rather than an in memory array as Charlie indicated in the process Charlie is referring to.

So the purge caches thing is still needed with this proc. There is an intent to provide the ability to specify the process or threads to have caches purged for if I ever get the time to add that. So the whole process would be more automated and self-contained.

We have a need to use a proc like the one mentioned below, table_builder.tcl. Was this written by a client or was it from Cloverleaf ?Is this available for everyone to use and how do we get the code in our environment ? Thanks in advance for your help!

I wrote that proc and you can have it – just email me and I will reply to your email with the proc and user doc.

Just click on the email button on my signature.

Thank  you and I appreciate your quick response ! Just sent you an email.

This was the subject of one of my “Power of Tcl” presentations at the UG a few years back.

Basically, pretty simple.

Maintain the Table in an Excel spreadsheet and when it changes kick a copy out to a known location and file name as a CSV file

Then in the Tcl proc use the CSV package to split the records into fields.  Read into an array using the key as the array index.  Make sure the array is global or, preferably, a namespace variable.  Then stat the table and save last mod time in a global variable.  With each message stat the table and compare current mod time with the one saved.  If different, reload table else use table as is

I have implemented this in many places

Charlie and Jim,

Thank you very much for your help with this ! I was able to use the table_builder.tcl successfully. It was only a one time load and it was pretty easy.

thanks,

Parvathi