Proxy to JDBC interface with multiple select queries

[former_member216353]

Hi all,

We have to develop a interface where SAP will be sending a Number based on which we have to fetch the data from the database and send it to back to the SAP system.

The data in database is spread across 6-7 tables/views and canu2019t be fetched using a single query even with joins.

Can anyone please suggest the best way to develop this interface.

As it would be a synchronous interface, I think it is not possible to use multimapping.

Regards

Younus

[PriyankaAnagani]

Hi,

>>>>Can anyone please suggest the best way to develop this interface.

Beter way is to create stored procedure which will fetch data from multiple tables and use it in PI.

Refer below discussion

http://forums.sdn.sap.com/thread.jspa?threadID=2123523

Regards,

Priyanka

[former_member216353]

Hi all,

Thanks for the inputs.

Database team is not ready to change anything at their end.

I have checked with the database team about the stored procedure but they are not willing to add any additional logic on their side to integrate with PI.

Is there any possibility of handling it in PI itself?

Regards

Younus

[Former Member]

Dear Mohammed

If the database team is not ready to do a change (like creating a store4d procedure) you can go for BPM.

You have to create various transformation steps for quring the database.

Sourabh

[PriyankaAnagani]

Hi Yonus,

In your previous questions, http://forums.sdn.sap.com/thread.jspa?threadID=2123523

you've specified that you achieved it with join statement . May I know why again you're not considering that solution if it is working fine.

Regards,

Priyanka

[former_member216353]

Hi Priyanka,

That is a different requirement where we have to extract the data from only 2-3 tables which we did by using join query.

But in this case , the data has to be fetched from 6-7 tables.

Out database team tried to write a single query with joins but they were not able to get the expected output.

So they have provided us 3 select queries and each select query is a join on 2 tables.

Regards

Younus

[former_member216353]

Hi all,

I have checked with the database team.

They have confirmed that they can't create a stored procedure but can create PL/SQL program or function with the same logic.

Can anyone please tell me if PI can call a Pl/SQL program or a oracle function?

Regards

Younus

[baskar_gopalakrishnan2]

>They have confirmed that they can't create a stored procedure but can create PL/SQL program or function with the same logic.

IMO, it is not fully feasible to call the oracle function from PI. I would recommend still to use query and construct the following jdbc data structure.

<StatementName>
<anyName action=u201D SQL_QUERYu201D
<access>SQL-String with optional placeholder(s)</access>
<key>
  <placeholder1>value1</placeholder1>
  <placeholder2>value2<placeholder2>
</key>
</anyName >  
</StatementName>

Place your complex join query in the access tag element. Under key tag you can pass values through placeholder element and reference the placeholder element as follows. Sample as follows...

<statement>
    <Customers action="SQL_QUERY">
      <access> Select x,y,z from customers1 a, customers2 b,customers3  c, where a.CompanyName=u2019$NAME$u2019 and b.CustomerID='$KEYFIELD$u2019
      </access>
      <key>
        <NAME>Company</NAME>
         <KEYFIELD>CO</KEYFIELD>
      </key>
    </Customers>
  </statement>

[Former Member]

go for stored procedure...

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/1671

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9618

chk the structure format:

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

[former_member184681]

Hi Younus,

How about creating a stored procedure in the database system, that will collect all the data necessary and return it to your PI system? It might be wise to encapsulate all the logic there in the procedure, and PI will only call it, passing relevant input parameters.

Hope this helps,

Greg