Posting Data from mySQL Database to Oracle Database

[Former Member]

Hi SDN

Can any one tell how to post data from mySQL database to Oracle. I please provide me the necessary links also.

Thanks in advance

Regards

Basha

[Former Member]

first configure mysql db at sender side and config oracle db at receiver side to their corresponding drivers after map the fields

[bhavesh_kantilal]

Hi Shaik,

Also take a look at this blog to see how to create the Datatype for the reciver JDBC adapter. it deals with a file to JDBC and so the destination JDBC datatype format can be understood using this blog,

/people/sap.user72/blog/2005/06/01/file-to-jdbc-adapter-using-sap-xi-30

In the case of sender,have a look at the help for configuring a JDBC Sender adapter: http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

It mentions all the settings you'll need to make e.g. the connection URL as posted above.

And it also lists how the Source Datatype should be defined based on the results according to the SQL Select statement in the adapter.

<resultset>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

<row>

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

</resultset>

Regards,

Bhavesh

[Former Member]

Hi Bhavesh

Thanks for u'r quick reply. Can u briefly tell the steps to be followed.

Regards

Basha

[bhavesh_kantilal]

Hi Shaik,

1. creating the source and destination data types.

<b>Source</b> :

<dt>

<row> 0 to UB

<column-name1>column-value</ column-name1>

<column-name2>column-value</ column-name2>

<column-name3>column-value</ column-name3>

</row>

</dt>

<b>Destination</b>,

<dt>

<StatementName>

<dbTableName action= "INSERT”>

<table>realDbTableName</table>

<access>

<col1>val1</col1>

<col2>val2new</col2>

</dbTableName>

</StatementName1>

2. Create Message type, Message Interface etc. Do the mapping of the fields.

In the Directory, create the communication channels for the Sender and Receiver JDBC adapter, sender agreement, receiver agreement, receiver determiantion and interface determination.

Regards,

Bhavesh

[Former Member]

Hi Bhavesh

Thanks a lot.I am just expecting the concept first but not the entire senario. Just briefly tell the Procedure but not the entire senario.

Thanks in advance yar.

Regards

basha

[Former Member]

Hi Bhavesh

Can u provide me the link on batch scheduling in XI.

Regards

Basha

[bhavesh_kantilal]

Hi Shaik,

As you are posting Data from mySql to Oracle, baisacly what you will be doing is a JDBC to JDBC scenario.

You will have a JDBC as a sender to collect the data from Mysql and then you will have another JDBC adapter as a receiver to push the Data into the oracle database.

Hope this info makes things clear,

Regards,

Bhavesh

[Former Member]

Thanks a lot Bhavesh.

Regards

Basha

[bhavesh_kantilal]

Hi Shaik,

To access any Database from XI, you will have to install the corresponding Driver on your XI server. To install the driver, check this documentation,

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

To install oracle driver, just check this link,

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

The details that have to be entered while adapter configuration for your Oracle,

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

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

Likewise, you need to install your Driver and get the parameters for MySQL.

Now, as you are posting data from mySQL to Oracle via XI, you will have a SENDER JDBC adapter pointing to mySQL and a RECEIVER jdbc adapter pointing to Oracle.

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. Also, there is another field in your sender JDBC adapter that is very important and that is the Update satatement. Once your JDBC adapter executes your select query and selects rows from the database, you might not need those rows to be selected again. In this csse, you can use the Update statement to update the database.

In the case of the receiver JDBC adapter, you can insert/ update/ delete data and execute stored prooedure into your databse.

For more info, look at these links,

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

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Do let me know if you need any further info,

Regards,

Bhavesh