Metawerx SJSQL - Simple JDBC SQL Command Line Batch Tool (OpenSource)

This is a free java command line application which can be used to execute SQL batch files.

Logging is provided through System.out (stdout) and System.err (stderr).

The tool is completely free, and full source code is included.

Comments welcome!

Download

• See the Attachments section at the bottom of this page

Compatibility

• Any JDBC driver (any database)
• Java JDK 1.4 and higher
• Windows, Linux, BSD, Solaris and most other Java-enabled platforms

Instructions (Windows/Linux)

• Download the class (and optional java source) files below, into a folder
• Download a JDBC driver JAR file into the same folder
• Use the following command to run the tool, from the same folder you downloaded to:

java -cp <classPath> sjsql <driverClass> <jdbcString> <userName> <password> <sqlFile>

• The classPath needs to include the JDBC driver you are going to use, and the current folder.
• Example for Windows, with SQL/Server, using the jTDS driver. Note that on Windows, the java classpath uses a semicolon (;) to separate paths.

java -cp .;jtds-1.2.jar sjsql net.sourceforge.jtds.jdbc.Driver jdbc:jtds:sqlserver://localhost:1433 user pass infile.sql

• Example for Linux, with MySQL, using the MySQL JDBC Connector. Note that on Linux, the java classpath uses a colon (:) to separate paths.

java -cp .:mysql-connector-java-3.1.13-bin.jar sjsql com.mysql.jdbc.Driver jdbc:mysql://localhost:3306/myDatabase user pass infile.sql

Command Line Options

Execute the tool without any command line options to see the help

driverClass - JDBC driver class name, eg: com.mysql.jdbc.Driver
jdbcString - JDBC connection string, eg: jdbc:mysql://localhost:3306/myDatabase
userName - database username
password - database password
sqlFile - list of SQL statements, one per line

Additional Switches (add these at the end of the command line, after the filename)

--start 123 - line number to start from, for resuming after errors. Default is 1.
--ignore-nodata - if query response is no-data (eg: INSERT statements) then don't log. Default is log all results.
--log-queries - include each query in the output. Default is to only log results.
--batch 500 - send 500 queries at a time, wrapped in a transaction (good for remote connections).
--batch all - wrap entire file in a transaction, call rollback on failure.

Notes

Line numbers start from 1 (not 0)
Each line is treated as a separate query.  Semicolons or other query-terminators are optional and will be ignored.
Query logging is sent to System.out (can be redirected with >results.log)
Other output is sent to System.err (can be redirected with 2>output.log)

Troubleshooting

• If you run large files (50mb or more) in batch mode (eg: --batch 5000), there is a chance java will run out memory. The error will be something like this:

[sjsql] Exception at line 70000: java.lang.OutOfMemoryError: Java heap space
java.lang.OutOfMemoryError: Java heap space
        at java.lang.String.toCharArray(String.java:2726)
        at net.sourceforge.jtds.jdbc.SQLParser.<init>(SQLParser.java:234)
        at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:155)
        at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:625)
        at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1114)
        at sjsql.execute(sjsql.java:385)
        at sjsql.process(sjsql.java:293)
        at sjsql.nonStaticMain(sjsql.java:220)
        at sjsql.main(sjsql.java:76)
[sjsql] Errors occurred - changes rolled back
[sjsql] Errors occurred - exiting, start next run from line 65001 using the argument --start 65001

In this case, use a smaller batch size, or provide java with more memory by putting a memory argument directly after the java command. For example -Xmx256m will provide 256mb.

Your data is still safe, and committed to line 65000. In the error above, the tool is instructing you how to start the next batch from line 65001, using the --start parameter.

OpenSource - Improve this tool

• You are welcome to make improvements to this tool, as long as you are happy with the copyright/terms below remaining the same.
• Your name will be included in the revision notes, and you are welcome to add a page describing yourself or linking to your site if you do any work on the software.
• To upload a new version, register on this wiki. Registration is free, and only a name, password and optional email address are required.
• Upload the new version as a class file and new Java source, as an attachment to this wiki page, then add revision notes below.
• Please use tabs for indenting

Copyright/Terms - By downloading the software, you agree to the following:

• This software and source code is (c) Copyright Metawerx Pty Ltd, 2006.
• This software and source code is provided free of charge and with absolutely no warranty
• You may download and use this software for any purpose, as long as it is legal to do so in your country
• Distribution, re-use, re-engineering and inclusion in or with any software product is permitted, provided that distributions of the source code, or inclusion of the source code in your product, retains the copyright notice and a link to this wiki page. Binary distributions carry no similar requirement.
• Metawerx is not responsible for any damage of any sort associated with this software, or costs incurred through using it.

Change Log

• 2006-Nov-10 - Neale Rudd - Initial Release
• 2007-Jan-17 - Neale Rudd - Now requires JDK1.4
• 2007-Jan-17 - Neale Rudd - Added batch support, for much better performance over slow connections, now 5000 queries can be batched together instead of executing each one separately. This also provides transaction support.
• 2007-Jan-17 - Neale Rudd - Added transaction support, allowing the entire file to be wrapped in a transaction and rolled back on error.
• 2007-Jan-17 - Neale Rudd - Removed static dependency, allowing use of the functionality as a class from a threaded application.
• 2007-Jan-17 - Neale Rudd - Added better instructions after a crash, so resume is easier.

--Neale Rudd

Attachments