Next Previous Up Contents
Next: Tips for Large Tables
Up: Invoking TOPCAT
Previous: System properties

10.3 JDBC Configuration

This section describes additional configuration which must be done to allow TOPCAT to access SQL-compatible relational databases for reading (see Appendix A.6.4) or writing (see Appendix A.7.2.4) 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); you can find more information on that on the web. The best place to look may be within the documentation of the RDBMS you are using.

To use TOPCAT with SQL-compatible databases you must:

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

Below are presented the results of some experiments with JDBC drivers. Note however that this information may be be incomplete and out of date. If you have updates, feel free to pass them on and they may be incorporated here.

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

MySQL
MySQL has been tested on Linux with the Connector/J driver and seems to work; tested versions are server 3.23.55 with driver 3.0.8 and server 4.1.20 with driver 5.0.4. Sometimes 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. Check the driver's documentation for additional parameters, for instance "useUnicode=true&characterEncoding=UTF8" may be required to handle some non-ASCII characters.
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 may not be able to 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).
SQL Server
There is more than one JDBC driver known to work with SQL Server, including jTDS and its own JDBC driver. Some evidence suggests that jTDS may be the better choice, but your mileage may vary.
Sybase ASE
There has been a successful use of Sybase 12.5.2 and jConnect (jconn3.jar) using a JDBC URL like "jdbc:sybase:Tds:hostname:port/dbname?user=XXX&password=XXX#SELECT...". An earlier attempt using Sybase ASE 11.9.2 failed.
It is probably possible to use other RDBMSs and drivers, but you may have to do some homework.

Here are some example command lines to start up TOPCAT using databases that at least have worked at some point.

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
SQL Server with jTDS
   java -classpath topcat-full.jar:jtds-1.1.jar \
        -Djdbc.drivers=net.sourceforge.jtds.jdbc.Driver \
        uk.ac.starlink.topcat.Driver


Next Previous Up Contents
Next: Tips for Large Tables
Up: Invoking TOPCAT
Previous: System properties

TOPCAT - Tool for OPerations on Catalogues And Tables
Starlink User Note253
TOPCAT web page: http://www.starlink.ac.uk/topcat/
Author email: m.b.taylor@bristol.ac.uk
Mailing list: topcat-user@jiscmail.ac.uk