usage notes

For each usage you need to supply values for: JDBC URL, driver, username, password
You can either provide these as options, or store them in a presets file and refer to them by name using the --preset option.

Sample presets.properties file: save this as ${HOME}/.xql/presets.properties; or specify another location using the --presetfile option.

XQL will need access to the JDBC driver classes provided by your database vendor. You supply the name of the main driver class using the --driver option, XQL will need to find this class. There are 2 ways you can provide the class to XQL:

1. put the class/jar in the java classpath (e.g. by suppling a -cp option to java)
2. use XQL's --cp option to set an 'extra' classpath that is searched in addition to the System classpath. For Example:
java -jar xql.jar --cp driver.jar:driver2.jar --driver com.foo.db.Driver ...
loads com.foo.db.Driver by scanning driver.jar and driver2.jar in addition to the regular system classpath

ChangeLog

October 2009

Use executeBatch() in copytable if --force option not set. (much faster)

Use ps.setDouble(..) meths instead of setObject (on SQL server was not preserving the precision, so Double was getting rounded on copy. Uses dedicated meths now for most recognisable types (Double,Float,Int,Varchar etc.).

September 2008

Can put surround values in presets.properties with double-quote characters, in which case commas inside them are ignored. This allows (for example) connection strings that contain commas to be used.

January 2008

Bugfix: --listpresets now works

September 2007

Bugfix: --copytable used to ignore the final column of a table - no longer does this

April 2007 version 0.24

Modified so can leave password/name empty in .xql/presets.properties and it will be treated as the empty string ("").

Jan 2007 version 0.23

• Added copytable option: for copying a table from one database to another (works across any 2 JDBC connections). You provide preset names for each JDBC connection, the table you want to insert into, and an SQL statement that returns a resultset (e.g. SELECT statement). XQL generates the resultset, then creates a prepared statement with the same SQL Types as the donor resultset. It executes the statement once per row. It is possible to use the --force option to ignore errors caused by such things as duplicate detection and continue anwyay.
• Added --force option. In the case where you execute multiple SQL statements in a batch, XQL continues even if one SQL statement causes an SQLException.

March 2006 version 0.22

Now supports queries which return multiple resultsets (prints one out after another). Also, if query updates DB, prints out update count, rather than reporting an error. (e.g. don't have to use the -u flag - though it may be marginally faster if you do so.

Feb 2006 version 0.21

fixed bug where --scriptfile arg not working

Oct 2005 (version 0.2 -- not backwards compatible)

• Changed to use yago instead of Jakarta CLI for commandline reading. Made some fixes to make error messages more informative at same time, and changed preset-save/list funcs.
• changed from -v option for verboseness to use java.util.Logging, so have to fiddle with ${JRE_HOME}/lib/logging.properties file to make output more verbose. By default this file writes everything at INFO level and above to std.err. Programme output (e.g. resultsets) still goes to std.out.
• changed commandline parser to GnuParser, so some options (e.g. -cp had to become --cp) to satisfy the parser.

Summer 2003 version 0.1

Ported to Java and made improvements, such as configurable formatting of results and update queries

Summer 2003

First created python script for executing SQL queries using JDBC

Notes

Sybase

Data dictionary example: select t.name,c.name from sysobjects t,syscolumns c where c.id=t.id and t.type='U' order by t.name,c.colid (thanks to SB)