cancel
Showing results for 
Search instead for 
Did you mean: 

Using JDBC Adapter posting data from Oracle database to Falt file????

Former Member
0 Kudos

Hi All,

By using JDBC adapter, for now I have to connect to Oracle Database and then get the employee information from the Employee Table and post it to the flat file, can you please help me out the complete procedure...

Thanks in advance

Andy

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi

Using JDBC sender and File receiver adapter you could able to do this scenario.

1. Create your Product and Component in SLD , if you want use Business system as service for Oracle database then create techincal system assign to Business system as thirdparty in SLD orelse leave it here use Businessservice in IR. better use business service.

2. Import the component into IR under create namespace and Design your DT,MT,MI,MM,IM for oracle table and File . Activate.

3. Login ID and create scenario underthat u can assign business system or create business serivce sender for sender and receiver adapters.

4. Create communication channels under each service for JDBC sender and File Receiver.

5. Create sender agreement

6. creat Reciver Agreement

7. Create Receiver Determination

8. Create Interface Determinatio and activate the scenario.

Use SXMB_Moni for message monitor on ABAP stack or login RWB for message monitor checking on Java Stack.

there is lot of weblogs are currently avilable in SDN..try to serach using related words..

Regards..rambarki

Former Member
0 Kudos

Hi Rambarki,

Thanks for ur response. can you please help me out in detail. Like connecting to Oracle database and getting the input for mapping, since I know the steps what you told, but I am stuck how to get the data from Oracle database and how to do mapping , if you can help me out in this scenario it will be a great help for me.

Thanks in advance

Andy

Former Member
0 Kudos

Hi Andy,

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 an XML file looks like that contains the query 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>

So you have to create a datatype, message type and message interface to match this structure (of course you'll need to substitute column-name1 etc. with the column names in your select statement).

Now if you want to create a flat file from that, you have to create an XML representation of that flat file and use the content conversion in the receiver file adapter as defined here: http://help.sap.com/saphelp_nw04/helpdata/en/d2/bab440c97f3716e10000000a155106/content.htm

Regards

Christine

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Andy,

To add to what christine had told and my prvious post.

As you are using a JDBC adapter to read data from your database, you need a SENDER jdbc adapter.

next, while doing your Adapter configuration, you will be asked for SELECT .. this is the select query which will select the rows from the table.

Next, there is anotgher statement called UPDATE. If you do not want the rows selected to be selected again, then you can write an Update Statement in this ,so that it is executed immediately after the rows are selected from your database.

eg:

Select name from emptable where flag='y'

Update emptable set flag='n'

Now, the rows selected by your JDBC adapter are in the format listed by Chrisitine. So your source datatype should also be of the same format.

DO the mapping for the source to your file datatype, and then do the content conversion on your receiver file adapter.

Regards,

Bhavesh

Former Member
0 Kudos

Hi Rambarki,

Can you please explain in detail about

1. Create your Product and Component in SLD , if you want use Business system as service for Oracle database then create techincal system assign to Business system as thirdparty in SLD orelse leave it here use Businessservice in IR. better use business service.

Since I didnt create any Business System in SLD, only at configuration I created Business Service and configured the communication channels. Next when I am trying to configure the Receiver and Interface Determination,Sender and Receiver Agreements I couldn't do that since there is no connection between the Business Service which I created and the Interface and Namespace. Can you please let me know whether I am in right path... and also while configuring the JDBC adapter, in UPDATE what shall I do....Since in my select stament is "SELECT from EMP where EMP_ID = '1000';

so according to this select I dont need to update , I have to out this record in Flat file...Please advice

Thanks

Andy

Answers (6)

Answers (6)

former_member91687
Active Contributor
0 Kudos

Hi Andy,

Does the target directory to which you are writing to have access permissions from the xi system? Make sure of this.

Also is the system windows based or unix based?, if its unix based, say the app server and XI server is on unix, the app server file system has to be NFS mounted onto the XI server so that XI can see it.

Regards,

Chandra

former_member91687
Active Contributor
0 Kudos

Hi Andy,

Check the syntax you have given for the target directory. It is C:/ followed by the path.

Regards,

Chandra

Former Member
0 Kudos

Hi Chandra,

I tried with C:/, still it is not working

Thanks

Andy

former_member91687
Active Contributor
0 Kudos

Hi Andy,

Consider an example, you have a column in you data base table whose value is set to true, and you are selecting the record if the value is true, now in your update statement, you would give something like this,

<b>UPDATE tablename SET param='false' where param='true'</b>, so that the same record would not be processed again. So depending on the tables and the information you are querying for you would have to write your update statement.

Go through the update statement section given in this link:

http://help.sap.com/saphelp_nw04/helpdata/en/1d/756b3c0d592c7fe10000000a11405a/frameset.htm

Can you explain in details about your other requirement, if its to do with patner profile settings etc etc, these documents give you a lot of information about how to do it:

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/73527b2c-0501-0010-5398-c4a...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/6bd6f69a-0701-0010-a88b-adb...

Regards,

Chandra

former_member91687
Active Contributor
0 Kudos

Hi Andy,

When you created your business service in the directory, on the <b>sender/receiver tab</b> did you choose your outbound and inbound interfaces? Once you do this, you must be able to proceed.

Consider this, first your SELECT statement is executed and your record is picked from database, and then your UPDATE statement is executed so that the picked record need not be picked again in the next polling time. This should give you a clear idea now.

Regards,

Chandra

Message was edited by: Chandravadhana

Former Member
0 Kudos

Hi Chandra,

Thanks a lot, in this sitaution can you please tell me what should I enter in the UPDATE statement

Thanks

Andy

Former Member
0 Kudos

Hi,

Also can anyone let me know that we have to do Port, Partner Profiles both in R/3 and Xi system in this scenarion...Can you please explain in brief

Thanks in advance

Andy

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Andy,

If you do not want anything to be updated, then you can also use the statement <TEST> for the update statement.

Regards,

Bhavesh

Former Member
0 Kudos

Hi Bhavesh,

For this scenario I finished design and configuration, now I have to test . Can you please tell me the steps for testing like how can we test the connection and also the result. This is very urgent task for me . Can you please advice

Andy

moorthy
Active Contributor
0 Kudos

Hi Andy,

While activating , hope you don't have any errors in JDBC Sender Adapter. You can check this Adapter Monitoring->JDBC. This is to ensure that , configuration of JDBC adapter is correct.

Then just activate the adapter for specifc period. Then go and check in the transaction code -SXMB_MONI of XI server and check the status of the Message.If you get the successful flag and then check for file in the Receiver system.

Hope this helps,

Moorthy

bhavesh_kantilal
Active Contributor
0 Kudos

Hi andy,

Make usre that there is data in your database that satisfies the SELECT query in the JDBC adapter.

Check your Adapter's configuration in the RWB , does it show any error, success??

Trace the mesage flow in SXMB_MONI and see if any errors.

FInally, in the RWB , check your file adapter for errors, success?

If no errors at any stage, then you must have the file created in the required directory.

Regards,

Bhavesh

Former Member
0 Kudos

Hi Moorthy,

I can see the job with succesful flag, but I could not see the output file. Here under you can find the details which i enetered

in the Target Directory : C:\Temp

File Name as : output.txt

in the porocessing parameters

File Type as Text

Thanks

Andy

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Andy,

Make sure that the folder / directory is accesible from your XI server and that it has write permissions on that folder.

Next, check the status in the File Adapter. What does it say??

Regards,

Bhavesh

Former Member
0 Kudos

Hi,

You need to specify a target directory of the application server and not the presentation server(not the system you are working on) C:/ i guess, is a local drive on your machine. You need to specify a directory of the app server.

In your system, start->run->give your app server's ip-> and check which folders you have write permission.

Give this link as the value for the target direcoty.

Regards,

Smitha.

bhavesh_kantilal
Active Contributor
0 Kudos

Hi Andy,

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

To install oracle driver, just check this link,

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

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

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>

Meannwhile, you have to give a Select statement for your Sender JDBC adapter. This is used to seelct the rows from the table.

You also have an update statement. This is used to update the rows that have been selected. Just check this link for the info,

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

Regards,

Bhavesh

moorthy
Active Contributor
0 Kudos

HI Andy,

You need to configure JDBC Sender Adapter for this.

You can directly query the Table from the Adapter and map it into the file and sent to file.

It will be a JDBC->XI->File Scenario. JDBC Adapter will poll the Table for partcular intervals.

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

http://help.sap.com/saphelp_nw2004s/helpdata/en/22/b4d13b633f7748b4d34f3191529946/frameset.htm

Also check for howTOguides from Service.sap.com/nw04

Hope this helps..

Regards,

Moorthy