PHP Language Binding Guide For The Connection Cloud Web Services

(1)

PHP Language Binding Guide

For

The Connection Cloud

Web Services

(2)

Table Of Contents

Overview ... 3

Intended Audience ... 3

Prerequisites ... 3

Term Definitions ... 3

Introduction ... 4

What’s Required ... 5

Language Binding ... 5

WSDL File ... 5

Generating the PHP Class ... 5

Downloading the PHP Class ... 6

Creating a Connection ... 6

Closing a Connection ... 7

Various Query Methods ... 7

Stored Queries ... 7

Dynamic Queries ... 8

Query Execution ... 9

Result Collection ... 10

Error Handling ... 11

Appendix ... 11

A: List of Methods ... 11

B: Full Sample Code ... 12

(3)

Overview

As the number of cloud applications increases, so does the amount of data stored in the cloud. More and more application developers are looking for ways to integrate the vast amounts of cloud data into their applications but, of course, this has been a challenge until now. The Connection Cloud is a platform that allows seamless integration of data from any data source by providing language bindings for

application developers. This document outlines the components needed to integrate any data into an application written in PHP.

Intended Audience

This document is for PHP developers who need to integrate cloud data or data “mash-‐ups” into their applications. The Connection Cloud makes this possible without requiring custom query syntax, custom authentication code, or specialized drivers. This applies to cloud application developers and website designers using WordPress or any other PHP-‐enabled platform. Basic understanding of Web services, SOAP & WSDL, and database query operations is required.

You will need access to the items listed in the Prerequisites section before following the instructions in this document.

Prerequisites

Before attempting to use the Connection Cloud capabilities in an application, please be sure the following software and credentials are available:

• IDE/Compiler — An Integrated Development Environment such as Eclipse to

support PHP development, or a simple command-‐line compiler.

• Connection Cloud PHP Class — Source for the PHP Binding class. You can

download the PHP Binding class from www.connectioncloud.com.

• Connection Cloud Login Credentials — The credentials to access your

Connection Cloud account. Register for a free account at

www.connectioncloud.com.

• Target Data Source Credentials — The credentials to access the cloud data

your application needs to integrate. For example, you may need your Salesforce.com username, password, and security token.

Term Definitions

This document uses the following terms:

• Cloud Data Source — Data stored in a cloud application’s underlying

database such as Salesforce or Facebook data.

• Web Service — A method of communication between applications and

devices over the Web using standard and customized operations that allow interactivity between a client and server.

• SOAP — Simple Object Access Protocol is the protocol used in Web services

(4)

more information refer to the PHP manual pages:

http://php.net/manual/en/class.soapclient.php

• WSDL — Web Services Description Language is an XML-‐based language used

for describing the functionality offered by a Web service.

• API — Application Programming Interface is the specification intended as an

interface by software components to communicate with each other. This is the most widely used mechanism provided by cloud applications to access the underlying data they store. An API may include specifications for routines, data structures, object classes, and variables.

• Database Query — An operation to manipulate data in a database

management system. This may include fetching, updating, and/or deleting data.

• Query Language — Syntax used to create database queries. Different

databases and cloud applications may have vastly diverse query language syntax.

• SQL — Structured Query Language is the most widely used query language

to manipulate and fetch data in database management systems. Also referred to as ANSI SQL due to being a standard recognized by the American National Standards Institute.

• WSQL — Web Services Query Language is the native query language of the

Connection Cloud. It is a superset of ANSI SQL and includes the ability to call web service operations. For more information on syntax and features please visit the WSQL help page:

https://connectioncloud.atlassian.net/wiki/display/wsql/Home

Introduction

The complexity of data and data integration for application developers increases with the exponential growth of cloud applications. Each cloud data source can expose and require an authentication method, an API style such as SOAP or REST, a native query language with varying levels of functionality (SOQL, FQL, YQL, etc.), and may even release API updates with no backward compatibility.

This has led to many “silos of data” since getting access to the underlying data varies from application to application. Furthermore, not having an ANSI SQL interface to these cloud “databases” increases the challenges involved with integrating multiple data sources into a single application. Most developers have had to first solve the data integration challenge before focusing on core application development tasks. This has stifled the rapid application development path most software professionals are accustomed to.

The Connection Cloud hooks up any development language, application, or

JDBC/ODBC compatible tool to cloud data sources just as easily as if the data were in a local, standard SQL database. In essence, the Connection Cloud allows

(5)

integrate cloud data into their applications with ease, using one interface and one unified query language, should use this document.

What’s Required

To access the Connection Cloud Web services you need a Connection Cloud account. Register for free at www.connectioncloud.com and use the username and password selected here when connecting to the Connection Cloud Web services. The

Connection Cloud platform does not grant anonymous access.

After you log into the Connection Cloud workbench, navigate to the Channels page and activate the desired cloud data sources. You can choose from sources like Salesforce.com, Facebook, or NetSuite. To set up credentials for each data source please refer to the Connection Cloud help pages/FAQ.

Note: some cloud data sources require a certain subscription level to provide full API access. For instance, Salesforce.com only provides API access to the underlying data when subscribed to the Enterprise or Unlimited editions of the platform. Please be mindful of such restrictions when looking for access to cloud data.

Language Binding

The PHP language binding for the Connection Cloud utilizes the SOAP Web services exposed by the Query Service API. The built-‐in support for SOAP programming in PHP makes it an ideal language for developing applications using the Connection Cloud. The platform also supplies an interface class that eliminates the need for creating native SOAP calls, thus making the application code easier to write and follow. The following sections describe the various methods for utilizing the Connection Cloud Web services.

WSDL File

The PHP built-‐in SOAP programming capabilities use the WSDL and Schema files available at this URL to call and query data via the Connection Cloud:

http://workbench.demandreports.com/QueryService/

Use PHP’s SoapClient object to interface with the WSDL and to call query

operations. To generate or use pre-‐built PHP classes for ease of implementation, please refer to the next two sections.

Generating the PHP Class

Various “WSDL to PHP” conversion tools exist on the Web for generating PHP classes from WSDL files. You can use any open-‐source tool such as

(6)

for more information and download instructions:

http://code.google.com/p/wsdl2phpgenerator/

Be aware that the tool provides options to keep the generated code manageable and to ensure the output is contained within a single PHP file.

Downloading the PHP Class

The most common way to access the Connection Cloud Web services in PHP is through the PHP interface class. This class can be included as an external library in any PHP application and the available public methods provide a clean interface for querying cloud data via the Connection Cloud. You can find download instructions at the following URL:

http://www.connectioncloud.com

Creating a Connection

In order to execute queries through the Connection Cloud an application needs to first instantiate a QueryService object and log in using Connection Cloud

credentials.

The Login method returns a session ID string that needs to be used in all subsequent calls to the Connection Cloud.

Note: The Login method does not require the tenant name in most cases. If no tenant name is available, leave this parameter blank.

<?php

include 'QueryService.php'; // Auto-generated using “wsdl2phpgenerator”

try

{

// Log into Connection Cloud

$queryService = new QueryService(

array(), 'http://server.elasticintelligence.com/QueryService/QueryService?wsdl'); $loginParams = new Login("mytenant", "myuser", "mypassword");

$loginResponse = $queryService->Login($loginParams);

$sessionId = $loginResponse->sessionId;

unset($loginParams);

unset($getResourceParams);

// Execute a query and collect the result set... // Use the data in the application...

}

catch (Exception $e) {

echo 'Caught exception: ' . $e->getMessage() . ' on line ' . $e->getLine() . "\n";

}

(7)

Closing a Connection

After you have executed your query and retrieved the data, close the connection to the Connection Cloud. To close the connection use the Logout method along with the session ID returned from Login.

Various Query Methods

There are different methods for querying cloud data via the Connection Cloud. Some allow for creating dynamic queries while others eliminate the need for sending SQL statements to the Connection Cloud. This section describes the most common query methods for developers:

Stored Queries

The Connection Cloud Repository (part of the Workbench) allows its users to create and store queries. These queries use the WSQL language, which provides an

enormous amount of flexibility and control over cloud data. For instance, some cloud data providers do not support JOIN operations or provide pre-‐joined tables, but WSQL can join data across multiple tables and even across different cloud data sources. You can run these queries directly from the workbench to ensure the Connection Cloud is returning the correct data set. An application using the

Connection Cloud to fetch data can simply execute these stored queries and return the resulting data set to the application for processing or presentation.

A second advantage of storing queries in the Connection Cloud Repository is that once saved, ODBC/JDBC enabled applications (such as business intelligence and reporting tools or spreadsheets) can present each query as a table inside the Connection Cloud “database”. This allows you to test queries for data accuracy without writing a single line of code.

To execute a stored query, instantiate a resourceReference object using the path and name of the query in the Connection Cloud Repository. Then use this object

<?php

try

{

// Execute a query and collect the result set...

$logoutParams = new Logout($sessionId);

$logoutResponse = $queryService->Logout($logoutParams);

unset($logoutParams);

unset($queryService);

// Use the data in the application...

}

(8)

along with the previously obtained session ID to call the StartQuery Connection Cloud method.

The following code snippet demonstrates executing a query named “My Query” stored in the Connection Cloud Repository under the path “/Catalogs/Test Queries”.

Dynamic Queries

Applications can send WSQL queries directly to the Connection Cloud without storing them in the Connection Cloud Repository. Please refer to the WSQL syntax guide for information about creating queries. Since this method does not employ stored queries there is no need for a resourceReference object.

Developers can also use ANSI SQL to build queries on top of existing (stored) queries inside the Connection Cloud Repository. This method typically serves the case where the application needs to apply additional predicates to the result set. Please note, stored queries inside the Connection Cloud Repository look like “tables” to the outside world. Therefore, the application may use ANSI SQL syntax to query or join these “tables”.

<?php

try

{

// Log into Connection Cloud...

// Create the query reference object

$resourceRef = new

resourceReference(/*folderName*/"/Catalogs/Test Queries", /*name*/"My Query",

/*repositoryName*/"", /*type*/"Query");

// Start query execution

$startQueryParams = new StartQuery($sessionId, $resourceRef, null, null, null, null, null, null, 50);

$queryId = $queryService->StartQuery($startQueryParams)->queryId;

// Collect the result set... // Use the data in the application...

}

?>

<?php

try

{

// Create the query string

$queryText = 'SELECT * FROM MyChannel::GetAllAccounts() WHERE State=\'CA\'';

$startQueryParams = new StartQuery($sessionId, null, $queryText, null, null, null, null, null, 50);

}

(9)

The following example demonstrates using a query named “My Query”, stored in the Connection Cloud Repository under the path “/Catalogs/Test Queries”, and applying a dynamic WHERE clause to the result set.

Query Execution

As demonstrated in the previous examples, after you choose which query method to use, you call Connection Cloud’s StartQuery method to start the query execution. This sequence of operations allows you the flexibility of passing filter values to the query or limiting the number of rows returned by the Connection Cloud. For more information on the different parameters available when calling this method, please refer to Appendix A or the online documentation at:

www.connectioncloud.com

As the Connection Cloud retrieves data during query execution it may call various cloud data sources and their respective web services. Therefore, query execution times may vary. For this reason, the Connection Cloud provides methods to check the status of a query before it returns the data to the application. Use a while loop along with an appropriate delay to check the progress of a query.

The getQueryProgress method may return any of these values:

Status Code Description

Setup Query is being set up before execution can begin

Preparing Query is being validated before execution

Running Query is being executed

Completed Query execution has completed

The following code snippet demonstrates running a query and checking the execution progress within a loop.

<?php

try

{

// Create the query string

$queryText = 'SELECT * FROM "/Catalogs/Test Queries/"."My Query" WHERE State=\'' . $theStateCode . '\'';

}

(10)

Once the query execution has completed, issue a call to the Connection Cloud to clean up resources allocated for the query. In order to accomplish this, call

StopQuery using the query ID obtained from StartQuery.

Result Collection

Upon completion of the query execution the result data set can be collected for use inside the application. Such uses may include data visualization or storage in the application’s internal database.

The first step is to get the total number of rows returned. You will find this value in the rowCount property of the QueryProgressInfo object returned from

getQueryProgress when the query has completed.

The next step is to retrieve these rows of data using the GetRows method. This method allows you to get the data rows in batches, which is very helpful in cases where the query returns large amounts of data. For more information, refer to Appendix A or the online documentation at www.connectioncloud.com.

GetRows builds a two-‐dimensional array and stores it in the rowList property of the returned object. Each element in this array represents a single row of data, which itself is presented as an array of values corresponding to the columns returned in the result set. You may need to use type casting on a column value retrieved from

<?php

try

{

// Create the query string (or select a stored query)

$queryText = 'SELECT * FROM "/Catalogs/Test Queries/"."My Query"';

$queryProgressInfo = null;

while(TRUE) {

usleep(500000); // Sleep for half a second to allow for the query to complete

$queryProgressInfo = $queryService->getQueryProgress(

new GetQueryProgress($sessionId, $queryId))->progressInfo;

$status = $queryProgressInfo->status;

if ($status != Running && $status != Setup && $status != Preparing)

break;

}

// Make sure the query has fully completed (could have been aborted)

if ($status != Completed)

throw new Exception('Query was aborted with status ' . $status); // Collect the result set...

}

(11)

the result rows before using it in your application. The following code snippet demonstrates how to navigate and retrieve the data set.

Error Handling

You should always use appropriate Exception handling when calling the Connection Cloud Web services. Implement all calls to the Connection Cloud within a try-catch block.

When an Exception is caught, it is recommended that you issue a StopQuery call for all in-‐progress queries and close all existing connections via the Logout method as described in the “Closing a Connection” section.

Appendix

A: List of Methods

The Connection Cloud Web services API documentation is available online at

http://www.connectioncloud.com

<?php

try

{

// Execute a query . . .

$totalRows = $queryProgressInfo->rowCount;

// Get all the rows from the query results

$getRowsResponse = $queryService->GetRows(new GetRows($sessionId, $queryId, 0, $totalRows, $totalRows));

$results = array();

for ($index = 0; $index < $totalRows; $index++)

{

// Fetch the results one row at a time

$row = $getRowsResponse->rowList[$index]->value;

// Each row has: column0, column1... column

// Type cast each column value based on the expected output of the query

$results[$index] = (int) $row[0] . (string) $row[1] . (float) $row[2]; }

}

if ($queryId != null && !empty($queryId))

{

$params = new StopQuery($sessionId, $queryId);

$queryService->StopQuery($params);

}

unset($params);

}

(12)

B: Full Sample Code

<?php

try

{

$queryService = new QueryService(

array(), 'http://server.elasticintelligence.com/QueryService/QueryService?wsdl'); $loginParams = new Login("mytenant", "myuser", "mypassword");

$loginResponse = $queryService->Login($loginParams);

$sessionId = $loginResponse->sessionId;

unset($loginParams);

unset($getResourceParams);

// Create the query reference object

$resourceRef = new

resourceReference(/*folderName*/"/Catalogs/My Queries", /*name*/"My Query",

/*repositoryName*/"", /*type*/"Query");

$startQueryParams = new StartQuery($sessionId, $resourceRef, null, null, null, null, null, null, 50);

$queryProgressInfo = null;

while(TRUE) {

usleep(500000); // Sleep for half a second to allow for the query to complete

$status = $queryProgressInfo->status;

if ($status != Running && $status != Setup && $status != Preparing)

break;

}

// Make sure the query has fully completed (could have been aborted)

if ($status != Completed)

throw new Exception('Query ' . $resourceRef->name . ' was aborted with status ' . $status);

$totalRows = $queryProgressInfo->rowCount;

// Get all the rows from the query results

$getRowsResponse = $queryService->GetRows(new GetRows($sessionId, $queryId, 0, $totalRows, $totalRows));

unset($params);

$logoutParams = new Logout($sessionId);

$logoutResponse = $queryService->Logout($logoutParams);

$results = array();

for ($index = 0; $index < $totalRows; $index++)

{

// Each row has: column0, column1... columnN

$row = $getRowsResponse->rowList[$index]->value;

$results[$index] = (string) $row[0] . (string) $row[1] . (float) $row[2]; }

}

if ($queryId != null && !empty($queryId))

{

}

unset($params);

}