HenPlus

JDBC SQL-shell

HenPlus is a SQL shell that can handle multiple open sessions in parallel. The commandline interface provides the usual history functions features and TAB-completion for commands, tables and columns.

In order to compile it, you need the java-readline in your classpath.

Overview

HenPlus is a SQL shell written in Java that works for any database that offers JDBC support. So basically any database. Why do we need this ? Any database comes with some shell, but all of them have missing features (and several shells are simply unusable). And if you work with several databases at once (if you are a developer, then you do this all the time), switching between these tools is tedious.

This is where HenPlus steps in. It supports:

• Any JDBC aware database.

• Has context sensitive command line completion for commands and tables / columns / variables within SQL-commands.

• Multiple open connection-sessions can be handled in parallel. You can just switch between them. So you can be connected to different databases to experiment with the same statement in all of them, for example.

  All JDBC-Urls of opened sessions are stored for later command line completion in the connect command.

• Command line history just like in the bash (with cursor-up/down, CTRL-R, ...)

• A describe command for database tables, that work all JDBC-Drivers that unveil the appropriate MetaData (Oracle, PostgreSQL, MySQL, DB2...).

• Supports variables that are expanded like shell variables with $VARIABLENAME or ${VARIABLENAME} (with completion of their names, just like in bash.

• Loading files.

• Supports several built-in commands (start, @, @@, spool) and syntax from the Oracle SQL-plus utility (like the single '/' on a line to close a statement). Most Oracle SQL-plus scripts will run directly, so its simple to switch to HenPlus. Except if you can't stand, that your life will become much simpler, then ;-) If you have problems running your old scripts, please let <a href="mailto:henplus@googlegroups.com">us</a> know.

• Allows a per-project storage of the commandline history, variables, plugins and connections.

• Allows to add your own plugins that behave just like the built-in commands.

• Is provided as Free Software. You are free to modify, copy, share and sell this software under the GPL. See http://www.gnu.org/licenses/gpl.txt for more info.

History

Know this ? You need a small little tool for your everyday work and you wonder why nobody did this before -- and then you write it yourself. HenPlus is the result of such an effort. Its name reflects the original intent: it started as a platform independent replacement for the Oracle SQLPlus tool.

I wrote the first version of HenPlus in 1997 (before Oracle was available for Linux) since I had to develop an application that connected to an Oracle database, but I had no Solaris machine at home -- and the only SQLPlus I could use back then was running on some Redmond operating system that lacked (and still lacks) the environment to seriously develop software.

Since then I've rewritten this tool, added command line completion and history -- but it still remained small and useful. It is freely distributable in source and binary form because it is protected by the GNU Public License. See http://www.gnu.org/licenses/gpl.txt for more info.

Getting started

Download

You can download source and binary packages of HenPlus from the github page at http://github.com/neurolabs/henplus

As of July 2017, HenPlus is included in some distributions, for example <a href="http://www.gentoo.org/">Gentoo</a>. On Debian and derivatives can try the included build and install script. If you have all dependencies installed, a simple:

$ ./debian-install.sh

should build and install HenPlus (or complain about missing dependencies).

Use the respective package management tool to install HenPlus on these distributions.

For an installation on <a href="http://fink.sourceforge.net/">fink</a> for Mac have a look at <a href="http://blog.wannawork.de/index.php/2005/09/18/henplus_on_mac_os_using_fink">Bodo Tasche's blog</a>.

If you have a hard time to get the java-readline compiled on Mac OS X, then you might want to check out <a href="http://toonetown.blogspot.com/2006/07/java-readline-on-mac-os-x-update.html">Nathan Toone's blog</a> for an installation description.

Compilation

Follow these instructions if you want to compile HenPlus yourself. First you need an additional library. HenPlus uses the features of the GNU-readline library and therefore needs the JNI java wrapper library.

<a href="http://sourceforge.net/project/showfiles.php?group_id=48669">java-readline 0.7.3</a> ( <a href="http://toonetown.blogspot.com/2006/07/java-readline-on-mac-os-x-update.html">compilation on Mac OS X</a> )

To build HenPlus the <a href="http://jakarta.apache.org/ant">ant build tool</a> (Version >= 1.4) is required.

Now, just type

$ ant jar

If you are root, then you can install it with:

# ant install

which will install henplus in

/usr/share/henplus/henplus.jar The jar-file containing the HenPlus classes. You can add additional jar files in this directory. All of them are added to the classpath in the henplus shellscript (use this for JDBC-drivers).

/usr/bin/henplus Shellscript to start henplus.

If you want another installation base (default: /usr), you provide this with the parameter prefix:

$ ant -Dprefix=/usr/local install

(For package providers: the build.xml provides as well the DESTDIR parameter with a similar meaning as in usual Makefiles).

If you've created packages for other operating systems (like Solaris) or Windows, please <a href="mailto:henplus@googlgroups.com">let us know</a>.

We haven't compiled this on Windows, but it shouldn't be a big deal if you manage to compile the java-readline. This is an JNI shared library and thus platform dependant. If you did it, please post your experience, so that we can include it in this documentation.

Running

You can start HenPlus with the henplus shell script with or without an jdbc-url on the command line.

$ henplus jdbc:mysql://localhost/foobar

Make sure, command line editing is enabled

If the first line, henplus writes reads:

no readline found (no JavaReadline in java.library.path). Using simple stdin.

then, the JNI-part of the readline library could not be found, so command line editing is disabled because henplus then reads from stdin as fallback. This happens if the LD_LIBRARY_PATH does not point to the JNI library; edit the /usr/bin/henplus shellscript so that the LD_LIBRARY_PATH contains the directory where libJavaReadline.so resides.

For RedHat and Debian you can just install the libreadline-java package.

First steps

The important commands you need to know to get it running are help and connect. The help command gives an overview, what commands are supported:

Hen*Plus> help
 help | ?                                 : provides help for commands
 about | version | license                : about HenPlus
 exit | quit                              : exits HenPlus
 echo | prompt                            : echo argument
 list-plugins | plug-in | plug-out        : 
handle Plugins
 list-drivers | register | unregister     : 
handle JDBC drivers
 list-aliases | alias | unalias           : 
handle Aliases
 list-key-bindings | bind-key-cmd-string  : 
handle function key bindings
 load | start | @ | @@                    : load file and execute commands
 connect | disconnect | rename-session | switch | sessions: 
manage sessions
 status                                   : show status of this connection
 tables | views | rehash                  : list available user objects
 describe <tablename>                     : describe a database object
 tree-view <tablename>                    : 
tree representation of connected tables
 dump-out | dump-in | verify-dump | dump-conditional: 
handle table dumps
 system | !                               : execute system commands
 set-var | unset-var                      : 
set/unset variables
 set-property | reset-property            : 
set global HenPlus properties
 set-session-property | reset-session-property: 
set SQL-connection specific properties

config read from [/home/hzeller/.henplus]

You exit the HenPlus shell by typing the exit or quit command or by just typing EOF-Character (CTRL-D).

For almost all commands, there is more detailed help available. You can just explore the commands by typing help [commandname] and learn what the built-in commands are all about; all help entries have a synopsis and a more detailed description like in the following example:

Hen*Plus> help system

SYNOPSIS
        system <system-shell-commandline>


DESCRIPTION
        Execute a system command in the shell. You can only invoke
        commands,  that do  not expect  anything  from  stdin: the
        interactive  input  from HenPlus  is disconnected from the
        subprocess' stdin. But this is useful to call some small
        commands in the middle of the session. There are two syntaxes
        supported: system <command> or even shorter with the
        exclamation mark: !<command>.
        Example:
        !ls

But for now, we are interested how to get connected, so start with the connect command

Hen*Plus> help connect

Getting connected

Getting connected to a database is simple: you need the JDBC driver from your database vendor, put it in the classpath, register the driver -- that's it. HenPlus provides the commands register, unregister and list-drivers to manage the drivers (you know it already: the