Query ADP Data in MySQL Workbench

MySQL Workbench allows users to administer MySQL environments and gain better visibility into databases. When paired with CData Connect (on-premise or Connect Server), you get live access to ADP data as if it were a MySQL database. This article shows how to create a virtual database for ADP in Connect and work with live ADP data in MySQL Workbench.

Create a Virtual MySQL Database for ADP Data

CData Connect uses a straightforward, point-and-click interface to connect to data sources and generate APIs.

1. Login to Connect and click Connections.
2. Select "ADP" from Available Data Sources.
3. Enter the necessary authentication properties to connect to ADP.

   Connect to ADP by specifying the following properties:

   • SSLClientCert: Set this to the certificate provided during registration.
   • SSLClientCertPassword: Set this to the password of the certificate.
   • UseUAT: The connector makes requests to the production environment by default. If using a developer account, set UseUAT = true.
   • RowScanDepth: The maximum number of rows to scan for the custom fields columns available in the table. The default value will be set to 100. Setting a high value may decrease performance.

   The connector uses OAuth to authenticate with ADP. OAuth requires the authenticating user to interact with ADP using the browser. For more information, refer to the OAuth section in the Help documentation.

4. Click Save Changes
5. Click Privileges -> Add and add the new user (or an existing user) with the appropriate permissions.

With the virtual database created, you are ready to connect to ADP from MySQL Workbench.

Query ADP from MySQL Workbench

The steps below outline connecting to the virtual ADP database in Connect from MySQL Workbench and issuing basic queries to work with live ADP data.

Connect to ADP through Connect

1. In MySQL Workbench, click to add a new MySQL connection.
2. Name the connection (CData Connect).
3. Set the Hostname, Port, and Username parameters to connect to the SQL Gateway.
4. Click Store in Vault to set and store the password.
5. Click Test Connection to ensure the connection is configured properly and click OK.

Query ADP Data

1. Open the connection you just created (CData Connect).
2. Click File -> New Query Tab.
3. Write a SQL query to retrieve ADP data, like SELECT * FROM adpdb.Workers;

With access to live ADP data from MySQL Workbench, you can easily query and update ADP, just like you would a MySQL database. Request a demo of the CData Connect and start working with ADP just like a MySQL database today.