Goal
This document describes how to connect to a Denodo Virtual DataPort server from Tibco Spotfire. The document explains how to configure these connections using both JDBC and ODBC, however, the JDBC alternative is recommended over ODBC.
Important note: Since Tibco Spotfire version 10.3, a Custom Connector to connect to Denodo is available. We recommend using this Custom Connector instead of the alternative described in this document. You can get more information in this Tibco Community section.
Connecting to Denodo using the JDBC Driver
To allow the Tibco Spotfire client application to use JDBC data sources, first, the data source needs to be configured in the Tibco Spotfire Server.
In the Spotfire Server Installation and Configuration Manual Data Source Templates section, the procedure needed to configure data sources is explained. Those data sources will be available when creating Information Links.
As Denodo’s JDBC driver is not pre-installed as part of the Spotfire Server installation, the driver must be manually installed on the Spotfire Server. The driver file can be copied from the folder <DENODO_HOME>/tools/client-drivers/jdbc of a Denodo installation to the <SPOTFIRE_HOME>/tomcat/lib folder of the Spotfire Server. After adding the JDBC driver, the Spotfire Server must be restarted.
Note: Spotfire does not provide a means to isolate classloading per data source, so a single installation can not simultaneously have different versions of the same drivers loaded.
Once the JDBC driver is added to the Spotfire installation, to add a new data source, an XML template with the data source configuration will be used. The complete list of available configuration settings and default values can be consulted in the XML Settings section of the Spotfire manual. This example can be used as a basic configuration to connect to the Denodo Virtual DataPort:
<jdbc-type-settings> <type-name>Denodo</type-name> <display-name>Denodo</display-name> <driver>com.denodo.vdp.jdbc.Driver</driver> <connection-url-pattern> jdbc:vdb://server:port/database?autoCommit=true </connection-url-pattern> <supports-catalogs>false</supports-catalogs> <supports-schemas>false</supports-schemas> <column‐alias‐pattern>$$name$$</column‐alias‐pattern> <use-ansii-style-outer-join>true</use-ansii-style-outer-join> <metadata-provider> com.spotfire.ws.im.ds.sql.BasicJDBCMetadataProvider </metadata-provider> <supports-procedures>true</supports-procedures> <ping-command>Select 1</ping-command> </jdbc-type-settings> |
To connect to a database configured with the Unicode charset for identifiers, the template must include the ` character for the alias and name patterns:
<jdbc-type-settings> <type-name>Denodo</type-name> <display-name>Denodo</display-name> <driver>com.denodo.vdp.jdbc.Driver</driver> <connection-url-pattern> jdbc:vdb://server:port/database?autoCommit=true </connection-url-pattern> <supports-catalogs>false</supports-catalogs> <supports-schemas>false</supports-schemas> <column‐alias‐pattern>`$$name$$`</column‐alias‐pattern> <column‐name‐pattern>`$$name$$`</column‐name‐pattern> <table‐name‐pattern>`$$name$$`</table‐name‐pattern> <use-ansii-style-outer-join>true</use-ansii-style-outer-join> <metadata-provider> com.spotfire.ws.im.ds.sql.BasicJDBCMetadataProvider </metadata-provider> <supports-procedures>true</supports-procedures> <ping-command>Select 1</ping-command> </jdbc-type-settings> |
IMPORTANT NOTE: In the XML template configuration as part of the connection-url-pattern, it is very important to include the URL parameter autoCommit and to set this parameter to true. Otherwise, Spotfire will use transactions for all queries, even for select statements introducing unnecessary overhead that will make queries run slower. This parameter is only available in the more recent and up to date versions of the Denodo Platform. Other parameters can also be added to the connection URL as needed.
Once the XML configuration file for the data source is ready, the add-ds-template command can be used to finally set up the data source. For more information about this command, check the section Reference:Commands > add-ds-template of the Spotfire Server manual. To add a new data source with name ‘Denodo’ and a configuration named ‘denodo_data_source.xml’ file run:
add-ds-template -n Denodo denodo_data_source.xml |
Once the data source has been added to the Spotfire Server, it will be possible to access data from a Denodo Virtual DataPort database from the Spotfire Client using an Information Link.
Connecting to Denodo using the ODBC Driver
In order to connect to Denodo from Spotfire using ODBC, a DSN pointing to the Denodo Virtual DataPort server needs to be created first as explained in the Virtual DataPort Developer Guide, section Access Through ODBC.
After the DSN is created, to access to it from the Spotfire Client follow these steps:
- Go to File > Open > From Database…
- Choose ODBC Data Provider data source type from the menu.
- Select the Denodo DSN and provide the credentials.
- All views in the virtual database are introspected. Select the views and fields to import ( or write the SQL sentence to issue to Denodo) and provide a data source name.
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Recommended resources Recommendations generated by AI
Questions
