You are currently reading a special Sybase-FAQified version of my home page. I will attempt to keep it as up-to-date as possible, however there is a chance that it may lag somewhat behind my personal page (http://www.voicenet.com/~gray/sqsh.html). Also, this version has been stripped of changelog and status information in order to shorten it up a bit for the plain-text version of the FAQ.
Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), it is intended as a replacement for the venerable 'isql' program supplied by Sybase. It came about due to years of frustration of trying to do real work with a program that was never meant to perform real work.
Sqsh is much more than a nice prompt (a la 'dsql', from David B. Joyner), it is intended to provide much of the functionality provided by a good shell, such as variables, redirection, pipes, back-grounding, job control, history, command completion, and dynamic configuration. Also, as a by-product of the design, it is remarkably easy to extend and add functionality.
Sqsh was designed with portability in mind and has been successfully compiled on most major UNIX platforms supported by Sybase, such as HP-UX, AIX, IRIX, SunOS, Solaris, Dynix, OSF/1, DEC Unix, SCO, NeXT, and CP/M (just kidding). It has also been compiled on most free versions of UNIX, Linux, NetBSD, and FreeBSD, using the -DNO_DB flag (which turns off database support). It should build relatively easily on most POSIX and X/OPEN compliant systems.
[The SQSH mailing list has moved, so I have taken the liberty of editing this. Send email to sqsh-users-subscribe@onelist.com to join the new home of the mailing list. Ed.]
Sqsh may be found on the following sites:
Keep in mind that sometimes the different sites become out of sync, so at times the latest version may be be available at one of them.
If you are wondering what the funny '.gz' extension is on the end of some of the files, I highly recommend that you grab a copy of ftp://prep.ai.mit.edu/pub/gnu/gzip-1.2.4.tar or you can get a regular UNIX compressed version http://www.voicenet.com/~gray/sqsh-1.7.tar.Z.
I also try to keep around the previous release http://www.voicenet.com/~gray/sqsh-1.6.tar.gz, just in case I royally screw up the current release (which could happen).
If you have trouble reaching any of the sites above, you can send me e-mail at gray@voicenet.com, I am typically pretty good about responding.
Sqsh provides all commands provided by isql (such as go, reset, etc.)-- which wasn't hard, there aren't many of them--along with a large base of extended commands. Typically all commands in sqsh are prefixed with a '\' to avoid collision with the TSQL syntax. For example:
1> \help Available commands: \abort \alias \buf-append \buf-copy \buf-edit \buf-get \buf-load \buf-save \buf-show \connect \done \echo \exit \go \help \history \jobs \kill \loop \quit \read \reconnect \redraw \reset \set \shell \show \sleep \unalias \wait \warranty emacs vi Use '\help command' for more details
However, for those of you that just can't stand the '\', all commands may be aliased to any other name that you wish via the '\alias' command (see Aliasing, below).
Variables are provided in sqsh, much in the same way they are used within a standard shell. They may be used for storing and retrieving information, both within a sqsh command as well as within a SQL batch.
For example, lets say that you have a long table name that you don't like to type over and over again, you can use a variable in place of the table name:
1> \set t="a_really_long_table_name"
1> SELECT "Count" = COUNT(*) FROM $t
2> go
Count
-----------
1123
(1 row affected)
Variables may also be used anywhere within a sqsh command, such as:
1> \set g="go"
1> SELECT "Count" = COUNT(*) FROM $t
2> $g
Count
-----------
1123
(1 row affected)
And, since virtually every aspect of sqsh is configurable through variables, the \set
command may also be used to adjust the behavior of sqsh without having to exit and re-run
with a different command line argument (like isql):
1> \set colsep="|" 1> SELECT id, COUNT(*) FROM syscolumns GROUP BY id 2> go |id | | |-----------|-----------| | 1| 19| | 2| 23| ...
This is the equivalent of exiting isql, and re-running it with the -c flag (which is also supported by sqsh).
How many times have you watched a result set disappear from your screen because you didn't hit ^S fast enough? Well, no more. Now, any command available in sqsh may be redirected to/from a file or pipelined to another process. For example, it is now legal to type:
1> SELECT * FROM sysobjects 2> go | grep test | more
You may also redirect output to files and (if you are careful) can redirect input from files:
1> select * from sysobjects 2> go 2>/dev/null >/tmp/objects.txt
As of release 1.2, sqsh supports full csh-style command aliasing. Aliasing provides a mechanism for supplying an alternate name for any given internal sqsh command, as well as a way of supplying additional argument to any given command. For example:
1> \alias mo="\go !* | more" 1> SELECT * FROM syspickles 2> mo -h
Is exactly the same as if you had typed:
1> SELECT * FROM syspickles 2> go -h | more
The !* acts as a placeholder that indicates to sqsh that the parameters supplied to the alias should be inserted at this location. If the !* is not supplied, the parameters to the alias are appended on the end of the alias body...
With the 1.0 release, sqsh is slowly beginning to look more-and-more like a real shell with the addition of command substitution. This feature allows a UNIX command to substituted anywhere within a sqsh command or within a SQL batch simply by placing the command within backquotes (or ` -- this may not come out to be a backquote depending on which font your web browser is using). For example:
1> SELECT COUNT(*) FROM `echo syscolumns` 2> go | `echo more`
Currently, sqsh allows a multi-line command within a SQL batch, however this is not support for command line functions as of yet. For example you can do:
1> SELECT COUNT(*) FROM `echo 2> syscolumns` 3> go
Whereas you cannot do:
1> SELECT COUNT(*) FROM syscolumns 2> go | `echo more`
Hopefully, in the near future I'll make sqsh smart enough to support line-continuations with sqsh commands. Believe it or not, it isn't that easy to do.
Suppose you want to run a long complex query and continue to work while waiting for the results. With isql, the most effective way to do this was to run two copies of isql. With sqsh you can now do:
1> SELECT ... /* big nasty select */ 2> go & Job #1 started 1>
After typing 'go &', sqsh launches a child process, which reconnects to the database and performs the desired query. This is similar to job control within a standard shell except that, by default, in sqsh the background job's output will be deferred until the job completes. So when the big nasty query, above, completes you will see a message like:
1> sp_helptext ...Job #1 completed (output pending) 2>
and to show the output of the job you can do:
1> \show 1 | more
Once again, the behavior of output deferral may be turned on and off via the $defer_bg variable.
Sqsh also provides the commonly used job control commands available in such shells as csh and bash, such as \jobs (to display running jobs) and \kill (to terminate jobs).
Sqsh provides two methods for history control, line-by-line history using either vi or emacs styles (via ftp://prep.ai.mit.edu/pub/gnu/readline-2.0.tar.gz), it also provides batch history, so that entire statements may be re-run or edited:
1> \history
...
(12) SELECT name, id
FROM syscolumns
WHERE name LIKE "%$name%"
(13) SELECT DISTINCT title, type
FROM titles
WHERE title IN
(SELECT title
FROM titles, titleauthor, authors
WHERE titles.title_id = titleauthor.title_id
AND authors.state = "CA")
...
Most commands support a csh-style reference to history entries via '!!', or '!n'.
1> \vi !!
One of the major complaints most people have with isql is its inability to react to or report any sort of error condition generated within a SQL batch. Sqsh provides a somewhat complex but very flexible for configuring what is considered an error, which errors are to be displayed, and how to report them back to the operating system.
Five internal variables are used to control sqsh's behavior to error conditions reported by SQL Server, $thresh_display, $thresh_fail, $thresh_failcount, $thresh_exit, and $exit_failcount all of which are configurable at run time as well as via command line flags. The following briefly outlines these variables and their relationship to each other:
Using the \bcp command, sqsh supports the ability to transfer the result set from any command batch to another server (or even the same server) via the Sybase bcp protocol. This feature is particulary nice because current the standard Sybase bcp program does not support being able to transfer directly between server, or the ability to specify which rows from the source server are to be copied.
1> SELECT customer_id, item, SUM(qty) 2> FROM orders 3> GROUP BY customer_id, item 4> \bcp -S SYB_DSS shipping.dbo.order_summary Starting... Batch successfully bulk-copied to SQL Server Batch successfully bulk-copied to SQL Server Batch successfully bulk-copied to SQL Server ...
The \bcp command can deal with multiple result sets, and thus multiple commands in a batch or multiple results coming back from a single stored procedure (as long as the data types in all result sets are identical).
With sqsh, it is possible to directly envoke a stored procedure without resorting to language calls (e.g. "EXEC proc_name ..."). This feature is of particular interest for controlling and Open Server that does not have language support built in. For example, to invoke the sp_who stored procedure, simply run:
1> \rpc sp_who gray ...
Sqsh also supports the ability to place the results of an OUTPUT parameter directly into a sqsh variable, for example, lets say we create a stored procedure that like so:
1> CREATE PROCEDURE test_output 2> @x int OUTPUT 3> AS 4> SELECT @x 5> SELECT @x=20 6> go
We may then invoke the test_output procedure like this:
1> \rpc test_output @x:my_x=10
-----------
10
(0 rows affected)
1> \echo $my_x
20
The \rpc command can be a little bit awkward and non-intuitive, so make sure you read the manual page closely before working with it.
As of release 0.5, sqsh now supports a form of in-line go, via a ; placed anywhere within the current line, such as:
1> sp_who ;
And, anything that can follow the "go" command may also follow the inline ;
1> sp_who ; | more
Sqsh even attempts to be relatively smart, and ignores semicolons found within single or double quotes of a single command, although it currently does deal with semicolons located in comments. Note, in order to turn this feature on, execute:
1> \set semicolon_hack=1
Although sqsh does not have a full flow-of-control language (yet), it is possible to build simple self-executable scripts using the using #! notation, and sqsh's support for positional parameters. For example, to create a UNIX sp_who program, you simply need to create an executable file containing:
#!/usr/local/bin/sqsh -i
sp_who ${1}
go
The ${1} parameter to sp_who will expand to whatever argument is given when the script is run. Currently sqsh does not support more advanced positional paramters, such as $* or $@, like most shells.
Ever get tired of wading through isql's messy output when dealing with very wide result sets? Sqsh currently supports three separate display styles, horizontal (standard isql style), vertical, and bcp, that are switchable at any time while running via the $style variable or by the -m flag to the \go command.
With the vertical display style, all data is displayed as column/value pairs virtically down the left side. The style also nicely deals with performing word-wrapping on very wide text and varchar column outputs.
1> SELECT * FROM my_table
2> go -m vert
int_col: 1
varchar_col: You will notice that both varchar and text columns gracefully
word-wrap and line up with the widest column name.
float_col: 1.23
text_col: This text column would look really hideous on isql's output
but fortunately sqsh make things look great with the vertical
display style!
int_col: 2
varchar_col: Not much text here.
float_col: 3.141592654
text_col:
(2 rows affected)
And, if you want to simply generate a result set that is easily BCP'able into another server, the bcp display style is for you. This style throws out all formatting and simply separates all columns by the value of the $colsep parameter (by default "|").
1> SELECT * FROM my_other_table 2> go -m bcp 1|Scott|11/03/96 12:59:56|0||| 1|Bob|11/19/96 12:59:56|7||32.5|
This mode pretty much only makes sense when redirecting the output to a file (see Redirection and Pipes, above),
The following touches on a more of the less prominent features of sqsh. It is by no means a comprehensive list, for more details please refer to the manual page.
The following table outlines platforms that sqsh has successfully been compiled on. In theory each of these platforms should have been compiled painlessly, but in practice the odder operating systems trend to require a few tweaks. However, I am always working to make sqsh as easily portable as possible (not always an easy task).
If you have any additional platforms that you would like to have added to this list, please send me e-mail, I always interested in hearing what people are doing with sqsh.
Hardware OS Compiler Comments ------------------- ------------------- --------- ---------------- Sun Sparc 1000 Solaris 2.4 gcc HP/9000 E35 HP-UX 10.x gcc, cc HP/9000 755 HP-UX 9.01 ? gcc -static SGI Indy IRIX 5.x, 6.x cc 3.19 See README.SGI NCR System 3000 SVR4 cc Sequent ? Dynix/ptx 2.1.0 ? ? NeXT ? 150Mhz Pentium SCO ? ? DEC Alpha OSF/1 ? ? IBM RS/6000 AIX 3.2 gcc -ltermcap, no -ltli * Sun IPX SunOS 4.1.2 gcc * Sun Sparc 4c SunOS 4.1.4 gcc * HP/300 NetBSD 1.1A gcc * 486DX/50 Linux 1.3.45 gcc * Indicates that it has been compiled with -DNO_DB turned on, therefore the actual database access has not been tested, however 99% of Sqsh has nothing to do with database activity.
And, for those of you that are interested in such things, sqsh is developed primarily on Linux 1.3.95 with the -DNO_DB flag on (I haven't managed to port DB-Lib to Linux yet), and tested on a Sun Sparc Server 1000 running Solaris 2.4.
99% of the software that I use is free, therefore I like to give back in kind. Sqsh is held under the GNU General Public License (GPL) and therefore may be freely distributed under the terms of this license.
Last Modified on Oct 16, 1996 at 21:24:52 EST by Scott C. Gray