Search

Your Notifications (0)

There are currently no notifications. You're all set!

Knowledge Base
ChemDraw and ChemOffice+ Products Signals Notebook E-Notebook and ChemBioOffice Enterprise Spotfire and Signals Spotfire Products Image Artist Spectrus, Percepta, Katalyst, Luminata and more
Downloads
Downloads
Community
Ideas Portal Product Basics
About Support
Legal Resources Support and Maintenance Plans Product Life Cycle Support News
Contact Support

Databases - Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)

Tanno, Shinichi
  • Updated
Date Posted:
Product: TIBCO Spotfire®

Problem:

Databases - Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)


Solution:

Connect TIBCO Spotfire® Data Science to MySQL and MariaDB (JDBC)

Follow these steps to configure the MySQL data source
 
1. Download the MySQL JDBC jar file from here and copy the mysql-connector-java-5.0.8-bin.jar file to the following locations: /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/   AND   /usr/local/chorus/shared/libraries/. For MariaDB, download this jar file and copy it to the same mentioned locations. 

2. Make sure that chorus user owns those copies (for MySQL and MariaDB): 
chown chorus:chorus /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/mysql-connector-java-5.0.8-bin.jar
chown chorus:chorus /usr/local/chorus/shared/libraries/mysql-connector-java-5.0.8-bin.jar
  
chown chorus:chorus /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc_driver/Public/mariadb-java-client-1.1.7.jar
chown chorus:chorus /usr/local/chorus/shared/libraries/mariadb-java-client-1.1.7.jar

3. Make sure that the appropriate permissions are granted to the user in MySQL that you are using in the data source connection: 
[root@green ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> GRANT ALL ON *.* TO root@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)
mysql>
  
4. Edit the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mariadb/driver.properties file to have the following content (for MariaDB): 
# Specify the JDBC class driver for the desired database type.
# Examples:
# Oracle = oracle.jdbc.driver.OracleDriver
# Greenplum = org.postgresql.Driver
# DB2 = com.ibm.db2.jcc.DB2Driver
# Netezza = org.netezza.Driver
# PostgreSQL = org.postgresql.Driver
# SQLServer = com.microsoft.sqlserver.jdbc.SQLServerDriver
# MySQL = com.mysql.jdbc.Driver
# Teradata = com.teradata.jdbc.TeraDriver
# Vertica = com.vertica.jdbc.Driver
# Sybase = com.sybase.jdbc2.jdbc.SybDriver
# Informix = com.informix.jdbc.IfxDriver
# SAPDB = com.sap.dbtech.jdbc.DriverSapDB
# InterBase = interbase.interclient.Driver
# HSqlDB = org.hsqldb.jdbcDriver
# MariaDB = org.mariadb.jdbc.Driver
# MySQL = com.mysql.jdbc.Driver
driverClass=org.mariadb.jdbc.Driver
useCatalog=true

If /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mysql directory doesn't exist, copy the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mariadb directory and rename it to mysql. Edit the /usr/local/chorus/shared/ALPINE_DATA_REPOSITORY/jdbc/mysql/driver.properties file to have the following content (for MySQL):
  
# Specify the JDBC class driver for the desired database type.
# Examples:
# Oracle = oracle.jdbc.driver.OracleDriver
# Greenplum = org.postgresql.Driver
# DB2 = com.ibm.db2.jcc.DB2Driver
# Netezza = org.netezza.Driver
# PostgreSQL = org.postgresql.Driver
# SQLServer = com.microsoft.sqlserver.jdbc.SQLServerDriver
# MySQL = com.mysql.jdbc.Driver
# Teradata = com.teradata.jdbc.TeraDriver
# Vertica = com.vertica.jdbc.Driver
# Sybase = com.sybase.jdbc2.jdbc.SybDriver
# Informix = com.informix.jdbc.IfxDriver
# SAPDB = com.sap.dbtech.jdbc.DriverSapDB
# InterBase = interbase.interclient.Driver
# HSqlDB = org.hsqldb.jdbcDriver
# MariaDB = org.mariadb.jdbc.Driver
# MySQL = com.mysql.jdbc.Driver
driverClass=com.mysql.jdbc.Driver
useCatalog=true

5. Restart Spotfire® Data Science and go to the data source configuration page. Configure it similar to the attached screenshot. 

For MariaDB, use this type of JDBC Url: 
jdbc:mariadb://<ip/fqdn>:3306/?sessionVariables=sql_mode=ANSI_QUOTES
For MySQL, use this type of JDBC Url: 
jdbc:mysql://<ip/fqdn>:3306/?sessionVariables=sql_mode=ANSI_QUOTES
For more information regarding connection to other data sources, look into this page - Connecting Spotfire Data Science to Data Sources

Related articles

Comments

0 comments

Article is closed for comments.