• About

On Technology

~ SOA and Integration blog

On Technology

Category Archives: Integration

Mulesoft : Create Cloudhub Notification

28 Tuesday Apr 2020

Posted by Padmarag Lokhande in Integration, MuleSoft, SOA

≈ Leave a comment

Tags

anypoint, cloudhub, esb, integration, muleesb, MuleSoft

We need different types of nontifications when creating an integration application using Mule or any other technology. We can of course use something like email or SMTP or any other specific API, but that ties the code strongly to the implementation. A better option is to use Mule Cloudhub’s Notifications which can generate different types of alerts with lot of details and also send email if required.

There are of course 2 parts to the solution –

A) Configuration and Setup in CloudHub

Follow instructions here to setup alert in CloudHub. These instructions work well, so I won’t repeat those.

B) Configuration and coding in Anypoint Studio

      1. Add Cloudhub Connector to your application. If you do not find it, download it from exchange inside your studio.
        1
      2. In your error handler flow, add a Cloudhub Create Notification component.
        2
      3. You may need to define a Cloudhub Configuration, Use your cloudhub username, password and environment details to do this.
      4. Now in the code open the file in XML, you can do this using the visual UI as well, but I prefer this in XML.
      5. Create a subflow to handle actual notifications
        3
        You can add any number of custom properties and then refer to them when creating your notification in CloudHub
      6. Call the subflow from your error handler section
        4

     

Advertisement

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Zapier – Beyond the basics – Part II : POST data to an external API

21 Saturday Apr 2018

Posted by Padmarag Lokhande in Integration, Zapier

≈ Leave a comment

Tags

Zapier

In the last blog, I explained how it is possible to make custom calls to external API’s to fetch data. In this blog I’ll show how you can post data to an external API via a POST action.

First we add a “Code by Zapier” step as usual and then map required inputs. refer to my last blog for details.

Next we go the the actual ode section and add something like this :


var line_amount = parseFloat(inputData.line_amount);
var tax_amount = parseFloat(inputData.tax_amount);
var customer_id = Number(inputData.customer_id);

console.log("line_amount : " + line_amount + ", tax_amount : " + tax_amount + " , customer_id : " + customer_id);

var invoice =
{
"Line": [{
"Description": inputData.description,
"Amount": line_amount,
"DetailType": "SalesItemLineDetail",
"SalesItemLineDetail": {
"ItemRef": {
"value": "3",
"name": "Product"
},
"UnitPrice": line_amount,
"Qty": 1,
"TaxCodeRef": {
"value": inputData.tax_code
}
}
}],
"TxnTaxDetail": {
"TxnTaxCodeRef": {
"value": "3"
},
"TotalTax": tax_amount,
"TaxLine": [{
"Amount": tax_amount,
"DetailType": "TaxLineDetail",
"TaxLineDetail": {
"TaxRateRef": {
"value": "3"
},
"NetAmountTaxable": line_amount
}
}]
},
"CustomerRef": {
"value": customer_id,
"name": inputData.customer_name
}
}

console.log("Invoice :" + JSON.stringify(invoice));

var options = {
headers:{
"Authorization": "Bearer " + inputData.access_token,
"Accept":"application/json",
"Content-Type":"application/json"
},
method : "POST",
body : JSON.stringify(invoice)

};

fetch('https://quickbooks.api.intuit.com/v3/company/123123123/invoice',options)
.then(function(res) {
console.log("response : " + res);
return res.json();
})
.then(function(json) {
var output = [{error: json.Fault, invoice:invoice, response:json}];
callback(null, output);
})
.catch(callback);

The important part to focus is the “options” passed in to fetch command where we specify the POST method, Headers and the JSON.stringify for body payload when body is of type JSON as is in the above case.

Lastly, Zapier uses fetch which uses a promise based design to process results, so we have the bunch of action->then->then code.

Enjoy!

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Zapier – Beyond the basics – Part I : Fetching data from external API

06 Friday Apr 2018

Posted by Padmarag Lokhande in Integration, Zapier

≈ 1 Comment

Tags

api, code, Zapier

Zapier is an excellent tool for Integration for Small and Medium Business. It fills 80% of the needs for most of the scenarios.

However there are a few scenarios which require you to go out of the box that zapier provides. One such scenario is interacting with an API which is not available in Zapier or which has exposed only limited operations in their official Zapier App.

Thankfully Zapier has provided the tools to do so. In these series of posts, I’ll be covering some common use-cases for Zapier.

The primary tool or component for interacting with external API is the “Code by Zapier” component.

You can add this as an Action step.

1

I typically choose “Javascript” which is essentially Node JS.

2

Any data from other steps that you want to pass in can be added here and it’ll be available under “inputData” object. So if you add a property “first_name” then it’ll be available in code as “inputData.first_name”

3

And under the code section, you can include your actual code like

fetch('http://example.com/')
  .then(function(res) {
    return res.json();
  })
  .then(function(json) {
    var output = {id: 1234, response: json};
    callback(null, output);
  })
  .catch(callback);

Zapier uses Node Fetch module to get data. It is a promise based library and requires some then, callback logic.

This is a simple example to call “GET” operation on any API. If you need to pass some headers, do it as :

var options = {
 headers:{
 "Authorization": "Basic Abcdesjjfjfj="
 } 
};
fetch('https://us2.api.mailchimp.com/3.0/campaigns/1a2s3d/content',options) 
    .then(function(res) {
        return res.json();
    })
    .then(function(json) {
        var html_content = json.archive_html;
        var output = {"content":html_content}; 
        callback(null, output);
    })
 .catch(callback);

In the next posts, we’ll go into some more complex scenarios including use of OAuth based API and POST using Zapier.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Importance of understanding your database

25 Thursday Jul 2013

Posted by Padmarag Lokhande in Integration, Uncategorized

≈ Leave a comment

Recently I was working on an integration project where the database used was MS SQL server and the integration platform was Oracle SOA Suite.

The service itself was quite simple – to fetch some data from SQL server and Enqueue it on JMS queue. We used DB Adapter for polling the database. We used the Delete strategy and the service was distributed on a cluster.

Once the ids were enqueued, there was a seperate EJB-based webservice which queried same database to create the canonical. We have used JPA Entity Beans for ORM. There is a particular query to get some extra information from a table which does not have foreign-key relation. The query used a single parameter – a string.

However we observed a huge performance issue for SQL server as well as the website hosted on the database. We observed 99% CPU usage.

It was our SQL DBA who found out the issue. The column in database was varchar, the index was based on same. However, the query parameter that got sent to the database was using nvarchar. This caused a full table scan and completely skipped the index.

The solution use “sendStringParametersAsUnicode” property of Weblogic SQL Server driver. By default everything gets sent as Unicode from JDBC Driver, using “sendStringParametersAsUnicode=false”, we made the driver send the parameter as varchar and immediately saw the difference. CPU usage was down to 1%.

This underscores the point that Frameworks and Engines abstract out a lot of features, but it is necessary to understand you database to make optimal use of it.

Reference – http://msdn.microsoft.com/en-us/library/ms378988.aspx

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Enterprise Integration Patterns (EIP) with OpenESB Part 2 : Dynamic Router

01 Friday Jun 2012

Posted by Padmarag Lokhande in BPEL, Integration, Java, SOA

≈ 3 Comments

This is second of the series Enterprise Integration Patterns (EIP) with OpenESB where we will cover Enterprise Integration Patterns using OpenESB.


According to Enterprise Integration Patterns,

The Dynamic Router is a Router that can self-configure based on special configuration messages from participating destinations.

Dynamic Router

We can realize this pattern in OpenESB using Dynamic Addressing which uses WS-Addressing to dynamically determine the service location at runtime.

To check this in action we need 3 Projects –

  • EJB Module implementing the Web Service – you can use any WSDL based web service, not necessary to use EJB.
  • BPEL – This will contain the actual code that determines the endpoint at runtime.
  • JBI / CASA – This is a composite application necessary to deploy the BPEL.

Step 1 – We create a web service with 2 operations – addition and subtraction. Both operations take 2 numbers as input and return result.
Here’s the xsd –

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    targetNamespace="http://xml.aurorite.com/schema/numberBase"
    xmlns:tns="http://xml.aurorite.com/schema/numberBase"
    elementFormDefault="qualified">

    <xsd:element name="NumbersAdditionRequest" type="tns:AdditionType"/>
    <xsd:element name="NumbersAdditionResponse" type="tns:OperationResultType"/>
    <xsd:element name="NumbersSubtractionRequest" type="tns:SubtractionType"/>
    <xsd:element name="NumbersSubtractionResponse" type="tns:OperationResultType"/>

    <xsd:complexType name="AdditionType">
        <xsd:sequence>
            <xsd:element name="number1" type="xsd:int"/>
            <xsd:element name="number2" type="xsd:int"/>
        </xsd:sequence>
    </xsd:complexType>

    <xsd:complexType name="SubtractionType">
        <xsd:sequence>
            <xsd:element name="number1" type="xsd:int"/>
            <xsd:element name="number2" type="xsd:int"/>
        </xsd:sequence>
    </xsd:complexType>

    <xsd:complexType name="OperationResultType">
        <xsd:sequence>
            <xsd:element name="result" type="xsd:int"/>
            <xsd:element name="processor" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:schema>

and here’s the wsdl –

<?xml version="1.0" encoding="UTF-8"?>
<definitions name="NumbersOperations"
    targetNamespace="http://websvcs.aurorite.com/wsdl/NumberOperationSvcs/NumbersOperations"
    xmlns="http://schemas.xmlsoap.org/wsdl/"
    xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:tns="http://websvcs.aurorite.com/wsdl/NumberOperationSvcs/NumbersOperations"
    xmlns:ns="http://xml.aurorite.com/schema/numberBase"
    xmlns:plnk="http://docs.oasis-open.org/wsbpel/2.0/plnktype"
    xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/">
    <types>
        <xsd:schema targetNamespace="http://websvcs.aurorite.com/wsdl/NumberOperationSvcs/NumbersOperations">
            <xsd:import namespace="http://xml.aurorite.com/schema/numberBase" schemaLocation="numberBase.xsd"/>
        </xsd:schema>
    </types>
    <message name="additionRequest">
        <part name="part1" element="ns:NumbersAdditionRequest"/>
    </message>
    <message name="additionResponse">
        <part name="part1" element="ns:NumbersAdditionResponse"/>
    </message>
    <message name="subtractionRequest">
        <part name="part1" element="ns:NumbersSubtractionRequest"/>
    </message>
    <message name="subtractionResponse">
        <part name="part1" element="ns:NumbersSubtractionResponse"/>
    </message>
    <portType name="NumbersOperationsPortType">
        <operation name="addition">
            <input name="input1" message="tns:additionRequest"/>
            <output name="output1" message="tns:additionResponse"/>
        </operation>
        <operation name="subtraction">
            <input name="input2" message="tns:subtractionRequest"/>
            <output name="output2" message="tns:subtractionResponse"/>
        </operation>
    </portType>
    <binding name="NumbersOperationsBinding" type="tns:NumbersOperationsPortType">
        <soap:binding style="document" transport="http://schemas.xmlsoap.org/soap/http"/>
        <operation name="addition">
            <soap:operation/>
            <input name="input1">
                <soap:body use="literal"/>
            </input>
            <output name="output1">
                <soap:body use="literal"/>
            </output>
        </operation>
        <operation name="subtraction">
            <soap:operation/>
            <input name="input2">
                <soap:body use="literal"/>
            </input>
            <output name="output2">
                <soap:body use="literal"/>
            </output>
        </operation>
    </binding>
    <service name="NumbersOperationsService">
        <port name="NumbersOperationsPort" binding="tns:NumbersOperationsBinding">
            <soap:address location="http://localhost:8080/NumbersOperationsService/NumberService"/>
        </port>
    </service>
    <plnk:partnerLinkType name="NumbersOperations">
        <!-- A partner link type is automatically generated when a new port type is added. Partner link types are used by BPEL processes. 
In a BPEL process, a partner link represents the interaction between the BPEL process and a partner service. Each partner link is associated with a partner link type.
A partner link type characterizes the conversational relationship between two services. The partner link type can have one or two roles.-->
        <plnk:role name="NumbersOperationsPortTypeRole" portType="tns:NumbersOperationsPortType"/>
    </plnk:partnerLinkType>
</definitions>

Here is the implementation of the webservice operations –

@WebService(serviceName = "NumbersOperationsService", portName = "NumbersOperationsPort", endpointInterface = "com.aurorite.websvcs.wsdl.numberoperationsvcs.numbersoperations.NumbersOperationsPortType", targetNamespace = "http://websvcs.aurorite.com/wsdl/NumberOperationSvcs/NumbersOperations", wsdlLocation = "META-INF/wsdl/NumberService/NumbersOperations.wsdl")
@Stateless
public class NumberService {

    Logger logger = Logger.getLogger("soa.aurorite.com.NumberService");

    public com.aurorite.xml.schema.numberbase.OperationResultType addition(com.aurorite.xml.schema.numberbase.AdditionType part1) throws UnknownHostException {
        logger.info("Running this service at " + InetAddress.getLocalHost().getHostName() + " on port 8080");
        com.aurorite.xml.schema.numberbase.OperationResultType result = new com.aurorite.xml.schema.numberbase.OperationResultType();
        result.setProcessor(InetAddress.getLocalHost().getHostName());
        try {
            result.setResult(part1.getNumber1() + part1.getNumber2());
        } catch (Exception e){
            result.setResult(0);
        }

        return result;
        //throw new UnsupportedOperationException("Not implemented yet.");
    }

    public com.aurorite.xml.schema.numberbase.OperationResultType subtraction(com.aurorite.xml.schema.numberbase.SubtractionType part1) throws UnknownHostException {
        logger.info("Running this service at " + InetAddress.getLocalHost().getHostName() + " on port 8080");
        com.aurorite.xml.schema.numberbase.OperationResultType result = new com.aurorite.xml.schema.numberbase.OperationResultType();
        result.setProcessor(InetAddress.getLocalHost().getHostName());
        try {
            result.setResult(part1.getNumber1() - part1.getNumber2());
        } catch (Exception e){
            result.setResult(0);
        }
        return result;
        //throw new UnsupportedOperationException("Not implemented yet.");
    }

}

Note that I’m returning the machine name in the response. This’ll help us to determine who actually processed a particular operation.

Now to the BPEL. We’ll create a simple process looking like this –
Dynamic Router BPEL

In the BPEL, we add these 2 namespace references –

xmlns:sref="http://docs.oasis-open.org/wsbpel/2.0/serviceref" 
xmlns:wsa="http://schemas.xmlsoap.org/ws/2004/08/addressing"
    <import namespace="http://docs.oasis-open.org/wsbpel/2.0/serviceref" location="ws-bpel_serviceref.xsd" importType="http://www.w3.org/2001/XMLSchema"/>
    <import namespace="http://schemas.xmlsoap.org/ws/2004/08/addressing" location="addressing.xsd" importType="http://www.w3.org/2001/XMLSchema"/>

And now the most important part – Adding the Service Endpoint Reference

<assign name="assign_endpoint">
    <copy>
                        <!--<from>ns2:doXslTransform('urn:stylesheets:wrap2serviceref.xsl', $EPRVariable.eprValue)</from>-->
        <from>
            <literal>
                <sref:service-ref>
                    <wsa:EndpointReference>
                        <wsa:Address>http://machine2:8080/NumbersOperationsService/NumberService</wsa:Address>
                        <wsa:ServiceName PortName="NumbersOperationsPort"
                                        xmlns:serv="http://websvcs.aurorite.com/wsdl/NumberOperationSvcs/NumbersOperations">serv:NumbersOperationsService
                        </wsa:ServiceName>
                    </wsa:EndpointReference>
                </sref:service-ref>
            </literal>
        </from>
        <to partnerLink="NumberService"/>
    </copy>
</assign>

The portname and Service name must match to the ones in target service.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Enterprise Integration Patterns (EIP) with OpenESB Part 1 : Content-Based Router

30 Wednesday May 2012

Posted by Padmarag Lokhande in BPEL, Integration, Java, SOA

≈ 1 Comment

This is first of the series Enterprise Integration Patterns with OpenESB where we will cover Enterprise Integration Patterns using OpenESB.


According to Enterprise Integration Patterns,

The Content-Based Router examines the message content and routes the message onto a different channel based on data contained in the message. The routing can be based on a number of criteria such as existence of fields, specific field values etc. When implementing a Content-Based Router, special caution should be taken to make the routing function easy to maintain as the router can become a point of frequent maintenance. In more sophisticated integration scenarios, the Content-Based Router can take on the form of a configurable rules engine that computes the destination channel based on a set of configurable rules.

Content Based Router

It can be achieved in OpenESB using “If” component.

In this example it simply checks for the value in “num:operation” element.

<soapenv:Envelope xsi:schemaLocation="http://schemas.xmlsoap.org/soap/envelope/ http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:num="http://xml.aurorite.com/schema/NumberOperationSchema">
  <soapenv:Body>
    <num:OperationRequest>
      <num:operation>ADDITION</num:operation>
      <num:data>
        <num:number1>20</num:number1>
        <num:number2>30</num:number2>
      </num:data>
    </num:OperationRequest>
  </soapenv:Body>
</soapenv:Envelope>     

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Can a SOA be designed with REST?

29 Tuesday May 2012

Posted by Padmarag Lokhande in BPEL, Integration, REST, SOA, Uncategorized

≈ Leave a comment

Tags

REST, soa

Recently I answered a question on stackoverflow.com asking can a SOA be designed with REST? I’m cross-posting the answer here.


 

At a high Level the answer is Yes, however not completely.

SOA requires thinking about the system in terms of

  • Services (well-defined business functionality)
  • Components (discrete pieces of code and/or data structures)
  • Processes (Service orchestrations. Generally using BPEL)

Being able to compose new higher level services or business processes is a basic feature of a good SOA. XML, SOAP based Web Services and related standards are good fit for realizing SOA.

Also SOA has a few accepted principles – http://en.wikipedia.org/wiki/Service-oriented_architecture#Principles

  • Standardized service contract – Services adhere to a communications agreement, as defined collectively by one or more service-description documents.
  • Service Loose Coupling – Services maintain a relationship that minimizes dependencies and only requires that they maintain an awareness of each other.
  • Service Abstraction – Beyond descriptions in the service contract, services hide logic from the outside world.
  • Service reusability – Logic is divided into services with the intention of promoting reuse.
  • Service autonomy – Services have control over the logic they encapsulate.
  • Service granularity – A design consideration to provide optimal scope and right granular level of the business functionality in a service operation.
  • Service statelessness – Services minimize resource consumption by deferring the management of state information when necessary.
  • Service discoverability – Services are supplemented with communicative meta data by which they can be effectively discovered and interpreted.
  • Service composability – Services are effective composition participants, regardless of the size and complexity of the composition.

A SOA based architecture is expected to have Service Definition. Since RESTful web services lack a definitive service definition (similar to wsdl), it is difficult for a REST based system to fulfill most of the above principles.

To achieve the same using REST, you’d need to have RESTful Web Services + Orchestration (possible using some lightweight ESB like MuleESB or Camel)

Please also see this resource – From SOA to REST


Adding this part as clarification for below comment –

Orchestration is required to compose processes. That’s what provides the main benefit of SOA.

Say you have a order processing application with operations like
–

  • addItem
  • addTax
  • calculateTotal
  • placeOrder

Initially you created a process (using BPEL) which uses these operations in sequence. You have clients who use this Composed Service. After a few months a new client comes who has tax exemption, then instead of writing new service, you could just create a new process skipping the addTax operation. Thus you could achieve faster realization of business functionality just by re-using existing service. In practice there are mutiple such services.

Thus BPEL or similar (ESB or routing) technology is essential for SOA. Without business use, a SOA is not really a SOA.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Enterprise Integration Patterns (EIP) with OpenESB

07 Wednesday Sep 2011

Posted by Padmarag Lokhande in Integration, Java, SOA

≈ 2 Comments

Tags

enterprise integration patterns openesb soa

This is a series which’ll cover Enterprise Integration Patterns (EIP) using OpenESB.

Part 1:Enterprise Integration Patterns (EIP) with OpenESB Part 1 : Content-Based Router

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Siebel CRM On Demand Web Services – Contact Search using SoapUI

11 Monday Apr 2011

Posted by Padmarag Lokhande in Camel, Integration, Java, Siebel, SOA

≈ 10 Comments

Tags

integration, siebel, soa, soapui, web service

Before we begin using Siebel CRM On Demand’s web services, we require the WSDL files. The WSDL files are specific to the account and can be downloaded by logging in to Siebel COD.

Create a new SoapUI project and add the WSDL as initial WSDL. SoapUI will create a new project with placeholder requests for all operations. Let’s open ContactQueryPage operation from SoapUI.

The endpoint path will be something like – https://secure-ausoxxxxx.crmondemand.com/Services/Integration
This path is  unique to your instance of Siebel COD.

Next lets remove the body of input and replace it with –

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
<soapenv:Header/>
<soapenv:Body>
     <con:ContactQueryPage_Input xmlns:con="urn:crmondemand/ws/ecbs/contact/" xmlns:quer="urn:/crmondemand/xml/Contact/Query">
        <quer:ListOfContact startrownum="0" pagesize="100" recordcountneeded="true">
            <quer:Contact searchspec="[ContactFirstName] LIKE 'John'">
                <quer:Id/>
                <quer:ContactFirstName/>
                <quer:ContactLastName/>
                <quer:AccountId/>
            </quer:Contact>
        </quer:ListOfContact>
    </con:ContactQueryPage_Input>
</soapenv:Body>
</soapenv:Envelope>

This results in Soap Fault response with Siebel error code SBL-ODU-01006 and message : “Internal Error: Session is not available. Aborting.”
This is because we still haven’t added username/password for authentication. There is another option to first get the session and then pass that value in header of the request. However I prefer to use the Stateless version above, as it separates the responsibility of handling session.

Let’s make 2 changes to fix this error.
1) Replace first line of the SOAP request with –

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:wsse="http://schemas.xmlsoap.org/ws/2002/07/secext">

2) Add these lines just below the SOAP Envelope element –

<soapenv:Header>
   <wsse:Security>
      <wsse:UsernameToken>
         <wsse:Username>your_siebel_username</wsse:Username>
         <wsse:Password Type="wsse:PasswordText">your_siebel_password</wsse:Password>
      </wsse:UsernameToken>
   </wsse:Security>
</soapenv:Header>

What we’ve done here is added WS-SECURITY headers to the SOAP request.
Now the request should run just fine.

Note –
1) You need to add any fields you need to the request before they can be returned by Siebel CRM. e.g., <ContactFirstName/>
2) Its better to confirm data actually exists by logging through web-console.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...

Call Oracle PL/SQL procedure with XMLType from Java using JDBC

11 Friday Mar 2011

Posted by Padmarag Lokhande in Database, Integration, Java, Oracle

≈ 2 Comments

Tags

jdbc, oracle, oracleaq, plsql, stored procedure, xmltype

Recently I needed to call a stored procedure which had Oracle’s XMLType as IN and OUT parameters.

The first thing to do is add xdb.jar and xmlparserv2.jar file to your application lib. These contain the required class files for Oracle XML API. the jars can be found under your installation of oracle client lib folders. Also don’t forget to add oracle jdbc driver files – ojdbc6 or ojdbc14.

The code I setup was this –

XMLType reqInXml;
XMLType reqOutXML;
String atpInStr = "";
OpResponse output = null;

try {
   reqInStr = jaxbMarshalRequestToString(input);
   System.out.println("Input : " + reqInStr);
} catch (JAXBException ex) {
   logger.log(Level.SEVERE, null, ex);
} catch (IOException ex) {
   logger.log(Level.SEVERE, null, ex);
}

//input.getHeader().setNotes("THIS VALUE RETURNED BY FACADE : " + XXDS);
try {
   Connection con = XXDS.getConnection();
   //The IN parameter for stored proc is Oracle XDB XMLType
   reqInXml = XMLType.createXML(con, reqInStr);
   OracleCallableStatement stmt = (OracleCallableStatement) con.prepareCall("call DEMO_PROC.ProcessXMLRequest(?, ?, ?, ?)");
   stmt.setObject(1, reqInXml);

   //set out parameters
   stmt.registerOutParameter (2, OracleTypes.OPAQUE,"SYS.XMLTYPE");
   stmt.registerOutParameter(3, Types.INTEGER);
   stmt.registerOutParameter(4, Types.VARCHAR);

   stmt.executeQuery();
   int resultCode = stmt.getInt(3);
   String resultMsg = stmt.getString(4);
   System.out.println("result code : " + resultCode);
   System.out.println("result msg : " + resultMsg);
   if (resultCode == 101 || resultCode == 100){
      reqOutXML = XMLType.createXML(stmt.getOPAQUE(2));
      System.out.println("Output from ERP :" + reqOutXML.getStringVal());
      output = jaxbUnmarshalFromString(reqOutXML.getStringVal());
   }
}
....

This gave me “java.lang.ClassCastException: com.sun.gjc.spi.jdbc40.ConnectionHolder40 cannot
be cast to oracle.jdbc.OracleConnection” Exception. The connection was being returned from GlassFish JDBC ConnectionPool and was instance of OracleConnectionPooldataSource.
The FIX proved tricky, but in the end it was simple –
Do this –

OracleConnection oraCon = con.unwrap(OracleConnection.class);
//The IN parameter for stored proc is Oracle XDB XMLType
atpInXml = XMLType.createXML(oraCon, atpInStr);

Pass the cast instance of OracleConnection to the XMLType API.

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to email a link to a friend (Opens in new window)
  • Click to print (Opens in new window)

Like this:

Like Loading...
← Older posts

Subscribe

  • Entries (RSS)
  • Comments (RSS)

Archives

  • April 2020
  • February 2019
  • April 2018
  • July 2015
  • July 2013
  • October 2012
  • June 2012
  • May 2012
  • September 2011
  • April 2011
  • March 2011
  • December 2010
  • August 2010

Categories

  • Camel
  • Database
  • Devops
    • Amazon AWS
    • Docker
    • Kubernetes
  • Integration
  • Java
  • JMS
  • MuleSoft
  • Oracle
  • Siebel
  • SOA
    • BPEL
    • REST
  • Uncategorized
  • Zapier

Meta

  • Register
  • Log in

Blog at WordPress.com.

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • On Technology
    • Already have a WordPress.com account? Log in now.
    • On Technology
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...
 

    %d bloggers like this: