Home / querying cloud sql

querying cloud sql

Zoho Home | Forums | Blogs | Contact Us | Toll Free : 888 900 9646
 
Quicklinks: Overview | Architecture Overview | Developer Info | Adoption Status | Roadmap
 
Zoho CloudSQL
Developer Information - Executing a SQL Query
 
Language Overview | Executing a Query | Connectivity with JDBC/ODBC | Sample
 

Querying as Zoho CloudSQL - A Preview:

Once you construct the relevant SQL Query, then you need to execute it against the respective Zoho Service. You need to submit the SQL Query as a HTTP Post request to the corresponding Web API, in the respective Zoho Service and execute the SQL Query. As Zoho CloudSQL supports industry standard SQL, you can refer to the ANSI SQL documentation or SQL documentation of databases like Oracle, SQL Server, DB2, MySQL, PostgreSQL, Sybase and Informix to learn more about the SQL dialects.

Lets walk through a sample SQL SELECT Query execution in Zoho Reports (previously known as Zoho DB & Reports) and discuss about the details involved.




Read More:

Zoho Reports:

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:

Column Name Column Type
DateDate
RegionString
Product Category String
Product Name String
Customer Name String
SalesCurrency

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:

https://reports.zoho.com/api/demouser/StoreSales?ZOHO_ACTION=EXPORT&ZOHO_OUTPUT_FORMAT=CSV&ZOHO_ERROR_FORMAT=XML&ZOHO_API_KEY=hewfdrbgs&ticket=gsssds&ZOHO_API_VERSION=1.0

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:

Column Name Column Type
reports.zoho.com
Zoho Reports service URL. Each service would be distinctly identified by their service URL.
demouserZoho user name on whose name space this query is executed
StoreSalesThis is the database name in Zoho Reports on which the query is executed
ZOHO_ACTIONThis parameter defines the type of action that is executed by this API call. In this example we are exporting (EXPORT) the data out of Zoho Reports as a CSV file based on SELECT query that is executed.
ZOHO_OUTPUT_FORMATThis parameter defines the output format of SELECT query being executed.
ZOHO_ERROR_FORMATThis parameter defines the format in which the error will be returned, incase there is a problem in executing this SELECT query. In this example XML is the error format
ZOHO_API_KEYFor using any Zoho Service API, you need to get the API key from Zoho. For details refer to this link.
ticketThis is the ID that would be generated for that specific session. You have to get this ticket id by authenticating yourself. This has to be done before the API is invoked. For details refer to this link.

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:

....
....

HttpClient client = new HttpClient();
PostMethod method = new PostMethod("https://reports.zoho.com/api/demouser/StoreSales");
method.addParameter("ZOHO_API_VERSION","1.0");
method.addParameter("ZOHO_API_KEY","hewfdrbgs"); //API Key
method.addParameter("ticket","gsssds"); //Ticket ID
method.addParameter("ZOHO_OUTPUT_FORMAT","CSV");
method.addParameter("ZOHO_ACTION","EXPORT");

//The SELECT query executed is given below
method.addParameter("ZOHO_SQLQUERY","SELECT "Date", "Region", "Customer Name", "Sales" FROM "Sales" where year("Date") = 2004 AND Sales > 100 ");

//Executing the API
int status = client.executeMethod(method);

//If the return status is OK, process the result
if (status == HttpStatus.SC_OK)
{
// The response in CSV format is fetched in a InputStream. You could process the same in subsequent steps.
InputStream is = method.getResponseBodyAsStream();

//You process the InputStream subsequently
....
....
}
else
{
int httpStatusCode = method.getStatusCode();
String message = method.getResponseBodyAsString().trim();
System.err.println("Status Code: " + httpStatusCode);
System.err.println("Error Message: " + message);
return -1;
}

....
....

To know more about the above refered Zoho Reports HTTP API click here.

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



    		 RSS of this page