Stored Procedure Execution and Database Insert, Update with Lookup Option

Requirement

Sometimes the stored procedure call is necessary in the data processing or data translation. Also sometime it is required to execute DML statement in database and it is important to know the status of execution for subsequent step. So the requirement is to insert or update the database table rows or to execute the stored procedure within the mapping.

Solution

The database Insert, Update and Execute is possible with System Accessor Java APIs of SAP PI. It is required to go with Java Mapping or to write an UDF.

Below is the code snippet for the same:

———————————————————————————————————————————-

// String Variable to hold the Request XML

String strDBSysRequest = “”;

// Channel Object with JDBC Receiver Channel

Channel channelDBSys = LookupService.getChannel(“BC_XYZ”, “JDBC_R_XYZ_LOOKUP”);

// System Accessor Object with JDBC Receiver Channel Object

SystemAccessor accessorDBSys = LookupService.getSystemAccessor(channelDBSys);

// Convert the Request XML String to Input Stream

InputStream isDBLookup = new ByteArrayInputStream(strDBSysRequest.getBytes());

// Create XML Payload object with request input stream

XmlPayload payload = LookupService.getXmlPayload(isDBLookup);

// Call System Accessor to process the Request data and return the response as XML Payload

XmlPayload result = (XmlPayload) accessorDBSys.call(payload);

// Convert the XML Payload into Response XML String

String strDBSysResponse = convertStreamToString(result.getContent());

———————————————————————————————————————————-

The String strDBSysRequest need to be prepared as below in case of Stored Procedure execution (exactly same as that of the one which is created for stored procedure execution through normal JDBC Receiver Channel):

<?xml version=”1.0″ encoding=”UTF-8″ standalone=”yes”?>

<ns2:SPValidate xmlns:ns2=”http://abcd.com/a“>

    <Statement>

        <SP_VALIDATE action=”EXECUTE”>

<table>SP_VALIDATE</table>

            <i_id isInput=”1″ type=”string”>JY4UbMhBkoP0501W</i_id>

            <i_card isInput=”1″ type=”string”>123123123123</i_card>

            <i_timestamp isInput=”1″ type=”TIMESTAMP”>14-07-2015 13:13:52.031</i_timestamp>

            <o_res_code isOutput=”1″ type=”integer” />

            <o_res_msg isOutput=”1″ type=”string” />

        </SP_VALIDATE>

    </Statement>

</ns2:SPValidate>

The sample response XML caputured in strDBSysResponse is as below:

<?xml version=”1.0″ encoding=”utf-8″?>

<ns2:SPValidate_response xmlns:ns2=”http://abcd.com/a“>

    <Statement_response>

        <o_res_code>0</o_res_code>

        <o_res_msg>Valid Data</o_res_msg>

    </Statement_response>

</ns2:SPValidate_response>

In case of database update and insert, corresponding XML structure need to prepared and pass as request XML Payload. Refer the link below for more information on SQL XML request structure:

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

The method convertStreamToString is used in above snippet and the code in this method is as below for the quick reference.

———————————————————————————————————————————-

public String convertStreamToString(InputStream in) {

      StringBuffer sb = new StringBuffer();

      try {

            InputStreamReader isr = new InputStreamReader(in);

            Reader reader = new BufferedReader(isr);

            int ch;

            while ((ch = in.read()) > -1)

                  sb.append((char) ch);

            reader.close();

      } catch (Exception exception) {

      }

      return sb.toString();

}

———————————————————————————————————————————-

Summary

So here the database interaction is with use of System Accessor and JDBC receiver channel. It is also possible to execute select statement with System Accessor and it is not required to create separate Database Accessor in case if select is also required to be executed in addition to insert/update/execute.

Advantages

• Avoid using of multiple Integration Flows
• Avoid writing java code to create java.sql.Connection and then doing the insert/update/execute

Disadvantages

• There is no control over database commit
• Not possible to rollback the database updates

Appreciate your feedback and comments on the usage of System Accessor for database interaction.