How to Create a Simple Proxy Service with Boxcarring Using WSO2 ESB and WSO2 DSS

Lets start this post from scratch cause this needs some infrastructure such as MYSQL DB and sometimes you might want a REST client to send sample requests.

First create the DB required in MYSQL.

First create PERSONSDB database. Then crete below table structure. 

CREATE TABLE PERSONS
(
PERSONID int,
LASTNAME varchar(255),
FIRSTNAME varchar(255),
ADDRESS varchar(255),
CITY varchar(255)
);

Lets create the data service call EBS_MYSQL

Name - EBS_MYSQL

Driver - com.mysql.jdbc.Driver

URL - jdbc:mysql://localhost:3306/PERSONSDB?autoReconnect=true

User Name - your username
Password - your password

Now you need to create a Data Service in DSS. For that simply click on Create under Data Service in left pane.

Data Service Name - PERSONS_DataService

Tick on Enable Boxcarring

Click Next>,

Again click Next>

Now click finish and select Services List from the left pane.

From above view select newly created PERSONS_DataService and from inside that view find the Edit Data Service (XML Edit)

Select everything in that box and replace it with below configuration.

<data enableBoxcarring="true" name="PERSONS_DataService" serviceNamespace="http://ws.wso2.org/dataservice">
   <config id="default">
      <property name="carbon_datasource_name">EBS_MYSQL</property>
   </config>
   <query id="select_all_PERSONS_query" useConfig="default">
      <sql>SELECT PERSONID, LASTNAME, FIRSTNAME, ADDRESS, CITY FROM PERSONSDB.PERSONS</sql>
      <result element="PERSONSCollection" rowName="PERSONS">
         <element column="PERSONID" name="PERSONID" xsdType="xs:decimal"/>
         <element column="LASTNAME" name="LASTNAME" xsdType="xs:string"/>
         <element column="FIRSTNAME" name="FIRSTNAME" xsdType="xs:string"/>
         <element column="ADDRESS" name="ADDRESS" xsdType="xs:string"/>
         <element column="CITY" name="CITY" xsdType="xs:string"/>
      </result>
   </query>
   <query id="insert_PERSONS_query" useConfig="default">
      <sql>INSERT INTO PERSONSDB.PERSONS(PERSONID,LASTNAME,FIRSTNAME,ADDRESS,CITY) VALUES(?,?,?,?,?)</sql>
      <param name="PERSONID" ordinal="1" sqlType="DOUBLE"/>
      <param name="LASTNAME" ordinal="2" sqlType="STRING"/>
      <param name="FIRSTNAME" ordinal="3" sqlType="STRING"/>
      <param name="ADDRESS" ordinal="4" sqlType="STRING"/>
      <param name="CITY" ordinal="5" sqlType="STRING"/>
   </query>
   <operation name="insert_PERSONS_operation" returnRequestStatus="true">
      <call-query href="insert_PERSONS_query">
         <with-param name="FIRSTNAME" query-param="FIRSTNAME"/>
         <with-param name="ADDRESS" query-param="ADDRESS"/>
         <with-param name="CITY" query-param="CITY"/>
         <with-param name="LASTNAME" query-param="LASTNAME"/>
         <with-param name="PERSONID" query-param="PERSONID"/>
      </call-query>
   </operation>
   <operation name="select_all_PERSONS_operation" returnRequestStatus="true">
      <call-query href="select_all_PERSONS_query"/>
   </operation>
</data>


Thats it from the DSS side lets move to ESB and deploy below proxy service call DSS_Proxy.

<?xml version="1.0" encoding="UTF-8"?>
<proxy xmlns="http://ws.apache.org/ns/synapse"
       name="DSS_Proxy"
       transports="https,http"
       statistics="disable"
       trace="disable"
       startOnLoad="true">
   <target faultSequence="Fault_1_s">
      <inSequence>
         <property xmlns:m0="http://ws.wso2.org/dataservice"
                   name="address"
                   expression="//body/insert_persons_operation/address"/>
         <property xmlns:m0="http://ws.wso2.org/dataservice"
                   name="personid"
                   expression="//body/insert_persons_operation/personid"/>
         <log level="full">
            <property name="==============address==============="
                      expression="get-property('address')"/>
            <property name="==============personid==============="
                      expression="get-property('personid')"/>
         </log>
         <header name="Action" value="urn:begin_boxcar"/>
         <payloadFactory media-type="xml">
            <format>
               <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                                 xmlns:dat="http://ws.wso2.org/dataservice">
                  <soapenv:Header/>
                  <soapenv:Body>
                     <dat:begin_boxcar/>
                  </soapenv:Body>
               </soapenv:Envelope>
            </format>
            <args/>
         </payloadFactory>
         <call>
            <endpoint>
               <address uri="http://localhost:9765/services/PERSONS_DataService.SOAP11Endpoint/"/>
            </endpoint>
         </call>
         <property name="setCookieHeader" expression="$trp:Set-Cookie"/>
         <property name="Cookie"
                   expression="get-property('setCookieHeader')"
                   scope="transport"/>
         <log level="full">
            <property name="==============Cookie==============="
                      expression="get-property('setCookieHeader')"/>
         </log>
         <payloadFactory media-type="xml">
            <format>
               <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                                 xmlns:dat="http://ws.wso2.org/dataservice">
                  <soapenv:Header/>
                  <soapenv:Body>
                     <p:insert_PERSONS_operation xmlns:p="http://ws.wso2.org/dataservice">
                        <xs:PERSONID xmlns:xs="http://ws.wso2.org/dataservice">$2</xs:PERSONID>
                        <xs:LASTNAME xmlns:xs="http://ws.wso2.org/dataservice">ranawaka</xs:LASTNAME>
                        <xs:FIRSTNAME xmlns:xs="http://ws.wso2.org/dataservice">thushara</xs:FIRSTNAME>
                        <xs:ADDRESS xmlns:xs="http://ws.wso2.org/dataservice">$1</xs:ADDRESS>
                        <xs:CITY xmlns:xs="http://ws.wso2.org/dataservice">matara</xs:CITY>
                     </p:insert_PERSONS_operation>
                  </soapenv:Body>
               </soapenv:Envelope>
            </format>
            <args>
               <arg evaluator="xml" expression="get-property('address')"/>
               <arg evaluator="xml" expression="get-property('personid')"/>
            </args>
         </payloadFactory>
         <call>
            <endpoint>
               <address uri="http://localhost:9765/services/PERSONS_DataService.SOAP11Endpoint/"/>
            </endpoint>
         </call>
         <property name="Cookie"
                   expression="get-property('setCookieHeader')"
                   scope="transport"/>
         <log level="full">
            <property name="==============Cookie==============="
                      expression="get-property('setCookieHeader')"/>
         </log>
         <payloadFactory media-type="xml">
            <format>
               <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                                 xmlns:dat="http://ws.wso2.org/dataservice">
                  <soapenv:Header/>
                  <soapenv:Body>
                     <p:insert_PERSONS_operation xmlns:p="http://ws.wso2.org/dataservice">
                        <xs:PERSONID xmlns:xs="http://ws.wso2.org/dataservice">$2</xs:PERSONID>
                        <xs:LASTNAME xmlns:xs="http://ws.wso2.org/dataservice">ranawaka2</xs:LASTNAME>
                        <xs:FIRSTNAME xmlns:xs="http://ws.wso2.org/dataservice">thushara2</xs:FIRSTNAME>
                        <xs:ADDRESS xmlns:xs="http://ws.wso2.org/dataservice">$1</xs:ADDRESS>
                        <xs:CITY xmlns:xs="http://ws.wso2.org/dataservice">matara2</xs:CITY>
                     </p:insert_PERSONS_operation>
                  </soapenv:Body>
               </soapenv:Envelope>
            </format>
            <args>
               <arg evaluator="xml" expression="get-property('address')"/>
               <arg evaluator="xml" expression="get-property('personid')"/>
            </args>
         </payloadFactory>
         <call>
            <endpoint>
               <address uri="http://localhost:9765/services/PERSONS_DataService.SOAP11Endpoint/"/>
            </endpoint>
         </call>
         <log level="full"/>
         <header name="Action" value="urn:end_boxcar"/>
         <property name="Cookie"
                   expression="get-property('setCookieHeader')"
                   scope="transport"/>
         <log level="full">
            <property name="==============Cookie==============="
                      expression="get-property('setCookieHeader')"/>
         </log>
         <payloadFactory media-type="xml">
            <format>
               <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                                 xmlns:dat="http://ws.wso2.org/dataservice">
                  <soapenv:Header/>
                  <soapenv:Body>
                     <dat:end_boxcar/>
                  </soapenv:Body>
               </soapenv:Envelope>
            </format>
            <args/>
         </payloadFactory>
         <call>
            <endpoint>
               <address uri="http://localhost:9765/services/PERSONS_DataService.SOAP11Endpoint/"/>
            </endpoint>
         </call>
         <respond/>
      </inSequence>
   </target>
   <description/>
</proxy>
                             
If needed you can deploy below fault sequence for debugging purposes.

<?xml version="1.0" encoding="UTF-8"?>
<sequence name="Fault_1_s" xmlns="http://ws.apache.org/ns/synapse">
    <log level="full">
        <property name="===============this is ========" value="fault seq"/>
    </log>
</sequence>

Now simply send the below curl request to DSS_Proxy endpoint.

curl -H "Content-Type: application/xml" http://localhost:8280/services/DSS_Proxy --data "<body><insert_persons_operation><address>rahula</address><city>matara</city><firstname>thushara</firstname><lastname>ranawaka</lastname><personid>1</personid></insert_persons_operation></body>"

If you are using a rest client please configure it as below

Note: Using a rest client is not preferred because it will cache your previous request and will lead to unusual behaviour.

Now please browse the MYSQL DB and verify that 2 columns are there.

Reference
https://docs.wso2.com/display/DSS300/Boxcarring
http://harshcreationz.blogspot.com/2015/02/boxcarring-sample-with-wso2-dss-and.html