TIBCO/BW6-CE/5 → SOAP exposed service fetching from Database and writing to CSV file

This is a post on Tibco Business Works 6 CE highlighting a SOAP exposed service which fetches data from a database table based on a passed input in request and then write the content to a csv file.

Tibco BW6-CE

• Pre-Requisite:

[i] Oracle DB XE installed locally and table created with Data inserted.

CREATE TABLE "<Schema>"."EMP" 
   ( "EMP_ID" NUMBER NOT NULL ENABLE, 
 "EMP_NAME" VARCHAR2(50 BYTE), 
 "CRT_UPD_DATE_TIME" TIMESTAMP (6) DEFAULT CURRENT_TIMESTAMP, 
  CONSTRAINT "EMP_PK" PRIMARY KEY ("EMP_ID")
   )

Put some records in the DB table. We have some dummy data in table.

Here we will query table, passing EMP_NAME=’ABC’ and it will fetch 2 records from DB.

[ii] Oracle Driver Installation in Tibco:

Download ojdbc6.jar from here:

Download ojdbc6.jar : ojdbc6 « o « Jar File Download (java2s.com)

Place it here:

<TIBCO_CE_HOME>\bwce\2.8\config\drivers\shells\jdbc.oracle.runtime\runtime\plugins\com.tibco.bw.jdbc.datasourcefactory.oracle\lib

eg. C:\DATA\Installed\Tibco\BWCE\bwce\2.8\config\drivers\shells\jdbc.oracle.runtime\runtime\plugins\com.tibco.bw.jdbc.datasourcefactory.oracle\lib

Open command prompt.

Go to <TIBCO_CE_HOME>\bwce\2.8\bin and execute command:

bwinstall oracle-driver

File → New → Project → BusinessWorks Application Module

Paste below content for “EmployeeSchema.xsd”

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.test.com/Employee/Schema"
            targetNamespace="http://www.test.com/Employee/Schema" elementFormDefault="qualified">
  <xsd:element name="empDetails">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="empDetailsReq" maxOccurs="unbounded">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="empId" type="xsd:string" minOccurs="0"/>
              <xsd:element name="empName" type="xsd:string" minOccurs="0"/>
              <xsd:element name="crtUpdDateTime" type="xsd:string" minOccurs="0"/>
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Paste the content for “Employee.wsdl”

<?xml version="1.0" encoding="UTF-8" ?>
<definitions targetNamespace="http://www.test.com/Employee" xmlns="http://schemas.xmlsoap.org/wsdl/"
             xmlns:tns="http://www.test.com/Employee"
             xmlns:scm="http://www.test.com/Employee/Schema" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
             xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/">
  <wsdl:types>
    <schema xmlns="http://www.w3.org/2001/XMLSchema">
       <import namespace="http://www.test.com/Employee/Schema" schemaLocation="../Schemas/EmployeeSchema.xsd"/>
    </schema>
        <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.test.com/Employee"><xsd:element name="response" type="xsd:string"></xsd:element></xsd:schema>
    </wsdl:types>
  <types>
   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:import namespace="http://www.test.com/Employee/Schema"
     schemaLocation="../Schemas/EmployeeSchema.xsd">
    </xsd:import>
   </xsd:schema>
  </types>
  <wsdl:message name="EmpDetailsRequest">
    <wsdl:part name="ReqPart" element="scm:empDetails"/>
  </wsdl:message>
  <wsdl:message name="EmpDetailsResponse">
    <wsdl:part name="RespPart" element="tns:response"/>
  </wsdl:message>
  <wsdl:portType name="EmpInsertPort">
    <wsdl:operation name="Insert">
      <wsdl:input message="tns:EmpDetailsRequest"/>
      <wsdl:output message="tns:EmpDetailsResponse"/>
    </wsdl:operation>
  </wsdl:portType>
</definitions>

Right click on WSDL and generate process with SOAP binding.

Delete the previous business process “Process.bwp” and rename the newly created Business Process to “EmpDetails.bwp”.

Next, we will create a HTTP connector that will help to expose as SSOAP service.

Put hard coded response ‘File Write Success’ which will go as a response back when SOAP service is invoked.

Inside project, click on “Module Properties” and go to properties. Need to configure the binding reference, if it is not showing. Need to browse and select the binding.

Restart BW6 and retry if needed.

Select Transport type as HTTP and refer the created connector.

Next, we will create a JDBC Connection to connect to Oracle DB.

Point the details of locally installed Oracle XE DB. Test the connection.

Next, we will select JDBC Query activity that will query the Database table EMP.

Go to properties tab for the JDBC Query activity. Browse and select the JDBC Connection created in previous step.

Put the SQL query: “SELECT * FROM <Schema>.<Table> WHERE EMP_NAME = ?” and also create a parameter “pEmpName” that will be used to pass the value of EMP_NAME to query from process flow. So, we will map the empName in request payload to the above parameter in next step.

We will add an Assign activity to create a variable that hold contents from DB fetch. The variable will be based on Employee schema. Use for-each to maintain the repeating element in the result set from DB fetch.

Put below content in schema “SingleString.xsd”. This schema is used to hold each rows’ data in comma separated text format from DB fetch in “SingleStringItem” repeating element.

<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns="http://www.singleString.com"
            targetNamespace="http://www.singleString.com" 
            elementFormDefault="qualified">
  <xsd:element name="SingleString">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="SingleStringItem" type="xsd:string" maxOccurs="unbounded"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

In .bwp put “TransformXml” and then “ParseXml”. After TransformXml it will generate transformed content in string format, that needs ParseXml to convert string back to xml format.

Create a Transformation folder inside project and put a XSLT file “DBOutToSingleString.xsl” with below content. Here we are removing the timezone offset from the crtUpdDateTime field, and then concating all 3 fields from DB table with comma as separator.

<xsl:stylesheet version="1.0" xmlns:mhdr="http://www.oracle.com/XSL/Transform/java/oracle.tip.mediator.service.common.functions.MediatorExtnFunction" xmlns:tns="http://www.singleString.com" xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20" xmlns:ns0="http://www.test.com/Employee/Schema" xmlns:xref="http://www.oracle.com/XSL/Transform/java/oracle.tip.xref.xpath.XRefXPathFunctions" xmlns:socket="http://www.oracle.com/XSL/Transform/java/oracle.tip.adapter.socket.ProtocolTranslator" xmlns:oracle-xsl-mapper="http://www.oracle.com/xsl/mapper/schemas" xmlns:dvm="http://www.oracle.com/XSL/Transform/java/oracle.tip.dvm.LookupValue" xmlns:oraxsl="http://www.oracle.com/XSL/Transform/java" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" exclude-result-prefixes=" xsd oracle-xsl-mapper xsi xsl ns0 tns mhdr oraext xp20 xref socket dvm oraxsl">
   <oracle-xsl-mapper:schema>
      <!--SPECIFICATION OF MAP SOURCES AND TARGETS, DO NOT MODIFY.-->
      <oracle-xsl-mapper:mapSources>
         <oracle-xsl-mapper:source type="XSD">
            <oracle-xsl-mapper:schema location="../Schemas/EmployeeSchema.xsd"/>
            <oracle-xsl-mapper:rootElement name="empDetails" namespace="http://www.test.com/Employee/Schema"/>
         </oracle-xsl-mapper:source>
      </oracle-xsl-mapper:mapSources>
      <oracle-xsl-mapper:mapTargets>
         <oracle-xsl-mapper:target type="XSD">
            <oracle-xsl-mapper:schema location="../Schemas/SingleString.xsd"/>
            <oracle-xsl-mapper:rootElement name="SingleString" namespace="http://www.singleString.com"/>
         </oracle-xsl-mapper:target>
      </oracle-xsl-mapper:mapTargets>
      <!--GENERATED BY ORACLE XSL MAPPER 12.2.1.4.0(XSLT Build 190828.0353.3300) AT [FRI OCT 27 15:24:15 IST 2023].-->
   </oracle-xsl-mapper:schema>
   <!--User Editing allowed BELOW this line - DO NOT DELETE THIS LINE-->
   <xsl:template match="/">
      <tns:SingleString>
         <xsl:for-each select="/ns0:empDetails/ns0:empDetailsReq">
            <tns:SingleStringItem>
               <xsl:value-of select="concat(ns0:empId,',',ns0:empName,',',substring-before(ns0:crtUpdDateTime,'+'))"/>
            </tns:SingleStringItem>
         </xsl:for-each>
      </tns:SingleString>
   </xsl:template>
</xsl:stylesheet>

After ParseXml, once we have the xml message, we will again add a second Assign activity that will create a variable that is based on SingleString schema. This will be used to finally join all rows from DB table, with new line separator using string-join.

Finally put a Write File activity to pass the concatenated message to a file.

After the first Assign and before the “TransformXml”, we also need to put a RenderXml to convert the XML payload to string format. This is required as the input and out to the TransformXml is text only.

In RenderXml activity, go to Input Editor, browse and point to root element in Employee schema. This will define a structure for the output from this activity. Once done, then we can do the mapping. Map output of Assign to the RenderXml structure.

Open the Endpoint URL appended with ?WSDL as below:

http://<HOST>:<PORT>/SOAPServiceBinding/empInsertPort/?wsdl

This will download the concrete WSDL. Using this create a SOAP UI SOAP project to test.

Out file generated with below content:

File content:

You can debug each step and check input and output values like the following. In case any error at any step you can debug too.

Thanks for visiting. Keep following my blog to get notification of more such blogs in future!!