cancel
Showing results for 
Search instead for 
Did you mean: 

SAP R/3 --> PI --> Oracle DB SYNC (JDBC)

Former Member
0 Kudos

What is the best approach to handle the following request:

We need to develop a synchonous call that will pass information from SAP R/3 through PI to an Oracle database can return the data.

For instance, we want to pass a material number from SAP R./3, and have all the related information stored in the Oracle DB returned immediately.

The data stored in Oracle will be retrieved via a JDBC connection. If possible can you provide an example....thx

Accepted Solutions (1)

Accepted Solutions (1)

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> We need to develop a synchonous call that will pass information from SAP R/3 through PI to an Oracle database can return the data

Yes that is possible. Use Proxy on the sender side and Jdbc on the reciever side. JDBC Reciever support synchronous mode.

Check this link for jdbc receiver synchronous

/people/luis.melgar/blog/2008/05/13/synchronous-soap-to-jdbc--end-to-end-walkthrough

one important thing to be noted.

Ex: If you name your jdbc request root node as Statement then the jdbc response should be named as Statement_response

If you need further details, Please contact us.

Former Member
0 Kudos

Hi Baskar,

Thank you for your posting. I followed the link you sent me, but I still have a few questions.

1.How can I call this from SAP R/3 Abap proxy? When I execute the outbound Service Interface via transaction SPROXY (test mode) I get the following message. RCVR_DETERMINATION">NO_RECEVIER_CASE_BE<

2. I am not sure where to add my select clause.(stored procedure) I need to be able to pass a value in the outbound

Abap Proxy and have the data returned from the Oracle DB.

Thanks for your help

Former Member
0 Kudos

check ur configuration--

as i said in my previous reply u have to follow JDBC structure....

refer this link

u need to create the data type as per JDBC std refer this link for the same.

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

chirag

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> 1.How can I call this from SAP R/3 Abap proxy? When I execute the outbound Service Interface via transaction SPROXY (test mode) I get the following message. RCVR_DETERMINATION">NO_RECEVIER_CASE_BE<

Possible issues:

1)This means no receiver could be found. Check your Receiver Determination. Activate and update cache.

2) Make sure your proxy configuration between R/3 and PI are working.

3) Have u specified business system properly at sender field in the Receiver Determination?

Former Member
0 Kudos

I think I am almost there, thanks for you help.

What if a stored procedure is not provided from the MS 2005 SQL side, is there another way I can query the database.

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> What if a stored procedure is not provided from the MS 2005 SQL side, is there another way I can query the database

Please provide your jdbc requirement now... we will see whether we can try at pi side itself ...

You can also try the below link for doing stored procedure

/people/arpil.gupta/blog/2008/11/03/workaround-for-jdbc-scenarios

Edited by: Baskar Gopal on Feb 18, 2011 12:49 PM

Former Member
0 Kudos

Just a simple query, no updating is required.

select f1 f2 f3....from ms_tbl where part = inbound-part

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> Just a simple query, no updating is required.

>>> select f1 f2 f3....from ms_tbl where part = inbound-part

Then you dont need sql stored procedure . We can easily handle at pi side.

Step 1) create data type for proxy as per your need

Step 2) Create the data type for jdbc request side as similar below ...

<stmt>

<Customers action="SQL_QUERY">

<access> SELECT f1,f2,f3 from ms_tbl WHERE PART ='$KEYFIELD$';</access>

<key>

<KEYFIELD>inbound-part</KEYFIELD>

</key>

</Customers>

</stmt>

Note: use constant to populate values for action and access field in mapping area.

Step 3)

create data type for the jdbc response side as below

<stmt_response>

<row>

<f1>

<f2>

<f3>

</row>

</stmt_response>

Step 4)

Then create Proxy response data type

Step 5) create message types and Service Interface.(outbound synchronous and inbound synchronous)

step 6)first map: Map step 1 messg type with step2 mesg type

step 7) second map: Map step 3 mesg type to step 4 mesg type

Integration Directory: configure Jdbc reciever. Rest are all same.

That's it.

Please follow this link ...

Jdbc structure :

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

Jdbc configure (comm channel)

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

Former Member
0 Kudos

Would the JDBC Lookup work the same way as the SELECT statement? If so, would it require a lot of changes. you have been a lot of help and I really do appreciate it.

Former Member
0 Kudos

Step 2) Create the data type for jdbc request side as similar below ...

<stmt>

<Customers action="SQL_QUERY">

<access> SELECT f1,f2,f3 from ms_tbl WHERE PART ='$KEYFIELD$';</access>

<key>

<KEYFIELD>inbound-part</KEYFIELD>

</key>

</Customers>

</stmt>

The DT in PI should look like this? Am I missing anything

Statement Element

Customer action Attribute

access Attribute

key Attribute

base_part_no Element

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> The DT in PI should look like this? Am I missing anything

NO. Just a sample ..

But you need action attribute and access and key element for sure. See the link i sent before.

Former Member
0 Kudos

Sorry I'm doing something wrong. My key field will be base_part_no. If i had the stored procedure I would have been done thanks to you

I have the following:

DT_JDBC_NEWS_PART_Request Complex Type

Statement Element 1..unbounded

Customers action Attribute optional

access Attribute optional

base_part_no Element 1

DT_JDBC_NEWS_PART_response Complex Type

Statement_response Element 1..unbounded

response_1 Element 1..unbounded

row Element DT_XYZ 1..unbounded

baskar_gopalakrishnan2
Active Contributor
0 Kudos

>> Statement Element 1..unbounded Just 1 occurence

>> Statement_response Element 1..unbounded Just 1 occurence

>> response_1 Element 1..unbounded -- not required Just ignore this line

Former Member
0 Kudos

Hi Baskar,

I am now getting a mapping error> EXCEPTION_DURING_EXECUTE

cannot create target element /nso:/MT_XYZ/response/row/part_no. Values missing in~

I checked all my mapping and it appears to be fine. under access to I need to list the fields? I have the fields listed in the response MT, and the MT name for the response is the same as the request except I added _response at the end

On the JDBC Request data types I have the following:

DT_JDBD_PART_Req

Statement

dbTableName

action - set to SQL_QUERY

access -Set to select partno, partname, start from table_xy where partno = '$part_no$'

key

part_no

Former Member
0 Kudos

Hi Baskar,

I am now getting a mapping error> EXCEPTION_DURING_EXECUTE

cannot create target element /nso:/MT_XYZ/response/row/part_no. Values missing in~

I checked all my mapping and it appears to be fine. under access to I need to list the fields? I have the fields listed in the response MT, and the MT name for the response is the same as the request except I added _response at the end

On the JDBC Request data types I have the following:

DT_JDBD_PART_Req

Statement

dbTableName

action - set to SQL_QUERY

access -Set to select partno, partname, start from table_xy where partno = '$part_no$'

key

part_no

Former Member
0 Kudos
Baskar Gopalakrishnan, amazing post men,,,, is the most complete about SQL_QUERY on the web..

But i have a question... tell me,, what´s the last secret ?

I have the request like you put it

<stmt>

   .....

   .....

</stmt>

the query extract info from the database, but the problem is that automatically search and put in the response Message Type the word RESPONSE, so put the information in the request Message Type adding the RESPONSE Word.

Example

1.   Request    ->  <stmt_req>

       Reponse   ->  <stmt_req_response>

  2- Request     -> <node_req>

      Response   -> <node_req_response>

So, doesn´t is important my Message Type... this take the request and add the word response.... so i have never got the mapping with my defined MT.

What is the secret men ?

Answers (5)

Answers (5)

rajasekhar_reddy14
Active Contributor
0 Kudos

This is very simple requirement you can use RFC/ABAP proxy in SAP R/3 to send data to Data base and use JDBC receiver adapter in Tragte side. JDBC receiver supports snyc calls..

search in sdn you will fine many links on the same

Former Member
0 Kudos

Some examples of JDBC adapter

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

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

various approaches possible in JDBC

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

former_member463616
Contributor
0 Kudos

Hi,

Please see the below links,it might be useful to you.

Step by Step JDBC Synchornous

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

/people/udo.martens/blog/2005/09/30/one-logical-system-name-for-serveral-bpm-acknowledgements

/people/prateek.shah/blog/2005/06/08/introduction-to-idoc-xi-file-scenario-and-complete-walk-through-for-starters

http://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cdded790-0201-0010-6db8-beb9...

/people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn

Regards,

Rajesh

Former Member
0 Kudos

This might help to start.

http://wiki.sdn.sap.com/wiki/display/XI/step%2bby%2bstep%2bRFC%2bto%2bJDBC%2bscenario

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

u need to create the data type as per JDBC std refer this link for the same.

http://help.sap.com/saphelp_nw04/helpdata/en/64/ce4e886334ec4ea7c2712e11cc567c/content.htm

search on SDN there are many JDBC blog available.

chirag

Edited by: Chirag Gohil on Feb 17, 2011 7:09 PM

Former Member
0 Kudos

Hi Jim,

Ref: http://www.saptechnical .com/Tutorials/XI/SOAP2JDBC/Page1.htm

Thanks,

Former Member
0 Kudos

There are a lot of great links people have posted, but I have not seen anything that starts with invoking an RFC or ABAP proxy on the SAP R/3 side, and using JDBC to query (no update required) a third party's Oracle database and return the results. I am new to the SYNC process within PI. Thanks for your help

Former Member
0 Kudos

Again - Best possible way to find out is just search on SDN.

There are blog available on SDn for RFC/Proxy u will even get step by step process....

At Oracle side either u need to do coding or u have to ask oracle developer to build a code.

U can ask more specific questions even after u r not getting any result on SDN.

weblog has most of the design as well as configuration steps to be performed on PI....

chirag

Edited by: Chirag Gohil on Feb 17, 2011 7:51 PM