Assume that you have a reporting database named "StoreSales" in Zoho Reports. This database has a table named "Sales" (the table has sales records of the store across years) with the columns as given below:
Lets assume that we want to execute a SQL Select query to fetch the sales records for the year 2008, where the sale value is greater than $100 from this table.
The SQL SELECT Query for this would look like the following:
SELECT "Date", "Region", "Customer Name", "Sales" FROM "Sales" where year("Date") = 2008 AND Sales > 100
You can execute the SQL SELECT Query and get the results as a CSV file (or PDF / HTML/ JSON / XML) in Zoho Reports by invoking the following HTTP API URL which would look like the following:
The actual SQL SELECT Query has to be sent as a POST parameter to the above API call. The post parameter name for the API call is ZOHO_SQLQUERY
ZOHO_SQLQUERY=SELECT "Date", "Region", "Customer Name", "Sales" FROM "Sales" where year("Date") = 2008 AND Sales > 100
HTTP API call parameters:
In the above API call the parameters passed are specific to Zoho Reports service. These parameters vary depending on the type of SQL statement that is being executed (i.e. INSERT, UPDATE, DELETE or SELECT). These parameters could vary across the various Zoho Services. Refer to the HTTP Web API documentation of the corresponding Zoho Service which supports CloudSQL, to know more about the respective API requests to be used for SQL Querying. Ex. Zoho Reports CloudSQL documentation.
The following table provides a brief description of the parameters used in the Zoho Reports CloudSQL API call refered above:
Result on Execution:
The result set on executing the SELECT query will be returned as a CSV file stream for processing.
Using HTTP Web API from your Application:
Depending on the development language (Java, Python, PHP etc.,) used, you need to use the appropriate HTTP libraries for making the API requests and for processing the output. For instance, the above SELECT query example when invoked from a Java application, the code would look something like the following:
Other SQL Statements:
Similar to the SELECT query explained above you are be able to execute INSERT, UPDATE and DELETE statements in Zoho CloudSQL. The SQL statements supported by each Zoho Service will be published by the respective Zoho Service CloudSQL documentation.
Next: Connectivity with JDBC and ODBC Drivers