Databases

Information on connecting to different databases with Pentaho.

JDBC drivers

See our JDBC drivers page.

MySQL

See also: Using My SQL

The Pentaho BI server can be downloaded from Sourceforge in a version that uses MySQL as its backend. For production purposes, this is vastly preferable to using the HSQL version (which really only exists for testing/evaluation).

Using Pentaho to connect to a MySQL database is quite well documented elsewhere.

PostgreSQL

Using Pentaho to connect to a PostgreSQL is quite well documented elsewhere.

We have instructions for employing PostgreSQL more directly as the backend of Pentaho BI Server 1.x.

Other resources:

• Subverting PostgreSQL Aggregates for Pentaho

Microsoft SQL Server and Sybase

Jboss and Pentaho use and recommend jTDS as a connector to Microsoft SQL server (6.5, 7, 2000 and 2005) and Sybase (10, 11, 12, 15). The FAQ answers many questions that you may have, including database connections strings. Keep the results sets types in mind when connecting.

One thing to make sure of is whether the database is accepting connections over TCP/IP.

The Spoon 3 Users' Guide covers MS SQL Server briefly on page 33.

More help at the Pentaho Wiki.

Testing database connections

If you're having trouble establishing a connection to a database, answer these questions:

• are the appropriate JDBC drivers installed?
• check your JNDI URL and authentication (username and password)
• does the network allow you to connect to the database port?
• are the database permissions/grants allowing the connection?

$ telnet <addr> <port>

Where <addr> is the address of the host you're connecting to, and <port> is the port to connect to. It is better to use an IP address instead of a host name to avoid name resolution (DNS or hosts file) issues.

Here's a set of default ports for different databases:

• MySQL - 3306
• PostgreSQL - 5432
• Oracle (thin) - 1521
• Microsoft SQL Server - 1433

Here are some resources to find the connection settings for your database:

• Spoon User Guide: Database Connections
• Configuring the Platform for Other Databases
• Setting Up Your Database Connections
• Configuring JNDI Datasources for use in the Pentaho BI Platform
• Pete's Guide to JDBC Driver URLs

I still can't connect (MySQL example)

If a telnet to the address does not connect, you should check the database itself.

Here's an example for MySQL. This type of problem is seen commonly on (but is not exclusive to) Debian GNU/Linux and derivatives such as Ubuntu.

Assume you're running MySQL on your local machine but are having trouble connecting to it on localhost (127.0.0.1). Your LAN IP address is 10.8.8.123.

1. Verify that MySQL is running.

   $ ps aux|grep mysql
   root     30561  0.0  0.1   1772   524 pts/0    S    12:27   0:00 /bin/sh /usr/bin/mysqld_safe
   mysql    30601  0.1  3.2 127224 16488 pts/0    Sl   12:27   0:07 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
   root     30602  0.0  0.1   2920   688 pts/0    S    12:27   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
   user     30707  0.0  0.1   3012   772 pts/0    S+   13:37   0:00 grep mysql

2. On the machine running the database, check what address(es) MySQL is listening on.

   $ netstat -tan|grep 3306
   tcp        0      0 10.8.8.123:3306       0.0.0.0:*               LISTEN

   In this configuration, you will have to connect to 10.8.8.123. Notice how there's no mention of 127.0.0.1 or 0.0.0.0, so even if you're running MySQL on localhost you'll still have to connect to 10.8.8.123.
3. Telnet to the address(es).

   $ telnet 10.8.8.123 3306
   Trying 10.8.8.123...
   Connected to 10.8.8.123.
   Escape character is '^]'

   A connection was accepted — MySQL is listening.

   $ telnet localhost 3306
   Trying 127.0.0.1...
   telnet: Unable to connect to remote host: Connection refused

   MySQL won't work on this address. This verifies what we saw in the previous step.

1. Open your my.cnf file (in /etc/ or /etc/mysql/) in a text editor.
2. Look for the line defining the 'bind-address' parameter.

   bind-address            = 10.8.8.123

3. You can set this to 127.0.0.1 if you're only intending to connect locally. Otherwise, you can just disable/delete that line. This is less secure, as it allows connections from any connected network. Use with care on an Internet-facing computer. Fedora/RHEL/CentOS are configured this way, but SELinux handles the security aspects.

• Can't connect to [local] MySQL server
• Causes of Access denied Errors