configuring jdbc adapter with multiple tables

[Former Member]

Hi,

I have scenario like I have multiple tables at my oracle side.I want to send the data to SAP using XI if any one of these tables got updated/insert/deleted.Means my sender is oracle and receiver is SAP.Could any one help me in this how to proceed?

Thanks in advance,

Prabhakar

[Former Member]

check this,

[https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/30dd49da-a6ac-2a10-23bf-d0d58b2532dd]

[bhavesh_kantilal]

hi prabhakar,

Also, to access any database from XI, the corresponding driver has to be installed on Xi.

To install oracle drive in XI server, just check these links,

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3867a582-0401-0010-6cbf-964...

http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html

The details that have to be entered while adapter configuration,

<b>jdbc:oracle:thin:@<IP adress>:<listener port>:<instance name (database name)></b>

Driver <b>oracle.jdbc.driver.OracleDriver</b>

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

In my Xi the drivers are installed,but I am unable to insert the data into table,though it is showing success in XI.do i need to congiure my Database system details in XI SLD ? or if the drivers are there then i can directly commuicate with DB systme using below details :

jdbc:oracle:thin:@<IP adress>:<listener port>:<instance name (database name)>

Driver oracle.jdbc.driver.OracleDriver

regards,

prabhakar

[bhavesh_kantilal]

Hi prabhakr,

<b>am unable to insert the data into table</b>

Can you give me more details.

Your JDBC message type.

Error Shown in the RWB--> Adapter Monitoring by your JDBC adapter.

This will help in tracing the error and reason for occurence.

the parameters mentioned are enoguh. you wil also need to give valid user id and password in the JDBC adapter.

regards,

bhavesh

[Former Member]

Hi Bhavesh,

"am unable to insert the data into table" means after sending the data from source (file adapter)to Destination(oracle) in xi monitoring it is not showing any error.means it successfully posted.but when i try to see this posted data in my oracle table it was not there.menas there is no update in my oracle.

Regards,

Prabhakar

[bhavesh_kantilal]

Hi prabhakr,

The info seen in SXMB_MONI is related only to Integration Engine errors.

Just go to <b>RunTimeWorkBench</b> from your XI home page--> <b>Adapter Monitoring</b> --><b>JDBC</b> adapter and see the message in your JDBC adapter.

This will give you the error thrown by your adapter while it was trying to insert into the database.

Regards,

bhavesh

[Former Member]

Prabhakar, As for your initial qn , chk this blog, may help you.

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

On the error you are facing with receiver JDBC adapter, suggest you chk the adapter monitor(should be green). May be basic, but Have you deployed the Oracle JDBC drivers to XI ?

Regards

Saravana

[Senthilprakash1]

Dear Saravana,

Thanks for the blog it was useful.

coming back to the query.

IS it possible for XI Sender JDBC capture data coming from a Single stored Procedure with Multiple SELECT Statemets?

The structre i suppose would be something like this:

Statement1

row

<fields>

Statement2

row

<fields>

Statement3

row

<fields>

Please Advice,

Senthilprakash.

[former_member206760]

Dear Senthi,

you can read data from more than one table by using JOIN in you select statement in the sender JDBC adapter..

Even for that the source data type would remaain the same with only one row 0...unbounded..

as even if you are fetching from multiple tables it will generate same source xml as while selecting from single table...but in source structure under row specify all the fields f4rom table1,table2 etc..

[bhavesh_kantilal]

Hi prabhakr,

You can use a <b>SENDER JDBC</b> adapter for your case.

This is how a SENDER JDBC Adapter works,

When you are using your JDBC as a sender, then your JDBC will poll over your database and select the rows that satisfy your Select Query.

In your case, as you are dealing with multiple tables, you can <b>write a stored proceudre</b> for your Database, and call this stored procedure in your Sender JDBC adapter in the SELECT field of your JDBC adapter.

Now, it is upto the functionality of your Stored Procedure to detect any changes in your table like insertion, deletion and updation and pass the rows to XI.

Do let me know if you need any further help,

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

Thanks for your reply.you mean to say that i have to write a stored procedure which will observe all the tables(any DB operations) and i need to call that stored procedure.Is my understaing is right ?.Could you please explain in detail how to handle stored procedure?This is first time im doing with oracle as a sender.

Regards,

Prabhakar

[bhavesh_kantilal]

Hi Prabhakar,

There are 2 options you get when you configure a Sender JDBC adapter.

1. In the SELECT field, you can directly give your SELECT query which will return 0 or more rows to XI

eg select name from Tablename where country='xxx'

2. You can call a stored procedure instead of giving a SELECT query in the same SELECT field

eg : EXECUTE procedure1

Now, in your case as you are dealing with a case where you want something to be selected from your Database when some updations happen to any table in your Database, you will have to write a stored procedure that will return the rows that have been updated.

You are dealing with multiple tables in this case and so , you cannot give the details directly in your select statement.

From the link I pasted previous post,

You have the following options:

œ Specify a valid SQL SELECT statement to select the data to be sent from the specified database.

œ Specify an SQL EXECUTE statement to execute a stored procedure, which contains exactly one SELECT statement.

The expression must correspond to the SQL variant supported by the relevant JDBC driver. It can also contain table JOINs.

Regards,

Bhavesh