Zoho Reports JDBC Driver
JDBC Driver for Zoho Reports CloudSQL
As CloudSQL enables users to execute SQL queries on structured data stored in Zoho Reports, it lends itself naturally to support Database Connectivity Standard like JDBC (Java Database Connectivity). Developers who are familiar using JDBC driver for connectivity to databases, can now use Zoho Reports CloudSQL JDBC driver to connect and execute the necessary SQL queries.
With the availablity of this JDBC driver, developers need not learn the Zoho Reports CloudSQL HTTP Web API to execute the SQL query. They just have know how to use the Zoho Reports JDBC driver and start interacting with the service, the same way as they would interact with a database using a JDBC standard driver.
Zoho Reports JDBC Driver Details:
To use the JDBC Driver you need to have a Zoho Reports user name and password. Also you need to get a Zoho API key. Currently you could only execute Select queries using the JDBC driver. Other SQL statements will be supported soon.
In JDBC, the DriverManager class manages the establishment of connections. DriverManager needs to be told which JDBC driver it should try to make connections with. The way to do this is to use Class.forName( ) on the class that implements java.sql.Driver interface.
Refer to the Zoho Reports JDBC Driver javadocs to know more about the same. Click to download Zoho Reports JDBC Driver
JDBC Connection URL for Zoho Reports:
http://reports.zoho.com/api/<zoho_username_dbowner>/<databasename>?apikey=<zoho_api_key>
&user=<zoho_username>&password=<zoho_password>
Sample:
The following Java code snippet shows how you can register Zoho Reports CloudSQL JDBC driver, obtain a Connection, create a Statement and execute the query:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class ZohoReportsCloudSQL
{
public static void main(String args[])
{
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try
{
Class.forName("com.zoho.cloudsql.jdbc.ZohoReportsDriver");
Properties conProps = new Properties();
conProps.put("ZOHO_API_KEY","abcd");
// Zoho username to login to Zoho service
conProps.put("user","david");
// Zoho password to login to Zoho service
conProps.put("password","davidpwd");
// Uncomment this incase you need proxy settings
/*
// Proxy host name to connect the internet
conProps.put("PROXYSERVER","proxy_hostname");
// Proxy port to connect the internet
conProps.put("PROXYPORT","proxy_port");
// Proxy user name to connect the internet
conProps.put("PROXYUSERNAME","proxy_username");
// Proxy password to connect the internet
conProps.put("PROXYPASSWORD","proxy_password"); */
/* Important Note: Connection is single threaded in Zoho Reports */
// david is the owner of the database 'Sales'
con = DriverManager.getConnection("http://reports.zoho.com/" +
"api/david/Sales",conProps);
stmt = con.createStatement();
String sql ="select * from <tablename>";
rs = stmt.executeQuery(sql);
}
catch(SQLException se)
{
// handle any errors
System.out.println("SQLException: " + se.getMessage());
System.out.println("Zoho Reports Error code: " + se.getErrorCode());
}
catch(Exception e)
{
// handle the exception
}
finally
{
if(rs != null)
{
try
{
rs.close();
}
catch(SQLException sqlEx) { } // ignore
}
if(stmt != null)
{
try
{
stmt.close();
}
catch (SQLException sqlEx) { } // ignore
}
if(con != null)
{
try
{
con.close();
}
catch (SQLException sqlEx) { } // ignore
}
}
}
}
Following methods can also be used to obtain the connection:
DriverManager.getConnection("http://reports.zoho.com/api/david/Sales?" +
"apikey=abcd","david","davidpwd");
DriverManager.getConnection("http://reports.zoho.com/api/david/Sales?" +
"apikey=abcd&user=david&password=davidpwd");
Jars to be kept in CLASSPATH for JDBC Driver:
All the jars from 'lib' directory within the JDBC Driver Zip need to be kept in CLASSPATH for JDBC driver.
JDBC Type Mapping with Zoho Reports Data types:
The following table provides the mapping between the Zoho Reports data types and the JDBC SQL types. Use this mapping to process the data fetched from Zoho Reports in your Java code.
|
Zoho Reports Data type |
JDBC SQL Type |
Javadocs:
Zoho Reports JDBC driver download:
You could download the Zoho Reports JDBC Driver from the below link.
Download - Zoho Reports JDBC Driver
Next: Zoho Reports CloudSQL Client Libraries
Looks like a great way to use an external database. I'd like to see more information related to how secure the transactions are as well as how the data is secured.
Thanks - great info.
Hi Behrens,
We do support https (SSL) connectivity through JDBC, to ensure that the transactions are encrypted and safe over the Network. If you require more details on the same, do let us know (Possibly let us know your email id to support at zohodb dot com, we could even mail the same to you).
Also Zoho DB & Reports are hosted in a very secure & redundant data centers in a grid setup which supports replicated servers and periodic backups. Also we have strong processes to prevent access by any human personal. To know more about Zoho Security practices, we recommend you to go through the following links:
http://www.zoho.com/security.html
http://blogs.zoho.com/general/security-practices
If you require any further clarifications mail us to support at zohodb dot com.
Thanks,
Clarence
Zoho DB & Reports
sorry for the beginner question, but everytime I try to run a compiled version of this program, it has a problem with the following line:
Class.forName("com.zoho.cloudsql.jdbc.ZohoReportsDriver");
I keep getting messages like the following:
Exception in thread "main" java.lang.ClassNotFoundException: com.zoho.cloudsql.jdbc.ZohoReportsDriver
I'm pretty sure I got the PATH and CLASSPATH settings correct, any ideas on what i'm doing wrong?
Hi,
ClassNotFoundException will occur only in the case of class not available in the classpath. Kindly make sure that you have kept all the following jars in the classpath:
ZohoCloudSQLJDBCDriver_1_0.jar
ZohoReportAPIClient.jar
csv.jar
commons-logging-api.jar
commons-httpclient-3.0.1.jar
commons-codec-1.3.jar
If you still find the issue, kindly send us the CLASSPATH content to support@zohodb.com which will help us to analyse the issue.
Thanks,
Saminathan
The JDBC driver doesn't seem to support standard create, update and delete operations. Is that a correct statement...?
Hi,
Currently we support only the SELECT Query. We are working on supporting DELETE queries which should be available in the near future. Subsequently we will also support INSERT and UPDATE SQL statements, for which we have not finalized the timeline as yet. We will surely keep the community posted about this.
Please let us know if you require any further clarifications.
Thanks,
Kovil Pillai P.,
Zoho Reports.