[Jim Kosloskey]

You may be correct, I have not tried that. Can you cite the authority for the global variables not being used for DB Handles?

The User Doc I see with 2209 indicates string content and does not specifically preclude object handles (but that does not mean they are not precluded). Have you tried using the global variables for the handle associated with the DB connection?

I guess as an alternative, and depending on the detail of your architecture, you could limit the connects somewhat by using the Tcl global variables at thread start. So, for those threads there would only be one open at start (more if there is an intervening drop of connection). This assumes each thread has a finite Table set it addresses, and a single connection can be effective.

Assume messages get directed in a site to multiple destinations and each destination addresses the management of a given DB Table within the thread. A single connect at the Start Context would be made and then used for as many messages traverse that destination thread. If there then 5 destinations, there would be 5 Connections for however many messages traverse instead of a connection for each message. Not what you expressed you wanted, but I think has the potential to reduce significantly the number of connections to the DB.

The above is purely speculation on my part not intimately knowing your architecture and not being familiar with the odbc package you are using. But, based on my experience with the Data Direct and assuming a like architecture the above should work.

 

 

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

The one I am referring to is the Cloverleaf Global Variables with the Tcl commands that begin with gv(damn I wish we could paste screen shots here) such as gvgetvar and gvsetvar with variable names beginning with $$. The Cloverleaf help has a lot of documentation on this feature – are you referring to that?

What release of Cloverleaf are you using? I am not sure what release CGV feature was introduced.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

I think what Rob was referring to is the Cloverleaf Global Variables not a Tcl Global Variable.

Look up ‘global variables’ in the Cloverleaf doc. Just read it carefully as there are ‘Virtual’ or temporary Cloverleaf Variables and ‘fixed’ Global Variables (my terms) and you need to make sure you get the variable set the correct way. I scratched my head over the use of these variables for a while before I figured that out. I think I posted somewhere on this forum I think there is a bug in the way the variables can be handled. I do not think that should impact you if you study the doc well.

What I see you using is the Tcl global which I don’t think is persistent enough for what you need.

 

 

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

It appears the dblookup command does not function exactly the same way the Xlate uses the lookup. Perhaps this is a bug.

When I use a selection SQL to look for a subset of data in the DB Lookup Configuration, it functions properly in the Xlate, but when I use dblookup from Tcl I get the entire dataset not the subset – no matter what I do.

So, Rick, I think if you want to use dblookup and the DB Lookup Table, you need to prepare to receive the entire dataset (using -maxrow potentially with -maxcolumnname then work the returned entire data set as a list – OR – use the Xlate to get the subset you want and ITERATE to work through that subset.

Peter – have you tried using dblookup against your example to see if you get the same result as in the Xlate?

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

It does not appear you are using the Data Direct Drivers. The DB Protocol use JDBC drivers which are not the same. The Data Dirext Drivers are optional from INFOR (extra money). They are very fast and have additional functionality beyond what is normally provided by most odbc (and even the JDBC) drivers. I am not familiar with the tdbc package so I cannot comment on its potential. I can comment on the Data Direct drivers which I used before Cloverleaf and with Cloverleaf. I know I could dynamically define parameter sets for Stored Procedures using the Data Direct Drivers, but I am not sure whether that can be accomplished with the tdbc package. Based on how the Data Direct Drivers work in that regard, I think the technique is not dependent on the drivers – but I could be wrong.

I now better understand your situation wherein you have a single DB with multiple Tables and you want one connection to the DB then use that connection multiple times in different threads. I did not do that with the Data Direct Drivers. Perhaps what Rob described wherein the handle of the Connection is stored in a Global Variable may be your solution – assuming the tdbc package does not bind the handle to the instance and it can be shared across instances. I see challenges in handling DB issues which may be detected by each thread/route using this global approach, but I do not think the challenges are insurmountable. For example, if there is a DB issue experienced by one of the thread/route and the DB needs to be disconnected, how would that be communicated reliably to the other thread/routes so they do not choke trying to reference the DB? I am sure there could be other challenges. Perhaps Rob and his team solved those challenges – or maybe they do not exist. In any case, I would spend some time thinking that through.

I have not used the DB protocol for invoking a Stored Procedure but I have used the DB Lookup with Stored Procedure and I was able to define the parameters for each lookup. I suspect the technique is similar for the protocol but that is somewhat static in that each protocol instance has its set of parameters, I think. So, if you want to have a single DB protocol thread to be used with multiple Stored Procedures each having their own parameter set, I am not sure that can be done. However, if each interaction with the DB is to be done in its own thread, then I think you should be able to configure the parameter set appropriately.

I do not have a DB I can play around with (certainly not MSSQL) to exercise the potentials. Otherwise, I would do some experimentation to see what can be accomplished.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

That is the way I have done it primarily because that is the way I prefer. I negotiate an exchange protocol with the author of the Stored Procedure (I don’t write it – the owner of the DB does) as to what parameters will be exchanged and what they are as well as what is to be accomplished. I don’t care how the author accomplishes the goal.

But it looks like Peter Heggie has provided a solution to populating your ‘like’ statement – does that not work?

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

Oh – I missed that.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

I am not sure what you mean by the ‘like statement’.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

Are you using the Data Direct ODBC Drivers as distributed with Cloverleaf as an option?

If so, I have successfully dynamically passed parameters to MSSQL (and Oracle for that matter) Stored Procedures. There are considerations to be made but it can be done.

I thought you could only have one instance of the DD Drivers in one process but reference the drivers multiple time from different threads (different Tcl procs) – if that is what you are attempting. In the case where I did this though we used multiple sites although I think I did it in one site for testing. I used one generic Tcl proc though used multiple times – but I do not think that should make a difference.

The most consumptive action one can do with any I/O file or DB is to Open and, in some cases, Close. This is because a lot of overhead is expended preparing for what is anticipated to be multiple reads or writes, making the performance of the reads and writes better.

Besides what you configure in your odbc.ini file for one, persistent connection, I believe something has to be done on the DB side as well, but I do not recall. In my case we used persistent Connection specification, and it worked well. Or5 are you saying you have multiple threads accessing the same DB and you only want one connection?

Bottom line, I think this should work for you and I don’t think you need an architectural change as long as all of the ODBC work is Data Direct and in one process in the site. Otherwise, you might be well served to re-analyze the site architecture with DB interaction in mind.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

I think the primary issue is your IN Value in your Table lookup configuration. You have @value. What is wanted here is the name of the field in the DB (probably result set – use the ellipses button to see what is available). This is the field that will be matched with some value.

I have a case where I have a month name and number table with column names “name and number”. For my Table Lookup my In value is “name”. If I wanted to look up based on number, I would need another configuration using “number” as the In value.

In addition, I think your dblookup as coded will only return one tuple. If that is what you want, fine, but I think you want multiple tuples. In that case you need to use the -maxrow switch. If you use that switch and do not provide a value ALL tuples will be returned.

However, what is returned is a string with each column separated by a comma and each tuple terminated with hex 0d0a. In my mind that is not the best data to work with. Give a table with abbreviated Month name and number ( like this Jan 01, etc.) executing :

dblookup -maxrow month_all_rows.tbl “Jan” I get back this:

Jan,01
Feb,02
Mar,03
Apr,04
May,05
Jun,06
Jul,07
Aug,08
Sep,09
Oct,10
Nov,11
Dec,12

Which in hex looks like this:

hcitcl>oth_data2hex $stuff
0000000: 4a61 6e2c 3031 0d0a 4665 622c 3032 0d0a Jan,01..Feb,02..
0000010: 4d61 722c 3033 0d0a 4170 722c 3034 0d0a Mar,03..Apr,04..
0000020: 4d61 792c 3035 0d0a 4a75 6e2c 3036 0d0a May,05..Jun,06..
0000030: 4a75 6c2c 3037 0d0a 4175 672c 3038 0d0a Jul,07..Aug,08..
0000040: 5365 702c 3039 0d0a 4f63 742c 3130 0d0a Sep,09..Oct,10..
0000050: 4e6f 762c 3131 0d0a 4465 632c 3132 Nov,11..Dec,12

I find using the -metacolumname switch gives me what I consider a much easier to work with keyed list like this:

{RSMETACOLUMNNAME {{name} {number}}} {RSDATA {{{Jan} {01}} {{Feb} {02}} {{Mar} {03}} {{Apr} {04}} {{May} {05}} {{Jun} {06}} {{Jul} {07}} {{Aug} {08}} {{Sep} {09}} {{Oct} {10}} {{Nov} {11}} {{Dec} {12}}}}

The RSDATA Key has the data values as a list of lists, the RSMETACOLUMNNAME  key tells me which order the columns are in and what their names from the DB are.

By the way, if you are unilaterally returning multiple tuples (using * in your SQL), the value you provide (in my case “Jan”) does not matter, you are going to get the entire result set. I am not sure you can control where the retrieval from the DB begins – perhaps with a specific SQL Statement or Stored Procedure – I have not experimented with that.

So, if the maximum number of tuples returned turns out to be too large and you want to break the retrieval up into more manageable chunks, I am not sure if you can do that or how.

 

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

OK I have done something similar in my dynamic table lookup proc. I am fairly busy this AM but will take a closer look after lunch.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

More information needed. How are you using the DB (lookup, protocol)? Share as much information as you can even as an attachment.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

If you do the Tcl lookup command for DB Lookup table and the proper SQL or Stored Procedure code is issued then you can get multiple values returned. Also, if the Lookup has the proper SQL Statements or Stored Procedure you can get multiple values returned in an Xlate.

The values are returned as a list and then can be ITERATEd over (Xlate) but depending on the DB, the list could be quite large.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

I was referring to the DB Protocol not DB Lookup. So, you would use the DB Protocol (with either SQL Statements or Stored Procedure – my preference is SP) to extract the elements of interest from the DB; then using Tcl (or perhaps an Xlate) you would do a lookup of each returned element from the Lookup Table doing whatever you want when there is no match or if there is a match.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Jim Kosloskey]

It all depends on what the DB requires for a Password. If you knew the encryption techniques used I suppose you could try to decrypt it.

If you are determined to do a compare and the .ini file does not work, maybe using the DB Protocol to extract the DB information then some Tcl to do a compare to the Table might work.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Author]

Replies