SQL Batch Manager

Highlights

SQLManager is no "big design tool" or something the like, it is just a small tool for certain database operations. I developed it when working on Java Webapplications using Enhydra Application server and DODS (data object design studio). There it is necessary to create databases with a set of SQL statements. Sometimes errors occur and modifications in the SQL text files are not very comfortable. SQL Manager leverages for example these procedures.

• Batch Processing of SQL Tools
• Written in Java with Swing (JFC) Interface hence Platform Independent
• Open Source: Binary as well as Source distribution available, sources are free
• Works with any Database System with a JDBC driver
• Performs a list of arbitrary SQL statements with the functions to:
  • Reorder statements
  • Perform only selected statements
  • Edit SQL statements
  • Save modified Statements
  • Show database response for each statement (perfect for debugging statements or porting database metadata)
  • New: Also SELECT statements do work: Results are converted to XML and can be viewed offline
• Easy integration of many data sources/databases
• Plugin mechanism for SQL parsers for different databases
• more features should follow...

Screenshots

Main Window

(click in image for full size)

Edit Window

Details of Database Messages (Errors)

Show Results of SELECT Statements

When a SELECT statement is performed, the result is converted to XML. These XML files are stored in the temporary XML directory defined in the config.xml.

When displaying the results, these XML files are read and displayed in tabular view. Hence offline browsing of SELECT results is possible. Also reuse of the XML files (representing the SELECT tables) is possible.

Installation

Distribution containing Binaries or Sources

1. Java Virtual Machine (JDK, J2SE) Version 1.2 or 1.3: If you have not installed a JVM on your system please download the appropriate version for your Operating system from http://java.sun.com and install it.
2. Install a JDBC driver for your database(s); Please consult the documentation of the database systems.
3. Add the jdbc driver(s) to the java classpath as described in the database documentation.
4. Download the binary (zip 883k) or the source (zip 917k) distribution of SQLManager.
5. Unzip it into an arbitrary directory
6. Modify the start file
7. • Windows: sqlmanager.bat
   • Unix: sqlmanager.sh
8. Modify the config file to add new database connections or to add new SQL parser drivers
9. Start SQL Manager:
10. • Windows: Start sqlmanager.bat
    • Linux/Unix: Start sqlmanager.sh
    • or manually: ...

Downloads

Binary & Source Distribution

Download the zip file and unzip it using e.g unzip on Unix systems or winzip on Windows OSs. This help file is included in the binary distribution.

• Binary Distribution (zip 883k) containing only jar archive, start script and config file
• Source Code (zip 917k) containing the complete sources

Sources

To compile the sources you need furthermore the JDOM XML parser library. You can download it from http://www.jdom.org

Javadoc Documentation

Download javadoc.zip (included in binary distribution)

How to Modify Start (Batch) File

Windows

The windows batchfile contains only one line:

java -classpath "SQLManager.jar;%CLASSPATH%" sqlmanager.SqlManager config.xml

What happens?

How to Modify Config File

Introduction

The config file is an XML file. However, you don´t have to know much about XML, it is pretty straightforward. Just start an texteditor, open the sample config file and add your modifications. But be aware of the following restrictions:

• XML is case-sensitive, hence you have to write <SQLParserClasses> not <sqlParserClasses> or the like, this would cause errors.
• Each start-tag must be finished with an end-tag.
• Attribute definitions must be made using precisely <tag attrib="value">...</tag>
• At best: use precisely the code as in the sample.

The sample XML file looks like this:

<SQLMANAGER>
  <ApplicationOptions>
    <xml_output_directory>/Java/SQLManager/out</xml_output_directory>
    <xml_encoding>ISO-8859-1</xml_encoding>
  </ApplicationOptions>
  <SQLParserClasses>
    <parser name="Basic">sqlmanager.parser.BasicDriver</parser>
    <parser name="Interbase">sqlmanager.parser.InterbaseDriver</parser>
  </SQLParserClasses>
  <Databases>
    <database connectionname="Interbase-Test">
      <jdbcdriverclass>interbase.interclient.Driver</jdbcdriverclass>
      <jdbcUrl>jdbc:interbase://localhost/C:\DATA\Java\SQLManager\test\SQLMAN.GDB</jdbcUrl>
      <loginname>SYSDBA</loginname>
      <password>masterkey</password>
    </database>
    <database connectionname="InstantDB-Test">
      <jdbcdriverclass>org.enhydra.instantdb.jdbc.idbDriver</jdbcdriverclass>
      <jdbcUrl>jdbc:idb:C:\\DATA\\Java\\SQLManager\\test\\idbTest.prp</jdbcUrl>
      <loginname></loginname>
      <password></password>
    </database>
  </Database>
</SQLMANAGER>

The config file has three sections:

• Application Options: General Options
• SQLParserClasses: In this section new SQL Parsers can be added.
• Databases: In this section new Database connections can be defined

Application Options

xml_output_directory

Enter path to the temporary directory where the XML files are stored, that are generated for the results of SELECT statements.

xml_encoding

Enter the encoding of the temporary XML files that are generated when a SELECT statement is performed. By default this is ISO-8859-1.

Add database Connections

Simply add a new copy one of the sample database definition tags and enter the correct values for

After adding new database connections you can start SQL Manager and Test each connection by selecting the connection name and selecting the menu item Database|Test Connection.

Add new SQL Parser Classes

Write a new SQL Parser class and add the class file and name of the parser into this config file.

Write new SQL Parser Class

Introduction

This is an advanced topic for java programmers.

Each databases have slightly different styles of SQL batch files. For example Interbase has ";" a command separator and " may not be used in the SQL statement when sent as JDBC command. Hence the SQL Parser driver for interbase detects separate statements by using the ";" as separator and removes all " from the statements.

The "basic" driver uses ";" as command separator and makes no further modification.

Howto

For writing you own driver simply perform the following steps:

1. Write a new Java class implementing the sqlmanager.parser interface

2. Implement the methods of this interface

Take a look at the sqlmanager.parser.BasicDriver or sqlmanager.parser.InterbaseDriver sources for examples.

3. Add the new parser class to the config file

by making an entry like:

    <parser name="myDriver">sqlmanager.parser.MyDriver</parser>

Be aware to either copy your driver into the sqlmanager.parser package or add the package with your drivers into the java-classpath. The driver will be loaded dynamically from SQLManager.

1a. Alternatively extend the BasicDriver class

and override only the methods necessary. Usually these are only the methods: parseNext() and eventually sqlCommandSeparator if it is different from ";".

Contact Information / Request for Help

If you like SQLManager (or dislike it) and have further suggestions or bug reports, please send me an email.

Please contact me too if you should have written new drivers for other RDBMS products than Interbase and InstantDB or if you should have written better ones for these two, so that I can add them to the package. This is urgently needed, especially for the following systems:

• Oracle
• DB2
• SAPDB
• PostgreSQL
• mySQL

Or even tell me, if the basic driver suceeds with any of them.