Approaches Where JDBC Native SQL fails

[aashish_sinha]

Business Requirement

Requirement came to create a scenario to get the status of records posted to database and it should be something like acknowledgement of the records posted to Database System. The status returned to SAP PI should get updated in SAP Tables of ECC.

When customer was posting multiple records to Third Party Database System, no response was coming. We need to send some query parameters to SAP PI from ECC and use these parameters in the select query to be framed.

 Approach and References:

Our first approach was to create a dynamic sql and send it to database to execute directly using SAP PI. SQL was getting formed in an ABAP program and we were creating the SQL Statement depending upon some unique number and against that unique number database will return the message assigned (Success or Fail).

I have seen a few questions related with Native SQL in SDN and I thought to explore it how to do that. Later on reading extensively on SAP Help about JDBC Adapters and Native SQL, I came across it is not possible to call it through JDBC receiver adapter until or unless we use some tricky solutions.

SAP Help documents says

XML Document Format for the Message Protocol Native SQL Format
This protocol is primarily for test purposes only. Instead of an XML document format, a text is expected that represents any valid SQL statement.

I thought about solutions like

Solution 1: Write one Java code outside for getting required information from Third Party database and call that Java program using one RFC to fulfill our requirement. This solution is possible and we can use JCo calls from RFC using settings to be done in SM59. But managing this solution was a very tough approach and dependency was increased.

Solution 2:  This solution is about to create some dynamic query at runtime to execute SQL query directly on Database. For this approach I tried to use below blog without any success.

PreparedStatement with JDBC Receiver Adapter

Solution 3: Last but not least, the solution we thought of is to write one Java mapping and pass the sql to receiver JDBC as text, and get the response back and make one synchronous scenario to send it back to R/3 table. But these kind of Synchronous scenarios have their own problems.

Final Solution:

Our approach was to set the parameters in ECC and generate a proxy for it to send data to SAP PI. In the mapping, depending upon the incoming message (i.e. query framed in ABAP Program) we used JDBC lookup to fetch the records and mapped it to target RFC to post the values to a ECC table.

Development in SAP XI/PI

Create Source Data Type, Message Type and Service Interface.

Import Receiver RFC to your SWCV. And Create Service interface for it as Inbound Asynchronous.

Create Message Mapping:

 

Here in the mapping Title is and UDF for RFC lookup. Please write a code like this.

 UDF Code:

String Query = " ";

Channel channel = null;

DataBaseAccessor accessor = null;

DataBaseResult resultSet = null;

Query  =  "SELECT AcknowledgementDescription FROM  [TESTDataExchange].[ERP].[Transaction] WHERE TransactionName = 10 AND TransactionData.exist('//PlannedOrderNumber = "+message[0]+"') = 1 ORDER BY TransactionDateTime DESC " ;

try{

//Determine a channel, as created in the Configuration

channel = LookupService.getChannel("TESTDV","JDBC_RCV_TEST_P2P_PlannedOrderAck");

//Get a system accessor for the channel. As the call is being made to an DB, an DatabaseAccessor is obtained.

accessor = LookupService.getDataBaseAccessor(channel);

//Execute Query and get the values in resultset

resultSet = accessor.execute(Query);

for(Iterator rows = resultSet.getRows();rows.hasNext();){

Map rowMap = (Map)rows.next();

result.addValue((String)rowMap.get("AcknowledgementDescription") ) ;

}

}

catch(Exception ex){

result.addValue(ex.getMessage()) ;

}

finally{

try{

if (accessor!=null) accessor.close();

}

catch(Exception e){

result.addValue(e.getMessage()) ;

}

}

Create Operations Mapping

 

After all configurations in ESR (Mapping and Operations Mapping), we need to create required configurations in Integration Directory for this scenario.

Configurations in Integration Directory

First we need to create JDBC receiver for the lookup to work, it should be with same name you want to use in JDBC lookup UDF. Create one Receiver agreement for this.

For sender side, we are using proxy so we needn’t be using Sender agreement or sender communication channel. Again in receiver side, we have to use RFC Adapter, and receiver agreement for this receiver.

Later on, we need to create one Receiver Determination and Interface Determination for End to End systems.

Activate all configuration and Run proxy to see results.

I hope this will help people for their solutions.