9.3 JDBC Configuration

This section describes additional configuration which must be done to allow TOPCAT to access SQL-compatible relational databases for reading (see Section 4.1.7) or writing (see Section 4.2.7) tables. If you don't need to talk to SQL-type databases, you can ignore the rest of this section. The steps described here are the standard ones for configuring JDBC (which sort-of stands for Java Database Connectivity), described in more detail on Sun's JDBC web page.

To use TOPCAT with SQL-compatible databases you must:

Installing the driver consists of two steps:
  1. Set the jdbc.drivers system property to the name of the driver class as described in Section 9.2.3
  2. Ensure that the classpath you are using includes this driver class as described in Section 9.2.1

These steps are all standard for use of the JDBC system.

To the author's knowledge, TOPCAT has so far successfully been used with the following RDBMSs and corresponding JDBC drivers:

MySQL
MySQL 3.23.55 on Linux has been tested with the Connector/J driver version 3.0.8 and seems to work, though tables with very many (hundreds of) columns cannot be written owing to SQL statement length restrictions. Note there is known to be a column metadata bug in version 3.0.6 of the driver which can cause a ClassCastException error when tables are written.
PostgreSQL
PostgreSQL 7.4.1 apparently works with its own driver. Note the performance of this driver appears to be rather poor, at least for writing tables.
Oracle
You can use Oracle with the JDBC driver that comes as part of its Basic Instant Client Package. However, you can't currently use the SQL load/SQL save dialogue boxes to do it. You have to specify a JDBC URL specifying the query to read/table to write as a string in the Location field of the normal table load/save dialogue boxes. The URL will look something like
    jdbc:oracle:thin:@//hostname:1521/database#SELECT ...
    
for querying an existing database (loading) and
    jdbc:oracle:thin:@//hostname:1521/database#new-table-name
    
for writing a new table (saving).
Other RDBMSs and drivers may or may not work - please let us know the results of any experiments you carry out. Sun maintain a list of JDBC drivers for various databases; it can be found at http://servlet.java.sun.com/products/jdbc/drivers.

Here are example command lines to start up TOPCAT using databases known to work.

PostgreSQL
   java -classpath topcat-full.jar:pg73jdbc3.jar \
        -Djdbc.drivers=org.postgresql.Driver \
        uk.ac.starlink.topcat.Driver
MySQL
   java -classpath topcat-full.jar:mysql-connector-java-3.0.8-bin.jar \
        -Djdbc.drivers=com.mysql.jdbc.Driver \
        uk.ac.starlink.topcat.Driver
Oracle
   java -classpath topcat-full.jar:ojdbc14.jar \
        -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \
        uk.ac.starlink.topcat.Driver