JDBC Driver for Bullhorn CRM

Build 20.0.7654

Data Model

Overview

This section shows the available API objects and provides more information on executing SQL to Bullhorn CRM APIs.

Key Features

  • The driver models Bullhorn CRM entities like candidates, client contacts, and job orders as relational tables, allowing you to write SQL to query Bullhorn CRM data.
  • Stored procedures allow you to execute operations to Bullhorn CRM.
  • Live connectivity to these objects means any changes to your Bullhorn CRM account are immediately reflected when using the driver.

Collaborative Query Processing

The driver offloads as much of the SELECT statement processing as possible to Bullhorn CRM and then processes the rest of the query in memory. API limitations and requirements "are also documented in this section.

See SupportEnhancedSQL for more information on how the driver circumvents API limitations with in-memory client-side processing.

Saved Searches

The driver exposes saved searches as separate views. These views will be named like 'SavedSearch_{Saved search name}'.

Layouts

The driver uses the ListLayouts property to determine what layouts to expose as separate views. This property is recommended because it will boost the performance on tables with too many columns, like Candidate. So you can use the desired layout view, instead of the original table, for a better performance.

The naming of these views is like '{original entity}{layout name}Layout'. For example, CandidateListRowLayout, is the view that corresponds to the ListRow layout of candidate entity.

Custom tables and custom columns

Bullhorn CRM can have custom entities and custom fields. If you want them to be listed as tables and columns respectively use the IncludeCustomTables and IncludeCustomFields properties. Both these properties are false by default.
Note that setting these properties to true may result on a slower performance.

Supported operators

The driver offloads as much of the filtering as possible to Bullhorn CRM API, in order to have a better performance. The Bullhorn CRM supports two query languages, Lucene and JPQL.

Lucene
Only Candidate and Note entities support the Lucene filtering for now. The supported operators on these tables are: 'AND', 'OR', '=', 'IN', '>', '>=', '<', '<='. Note that '>', '>=', '<', '<=' are only supported for datetime columns.

Some query examples that are handled server side are:

SELECT * FROM Candidate WHERE Id = '109989'
SELECT * FROM Candidate WHERE Id IN (109989, 110011)
SELECT * FROM Candidate WHERE Id IN (109989, 110011) AND DateAdded > '2018-06-11 11:40:17.577'
SELECT * FROM Candidate WHERE DateAdded < '2019-12-15 14:31:01'
SELECT * FROM Candidate WHERE DateAdded >= '2019-12-16 14:31:01' AND (AddressCity = 'southfield' OR AddressState = 'indiana')
JPQL
All the tables except Candidate and Note support JPQL filtering. The supported operators on these tables are: 'AND', 'OR', 'NOT', '=', '!=', '>', '>=', '<', '<=', 'IS', 'IS NOT', 'IN', 'NOT IN'.

Some query examples that are handled server side are:
SELECT * FROM Appointment WHERE Id in ('83409', '83404', '83398')
SELECT * FROM Appointment WHERE Id in ('83409', '83404', '83398') and communicationMethod = 'phone'
SELECT * FROM Appointment WHERE CandidateId IS NOT NULL AND StartDate > '2019-12-17 12:01:00.000' ORDER BY Id, CandidateId
SELECT * FROM Appointment WHERE (CandidateId IS NOT NULL AND StartDate >= '2019-12-17 12:01:00.000') OR Description IS NOT NULL
SELECT * FROM Appointment WHERE (CandidateId NOT IN (19624,19622,19621,19620,19618) AND StartDate > '2019-12-17 12:01:00.000') OR Location != 'Louisville, KY'

Insert, Update, Delete You can perform write operations using Insert, Update, Delete SQL statements. Some query examples:
INSERT INTO Task(Subject, Description) VALUES('Follow up - Billy Joel', 'Follow up the candidate Billy Joel.')
UPDATE Task SET Description = 'An updated description here.' WHERE id = 84905
DELETE FROM Task WHERE id = 84905

GETDELETED You can get the IDs of the deleted records on several entities (Not all entities support deleted records).
GETDELETED FROM Candidate
GETDELETED FROM Appointment
GETDELETED FROM Task

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7654