JDBC sender adapter - 3 calls in PO 7.4

[Former Member]

Hi Experts,

I have a scenario to fetch database records from ORACLE database using SAP PO 7.4 - JDBC adapter

There are 3 calls to make in order to fetch the data

a) Call a procedure before the fetch

b) fetch the record

c) Call another procedure after the fetch

How can we achieve this ?

thx

mike

[Former Member]

Hello,

All the 3 calls are to a single DB or they are to different DBs?

Thanks

Amit Srivastava

[Former Member]

All the 3 calls are to a single ORACLE DB

[former_member184720]

Hi Michael - Why don't you merge them into another stored procedure and call it form JDBC sender channel?

Any constraint in doing that?

[Former Member]

Hello,

If that's the case, then just call first SP with the request message and let DB perform other DML opeartions? Why to unnecessary burden message processing in PI?

In case u still want to use PI then u can use NW BPM (since u are using PO?) to call ur SPs in sequence and get the response back.

Thanks

Amit Srivastava

[Former Member]

Hi Guys,

thanks for the replies. There is a standard architecture they are following where in the consumer of the database (in this case PO 7.4) has to follow the calls in the sequence step 1, step 2 and 3. 

If I have to use NW BPM, what would be the steps? By the way, there is no response I am expecting in step 1 and 3 while calling corresponding procedures. Only fetch records in step 2 gets the records using select statement with join.

Thanks

mike

[Former Member]

Hello,

>>By the way, there is no response I am expecting in step 1 and 3 while calling corresponding procedures. Only fetch records in step 2 gets the records using select statement with join.

That's the reason i was suggesting u to use everything in DB instead of making multiple calls.

>>There is a standard architecture they are following where in the consumer of the database (in this case PO 7.4) has to follow the calls in the sequence step 1, step 2 and 3.

IMO, u can certainly challenge the architecture if u are finding gaps in that.

BTW, if u are really interested in implementing this using NW BPM then u can post ur doubts here. Will try to help u in implementing the same.

Thanks

Amit Srivastava

[Former Member]

Hi All,

I am opening this up for a follow up qs on this topic. Is it true that we can't directly invoke a stored procedure from PI 7.4 sender JDBC adapter for retrieving data from ORACLE DB and we have to rely on table functions.

I was looking at some blogs on sender JDBC stored procedure calls for SAP PI 7.1/7.3/7.4

And the note Michael was referring to 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities  has the following piece of code for invoking a stored procedure from a package function returning collection of elements.

           ---------- pkg1 ----------

CREATE PACKAGE pkg1 AS

  TYPE numset_t IS TABLE OF NUMBER;

  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

END pkg1;

/

CREATE PACKAGE BODY pkg1 AS

-- FUNCTION f1 returns a collection of elements (1,2,3,... x)

FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS

  BEGIN

    FOR i IN 1..x LOOP

      PIPE ROW(i);

    END LOOP;

    RETURN;

  END;

END pkg1;

/

---------- pkg1 ----------

           This function has to be invoked from the sender channel configuration (SELECT statement) as follows:

           SELECT * FROM TABLE(pkg1.f1(5));

If I can't call stored procedure directly from PI sender JDBC adapter in 7.4,

I have to ask the Oracle DB guy to write a stored procedure which wraps all 3 procedures into one and again wrap it up using the package functions as well if required. So, please let me know.

thx

mike

[Former Member]

Anybody?

[Former Member]

Hi Mike

I think we can call a store procedure directly in sender JDBC adapter.

Why don't you give it a try and let us know if that works or not.

[Former Member]

Indrajit,

I've mySQL server as part of the wamp bundle in my laptop. And I have written a stored procedure there. Now, how do I call it from PI? what would be the driver, connection details to be given?

I am doing a test myself to make sure it works before I ask the DB guy to work on package functions wrapping the procedure..pls help

thx

mike

[Former Member]

anybody? the qs basically is how to connect to local Database server on your laptop from SAP PI using JDBC adapter? Has anyone tried this? This is similar to having a local FTP server installed in your laptop and test file polling or pushing.

thx

mike