JDBC Driver

Tags:  

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 Download

Download the Zoho Reports JDBC Driver from the below link.

http://css.zohostatic.com/db/api/v4_m1/ZohoCloudSQLJDBC_1_0.zip

Javadocs

Zoho Reports JDBC Driver

To use the JDBC Driver you need to have a zoho login email address and password. 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

https://reportsapi.zoho.com/api/<zoho_username_dbowner>/<databasename>?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("https://reportsapi.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("https://reportsapi.zoho.com/api/david/Sales?" +
"david","davidpwd");

DriverManager.getConnection("https://reportsapi.zoho.com/api/david/Sales?" +
"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

Plain Text

VARCHAR

Multi Line Text

LONGVARCHAR

Email

VARCHAR

Auto Number

BIGINT

Number

BIGINT

Positive Number

BIGINT

Decimal Number

DOUBLE

Currency

DOUBLE

Percent

DOUBLE

Date

TIMESTAMP

Decision Box

BIT

Connecting Zoho Reports from Database Visualization Tools

Zoho Reports JDBC Driver supports connecting Zoho Reports from the following Database Visualizations tools.

  • DBVisualizer
  • Squirrel SQL Client

DbVisualizer tool

You can connect to Zoho Reports from the DbVisualizer tool by following the steps below.

Step 1: Create a New Driver Name

  • Click Tools > Driver Manager from the toolbar menu. The Driver Manager dialog will open.
  • In the Driver Settings options, specify the name for the New Driver in the Name field (say, ZohoReportsDriver)
  • In the URL Format field enter the URL in the format https://reportsapi.zoho.com/api/<zoho_user_email_address>/<zoho_database_name>.
  • Provide the Driver Class name of Zoho Reports JDBC driver (com.zoho.cloudsql.jdbc.ZohoReportsDriver) used for connecting Zoho Reports.
  • In the Driver File Paths group option, on the User Specified tab click the File icon to add JAR files. Browse and add all relevant jar file for Zoho Reports JDBC driver.
  • Close the wizard.



Step 2: Creating New Database Connection

  • Click Create new database connection icon in Databases list window. It will prompt to select Use Wizard or No Wizard.
  • Choose Use Wizard option. New Connection Wizard will open.
  • Enter the connection alias name for the new database connection and then click Next.


  • Select the appropriate database driver (ZohoReports Driver) from the drop down and then click Next button.



  • In the Database URL field, specify the URL of the database to be connected as follow.
    Format: https://reportsapi.zoho.com/api/<zoho_user_email_address>/<zoho_database_name>
    Example: https://reportsapi.zoho.com/api/david@zoho.com/Sales
  • Specify Zoho Login Email Address and password for authentication and then click Finish.
  • Click Finish to complete the creation of the new connection.

Connections Overview

The Connection Overview will list all the database connections configured in the tool, on selecting the Connections object in the Databases Objects Tree. To connect to your Zoho Reports account, double click the Zoho Reports connection. All the databases in your account along with the table will be listed.

Executing a SQL SELECT Query

Follow the below steps to execute a SQL SELECT query in your Zoho Reports database.

  • Click Create a new SQL commander tab icon from the toolbar.
  • Choose database name from the Database drop down.
  • Specify SQL SELECT Query in QUERY area and run the query.
  • Only SQL SELECT queries can be used to access Zoho Reports from the tool.

Squirrel SQL Client

You can also connect to Zoho Reports from the Squirrel SQL client. To connect to a Zoho Reports database from Squirrel SQL client, you need to provide two set of information i.e., the driver definition and the alias. The driver definition specifies the JDBC driver to use and the alias specifies the connection parameters.

Steps for Creating New Driver Definitio

Create a new driver definition to specify the JDBC driver by following the steps below.
  • Click Windows > View Drivers from the toolbar menu.
  • Click the Create a New Driver (+) icon in the driver List window. The Add Driver wizard will open.
  • Specify the Name for the driver in the Name field (say, ZohoReportsDriver).
  • In the Example URL field, enter the URL format https://reportsapi.zoho.com/api/<zoho_user_email_address>/<zoho_database_name>
  • Specify https://zohoreportsapi.wiki.zoho.com/JDBC-Driver.html in the Website URL, which allows you to store a link.
  • Open Extra Class Path tab and click Add. You will find option to add JAR files.
  • To ensure it is added properly, click the List Drivers button. The class name found in the class path of the Zoho Report's JDBC driver will be listed in the Class Name drop down.
  • Click OK.

Steps for Adding Alias

Alias holds all configurations to connect to a database. Create new alias for Zoho Reports to specify the connection parameters by following the steps below.

  • Click Windows > Aliases from the toolbar menu.
  • Click the Create a New Alias (+) icon in the Aliases List window. The Add Alias wizard will open.
  • Specify the alias name for the database connection in the Name field.
  • Select the driver for this alias from the Driver drop down.
  • In the URL field, specify the URL of the database to be connected as follow.
    Format: https://reportsapi.zoho.com/api/<zoho_user_email_address>/<zoho_database_name>
    Example: https://reportsapi.zoho.com/api/david@zoho.com/Sales
  • Specify the Zoho Username and Password. This is optional. You could save the user credentials by specifying here for further data upload.
  • The Test button will allow you to ensure that the parameters you have entered are correct by attempting to connect to your alias.
  • Click OK to save the alias definition.

Connecting to Zoho Reports Database

Follow the steps below to connect to a database in Zoho Reports

  • The newly created alias will be listed in the Aliases List Window. Right-click on alias Name and then click Connect.
  • The connection will be established to your Zoho Reports account and the Object Tree will display the structure of the reporting database in Zoho Reports.

Executing a SQL SELECT Query

SQL SELECT Query can be executed from the SQL tab as said below.

  • Open the SQL tab.
  • Specify the required SQL SELECT query to execute in your Zoho Reports database.
  • Click Run SQL icon in the session Window toolbar to execute the query.>
  • Only SQL SELECT queries can be executed in Zoho Reports from the tool.

Next: Zoho Reports CloudSQL Client Libraries


    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