Integrating Office applications with Salesforce.com - Part III

It has been a while since my last post, more than time to continue with the Integrating Office applications with Salesforce.com series. Today we’ll explain in detail with many several concrete examples, how to query and update your Salesforce.com data through XQuery.

The idea is to access Salesforce.com through its web service interface. But before looking into the Salesforce.com specifics, let’s first explain how easy it is to invoke web services from within XQuery using the DataDirect Integration Suite.

The function ddtek:wscall enables access to any web service over SOAP. ddtek:wscall has two parameters. First a structure with technical information specifying which and how the web service should be accessed, e.g. the address, credentials, proxy information, etc. The payload is the second parameter, in a format typically defined by the Web Services Description Language (WSDL). An overloaded version of ddtek:wscall has than additional parameter, it allows to specify the SOAP header. Finally the function ddtek:wscall returns an XML document with the web service’s response.

For example, assume a zip code web service. One of the operations could be getCityByZIP, which can be easily invoked as follows.[cc lang="xquery"]ddtek:wscall( ,

01730

)[/cc]As the parameters are all XML, you can of course call the web service more dynamically. Assume we get a document with a list of ZIP codes and for each we like to get the city details.[cc lang="xquery"]for $zip in fn:doc("zipcodes.xml")/cities/city/@ZIP return ddtek:wscall( ,

{$zip}

)[/cc]

We’ve learned how to access web services, let’s now bring this into practice with Salesforce.com. Salesforce.com offers two APIs, Force.com Enterprise and Force.com Partner. The former offers a strongly typed representation of your organization’s data, the latter is loosely-typed. The Force.com Web Services API Developer's Guide explains the differences in detail. For our examples here, we use the Force.com Partner API.

A Salesforce.com application must first log in to the server to start a session. This session is identified by a session id, and allows to query and update your organization’s data. Rather than repeating all these web service invocations using ddtek:wscall in our XQueries, we’ll abstract those technical details in a library. The sflib.xq library offers three functions.

• sf:login – allows to login to salesforce with a specific user names and password. Returns a session id
• sf:query – executes a query, using the Salesforce Object Query Language (SOQL)
• sf:create – creates a new object in your Salesforce.com system

This example library doesn’t aim to be a complete XQuery library for accessing Salesforce.com. It is sufficient for our current exercise, and you may consider it as a starting point for your own development projects. [cc lang="xquery"]module namespace sf = "urn:partner.soap.sforce.com";

declare namespace tns = "urn:partner.soap.sforce.com"; declare namespace ens = "urn:sobject.partner.soap.sforce.com";

declare variable $sf:compression := "gzip";

declare function sf:login($url as xs:string, $user as xs:string, $pwd as xs:string) { ddtek:wscall( ,

,

{$user} {$pwd}

) };

declare function sf:query($id as xs:string, $url as xs:string, $query as xs:string) { ddtek:wscall( ,

{$id} ,

{$query}

) };

declare function sf:create($id as xs:string, $url as xs:string, $sObjects as element(*, xs:anyType)*) { ddtek:wscall( ,

{$id} , {$sObjects} ) };[/cc]In the following example, we lookup information for an Account with a given name. First sf:login starts a Salesforce.com session. Subsequently the session id is used to query the Salesforce.com system.[cc lang="xquery"]import module namespace sf = "urn:partner.soap.sforce.com" at "sflib.xq";

declare namespace ens = "urn:sobject.partner.soap.sforce.com";

declare variable $serverUrl := "https://www.salesforce.com/services/Soap/u/9.0"; declare variable $user := "XXXXXXXX"; declare variable $pwd := "XXXXXXXX";

declare variable $name as xs:string external;

(: Login to Salesforce.com to get the session id :) let $login := sf:login($serverUrl, $user, $pwd) return (: Query the Accounts:) sf:query($login//sf:sessionId, $login//sf:serverUrl, fn:concat("SELECT id, Name, Phone, BillingStreet, BillingCity, BillingState, BillingCountry FROM Account where Name = '", $name, "'"))[/cc]Assuming we’re looking up the Account with name "GreatCompany" we could get a result as follows.[cc lang="xquery"]

true

Account 0018000000NfSpOAAV 0018000000NfSpOAAV GreatCompany 123-456-789 1st Avenue 123 San Francisco CA 94087 United States

1

[/cc]And as we’re in XQuery-world, we can easily transform the data in a more readable format... [cc lang="xquery"]import module namespace sf = "urn:partner.soap.sforce.com" at "sflib.xq";

declare namespace ens = "urn:sobject.partner.soap.sforce.com";

declare variable $serverUrl := "https://www.salesforce.com/services/Soap/u/9.0"; declare variable $user := "XXXXXXXX"; declare variable $pwd := "XXXXXXXX";

declare variable $name as xs:string external;

(: Login to Salesforce.com to get the session id :) let $login := sf:login($serverUrl, $user, $pwd) (: Query the Accounts:) let $result := sf:query($login//sf:sessionId, $login//sf:serverUrl, fn:concat("SELECT id, Name, Phone, BillingStreet, BillingCity, BillingState, BillingCountry FROM Account where Name = '", $name, "'")) let $account := $result/sf:queryResponse/sf:result/sf:records return

{$account/ens:Phone/text()} {$account/ens:BillingStreet/text()} {$account/ens:BillingCity/text()} {$account/ens:BillingState/text()} {$account/ens:BillingCountry/text()} [/cc]With the following result.[cc lang="xquery"] 123-456-789 1st Avenue 123 San Francisco CA 94087 United States [/cc]

Let’s now look at a more complex example to conclude this post. Here we will not only query the Salesforce.com database, but also perform updates.

• First we lookup a specific account
• If the account exists we only create a trip report
• If the account does not exist we create the account, the contact person and save the trip report

Clearly not realistic in a real world scenario, but to keep the example fairly simple all the account, contact and trip report data is stored in variables. [cc lang="xquery"]declare namespace ens = "urn:sobject.partner.soap.sforce.com";

declare variable $serverUrl := "https://www.salesforce.com/services/Soap/u/9.0"; declare variable $user := " XXXXXXXX"; declare variable $pwd := " XXXXXXXX";

declare variable $name := "GreatCompany"; declare variable $description := "This is a great company"; declare variable $date := "2008-12-31"; declare variable $contact := "Roxy"; declare variable $contact-email := "joe.roxy@greatcompany.com"; declare variable $phone := "123-456-789"; declare variable $street := "1st Avenue 123"; declare variable $city := "San Francisco"; declare variable $state := "CA 94087"; declare variable $country := "United States"; declare variable $note := "Our first trip was great. We have scheduled a follow up call to answer the open questions.";

{ (: Login to Salesforce.com to get the session id :) let $login := sf:login($serverUrl, $user, $pwd) return (: Check if the Account exists already :) let $response := sf:query($login//sf:sessionId, $login//sf:serverUrl, fn:concat("SELECT id, Name FROM Account where Name = '", $name, "'")) (: Get the Id of the returned Account. Get the first one, as SalesForce returns it twice in the result :) let $accountId := ($response//ens:Id)[1]/text() return if ($accountId) then (: Account exists, only save the trip report information :) sf:create($login//sf:sessionId, $login//sf:serverUrl,

Note {$accountId} {"Trip report", $date} {$note} ) else (: Account doesn't exist. Create the Account and save the trip report :) let $response1 := sf:create($login//sf:sessionId, $login//sf:serverUrl,

Account Prospect {$name} {$description} {$phone} {$street} {$city} {$state} {$country} ) let $accountId := $response1//sf:id/text() return( sf:create($login//sf:sessionId, $login//sf:serverUrl,

Contact {$accountId} {$contact} {$contact-email} ), sf:create($login//sf:sessionId, $login//sf:serverUrl,

Note {$accountId} {"Trip report", $date} {$note} ) ) }[/cc]After executing this query, logging in our Salesforce.com system we find the account, contact and trip report. Executing the query a second time, as the account exists already, would only create an additional trip report.

We have learned today how to query and update your Salesforce.com data, which opens a complete new world for your business applications. And remember the previous post on querying Microsoft Word documents. In the next and last post of this series we will combine both, reading data out of a word document and subsequently updating Salesforce.com.