Spotfire® JDBC Connectivity for TIBCO ActiveSpaces® 4.x

The ActiveSpaces JDBC Driver

The TIBCO ActiveSpaces JDBC driver implements the Java Database Connectivity (JDBC) API. The driver provides TIBCO ActiveSpaces data grid connectivity for TIBCO Spotfire and other Java applications by using JDBC. The TIBCO ActiveSpaces JDBC driver is a Type 2 driver and makes native library calls for communicating with the data grid. 

The TIBCO ActiveSpaces JDBC driver is bundled with the TIBCO ActiveSpaces Java client API and included in tibdg.jar.

1. Locate tibdg.jar at <TIBCO_HOME>/as/<VERSION>/lib
2. Copy that jar and place it in the <tibco_spotfire_server_home>/<VERSION>/tomcat/custom-ext

Setting up the Environment 

TIBCO Spotfire needs to get a reference to both <FTL_HOME>/bin and <AS4x_HOME>/bin.Please add both paths to System Environmental variables PATH (Windows) or LD_LIBRARY_PATH (Linux).

Example:

Windows

 set PATH=C:/tibco/ftl/6.5/bin;C:/tibco/as/4.5/bin;%PATH%

Linux

 export LD_LIBRARY_PATH=/opt/tibco/ftl/6.5/bin:/opt/tibco/as/4.5/bin:$LD_LIBRARY_PATH

Add JDBC Data Source to Spotfire Server

1. Find below the data source template for TIBCO ActiveSpaces 4.x JDBC. You can use it as a template. A further modification is documented here
2. Launch the Spotfire configuration tool.
3. Click on the Configuration tab.
4. Click on DataSource Templates on the left pane.
5. On the right pane, click on on "New", type in a name (I use TIBCO ActiveSpaces 4.x) and paste the data source template within the "Data source template" section.
6. Click on "OK" and make sure that the status shows the following message at the bottom of the page "XML parsed OK.
7. Confirm that the data source is enabled with a checkbox.
8. Save the configuration and restart the Spotfire service for changes to take effect

<jdbc-type-settings>
  <type-name>tibco_datagrid</type-name>
  <driver>com.tibco.datagrid.jdbc.DriverImpl</driver>
  <connection-url-pattern>jdbc:tibco:tibdg;gridname=[gridname];realmurl=[realmurl]</connection-url-pattern>
  <ping-command>SELECT * from ms</ping-command>
  <fetch-size>10000</fetch-size>
  <table-types>TABLE</table-types>
  <supports-catalogs>false</supports-catalogs>
  <supports-schemas>false</supports-schemas>
  <supports-procedures>false</supports-procedures>
  <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>$$table$$</table-expression-pattern>
  <java-to-sql-type-conversions>
    <type-mapping>
      <from>String</from>
      <to>STRING</to>
    </type-mapping>
    <type-mapping>
      <from>Integer</from>
      <to>INTEGER</to>
    </type-mapping>
    <type-mapping>
      <from>Long</from>
      <to>LONG</to>
    </type-mapping>
    <type-mapping>
      <from>Float</from>
      <to>FLOAT</to>
    </type-mapping>
    <type-mapping>
      <from>Double</from>
      <to>DOUBLE</to>
    </type-mapping>
  <type-mapping>
      <from>DATE</from>
      <to>DATETIME</to>
    </type-mapping>
  <type-mapping>
      <from>TIME</from>
      <to>DATETIME</to>
    </type-mapping>
  <type-mapping>
      <from>TIMESTAMP</from>
      <to>DATETIME</to>
    </type-mapping>
    <type-mapping>
      <from>DateTime</from>
      <to>DATETIME</to>
    </type-mapping>
  </java-to-sql-type-conversions>
  <temp-table-name-pattern>$name$</temp-table-name-pattern>
  <create-temp-table-command>CREATE TABLE $name$ $column_list$</create-temp-table-command>
  <drop-temp-table-command>DROP TABLE $name$</drop-temp-table-command>
  <use-ansii-style-outer-join>true</use-ansii-style-outer-join>
</jdbc-type-settings>

Create Data Source from Information Designer

In the Spotfire Information Designer, set up a new data source and choose TIBCO ActiveSpaces 4.x as the type. Below is the connection URL pattern:

 jdbc:tibco:tibdg;gridname=[gridname];realmurl=[realmurl] 

• [gridname] is your Datagrid name. The default name is _default
• [realmurl] is your Realm Url. The default is http://localhost:8080