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:
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.
The sample Select Query will fetch all the employees in the 'finance' department along with their details in CSV Format.
Database Name : EmployeeDB
Tables Involved : Employee, EmpDetails
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:
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:
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.