CloudSQL API Spec

Tags:  

Zoho Reports CloudSQL - API Specification

SQL Querying over HTTP using Zoho Reports API

Zoho Reports has implemented the Zoho CloudSQL technology as an extension to its HTTP Web API. Using the HTTP API, users can query Zoho Reports database by providing the SQL queries.

Currently Zoho Reports supports only SQL SELECT Queries. Other SQL statements like INSERT, UPDATE and DELETE will be supported very soon.

Using SQL Select statements developers can fetch data from a single Table/Query Table or joining one or more tables in Zoho Reports. The data can be fetched in different response formats, which includes CSV, PDF, HTML, JSON and XML

SQL SELECT Query Request Format:

Zoho Reports uses the EXPORT HTTP API action request to execute any SQL SELECT query given. The following parameters are to be provided as part of the Export API request to execute an Select query:

Query String Parameters:

The following parameters are to be passed in the Query String

  • ZOHO_ACTION parameter should be 'EXPORT'
  • ZOHO_OUTPUT_FORMAT that defines the output format for the API request could be any of the following:
    • CSV
    • XML
    • JSON
    • HTML
    • PDF

SQL Query as POST parameter:

The exact SQL Select query has to be passed as a post parameter to the API request. The query should be passed as a value to the parameter ZOHO_SQLQUERY. The exact SQL Select query string should be URL encoded.

Sample:

The sample Select Query will fetch all the employees in the 'finance' department along with their details in CSV Format.

Entities Involved:

Database Name: EmployeeDB
Tables Involved: Employee, EmpDetails

Request URL:

https://reportsapi.zoho.com/api/abc@zoho.com/EmployeeDB?ZOHO_ACTION=EXPORT
&ZOHO_OUTPUT_FORMAT=CSV&ZOHO_ERROR_FORMAT=XML
&authtoken=g38sl4j4856guvncrywox8251sssds
&ZOHO_API_VERSION=1.0

Select Query in POST Parameter: (Note the query should be URL Encoded)

ZOHO_SQLQUERY=select empdet.Name Name,empdet.DOB Date_Of_Birth,empdet.Address Address,emp.BasicSal BasicPay,round(emp.BasicSal + emp.Allowance,2) Salary from Employee emp inner join EmpDetails empdet on emp.ID = empdet.ID where emp.Dept = 'Finance'

URL Encoded Select Query in POST Parameter:

&ZOHO_SQLQUERY=select%20%20empdet.Name%20Name%2Cempdet.DOB%20Date_Of_Birth%2C
empdet.Address%20Address%2Cemp.BasicSal%20BasicPay%2Cround(emp.BasicSal%20%2B%20emp.Allowance%2C2)%20
Salary%20from%20Employee%20emp%20inner%20join%20EmpDetails%20empdet%20
on%20emp.ID%20%3D%20empdet.ID%20where%20emp.Dept%20%3D%20'Finance'


Note


It is recommended to use Zoho Reports Login Email Address in the API URL instead of Zoho Username.


Sample Success Response:

The sample response for the above query in CSV format is given below. The first row of the CSV response will contain the column names:

Name,Date_Of_Birth,Address,BasicPay,Salary
Kumar,"10 Dec, 1979 00:00:00",Chennai,10000,10500
....

To know more about the EXPORT API action and the parameters (like ZOHO_ERROR_FORMAT etc.,) involved in the request, refer to this link.

Next: Using Zoho Reports JDBC Driver

 


    Post a comment

    Your Name or E-mail ID (mandatory)

    Note: Your comment will be published after approval of the owner.




     RSS of this page