JDBC outbound reading from multiple tables

[Former Member]

Hi,

I have a scenario in which I have to read data from multiple tables.

The keys of these tables (employee details) are dependent on a employee master table.

Now there is a 1-N relationship between master and the details table. I have to send the data to an IDOC.

I am stuck since reading from all the tables at the same time is not possible.

Please suggest some solution.

Thanks,

Vaibhav

[Former Member · Accepted solution]

You can read data from multiple tables at a time. You can use a join in your SELECT SQL or you can have a stored procedure which does the same and call the stored proc from XI. Stored procs in Oracle can not be accessed.

Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi Jai,

That is an obvious solution. But the problem that i have is that i donot want to have a join as i have 8 details tables. that will produce a huge amount of data.

what i need is some way in which i can have hierarchical format of data as its there in an XML file.

something like.

<EmpMaster>

<addresses>

<add1>

......

......

</add1>

<add2>

......

......

</add2>

</addresses>

<Dependents>

.

.

</Dependents>

and so on...

Any suggestions???

Thanks,

Vaibhav

[bhavesh_kantilal · Accepted solution]

Vaibhav,

Use a BPM and use a separate step for selecting data from every table using a JDBC receiver adapter and then use mapping do the the required logic.

Regards,

Bhavesh

[Former Member · Accepted solution]

Thanks Bhavesh but i am not sure if we can have a JDBC adapter being called in a BPM unless we have a webservice.

Have you worked on any such scenario before?

Thanks in advance.

Vaibhav

[bhavesh_kantilal · Accepted solution]

vaibhav,

Take a look at my blog , It is possible,

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Thanks for ur reply. It solved my problem.

Full points to you...

Pls help me with one more issue.

As per the method u told i am reading from multiple tables. Each table has data for multiple employees.

The response message is of the format

<STATEMENT_Table1_response>

<row>

Data for emp 1

</row>

<row>

Data for emp 1

</row>

</STATEMENT_Table1_response>

<STATEMENT_Table1_response>

<row>

Data for emp 2

</row>

<row>

Data for emp 2

</row>

</STATEMENT_Table1_response>

<STATEMENT_Table2_response>

<row>

Data for emp 1

</row>

<row>

Data for emp 1

</row>

</STATEMENT_Table2_response>

<STATEMENT_Table2_response>

<row>

Data for emp 2

</row>

<row>

Data for emp 2

</row>

</STATEMENT_Table2_response>

I want to collect it to an idoc where i have data of each employee collected together. Like

<Emp1>

<Data from Table1>

<Data from Table2>

</Emp1>

<Emp2>

<Data from Table1>

<Data from Table2>

</Emp2>

I have tried many ways and looks like a custom code in java have to written.

Is there anything else possible?

Thanks in Advance,

Vaibhav

[bhavesh_kantilal · Accepted solution]

Vaibhav,

Until we do not have the exact source and target strucutres, it will be really difficult to be able to go ahead on the mapping. If you can give us the source and target strucutres, maybe we can give it a try.

Regards,

Bhavesh