on 02-17-2011 12:50 PM
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
>> 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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
>> 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?
>> 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
>> 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
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
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
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
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
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 ?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
/people/sameer.shadab/blog/2005/10/24/connecting-to-ms-access-using-receiver-jdbc-adapter-without-dsn
Regards,
Rajesh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.