![[new]](new.gif){kind=small}
The following outline the major features of the latest release of sqsh:
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 the GNU Readline Library), or 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).
The ability to do direct remote procedure calls with sqsh went away for a while when everything was re-written using CT-Library, but is is back now.
So, once again, it now 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 ...
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
With sqsh 1.5, Sqsh attempts to be relatively smart, and ignores semicolons embedded within single or double quotes, as well as both C-style and SQL-style comments.
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.
With the 2.1 release of sqsh (well, actually the 2.0 release, but that one wasn't public), sqsh gains a basic flow-of-control syntax, similar to bourne shell, sufficient to write simple scripts.
Sqsh's new \if statement works, more or less, like that of bourne shell's \if statement with the exception that there is no 'then' and you must have a closing \fi:
\if [ $x -lt 10 ]
\echo Scott is smart.
\elif [ $x -lt 20 ]
\echo Scott is sexy.
\else
\echo Scott is modest.
\fi
It should be noted that, just like bourne shell, the actual syntax of an \if statement is:
\if expression
block
\fi
where expression is an external operating system program and command line, the exit status of which is used to control whether or not block is executed.
The special syntax:
\if [ expression ]
block
\fi
Is simply "syntactic sugar" for:
\if test expression
block
\fi
where test is the standard UNIX program test(1). Sqsh does not have its own built-in version of test. This is important to remember when using sqsh on NT, as NT doesn't natively have a version of the test program (however it does come with Cygwin, so you'll probably be OK).
The while provides a basic conditional looping
construct.
\while [ $x -lt 10 ]
\echo Scott is smart.
\set x=`expr $x + 1`
\done
The for loop allows for the iteration over a list
of values:
\for i in 1 2 3 4 5
\echo $i
\done
The \do loop doesn't bear any resemblance to most other
programming languages, however it is a very powerful construct.
The do loop allows a block of code or other SQL
commands to be executed for each row returned by a SQL statement,
kind of like a SQL CURSOR. During the execution of this loop,
the columns of the iterating query can be referred to by the
special #N variable syntax (where N is the column
number you wish to refer to. For example:
SELECT name FROM sysdatabases
\do
USE #1
go
CHECKPOINT
go
\done
Under the hood, the \do command utilizes a second
connection to the database and executes the block of code
contained between the \do and the \done
once for each row returned by the outer SQL statement. The
\do command even allows for flags to execute the
block against a different database or as a different user.
What good would flow-of-control constructs do if you couldn't
bundle them up into re-usable packages. Thus introduces the
new sqsh \func command, to declare a function.
\func stats
SET STATISTICS IO ${1}
SET STATISTICS TIME ${1}
go
\done
Declares a function called stats that, when executed with:
\call stats on
Will execute "SET STATISTICS IO on" and "SET STATISTICS TIME on". Note
that you can pass the -x flag to \func the
function is "exported"--meaning that it you don't need to use the
word \call to execute the function.
Ever get tired of wading through isql's messy output when dealing with very wide result sets? Sqsh currently supports seven separate display styles, horizontal (standard isql style), vertical, html and bcp, pretty, meta, and none 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),
With the addition of the html display style, sqsh may now be used to generate HTML <TABLE> constructs for use within a CGI application (or just to save you time).
For example:
1> SELECT type, COUNT(*) 2> FROM sysobjects 3> GROUP BY type 4> COMPUTE SUM(COUNT(*))
Comes out of sqsh looking like:
<table border>
<tr>
<th>type</th>
<th><br></th>
</tr>
<tr>
<td align=left>D </td>
<td align=right>9</td>
</tr>
<tr>
<td align=left>P </td>
<td align=right>518</td>
</tr>
<tr>
<td align=left>RI</td>
<td align=right>200</td>
</tr>
<tr>
<td align=left>S </td>
<td align=right>21</td>
</tr>
<tr>
<td align=left>TR</td>
<td align=right>19</td>
</tr>
<tr>
<td align=left>U </td>
<td align=right>248</td>
</tr>
<tr>
<td align=left>V </td>
<td align=right>2</td>
</tr>
<tr>
<th><br></th> <th>sum</th></tr>
<tr>
<td align=left><br> <td align=right>1017</tr>
</table>
<p>
(8 rows affected)
</p>
Which looks like this when viewed with your handy-dandy web browser:
| type | |
|---|---|
| D | 9 |
| P | 518 |
| RI | 200 |
| S | 21 |
| TR | 19 |
| U | 248 |
| V | 2 |
| sum | |
| 1017 |
(8 rows affected)
This probably isn't a very good example, but you get the idea.
With the pretty display style, sqsh now supports an ANSI-like display output using ASCII-graphic tables. Using the same query as above, we the following output:
+======+=============+ | type | | +======+=============+ | P | 12 | +------+-------------+ | S | 46 | +------+-------------+ | U | 7 | +------+-------------+ +======+=============+ | | sum | +======+=============+ | | 65 | +------+-------------+
For those of you that are just learning Open Client programming, sqsh also supports the meta display style. This displays only the meta-data returned from Open Client for each result set. Again, using the above SELECT statement you will get the following:
CS_ROW_RESULT
CS_BROWSE_INFO = CS_FALSE
CS_CMD_NUMBER = 1
CS_NUMDATA = 2
CS_NUMORDERCOLS = 0
COLUMN #1
name = type
namelen = 4
datatype = CS_CHAR_TYPE
format = CS_FMT_UNUSED
maxlength = 2
scale = 0
precision = 0
status = CS_UPDATABLE
count = 0
usertype = 0
locale = 0x177378
...
When X11 support is compiled into sqsh, the output of any given display style listed above may be redirected into a separate window by supplying the -x flag to the 'go' command.
1> sp_who 2> go -x
At compile time the X display may either use the Athena Widget objects or the slightly prettier Motif widget set.
It is also possible to override the default display and the default window size:
1> sp_who 2> go -x 80x25 -w 132 -d smell.stink.com:0.0
This will cause the result set to wrap at 132 columns wide and be displayed in a window that holds 80x25 characters, displayed on host smell.stink.com.
If you don't know about Characteristic Encoding Functions in SQL you should. Many of us use these handy expressions in our stored procedures daily, but testing out the logic can be a real pain interactively...the expressions aren't pretty.
As luck would have it, sqsh can solve this problem (and more!) by allowing you to filter each batch through a macro pre-processor prior to being shipped off to the database for execution. Thus common, repetitive tasks can be encapsulated in macros:
Sqsh allows you to define the filtering macro pre-processor you wish to use through the $filter_prog variable, like so:
1> \set filter_prog="/usr/bin/m4 $HOME/.sqsh_m4 -"
And by supplying a macro file in $HOME/.sqsh_m4 like so:
changequote([,])
define([STATS], [
set statistics io [$1]
set statistics time [$1]
])
You may now perform:
1> STATS(on) 2> go -t
To perform the same thing as:
1> set statistics io on 2> set statistics time on 3> go
A whole slew of sample m4 macros are supplied with the sqsh distribution.
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.