[JP Trosclair]

You can build database lookup tables using the lookup table configurator tool in the IDE. You can specify an input value as a parameter to a query. This works for fairly simple stuff. To get started:

• Go to Options -> Site Preferences and then the Database Configurations tab in the IDE. You can add a connection here to the SQL server using the JDBC MSSQL driver which is included.
• After that’s done, open the lookup table configurator and you can build a “Basic” or an “Advanced” database lookup per your needs.
• From there you can use the dblookup TCL command in your TCL procs to query data.

We use this setup for querying data from Clarity (Epic) on MS SQL. There was a bug with the keyed lists returned 6.1.2, it was resolved in 6.2.

 

[JP Trosclair]

Worth noting that we are in the process of upgrading to 6.2 and I tested dblookup using the latest point release 6.2.0.2P and the keyed list returned is fine; tested using hcitcl. Looks like it was just a bug in 6.1.2.0P.

The scheduling in 6.2.0.2P also works correctly for db protocol threads.

[JP Trosclair]

Ended up building a thread using the inbound database protocol to pull the data, convert it to a keyed list and write it out to a data file we can consume in procs needing the data provided by the database lookup table. More convoluted than I wanted, but it works. 🙂

For some reason when using Advanced Scheduling on the inbound database protocol the schedule shows disabled. You can enable it, but it just disables again. It doesn’t stop it from running when scheduled, that I’ve seen in testing, but it does prevent you from running it ad-hoc using the schedule_runnow command to the thread. Minor annoyance, as the thread can be reconfigured to make it run outside the normal schedule, just something I noticed.

[JP Trosclair]

I’ve played around with keylkeys and it doesn’t like the keyed list being returned either; gives the same error as noted in the first post. I’m in agreement with Jim that the syntax of the list being returned doesn’t look correct. Not sure if it’s a bug or if it’s a misunderstanding on my part.

If you don’t use the meta option with dblookup the field data returned isn’t going to play well with data that has commas in it so that really makes the meta option the only solution when returning two or more fields from the database. Not being able to use the meta option really throws a wrench in things; may end up just automating a script to pull the data into a flat file on a nightly basis.

[JP Trosclair]

This is the keyed list that dblookup -metacolumnname returns. It matches what’s in the documentation as well.

Example from the cloverleaf 6.1 docs:

Code:

hcitcl>dblookup -metacolumnname test.tbl key01 test1
{RSMETACOLUMNNAME {intFLD} {dateFLD} {flag} {strFLD} {ID}} {RSDATA {{1} {2014-04-01} {0} {test1} {key01}}}

Looking at it I think I’d expect the column names in RSMETACOLUMNNAME to be a list as well and it does look off.

And just for clarification, the docs do say this returns a keyed list:

dblookup command

dblookup ?-maxrow ?count?? ?-metacolumnname? table value ?value…?    

This looks up database data through a Database Lookup table based on the given value. The table is a Database Lookup file name; the file extension can be omitted.

Multiple input values are supported to match the In columns on the Database Lookup table one by one. If the value is not found, this command returns nothing.

[JP Trosclair]

This is good, but there’s a glaring race condition here if you’ve got a 3rd party system picking these messages up. Any of you guys run into it? Any solution? I’m looking at the temp file option in the fileset properties dialog now. The docs specifically say the temp file is renamed and not copied so I think this would solve the problem so long as it’s not writing the temp file in the same output directory.

[JP Trosclair]

Thanks guys, this gives me a lot to work with. I appreciate the information and time.

[JP Trosclair]

That’s indeed what I’m trying to do. So it’s obvious now that I’m new to this; where in the fileset configuration would you place such a proc? The outbound section for fileset-local doesn’t seem to have a place.

All the same, I appreciate the offer for the pre-written proc but I’m more interested in learning the ropes.

[JP Trosclair]

Thanks Jim. I’ve already got one for filtering the messages, I’ll work in there. Was hoping there was another option but that works.

Thanks again

[Author]

Replies