Connecting TIBCO Spotfire Information Services to a CData Virtuality server requires a custom JDBC data source template. Below we outline the driver details and provide a correctly structured XML template (rooted at <jdbc-type-settings>) for Spotfire’s latest version (Spotfire 12+). This template defines the JDBC driver class, connection URL pattern, and authentication properties needed to connect to a CData Virtuality server on localhost with default credentials.
Driver and Connection Details for CData Virtuality
Before creating the template, ensure the JDBC driver JAR for CData Virtuality is installed on the Spotfire Server. Copy the datavirtuality-jdbc.jar into Spotfire Server’s classpath (for example, the tomcat/lib directory). The CData Virtuality JDBC driver class name and URL format are as follows:
-
Driver Class: `
com.datavirtuality.dv.jdbc.Driver` -
JDBC URL Format: `
jdbc:cdatavirtuality:<vdb-name>@mm[s]://<host>:<port>`<vdb-name>is the name of the Virtual Database (VDB) to connect to. The primary VDB is typically named “datavirtuality”mmvs.mms: Use@mmfor an unencrypted connection or@mmsfor SSL. The default ports are 31000 formmand 31001 formms
For example, to connect to the primary VDB on a local server over the default port, the JDBC URL would be:
jdbc:cdatavirtuality:datavirtuality@mm://localhost:31000
with Username admin and Password admin We will use these defaults in the template (placeholders can be adjusted for your environment).
Template Structure and Configuration
Spotfire data source templates define a unique <type-name>, the JDBC driver, URL pattern, and various settings/capabilities for the data source. The structure follows the same pattern as the CData MongoDB example, adjusted for the Virtuality driver. Key elements include:
-
<type-name>: A label for this data source type (e.g. “DataVirtuality”). -
<driver>: The fully qualified JDBC driver class (as given above). -
<connection-url-pattern>: The JDBC URL prefix including protocol and any static parts. We include thejdbc:cdatavirtuality:prefix (and default VDB and protocol) here, then supply host/port as separate properties. -
<ping-command>: A simple test query Spotfire can use to verify the connection. We useSELECT 1(which the Virtuality engine will execute as a trivial query). -
<connection-properties>: Individual connection parameters (key/value pairs). For the Virtuality driver we provide: -
These properties will be combined with the URL pattern to form the complete JDBC connection string at runtime. (The CData driver accepts
ServerandPortas properties similarly to other CData connectors.)-
Server– the hostname (e.g.localhost). -
Port– the port (e.g.31000for default non-SSL). -
User– the username for the Virtuality server (e.g.admin). -
Password– the password (e.g.admin).
-
JDBC Capabilities and SQL Settings
Additional tags define how Spotfire should interact with this source. We include typical settings similar to other templates, for example:
-
Fetch and batch sizes for data retrieval (
<fetch-size>10000</fetch-size>,<batch-size>100</batch-size>). -
Supported metadata features (
<supports-catalogs>true</supports-catalogs>,<supports-schemas>true</supports-schemas>, etc.). Data Virtuality’s virtual DB supports schemas (for underlying data sources andINFORMATION_SCHEMA) but operates within a single catalog (the VDB), so it’s safe to leave catalogs enabled (Spotfire will see one catalog corresponding to the VDB). Procedures can be marked unsupported (false) since we typically query tables/views. -
Quoting and naming patterns for identifiers (using
$$name$$placeholders wrapped in quotes, as in other CData templates) to ensure Spotfire generates correct SQL. -
Temp table patterns and creation/drop commands for Spotfire’s internal use (same conventions as other JDBC templates).
-
<data-source-authentication>false</data-source-authentication>to indicate that the template will supply its own credentials (via the User/Password properties) rather than using Spotfire’s stored credentials.
All these elements are wrapped in the <jdbc-type-settings> root tag. Below is the complete XML template incorporating the above settings.
XML Template for CData Virtuality Data Source
Use the following XML as the data source template in the Spotfire Server Configuration (under Configuration > Data Source Templates > New). This template is designed for the CData Virtuality JDBC driver on a local server with default admin/admin credentials – adjust host/port or credentials placeholders as needed for your environment. The template is compatible with Spotfire Server 12 (and later):
<jdbc-type-settings> <type-name>DataVirtuality</type-name> <driver>com.datavirtuality.dv.jdbc.Driver</driver> <connection-url-pattern>jdbc:cdatavirtuality:datavirtuality@mm://</connection-url-pattern> <ping-command>SELECT 1</ping-command> <connection-properties> <connection-property> <key>Server</key> <value>localhost</value> </connection-property> <connection-property> <key>Port</key> <value>31000</value> </connection-property> <connection-property> <key>User</key> <value>admin</value> </connection-property> <connection-property> <key>Password</key> <value>admin</value> </connection-property> </connection-properties> <fetch-size>10000</fetch-size> <batch-size>100</batch-size> <max-column-name-length>32</max-column-name-length> <table-types>TABLE, VIEW</table-types> <supports-catalogs>true</supports-catalogs> <supports-schemas>true</supports-schemas> <supports-procedures>false</supports-procedures> <supports-distinct>true</supports-distinct> <supports-order-by>true</supports-order-by> <column-name-pattern>"$$name$$"</column-name-pattern> <table-name-pattern>"$$name$$"</table-name-pattern> <schema-name-pattern>"$$name$$"</schema-name-pattern> <catalog-name-pattern>"$$name$$"</catalog-name-pattern> <procedure-name-pattern>"$$name$$"</procedure-name-pattern> <column-alias-pattern>"$$name$$"</column-alias-pattern> <string-literal-quote>'</string-literal-quote> <max-in-clause-size>1000</max-in-clause-size> <condition-list-threshold>10000</condition-list-threshold> <expand-in-clause>false</expand-in-clause> <table-expression-pattern>[$$catalog$$.][$$schema$$.]$$table$$</table-expression-pattern> <procedure-expression-pattern>[$$catalog$$.][$$schema$$.]$$procedure$$</procedure-expression-pattern> <procedure-table-jdbc-type>0</procedure-table-jdbc-type> <procedure-table-type-name></procedure-table-type-name> <date-format-expression>$$value$$</date-format-expression> <date-literal-format-expression>'$$value$$'</date-literal-format-expression> <time-format-expression>$$value$$</time-format-expression> <time-literal-format-expression>'$$value$$'</time-literal-format-expression> <date-time-format-expression>$$value$$</date-time-format-expression> <date-time-literal-format-expression>'$$value$$'</date-time-literal-format-expression> <java-to-sql-type-conversions>VARCHAR($$value$$) VARCHAR(255) INTEGER BIGINT REAL DOUBLE PRECISION DATE TIME TIMESTAMP</java-to-sql-type-conversions> <temp-table-name-pattern>$$name$$#TEMP</temp-table-name-pattern> <create-temp-table-command>CREATE TABLE $$name$$#TEMP $$column_list$$</create-temp-table-command> <drop-temp-table-command>DROP TABLE $$name$$#TEMP</drop-temp-table-command> <data-source-authentication>false</data-source-authentication> <lob-threshold>-1</lob-threshold> <use-ansii-style-outer-join>false</use-ansii-style-outer-join> <credentials-timeout>86400</credentials-timeout> </jdbc-type-settings>
This XML can be saved and uploaded as a new data source template in Spotfire Server’s configuration. After adding the template, restart the Spotfire Server, then create an Information Services data source using this template. You should be able to connect to the CData Virtuality server and build information links against the virtual databases.
