Input from ABAP form to JDBC via SAP-PI 7.31

[Former Member]

Hello Team,

I am working on synchronous scenario ECC ABAP Proxy to JDBC. In ECC, a form is designed to allow the user to enter the vendor ID and country.This

construct the request message from ECC and will be sent to database will SAP PI 7.3

I have to fetch details of Vendor from JDBC using the unique key Vendor ID and Country. These details as response then goes back to ECC and the user should be able to view the details of the vendor requested in the form.

1. Since the values of the primary key i.e. Vendor Id and Country are filled once the user fills it, the value is taken at runtime. What is the design concept in ESR for the request message particularly with the field formats.

2. In the ID part, what form does the select statement takes place in the receiver JDBC adapter considering the values of the unique key of the JDBC table are taken from form.

3. Any design and configuration inputs for ABAP proxy.

4. How the form will capture the response

Regards

Rebecca Alice

[Ryan-Crosby · Accepted solution]

Hi Rebecca,

You can build ESR structures like this to execute a synchronous proxy to JDBC query.  Whether you have separate structures for the proxy request/response and use maps is up to you but here is the basic JDBC structure you can setup:

Request -

Response -

You'll notice the _response at the end of the message type and node named Statement.  This is intentional because the adapter will be looking to place the contents of the query result in a node with that naming convention.  For the request the action would be mapped with a constant of 'SELECT' and for table it would simply be the database/table name you are querying as a constant as well.  The access data is just a list of fields that you want to select from the database and the key gives the field names for the key values as well as the values themselves on which to query.

Regards,

Ryan Crosby

[Former Member · Accepted solution]

Dear Ryan,

Thanks for the response .. Some of queries are still not cleared.

1. Since the values of the primary key i.e. Vendor Id and Country are filled once the user fills i.e. the value is taken at runtime, how to handle in proxy for runtime values.

2. In the ID part, what form does the select statement takes place in the receiver JDBC adapter considering the values of the unique key of the JDBC table are taken from form.Form is developed 
   by ABAP webdynpro .

3. What would be the Select statement inserted in the receiver JDBC adapter for the runtime values?

Regards

Rebecca

[markangelo_dihiansan · Accepted solution]

Hi Rebecca,

1. Since the values of the primary key i.e. Vendor Id and Country are filled once the user fills i.e. the value is taken at runtime, how to handle in proxy for runtime values.

You can approach the scenario modularly. Even if the screen is realtime, you can treat an input for vendor id and country as one transaction in PI e.g call a proxy class and wait for the response. Once you get the values, you can resume or call other transactions. The screen would still be receiving the results in realtime, but the PI transactions would be marked as completed or processed.

2. In the ID part, what form does the select statement takes place in the receiver JDBC adapter considering the values of the unique key of the JDBC table are taken from form.Form is developed by ABAP webdynpro .

The SQL statement that would be used would be the one's you have developed in ESR. The JDBC adapter would be there for connection and conversion details (convert the XML SQL to a SQL statement that the JDBC receiver understands and vice-versa for response).

3. What would be the Select statement inserted in the receiver JDBC adapter for the runtime values?

Same answer as number 2.

Regards,

Mark

[iaki_vila · Accepted solution]

Hi Rebbeca,

1. Since the values of the primary key i.e. Vendor Id and Country are filled once the user fills i.e. the value is taken at runtime, how to handle in proxy for runtime values.

You can think about two aproaches. If the vendor ID and country range dont change too much and the number is not to high, you can develop a scenario to take all the values replicating a similar table in the sender system, i.e a backgroung task all the weeks in a Proxy - JDBC scenario. If the values can change more or less frequentlly, you will need a scenario to take only the data requeried and it will need throwed in each call. May be an intermedium mix option would be better.

2. In the ID part, what form does the select statement takes place in the receiver JDBC adapter considering the values of the unique key of the JDBC table are taken from form.Form is developed by ABAP webdynpro .

You only need to know the database vendor in the target system, You or your basis team will need to install the drivers in the PI system in order to make the connection possuble.

3. What would be the Select statement inserted in the receiver JDBC adapter for the runtime values?

You will need to know the database structure. Later you have two options in PI to make the call:

With XML structure provided by SAP  ( Defining XML Documents for Message Protocol XML SQL Format - Advanced Adapter Engine - SAP Library). You can do Defining a SELECT Statement

or Defining an SQL_QUERY Statement

Hope this helps.

Regards.

[Former Member · Accepted solution]

Hi Mark and Inaki,

Now I am almost in verge of closing the query.

1. Can I collect the details from Oracle database using VIEW. Would that affect the select statement.

2. I assume this would be my Select statement.

   Select F1,F2,F3,F4 from database table VENDETL where VendID = (runtime value) and          VendCont = (runtime value).

   I am confused with the runtime value which I will get it from portal input. Can you correct the Select statement with the way to capture the runtime value in statement.

Regards

Rebecca

[iaki_vila · Accepted solution]

Hi Rebecca,

1. Can I collect the details from Oracle database using VIEW. Would that affect the select statement.

Yes, you can do it without any problem or difference.

2. I assume this would be my Select statement.


   Select F1,F2,F3,F4 from database table VENDETL where VendID = (runtime value) and          VendCont = (runtime value).

There is not any problem for the runtime value. You only need to map the runtime values in the corresponding values of theXML tags (ACCES - VendID column and so on)

In the XML format you can change the name of some tags and to put your DB colum names.

Check a example here

Regards-

[Former Member · Accepted solution]

HI Inaki and Team,

I am still not clear with the full Select statement that I will enter. Since the runtime value for VenID and VendCont can be any valuem what would be the exact Select statement.

Does the below statement holds good..

Select F1,F2,F3,F4 from database table VENDETL where VendID = " " and    VendCont = " ".
Does this double quotes after the field value accepts the runtime value.

Regards

Rebecca