configuring sender jdbc adapter

[Former Member]

Hi all,

I have a requirement in which i have to select certain data from Oracle table and send it to an IDOC via SAP XI.

Problem is the select can happen from several tables. For example:

SELECT col1,col2,col3 FROM <tablename>

The <tablename> has to be variable. I do not need to do any JOIN. At a time select will be only from a single table i.e. col1, col2, col3 will all belong to the same table. But the adapter should poll multiple tables.

One more requirement is that once a particular record from a particular table has been selected some UPDATE also need to be done on the same record.

Pls can anybody provide me with a proper solution.

This requirement is a bit urgent.

Thanks in advance.

Regards

Neetu

[Former Member]

Hi Neetu,

I had exactly the same requirement as yours.

Follow the instructions given in the following weblog by Bhavesh:

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

Here u have to remember 2 things:

1. For multiple tables have multiple Statement blocks like

<STATEMENT_TABLE1>

......

<STATEMENT_TABLE2>

......

2. You need to have some trigger to start the process. This may the primary key of the tables or some relevent data.

Cheers,

Vaibhav

[Former Member]

I am not sure about ur requirement.. However, from my understanding

1 since lot of tables to be fetched

2.write a join query get the necessary necessary table.column values..Have receiver as file and do message mapping for required fields and conditions.

3.Have BPM..to synch..

4.update the values as per conditions using the synch call and calling JDBC adapter update query

thanks-GS

[Former Member]

Hi,

Actually i need to write more than one Select Statements.

Can we append more than one select statements in the sender JDBC Adapter Channel.

Regards

Neetu

[bhavesh_kantilal]

Hi,

><i>Actually i need to write more than one Select Statements.

Can we append more than one select statements in the sender JDBC Adapter Channel.</i>

This is not possible.

Regards,

Bhavesh

[Former Member]

Not possible you have to go only with a nested querry or join.

Regards,

Jai Shankar.

[deviprasad_pothireddy]

Hi Neetu,

Refer these weblogs. I hope these are helpfull for you

JDBC Sender/Receiver adapter

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

Sql Statements

/people/alessandro.berta/blog/2005/10/04/save-time-with-generalized-jdbc-datatypes

[Former Member]

Hi Neetu,

Dint get your requirement clearly. Where will you get the table name from for doing the select operation each time?

Regards,

Jai Shankar.

[bhavesh_kantilal]

Neetu,

You need to have the Table name dynamic in the Sender JDBC adapter's Select Query . This is not possible

You can consider using a Reciver JDBC adapter for which you can have a look at this blog ,

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

Or, you need a Separate sender JDBC adapter for every table to be polled.

>><i>One more requirement is that once a particular record from a particular table has been selected some UPDATE also need to be done on the same record.</i>

If you are using the Sender JDBC adapter, this would be simple using the UPDATE field of the Sender JDBC adapter .

If you are planning to use Receiver JDBC adapter, then You need a separate Send Step for this Update.

Better way to go would be to have separate sender JDBC adapters.

Regards,

Bhavesh