JDBC to Proxy scenario

[Former Member]

Hi Experts,

I am having a requirement in my JDBC -> PI --> Proxy scenario, like in database PI need to fetch the data from two tables and need to update these fields into a single table of SAP through Proxy. Am not able to deside the approch how I need to develop the ESR part. Because am having 2 similar fields in table 1 and table 2, due to this am feeling difficulty to create the DT types by taking the duplicate names.

So please suggest me how I can handle this scenario by fetching data from 2 tables of database.

Thanks in Advance..!

--

With Warm Regrads

Raj

[ambrish_mishra · Accepted solution]

Hi Raj,

My recommendation would be to either handle it through stored procedure or get a staging table created in the database.

What will a stored procedure do:

It will retrieve the data from the 2 tables based on the foreign key relationship and PI will simply get the output which is a direct input to the SAP table.

What will the staging table do:

It will hold the record for the SAP table. Database team will populate this table with a batch job based on Select|Join (you can discuss the solution with them). PI will just poll this DB table and read the records. Can be done by Select query and Update flag.

This will facilitate a simple design.

Hope it answers your question.

Ambrish

[Former Member · Accepted solution]

Hi Ambrish,

Thanks for your timely reply.. I will cehck these possibiliteis with my Db team to achieve this.

--

With Warm Regrads

Raj

[Former Member · Accepted solution]

Hi Ambrish/Zameer,

I am trying to achive this by using N:1 mapping, by creating 2 DT's,2MT's and commonmapping program by using signature tab in Mapping tool.

Can i achive this by using 2 communcation channel and 2 ICO program.

Please suggest me..

Thanks in Advance.!

--

With Warm Regards

Raj

[ambrish_mishra · Accepted solution]

Hi,

You would need a BPM for this design. You would also need to work out how to merge data from the 2 tables. What is the relationship between the data in the 2 tables? 1:1 or 1:many?

Best design would be like I suggested earlier which will simplify the process and ensure data integrity.

Cheers,

Ambrish

[Former Member · Accepted solution]

Hi Ambarish,

As per your suggestion I have discussed with DB team and asked to create a single view for both tables. So now the scenario is PI need to fetch the data and need to delete the same data after extraction in Asynchronous mode.

Am using DELETE query in receiver JDBC to achive this.

Many thanks for your suggestion..!

--

With Warm Regards

Raj

[zameerf]

Hi,

>>Because am having 2 similar fields in table 1 and table 2, due to this am feeling difficulty to create the DT types by taking the duplicate names.

Apart from above suggestion of using Stored Proc or Staging Table, if your only concern is with naming the DT fields in ESR, you can try to give alias for field name in Select query in the channel.

something like,

Select First_Name as FirstName, Last_Name, Date...

your DT can have field as FirstName instead of First_Name

did i understand your issue correctly?

Regards,

Zameer