Zoho CRM Connector

Zoho CRM Connector

Additional Documentation

This Connector is based on the Zoho CRM ADO.NET Provider by CData. For additional help, including Connection String parameters, please see: https://cdn.cdata.com/help/CZG/ado/

Authenticating to Zoho CRM

The provider is already registered with Zoho CRM as an OAuth application. As such, it will, by default, automatically use its Embedded Credentials to connect.

Using OAuth Authentication

OAuth requires the authenticating user to interact with Zoho CRM using the browser. Simply log into your instance of Zoho CRM and press Authenticate Now.



SELECT Statements

A SELECT statement can consist of the following basic clauses.

  • SELECT
  • INTO
  • FROM
  • JOIN
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • ORDER BY
  • LIMIT

Examples

  1. Return all columns:
    SELECT * FROM Accounts
  2. Rename a column:
    SELECT [AccountNumber] AS MY_AccountNumber FROM Accounts
  3. Cast a column's data as a different data type:
    SELECT CAST(AnnualRevenue AS VARCHAR) AS Str_AnnualRevenue FROM Accounts
  4. Search data:
    SELECT * FROM Accounts WHERE Industry = 'Data/Telecom OEM';
  5. Return the number of items matching the query criteria:
    SELECT COUNT(*) AS MyCount FROM Accounts
  6. Return the number of unique items matching the query criteria:
    SELECT COUNT(DISTINCT AccountNumber) FROM Accounts
  7. Return the unique items matching the query criteria:
    SELECT DISTINCT AccountNumber FROM Accounts
  8. Summarize data:
    SELECT AccountNumber, MAX(AnnualRevenue) FROM Accounts GROUP BY AccountNumber
    See Aggregate Functions for details.
  9. Retrieve data from multiple tables.
    SELECT Accounts.AccountName, Contacts.FirstName, Contacts.LastName FROM Accounts, Contacts WHERE Accounts.AccountId=Contacts.AccountId
    See JOIN Queries for details.
  10. Sort a result set in ascending order:
    SELECT AccountName, AccountNumber FROM Accounts  ORDER BY AccountNumber ASC
  11. Restrict a result set to the specified number of rows:
    SELECT AccountName, AccountNumber FROM Accounts LIMIT 10
  12. Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
    SELECT * FROM Accounts WHERE Industry = @param

How to download an attachment from Zoho CRM

StarfishETL has added a "virtual" table called DownloadFile for this purpose. To download an attachment, you can query this table and pass in 3 parameters. They are: ModuleName, RecordID and AttachmentID. All 3 are needed in order to download the correct file. You can find these values by querying the Attachments table.
Example:
SELECT * FROM DownloadFile WHERE ModuleName='Leads' AND RecordID='2976013000000289057' and AttachmentID='2976013000000606001'
This will return a single row, where the "Content" column will hold the attachment data in Base64 format.


Parameter
OAuth Access Token
OAuth Refresh Token