cancel
Showing results for 
Search instead for 
Did you mean: 

Reg:JDBC Lookup to pull multiple values

Former Member
0 Kudos

Hi All,

We are trying to pull the data from SQL server and passing it to to RFC through JDBC lookup. Can anyone suggest me how to pull data from different tables and assign it to the target structure (RFC).

Thanks in Advance,

Lavanya.B

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Lavanya,

You can do that scenario using BPM. For that u need to write the stored procedure and execute it. The response from the stored procedure can be mapped to the RFC request.

Soap-JDBC---RFC

Find the below details.

1.Receive(Opens Bridge)

2.Synchronous Send(JDBC -- Stored procedure is executed)

3.Transformation( response from JDBC stored procedure to RFC Request)

4.Synchronous Send (RFC)

5.Send(Closes Bridge)

Regards

Leela

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi,

You can consider this alternative using Stored Procedure...

At receiver side you can execute stored procedure and return the required values in the Response structure. this response structure you have to map to you RFC structure.

i) You can easily handle your select queries, complex logic in the stored procedure.

ii) It is faster than triggering select queries.

You can check these examples for practice and doubts...

https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/5474f19e-0701-0010-4eaa-97c4f78d...

(3rd Scenario in the above pdf)

Also refer

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

Regards

Former Member
0 Kudos

Hi All,

My actual requirement is,A project ID is being triggered to PI which will pull the data from the database(SQL Server 2000) ,from different tables and it has to post to SAP-R3 system through a RFC call.Receiver side I will be using the RFC Adapter.

Please suggest me the best Sender adapter to use for my scenario.

Solution:

For the above scenario I have used ,

Sender = Soap adapter

Receiver = JDBC Lookup Table

Receiver = RFC adapter

Lavanya.B

Edited by: Lavanya Balanandham on Dec 12, 2008 11:49 AM

Edited by: Lavanya Balanandham on Dec 12, 2008 11:53 AM

prateek
Active Contributor
0 Kudos

Your procedure is correct but for multiple tables, you will have to create multiple JDBC lookup calls.

Regards,

Prateek

Former Member
0 Kudos

Hi All,

For the above scenario I need to pull data from different tables against the project Id and which is of Two dimensional or Three dimensional array type and not a single array type.

Is this possible in PI through a User Defined Function.

Can someone suggest me on this.

Prateek,Can you please explain in detail.

Thanks in Advance,

Lavanya.B

Former Member
0 Kudos

Hi,

To use Multiple lookup, you need to write JDBC lookup for each table you want to read. Read the data and map it to appropriate fields to request. Your scenario will become very slow and also complex as you need to make lot of JDBC lookup calls which will take time.

I will suggest it like this:

1) Find some way by which you can pass Project Id to Stored procedure which will read the data from Database and send it to XI using sender JDBC adapter.

2) Do simple mapping.

To pass that Project Id value to SP directly, you can maintain one more table in database. SP should read the data for Project id in database and should delete the entry after successfully read operation.

It may seem to be difficult and but can improve the performance drastically.

If volume of messages for interface is very low you can go for JDBC lookup.

Kulwinder

Former Member
0 Kudos

Hi,

Try to use Stored procedure as mentioned earlier. If your table structure is comples it will be more difficult to write JDBC lookup.

Kulwinder

Former Member
0 Kudos

Hi Kulwinder,

Thanks for your valuable suggestion.

I need to pull data for NxN structure from the database,is this possible through stored procedure .

Lavanya .B

Former Member
0 Kudos

Hi,

I beleive its possible because Stored procedure are much more powerful tool to read that than the simple queries. It will be good if you take help of some guy who has experience in writing Stored Procedure.

Kulwinder

Former Member
0 Kudos

Hi Kulwinder,

When it is possible through a stored procedure then BPM is only the solution I suppose.

Please suggest me on this.

Lavanya.B

Former Member
0 Kudos

Hi Lavanya

For your requirement Lookups will not help much as creating multiple lookups will hit performance and even then creating NxN structure is a challenge.

Solution can be creating a Stored Procedure and do a direct call from Java Mapping to database and get the values in NxN array and pass it to RFC but this is not preferred.

You can create a SP and pass this Project ID to it.

Use BPM for this scenario.

Steps given by Leela should work for you.

Thanks

Gaurav

Former Member
0 Kudos

Hi All,

Thanks for all your valuable solutions.

Can this scenario be achieved only through BPM or is it possible without BPM.

If this could be achieved without BPM then suggest me the procedures and steps for the SOAP-JDBC-RFC scenario

Lavanya.B

dharamveer_gaur2
Active Contributor
0 Kudos

Hi

I think Look up is not possible with multiple tables, however i did'nt tried. When u Import SQL meta data need to mention table name, so only one table name can be imported.

use this blog to impot SQL meta data

If you are using PI7.1 then use this blog

https://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/8661. [original link is broken] [original link is broken] [original link is broken]