[Jim Kosloskey]

One way I can think of is to route the messages to the OB side of the thread (or another sftp thread) pointing the OB side to the directory where you want the archive/backup to exist. I think the metadata will contain the file name but you may need to provide the directory path.

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

[Jim Kosloskey]

John,

Upon investigation and in keeping with your observation, it appears like the Stage DB Tcl API (stageinit and stagegetkeys for example) is not provided even though it is documented.

In reality the protocol fairly cries for a Gui tool to browse the Stage DB (like for SMAT DB or the DB Admin). I was hoping to use the documented Stage DB Tcl API commands to provide a command line utility or set of utilities to expose the Stage DB for analysis and debugging.

Apparently, that is not possible in a release as late as 2209. Maybe 2205 has provided the API that is documented.

I too like the DTC protocol and have used it once. I feel it needs to be polished however and have made suggestions. Time will tell if it gets more polished.

In my opinion, it has some real limitations for usability for message enrichment in all potential scenarios. But to truly make orchestration and enrichment fully functional I think the Xlate will need to be modified as well to allow the definition of multiple IB messages to accomplish a merge function.

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

[Jim Kosloskey]

John,

Thanks for the info. I will investigate. If I have any additional questions, would you be willing to commiserate off-line? If so, just send me an email.

 

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

[Jim Kosloskey]

I am just musing out loud here, but I wonder if that is not a copy of the elog with a person’s initials (-wal). Is that possible?

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

[Jim Kosloskey]

If this is for an Xlate, I have a reusable proc intended for this purpose. Arguments provide the offset desired among other things (like resultant format). If you would like a copy of that proc, email me.

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

[Jim Kosloskey]

Hopefully, the sending system can provide a valid XML Schema. With that, you can compile the XML message and use that structure in an Xlate (along with the HL/7 needed).

Cloverleaf can reference the file via the Fileset/FTP protocol if needed (that would have a port involved). The exchange is not ‘real time’ however, it is still file-based and will involve reads.

If there is no schema, you could use 3rd party tools to create a schema from the XML but that is not very reliable. You could also use Tcl to parse the XML yourself – not something I have done.

 

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

[Jim Kosloskey]

You will need to use Tcl. In your Tcl code issue the gvsetvar command (search for gvsetvar in the User Doc). As I understand it the variable can be in-memory or be stored in a .ini file. Using gvsetvar only sets the in-memory value. If you would like the value to be more permanent, then I think you also need to do a gvsave (but be aware that saves ALL in-memory Global Variables to the .ini file).

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

[Jim Kosloskey]

Sorry, I mis-read the message. It is PID-3.5 but if you want the subcomponents the method remains the same.

I see in the first message there are repeating fields (missed that – eyes getting old). Is that what you want to know how to get to?

If so, I see 2 choices:

1. use the field repetition position in the address Path like this: …PID#3.(0) and PID#3.(1).
2. Use the ITERATE Action to work through the repetitions.

I do not think there is a need for Tcl here.

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

[Jim Kosloskey]

For the PID-3 issue, there is a subcomponent expressed (the & is the standard HL/7 subcomponent separator). Assuming those are there because there really are subcomponents and not erroneously placed text, one would use subcomponent notation to get at those items. It appears the subcomponents are to the 4th component so your copy would look something like this: …PID#3.(0).[5][0] and PID#3.(0).[5].[1].

However, PID-3.6 is usually Mother’s Maiden Name and so I am not sure those are not ampersands erroneously entered into the text rather than subcomponents. If that is the case, the source system should have escaped them like this: \T\ (assuming \ is what is specified in MSH-2 escape character – \ is the standard).

As for the message structure, I am not sure I fully understand your question but if you want the standard structure for a ive HL/7 2 version Message/Event Type, use the HL/7 Configurator. That defaults to the standard message structure.

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

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

[Author]

Replies