Oracle DB - XI - SQL DB

[Former Member]

Hi,

We need to pick some data from Oracle DB (in a separate server) using SAP XI and update the tables in SQL database(Internal to organization). and also mark the field as sent in OracleDB after collecting the data daily.

Please help me to identify the scenario and give me some config instruction for the same.

Thanks and regards,

Rehan

[Former Member · Accepted solution]

This scenario will be jdbc to jdbc scenario.

In both the adapters just the connection strings and user id and password will differ.

*also mark the field as sent in OracleDB after collecting the data daily. *

You can write query in adapter for the field UPDATE to update selected records in the Oracle DB.

[Former Member · Accepted solution]

Hi Hetal,

Thanks a lot, Is there any link where we can get a blog on config steps for jdbc to jdbc scenario.

Regards,

Rehan

[Former Member · Accepted solution]

hi,

see this

regards.

[Former Member]

before go to the configuration part,u should follow some structure format in IR.ur source structure(XI) should be same as oracle table columns.

ex:

Oracle table fields:

EMPNO,NAME,CITY,IS_UPDATE

SQL table fields:

EMPNO,NAME,CITY

source structure:

note : "+" represent the header element node

+ Name of message type(source)

-

---

+ row(small letters)---(occurance of 0 to unboundrecom)

-

---

EMPNO

-

---

NAME

-

---

CITY

-

---

IS_UPDATE(donot map to target)

target structure:

+ Name of message type(target)

+ STATEMENT--(occurance of 0 to unbound)

-

---

+NAME OF THE TABLE or any name

-

---

ACTION---category of Attribute

-

---

TABLE

-

---

+ ACCESS

-

---

EMPNO

-

---

NAME

-

---

CITY

-

---

+ KEY

-

---

+ EMPNO

-

---

compareOperation(only "O" caps letter)

MESSAGEMAPPING :

1. map "row" to STATEMENT

2. map constant[UPDATE]function to ACTION(target)

3.map constant[dbo.table name in SQL]function to TABLE

(target)

4.map constant to KEY

5.map EMPNO(source) to EMPNO(target under key node)

6.map constant "EQ" to compareOperation.

ID part:(config)

sender communication for oracle:

1.choose adapter type is "JDBC"

2.define ur Oracle database connection.it should be exactly where ur oracle server located.

3.define sql query to reterive the date from oracle table SELECT * from EMPDETAILS where IS_UPDATE !='Y'

4.once data reteriving from oracle table, it will update the table using update query :UPDATE EMPDETAILS set IS_UPDATE='Y' where EMPNO=100

5. define document name as message type name for sender(oracle).

6.define your namespace which is from IR.

7.check "Advanced mode"

8.check "Database Auto-commit-Enabled"

Receiver communication for SQL:

1.choose adapter type is "JDBC"

2.define ur SQL database connection.it should be exactly where ur SQL server located.connection and JDBC drivers will differ based on ur MSSQL version.find urself thrgh internet.

I hope this will help u more .....

Regards

SenthilKumar.

Edited by: senthil kumar on Aug 7, 2008 6:23 AM

Edited by: senthil kumar on Aug 7, 2008 6:28 AM

Edited by: senthil kumar on Aug 7, 2008 6:33 AM

[Former Member]

Hello Rehan,

when i was developing Oracle DB communication with XI i have taken help of SAP documentation

Good doc about JDBC adapter:

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

hope it will help you

[Former Member]

Hi,

For configuring the JDBC-to-JDBC scenario please go through the following link it may help u.

https://www.sdn.sap.com/irj/sdn/wiki?path=/display/xi/jdbcTOJDBC

Regards

Sridhar Goli

[Former Member]

Dear Rehan,

Just to add answer to your query apart from what experts told.

Do make sure both Individual Data Base Servers are configured by installing the necessary drivers in to XI.

without which JDBC adapter doesnt work Individual databases.

for the list of SAP OSS notes related to JDBC Adapters plz check the link in SDN WIKI area.

WIKI> Exchange Infrastructure> XI Adapter Notes

Best Regards

Praveen

[JaganMohan]

Dear Rehan,

Your scenario is JDBC to JDBC. You have to set a flag with some value (like N) before reading the data from Oracle. After reading you have to change that flag as some value (Y). Means that you assign value N before reading and after that you change it as Y.

In Configuration:

Database Connection Parameters:

Corresponding your Oracle and SQL databases.

Processing Parameters:

Oracle is a sender. So in sender CC has the processing parameters. In that you specify the Query SQL Statement as "SELECT" query and Update SQL Statement as "UPDATE" query. In the select query you can fetch the data from Oracle and check the condition with that particular flag and from Update query you set flag that you want to change after the data insertion in SQL.

Regards,

Jagan.

Edited by: Jaganmohan Ponmani on Aug 6, 2008 12:46 PM

Edited by: Jaganmohan Ponmani on Aug 6, 2008 1:01 PM