xref: /dports/databases/sqsh/sqsh-2.5/doc/sqsh.pod

=head1 NAME

sqsh - Interactive database shell (version 2.5)

=head1 SYNOPSIS

       sqsh [[options]] [[args......]]
            [ -a count ]
            [ -A packet_size ]
            [ -b ]
            [ -B ]
            [ -c [cmdend] ]
            [ -C sql ]
            [ -d severity ]
            [ -D database ]
            [ -e ]
            [ -E editor ]
            [ -f severity ]
            [ -G tds_version ]
            [ -h ]
            [ -H hostname ]
            [ -i filename ]
            [ -I interfaces ]
            [ -J charset ]
            [ -k keywords ]
            [ -K keytab]
            [ -l debug_flags ]
            [ -L var=value ]
            [ -m style ]
            [ -n on|off ]
            [ -N appname ]
            [ -o filename ]
            [ -p ]
            [ -P [password] ]
            [ -Q query_timeout ]
            [ -r [sqshrc] ]
            [ -R server principal ]
            [ -s colsep ]
            [ -S server ]
            [ -t [filter] ]
            [ -T login_timeout ]
            [ -U username ]
            [ -v ]
            [ -V [bcdimoqru]]
            [ -w width ]
            [ -X ]
            [ -y directory ]
            [ -z language ]
            [ -Z [secmech|default|none] ]

=head1 DESCRIPTION

Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), and 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, it is intended to provide much of the
functionality provided by a good shell, such as variables, aliasing,
redirection, pipes, back-grounding, job control, history, command substitution,
and dynamic configuration. Also, as a by-product of the design, it is remarkably
easy to extend and add functionality.

=head1 OPTIONS

The following options may be used to adjust some of the behavior of sqsh,
however a large portion of the configuration options are available only through
environment variables which may be set at runtime or via a .sqshrc file.

Options may also be supplied in the SQSH environment variable. This variable is
parsed prior to parsing the command line, so in most cases the command line will
override the contents of the variable. Be aware that for options which are
allowed to be supplied multiple times, such as B<-c>, supplying them both in a
variable and on the command line will be the same as supplying them multiple
times on the command line.

=over 4

=item -a count

Sets the maximum I<count> of failures (as determined by the B<$thresh_fail>
variable) that may occur before sqsh will abort. Setting this to 0 indicates
that sqsh should not exit on errors. This value defaults to 0 and may also be
set using the B<$thresh_exit> variable. See section B<EXIT STATUS> for details.

=item -A packetsize

Specifies the size of the network TDS packets used to communicate with the SQL
server. This value must be between 512 and 8192, and be a multiple of 512. Check
your SQL Server configuration to determine supported packet sizes. This value
may also be specified at run-time using the B<$packet_size> variable.

=item -b

Suppress the banner message upon startup. This is unnecessary in cases where
stdout has been redirected to a file. This option may also be set via the
B<$banner> variable.

=item -B

Turns off all buffering of stdin, stdout, and stderr. This feature allows sqsh
to be run from an interactive control script such as chat and expect.

=item -c [cmdend]

Internally sqsh provides the command B<\go> to send a batch of SQL to the
database and provides a single alias, B<go> for this command. Each time
I<cmdend> is supplied a new alias for B<\go> is established.

=item -C sql

Causes the I<sql> command to be executed by sqsh, similar to the same behavior
exhibited by the -i flag. This I<sql> statement may not contain double quotes
(this limitation may be lifted in a future release of sqsh).

=item -d severity

Sets the minimum SQL Server error severity that will be displayed to the user.
The default is 0, and valid ranges are from 0 to 22. This may also be set using
the B<$thresh_display> variable. See section B<EXIT STATUS>.

=item -D database

Causes sqsh to attempt to start with your database context set to I<database>
rather than your default database (usually master). This may also be set using
the B<$database> variable.

=item -e

Includes each command issued to sqsh to be included in the output. This option
may also be set via the B<$echo> variable (which is unrelated to the B<\echo>
command).

=item -E editor

Set the default editor to I<editor>. This may also be set using the UNIX
environment variable B<$EDITOR> to the name of the editor desired.

=item -f severity

Sets the minimum I<severity> level considered a failure by sqsh. This is the
same as setting the B<$thresh_fail> variable. See section B<EXIT STATUS> for
details.

=item -G tds_version

Set the TDS version to use. Valid versions are 4.0, 4.2, 4.6, 4.9.5, 5.0 and
freetds additionally supports versions 7.0 and 8.0. The specified value is
assigned to the variable B<$tds_version>. Input validation is not performed by
sqsh. However, when an invalid TDS version is specified, the default version of
5.0 will be used. After a session is setup, the variable B<$tds_version> will be
set to the TDS version in effect. The variable will not be available if option
-G is not used. Meant for test and debugging purposes only.

TDS stands for Tabular Data Stream and is the communication protocol Sybase and
Microsoft uses for Client-Server communication.

=item -h

Turns off column headers and trailing "(# rows affected)" from batch output.

=item -H hostname

Sets the client hostname as reported in sysprocesses. This may also be set via
the B<$hostname> variable.

=item -i filename

Read all input from I<filename> rather than from stdin.

=item -I interfaces

When a connection is established to the database, the I<interfaces> file is used
to turn the value of B<$DSQUERY> into the hostname and port to which the
connection will be made, by default this is located in B<$SYBASE>/interfaces.
This flag allows this default to be overridden.

=item -J charset

Specifies the character set to be used on the client side to communicate with
SQL Server. This may also be set using the B<$charset> environment variable.

=item -k keywords

Specifies a file containing a list of keywords to be used for keyword tab
completion, if readline support has been compiled into sqsh. This file may also
be set via the B<$keyword_file> variable, which defaults to B<$HOME>/.sqsh_words.

=item -K keytab

Kerberos support. Specify the keytab file name for DCE.

See the Kerberos Support section below for details.

=item -l debug_flags

If sqsh has been compiled with -DDEBUG, this option may be used to turn on and
off debugging options. See the B<$debug> variable, below.

=item -L var=value

Sets the value of B<$var> to I<value>. This may be used to set the value of any
sqsh variable even if an explicit command line variable is supplied for setting
the variable. The B<-L> flag may be used to set the value of non-configuration
variables as well.

=item -m style

Changes the current display style to I<style>. Currently supported styles are
B<horiz>, B<vert>, B<bcp>, B<csv>, B<html>, B<meta>, B<pretty> and B<none>. The
current display style may also be set using the B<$style> variable or via the
B<-m> flag to the B<\go> command.

=item -n on|off

Enables chained transaction mode on connect (if set to on). Chained transaction
mode is also known as "AutoCommit off" mode.

=item -N appname

Set the application name sqsh uses to identify itself to the SQL server. The
application name can be retrieved from the server using the column
B<program_name> of the master.dbo.sysprocesses table. Defaults to I<sqsh-2.1.7>.

=item -o filename

Redirects all output to I<filename> rather than stdout.

=item -p

Display performance statistics upon completion of every SQL batch. This option
may also be turned on via the B<$statistics> variable, or by supplying the B<-p>
flag to the B<\go> command.

=item -P [password | -]

The Sybase I<password> for I<username> required to connect to I<server>
(default, NULL). The I<password> may also be set via B<$password>. Supplying a
password of '-' causes the password to be read from the first line of stdin.

sqsh takes pains to hide the password from any user trying to view the command
line used to launch sqsh, so using B<-P> should not constitute a security hole.
Alternatively, you can store your default password in .sqshrc file which is not
readable by anyone other than yourself. (chmod 600 .sqshrc)

=item -Q value

Query timeout value (equivalent to isql's -t option). If set, will timeout a
long running query. Maps to the B<$query_timeout> variable.

For example:

    sqsh -SASE1502 -Usa -P -Q30
    ASE1502.sa.master.1> cd tix
    ASE1502.sa.tix.1> select count(*) from E_TIX;
    Open Client Message
    Layer 1, Origin 2, Severity 2, Number 63
    ct_results(): user api layer: internal Client Library error:
                Read from the server has timed out.
    ASE1502: Query or command timeout detected, command/batch cancelled
    ASE1502: The client connection has detected this 1 time(s).

See also the B<$max_timeout> variable, which controls the number of times a
timeout may occur on the current connection before the session is aborted.

=item -r [sqshrc]

Specifies an alternate I<.sqshrc> file to be processed, rather than the default.
If no I<sqshrc> is supplied following B<-r>, then no initialization files are
processed. This flag B<must> be the first argument supplied on the command line,
all other instances will be ignored.

=item -R server_principal

Kerberos support: Specifies a I<server principal> to use for network (Kerberos)
authentication, if the server name in the interfaces file differs from the real
server name.

See the Kerberos Support section below for details.

=item -s colsep

Causes the string I<colsep> to be used to delimit SQL column output columns,
this defaults to " ".

=item -S server | host:port[:filter]

The name or the address of the Sybase I<server> to connect. The default of this
is the external environment variable B<$DSQUERY>. If neither B<-S> nor
B<$DSQUERY> is set then defaults to "SYBASE". This value may also be set via the
internal variable B<$DSQUERY>. The servername must exist in the interfaces or
sql.ini file. As an alternative it is also possible to specify the target
server as B<host:port[:filter]> where I<host> may also be an IP address. Note
that B<filter> may be defined in $SYBASE/$SYBASE_OCS/config/libtcl[64].cfg. For
example:

    [FILTERS]
    ssl=libsybfssl.so
    ssl64=libsybfssl64.so

=item -t [filter]

Enables filtering of command batches through an external program, I<filter>,
and prior to being sent to the SQL Server. If I<filter> is not supplied, then
B<$filter_prog> is used (default is 'm4 -'). This value may also be set via the
B<$filter> and B<$filter_prog> variables.

=item -T value

Specifies the login timeout (similar to isql's -l flag). If set specifies the
number of seconds I<sqsh> will wait before timing out a login request. Maps to
the B<$login_timeout> variable.

=item -U username

The Sybase I<username> to connect to the database as, this defaults to the
username of the user running I<sqsh>. The I<username> may also be set via the
B<$username> variable.

=item -v

Displays the version number, B<$version>, and exits.

=item -V [bcdimoqru]

Kerberos support: Specify the security options to use.

See the Kerberos Support section below for details.

=item -w width

The maximum output I<width> of a displayed result set, this defaults to your
screen width in interactive mode.

=item -X

Initiates the login connection to the server with client-side password
encryption (if supported). If either SQL Server does not recognize this option,
or if the version of CT-Lib used to compile I<sqsh> does not support this
option, then it will be ignored. This option may also be set using the
B<$encryption> environment variable.

=item -y directory

Specifies a SYBASE I<directory> to use other than the value of B<$SYBASE> in
order to find the interfaces file.

=item -z language

Specifies an alternate I<language> to display sqsh prompts and messages. Without
the B<-z> flag, the server's default language will be used. This may also be
set using the B<$language> variable.

=item -Z [secmech|default|none]

Kerberos support: Specify the security mechanism to use.

See the Kerberos Support section below for details.

=item args...

If sqsh is run with the B<-i> flag specifying an input file to be processed
(rather than initiating an interactive session), arguments may be supplied on
the command line to be passed to the input file. These arguments may be accessed
using the variables ${0}, ${1}, ... (see the B<Variables> section, below, for
more information).

=back

=head2 Initialization

Upon startup, sqsh initializes all internal environment variables, commands, and
aliases to their default values, it then looks in the system-wide configuration
file (usually /usr/local/etc/sqshrc), followed by a local configuration file
B<$HOME>/.sqshrc (this may be overridden via the SQSHRC external environment
variable). If this file is found it is executed just like a script would be
using the B<-i> flag.

The I<.sqshrc> file may contain anything that could normally be typed at the
prompt, however it should be noted that at the time this file is read sqsh has
yet to establish a connection to the database, however most commands that
perform database activity, such as B<\go> will attempt to establish a database
connection when executed (it may also prompt you for a password if necessary).
Also, if database activity is required within this startup file, the B<\connect>
command (see B<COMMANDS>, below) may be executed.

After the I<.sqshrc> file has been executed, I<sqsh> then parses any command
line options (thus any variables set in your I<.sqshrc> file may be overridden
by command line options). Following that, if I<sqsh> is run in interactive mode
(i.e. without B<-i> and if stdin is attached to a tty), it then looks for the
file provided by the B<$history> variable and loads the contents of that file
into the history buffers. (see B<BUFFERS>, below).

Immediately prior to establishing a connection to the database (either during
startup, or by an explicit B<\connect> or B<\reconnect> command), the file
B<$HOME>/.sqsh_session is executed. The name of this file may be overridden
using the B<$session> variable.

=head2 Command line

When a line is first read by I<sqsh>, the first word is separated from the line.
This word is then expanded of all variables (see B<Variable Substitution>,
below), followed by command expansion (see B<Command Substitution>, below). The
first word of the resulting string is then analyzed to see if it is either a
valid I<sqsh> command or alias.

The I<sqsh> command line follows many of the same rules as Bourne shell,
allowing file redirection, pipelining, command substitution, and back-grounding
via the same syntax.

=head2 Comments

Any line beginning with a B<#> followed by a non-alphanumeric character (any
character other than 0-9, a-z, A-Z, and _) causes the entire line to be ignored.
Because of the possible collision with T-SQL session specific temp-table names,
the line will not be ignored if the first character following the B<#>, is
alphanumeric.

=head2 Quoting

Quoting is used to prevent the interpretation of special keywords or characters
to sqsh, such as white-space, variable expansion, or command substitution. There
are three types of quoting, I<escape>, I<single-quotes>, and I<double-quotes>.

Enclosing characters in single quotes preserves the literal interpretation of
each character contained within the quotes. A single quote may not appear within
single quotes, even when preceded by an escape. For example:

    1> \echo I can not expand '$username'

outputs

    I can not expand $username

The characters \\ are used to escape the meaning (and thus prevent the
interpretation) of the character immediately following them. The \ character
itself may be escaped. For example:

    1> \echo I can\\'t expand '$username'

outputs

    I can't expand $username

The escape character may also be used to escape a new-line in order to perform a
line continuation, in this case the new-line is discarded and the continued line
is automatically appended to the previous line, for example:

    1> \echo Hello \\
    --> World!
    Hello World!

Enclosing characters in double quotes preserves the literal meaning of all
characters within them with the exception of B<$>, B<'>, and B<\\>. A double
quote may be contained within double quotes by escaping it.

    1> \echo "\\"I can't deny it, I like $username\\", she said"

prints out

    "I can't deny it, I like gray", she said


=head2 Expansion

After a line of input has been read, I<sqsh> attempts to expand the line of any
aliases (see B<Aliasing>, below), following that it attempts to determine if the
line begins with a command keyword. Once a line has been determined to contain a
command name it has three types of expansion performed to it: I<variable
substitution>, followed by I<command substitution> respectively. Finally, if a
tilde was provided on the command line, then I<tilde expansion> will be performed
and the B<~> will be substituted with the corresponding B<HOME> directory name.

    1> \echo ~sybase/err.log

may result in I</home/sybase/err.log> for example.

    1> exec sp_helpdb
    2> go > ~/db.log

may result in a file I</export/home/dba/db.log> for example, depending on the
Unix login and the exact OS you are using.
Following this expansion the command line is separated into words and the
command is executed.

=head2 Variable Substitution

The character $ is used to indicate variable substitution or expansion within a
word. These variables may be assigned values by the B<\set> command like so:

    1> \set name=value

I<name> may be a character or underscore followed by any combination of
characters, digits, or underscore, and may not contain any special characters,
such as (') and ("). The restriction on the first character being a digit is
introduced because SQL allows the representation of money data types as $nn.nn
where n is a digit.

I<value> may contain anything, however if it is to include white-space, then it
must be quoted (see B<Words & Quoting>, above). Note that in order to prevent
the expansion of a variable use either single quotes, or two \'s, like thus:

    1> \echo \\$name
    $name

Variables may be referenced in one of two ways:

$variable In this manner all characters, digits, and underscores are treated as
the I<name> of the variable until another type of character is reached (either a
special character, or a white-space). ${variable} The braces are required only
when I<variable> is followed by a letter, digit, or underscore that is not to be
interpreted as part of its name. Note that the same effect may be achieved using
double quotes.

It should be noted that because the variables are expanded prior to breaking the
command line into words, if the contents of the variable contain white spaces,
they are treated as significant by the parser. In the following example:

    1> \set x="1 2 3"
    1> \echo $x

the B<\echo> command receives three arguments, "1", "2", and "3", although it
looks as if only one argument was passed to it. This behavior is consistent with
most shells (such as csh, bourne shell, etc.).

=head2 Command Substitution

I<Sqsh> supports a second form of expansion called I<command substitution>. This
form of expansion substitutes a command on the command line with the output of
the external UNIX command. This expansion may be achieved by placing the command
line to be executed in back-quotes (`). For example:

    1> \set password=`/sybase/bin/getpwd $DSQUERY`
    1> \echo $password
    ilikepickles

This example, the external program B</sybase/bin/getpwd> is executed with the
current contents of the B<$DSQUERY> environment variable, the entire expression is
then replaced with the output of B<getpwd (ilikepickles)> prior to executing the
B<\set> command. By default, the output of the substituted command is first
broken into words according to the contents of the B<$ifs> variable prior to
assembling together back into the command line. So, by overriding the contents
of B<$ifs> you may affect the behavior of the substitution process.

For example:

    1> \set ifs=":"
    1> \echo `echo hello:how:are:you`
    hello how are you

This mechanism is frequently useful for parsing input files, such as
B</etc/passwd> into fields.

=head2 Input/Output Redirection

As with standard Bourne shell (and most other shells, for that matter), a
command's input and output may be redirected using a special notation
interpreted by the shell. The following may appear anywhere on the command line,
but only redirection that is specified I<prior> to a pipe (|) actually has any
effect on the behavior of internal sqsh commands (refer to B<Pipes>, below).

=over 4

=item <word

Use the file I<word> as the standard input for the command. Typically very few
I<sqsh> commands actually read anything from stdin, so this will usually have no
effect (see the B<\loop> command).

=item [n]>word

Associate the output of file descriptor I<n> (stdout, by default) with file
I<word>. If this file does not exist it is created; otherwise it is truncated to
zero length.

=item [n]>>word

Append the output of file descriptor I<n> (stdout, by default) to file I<word>,
creating it if it does not exist.

=item [m]>&n

Redirect the output of file descriptor I<m> (stdout by default), to same output
as file descriptor I<n>. The order in which redirections are specified on the
command line is significant, as the redirections are evaluated left-to-right.
For example:

    1> select * from select /* syntax error */
    2> \go >/tmp/output 2>&1

This statement first redirects the standard output of the B<\go> command to the
file I</tmp/output>, then redirects the stderr to the same file. So, when the
commands fails, the error output will be found in the file I</tmp/output>.

However, by changing the order of redirection, you can completely change the
meaning:

    1> select * from select
    2> \go 2>&1 >/tmp/output
    Msg 156, Level 15, State 1
    Server 'SQSH_TEST', Line 1
    Incorrect syntax near the keyword 'select'.

In this case, error output will be sent to stdout, while what would have gone to
stdout is redirected to I</tmp/output> (in this case I</tmp/output> will be
empty).

=back

Please read the section on B<Background Jobs>, below, for detailed info on the
interaction between file redirection and background jobs.

=head2 Pipes

A I<pipeline> is a sequence of one or more commands separated by a '|', each
command using the stdout of the preceding program for its own stdin. However the
first command in the I<pipeline> must be a sqsh command, and all other commands
must be external (or UNIX) programs. Any sqsh command may be run through a
pipeline, although for many of them (such as the B<\set> command) it doesn't
really make any sense to do this. The following is an example of a I<pipeline>:

    1> select * from syslogins
    2> \go | more

This command causes the result set generated by the B<\go> command to be sent to
the B<more(1)> program, which then sends it to your screen, pausing at each
screen full of data (this is the primary reason that I wrote sqsh).

There are several peculiarities in the way in which sqsh deals with I<pipelines>
as opposed to the way in which standard Bourne shell treats them.

Everything following the first occurrence of a pipe (|) character is broken into
white-space delimited words, including such special shell commands as '2>&1'
and other occurrences of pipes. If there are any variables contained in these
words they are expanded following the same quoting rules as described in
B<Words & Quoting>, above, with the one exception that all quotes are left in
place. These words are then reassembled into a single string and shipped off to
/bin/sh for processing.

In short, sqsh makes no attempt to interpret what follows the first pipe,
instead it is shipped off to a "real" shell to do the work. The rationale behind
this is that I was lazy and didn't feel like writing all of the same bizarre
variable handling, &&'ing, ||'ing, grouping, and variable expansion rules
that Bourne shell supports, and instead I let Bourne do the dirty work.

The advantage of this method is that you can do some very complex stuff after
the pipeline, such as:

    1> select * from syscolumns
    2> \go | (cd /tmp; compress -c > sysolumns.Z)

Not that I can think of any real reason to do this...but you can if you want to.

=head2 Background Jobs

Backgrounding provides a mechanism whereby you may run any sqsh command as a
background process and continue working while it runs. Sqsh offers two types of
backgrounding:

=over 4

=item Deferred

In this mode sqsh redirects all output of the background job to a temporary file
(located in the directory B<$tmp_dir>) while the job is running, so that the
output is not intermixed with what you are currently working on. When the job
completes you are notified of the process completion and the output may be
viewed using the B<\show> command.

=item Non-Deferred

This corresponds to the common idea of a background process under UNIX. In this
mode the output of the job is not implicitly redirected for you, and thus may
become intermingled with what you are currently working. The mode selection you
choose is selectable via the B<$defer_bg> variable (which defaults to '1', or
'On'). Typically the only reason to not use I<deferred> mode is to prevent
large result sets from filling up your file system.

=back

To specify that a job be run in the background, simply append a & to the end of
the command line, as:

    1> sp_long_arduous_proc 1, 30
    2> \go &
    Job #1 running [xxxx]
    1>

When sqsh encounters the & on the end of the command line it spawns a child
process (with a Unix process id of I<xxxx>) then the child process calls the
B<\go>. B<\go> command then establishes a new connection to the database (using
the current values of the B<$DSQUERY>, B<$username>, B<$password> variables)
and executes the shown query. While the job is executing the commands B<\jobs>,
B<\wait> and B<\kill> may be used to monitor or alter a currently running job
(see section B<COMMANDS>, below). When any job completes sqsh will display a
notification, such as:

    1> select count(*) from <return>
    Job #1 complete (output pending)
    2>

When a job completes, if it had no output, it is immediately considered
terminated and will not show up in the current list of running jobs. However if
the complete job has pending output, it will continue to be displayed as a
running job (with the B<\jobs> command) until a B<\show> is used to display the
output of the job.
When you exit your parent sqsh session and there are background jobs active then
a message is shown: B<You have running jobs or pending job output>.
You have to process all the jobs first before being able to exit sqsh.

There is a known bug with job backgrounding when used in conjunction with pipes,
please refer to the B<BUGS> section at the end of the manual.

=head2 Buffers

In normal B<isql> only two buffers are maintained; the buffer into which you are
currently typing, and a buffer that contains the last batch executed (this is
kept around for when you run 'vi', or 'edit').

Sqsh maintains several distinct sets of buffers:

B<Work Buffer>: This buffer corresponds directly to the B<isql> work buffer. It
is the buffer into which you enter the current batch prior to sending it to the
database.

B<History Buffer>: This is actually a chain of 0 or more buffers (configurable
by the B<$histsize> variable) of the last B<$histsize> batches that have been
run. This buffer is only maintained when sqsh is run in interactive mode; that
is, batches executed using the B<-i> flag, or executed via redirection from the
UNIX prompt will not be maintained in history (after all, they are already in a
file somewhere).

If the variable B<$histsave> is True (see section B<SPECIAL VARIABLES>) and sqsh
is in interactive mode, then the current history buffer is written to
B<$HOME>/.sqsh_history when you exit. This file is then read back into sqsh the
next time it is started in interactive mode.

B<Named Buffers>: At any time during a session the B<Work Buffer>, or any of the
B<History Buffers> may be copied into a named buffer using the B<\buf-copy>
command (see section B<COMMANDS>, below). These buffers are lost when you exit
(however you may use the B<\buf-save> command to save named buffers to a file).

=head3 Buffer Short-Hand

Many commands allow all of these buffers to be referenced in a short-hand
fashion, very similar to the way that B<csh(1)> references its commands history.
Any of these shorthands may be used for any I<buffer> parameter described in the
B<COMMANDS> section:

=over 4

=item !.

The current work buffer.

=item !!

The last command executed (note, this is not available in non-interactive mode
as it does not maintain a history).

=item !+

The next available history entry. This is a write-only buffer, so typically only
applies to such commands as B<\buf-copy>.

=item !n

Refers to history #n. Each time an entry is written to history it is assigned an
increasing number from the last entry, with this short-hand you may reference
any given history.

=item !buf_name

Just for consistency this is supplied as a reference to named buffer
I<buf_name>, however I<buf_name> without the leading '!' is also considered
correct.

=item buf_name

Refers to the named buffer I<buf_name>.

=back

=head3 Variables

Variables may also be contained within work buffers. Under these circumstances
the variables remain unexpanded until the buffer is sent to the database (via
the B<\go> command), during which time they are expanded and replaced within the
buffer. This behavior may be altered via the B<$expand> variable.
(see B<Special Variables>, below).

The following is an example of using variables within a buffer:

    1> \set table_name=syscolumns
    1> select count(*) from $table_name
    2> \go

This is the equivalent of performing the query:

    1> select count(*) from syscolumns
    2> \go

directly. Typically this feature is useful for reusing large complex B<where>
clauses, or long column names.

Quoting rules apply the same in SQL buffers as they do in command lines. That
is, any variables contained within double quotes (") are expanded and variables
contained within single quotes (') are left untouched. Thus:

    1> select "$username", '$username'
    2> \go

yields the results

    ---- ---------
    gray $username


=head3 Command Substitution

As with the command line, the output of UNIX commands may also be substituted
within a SQL buffer upon execution (once again, only if the B<$expand> variable
is set to 1, or true). In this circumstance the command contained within back
quotes (`) is replaced with its output prior to forwarding the buffer to SQL
server. For example:

    1> select count(*) from `echo syscolumns`
    2> \go

Causes the strings 'echo syscolumns' to be replaced by the word
I<syscolumns> prior to executing the command. It should be noted that the
contents of the substituted command are only executed at the time of the B<\go>
command, not when the line of SQL is input.

=head2 Flow-of-Control

New with version 2.0 of sqsh, is the ability to perform basic flow-of-control
and functions using the B<\if>, B<\while>, B<\do>, and B<\func> commands.

=head3 Blocks & SQL Buffers

All sqsh flow-of-control commands are block-based. That is, if the test
expression of the command is met, then a block of sqsh-script will be executed.
For example, the definition of the B<\if> command is:

    \if expression
        block
    \fi

This I<block> may be any number of lines of sqsh commands, SQL, or
flow-of-control statements to be executed if the I<expression> evaluates to a
success condition (0).

Each I<block> has its own SQL buffer for the duration that the I<block> is
executed. That is, the following statements:

    1> /*
    2> ** IMPROPER USAGE OF IF BLOCK
    3> */
    4> select count(*) from
    5> \if [ $x -gt 10 ]
    6>     sysobjects
    7> \else
    8>     sysindexes
    9> \fi
    5> go

will yield:

    Msg 102, Level 15, State 1
    Server 'bps_pro', Line 1
    Incorrect syntax near 'from'

because the string 'sysobjects' or 'sysindexes' were inserted into their
own SQL buffers. These buffers are discarded as soon as the end of the block was
reached, and since a B<\go> command was not contained within the block, no
additional errors were generated.

Thus, the correct way to write the above expression would be:

    1> /*
    2> ** PROPER USAGE OF IF BLOCK
    3> */
    4>  \if [ $x -gt 10 ]
    5>     select count(*) from sysobjects
    6>     go
    7> \else
    8>     select count(*) from sysindexes
    9>     go
    10> \fi

or, even:

    1> /*
    2> ** PROPER USAGE OF IF BLOCK
    3> */
    4>  \if [ $x -gt 10 ]
    5>     \set table_name=sysobjects
    6> \else
    7>     \set table_name=sysindexes
    8> \fi
    4> select * from $table_name
    5> go

Also, note that the line number displayed in the sqsh prompt resets to the
current position in the outer SQL buffer after reaching the B<\fi> terminator.

=head3 Expressions

All flow-of-control statements in sqsh take an I<expression> to determine which
I<block> of code to execute. Just like UNIX's Bourne Shell, this I<expression>
is simply an operating system program that is executed by sqsh. If the command
returns a success status (calls exit(0)), then it is considered successful.

For example, with following statement:

    \while test $x -lt 10
        block
    \done

will execute the contents of I<block> while the current value of $x is less than
10. Note that 'test' is a standard UNIX program to perform basic string or
numeric comparisons (among other things). Also, unlike many shells, sqsh has no
built-in version of 'test'.

Sqsh does, however, support the standard short form of 'test':

    \while [ $x -lt 10 ]
        block
    \done

With this expression the open brace ('[') is replaced by the sqsh parser
with 'test', and the close brace (']') is discarded.

=head3 Unsupported Expressions

Currently sqsh does not support the standard shell predicate operators '&&'
and '||'. These can be performed like so:

    \if sh -c "cmd1 && cmd2"
        block
    \done


=head4 \if statement

The B<\if> command performs conditional execution of a sqsh I<block> based upon
the outcome of a supplied expression:

    \if expr1
        block1
    \elif expr2
        block2
    \else
        block3
    \fi

In this example, if expression I<expr1> evaluates to true, then the block
I<block1> is evaluated. Otherwise, if the expression I<expr2> evaluates to true,
then block I<block2> is evaluated. Finally, if all other tests fail I<block3> is
evaluated.

Note that, unlike Bourne Shell, every B<\if> command must be accompanies by a
trailing B<\fi> statement. Also the sqsh parser is not terribly intelligent: The
B<\else> and B<\fi> statements must be the only contents on the line in which
they appear, and they may not be aliased to another name.

=head4 \while statement

The B<\while> command executes a I<block> of sqsh code for the while a supplied
expression remains true.

    \while expr
        block
    \done

In this example, while the expression I<expr> evaluates to true, then the block
I<block> is evaluated.

The B<\break> statement may be used to break out of the inner-most B<\while> or
B<\for> loop (more on B<\for> below).

=head4 \for statement

The B<\for> command executes a I<block> of sqsh code for each I<word> supplied:

    \for var in word ...
        block
    \done

For each I<word> supplied, the value of the variable B<$var> is set to the word
and the I<block> of code is executed. Execution ends when there are no more
words in the list.

As with B<\while> the B<\break> statement may be used to break out of the
inner-most execution loop.

=head4 \do command

The B<\do> command is kind of a cross between a statement and a command.

It is a form of B<\go> (see below for details on the B<\go> command) in which a
I<block> of sqsh code may be executed for each row of data returned from the
query. When the I<block> is executed, special sqsh variables #[0-9]+ (a hash
followed by a number) may be used to reference the values in the returned query.
For example the following command:

    select dbid, name from master..sysdatabases
    \do
        \echo "Checkpointing database #2, dbid #1"
        use #2
        go
        checkpoint
        go
    \done

would cause a CHECKPOINT command to be issued in each database on the server.

=head4 Command line options

The B<\do> command establishes a new connection to be used by the I<block> of
code when executed. By default, this connection is established to the current
server (the current setting of B<$DSQUERY>), using the current username
(B<$username>) and the current password (B<$password>). This behavior may,
however, be overridden using command line options:

=over 4

=item -D database

Establishes the connection to the database as the supplied I<database>.

=item -U username

Establishes the connection to the server as the supplied I<username>.

=item -P password

Establishes the connection to the server using the supplied I<password> (which
is hopefully a valid password for the supplied I<username>).

=item -S server | host:port[:filter]

Establishes the connection to the supplied I<server>.

=item -n

Do not create a connection for use by the B<\do> loop. This flag is mutually
exclusive with the above flags. With this flag enabled, attempts to perform
database commands within the I<block> will generate a flurry of CT-Library
errors.

=back

=head4 Column variables

As mentioned above, the values of the columns in the current result set may be
determined using the special #[0-9]+ variables. Thus, the variable #1 would
contain the value of column number one of the current result set, and #122 could
contain the value of the 122'nd column (column numbers begin at 1).

In the case of nested B<\do> loops, values in previous nesting levels may be
referred to by simply appending an addition '#' for each previous nesting
level, like so:

    select id, name from sysobjects
    \do
        select name, indid from sysindexes where id = #1
        \do
            \echo "Table ##2 (objid ##1) has index #1"
        \done
    \done

obviously, this isn't the way you would do this query in real life, but you
get the idea.

When expanding columns with NULL values, the column variable will expand to an
empty string (''). Also, references to non-existent columns, such as #0,
will result in an empty string ('').

As with regular sqsh variables (those referenced with a '$'), column
variables will not be expanded when contained within single quotes.

=head4 Aborting

If the B<\break> or B<\return> commands are issued during the processing of a
B<\do> loop, the current query will be canceled, the connection used by the loop
will be closed (unless the B<-n> flag was supplied) and the B<\do> loop will
abort.

=head4 \func command

The B<\func> command is used to define a reusable block of sqsh code as a
function. Functions are defined like so:

    \func stats
        \if [ $# -ne 1 ]
            \echo "use: stats [on | off]"
            \return 1
        \fi
        set statistics io ${1}
        set statistics time ${1}
        go
    \done

In this example a new function is established called I<stats> that expects a
single argument, either "on" or "off". Using this argument, I<stats> will enable
or disable time-based and I/O-based statistics.

Once established, the function may be called like so:

    \call stats on

Causing all instances of ${1} to be replaced with the first command line
argument to I<stats>.

=head4 Command line options

Currently only one command line argument is available to the B<\func> command.

=over 4

=item -x

Causes the function to be exported as a I<sqsh> command. That is, the function
may be invoked directly without requiring the B<\call> command. This behavior is
optional because command names can potentially conflict with T-SQL keywords.
When using this flag it is recommended that you prepend a backslash (\) to your
function name.

=back

=head4 Function variables

As shown in the example above, several special variables are available for use
within the body of the function. These are:

=over 4

=item $#

Expands to the number of arguments supplied to the function or script when
invoked.

=item $*

Expands to the complete list of arguments supplied to the function or script
when invoked.

=item ${0}..${N}

Expands to positional arguments to the function. ${0} is the name of the
function or the script file being invoked, ${1} is the first argument,
${2} the second and so-on, up to argument I<N>. Note that, unlike most shells,
sqsh requires that function arguments be referred to using the special curly
brace syntax (${1}, rather than $1). The reason for this is that $1 is a valid
MONEY value and using the curly braces gets rid of this ambiguity.

=item $?

After the invocation of a function, this will contain its return value (see
below).

=back

=head4 Return value

A value may be returned from a function via the B<\return> command.

Like so:

    \return N

Where I<N> is a positive value. This return value is available to the caller of
the function via the B<$?> variable. As convention, a return value of 0 is used
to indicate a success.

If B<\return> is not explicitly called, the default return value is the current
value of the B<$?> variable (which is set to 0 upon entry of the function).
Thus, if any SQL statements are invoked within the function, the default return
value of B<$?> will be the last error code returned during the processing of the
SQL statement.

=head2 Kerberos Support

Starting with version 2.1.6, I<sqsh> provides the same command line options as
I<isql> to handle Kerberos network authentication.

In version 2.1.5 experimental Kerberos support was added using the -K and -R
options. -K was merely a switch to set Kerberos on. In sqsh 2.1.6 a more
advanced implementation of network authentication is introduced, although still
experimental.

By using the parameters -K, -R, -V, -Z you can make use of your defined network
security settings (libtcl.cfg). The named options are identical to the ones
defined for I<isql>.

=over 4

=item -K keytab_file

Keytab_file name for DCE.

=item -R server_principal

Server principal name when servername specified in interfaces differs from the
real server name.

=item -V [bcdimoqru]

Specify security options to use with the security mechanism. Each character
stands for a specific security service.

=item -Z [secmech|default|none]

Request a security mechanism defined for Kerberos, DCE or PAM in your
libtcl.cfg file. Use B<secmech> to specify the name of a SECURITY entry or
B<default> for the first available entry in libtcl.cfg. B<None> must be
specified to disable network authentication or reset possible existing
values in variables B<$secmech> or B<$secure_options>.

=back

For example, connecting to a server using Kerberos (which happens to be
the default, i.e. first entry in libtcl.cfg [SECURITY] tab in this example):

    ~$ sqsh -SSYB1502 -Uuser1 -RFC6A1502 -Z
    \connect: Network authenticated session expires at:
            16 Feb 2010 15:28:39 (11764 secs)
    SYB1502.user1.master.1> select @@servername,@@authmech,
                                 show_sec_services();
    ----------------  -----------  ----------------------------------
    FC6A1502          kerberos     unifiedlogin delegation mutualauth
                                 integrity confidentiality
                                 detectreplay detectseq

Note that the real name of the server (@@servername) differs from the server
name in the interfaces file, so we have to specify the principal name through
the B<-R> parameter.
When you do not specify the B<-V> parameter together with B<-Z>, all available
security options will be enabled. When B<-V> is specified without any security
service options, only option B<u> for Network Authentication will be implicitly
set and the default security mechanism will be used if B<-Z> is not specified.

    [user1@linux-fc6a ~]$ sqsh -SFC6A1502 -Uuser1 -V
    Open Client Message
    Layer 7, Origin 9, Severity 5, Number 1
    ct_connect(): security service layer: internal security control
                layer error:
                Security service provider internal error -1765328352
                occurred.
    [user1@linux-fc6a ~]$ kinit
    Password for user1@LOCALDOMAIN:
    [user1@linux-fc6a ~]$ sqsh -SFC6A1502 -Uuser1 -V
    \connect: Network authenticated session expires at:
            16 Feb 2010 15:28:39 (10964 secs)
    FC6A1502.user1.master.1>

When the connection succeeds, I<sqsh> will store the real name of the security
mechanism in the variable B<$secmech>. For example: "\echo $secmech" may show
I<csfkrb5>. The parameter B<-V> takes a list of characters from the possible
values of I<bcdimoqru>. The option B<u> enables Network Authentication, is the
default and will allways be set when using B<-V> or B<-Z>, specified or not.

    b - chanbinding     : Channel binding
    c - confidentiality : Data confidentiality service
    d - delegation      : Allow delegated credentials
    i - integrity       : Data integrity service
    m - mutualauth      : Mutual authentication for connection
                          establishment
    o - dataorigin      : Data origin stamping service
    q - detectseq       : Out-of-sequence detection
    r - detectreplay    : Data replay detection
    u - unifiedlogin    : Network Authentication

Please check master.dbo.syssecmechs for available services. Non-existing or not
supported services supplied with B<-V> are silently ignored. If you specify
B<-V> and/or B<-Z>, I<sqsh> assumes network authentication is tried and no
password is required.

If you have a network authenticated connection and want to \reconnect using
normal ASE authentication with username and password, you have to reset the
network authentication variables by specifying -Znone

For example:

    [user1@linux-fc6a ~]$ sqsh -SFC6A1502 -Uuser1 -V
    \connect: Network authenticated session expires at:
            16 Feb 2010 15:28:39 (10764 secs)
    FC6A1502.user1.master.1> \echo $secmech csfkrb5
    FC6A1502.user1.master.1> \reconnect -SASE1502 -Usa -Psybase
    Open Client Message
    Layer 7, Origin 9, Severity 5, Number 8
    ct_connect(): security service layer: internal security control
                layer error:
    Consistency checks performed on the credential failed
    (minor status 0).
    FC6A1502.user1.master.1> \reconnect -SASE1502 -Usa -Psybase -Znone
    ASE1502.sa.master.1>

The first \reconnect fails because sqsh still wants to try network
authentication. However, no user principal for 'sa' exists and no ticket is set
and thus the connection fails. The second B<\reconnect> succeeds as the -Znone
option reset appropriate variables.
If the Kerberos ticket is renewed with B<kinit> or any other client tool, the
I<sqsh> session must perform a B<\reconnect> to refresh the credentials and
to prevent a premature session abort. With the command B<\snace> you can request
for the session expiration interval. Depending on the security services that are
set, the database connection may be closed without warning as soon as the ticket
expires.

See chapter 16 "External Authentication" from the Sybase System Administration
Guide volume 1 for more information on Kerberos network authenticationi, e.a.

=head1 COMMANDS

=head2 Read-Eval-Print

The read-eval-print loop is the heart of the sqsh system and is responsible for
prompting a user for input and determining what should be done with it.
Typically this loop is for internal use only, however they are open to the user
because there are some creative things that can be done with them.

=over 4

=item \loop [-i] [-n] [-e sql] [file]

The B<\loop> command reads input either from a file, a supplied SQL statement,
or from a user (see the options below), determining whether the current line is
a portion of a T-SQL statement or a sqsh command, and performing the appropriate
action. When run in an interactive mode B<\loop> is also responsible for
displaying the current prompt (see B<$prompt> below).

B<\loop> completes when all input has been depleted (end-of-file is encountered)
or when a command, such as B<\exit> requests that B<\loop> exit.

=over 4

=item -i

Normally, if I<file> is supplied and does not exist, B<\loop> will return with
an error condition, usually causing sqsh to exit. By supplying the B<-i> flag,
control will be returned to the calling loop as if end-of-file had been reached
(that is, with no error condition).

=item -n

By default, B<\loop> will automatically attempt to connect to the database if a
connection has not already been established via the B<\connect> command. The
B<-n> flag disables this behavior allowing B<\loop> to process commands that do
not require database support.

=item -e sql

Causes B<\loop> to process the contents of I<sql> as if the user had typed it at
the prompt and an implicit call to B<\go> is automatically appended to the
statement. If multiple instances of B<-e> are supplied, they are all sent as a
single batch to the SQL Server for processing. This option may not be used in
combination with a I<file> name as well.

=item file

Specifies the name of a I<file> to be used as input rather than reading input
from the user or from the B<-e> flag.

=back

=back

=head3 Database Access

Given the size and complexity of sqsh (just look at the length of this man
page), it is amazing how few database manipulation commands that there actually
are. The following are commands that affect or use the current database
connection:

=over 4

=item \connect [-A packet size] [-c] [-D db] [-G tds version} [-S srv] [-U user] [-P pass] [-I ifile] [-J charset] [-K keytab] [-R server_principal] [-n] [-N appname] [-Q query_timeout] [-T login_timeout] [-V [bcdimoqru]] [-X] [-z language] [-Z [secmech|default|none]]

This command is used primarily for internal use to establish a connection to a
database. If a connection is already established it has no effect, however if a
connection has not been established and B<$password> has not been supplied, then
the password is requested and a connection is established. B<\connect> accepts
the following parameters:

=over 4

=item -A

Specifies the size of the network TDS packets used to communicate with the SQL
server. This value must be between 512 and 8192, and be a multiple of 512. Check
your SQL Server configuration to determine supported packet sizes. This value
may also be specified at run-time using the $packet_size variable.

=item -c

By default, the B<\connect> command uses the contents of B<$database> to
determine the database context that should be used upon establishing the
connection (this is used by B<\reconnect> to preserve the current database
context upon reconnection). The B<-c> flag suppresses this behavior and the
default database context of login is used instead.

=item -D db

Causes B<\connect> to attempt to automatically switch the database context to
I<db> after establishing the connection.

Using this flag is identical to setting the B<$database> variable prior to
establishing the connection.

=item -G tds version

Set the TDS version to use. See the global startup parameter -G for more
information on TDS version.

=item -S srv | host:port[:filter]

The name or address of the Sybase I<server> to connect, this defaults to
B<$DSQUERY> if not supplied.

=item -U user

The Sybase I<user> to connect to the database as, this defaults to B<$username>
variable if not supplied.

=item -P pass

The I<password> for I<user> required to connect to I<server>. This defaults to
B<$password> if not supplied.

=item -I ifile

The full path of an alternate Sybase I<interfaces> file to use.

=item -J charset

The name of the client character set to communicate with the server.

=item -K keytab_file

Used for DCE user authentication.

=item -R principal_name

Use for Kerberos user authentication to specify the name of the server
principal when the name differs from the B<$DSQUERY> value.

See also the discussion on Kerberos support.

=item -n

Specifies that the connection must use ANSI compliant chained mode.

=item -N appname

Specify the application name the server will use for program_name in the
sysprocesses table.

=item -Q query_timeout

Set a query timeout period in seconds.

=item -T login_timeout

Specifies a maximum wait time for session setup.

=item -V [bcdimoqru]

Security services used for Kerberos support and other security mechanisms.

=item -X

Initiates the login connection to the server with client-side password
encryption (if supported). If either SQL Server does not recognize this option,
or if the version of CT-Lib used to compile I<sqsh> does not support this
option, then it will be ignored. This option may also be set using the
B<$encryption> environment variable.

=item -z language

Specifies an alternate language to display sqsh prompts and messages. Without
the -z flag, the server's default language will be used. This may also be set
using the $language variable.

=item -Z [secmech|default|none]

Specifies the security mechanism to use for user authentication. For example
csfkrb5 for Kerberos support.

=back

=item \reconnect [-A packet size] [-c] [-D db] [-G tds version} [-S srv] [-U user] [-P pass] [-I ifile] [-J charset] [-K keytab] [-R server_principal] [-n] [-N appname] [-Q query_timeout] [-T login_timeout] [-V [bcdimoqru]] [-X] [-z language] [-Z [secmech|default|none]]

The B<\reconnect> command may be used to force a reconnection to the database
using a new username, server name, or password (if desired). If this command
fails, the current connection remains (if there is any), however if it succeeds
then the current connection is closed and the new connection becomes the only
active one.

All arguments that are accepted by B<\connect> are also accepted by
B<\reconnect> (in fact B<\reconnect> uses B<\connect> to establish the new
connection).

=item \run

This command will execute a script file like B<\loop> but the B<\run> command
will allow optional script parameters. Furthermore the command accepts the
following parameters.

=over 4

=item -e

Run the script file with echo on.

=item -f

Suppress footers.

=item -h

Suppress headers.

=item -l

Suppres separator lines with pretty output style.

=item -n

Disable SQL buffer variable expansion.

=item -p

Report runtime statistics.

=item -m style

Specify output style {bcp|csv|horiz|html|meta|none|pretty|vert}.

=item -i filename

Required parameter to specify a filename to be run by I<sqsh>.

=back

For example: B<\run -p -i ~/tmp/runtst.sqsh 10 -m pretty>

=item \lcd dirname

Local Change Directory. This command takes a directory name as argument and
changes the local SQSH context to this directory. You can use B<\lcd -> to
return back to the previous directory. If you exit I<sqsh> then the shell is
still in the same directory from where sqsh was started.

=item \pwd

Print Working Directory. Show the name of the current local working directory.

=item \ls

List files in the current directory. Does not take any arguments and is
basically a shortcut for B<\shell ls>.

=item \snace

Will show the session expiration interval for a network authenticated session,
like in a Kerberos enabled session, for example.

=item \go [options] [xacts]

Sends the contents of the B<Work Buffer> to the database, establishing a new
connection to the database if one does not already exist (by calling the
B<\connect> above). It them displays the results of the query back to stdout and
returns, causing the B<Work Buffer> to be cleared and moved to the end of the
B<History Buffer>.

If the B<Work Buffer> is empty and the B<$repeat_batch> variable is set to "On",
B<\go> will attempt to re-run the last command executed (this will only work in
interactive mode if history support is enabled).

B<\go> accepts the following arguments:

=over 4

=item -d display

If X11 support is compiled into sqsh, and X display mode is being used (see
B<-x>, below), then I<display> will be used as the X display area for the result
set. By default the environment variable B<$DISPLAY> is assumed.

=item -e

Echo the expanded SQL buffer before sending it to the server.

=item -f

Turns off the display of the footer message "(%d rows affected)". Footer
messages may also be turned off via the B<$footers> variable.

=item -h

Turns off all column headers. These may also be turned off via the B<$headers>
variable.

=item -m style

Temporarily changes the display style to I<style> for the duration of the
command. Currently supported styles are B<horiz> (or B<hor> or B<horizontal>),
B<vert> (or B<vertical>), B<bcp>, B<csv>, B<html>, B<meta>, B<pretty> and
B<none>. The display style may be permanently set via the B<$style> variable or
the B<-m> command line flag.

=item -l

Suppress separator lines when using the B<-m pretty> output style. May also
be turned off via the B<$nosepline> variable.

=item -n

Turns off variable expansion in the B<Work Buffer> prior to sending it to the
server, this may also be turned off via the B<$expand> variable.

=item -p

Turns on output of performance statistics when the result set has been
successfully returned from the server. This may also be turned on via the B<-p>
command line argument to sqsh, or the B<$statistics> variable.

=item -s sec

If the value of I<xacts> is greater than 1, this causes sqsh to sleep for I<sec>
seconds before executing the next transaction. Note that the time spent sleeping
is excluded from the statistical information displayed with the B<-p> flag.

=item -t [filter]

Filters the command batch through an external program, I<filter>, and prior to
being sent to the SQL Server. If I<filter> is not supplied, then B<$filter_prog>
is used (default is 'm4 -'). This value may also be set via the B<$filter> and
B<$filter_prog> variables.

=item -w width

Overrides the value of B<$width> for the life of the query (see B<$width>
below).

=item -x [xgeom]

Turns on the X11 display filter (only if X11 support is compiled into sqsh),
which causes the result set to be sent to a separate window. If I<xgeom> is
supplied, then this value will be used as B<$xgeom> for the life of the query
(see B<$xgeom> below).

=item -T xwin_title

Specify the title name of the X result window to create. This will temporarily
override the value of B<$xwin_title>. Only useful to specify -T in conjunction
with B<-x>.

=item xacts

Specifies number of times the contents of the B<Work Buffer> should be executed.
Note that, similar to isql, a result set will only be displayed during the final
execution of the batch. Also, the contents of the B<Work Buffer> are only
expanded once, prior to the first execution, so the contents of the buffer will
not change between subsequent executions.

=back

=item \bcp [bcp_options] table[:slicenumber|:partition name]

The B<\bcp> commands acts as a sort of enhanced B<\go> command that redirects
the result set(s) of the batch to another server via the bcp protocol. While it
is possible to B<\bcp> the result set back to the current server (the
B<$DSQUERY> variable), this is achieved more easily via a SELECT INTO.

The nitty-gritty details of B<\bcp> go like this: First the current SQL batch is
expanded (unless the B<$expand> variable is set to 0) and shipped off to the
database for processing. If all goes well, a new connection is established to
the destination database (as specified via B<$DSQUERY> or the B<-S> flag) to
transfer the result set using bcp. Then, the output of the source database
connection is bound to the new bcp connection and data transfer is performed.
B<\bcp> can handle multiple result sets without any problem (including result
sets returned from stored procedures, etc.) provided that all of the result sets
are valid for the destination table.

The equivalent of a "bcp out" may be performed using the B<bcp> display style
setting and file redirection (see the B<$style> variable).

=over 4

=item -A packet

Specifies the TDS packet size used to communicate with the destination server.
If not supplied this defaults to the value the B<$packet_size> variable, or (if
that is not set), the default server packet size (usually 512 bytes).

=item -b batch_size

The number of records transferred in a single transaction between servers. Note
that reaching the end of a result causes the batch to be transferred, regardless
of the value of I<batch_size>. The default is the entire result set.

=item -i "<initialization command>"

Using the I<-i> parameter you can send a SQL command to the target server that
will be executed just before the bulk copy operation is started. This is useful
if you need to truncate the target table first. For example:

    1> select * from proddb..materials
    2> \bcp -SDTA -i "truncate table testdb..materials" -N -X testdb..materials

=item -I ifile

The full path of an alternate Sybase I<interfaces> file to use.

=item -J charset

Specifies the default I<charset> used to communicate with the SQL Server. This
defaults to the current character set (the value of the B<$charset> variable).

=item -m maxerr

The maximum number of batches that may fail before B<\bcp> gives up the ghost
(default is 10). Note that this only refers to failures within a given batch.
When performing a bcp of multiple result sets to a server, if a given result set
has, say, too many columns or bad data types, then the entire bcp process is
aborted regardless of the value of I<maxerr>.

=item -N

Indicates that the value for an identity column in the destination table is
being supplied within the result set.

=item -P password

The I<password> for I<user> required to connect to I<server>. This defaults to
B<$password> if not supplied.

=item -S server | host:port[:filter]

The name or address of the Sybase I<server> to connect, this defaults to
B<$DSQUERY> if not supplied.

=item -T

Transfer the data in transit without performing character set conversion
at the client side.

=item -U user

The Sybase I<user> to connect to the database as, this defaults to B<$username>
variable if not supplied.

=item -X

Causes password negotiation with the destination server to be performed using
client-side encryption.

=item -z language

Specifies the language setting to use.

=item table[:slicenumber|:partition name]

As with regular B<bcp>, I<table> may be either a fully or partially specified
table name in the destination server. Note that since a new database connection
is established during the bcp processes that the database context of the
connection may not be the same as the current context, so it is usually safest
to fully specify the table name in the form database.owner.table.
For partitioned tables you may supply a slicenumber or a partition name
(separate table name and partition with a colon (:)) to bcp the data into the
specified partition.

=back

=item \rpc [rpc_opt] rpc_name [[parm_opt] [@var=]value ...]

The B<\rpc> command is used to directly invoke a stored procedure call in the
connected server. This command is particularly useful for communicating with an
Open Server that does not directly support language calls.

B<\rpc> invokes the remote procedure I<rpc_name> with one or more parameters
that may be named (using I<@var>) or anonymous (by not supplying a name).
Unfortunately, due to the fact that Sybase's implementation of RPC's, does
not directly support most implicit data type conversions (mainly between VARCHAR
(the string you supply on the command line) and the most other data types (that
the remote procedure is expecting), the syntax for the B<\rpc> command is
somewhat complex. However, in short here is how things work:

As the B<\rpc> command line is being parsed, I<sqsh> attempts to guess the data
type of the parameter I<value> based on the format (for example if it contains
only digits, it is assumed to be an integer), I<sqsh> then performs an explicit
data type conversion prior to calling the remote procedure call. If I<sqsh>
guesses wrong, several flags are supplied to force it to perform the correct
data type conversion (see B<parm_opt>).

=back

=head3 Display Options

The following options may be supplied anywhere on the command line and are used
to affect the manner in which the result set(s) returning from the remote
procedure call are displayed:

=over 4

=item -d display

If X support is compiled into sqsh, the value of I<display> is used as the X
windows DISPLAY variable. Note, this is usually supplied with the B<-x> flag,
below.

=item -f

Turns off the display of the footer message "(%d rows affected)". Footer
messages may also be turned off via the B<$footers> variable.

=item -h

Turns off all column headers. These may also be turned off via the B<$headers>
variable.

=item -m style

Temporarily changes the display style to I<style> for the duration of the
command. Currently supported styles are B<horiz> (or B<hor> or B<horizontal>),
B<vert> (or B<vertical>), B<bcp>, B<csv>, B<html>, B<meta>, B<pretty> and
B<none>. The display style may be permanently set via the B<$style> variable or
the B<-m> command line flag.

=item -r

Request to recompile the procedure prior to execution.

=item -w width

Temporarily sets the output width to I<width>. The output width may be
permanently set via the I<$width> variable.

=item -x [xgeom]

Sends output to a separate X window. If I<xgeom> is supplied, then the X window
uses this geometry (see B<$xgeom> for details).

=item -T xwin_title

Specify the title name of the X result window to create. This will temporarily
override the value of B<$xwin_title>. Only useful to specify -T in conjunction
with B<-x>.

=back

=head3 Parameter Options

The following options may be supplied immediately prior to specifying a
parameter I<value> and are used to affect the way in which I<sqsh> interprets
the contents of the I<value> prior to calling the remote procedure. Although
I<sqsh> will allow any combination of these parameters to be combined, it only
really makes sense to combine the B<-x> flag with any other flag.

=over 4

=item -b

Indicates that the I<value> that is specified should be converted to VARBINARY
before calling I<rpc_name>. This flag is implicit (i.e. you need not supply it)
if I<value> starts with "0x" and contains only digits.

=item -c

Indicates that the I<value> that is specified should be converted to VARCHAR
prior to calling I<rpc_name>. This flag is implicit if I<value> does not match
any of the implicit conversions for the other data types.

=item -d

Indicates that the I<value> that is specified should be converted to double
(float) before calling I<rpc_name>. This flag is implicit if I<value> is in
valid floating point notation (e.g. 0.1, .1, 1.4e10, or 4e10).

=item -i

Indicates that the I<value> that is specified should be converted to integer
(int) before calling I<rpc_name>. This flag is implicit if I<value> contains
only digits (and, optionally, a leading sign).

=item -y

Indicates that the I<value> that is specified should be converted to money
before calling I<rpc_name>. This flag is implicit if I<value> begins with a "$",
and contains only digits and, optionally, a decimal.

=item -n

Indicates that the I<value> that is specified should be converted to numeric
before calling I<rpc_name>. This flag is never implicit, as I<value> would
always match either int (B<-i>) or float (B<-d>); however, both of these types
will implicitly be converted to a numeric as necessary by the procedure call.

=item -u

Indicates that I<value> should be ignored and treated as a NULL value, This flag
is implicit if I<value> is "".

=back

=head2 Buffers

The following commands may be used to create, destroy, or manipulate the various
buffers described in the B<BUFFERS> section, above.

=over 4

=item \clear

The B<\clear> command will discard the current buffer and in contradiction with
the B<\reset> command, will not save the current buffer to the history. When
sqsh is compiled with readline support, this command will also clear the screen.
(as ^l does.) The alias B<clear> is automatically established upon startup.

=item \history [-i] [-x count]

Displays the last B<$histsize> batches that have either been sent to the
database via the B<\go> command or cleared from the B<Work Buffer> via the
B<\reset> command. With option B<-i> also display the number of times the buffer
is used and the last time the buffer was used. This information may be of
special interest when using the B<$histunique> feature. The B<-x> option may
specify the number of most recent history entries to display instead of the
total list of history entries.

=item \hist-load [filename]

Load a history file and append items to the current history list.
Use B<$history> if no filename is specified as a parameter.

=item \hist-save [filename]

Will save the current history buffers to the B<$history> file or to the filename
specified as a parameter.

=item \redraw

Returns a request back to the current read-eval-print loop for it to redisplay
the current B<Work Buffer>. If run from non-interactive mode, this command has
no effect.

=item \reset

The B<\reset> command corresponds directly to the I<isql> 'reset' command,
returning a request to the read-eval-print loop to clear the contents of the
current B<Work Buffer> and, if you are running in interactive mode, place a copy
of the buffer into the B<History Buffer>. The alias B<reset> is automatically
established upon startup of I<sqsh> for backward compatibility with I<isql>.

=item \buf-append dst-buffer [src-buffer]

Appends the contents of I<src-buffer> (defaults to !.) to the contents of
I<dst-buffer>, if it exists. If I<dst-buffer> doesn't exist it is created.

=item \buf-copy dst-buffer [src-buffer]

Copies the contents of I<src-buffer> (defaults to B<!.>, the B<Work Buffer>, if
not supplied), to I<dst-buffer>. Refer to B<BUFFERS> for information on buffer
naming conventions.

=item \buf-del [buffer|range]

Remove a history buffer from the history list. You can also specify a range list
consisting of the first and last buffer numbers separated by a '-'. For example:
"\buf-del 1-10" will delete the first 10 history entries. Another example,
"\buf-del 21" will only delete buffer number 21. Note that the history buffers
will be renumbered consecutively.

=item \buf-edit [-r read-buf] [-w write-buf]

The B<\buf-edit> command is used to edit the contents of a buffer and place the
changes into another buffer. This command may only be run while in interactive
mode. If I<read-buf> is not supplied then the buffer to be edited defaults to
!., if it is not empty, otherwise it defaults to !!. If I<write-buf> is not
supplied then the edited buffer is written back to !..

By default, B<\buf-edit> uses the environment variable B<$EDITOR> first,
followed by B<$VISUAL> to determine which editor to use, defaulting to 'vi'
if the variable is not set.

It is important to note that as of release 1.2, B<\buf-edit> is no longer able
to use the name of an alias to it as the name of the editor to launch. This is
primarily due to the change in the behavior of alias (see section B<Aliasing>,
below, for details).

The commands B<edit vi> and B<emacs> are automatically established upon startup
of I<sqsh> for backward compatibility with I<isql>.

=item \buf-get buffer

The B<\buf-get> command is supplied as a shorthand method of running
B<\buf-copy> It is the equivalent of running:

    \buf_append !. buffer

=item \buf-load [-a] filename [dst-buffer]

Copies the contents of I<filename> in I<dst-buffer> (defaults to !.). If the
B<-a> flag is supplied, the contents of I<filename> are appended to
I<dst-buffer>. Note that it is illegal to attempt to write to the contents of
the history buffer.

=item \buf-save [-a] filename [src-buffer]

Saves the contents of I<src-buffer> (defaults to !.) to I<filename>. If the
B<-a> flag is supplied the contents are appended to I<filename> rather than
overwriting the current contents.

=item \buf-show [buffer]

Displays the contents of the named I<buffer>. If I<buffer> is not supplied, then
the contents of all named buffers are displayed. This command is slightly
different from the commands above in that it is only legal to supply a
B<Named Buffer> I<buffer>, B<History Buffers>, and the B<Work Buffer> will have
no results.

=back

=head2 Variables

The following command(s) are used to manipulate the contents of internal
variables and environment variables.

=over 4

=item \set [-x] [name=I<value> ...]

If no arguments are supplied to B<\set> then the current values of all variables
are displayed. Otherwise the variable I<name> is set to I<value>. Note that some
internal variables (see B<SPECIAL VARIABLES>) may only be set with certain
I<value>s, so this action may fail, leaving the previous contents on I<name>
intact. The B<-x> flag causes the variable to be exported to the environment of
any programs launched from I<sqsh>.

=back

=head2 Job Control

The following commands are used to view the status of, or manipulate background
jobs that are currently running, these correspond roughly to the commands
supplied by such shells as B<csh(1)>.

=over 4

=item \jobs [-i]

Displays the status of any currently running jobs, including whether or not
these jobs have pending output, how long they have been running, and when they
were started. The B<-i> option will show some additional job information. Note
that the total run time of the job is determined from the moment the job is
marked complete by the read-eval-print loop when polled, not when it actually
ended and is flagged as terminated by the signal handler.

=item \wait [job_id]

Will pause until job designated by I<job_id> completes. If I<job_id> is a
negative number then B<\wait> will pause until I<any> pending jobs completes. If
there are no jobs pending, or I<job_id> does not belong to a running job, then
an error message is displayed. Note that if multiple jobs are running, and a
B<\wait> is issued for one of them, the other jobs may not automatically get
signaled when completed within the wait period of the specific job. The other
jobs may need to get signaled with a B<\wait -1> as well to be noticed complete
by I<sqsh>.

=item \kill job_id

Terminates the job specified by I<job_id>, throwing away any output that may be
deferred for the job. If I<job_id> is not a running job then an error message is
displayed.

=item \show job_id

Displays the deferred output of completed background job I<job_id> and removes
the job from the list of pending jobs (removing the defer file in the process).
If I<job_id> is still running, or is not a valid complete job, then an error
message is displayed. You may need to issue a B<\wait job_id> first, to get
notified of actual job completion.

=back

=head2 Aliasing

As of release 1.2, I<sqsh> supports full I<csh>-style command aliasing. With
this feature, I<sqsh> checks the first word of each line, I<prior to any form of
expansion>, to see if it matches the name of an existing alias. If it does, the
command is reprocessed with the alias definition replacing its name. Unlike
I<csh>, however, only one form of history substitution is available within an
alias: the 'B<!*>' entry, indicating the current line being expanded. If no
history expansion is called for, the arguments on the command line remain
unchanged.

Like I<csh>, aliases are not recursively expanded, so it is perfectly legal to
create an alias that expands to a command by the same name.

The following command is used to create an alias:

=over 4

=item \alias [alias_name=alias_body]

If no arguments are supplied to the B<\alias> command, then the list of aliases
currently in effect is displayed. Otherwise, it creates a new alias with a name
of I<alias_name> and a body of I<alias_body>; if I<alias_name> already exists,
the body of the existing I<alias_name> is replaced with the new definition.

After defining the new alias, whenever I<sqsh> encounters a line beginning with
I<alias_name>, the remainder of the line is replaced with I<alias_body> before
any further processing is performed.

If the string 'B<!*>' exists anywhere within I<alias_body>, the arguments
supplied to the alias are inserted at that point, otherwise the argument are
appended to the end of the alias definition. For example:

    1> \alias hi='\echo !* said hello'
    1> hi Scott
    Scott said hello

where as if the alias does not include the B<!*> keyword, then it behaves like
so:

    1> \alias hi='\echo said hello'
    1> hi Scott
    said hello Scott

It is perfectly legal to include a B<!*> more than once within a given
I<alias_body>. Currently there is no way to escape the string B<!*>, if you
really need this feature send me mail.

=item \unalias alias_name

Removes I<alias_name>.

=back

=head2 Miscellaneous

The left over commands.

=over 4

=item \exit [x]

The B<\exit> command requests that current read-eval-print loop cease
processing. When the last loop returns, I<sqsh> B<exit(1)>s. You may specify a
exit code as parameter which will be stored in B<$exit_value> and will be used
as return value to the shell upon exit of I<sqsh>. (0 <= x <= 255).

=item \abort

Causes all nested read-eval-print loops to abort processing, causing I<sqsh> to
exit with an exit value of 254 (see section B<EXIT STATUS>).

=item \read [-a] [-n] [-h] var_name [< filename]

Reads a line of input from the user, placing the text of the line in the
variable I<var_name>. If the B<-n> is used, then the trailing new-line is left
on the line of text, and if B<-a> is supplied, then the text of the line is
appended to the existing value of I<var_name>. The B<-h> flag turns off echoing
of typed characters back to the user. It is also possible to read the contents
of a file using the I<\<> file redirection notation.

=item \sleep seconds

Causes I<sqsh> too pause for I<seconds>. This is useful within scripts of
batches which need to pause briefly between batches (it was primarily useful to
me for testing background jobs).

=item \echo [-n] [args ...]

Just like the UNIX B<echo(1)>, this prints its arguments to stdout, followed by
a new-line. If the B<-n> flag is supplied, the newline is omitted.

=item \warranty

Displays the standard GNU warranty.

=item \help [command]

Without any arguments B<\help> displays a brief list of all available commands,
otherwise, it provides specific help for I<command>, if available. When help is
requested on a specific I<command>, B<\help> looks for the file
I<$help_dir/command.hlp> and displays it to stdout.

=item \shell [shell command]

If I<shell command> is not supplied then I<sqsh> executes B<$SHELL>. If the
B<$SHELL> variable has not been set, then, by default, /bin/sh is executed.
Otherwise, if I<shell command> is supplied then it is executed. The exit status
of the command executed is stored in the special B<$?> read-only environment
variable.

=item \lock

Locks the current session until the correct password is typed. By default
B<\lock> attempts to use the UNIX password (from /etc/passwd or /etc/shadow)
associated with the user running I<sqsh> (if sqsh is linked with the crypt
library), however if the B<$lock> variable is set then the contents of that is
used for validation instead.

=back

=head2 Aliases

The following aliases are established upon startup of I<sqsh>, and are provided
primarily for backward compatibility with I<isql>. These may be removed at any
time using the B<\unalias> command (either at the prompt, or within your .sqshrc
file).

=over 4

=item !

The B<!> alias is provided as a B<csh(1)>-like history mechanism, and is an
alias of B<\buf-append>. With release 0.7, this alias is provided only for
backwards compatibility with previous releases of I<sqsh>. See B<SPECIAL
VARIABLES>, B<$history_shorthand> for details on the new shorthand mechanism
(the new shorthand more closely resembles that of B<csh>).

=item clear

An alias for the B<\clear> command, which causes the contents of the current
work buffer to be cleared and discarded. Also the screen will be cleared if
readline is compiled into I<sqsh>.

=item reset

An alias for the B<\reset> command, which causes the contents of the current
work buffer to be cleared and copied to history (if in interactive mode).

=item exit [x]

=item quit [x]

An alias for the B<\exit [x]> command, causes the current read-eval-print loop to
complete.

=item edit

=item vi

=item emacs

These are provided as aliases for the B<\buf-edit> command. See
B<COMMANDS-Buffers> for information on the interactions between B<\buf-edit> and
aliases.

=item go

Provided as an alias for the B<\go> command (for obvious reasons).

=item help

An alias for the B<\help> command.

=back

=head3 In-Line \go

If the variable B<$semicolon_hack> is set to 1 (on), then sqsh supports what is
called an in-line B<\go> feature. This allows the current command batch to be
terminated and sent to the database in a single step by appending a ';' onto
the end of the current work buffer. This allows

    1> sp_who;

To behave in the same manner as if you had typed:

    1> sp_who
    2> \go

Likewise, anything following the semicolon is passed to the B<\go> command just
as if it was run as a normal command:

    1> sp_who ; 2>/dev/null | more

Unlike most other I<isql> replacements, I<sqsh> attempts to be smart about the
semicolons. If a semicolon is contained within a set of single or double quotes
it will not be interpreted. This includes multiple quotes. For example:

    1> select "This is a multiple line
    2> quote; it is smart!" ;

In the above example, only the second semicolon (the one at the end of the line)
will be interpreted.
The variable B<$semicolon_cmd> is a string that contains the command that will
be substituted by the semicolon which is B<\go> by default. But you can change
that to B<\bcp> for example and execute:

    1> select * from pubs2..titles; -S... tempdb..titles

In sqsh-2.2.0 you can also set variable B<$semicolon_hack2> to allow multiple
commands on one line to be fired by a semicolon. If this option is set you
cannot use the construct above to pass additional parameters to the B<\go>
command. But of course you can change B<$semicolon_cmd> to your needs as this
command will be used to execute SQL batches.

    echo "exec sp_who;exec sp_helpdb;\echo Done;" | sqsh -S... -U... -P...

In this example the semicolon acts as a B<\go> command to a SQL buffer and as
a command separator for sqsh commands that will be executed in sequence.
Note that B<$semicolon_hack2> takes precedence over B<$semicolon_hack>.

=head1 SPECIAL VARIABLES

There are several options that are configurable via the command line options to
I<sqsh>, however these are by no means complete. There are many aspects of
I<sqsh>'s behavior that may only be modified by setting special variables. (In
fact, the command line options really only set these variables for you).

=head2 Variable Data types

Next to all of the variables that follow is the type of data with which they may
be set. Any attempts to set the variable with a type of data that it does not
accept will fail.

=over 4

=item string

Any sequence characters.

=item boolean

A positive I<boolean> value may be represented as either "True", "Yes", "1", or
"On" (case insensitive) and a negative boolean value may be represented as
"False", "No", "0", or "Off" (case insensitive). However, internally the value
of the variable will always be represented as either a "1" or "0".

=item path

Must be the I<path> name that is readable by the I<sqsh> program.

=item int

Must be one or more digits. Note that some variables also restrict the range of
the integer.

=item date-spec

This is a string of the format used to specify dates and times for the
B<date(1)> command, or the B<strftime(3C)> and B<cftime(3C)> standard C library
functions. For example '%H:%M:%S' specifies a time of hours in 24 hour
format, followed by a colon, followed by minutes, followed by a colon, followed
by seconds.

=item float-format

A string of the format B<p.s>, where B<p> is the total precision of a floating
point value (the total number of digits to be displayed, including those
following the decimal) and B<s> is the scale of the value (the total number of
digits following the decimal to be displayed).

=back

=head3 Variables

The following variables have special meanings within I<sqsh> and the setting of
these variables alter the behavior of the shell.

=over 4

=item $? (int)

This read-only variable may contain the following return value:

=over 4

=item *

The most recent error number returned from the SQL Server (@@errno) of severity
> 10 (above informational messages).

=item *

The exit value of a previously executed pipe command.

=item *

The return value of the most recently executed sqsh function.

=item *

The result of the last executed \if statement:

=over 4

=item 0 - The test evaluated to True.

=item 1 - The test evaluated to False.

=item 2 - There was an error in the test specification.

=back

=back

=item $# (int)

Contains the number of arguments passed into the sqsh function or script.

=item $* (string list)

Expands to the complete list of arguments supplied to the function or script
when invoked.

=item ${0}...${N} (int)

Used to reference positional function arguments. Argument ${0} is the name of
the function being called, ${1} is the first argument, etc.

=item $$

Expands to the process ID of the current running sqsh session.

=item DISPLAY

May contain the name of the DISPLAY the X-server uses to put the XWindows
result window when using B<\go -x>. Defaults to the I<$DISPLAY> shell
environment.

=item appname

Contains the name I<sqsh> uses to identity itself to the server. Defaults to
sqsh-2.1.7. The application name can be retrieved from the sysprocesses table
like:
    select program_name from master.dbo.sysprocesses where spid=@@pid;

=item autouse (string)

Note: the meaning of this variable has been deprecated.

If B<$autouse> is set, and the B<$database> variable has not been set, then this
variable causes B<\connect> to perform a "use B<$autouse>" once a connection has
been established.

This variable may also be set using the B<-D> command line option.

=item banner (boolean)

Turns off the banner message displayed on startup, this variable defaults to 1
and may also be turned off using the B<-b> command line argument.

=item batch_failcount (int)

This internal variable is used to keep track of the number of batches that have
failed to execute (essentially, the number of times that the error handler was
called).

A batch is considered failed whenever an error of severity B<$thresh_fail> is
encountered. When B<$batch_failcount> reaches B<$thresh_exit> I<sqsh> exits with
an exit value of the total number of batches that have failed. Setting
B<$batch_failcount> to the string "" will cause it to reset to zero, any other
value may have unpredictable results.

See B<EXIT STATUS> for details.

=item batch_pause (boolean)

Causes a "Paused. Hit enter to continue..." message to be displayed after each
batch is executed. This variable, in conjunction with B<$echo> is good for
debugging SQL scripts specified with the B<-i> option.

=item bcp_colsep (string)

Used as a separator between columns during BCP style output (see the B<$style>
configuration variable and the B<-m> option to the B<\go> command). The default
setting is "|".

=item bcp_rowsep (string)

Used as a separator between rows during BCP style output (see the B<$style>
configuration variable and the B<-m> option to the B<\go> command). Note that, a
newline ("\n") is automatically appended and should not be supplied.
The default setting is "|".

=item bcp_trim (boolean)

Controls whether or not BCP style output trims trailing spaces from fixed length
columns. The default is "True".

=item builddate (none)

Read only variable specifying the date when the I<sqsh> executable was compiled.

=item buildtime (none)

Read only variable specifying the time when the I<sqsh> executable was compiled.

=item chained (boolean)

If set then sqsh uses the "chained" transaction mode (aka "AutoCommit off").
Setting this has NO effect on the current connection. It can be set via the
command line argument B<-n>.

=item charset (string)

If this variable is set prior to establishing a connection with SQL Server, then
during the connection I<sqsh> will request that the server transform to and from
the requested I<charset>. After establishing a connection, this variable is
automatically set to the current character set in use.

=item clear_on_fail (boolean)

Normally, whenever the B<\go> command is run, I<sqsh> clears the current work
buffer of its contents, moving them to history. Setting B<$clear_on_fail> to 0,
leaves the current work buffer intact if a failure is encountered while sending
the contents to the database. The default value is 1, or on.

=item colsep (string)

Causes the string I<colsep> to be used to delimit SQL column output columns,
this defaults to " ", it may also be set via the command line argument B<-s>.

=item colwidth (int)

Used to control the maximum column width displayed by the B<pretty> display
style (see B<$style> below). If a row of a column exceeds this width, it will be
wrapped in a relatively visually appealing manner at B<$colwidth> characters.
Note, however, that if there is enough screen width to hold all columns
B<$colwidth> may be exceeded until the width of the screen is reached.

=item database (string)

If this variable is set prior to establishing a connection to the SQL Server,
the a "use B<$database>" is performed immediately after the connection is
established. Once a connection has been established this variable will
automatically be set to the current database context.

=item date (date-spec)

This variable may be set with a date format (see the man page for B<date(1)>),
and the variable expands to the current date in the supplied format. The default
format for this variable is %d-%b-%y (e.g. 02-Feb-1996).

=item datefmt (date-spec)

This variable may be set with a date format similar to B<$date> and
is used to control the display format of all SQL Server DATE columns. (Similar
to B<$datetime>.) Note that only 63 bytes are available in total for the
expanded string and the remainder will be truncated.

=item datetime (date-spec)

This variable may be set with a date format similar to B<$date> and B<$time> and
is used to control the display format of all SQL Server DATETIME, BIGDATETIME
and SMALLDATETIME columns. Note that only 63 bytes are available in total for
the expanded string and the remainder will be truncated.

Note that this features relies upon the operating system specific locale
information and the setting of B<$localeconv> for determining such things as the
name of the month and day, rather than going through the CT-Lib locale
information. This means that the date format could potentially miss-match the
locale as requested using the B<-z> flag. For example, if I<sqsh> is run on an
operating system configured for US English, but requests French as the language
of choice using B<-z>, the use of B<$datetime> will cause all date information
to be displayed in US English rather than French.

Ordinary characters defined in the variable are left in place without any
conversion. Characters introduced by a '%' character are replaced during
display of a column value according to the definitions in the B<strftime>
manual page.

=over 4

=item []

Any contained between a pair of braces ('[' and ']') will be removed
when displaying SMALLDATETIME columns. This feature is particularly useful for
removing the seconds, milliseconds or microseconds values which are not
applicable to SMALLDATETIME anyway. For DATETIME or BIGDATETIME columns,
only the actual braces will be removed.

=item %q

Specifies the milliseconds for DATETIME and TIME and microseconds for
BIGDATETIME and BIGTIME data types. In previous versions of sqsh the I<%u>
specifier was used for the millisecond part of the datetime datatype.
However, as I<%u> is used by B<strftime> to denote the day number of the week,
this is replaced by I<%q> since sqsh-2.3. For example when using the default C
locale:

    1> \set datetime='%e %b %Y %H:%M[:%S.%q]%p'
    1> select convert(bigdatetime,getdate())
    2> select convert(smalldatetime,getdate())
    3> go

    -----------------------------
    22 Jul 2013 13:26:52.938000PM
    -------------------
    22 Jul 2013 13:27PM

=back

=item debug (string)

If I<sqsh> has been compiled with debugging enabled (-DDEBUG), this variable may
be used to control the amount of debugging output displayed. B<$debug> may be
set to a pipe (|) delimited (logical OR) set of the following words to turn on
various pieces of debugging: B<ALIAS>, B<AVL>, B<BCP>, B<DISPLAY>, B<ENV>,
B<ERROR>, B<EXPAND>, B<FD>, B<HISTORY> (abbr. B<HIST>), B<JOB>,
B<READLINE> (abbr. B<RL>), B<RPC>, B<SCREEN>, B<SIG>, B<SIGCHLD> (or B<SIGCLD>),
B<TDS> or B<ALL>. Can also be set through the I<-l> startup option.

=item debug_tds_capture (string)

When B<TDS> debugging is enabled and this variable is defined with a valid file
name, a debugging trace will be logged into this file that can be analyzed with
Ribo. You might need the devlib versions of the OpenClient library to link with
sqsh to be able to use this option. See for more information the ct_debug
function in the Sybase SDK documentation.

=item debug_tds_logdata (string)

When B<TDS> debugging is enabled and this variable is defined with a valid
filename, a debugging log will be written by OpenClient with all possible
debugging info (CS_DBG_ALL). See also option above.

=item defer_bg (boolean)

Normally, when a job is run in the background (via a '&' on the command
line), the output of the job is deferred to a temporary file (located in
I<$tmp_dir>) until the user requests the output to be displayed using B<\show
jobid>. This way the results of the job will not interfere with what the user is
doing. Setting this variable to false will result in no deferred output files
being created and output sent to the screen immediately

=item echo (boolean)

Setting B<$echo> to on (1) causes each command submitted to the database via the
B<\go> command to be displayed prior to the output. This variable defaults to 0
(or off), and may also be set using the B<-e> command line option.

=item encryption (boolean)

Setting the B<$encryption> variable prior to establishing a connection to the
server will cause the login connection to be initiated using client-side
password encryption. This variable may also be set using the B<-X> command line
option. Client-side password encryption will also occur when using the B<-X>
option on the B<\connect>, B<\reconnect> or B<\bcp> commands.
Please note that the Sybase ASE setting for the B<net password encryption reqd>
(15.0.2) configuration parameter may enforce you to use client side password
encryption. I<sqsh-2.1.9> supports RSA password encryption when the CT-Lib
version in use supports it. RSA password encryption is required if the above
mentioned ASE configuration parameter is set to B<2>. Older version of sqsh only
supports a value of B<1> (CT-Lib internal encryption algorithms).

=item exit_failcount (boolean)

Settings this value to 1 causes I<sqsh> to return an exit status of
B<$batch_failcount> rather than 0, upon a non-error termination. See B<EXIT
STATUS> for details. The default value is 0.

=item exit_value (int)

When you exit sqsh by specifying an exit code, like B<\exit 3> or using one of
the aliases B<exit> or B<quit> commands, then this value is assigned to the
B<$exit_value> variable. You can also assign a value using the B<\set> command.
When sqsh determines this variable has a non-zero value during termination, this
value will be used as exit code. See B<EXIT STATUS> for details.

=item expand (boolean)

By default when the B<\go> command is executed, the contents of the current work
buffer is expanded of all environment variables prior to being sent to the
database for execution. By setting this variable to "0", the buffer will no
longer be expanded before being sent to the database. This is useful when you
either (1) have strings in the buffer that contain a '$' and you don't
want them to be expanded, or (2) for performance reasons; it takes time (and an
extra copy of the buffer) to perform the variable expansion.

=item filter (boolean)

Toggles filtering of the SQL batch through an external program (defined by the
B<$filter_prog> variable, below) prior to being sent to the SQL Server. Default
is '0', or 'off'.

=item filter_prog (string)

Defines the external program through which the SQL batch will be filtered prior
to being sent to the SQL Server. This variable is ignored if B<$filter> is set
to '0' or 'off'. The default is 'm4 -'.

=item float (float-format)

Defines the display format (the precision and scale) for all floating point
values displayed by sqsh. The default is '18.6'. Note that values exceeding
the defined precision are not truncated, so setting this value too low may cause
columns in a result set to be miss-aligned.

=item footers (boolean)

Toggles the "(%d rows affected)" following a result set. The default for this
variable is '1'.

=item headers (boolean)

Toggles the column headers preceding a result set. The default for this variable
is '1'.

=item help_dir (path)

This is the location of the help files used by the B<\help> command, typically
it defaults to something like /usr/local/lib/sqsh/help.

=item hist_auto_save (int)

When variable B<$histsave> is on and the B<$hist_auto_save> variable has a value
greater than 0, then the history will be automatically saved to B<$history>
after B<$hist_auto_save> modifications of the history buffers. When the value is
0 (default), then no automatic history save will be performed.

=item histmerge (boolean)

When this option is on, sqsh will merge the contents of the history on disk
with the history in memory before writing the history back to disk. This is
to prevent data loss in the history file when other sqsh sessions have also
written to the same history file on disk. This option is off by default which
will just overwrite the existing history file when you exit sqsh or the
history is being automatically saved because of B<$hist_auto_save> being set.

=item histnum (int)

Contains the history number that will be assigned to the current command batch
as soon as the B<\go> command is executed. This variable should be considered
read-only. See also the discussion on the B<$histunique> variable.

=item history (path)

This is the location of the history file used to store and retrieve a user's
history during start-up and shutdown. This defaults to I<$HOME/.sqsh_history>.
This variable is expanded each time it is referenced by sqsh, much in the same
way that B<$prompt> is referenced each time the prompt is displayed.

=item history_shorthand (boolean)

This variable is only meaningful within an interactive session. If set, it turns
on the ability to append any named buffer or history buffer onto the current
work buffer in a 'sh' history style, such as '!40'. Be careful with this
feature, I<sqsh> is not terribly intelligent with looking for history shorthand,
so it is possible that it may get confused (although, it is smart enough to
ignore !'s in quoted strings).

=item histsave (boolean)

The value of this variable is used by I<sqsh> to indicate whether the history
should be saved to B<$history> prior to termination of sqsh.

=item histsize (int)

The value of this variable is used to alter the maximum number of history
entries are maintained by I<sqsh> (the default is 10). Note that decreasing the
value of this variable causes some history entries to be lost.

=item histunique (boolean)

If set, I<sqsh> maintains a MRU-LRU order of executed buffers and does not store
duplicate command buffers. For example, observe the following situation:

    LINUX1502.user1.master.1> \history
    (1) sp_who
    (2) grant role mon_role to sa_role
    (3) select * from monProcessActivity
    (4) select @@authmech,show_sec_services()
    (5) select @@servername,@@authmech,show_sec_services()
    LINUX1502.user1.master.1> sp_who
    LINUX1502.user1.master.2> go
    ... output omitted
    LINUX1502.user1.master.1> \history
    (1) grant role mon_role to sa_role
    (2) select * from monProcessActivity
    (3) select @@authmech,show_sec_services()
    (4) select @@servername,@@authmech,show_sec_services()
    (5) sp_who

I<sp_who> is the last executed command and the buffer - originally the last in
the list - is now on top of the list. When an already existing buffer is reused,
the value of the B<$histnum> variable is not changed.

=item hostname (string)

Used during the connection process to indicate to SQL Server the name of the
host from which I<sqsh> is connecting. This variable may also be set using the
B<-H> flag.

=item ifs (string)

The list of Internal Field Separators.

=item ignoreeof (boolean)

By default, I<sqsh> terminates if the user presses ^d (control-D) on an empty
line and readline support is compiled in. If B<$ignoreeof> is set in the sqshrc
file you get a warning message instead:

    CTRL-D: Use "exit" or "quit" to leave the sqsh shell.

This is equivalent to using "set -o ignoreeof" in the bash shell.

=item interactive (boolean)

This is a variable used internally and should probably not be altered by the
user. If B<$interactive> is '0', then the prompt is not displayed, the
history is neither read nor written and some user messages are suppressed.

=item interfaces (path)

This is the full path name of the interfaces file, it defaults to
I<$SYBASE/interfaces>.

=item keytab_file (string)

Used in Kerberos and DCE user authentication security mechanisms. Corresponds
with the B<-K> startup option.

See also the discussion on Kerberos Support.

=item keyword_completion (int/string)

This variable only applies if GNU Readline support has been compiled into
I<sqsh>. B<$keyword_completion> is used to control the T-SQL keyword completion
feature in readline, and may be set using either an integer between 0 and 4, or
one of the strings I<none>, I<lower>, I<upper>, I<smart>, or I<exact>. If it is
set to either 0 or I<none>, then no keyword completion is performed (this is the
default). I<lower> or 1, causes I<sqsh> to complete the keyword in lowercase,
regardless of the case that the partially completed keyword was typed. I<upper>
or 2 forces completion to be performed in upper case, I<smart>, or 3, bases the
decision on case upon the first character of the partial keyword, and I<exact>
completes the keyword in exactly the same case as defined in the I<.sqsh_words>
(for the built-in T-SQL keywords, this will be lower case).
New in I<sqsh-2.5> is that when regular Readline completion does not match
the typed string with a keyword, then filename completion will be tried in the
current directory. For example:

    SYBASE.sa.master.1> \lcd $SYBASE/$SYBASE_ASE/scripts
    \lcd: local directory changed to: /opt/sybase/ASE-15_0/scripts
    SYBASE.sa.master.1> \run -n -i ./instm<TAB>

results in the completed filename "./instmsgs.ebf".

=item keyword_dynamic (boolean)

This variable controls the dynamic loading of keywords in the completion list
when initially logging in to the server or when the database context is changed
using the I<use database> command. When this variable is set to 'On'
(default is 'Off'), then the query that is provided through the
B<$keyword_query> variable is executed and the query result set is loaded into
the Readline completion list. This will only work for Sybase ASE and Microsoft
SQL servers.
This variable also controls dynamic loading of a column list to be auto-
completed by Readline. When an object name is followed by a dot and TAB
completion is requested, I<sqsh> dynamically creates a list of columns that
belongs to the object (table, view, procedure) and allows for Readline TAB
completion of the column or parameter names. This feature is only available if
GNU Readline support has been compiled into I<sqsh> and B<$keyword_completion>
is set to a value greater than zero. In sqsh-2.4 it is also possible to use
the object alias in the SQL buffer for auto-completion. For example when you
enter the following query in the buffer and type the TAB key twice after
the alias 'd' and the dot, a list of column names will be produced for the
'sysdatabases' table for further completion:

    SYBPROD.sa.tempdb.1> select * from master..sysdatabases d,
    SYBPROD.sa.tempdb.2>               master.dbo.sysusages u
    SYBPROD.sa.tempdb.3> where d.<TAB><TAB>
    d.audflags         d.def_remote_loc   d.durability       d.status2
    d.audflags2        d.def_remote_type  d.logptr           d.status3
    d.crdate           d.deftabaud        d.name             d.status4
    d.dbid             d.defvwaud         d.spare            d.suid
    d.defpraud         d.dumptrdate       d.status           d.version
    SYBPROD.sa.tempdb.3> where d.

This works both for the T-SQL join syntax and the ANSI inner, outer, left and
right join syntax. Note that sqsh is now able to perform cross database
auto-completion as well.

=item keyword_file (string)

If Readline support has been compiled into I<sqsh>, and I<sqsh> is being run in
interactive mode, the contents of this file are used for keyword tab completion
by Readline rather than the default set of T-SQL syntactical keywords. The
default is I<$HOME/.sqsh_words>. When B<$keyword_dynamic> is enabled this
takes precedence and overrules the list loaded from B<$keyword_file>.

=item keyword_query (string)

This variable contains the query that will be executed when a change of database
is detected or during initial login (Msg 5701). The default query is:

    select name from sysobjects order by name

But you can supply a different query that suits your needs even better. E.g.

    \set keyword_query="\\
      select name from sysobjects \\
      where type in ('U','V','P','S') \\
      union \\
      select name from sybsystemprocs..sysobjects \\
      where type='P' \\
      order by name"

This feature is controlled by the variables B<$keyword_completion> and
B<$keyword_dynamic> and is only available if GNU Readline support has been
compiled into I<sqsh>.

=item language (string)

The B<$language> variable is used while establishing a connection to the server
to specify the national language used to display system prompts and messages.
The variable will automatically track the current language setting of the
server. This may also be set via the B<-z> flag.

=item lineno (int)

This is an internal variable and should not be altered by the user. It is used
to maintain the line number that is being typed into within the current work
buffer.

=item linesep (string)

Used to configure the line separator for the horizontal display style, this
defaults to "\n\t".

=item localeconv (boolean)

A boolean variable that when set to true will result in the Operating System
locale to be used for displaying all datetime (DATETIME, SMALLDATETIME, TIME,
DATE, BIGDATETIME and BIGTIME) and the numeric, decimal, real, float and
money/smallmoney datatypes in the result set. When this variable is
set to false (i.e. the default), sqsh will use the internal C/POSIX locale
to determine how to display these datatypes. For example:

    ~$ export LANG=nl_NL.utf8
    ~$ locale -ck LC_TIME
    LC_TIME
    abday="zo;ma;di;wo;do;vr;za"
    day="zondag;maandag;dinsdag;woensdag;donderdag;vrijdag;zaterdag"
    abmon="jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec"
    mon="januari;februari;maart;april;mei;juni;juli;augustus;september;oktober;
         november;december"
    am_pm=";"
    ...

    ~$ sqsh -Llocaleconv=On -Ldatetime="%A %e %B %Y" -C"select getdate()"
     ---------------------------
            maandag 22 juli 2013

    (1 row affected)
    ~$ sqsh -Llocaleconv=Off -Ldatetime="%A %e %B %Y" -C"select getdate()"
     ---------------------------
             Monday 22 July 2013

    (1 row affected)

Also, depending on the locale, the decimal specifier in money, numeric,
decimal, float and real datatypes may be displayed as a B<",">. Note that sqsh
does not take the thousands_sep into account when displaying numerical data
values.

=item lock (string/write-only)

Defines the password to be used by the B<\lock> command. If unset or set to the
string "NULL", then the UNIX password of the user running I<sqsh> is used
instead (if possible). Note that B<$lock> will always expand to the string
"*lock*" if referenced.

=item login_timeout (int)

The number of seconds to wait when attempting to create a new connection to a
data server. Default is 0 (infinite wait). See also the B<-T> command line
option.

=item max_timeout (int)

Controls the number of times a query needs to reach its time-out value before
the session is aborted. See B<$query_timeout> and the B<-Q> command line flag.
The following messages will be displayed and sqsh exits with value 255 when the
B<$max_timeout> limit is reached.

    Query or command timeout detected, session aborted
    The client connection has detected this x time(s)
    Aborting on max_timeout limit

=item maxlen (int)

Controls the maximum amount of data that will be displayed (in any display mode)
in a single column. This setting will automatically truncate the output of
particularly large data types (such as TEXT) to the value supplied. The default
setting is 32768 bytes (32KB).

=item newline_go (boolean)

This flag is used as a horrible kludge to support an "empty" alias for the
B<\go> command, that is, the equivalent of supplying "-c ''" or just "-c" on
the command line. When on, an empty line is interpreted as a call to the B<\go>
command. This feature is not recommended but is supplied for completeness.

=item nosepline (boolean)

Suppress the separator lines with the pretty output style.

=item output_parms (boolean)

Flag used to enable or disable the display of output parameter result sets from
stored procedures. The default is to enable the display.

=item p2faxm (int)

Implements the feature Print messages to File (P2F). This variable is used in
conjuction with B<$p2fname> and specifies a threshold on the number of messages
per batch that will be send to the terminal window. If the threshold is
exceeded, the remaining messages will be send to the specified file. The default
value is NULL which disables the P2F feature. Also an assigned value of 0
disables this feature. This feature will only work in interactive mode and is to
prevent an accidental flood of server messages scrolling over the screen.
For example:

    SYBASE.sa.tempdb.1> \set p2fname=/tmp/sqsh_p2f.out
    SYBASE.sa.tempdb.1> \set p2faxm=10
    SYBASE.sa.tempdb.1> create clustered index keys_cx on keys(id)
    SYBASE.sa.tempdb.2>   with ignore_dup_row
    SYBASE.sa.tempdb.2> go
    Warning: deleted duplicate row.  Primary key is '0'
    Warning: deleted duplicate row.  Primary key is '1'
    Warning: deleted duplicate row.  Primary key is '2'
    Warning: deleted duplicate row.  Primary key is '3'
    Warning: deleted duplicate row.  Primary key is '4'
    Warning: deleted duplicate row.  Primary key is '5'
    Warning: deleted duplicate row.  Primary key is '6'
    Warning: deleted duplicate row.  Primary key is '7'
    Warning: deleted duplicate row.  Primary key is '8'
    Warning: deleted duplicate row.  Primary key is '9'
    Warning: Number of printed server messages exceeds p2faxm=10 limit for current batch.
             Remaining server messages will be printed to file: /tmp/sqsh_p2f.out

=item p2fname (path)

Contains the filename for the Print messages to File (P2F) feature described
above. Default is NULL which disables the P2F feature. If a file is specified
that already exists, then new data will be appended.

=item packet_size (int)

Defines the size of the TDS packets used to communicate with SQL. Changing the
value of the variable will not affect the current connection but will take
effect upon the next B<\reconnect> command. Specifying a value of NULL indicates
that the default packet size is desired.

=item password (string/write-only)

This is the user's current password. A NULL password may be assigned using an
explicit "NULL" string. For security reasons, when referenced the B<$password>
variable will always expand to the string "*password*".

=item password_retry (boolean)

When the variable is on and sqsh started in interactive mode, sqsh will ask for
the password again if login failed. When set to 'off', sqsh will terminate
immediately. Default is on.

=item principal (string)

Used in Kerberos user authentication as the Server principal name when it
differs from the B<$DSQUERY> name. Corresponds with the B<-R> startup option.

See also the discussion on Kerberos Support.

=item prompt (string)

This variable is used by I<sqsh> to build your current prompt. Any variables
contained within B<$prompt> are expanded each time the prompt is displayed. The
default value for this is '${lineno}> '. See also the discussion on color
support below.

=item prompt2 (string)

This contents of this prompt are expanded and displayed during interactive use
when I<sqsh> requires additional input, such as during a line continuation. The
default value is '--> '.

=item query_timeout (int)

Number of seconds to wait for a query to return data. If the timeout is reached
the query is aborted (canceled). See also B<$max_timeout> and the B<-Q> command
line option.

=item rcfile (path)

Contains a colon (:) delimited list of sqsh resource (sqshrc) files. The default
setting is /usr/local/etc/sqshrc (unless overridden by the --prefix option when
sqsh was compiled) followed by I<$HOME/.sqshrc>).

=item readline_histignore (string)

If readline support has been compiled into I<sqsh>, you can control which
commands or statements on the command line will be filtered from the readline
history file specified by B<$readline_history>. It is a colon (:) separated
string or a string containing a regular expression that is identified by "RE:".
For example:

    \set readline_histignore='go:lo:mo:exit:quit:vi:!!:GO'
    or
    \set readline_histignore='"RE:^[glm]o$|^cd |^exit$|^quit$|^vi$"'

The regular expression in the second example starts with I<RE:> and it is
recommended to supply the string between quotes and double quotes to prevent
expansion and display problems due to the pipe characters. The regular
expression is evaluated case insensitive.

=item readline_history (string)

If readline support has been compiled into I<sqsh>, the contents of the readline
line-by-line history will be written to the file specified by the
B<$readline_history> variable. The default is I<$HOME/.sqsh_readline>.

=item readline_histsize (int)

If readline support has been compiled into I<sqsh>, the value of
B<$readline_histsize> specifies the number of lines that are saved in the
readline line-by-line history. Setting this to a value of 0 causes every line to
be saved. The default value is 100.

=item real (float-format)

Defines the display format (the precision and scale) for all real values
displayed by sqsh. The default is '18.6'. Note that values exceeding the
defined precision are not truncated, so setting this value too low may cause
columns in a result set to be miss-aligned.

=item repeat_batch (boolean)

When set to B<On> or B<True>, a B<\go> executed with an empty B<SQL Buffer> will
cause the previous batch to be re-executed.

=item secmech (string)

The name of the security mechanism used for user authentication. For instance
csfkrb5. When using the B<-Z> option, the name of the security mechanism can be
specified, or when no secmech is supplied, the OpenClient will use the default,
i.e. the first security entry from the I<$SYBASE/$SYBASE_OCS/config/libtcl.cfg>
file. Note that the name used must match the name of the corresponding secmech
entry in the I<objectid.dat> file. When the connection to the server is setup,
the actual secmech name will be stored in the B<$secmech> variable.

=item secure_options (string)

Describe the security options used for user authentication security mechanisms.
Corresponds to the B<-V> startup option.

See also the paragraph on Kerberos Support.

=item script (string)

If I<sqsh> is run using the B<-i> flag, then this variable contains the name of
the script being executed.

=item semicolon_cmd (string)

When B<$semicolon_hack> (see below) is enabled, the contents of this variable is
executed when a semicolon is encountered in the B<SQL Buffer>. This variable
defaults to the string 'B<\go>'.

=item semicolon_hack (boolean)

Toggles on the ability to use a ';' as an in-line command terminator. This
feature is not recommended and is only in here because enough users complained.
See section B<COMMANDS, In-Line \go>.

=item semicolon_hack2 (boolean)

Toggles on the ability to use multiple ';' as an in-line command terminator.
See section B<COMMANDS, In-Line \go>.

=item session (string)

The location of the session file that will be processed just before connecting
to a server. The variable will be expanded so it may contain environment
variables.

=item SHELL (string)

The name of the shell to be used to execute pipes and to be used by the
B<\shell> command (default '/bin/sh').

=item statistics (boolean)

Setting B<$statistics> to 1 causes timing statistics to be displayed upon the
successful execution of every batch of SQL. This variable may also be set via
the B<-p> command line flag, or by supplying B<-p> to the B<\go> command.
B<$statistics> defaults to 0.

=item style (string)

Selects result set display style. Currently eight styles are supported. The
B<horiz> (which may also be defined as B<hor> or B<horizontal>), closely
resembles the output of isql, with the traditional columnar output.

The B<vert> (or B<vertical>) style rotates the output, so that every line is
represented by a column name followed by a column value. This is nice for
looking at particularly wide output.

The B<bcp> style displays results in a format amenable to bcp'ing the result
set back into another table. That is, every column value is separated by
B<$bcp_colsep> with the final column separated by B<$bcp_rowsep> followed by a
newline (\n). If B<$bcp_colsep> or B<$bcp_rowsep> are not defined then '|'
is used as the default separator. Note that this output does not work well with
COMPUTE columns, and uses the default conversion methods for all data types
(that is, B<datetime> columns may truncate the millisecond).

The B<csv> display style outputs all result sets in the form of a comma
separated construct. This mode is ideal to import result sets into spreadsheet
programs, for instance.

The B<html> display style outputs all result sets in the form of an HTML <TABLE>
construct. This mode is ideal for the use of sqsh as a CGI application.

The B<meta> display style outputs only the meta-data information associated with
the result and discards the actual row results. This mode is useful for
debugging the result sets generated from a full passthru Open Server gateway, or
for those interested in what is really coming back from the server.

The B<pretty> display style generates a fluffy table-like output using regular
ASCII characters for borders. This mode does not perform any explicit column
wrapping, like the B<horiz> display mode. However, the B<$colwidth> variable can
be used to control the maximum width of a given column on the screen. If the
column exceeds B<$colwidth> characters wide, it is wrapped in a relatively
visually appealing manner. Note that B<$colwidth> may be exceeded if there is
enough screen width to hold the columns without wrapping.

The B<none> display style suppresses all results from being displayed (however
it does actually retrieve result information from the SQL Server). This is
particularly useful when used with the B<-p> flag (or the B<$statistics>
variable) for gathering accurate performance statistics.

=item term_title (string)

When this variable is set, sqsh will reset the name of the current terminal
(xterm) window it is running in. Works for xterm, rxvt, putty and MS-Windows CMD
windows. The name consists of the sqsh startup name appended with the expanded
value of the B<$term_title> variable.

=item thresh_display (int)

Sets the minimum SQL Server error severity that will display a message to the
user, the default is 0 and valid ranges are between 0 and 22, inclusive.

=item thresh_exit (int)

Defines the maximum number of errors of severity level B<$thresh_fail> that may
be encountered before sqsh aborts. This is useful primarily for non-interactive
scripts, but is allowed on an interactive session. Setting B<$thresh_exit> to a
value of 0 disables this feature. See section B<EXIT STATUS> for details.

=item thresh_fail (int)

Sets the minimum SQL Server severity level that is to be considered a failed
batch. The minimum for this value is 0 (meaning any error that is not an
information message), and the maximum is 22. Whenever B<$thresh_fail> is
crossed, the variable B<$batch_failcount> is incremented by 1. See section
B<EXIT STATUS> for details.

=item time (date-spec)

This variable may be set with a time format (see the man page for B<date(1)>),
and the variable expands to the current time in the supplied format. The default
format for this variable is %H:%M:%S (e.g. 14:32:58).

=item timefmt (date-spec)

This variable may be set with a time format similar to B<$time> and
is used to control the display format of all SQL Server TIME and BIGTIME
columns. (Similar to B<$datetime>.) Note that only 63 bytes are available in
total for the expanded string and the remainder will be truncated.

=item tmp_dir (path)

This contains the directory to which temporary files used internally by I<sqsh>
are to be written. These files are generated either during buffer editing (the
B<\buf-edit> command), or to maintain output defer files for background jobs.
The default value for this variable is /tmp.

=item usedbcheck (boolean)

When this variable is set to 'On' (default is 'Off') and a database name is
provided with the -D parameter and sqsh is run in batch mode, i.e.
non-interactive, then a check will be performed if the specified database is
accessible, otherwise sqsh is aborted with exit code 254. Also the following
error message will be shown:

B<sqsh: ERROR: Unable to use database '...' in batch mode>

This is to prevent that a SQL script is inadvertently executed in the wrong
database, usually the default database of the login (master for example).

=item username (string)

The name of the user currently connected to the database.

=item version (none)

This read-only variable contains the current version number. (sqsh-2.1.7)

=item width (int)

The current width of the SQL output.

=item xgeom (string/int)

If X11 support is compiled into I<sqsh>, this value is used to configure the
default window size (in characters) of the X display. This variable must be of
the format B<WxH> or just B<W>, where B<W> is the width of the window and B<H>
is the height of the window. If the height of the window is not supplied, then
25 lines is assumed. If B<$xgeom> is not set, then B<$width> is used as the
default width and the height is assumed to be 25. If neither is set, then 80x25
is assumed.

=item xwin_title (string)

If X11 support is compiled into I<sqsh> and an X11 result windows is requested
by using the B<-x> parameter of the I<\go> or I<\rpc> commands, then this
variable will be used to set the title name of the window. Can be temporarily
overridden with the B<-T> option of the I<\go> and I<\rpc> commands.

=back

=head2 Script Execution

As with most shells, I<sqsh> allows a file containing SQL and script commands to
be executed directly via the magical UNIX B<#!> convention. On most UNIX
platforms, when the operating system encounters the bytes B<#!> as the first two
bytes of an executable file it will automatically pipe the file through the
interpreter specified immediately after the B<#!>. For example, to create an
executable I<sqsh> script to run B<sp_who>, you simply need to create a file
like so:

    #!/usr/local/bin/sqsh -i
    sp_who
    go

Thus, if your B<sp_who> script is executed directly, it will automatically
launch "B</usr/local/bin/sqsh -i sp_who>" for you.

And, to make things even more flexible, I<sqsh> supports positional parameters,
similar to most shells, of the form B<${n}> which will expand to the B<n>th
argument to your I<sqsh> script. For example:

    #!/usr/local/bin/sqsh -i
    sp_who ${1}
    go

will cause the B<sp_who> stored procedure to be executed with an argument of the
first command line parameter supplied to the B<sp_who> shell script.

Note that positional parameters I<must> be contained between braces to avoid
conflicts with the T-SQL B<money> data type (without the braces, the variable
will not be expanded).

=head1 EXIT STATUS

One of the major complaints of I<isql> is that it provides no facility to detect
when an error condition occurred while it is performing processing. I<sqsh>
provides a rather complex, but flexible mechanism for returning meaningful
information concerning its reason for exit in the form of an exit status (see
B<exit(3)>).

When I<sqsh> begins execution two handlers are associated with the current
connection to the database, one is a message handler which is responsible for
displaying the text of any SQL Server messages or errors, and the other is an
error handler, which is responsible for determining what to do with an error
condition (bear with me, these are only loose descriptions). And, associated
with each message and error condition is a severity level, between 0 and 22
(informational message to fatal condition).

Associated with these two message handlers are several variables that are used
to either control their behavior, or are used as indicators by the message
handler:

=over 4

=item $thresh_display

This variable is used by the message handler to determine the minimum error
severity which will cause a message to be displayed. By default this is 0, which
will display all messages (with a couple of exceptions). Setting this to 1, for
example, would suppress informational messages such as the output of
B<set showplan on>.

=item $thresh_fail

This variable is used by the error handler to determine which error severity is
considered by I<sqsh> to be a failure. Normally, this defaults to 11 which
indicates that any error, other than informational messages, is a failure. The
next variable will explain the importance of this value.

=item $batch_failcount

This variable should be considered read-only, and contains the total number of
times that batches have caused an error of severity B<$thresh_fail> or more. The
only value that is valid to explicitly set this value to, is "" (the empty
string), which will reset this value to 0, any other value may have
unpredictable results.

=item $thresh_exit

This variable is used to determine the limit at which B<$batch_failcount> will
cause I<sqsh> to exit. If B<$thresh_exit> is 0, then this feature is disabled.
In other words, if B<$batch_failcount = $thresh_exit> and B<$thresh_exit> is
greater than 0, then I<sqsh> will exit, returning B<$batch_failcount> as an exit
status.

Note that, unless B<$exit_failcount> is set to 1, I<sqsh> will exit with 0 if
the total number of failures does not reach B<$thresh_exit>.

=item $exit_failcount

This variable is used only when I<sqsh> would normally exit with a success
status (0), this causes it to instead exit with a value of B<$batch_failcount>
(which may, itself, be 0).

=item $exit_value

When this variable is non-zero, it will override the B<$exit_failcount> and
B<$batch_failcount> values and just exit the value of B<$exit_value>. This
variable may be explicitly set using the B<\set> command, or implicitly using
B<\exit x> and its aliased counterparts.

=back

To recap, here is a list of error codes that may be returned by I<sqsh> upon
exit, and the reason that they could be returned:

=over 4

=item - 0 -

No error has been encountered.

=item - 1...253 -

Between 1 and 253 batches have failed (if you run more than 253 batches, the
exit status of sqsh is undetermined...I may fix this in the future).

=item - 254 -

An explicit B<\abort> was called, or a SIGINT (^C) was issued during a
non-interactive session.

=item - 255 -

A general error condition has occurred, such as a bad command line argument to
sqsh, memory allocation failure, file access error, etc.

=back

The following sections provide detailed examples of combinations of variable
settings and the results produced upon exit with certain failure conditions. It
is assumed the B<$exit_value> variable contains 0 in the next examples.

=over 4

=item thresh_display=0, thresh_fail=0, thresh_exit=1

With this combination, all error messages will be displayed as they happen, and
every error will be considered an failure condition. Upon reaching the first
error, I<sqsh> will abort with an exit status of 1, or the total number of
failures (the B<$batch_failcount> variable). However, if nothing goes wrong
during the whole process, a zero is returned.

=item thresh_display=0, thresh_fail=0, thresh_exit=3

This combination will cause all error conditions to be displayed and all of them
to be considered a failure condition. Upon reaching three total failed batches,
I<sqsh> will exit with a status of 3. However if 0, 1, or 2 batches fail, then 0
is returned.

=item thresh_display=22, thresh_fail=0, thresh_exit=3

This behaves the same as the previous example, with the exception that all error
messages will be suppressed from being displayed. This is particularly useful if
you just care about the exit value more than the actual error.

=item thresh_display=0, thresh_fail=2, thresh_exit=1

This will cause the first error of severity 2 or higher to be displayed and
cause I<sqsh> to exit with a failure condition of 1.

=item thresh_display=0, thresh_fail=0, thresh_exit=3, exit_failcount=1

This is identical to the second example, above, however B<sqsh> will return the
total number of batches that failed even if B<$batch_failcount> does not reach
3.

=item exit_value=nonzero

This will override all conditions explained above and just exit the specific
value.

=back

=head1 MISCELLANEOUS

=head2 Colorizing sqsh prompts

Color codes are presented as a string like {1;2;3}. If sqsh encounters a {
(curly brace) in the prompt string it will assume a color-code is supplied and
will act as such. No checks will be performed on the validity of the color-code
definition itself. The color definition consists of three values separated by a
semicolon.

The first code defines the Color Attribute Code with possible values: 0=none
1=bold
The second value defines the Text Color Code: 30=black 31=red 32=green 33=yellow
34=blue 35=magenta 36=cyan 37=white
The third value defines the Background Color Code: 40=black 41=red 42=green
43=yellow 44=blue 45=magenta 46=cyan 47=white The last color-code for the
background may be omitted.
Not all color and attribute combinations will present good results, depending on
your terminal type and color scheme. But the following values work OK on a xterm
with a creamy white background color in Linux as well as rxvt in Cygwin with a
black background window:

    Prompt: Blue text in white background "{0;34;47}"
    Command text: Yellow text             "{0;33}"
    Command text: Default text color      "{0}"

sqsh will translate the color-codes to an actual color-code string that is
presented to readline: for example "\001\033[0;36;47m\002". Note that if you
want to use curly brackets in your prompt, you have to escape colorization by
specifying a double brace, like {{...}}. For example:

    \set prompt_color='{0;34;47}'
    \set text_color='{0}'
    \set prompt='$prompt_color{{$DSQUERY.$username.$database.$lineno}}>$text_color '

Color support is automatically available if sqsh is compiled and linked with
readline support enabled.

=head1 FILES

Defaults: $HOME/.sqshrc, $HOME/.sqsh_session, $HOME/.sqsh_history,
$HOME/.sqsh_readline, $HOME/.sqsh_words, $tmp_dir/sqsh-dfr.*,
$tmp_dir/sqsh-edit.* These can all be modified using the internal I<sqsh>
variables described above.


=head1 BUGS

The addition of flow-of-control expressions has extended sqsh I<way> beyond the
scope of its original design, and it is quite obvious from using the features
they are hacked in and are rather clunky (although still quite usable). As a
result, the processing of these expressions is rather slow (when compared to
bourne shell), and the error reporting doesn't lend itself to debugging large
scripts. The development of 1000+ line scripts is discouraged.

The combination of backgrounding and pipes does not work properly right now.
What happens is, when a background job is run that incorporates a pipe-line,
I<sqsh> will suspend until the job is complete, which is obviously not what
you desire. To test this, try the following:

    1> select * from syscolumns
    2> go | grep id &

You will find that you do not get your prompt back until the job completes.
The same is more ore less true for pipes being used with the \do command:

    1> select name from master..sysdatabases
    2> \do | grep syb

This will not give you a prompt anymore until the block completes with \done.
So you are typing the do block itself in the dark and there is no possiblity
to edit a do block or retrieve a do block from the history list.
So you may have typed (hopefully without syntax errors):

    \echo #1
    \done

After \done is processed the results will be displayed followed by a fresh
prompt.

Please report any other bugs to http://sourceforge.net/p/sqsh/bugs
Feature requests may be posted to http://sourceforge.net/p/sqsh/feature-requests