Sqsh; a superior choice to isql

Sqsh is a tool written by Scott Gray, a Sybase professional services consultant based in Philadelphia (and coincidentally a former project team member at a previous client). He has written an open-server based tool that far exceeds the capabilities of the Sybase tool isql for performing interactive SQL tasks. Sqsh stands for SQ-shell and can be found at its home page (this will open in a new browser window).

The extensive man page can be found in sqsh.1, which comes with the sqsh distribution. To view this man page you'll need to execute this command:

% nroff -man sqsh.1 | more

The advantages of sqsh include:

Simplified login; by specifying a .sqshrc file in your home directory, a number of login parameters normally specified at isql command line are read from the rc file; thus this isql command line entry:
```
% isql -Usa -S[server] -P[password] -H[hostname]
```
is replaced by simply typing
```
% sqsh
```
and by having all the other options designated in your chmod'd 400 .rc file.
Enhanced security; by specifying the password in your .rc file, you can type sqsh and enter the SQL server but your password will not appear in the process table. This is contrasted to logging into the SQL server via isql -Usa -P[password] , by doing so causes the sa password to be clearly visible in the process table (i.e., by doing a ps -fe | grep isql one can see your password).
integration of Unix commands to SQL output; the biggest feature for me; you can execute commands like this:
```
1> select * from syslogins
2> go | more
```
the SQSH program pipes the output of the select statement through the unix command more! You can also redirect input and output to files, pipe output to unix other unix commands, and generally do most any type of file manipulation thats available in shell (including backgrounding of jobs and decent job control).
Command recall/history: An incredible resource; like in the shell "bash" you can hit the up and down arrows to get previous commands. Also, by typing \history at the sqsh prompt you get a list of previous commands that are recall-able via "!" comamnds like in csh (!15 returns the sql command entered at line 15, !select will recall the sql from the last select statement, etc...). By typing "go" by itself sqsh automatically executes the last command; by typing !! sqsh recalls the last command (just like in csh). This is a far cry from isql, which has none of this (save for the vi hack to get the last command in your vi session...that is unless you've hit enter and cleared the vi buffer)...
Aliasing of commands; very nice feature; some rudimentary examples are described below in the sample .sqshrc file (they only provide for the two or three common commands that are in isql but not in sqsh due to enhanced sqsh features).
A host of Other features: keyword completion (like setting filec in csh) based on a keyword hotlist, looping mechanisms, variables inline w/ sql statements, command substitution, rpcs from within sqsh, and bcp'ing from command line directly to a file! See the man pages for further details.

Here's an example .sqshrc file; edit this file in your home directory and chmod 400 it if you're keeping cleartext passwords in it.

\set banner=no
\set username="sa"
\set password="[password]"
\set hostname="Boss_sqsh"
\set history_shorthand=on
\set statistics=off
\set semicolon_hack=off
\set database=db_name
\set prompt="SERVER >"
\alias clear="\shell clear"
\alias r="\buf-load "
\alias :r="\buf-load "
\alias sh="\shell "                  
\set style="bcp"
\alias gob="\go -m bcp"
\alias goh="\go -m hor"

the options are as follows:
- \set banner=no: turns off the sqsh banner; cosmetic only
- \set username="sa": emulates isql -Usa
- \set password="[password]": emulates isql -P[password]
- \set hostname="Boss_sqsh": emulates isql -H[hostname]
- \set history_shorthand=on: turns on ability to have !-recall commands
- \set statistics=off: if on, you get "set statisticsio on" stats after every command
- \set semicolon_hack=off; if on, you can have a ";" execute commands just like in oracle, in addition to having "go" execute commands. You can also have inline semicolons executing commands...but its a performance drag
- \set database=db_name; automatically issues an "use db_name" call as you log in, overriding whatever default database you may have configured for your login name - \set prompt="SERVER >": resets the isql prompt to be as specified, not the line number of the isql prompt. useful when you're logging into multiple server environments, but it is not dynamically configurable, and you lose the line number (which some people have grown to depend on). - \alias clear="\shell clear": alias to clear screen within sqsh session (provides same functionality as clear does in isql)
- \alias :r="\buf-load ": provides isql functionality to read a file into sqsh session
- \alias r="\buf-load " : same as above
- \alias sh="\shell ": drops you into a shell out of sqsh
- \alias gob="\go -m bcp ": this prints output of a select statement in bcp format. VERY useful for doing quick bcp commands, and using re-direction to a file. - \alias goh="\go -m hor ": this is the default way of returning data; alias included incase you have set default style of returning to be bcp. (set w/ command below) - \set style="bcp": defaults select statement data returned to be in bcp format.