dblookup keyed list

[JP Trosclair]

Hi Folks,

I’m working on using dblookup to pull data from SQL server. I’m able to get the data fine but I’m not having any luck pulling data out of the keyed list that it’s returning. Would appreciate a little assistance/guidance in what I’m doing wrong here.

Code:

set dept [dblookup -maxrow 1 -metacolumnname clarity_dep.tbl $deptId]

The result looks like this. The test, comma I was using to see how the non-meta return value behaved since it uses comma as its delimiter; doesn’t look like it will work. The meta option looks like the _only_ solution to deal with data that might have commas in it.

Code:

{RSMETACOLUMNNAME {department_id} {department_name} {dept_abbreviation} {external_name} {phone_number} {rev_loc_id} {serv_area_id} {test}} {RSDATA {{Department ID} {Record Name} {Abbreviation} {Friendly Name} {555-555-5555} {1} {1} {test, comma}}}

Trying to access the items in the keyedlist:

Code:

puts [keylsget dept RSMETACOLUMNNAME]

Yields:

Code:

keyed list entry must be a valid, 2 element list, got “RSMETACOLUMNNAME {department_id} {department_name} {dept_abbreviation} {external_name} {phone_number} {rev_loc_id} {serv_area_id} {test}”

I suspect the problem is with me and my understanding of keyedlists in TCL. The keyed list returned from dblookup looks like it should be fine to me and looks like it should work with the keylget call, but it’s not. I’ve been reading through various TCL keyed list references on the web and I’m stumped.

Would appreciate it if anyone has any advice on what I might be doing wrong here.

Topic

[Jim Kosloskey]

Your Keyed list is like this:

{RSMETACOLUMNNAME {department_id} {department_name} {dept_abbreviation} {external_name} {phone_number} {rev_loc_id} {serv_area_id} {test}} {RSDATA {{Department ID} {Record Name} {Abbreviation} {Friendly Name} {555-555-5555} {1} {1} {test, comma}}}

I think it needs to be like this:

{RSMETACOLUMNNAME {{department_id} {department_name} {dept_abbreviation} {external_name} {phone_number} {rev_loc_id} {serv_area_id} {test}}} {RSDATA {{Department ID} {Record Name} {Abbreviation} {Friendly Name} {555-555-5555} {1} {1} {test, comma}}}

Note the additional { in front of department_id and the additional } at the end of this keyed list (RSMETACOLUMNNAME). I think the second keyed list is OK.

Was this keyed list set created with a text editor or Tcl using keylset, etc?

Try creating a the keyed list using Tcl keyed list and list commands then compare the result to what you have.

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

[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.

[Keith McLeod]

Try following command on your result.

keylkeys dept

Does this return a key?

Then

keylget dept

What is returned?

Use the key commands when dealing with keyed lists.

Not sure but hope this helps. I have not tried the new table functionality yet, so looking to learn something here.

[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]

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]

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.

[Jim Kosloskey]

JP,

Thanks for following up that is worth noting.

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

[Author]

Replies