[Jim Kosloskey]

I have done this now and have produced a white paper on what was done. I will attempt to attach that white paper here. If that fails, and you want a copy or just want to discuss what I discovered, feel free to email me.

Attachments:

You must be logged in to view attached files.

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

[Jim Kosloskey]

Thank you for the hard work getting us back on-line. One really does not know how much one uses something until it is gone.

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

[Jim Kosloskey]

This is a wide topic. There are some ‘normal’ situations that fit the standard (assuming HL/7 and TCP/IP) and some ‘not so normal’ situations. Many have tried ‘one solution fits all’ with what I consider mixed success. Compounding things is receiving systems don’t always follow the rules.

If you would like, I would be happy to discuss various scenarios with you and go over the configuration setup. Just email me and we can discuss this off-line.

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

[Jim Kosloskey]

Jason,

Yes, one could get the host xlate name in the host xlate and set a temp variable (temp variables are transferrable between host an included xlate and vice-versa) but that reduces the generic potential of the facility. I was hoping there was something already in place under the covers that could be referenced.

Before I explored alternatives and alternatively asked for an enhancement, I wanted to find out if there was something already provided.

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

[Jim Kosloskey]

I agree that is what is happening, but I don’t believe that is what should be happening. The implication in all of the documentation is that the xlateInList and XlateInVals lists are in synch with each other and that is to be expected. The reality is there are cases where that is not true and I do not think those cases should exist. The relationship between xlatexxLixt and xlatexxVals (in and out) should be maintained in my opinion and based on what I can gather from the documentation available and my past experience.

I have coded xltp type procs this way since release 3.1 and although I cannot prove it, I do not recall this particular issue in the intervening releases I have used (I have not used all the intervening releases).

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

[Jim Kosloskey]

I have attached a white paper on using a Stored Procedure for Table lookup. This happens to be for a postgresql db but the techniques should work for any DB that has Stored Procedures.

Attachments:

You must be logged in to view attached files.

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

[Jim Kosloskey]

Actually, I think you might be able to do this within one Xlate and re-route one of 2 messages (the one without the PDF). Still 2 OB threads though.

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

[Jim Kosloskey]

I think SMAT records messages once they have been successfully sent so removing the PDF prior to SMAT means the OB message won’t have the PDF. Not what you want.

One thing you could do is to use an Xlate chain and re-route with another ‘dummy’ thread as the destination that delivers the messages to a dev/nul file (essentially nowhere) and have the SMAT on that thread and not the one delivering to the downstream system.

The Xlate Chain would have the Xlate that builds the message with the pdf followed by another Xlate that SENDS the original message to the downstream system thread and removes the OBX-5 with the PDF and re-routes another message missing the OBX to a new trxID which then routes to the ‘dummy’ thread.

If you are not familiar with either chaining or re-routing, look in the documentation or email me and I will send you technique white papers on chaining and re-routing.

Of course, you could also use Tcl to remove the OBX-5 on a route to the ‘dummy’ thread.

The downside is you really do not have an exact copy of the message sent to the downstream system in SMAT, you have a copy that should be the same sans the OBX-5 with PDF.

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

[Jim Kosloskey]

Quick update — I got the invocation of a Stored Procedure for Advanced Table Lookup working in CL 2209. I will produce a White Paper on how I did it and what I found out and post it as soon as it is done.

But, in the meantime, if you want to do that (or any DB Lookup that supports Stored Procedures) I am willing to assist.

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

[Jim Kosloskey]

I am glad you got that working.

As for SP postgresql, not good.

First, I am attempting this on CL 19.1 which does not natively support postgresql. We got a driver and installed that, but then needed to install postgresql libraries. Finally got connectivity. A simple query works fine.

But calling a SP fails. After trying everything I could I came to the conclusion the issue is Cloverleaf only supports IN and OUT parameters to a called procedure (at least in 19.1).  That is what is documented. Postgresql does NOT support OUT parameters it only supports IN and INOUT parameters.

CL 2209 documentation still indicates only supporting IN and OUT parameters for SP but CL 2209 claims support for postgresql. I am unsure if a postgresql Stored Procedure with INOUT parameters can be successfully called from CL 2209.

Unfortunately, at this point, I cannot reference the DB I am using in CL 19.1 from CL 2209. Otherwise, I could determine if CL 2209 has the same issue.

If someone out there has successfully called a postgresql SP with INOUT parameters in post 19.1 Cloverleaf, I would be interested in conversing with them.

If I finally determine 2209 capability vis-a-vis postgresql and my memory allows, I will post here.

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

[Jim Kosloskey]

Are you doing SQL or Stored Procedure?

I am currently trying to get advanced lookup via Stored Procedure with a postgresql DB (Cloverleaf 19.1).

If you are doing Stored Procedure, perhaps what I discover could be of use to you.

I have been fighting my way through a lot of unknowns but making some progress (no join in my SP)

If you are using SQL, I am afraid I cannot be any help at this point.

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

[Jim Kosloskey]

Thanks Paul. I guess I will modify my addition to the ‘when I have time’ list now to try to determine the limit as accurately as possible.

 

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

[Jim Kosloskey]

This link might be a start:

https://wiki.tcl-lang.org/page/Active+Directory

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

[Jim Kosloskey]

Paul,

Thanks for the information. Now I wonder if this is a GUI limit or a real engine run-time limit.

In other words, I wonder if a larger table can be built via a Tcl proc and used effectively in the engine at run time but not be editable in the GUI?

An experiment to add to my long list of ‘when I have time’ items.

Jim

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

[Jim Kosloskey]

If you really want this done in an Xlate then you could set the thread configuration to include the message in USERDATA; then in the Xlate (using Tcl) do a string length on the entire message returning the length; then using Xlate IF Action(s) decide what to do with the length the proc returned.

As an aside, a post I made earlier regards the related efficacy of Tcl vs Xlate filtering. My conclusion was there is little difference in performance. If you are interested, find that post or email me and I will send the product of my research.

And, no I do not have code to do specifically what you ask but it is pretty straightforward to do what I outlined above.

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

[Author]

Replies