Boxcarring Sample with WSO2 DSS and WSO2 ESB

Boxcarring is one of well known function of WSO2 DSS. Boxcarring allow user to execute multiple queries on a database tables at once. So the failure of one operation will lead to rollback all the other database operation which is a very useful feature in most integration scenarios. In this blog post, I'm going to explain how user can work with boxcarring from WSO2 ESB. Thanks of a scenario where we use WSO2 ESB to insert data to multiple table once in proxy service which should support failure of one operation to rollback entire all executed queries. So we need to integrate WSO2 ESB with WSO2 DSS to enable this feature. Make sure you are using WSO2 DSS 3.2.2.

In this sample proxy service in WSO2 ESB insert data into two different tables called EMPLOYEE and EMPLOYEE_A. If one insertion failed, the whole operation need to be rollback. In boxcarrying, DSS identifies the incoming request belongs to particular client from session id. Simply session id is the parameter which DSS queuing the operations to be executed. 

begin_boxcar - when this Operation is execute, it returns the session id which need to set in next set of database operation which call to the DSS endpoint.

end_boxcar - When this operation is execute, it will execute all the database operations waiting for same session on database.

Sample ESB config

<proxy name="DSS_Proxy"

          transports="https http"

          startOnLoad="true"

          trace="disable">

      <description/>

      <target faultSequence="Fault_1_s">

         <inSequence>

            <property xmlns:m0="http://ws.wso2.org/dataservice"

                      name="name"

                      expression="//m0:op_employee/m0:Name_param"/>

            <property xmlns:m0="http://ws.wso2.org/dataservice"

                      name="age"

                      expression="//m0:op_employee/m0:Age_param"/>

            <log level="full">

               <property name="==============ANME==============="

                         expression="get-property('name')"/>

               <property name="==============AGE==============="

                         expression="get-property('age')"/>

            </log>

            <enrich>

               <source type="body" clone="true"/>

               <target type="property" property="FirstBody"/>

            </enrich>

            <property name="messageType" value="application/xml" scope="axis2"/>

            <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:9764/services/DSS_Insert_Sample_ORg/"/>

               </endpoint>

            </call>

            <property name="setCookieHeader" expression="$trp:Set-Cookie"/>

            <property name="Cookie"

                      expression="get-property('setCookieHeader')"

                      scope="transport"/>

            <property name="OUT_ONLY" value="true"/>

            <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:op_employee xmlns:p="http://ws.wso2.org/dataservice">

                           <xs:Name_param xmlns:xs="http://ws.wso2.org/dataservice">$1</xs:Name_param>

                           <xs:Age_Param xmlns:xs="http://ws.wso2.org/dataservice">$2</xs:Age_Param>

                        </p:op_employee>

                     </soapenv:Body>

                  </soapenv:Envelope>

               </format>

               <args>

                  <arg evaluator="xml" expression="get-property('name')"/>

                  <arg evaluator="xml" expression="get-property('age')"/>

               </args>

            </payloadFactory>

            <call>

               <endpoint>

                  <address uri="http://localhost:9764/services/DSS_Insert_Sample_ORg/"/>

               </endpoint>

            </call>

            <property name="setCookieHeader" expression="$trp:Set-Cookie"/>

            <property name="Cookie"

                      expression="get-property('setCookieHeader')"

                      scope="transport"/>

            <property name="OUT_ONLY" value="true"/>

            <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:op_employee_a xmlns:p="http://ws.wso2.org/dataservice">

                           <xs:Name_param xmlns:xs="http://ws.wso2.org/dataservice">$1</xs:Name_param>

                           <xs:Age_Param xmlns:xs="http://ws.wso2.org/dataservice">$2</xs:Age_Param>

                        </p:op_employee_a>

                     </soapenv:Body>

                  </soapenv:Envelope>

               </format>

               <args>

                  <arg evaluator="xml" expression="get-property('name')"/>

                  <arg evaluator="xml" expression="get-property('age')"/>

               </args>

            </payloadFactory>

            <call>

               <endpoint>

                  <address uri="http://localhost:9764/services/DSS_Insert_Sample_ORg/"/>

               </endpoint>

            </call>

            <log level="full"/>

            <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:9764/services/DSS_Insert_Sample_ORg/"/>

               </endpoint>

            </call>

            <respond/>

         </inSequence>

      </target>

   </proxy>

Sample Data Service

<data enableBoxcarring="true" name="DSS_Insert_Sample_ORg">

   <config id="DSS_5">

      <property name="driverClassName">com.mysql.jdbc.Driver</property>

      <property name="url">jdbc:mysql://localhost:3306/test</property>

      <property name="username">root</property>

      <property name="password">root</property>

   </config>

   <query id="Q_6" useConfig="DSS_5">

      <sql>INSERT into EMPLOYEE(name,age) VALUES(?,?)</sql>

      <param name="Name_param" sqlType="STRING"/>

      <param name="Age_Param" sqlType="STRING"/>

   </query>

   <query id="Q_5" keyColumns="null" returnGeneratedKeys="true" useConfig="DSS_5">

      <sql>INSERT into EMPLOYEE_A(name,age) VALUES(?,?)</sql>

      <param name="Name_param" sqlType="STRING"/>

      <param name="Age_param" sqlType="STRING"/>

   </query>

   <operation name="op_employee" returnRequestStatus="true">

      <call-query href="Q_5">

         <with-param name="Name_param" query-param="Name_param"/>

         <with-param name="Age_param" query-param="Age_param"/>

      </call-query>

   </operation>

   <operation name="op_employee_a" returnRequestStatus="true">

      <call-query href="Q_6">

         <with-param name="Name_param" query-param="Name_param"/>

         <with-param name="Age_Param" query-param="Age_Param"/>

      </call-query>

   </operation>

</data>

References

[1] - https://docs.wso2.com/display/DSS321/Boxcarring+Sample