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:
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.votor 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.votYou 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.Driverin 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:
useUnicode=true&characterEncoding=UTF8
" may be required
to handle some non-ASCII characters.
jdbc:sybase:Tds:hostname:port/dbname?user=XXX&password=XXX#SELECT...
".
An earlier attempt using Sybase ASE 11.9.2 failed.
Here are some example command lines that at least have at some point got STILTS running with databases:
stilts -classpath pg73jdbc3.jar \ -Djdbc.drivers=org.postgresql.Driver ...
stilts -classpath mysql-connector-java-3.0.8-bin.jar \ -Djdbc.drivers=com.mysql.jdbc.Driver ...
stilts -classpath ojdbc14.jar \ -Djdbc.drivers=oracle.jdbc.driver.OracleDriver ...
stilts -classpath jtds-1.1.jar \ -Djdbc.drivers=net.sourceforge.jtds.jdbc.Driver ...