Examples of exchanging parameters with Stored Procedure in DB Lookup Table

[Jim Kosloskey]

Cloverleaf 19.1, CentOS.

I am considering the use of Stored Procedures for Lookup Tables and want to exchange parameters in the call. Is anyone willing to share their insight into how they accomplished this and any caveats of which to be aware?

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

Topic

[Jim Vilbrandt]

Hi Jim,

I am running inbound stored procedures with both Oracle and MSSQL. The process is very different for both. I am not passing parameters to the called procedure, so I am curious if someone has accomplished this.

Oracle:
CREATE OR REPLACE PROCEDURE cp_clv_test(out_var out sys_refcursor)
IS
BEGIN
open out_var for select <values> from <tablename> where <qualification>;
END;

Read Action: {call cp_clv_test(rowset OUT CURSOR)}

MSSQL:

CREATE PROCEDURE cp_clv_test
AS
BEGIN
SET NOCOUNT ON;
select <values> from <tablename> where <qualification>;
RETURN;
END;

Read Action: {call cp_clv_test()}

You can update the row sent in this way:

Read Success Action: UPDATE <tablename> SET <fieldname>=<value> WHERE <fieldname>=<<passedvalue>>

Note: passedvalue must be a field returned by the stored procedure and must be enclosed in <>.

I hope that helps!

Are you aware that CentOS reached “end of life” on 30.06.2024???

Best Regards from Germany, Jim Vilbrandt

• This reply was modified 5 months, 2 weeks ago by Jim Vilbrandt.

[Jim Kosloskey]

Jim,

Thanks for the input. In my case someone else decides the O/S and while the information you provided is very helpful, I wonder if the Table Lookup for a DB via Stored Procedure configuration is different.

I very specifically want to exchange parameters with the invoked Stored Procedure so I hope (as you do) that someone who has done this will chip in.

For example, I wonder how the Xlate gets the value of an Output Parameter(s).

 

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

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

[Author]

Replies