If you want to connect to a data source driver that doesn’t ship with EspressChart, CData JDBC drivers are an interesting option.
CData JDBC drivers can be also used to increase capabilities of non-database data sources like Excel or JSON.
![[Tip]](../../../images/tip.png) | Tip |
|---|---|
Although CData JDBC drivers are a 3rd party commercial product, you can install a free trial version to test the product before purchasing. |
![[Note]](../../../images/note.png) | Note |
|---|---|
CData JDBC drivers are a 3rd party product. If you want to use the drivers, you have to purchase them at https://www.cdata.com |
Currently, we support the following CData JDBC drivers:
Salesforce
BigQuery
Excel
JSON
MongoDB
Kintone
Other CData JDBC drivers might work too but we can not guarantee full functionality for unsupported drivers. If you require a connection to a driver that is not listed here, please contact us at <support@quadbase.com>
Download: https://www.cdata.com/drivers/excel/jdbc
Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/RXF/jdbc
After downloading the driver, see the following chapters: Section 4.12.2 - CData JDBC driver installation, Section 4.12.3 - Deploying the CData JDBC Driver in EspressChart and Section 4.12.4 - Using the CData JDBC drivers in DataSource Manager
The CData Excel driver allows you to run SQL queries on top of an Excel file just like it was a database. This allows you to use the QueryBuilder, build Data Views in a graphical user interface as well as write SQL queries manually (in Query Builder).
| Tip |
|---|---|
You can also write queries with parameters and multi-value parameters using Excel files as the data source. |
Since Excel does not have data types set for columns as a normal database has, determining the data type for the columns can be a bit tricky at times.By default, we added the following parameters to the CData Excel driver connection URL:
TypeDetectionScheme=RowScan;
RowScanDepth=10;
This makes the CData Excel driver scan the first ten rows when loading the selected Excel file and detect the data source for each column automatically based on the data in the first ten rows.
However, there are multiple options of how to determine the data type for each column.
For more options, read the CData JDBC Excel driver documentation about the TypeDetectionScheme parameter: https://cdn.cdata.com/help/RXH/jdbc/RSBExcel_p_TypeDetectionScheme.htm
You can change the TypeDetectionScheme value in the Setup Database… dialog in Data Source Manager in the URL field (the field where you entered the Excel file path).
Download: https://www.cdata.com/drivers/json/jdbc
Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DJF/jdbc
After downloading the driver, see the following chapters: Section 4.12.2 - CData JDBC driver installation, Section 4.12.3 - Deploying the CData JDBC Driver in EspressChart and Section 4.12.4 - Using the CData JDBC drivers in DataSource Manager
The CData json driver allows you to run SQL queries on top of a json file just like it was a database. This allows you to use the QueryBuilder, build Data Views in a graphical user interface as well as write SQL queries manually (in Query Builder).
| Tip |
|---|---|
You can also write queries with parameters and multi-value parameters using JSON files as the data source. |
To connect to Salesforce:
Download: https://www.cdata.com/drivers/salesforce/jdbc
Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/RFF/jdbc
After downloading the driver, see the following chapters: Section 4.12.2 - CData JDBC driver installation, Section 4.12.3 - Deploying the CData JDBC Driver in EspressChart and Section 4.12.4 - Using the CData JDBC drivers in DataSource Manager
To connect to MongoDB:
Download: https://www.cdata.com/drivers/mongodb/jdbc
A bug fix was added to newer MongoDB driver from https://cdatabuilds.s3.amazonaws.com/support/DGRJV_8598.exe
Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DGF/jdbc
After downloading the driver, see the following chapters: Section 4.12.2 - CData JDBC driver installation, Section 4.12.3 - Deploying the CData JDBC Driver in EspressChart and Section 4.12.4 - Using the CData JDBC drivers in DataSource Manager
To connect to BigQuery:
Download: https://www.cdata.com/drivers/bigquery/jdbc
Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DBF/jdbc
After downloading the driver, see the following chapters: Section 4.12.2 - CData JDBC driver installation, Section 4.12.3 - Deploying the CData JDBC Driver in EspressChart and Section 4.12.4 - Using the CData JDBC drivers in DataSource Manager
To connect to Kintone:
Download: https://www.cdata.com/drivers/kintone/jdbc
Official CData Documentation (for the JDBC driver only): https://cdn.cdata.com/help/DBF/jdbc
After downloading the driver, see the following chapters: Section 4.12.2 - CData JDBC driver installation, Section 4.12.3 - Deploying the CData JDBC Driver in EspressChart and Section 4.12.4 - Using the CData JDBC drivers in DataSource Manager
The installation process of all CData JDBC drivers is basically the same for all the supported data sources. We’ll guide you through the process of installing the CData Excel JDBC driver for example.
First, install the CData JDBC Driver of your choice. You will find the links to each supported CData JDBC driver in the chapters below.
After downloading the installer, proceed with the setup according to the dialogs.
In the following dialog, you can choose to either install a paid version of the product or a free 30-day trial version. In this example, we’ll use the trial version.
When the setup is complete, JDBC Driver will be created in "CData JDBC Driver for Microsoft Excel 2022\lib".
| Tip |
|---|---|
Executing "cdata.jdbc.excel.jar" will launch the connection test tool. You can use this tool to test or troubleshoot the CData driver. |
| Tip |
|---|---|
The connection test tool also displays you the connection URL that can be used in EspressChart Data Source Manager. |
Locate the "CData Installation Directory"\CData JDBC Driver for Microsoft Excel 2023\lib (for example: C:\Program Files\CData\CData JDBC Driver for Microsoft Excel 2023\lib) on your hard drive.The directory should contain three files: cdata.jdbc.excel.jar, cdata.jdbc.excel.lic, cdata.jdbc.excel.remoting
Copy the three files to EC/lib/
After you've copied the files, restart The EspressManager (if it is running).
Launch the DataSource Manager and open a data registry in it (existing one or a new one).
Select the “Databases” option in the tree-list and press the “ADD” Button.
In the “Driver List:” drop-down menu, select “CData Excel” (or any other CData JDBC driver you might be installing).
In the “URL:” text field, replace the placeholders (like "EXCEL FILE LOCATION") with real values.
| Tip |
|---|---|
Alternatively, you can replace the text in the “URL:” text field with the connection string obtained by the CData Test Connection tool described in the previous chapter. |
Click OK. You’re done. You can start using Excel files as if they were a database.