Natively Connect to Paylocity Data in PHP

Drop the CData ODBC Driver for Paylocity into your LAMP or WAMP stack to build Paylocity-connected Web applications. This article shows how to use PHP's ODBC built-in functions to connect to Paylocity data, execute queries, and output the results.

Configure a DSN

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs.

Set the following to establish a connection to Paylocity:

• RSAPublicKey: Set this to the RSA Key associated with your Paylocity, if the RSA Encryption is enabled in the Paylocity account.

  This property is required for executing Insert and Update statements, and it is not required if the feature is disabled.

• UseSandbox: Set to true if you are using sandbox account.
• CustomFieldsCategory: Set this to the Customfields category. This is required when IncludeCustomFields is set to true. The default value for this property is PayrollAndHR.
• Key: The AES symmetric key(base 64 encoded) encrypted with the Paylocity Public Key. It is the key used to encrypt the content.

  Paylocity will decrypt the AES key using RSA decryption.
  It is an optional property if the IV value not provided, The driver will generate a key internally.

• IV: The AES IV (base 64 encoded) used when encrypting the content. It is an optional property if the Key value not provided, The driver will generate an IV internally.

Connect Using OAuth Authentication

You must use OAuth to authenticate with Paylocity. OAuth requires the authenticating user to interact with Paylocity using the browser. For more information, refer to the OAuth section in the Help documentation.

The Pay Entry API

The Pay Entry API is completely separate from the rest of the Paylocity API. It uses a separate Client ID and Secret, and must be explicitly requested from Paylocity for access to be granted for an account. The Pay Entry API allows you to automatically submit payroll information for individual employees, and little else. Due to the extremely limited nature of what is offered by the Pay Entry API, we have elected not to give it a separate schema, but it may be enabled via the UsePayEntryAPI connection property.

Please be aware that when setting UsePayEntryAPI to true, you may only use the CreatePayEntryImportBatch & MergePayEntryImportBatchgtable stored procedures, the InputTimeEntry table, and the OAuth stored procedures. Attempts to use other features of the product will result in an error. You must also store your OAuthAccessToken separately, which often means setting a different OAuthSettingsLocation when using this connection property.

Establish a Connection

Open the connection to Paylocity by calling the or methods. To close connections, use or .

$conn = odbc_connect("CData ODBC Paylocity Source","user","password");

Connections opened with are closed when the script ends. Connections opened with the method are still open after the script ends. This enables other scripts to share that connection when they connect with the same credentials. By sharing connections among your scripts, you can save system resources, and queries execute faster.

$conn = odbc_pconnect("CData ODBC Paylocity Source","user","password");
...
odbc_close($conn); //persistent connection must be closed explicitly

Create Prepared Statements

Create prepared statements and parameterized queries with the function.

$query = odbc_prepare($conn, "SELECT * FROM Employee WHERE EmployeeId = ?");

Execute Queries

Execute prepared statements with .

$conn = odbc_connect("CData ODBC Paylocity Source","user","password");
$query = odbc_prepare($conn, "SELECT * FROM Employee WHERE EmployeeId = ?");
$success = odbc_execute($query, array('1234'));
 

Execute nonparameterized queries with .

$conn = odbc_connect("CData ODBC Paylocity Source","user","password");
$query = odbc_exec($conn, "SELECT FirstName, LastName FROM Employee WHERE EmployeeId = '1234'");
 

Process Results

Access a row in the result set as an array with the function.

$conn = odbc_connect("CData ODBC Paylocity data Source","user","password");
$query = odbc_exec($conn, "SELECT FirstName, LastName FROM Employee WHERE EmployeeId = '1234'");
while($row = odbc_fetch_array($query)){
 echo $row["FirstName"] . "\n";
}

Display the result set in an HTML table with the function.

$conn = odbc_connect("CData ODBC Paylocity data Source","user","password");
$query = odbc_prepare($conn, "SELECT * FROM Employee WHERE EmployeeId = ?");
$success = odbc_execute($query, array('1234'));
if($success)
 odbc_result_all($query);

More Example Queries

You will find complete information on the driver's supported SQL in the help documentation. The code examples above are Paylocity-specific adaptations of the PHP community documentation for all ODBC functions.