Next Previous Up Contents
Next: JyStilts - STILTS from Python
Up: Invocation
Previous: System Properties

### 3.4 JDBC Configuration

This section describes additional configuration which must be done to allow the commands to access SQL-compatible relational databases for reading or writing 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 STILTS with SQL-compatible databases you must:

- Have access to an SQL-compatible database locally or over the network
- Have a JDBC driver appropriate for that database
- Install that driver for use with STILTS
- Know the format the driver uses for URLs to access database tables
- Have appropriate privileges on the database to perform the desired operations

Installing the driver consists of two steps:

1. Ensure that the classpath you are using includes this driver class as described in Section 3.1
2. Set the `jdbc.drivers` system property to the name of the driver class as described in Section 3.3

Here is an example of using `tpipe` to write the results of an SQL query on a table in a MySQL database as a VOTable:

       stilts -classpath /usr/local/jars/mysql-connector-java.jar \
              -Djdbc.drivers=com.mysql.jdbc.Driver \
              tpipe \
              in="jdbc:mysql://localhost/db1#SELECT id, ra, dec FROM gsc WHERE mag < 9" \
              ofmt=votable gsc.vot

or invoking Java directly:

       java -classpath stilts.jar:/usr/local/jars/mysql-connect-java.jar \
            -Djdbc.drivers=com.mysql.jdbc.Driver \
            uk.ac.starlink.ttools.Stilts tpipe \
            in="jdbc:mysql://localhost/db1#SELECT id, ra, dec FROM gsc WHERE mag < 9" \
            ofmt=votable out=gsc.vot

You have to exercise some care to get the arguments in the right order here - see Section 3.

Alternatively, you can set some of this up beforehand to make the invocation easier. If you set your CLASSPATH environment variable to include the driver jar file (and the STILTS classes if you're invoking Java directly rather than using the scripts), and if you put the line

       jdbc.drivers=com.mysql.jdbc.Driver

in the `.starjava.properties` file in your home directory, then you could avoid having to give the `-classpath` and `-Djdbc.drivers` flags respectively.

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, STILTS has 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.
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 that at least have at some point got STILTS running with databases:

PostgreSQL

         stilts -classpath pg73jdbc3.jar \
                -Djdbc.drivers=org.postgresql.Driver ...

MySQL

         stilts -classpath mysql-connector-java-3.0.8-bin.jar \
                -Djdbc.drivers=com.mysql.jdbc.Driver ...

Oracle

         stilts -classpath ojdbc14.jar \
                -Djdbc.drivers=oracle.jdbc.driver.OracleDriver ...

SQL Server with jTDS

         stilts -classpath jtds-1.1.jar \
                -Djdbc.drivers=net.sourceforge.jtds.jdbc.Driver ...

Next Previous Up Contents
Next: JyStilts - STILTS from Python
Up: Invocation
Previous: System Properties

STILTS - Starlink Tables Infrastructure Library Tool Set
Starlink User Note256
STILTS web page: http://www.starlink.ac.uk/stilts/
Author email: m.b.taylor@bristol.ac.uk
Mailing list: topcat-user@jiscmail.ac.uk