The QuerySurge Salesforce driver provides two approaches for querying data within a Salesforce environment. Both of these approaches rely upon the user to have knowledge of Salesforce Object Query Language (SOQL). This document will provide a high level primer for executing basic SOQL queries, however for more in-depth knowledge please see the Salesforce article here.
Salesforce Object Query Language (SOQL)
Salesforce Object Query Language or SOQL is a SQL like dialect for querying data contained in a Salesforce environment. While SOQL does strongly resemble SQL it does have significant differences. For example, the * notation is not supported when querying all fields in an object. Below is a small primer on SOQL syntax.
Basic Select Example:
SELECT Name from Account
The above example, shows how to select the field "Name" from the object "Account". If multiple fields are needed they can be included in the select separated by commas as seen in the following example:
SELECT Id, Name from Account
Filtering:
SOQL does support filtering results using a where clause. In the below example, we are querying the object "Account" returning the fields "Id", "Name" and "AccountNumber" when the record contains the value 'testing' for the "Industry" field.
SELECT Id, Name, AccountNumber FROM Account where Industry = 'testing'
Relationships (Joins)
As with tables, Objects can be joined together in SOQL statements. Salesforce supports parent-to-child joining and child-to-parent joining. In the following example, we are implementing a child-to-parent join with the child object "Contact" being joined to the parent object "Account". In this case this is a one-to-one join as a single account is tied to each contact.
SELECT Id, FirstName, Account.Name FROM Contact
The following query represents a parent-to-child query. In this case, the origin object is "Account" which is a parent object to "Contact". A single "Account" entry might have multiple "Contact" entries. Parent-to-child relationships are always represented with a sub query.
SELECT Id, (Select Id, FirstName from Contacts) from Account
Note: When querying a parent-to-child relationship, the object name will be the plural of the child name. In the above example we use Contacts versus Contact.
The output from a join query can be seen below. Since records can have a one-to-many relationship, QuerySurge will automatically separate these records into new rows for easy comparison.
Note: Separation logic does currently have some limitations. If no records are identified for the subquery the column splitting will not occur. In these cases, a column will be created for the object with a null value. In the below example, the account object is filtered to return an account without any contacts.
SELECT Id, (Select Id, FirstName from Contacts) from Account where id = '001aj00000FK5AzAAL'
Aggregation
Aggregate functions such as count, count_distinct, sum, avg, min, max are supported by Salesforce. The following query, counts the occurrences of field "Id" from object "Lead" when grouping the records by "LeadSource".
SELECT LeadSource, COUNT(Id) FROM Lead GROUP BY LeadSource
Aggregation is only allowed in the root query, as such the below example is invalid SOQL.
SELECT Id, (Select count(Id) from Contacts) from Account
Note: Salesforce limits the number of records which can be included in a group by. For more information on these limits please see this article.
Compounded Fields
A compounded field is a unique field in Salesforce. When a compounded field is used in query, multiple fields are returned for the referenced entity instead of one. In the following example, the compounded field "BillingAddress" is used. Rather then returning three fields for "Id", "Name" and "BillingAddress" nine fields are returned which include "Id", "Name", "CountryCode", "Street", "Latitude", "PostalCode", "GeoCodeAccuracy", "StateCode" and "Longitude". The number of columns returned will be determined on the compounded object being queried.
SELECT Id, Name, BillingAddress FROM Account
Note: Compounded fields cannot be used with most functions. In addition, compounded fields cannot be used as part of a group by.
Note: If a compounded field is returned in a resultset which returns no rows the compounded field will not be split. In these cases a single column will be created with the compounded field name. For example "Select BillingAddress From Account Limit 0" will return a single column "BillingAddress" versus the split columns seen above.
How to Query Salesforce in a QueryPair
There are two ways to write queries against Salesforce which are outlined below.
Direct SOQL Queries
Direct SOQL queries are the fastest and easiest way to query data contained in Salesforce. When using a direct query, you can use direct SOQL queries in your QueryPair. QuerySurge will automatically handle data types (based on connection settings).
SELECT Id, Name, BillingAddress FROM Account
Wrapped SOQL Queries
Wrapped SOQL queries are a custom capability added to the QuerySurge Salesforce driver which greatly expands users capabilities when testing Salesforce data. Like direct queries, users are able to use SOQL to query data from Salesforce, the results from these queries are converted into tables and stored in a temporary H2 database. Using this approach, users can create complex joins and use any of the available syntax supported by the H2 data platform. For information on the supported commands in H2 SQL please see this page.
To use a wrapped SOQL query, you will need to use the following syntax.
SFSOQL('SOQL QUERY', useDataTypes)
-
SOQL Query(string): SOQL Query to be sent to Salesforce -
useDataTypes(Integer): Optional field. Defaults to connection setup. Pass -1 to override and disable datatype parsing
Below is an example of a wrapped SOQL query.
select *
from SFSOQL('SELECT id, name from Account',-1) x
left join SFSOQL('SELECT accountid, LastName from Contact') y
on x.id = y.accountid
Data Type Handling
Due to the nature of SOQL, the data that is returned is untyped. QuerySurge will attempt to determine the underlying data types from your SOQL queries automatically. This is done through a mixture of pre and post result parsing. It is possible for a data type not be parsed by QuerySurge, when this occurs QuerySurge will automatically convert these types to a string. In these cases, we do recommend the use of a wrapped SOQL query and casting the data type manually.
Custom Functions
Custom Functions are user defined Java functions which can be used in your queries to expand the capabilities of your tests. Custom Functions can be used with the QuerySurge Salesforce driver in two ways which are listed below.
Call Statement
The first approach is to call the function directly. This can be done done using the CALL keyword. Multiple call statements can be included in your query, however all queries must contain at least 1 select statement which returns data. Below is an example of using a call statement:
call createSalesforceTestingLead();
SELECT address FROM Lead;
call deleteSalesforceTestingLead();
In the above example, two custom functions are called named createSalesforceTestingLead and deleteSalesforceTestingLead. These functions are called before and after the select statement for Salesforce.
Column Function
The second approach is with a Column Function. A Column Function can be used in conjunction with wrapped SOQL queries ONLY. When using a wrapped SOQL queries any of the returned columns from can be wrapped with a Column Function.
Note: You cannot use column functions inside of your SOQL query itself. Doing so will trigger a syntax exception.
Example Column Function:
select id, truncateName(name)
from SFSOQL('SELECT id, name from Account',-1) x
In the above example, the column name is wrapped with a Column Function called truncateName.
The following is an example of an INVALID use of a Column Function:
select *
from SFSOQL('SELECT id, truncateName(name) from Account',-1) x
Building a Custom Function
The purpose of this example is to show how to set up custom function for a Salesforce Connection. Here we will be creating a Java method that converts a string into a timestamp with a user provided format.
Note: The JDK level used to compile the jar containing your custom function file must match the JDK level used by QuerySurge. You can find your QuerySurge Java Version in Administration > Server Properties > Java Version.
1. Create an external Java method to handle the timestamp conversion. In this example, the following Java method is used. As illustrated here, custom methods should always be public and static
2. Compile the Java to create the jar file and copy it, along with any dependencies to the <QuerySurge Install Dir>/QuerySurge/agent/jdbc folder
3. Stop your Agent(s)
4. Locate the agentconfig.xml file for your Agent(s), and make a copy of the file before editing it:
<QuerySurge Install Dir>/QuerySurge/agent/config/agentconfig.xml. Within the section named connectionProps add a new driverProp as seen below.
<connectionProps>
...
<driverProp driver="com.querysurge.salesforce.jdbc.SFDriver" prop="function:parse_timestamp" type="String" value="com.rttsweb.manager.CustomFunctions" />
...
</connectionProps>
-
Driver: The driver class to register this function with. For the QuerySurge Salesforce driver this should be "com.querysurge.salesforce.jdbc.SFDriver" -
Prop: The function which will be loaded. This should always be function:<Your Function Name> -
Type: This will always be "String" -
Value: The namespace and class which contains your function
5. Restart the QuerySurgeAgent service for each Agent modified.
Limitations:
- https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_limits.htm#OneLevelPerQuery