sqsh(1)

📦 version 2.5.16.1, in ubuntu jammy

okay bestie, sit down, hydrate, and let me tell you about sqsh(1) from the sqsh-2.5 era 💖 this is the interactive database shell and she is ICONIC 🎀

✨ NAME ✨

sqsh — Interactive database shell (version 2.5) 🦋

🌸 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] ]

💜 DESCRIPTION 💜

So Sqsh — pronounced "skwish", yes really, it's giving cute little noise — is short for SQshelL (and that is pronounced "s-q-shell", we contain multitudes ✨). She exists as a glow-up replacement for the venerable isql program that Sybase ships. Why does she exist? Because people spent years being frustrated trying to do real work with a program that was never meant to perform real work 😭 periodt.

Sqsh is much more than a nice prompt, okay?? She's out here trying to give you basically everything a good shell gives you:

variables
aliasing
redirection
pipes
back-grounding
job control
history
command substitution
dynamic configuration

...and also, as a lil by-product of the whole design, she is remarkably easy to extend and add functionality to 👑 no cap.

💅 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. iykyk 🫶

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. Heads up bestie 🚨 — for options that are allowed to be supplied multiple times, like -c, supplying them both in a variable and on the command line is the same as supplying them multiple times on the command line. Same energy, same result 💀

-a count — Sets the max count of failures (as determined by the $thresh_fail variable) that can happen before sqsh just gives up and aborts. Setting it to 0 means sqsh should not exit on errors. This defaults to 0 and can also be set with the $thresh_exit variable. See the EXIT STATUS section for the deets 🔍
-A packetsize — Sets the size of the network TDS packets used to talk to the SQL server. This value must be between 512 and 8192, and has to be a multiple of 512. Go check your SQL Server configuration to figure out which packet sizes are supported. You can also set this at run-time using the $packet_size variable 📦
-b — Suppresses the banner message on startup. Lowkey unnecessary when stdout has been redirected to a file. Can also be set via the $banner variable 🤫
-B — Turns off all buffering of stdin, stdout, and stderr. This is the feature that lets sqsh run from an interactive control script like chat and expect. slay 🦋
-c [cmdend] — Internally sqsh gives you the command \go to send a batch of SQL to the database, and provides a single alias, go, for that command. Every time cmdend is supplied, a new alias for \go is established 👑
-C sql — Causes the sql command to be executed by sqsh, kind of like the same behavior the -i flag exhibits. This sql statement may not contain double quotes (this limitation may be lifted in a future release of sqsh) 😭
-d severity — Sets the minimum SQL Server error severity that will actually get shown to you. The default is 0, and valid ranges are from 0 to 22. Can also be set with the $thresh_display variable. See the EXIT STATUS section 🚦
-D database — Makes sqsh attempt to start with your database context set to database instead of your default database (which is usually master). Can also be set with the $database variable 🗂️
-e — Includes each command issued to sqsh in the output. Can also be set via the $echo variable — and no, that variable is unrelated to the \echo command, don't get it twisted 🙈
-E editor — Sets the default editor to editor. You can also set this using the UNIX environment variable $EDITOR set to the name of the editor you want 📝
-f severity — Sets the minimum severity level that sqsh considers a failure. Same thing as setting the $thresh_fail variable. See the EXIT STATUS section for details 💀
-G tds_version — Sets 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 value you specify gets assigned to the variable $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 $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 🌐

-h — Turns off column headers and the trailing (# rows affected) from batch output ✂️
-H hostname — Sets the client hostname as reported in sysprocesses. Can also be set via the $hostname variable 🏠
-i filename — Read all input from filename instead of from stdin 📖
-I interfaces — When a connection is established to the database, the interfaces file is used to turn the value of $DSQUERY into the hostname and port the connection will be made to. By default this is located in $SYBASE/interfaces. This flag lets you override that default 🔀
-J charset — Specifies the character set used on the client side to communicate with SQL Server. Can also be set using the $charset environment variable 🔤
-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 can also be set via the $keyword_file variable, which defaults to $HOME/.sqsh_words ⌨️
-K keytab — Kerberos support ✨ Specify the keytab file name for DCE.

See the Kerberos Support section below for details 🎫

-l debug_flags — If sqsh has been compiled with -DDEBUG, this option can be used to turn debugging options on and off. See the $debug variable, below 🐛
-L var=value — Sets the value of $var to value. This can be used to set the value of any sqsh variable, even if an explicit command line variable is supplied for setting that variable. The -L flag can also be used to set the value of non-configuration variables too 🎛️
-m style — Changes the current display style to style. Currently supported styles are horiz, vert, bcp, csv, html, meta, pretty and none. The current display style can also be set using the $style variable, or via the -m flag to the \go command 🎨
-n on|off — Enables chained transaction mode on connect (if set to on). Chained transaction mode is also known as "AutoCommit off" mode 🔗
-N appname — Sets the application name sqsh uses to identify itself to the SQL server. The application name can be retrieved from the server using the column program_name of the master.dbo.sysprocesses table. Defaults to sqsh-2.1.7 🏷️
-o filename — Redirects all output to filename instead of stdout 📤
-p — Displays performance statistics upon completion of every SQL batch. Can also be turned on via the $statistics variable, or by supplying the -p flag to the \go command 📊
-P [password | -] — The Sybase password for username required to connect to server (default, NULL). The password can also be set via $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 -P should not constitute a security hole. Alternatively, you can store your default password in the .sqshrc file, which is not readable by anyone other than yourself. (chmod 600 .sqshrc) 🛡️

-Q value — Query timeout value (equivalent to isql's -t option). If set, it will timeout a long running query. Maps to the $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 $max_timeout variable, which controls the number of times a timeout may occur on the current connection before the session is aborted 💀

-r [sqshrc] — Specifies an alternate .sqshrc file to be processed, rather than the default. If no sqshrc is supplied following -r, then no initialization files are processed. This flag must be the first argument supplied on the command line — all other instances will be ignored 🚨
-R server_principal — Kerberos support: Specifies a 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 ✨

-s colsep — Causes the string colsep to be used to delimit SQL column output columns, this defaults to " " 🔡
-S server | host:port[:filter] — the name (or address!) of the Sybase server you're connecting to. the default is the external environment variable $DSQUERY. if neither -S nor $DSQUERY is set, it falls back to "SYBASE". you can also set this through the internal variable $DSQUERY. the servername must exist in the interfaces or sql.ini file, no cap. as an alternative you can specify the target server as host:port[:filter], where host can also be an IP address. note that filter may be defined in $SYBASE/$SYBASE_OCS/config/libtcl[64].cfg. for example:

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

-t [filter] — turns on filtering of command batches through an external program, filter, before they get sent to the SQL Server. if you don't hand it a filter, then $filter_prog is used (the default is m4 -). this value may also be set via the $filter and $filter_prog variables.
-T value — sets the login timeout (it's giving isql's -l flag energy). if set, it's the number of seconds sqsh will wait before timing out a login request. maps to the $login_timeout variable.
-U username — the Sybase username you connect to the database as. defaults to the username of whoever's running sqsh. also settable via the $username variable.
-v — displays the version number, $version, and exits. that's it, that's the whole flag 💅
-V [bcdimoqru] — Kerberos support: specify the security options to use. peek the Kerberos Support section below for the details.
-w width — the maximum output width of a displayed result set. defaults to your screen width in interactive mode.
-X — starts up the login connection to the server with client-side password encryption (if it's supported). if either SQL Server doesn't recognize this option, or the version of CT-Lib that sqsh was compiled with doesn't support it, then it just gets ignored. this option may also be set using the $encryption environment variable.
-y directory — specifies a SYBASE directory to use other than the value of $SYBASE, so sqsh can find the interfaces file.
-z language — specifies an alternate language to display sqsh prompts and messages. without the -z flag, the server's default language gets used. also settable using the $language variable.
-Z [secmech|default|none] — Kerberos support: specify the security mechanism to use. see the Kerberos Support section below for the deets.
args... — if sqsh is run with the -i flag specifying an input file to be processed (instead of kicking off an interactive session), you can supply arguments on the command line to be passed to that input file. these arguments may be accessed using the variables ${0}, ${1}, ... (check the Variables section below for more).

🌸 Initialization 🌸

okay so when it starts up, sqsh initializes all internal environment variables, commands, and aliases to their default values. then it goes looking in the system-wide configuration file, /etc/sqshrc, followed by a local configuration file $HOME/.sqshrc (and yes, you can override that via the SQSHRC external environment variable). if this file is found, it gets executed just like a script would be using the -i flag. 🫶

the .sqshrc file can contain literally anything you could normally type at the prompt. but heads up bestie — at the time this file is read, sqsh hasn't established a connection to the database yet. most commands that do database activity, like \go, will attempt to establish a database connection when they're executed (and it may prompt you for a password if it needs one). also, if you need database activity inside this startup file, the \connect command (see COMMANDS, below) may be executed.

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

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

💬 Command line 💬

when a line is first read by sqsh, the first word is separated from the line. that word is then expanded of all variables (see Variable Substitution, below), followed by command expansion (see Command Substitution, below). the first word of the resulting string is then analyzed to see if it's either a valid sqsh command or alias.

the sqsh command line follows a lot of the same rules as Bourne shell — file redirection, pipelining, command substitution, and backgrounding all work via the same syntax. iconic.

🎀 Comments 🎀

any line beginning with a # followed by a non-alphanumeric character (any character other than 0-9, a-z, A-Z, and _) causes the entire line to be ignored. but 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 # is alphanumeric.

🌈 Quoting 🌈

quoting is what you use to prevent the interpretation of special keywords or characters by sqsh — stuff like white-space, variable expansion, or command substitution. there are three types of quoting: escape, single-quotes, and double-quotes. slay.

enclosing characters in single quotes preserves the literal interpretation of each character inside the quotes. a single quote may not appear within single quotes, even when it's 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 too. 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 that case the new-line is discarded and the continued line is automatically appended to the previous line, like:

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

enclosing characters in double quotes preserves the literal meaning of all characters inside them, with the exception of $, ', and \\. 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

✨ Expansion ✨

after a line of input has been read, sqsh attempts to expand the line of any aliases (see Aliasing, below), and 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: variable substitution, followed by command substitution respectively. finally, if a tilde was provided on the command line, then tilde expansion will be performed and the ~ will be substituted with the corresponding HOME directory name. 🦋

1> \echo ~sybase/err.log

may result in /home/sybase/err.log for example.

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

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

💜 Variable Substitution 💜

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

1> \set name=value

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

value may contain anything, but if it's going to include white-space, then it has to be quoted (see Words & Quoting, above). note that to prevent the expansion of a variable you 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 name of the variable until another type of character is reached (either a special character, or a white-space).
${variable} — the braces are only required when 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 \echo command receives three arguments, "1", "2", and "3", even though it looks like only one argument was passed to it. this behavior is consistent with most shells (such as csh, bourne shell, etc.). periodt.

🧚 Command Substitution 🧚

sqsh supports a second form of expansion called command substitution. this form of expansion substitutes a command on the command line with the output of the external UNIX command. you do this by placing the command line to be executed in back-quotes (`). for example:

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

in this example, the external program /sybase/bin/getpwd is executed with the current contents of the $DSQUERY environment variable, and the entire expression is then replaced with the output of getpwd (ilikepickles) prior to executing the \set command. by default, the output of the substituted command is first broken into words according to the contents of the $ifs variable before being assembled back together into the command line. so by overriding the contents of $ifs you can 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 /etc/passwd, into fields. 🔥

Input/Output Redirection 🎀

okay so bestie, just like your standard Bourne shell (and honestly most other shells too), a command's input and output can be redirected using a special notation that the shell interprets for you. ✨

The following can pop up anywhere on the command line, but here's the tea: only redirection specified before a pipe (|) actually has any effect on the behavior of internal sqsh commands (go read Pipes, below, for the whole story).

<word — use the file word as the standard input for the command. Ngl, very few sqsh commands actually read anything from stdin, so this will usually do absolutely nothing (peep the \loop command if you're curious).
[n]>word — hook the output of file descriptor n (stdout by default) up to the file word. If that file doesn't exist yet, it gets created; if it does exist, it gets truncated to zero length. 💀
[n]>>word — append the output of file descriptor n (stdout by default) onto the file word, creating it if it isn't there yet.
[m]>&n — redirect the output of file descriptor m (stdout by default) to the same output as file descriptor n.

The order in which redirections are specified on the command line is significant, bestie — they get evaluated left-to-right. This is not a drill. 🚨

For example:

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

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

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 gets sent to stdout, while what would have gone to stdout is redirected to /tmp/output (in this case /tmp/output will be empty). the way that one character move changes everything 😭

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

Pipes 🌈

A 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. But the first command in the pipeline must be a sqsh command, and all the other commands must be external (or UNIX) programs. Any sqsh command may be run through a pipeline, although for many of them (like the \set command) it doesn't really make any sense to do this. 🙈

The following is an example of a pipeline:

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

This command causes the result set generated by the \go command to be sent to the 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). iconic behavior fr fr 👑

There are several peculiarities in the way in which sqsh deals with 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 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. 🤷‍♀️

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:

Deferred

In this mode sqsh redirects all output of the background job to a temporary file (located in the directory $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 \show command. 🦋

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 $defer_bg variable (which defaults to '1', or 'On'). Typically the only reason to not use deferred mode is to prevent large result sets from filling up your file system. 💀

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 xxxx) then the child process calls the \go. \go command then establishes a new connection to the database (using the current values of the $DSQUERY, $username, $password variables) and executes the shown query. While the job is executing the commands \jobs, \wait and \kill may be used to monitor or alter a currently running job (see section 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 \jobs command) until a \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: You have running jobs or pending job output. You have to process all the jobs first before being able to exit sqsh. 😤 no escape, bestie

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

Buffers 🎀

In normal 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'). two. that's it. two.

Sqsh maintains several distinct sets of buffers:

Work Buffer: this buffer corresponds directly to the isql work buffer. It is the buffer into which you enter the current batch prior to sending it to the database. 💅
History Buffer: this is actually a chain of 0 or more buffers (configurable by the $histsize variable) of the last $histsize batches that have been run. This buffer is only maintained when sqsh is run in interactive mode — that is, batches executed using the -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 $histsave is True (see section SPECIAL VARIABLES) and sqsh is in interactive mode, then the current history buffer is written to $HOME/.sqsh_history when you exit. This file is then read back into sqsh the next time it is started in interactive mode. 🫶
Named Buffers: at any time during a session the Work Buffer, or any of the History Buffers may be copied into a named buffer using the \buf-copy command (see section COMMANDS, below). These buffers are lost when you exit (however you may use the \buf-save command to save named buffers to a file). 💾

Buffer Short-Hand

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

!. — the current work buffer.
!! — the last command executed (note, this is not available in non-interactive mode as it does not maintain a history).
!+ — the next available history entry. This is a write-only buffer, so typically only applies to such commands as \buf-copy.
!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.
!buf_name — just for consistency this is supplied as a reference to named buffer buf_name, however buf_name without the leading '!' is also considered correct.
buf_name — refers to the named buffer buf_name.

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 \go command), during which time they are expanded and replaced within the buffer. This behavior may be altered via the $expand variable. (see 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 where clauses, or long column names. lowkey a lifesaver 💜

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

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 $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

okay so this one causes the strings echo syscolumns to be replaced by the word syscolumns prior to executing the command. and bestie, heads up: the contents of that substituted command are only executed at the time of the \go command — not when the line of SQL is input. so don't come at it expecting early fireworks 💅

🌈 FLOW-OF-CONTROL 🌈

new with version 2.0 of sqsh — we got the ability to perform basic flow-of-control and functions using the \if, \while, \do, and \func commands. like, actual logic?? in my SQL shell?? it's giving main character energy and honestly we're obsessed. 👑

🧱 Blocks & SQL Buffers 🧱

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

\if expression
    block
\fi

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

Each block has its own SQL buffer for the duration that the 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 \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 \fi terminator. 🎀

🌸 Expressions 🌸

All flow-of-control statements in sqsh take an expression to determine which block of code to execute. just like UNIX's Bourne Shell, this 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 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. 🪄

🚫 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

💖 `\if` statement 💖

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

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

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

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

🔁 `\while` statement 🔁

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

\while expr
    block
\done

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

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

🔂 `\for` statement 🔂

The \for command executes a block of sqsh code for each word supplied:

\for var in word ...
    block
\done

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

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

🔄 `\do` command 🔄

The \do command is kind of a cross between a statement and a command. 💫

It is a form of \go (see below for details on the \go command) in which a block of sqsh code may be executed for each row of data returned from the query. When the 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. 👑

⚙️ Command line options ⚙️

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

-D database — Establishes the connection to the database as the supplied database.
-U username — Establishes the connection to the server as the supplied username.
-P password — Establishes the connection to the server using the supplied password (which is hopefully a valid password for the supplied username).
-S server | host:port[:filter] — Establishes the connection to the supplied server.
-n — Do not create a connection for use by the \do loop. This flag is mutually exclusive with the above flags. With this flag enabled, attempts to perform database commands within the block will generate a flurry of CT-Library errors. 💀

🗂️ 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 \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. 🚫

🛑 Aborting 🛑

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

🧚 `\func` command 🧚

The \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 stats that expects a single argument, either "on" or "off". Using this argument, 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 stats.

⚙️ Command line options ⚙️

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

-x — Causes the function to be exported as a sqsh command. That is, the function may be invoked directly without requiring the \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. ⚠️

🎀 Function variables 🎀

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

$# — Expands to the number of arguments supplied to the function or script when invoked.
$* — Expands to the complete list of arguments supplied to the function or script when invoked.
${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 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. 💅
$? — After the invocation of a function, this will contain its return value (see below).

💫 Return value 💫

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

Like so:

\return N

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

If \return isn't explicitly called, the default return value is just whatever $? is sitting at right now — and heads up, $? gets set to 0 the moment you enter the function. So if you run any SQL statements inside that function, the default return value of $? becomes the last error code that popped up while the SQL statement was being processed. That's it, that's the whole vibe check. ✨

🎫 Kerberos Support 🎫

Okay so starting with version 2.1.6, sqsh serves up the same command line options as isql does for handling Kerberos network authentication. Iconic of her honestly. 👑

Back in version 2.1.5, Kerberos support was experimental and showed up through the -K and -R options. -K was literally just a switch to flip Kerberos on. Then in sqsh 2.1.6, a more advanced implementation of network authentication rolled in — still experimental though, bestie, don't come for me. 🔥

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

-K keytab_file — the keytab_file name for DCE.
-R server_principal — the server principal name, for when the servername specified in interfaces is different from the real server name.
-V [bcdimoqru] — specify which security options to use with the security mechanism. Each character stands for a specific security service.
-Z [secmech|default|none] — request a security mechanism defined for Kerberos, DCE or PAM in your libtcl.cfg file. Use secmech to specify the name of a SECURITY entry, or default for the first available entry in libtcl.cfg. And none must be specified to disable network authentication or to reset possible existing values in the variables $secmech or $secure_options. 💅

For example, connecting to a server using Kerberos (which happens to be the default here, i.e. the first entry in the 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

Now, notice how the real name of the server (@@servername) differs from the server name in the interfaces file — that's exactly why we have to specify the principal name through the -R parameter. And when you don't specify the -V parameter together with -Z, all available security options get enabled. But when -V is specified without any security service options, only option u for Network Authentication gets implicitly set, and the default security mechanism is used if -Z isn't 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, sqsh stores the real name of the security mechanism in the variable $secmech. For example, "\echo $secmech" may show csfkrb5. The parameter -V takes a list of characters from the possible values of bcdimoqru. And the option u enables Network Authentication, is the default, and will allways be set when using -V or -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 -V are silently ignored. And if you specify -V and/or -Z, sqsh assumes network authentication is being tried and no password is required. 🫶

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

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. But like — no user principal for 'sa' exists and no ticket is set, so the connection fails. The second \reconnect succeeds because the -Znone option reset the appropriate variables. 🎀

And if the Kerberos ticket is renewed with kinit or any other client tool, the sqsh session must perform a \reconnect to refresh the credentials and to prevent a premature session abort. With the command \snace you can request 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. 😭 scary but true.

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

🌈 COMMANDS 🌈

💖 Read-Eval-Print 💖

The read-eval-print loop is literally the heart of the sqsh system 💓 — she's responsible for prompting a user for input and figuring out what should be done with it. Typically this loop is for internal use only, however they're open to the user because there are some creative things you can do with them. main character energy fr fr.

\loop [-i] [-n] [-e sql] [file] — the \loop command reads input either from a file, a supplied SQL statement, or from a user (peep 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, \loop is also responsible for displaying the current prompt (see $prompt below).

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

-i — Normally, if file is supplied and does not exist, \loop will return with an error condition, usually causing sqsh to exit. By supplying the -i flag, control is returned to the calling loop as if end-of-file had been reached (that is, with no error condition).
-n — By default, \loop will automatically attempt to connect to the database if a connection has not already been established via the \connect command. The -n flag disables this behavior, allowing \loop to process commands that do not require database support.
-e sql — Causes \loop to process the contents of sql as if the user had typed it at the prompt, and an implicit call to \go is automatically appended to the statement. If multiple instances of -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 file name as well.
file — Specifies the name of a file to be used as input rather than reading input from the user or from the -e flag.

🗄️ 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 there actually are. The following are commands that affect or use the current database connection:

\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 $password has not been supplied, then the password is requested and a connection is established. \connect accepts the following parameters:

-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.
-c — By default, the \connect command uses the contents of $database to determine the database context that should be used upon establishing the connection (this is used by \reconnect to preserve the current database context upon reconnection). The -c flag suppresses this behavior and the default database context of login is used instead.
-D db — Causes \connect to attempt to automatically switch the database context to db after establishing the connection.

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

-G tds version — Set the TDS version to use. See the global startup parameter -G for more information on TDS version.
-S srv | host:port[:filter] — The name or address of the Sybase server to connect, this defaults to $DSQUERY if not supplied.
-U user — The Sybase user to connect to the database as, this defaults to the $username variable if not supplied.
-P pass — The password for user required to connect to server. This defaults to $password if not supplied.
-I ifile — The full path of an alternate Sybase interfaces file to use.
-J charset — the name of the client character set you're using to talk to the server. ✨
-K keytab_file — for DCE user authentication. that's the whole thing, bestie.
-R principal_name — Kerberos user auth, and you use this to name the server principal when it's not the same as whatever's in $DSQUERY.

(there's more tea on this in the Kerberos support discussion, go look.)

-n — forces the connection to use ANSI compliant chained mode. slay.
-N appname — sets the application name, which the server will show as program_name in the sysprocesses table. cute little nametag for your session 💅
-Q query_timeout — sets a query timeout, in seconds.
-T login_timeout — the max wait time for session setup.
-V [bcdimoqru] — security services used for Kerberos support and other security mechanisms.
-X — kicks off the login connection to the server with client-side password encryption (if supported). If SQL Server doesn't recognize this option, or if the version of CT-Lib that sqsh was compiled with doesn't support it, then it just gets ignored, no drama. You can also set this with the $encryption environment variable. 🔐
-z language — picks an alternate language for sqsh prompts and messages. Without -z, you get the server's default language. Can also be set via the $language variable. 🌈
-Z [secmech|default|none] — specifies the security mechanism for user authentication. Like, csfkrb5 for Kerberos support. 🎀

🌀 `\reconnect`

\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]]

okay so \reconnect is the glow-up command: it forces a reconnection to the database using a new username, server name, or password (whatever you feel like changing). And if it fails? no worries, the current connection stays put (if there is one). But if it succeeds? then the old connection gets closed and the new one becomes the only active connection. main character energy, periodt. 👑

Every single argument that \connect accepts is also accepted by \reconnect — literally, because \reconnect uses \connect under the hood to build the new connection. iykyk.

🏃 `\run`

This one runs a script file, kinda like \loop, except \run lets you pass optional script parameters. oh and it also takes these:

-e — run the script file with echo on.
-f — suppress footers.
-h — suppress headers.
-l — suppress separator lines when using pretty output style.
-n — disable SQL buffer variable expansion.
-p — report runtime statistics.
-m style — specify the output style: {bcp|csv|horiz|html|meta|none|pretty|vert}.
-i filename — required parameter, this is the filename sqsh is gonna run.

for example:

\run -p -i ~/tmp/runtst.sqsh 10 -m pretty

🗂️ `\lcd` dirname

Local Change Directory. bestie it does exactly what it says — takes a directory name and changes the local SQSH context to that directory. You can also do \lcd - to bounce back to the previous directory. and heads up: if you exit sqsh, your shell is still sitting in the same directory you started sqsh from. 🐚

📍 `\pwd`

Print Working Directory. shows you the name of the current local working directory. simple, iconic. ✨

📂 `\ls`

Lists the files in the current directory. Takes zero arguments, and it's basically just a shortcut for \shell ls. 🍓

⏰ `\snace`

Will show the session expiration interval for a network authenticated session — like, say, a Kerberos enabled session. yeah. 🕰️

🚀 `\go` `[options] [xacts]`

Sends the contents of the Work Buffer to the database, and it'll establish a new connection to the database if there isn't one already (by calling \connect, like we talked about). Then it displays the query results back to stdout and returns, which means the Work Buffer gets cleared and moved to the end of the History Buffer. bye buffer 💖

Oh and if the Work Buffer is empty and $repeat_batch is set to "On", \go will try to re-run the last command you executed. (this only works in interactive mode if history support is enabled, ngl.)

\go accepts these arguments:

-d display — if X11 support is compiled into sqsh and you're using X display mode (peep -x below), then display becomes the X display area for the result set. By default it assumes the $DISPLAY environment variable.
-e — echo the expanded SQL buffer before sending it off to the server.
-f — turns off the footer message "(%d rows affected)". You can also kill footers with the $footers variable.
-h — turns off all column headers. Also controllable via the $headers variable.
-m style — temporarily switches the display style to style for the duration of the command. Currently supported styles are horiz (or hor or horizontal), vert (or vertical), bcp, csv, html, meta, pretty and none. You can set the style permanently with the $style variable or the -m command line flag.
-l — suppress separator lines when you're using the -m pretty output style. Also turnoff-able via the $nosepline variable.
-n — turns off variable expansion in the Work Buffer before it gets sent to the server. also available via the $expand variable.
-p — turns on output of performance statistics once the result set comes back from the server successfully. You can also turn this on with the -p command line argument to sqsh, or the $statistics variable.
-s sec — if xacts is greater than 1, this makes sqsh sleep for sec seconds before executing the next transaction. heads up though: the time spent sleeping is not counted in the statistical info shown by the -p flag. 💤
-t [filter] — filters the command batch through an external program, filter, before it gets sent to the SQL Server. If filter isn't supplied, then $filter_prog is used (default is m4 -). This value can also be set via the $filter and $filter_prog variables.
-w width — overrides the value of $width for the life of the query (see $width below).
-x [xgeom] — turns on the X11 display filter (only if X11 support is compiled into sqsh), which sends the result set to a separate window. If xgeom is supplied, that value gets used as $xgeom for the life of the query (see $xgeom below).
-T xwin_title — specify the title name of the X result window to create. This temporarily overrides the value of $xwin_title. Only useful to specify -T together with -x.
xacts — the number of times the contents of the 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 Work Buffer are only expanded once, before the first execution, so the buffer contents won't change between subsequent executions.

🔥 `\bcp` `[bcp_options]` `table[:slicenumber|:partition name]`

\bcp is like an enhanced \go that redirects the result set(s) of the batch to another server via the bcp protocol. Now, you can \bcp a result set back to the current server (the $DSQUERY variable), but honestly it's easier to just do a SELECT INTO. 🙈

The nitty-gritty of \bcp goes like this: first the current SQL batch gets expanded (unless $expand 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 (specified via $DSQUERY or the -S flag) to transfer the result set using bcp. Then the output of the source database connection is bound to that new bcp connection, and the data transfer happens. \bcp can handle multiple result sets with zero problem (including result sets returned from stored procedures, etc.) — as long as all of those result sets are valid for the destination table. 💅

And psst — the equivalent of a "bcp out" can be done using the bcp display style setting plus file redirection (see the $style variable).

-A packet — specifies the TDS packet size used to communicate with the destination server. If not supplied, this defaults to the value of the $packet_size variable, or (if that's not set) the default server packet size (usually 512 bytes).
-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 batch_size. The default is the entire result set.
-i "<initialization command>" — with the -i parameter you can send a SQL command to the target server that will be executed right before the bulk copy operation starts. Super 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

-I ifile — the full path of an alternate Sybase interfaces file to use.
-J charset — specifies the default charset used to communicate with the SQL Server. This defaults to the current character set (the value of the $charset variable).
-m maxerr — the maximum number of batches that may fail before \bcp gives up the ghost (default is 10). Note that this only refers to failures within a given batch. When you're bcp-ing 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 maxerr. 💀
-N — indicates that the value for an identity column in the destination table is being supplied within the result set.
-P password — the password for the user required to connect to the server. This defaults to $password if not supplied.
-S server | host:port[:filter] — the name or address of the Sybase server you wanna connect to 🫶. If you don't hand one over, it just defaults to $DSQUERY. ngl, that's the whole vibe.
-T — transfers the data in transit without doing any character set conversion on the client side. no conversion, no drama ✨
-U user — the Sybase user you're logging into the database as 💅. Skip it and it falls back to the $username variable, easy.
-X — makes password negotiation with the destination server happen using client-side encryption 🔐. literally, that's it.
-z language — picks which language setting to use 🌸.
table[:slicenumber|:partition name] — okay so same deal as regular bcp: table can be a fully or partially specified table name on the destination server 👑. But heads up bestie — because a new database connection gets established during the bcp process, the database context of that connection might not match your current context 😭, so it's usually safest to fully specify the table in the form database.owner.table. For partitioned tables you can hand over a slicenumber or a partition name — separate the table name and the partition with a colon (:) — to bcp the data into that specific partition 🎀.

`\rpc` — the "just call the procedure directly" icon 💫

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

The \rpc command is how you directly invoke a stored procedure call on the connected server 🔥. It's especially iconic for talking to an Open Server that doesn't directly support language calls — iykyk.

\rpc invokes the remote procedure rpc_name with one or more parameters that can be named (by using @var) or anonymous (by just... not supplying a name 🙈).

Now, the messy part — and I'm being so fr right now — Sybase's implementation of RPCs doesn't directly support most implicit data type conversions (mainly between VARCHAR, aka the string you type on the command line, and basically every other data type the remote procedure is expecting 💀). So the syntax for \rpc is kind of complex. But short version, here's how it goes:

As the \rpc command line is being parsed, sqsh tries to guess the data type of the parameter value based on its format ✨ (for example, if it's only digits, it assumes integer). Then sqsh performs an explicit data type conversion before calling the remote procedure call. If sqsh guesses wrong? No worries, there are flags you can supply to force the correct conversion — peep parm_opt below 💅.

Display Options ✨

These options can go anywhere on the command line, and they control the vibe of how the result set(s) coming back from the remote procedure call get displayed 🦋:

-d display — if X support is compiled into sqsh, display is used as the X windows DISPLAY variable. Heads up: this is usually supplied alongside the -x flag, below 🌈.
-f — turns off the display of the footer message "(%d rows affected)". You can also kill footer messages with the $footers variable 🎀.
-h — turns off all column headers. These can also be turned off via the $headers variable 💖.
-m style — temporarily switches the display style to style for the duration of the command. Currently supported styles are horiz (or hor or horizontal), vert (or vertical), bcp, csv, html, meta, pretty and none 👑. The display style can be set permanently via the $style variable or the -m command line flag.
-r — requests that the procedure be recompiled before it executes 🔄.
-w width — temporarily sets the output width to width. Output width can be set permanently via the $width variable ⭐.
-x [xgeom] — sends output to a separate X window 🪟. If xgeom is supplied, the X window uses that geometry (check $xgeom for the deets).
-T xwin_title — specifies the title name of the X result window to create. This temporarily overrides $xwin_title. Only useful to specify -T together with -x 🧚.

Parameter Options 💅

These options go immediately before a parameter value, and they control how sqsh interprets what's inside that value before calling the remote procedure 🫶. Even though sqsh will let you combine any of these parameters, honestly it only really makes sense to combine the -x flag with any other flag 🤷.

-b — says the value should be converted to VARBINARY before calling rpc_name. This flag is implicit (so you don't even need to supply it) if value starts with "0x" and contains only digits 💀.
-c — says the value should be converted to VARCHAR prior to calling rpc_name. This flag is implicit if value doesn't match any of the implicit conversions for the other data types 🌸.
-d — says the value should be converted to double (float) before calling rpc_name. This flag is implicit if value is in valid floating point notation (e.g. 0.1, .1, 1.4e10, or 4e10) ✨.
-i — says the value should be converted to integer (int) before calling rpc_name. This flag is implicit if value contains only digits (and, optionally, a leading sign) 🔥.
-y — says the value should be converted to money before calling rpc_name. This flag is implicit if value begins with a "$", and contains only digits and, optionally, a decimal 💰.
-n — says the value should be converted to numeric before calling rpc_name. This flag is never implicit, because value would always match either int (-i) or float (-d) — but both of those types will implicitly get converted to a numeric as needed by the procedure call anyway 🎀.
-u — says value should be ignored and treated as a NULL value. This flag is implicit if value is "" 🫥.

Buffers 🦋

The following commands create, destroy, or manipulate the various buffers described in the BUFFERS section, above. Main character energy for your edit workflow, fr fr.

\clear — discards the current buffer and, in contradiction with the \reset command, does not save the current buffer to the history 😭. When sqsh is compiled with readline support, this command also clears the screen (as ^l does). The alias clear is automatically established upon startup 💫.
\history [-i] [-x count] — displays the last $histsize batches that have either been sent to the database via the \go command or cleared from the Work Buffer via the \reset command 👑. With the -i option it also shows the number of times the buffer is used and the last time the buffer was used — which is especially interesting when you're using the $histunique feature 🔍. The -x option lets you specify how many of the most recent history entries to display instead of the whole list ⭐.
\hist-load [filename] — loads a history file and appends items to the current history list 📥. Use $history if no filename is given as a parameter.
\hist-save [filename] — saves the current history buffers to the $history file, or to the filename specified as a parameter 💾.
\redraw — sends a request back to the current read-eval-print loop so it redisplays the current Work Buffer 🖥️. If you run it from non-interactive mode, this command does literally nothing 🙈.
\reset — corresponds directly to the isql 'reset' command: it returns a request to the read-eval-print loop to clear the contents of the current Work Buffer and, if you're in interactive mode, place a copy of the buffer into the History Buffer ✨. The alias reset is automatically established on sqsh startup for backward compatibility with isql 🎀.
\buf-append dst-buffer [src-buffer] — appends the contents of src-buffer (defaults to !.) onto the contents of dst-buffer, if it exists. If dst-buffer doesn't exist, it gets created 🌱.
\buf-copy dst-buffer [src-buffer] — copies the contents of src-buffer (defaults to !., the Work Buffer, if not supplied) to dst-buffer 📋. Check BUFFERS for the buffer naming conventions.
\buf-del [buffer|range] — removes a history buffer from the history list 🗑️. You can also specify a range list made of the first and last buffer numbers separated by a '-'. For example: "\buf-del 1-10" deletes the first 10 history entries. Another example: "\buf-del 21" deletes only buffer number 21. Note that the history buffers get renumbered consecutively after this 🔢.
\buf-edit [-r read-buf] [-w write-buf] — used to edit the contents of a buffer and drop the changes into another buffer ✍️. This command may only be run while in interactive mode. If read-buf isn't supplied, the buffer to edit defaults to !. if it isn't empty, otherwise it defaults to !!. If write-buf isn't supplied, the edited buffer gets written back to !. 💅.

By default, \buf-edit uses the environment variable $EDITOR first, then $VISUAL, to figure out which editor to launch — defaulting to 'vi' if the variable isn't set 🧚.

Important bestie: as of release 1.2, \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 mainly because of the change in the behavior of alias (see the Aliasing section, below, for details).

The commands edit, vi and emacs are automatically established on sqsh startup for backward compatibility with isql 👑.

\buf-get buffer — a shorthand method of running \buf-copy 💖. It's the equivalent of running:

\buf_append !. buffer

\buf-load [-a] filename [dst-buffer] — copies the contents of filename into dst-buffer (defaults to !.) 📂. If the -a flag is supplied, the contents of filename are appended to dst-buffer. Note: it is illegal to attempt to write to the contents of the history buffer 🚫.
\buf-save [-a] filename [src-buffer] — saves the contents of src-buffer (defaults to !.) to filename 💾. If the -a flag is supplied, the contents are appended to filename rather than overwriting what's currently there.
\buf-show [buffer] — displays the contents of the named buffer 👀. If buffer isn't supplied, then the contents of all named buffers are displayed. This command is slightly different from the ones above: it's only legal to supply a Named Buffer buffer — History Buffers and the Work Buffer will have no results 💀.

Variables 🌈

The following command(s) manipulate the contents of internal variables and environment variables ✨.

\set [-x] [name=value ...] — okay so if you hand \set literally zero arguments, it just shows you the current values of all the variables, no cap. Otherwise name gets set to value. Heads up bestie: some of the internal variables (peep the SPECIAL VARIABLES section 💜) can only be set with certain specific values, so this whole thing might just fail and leave the old contents on name intact, untouched, like nothing happened. The -x flag exports that variable into the environment of any programs you launch from sqsh, so it travels with them 🧳.

🎀 Job Control 🎀

These commands let you peek at the status of, or mess with, background jobs that are currently running — they roughly line up with the commands you'd get from shells like csh(1).

\jobs [-i] — shows the status of any currently running jobs, including whether they've got pending output, how long they've been running, and when they started. The -i option shows you some extra job info on top of that. ⚠️ Ngl there's a lil gotcha: the total run time of a job is figured out from the moment the job gets marked complete by the read-eval-print loop when it's polled, not when it actually ended and got flagged as terminated by the signal handler. So like, it's giving approximate energy, not exact.
\wait [job_id] — pauses until the job picked out by job_id finishes. If job_id is a negative number, then \wait pauses until any pending job completes. If there are no jobs pending, or job_id doesn't belong to a running job, you get an error message. ⚠️ And here's the tea: if multiple jobs are running and you issue a \wait for just one of them, the other jobs might not automatically get signaled when they complete during the wait period of that specific job. Those other jobs may need to get signaled with a \wait -1 as well before sqsh notices they're complete. Annoying but that's the vibe 🙈.
\kill job_id — terminates the job specified by job_id, and throws away any output that might be deferred for that job. If job_id isn't a running job, you get an error message. 💀
\show job_id — displays the deferred output of the completed background job job_id and removes that job from the list of pending jobs (deleting the defer file in the process). If job_id is still running, or isn't a valid complete job, you get an error message. You might need to fire off a \wait job_id first to actually get notified of real job completion.

Aliasing

As of release 1.2, sqsh supports full csh-style command aliasing — iconic behavior honestly ✨. With this feature, sqsh checks the first word of every line, before any form of expansion, to see if it matches the name of an existing alias. If it does, the command gets reprocessed with the alias definition standing in for its name. But — unlike csh — only one form of history substitution is available inside an alias: the !* entry, which indicates the current line being expanded. If no history expansion is called for, the arguments on the command line just stay unchanged.

And like csh, aliases are not recursively expanded, so it is perfectly legal to create an alias that expands to a command by the same name. No infinite loop panic, you're good 💅.

The command you use to make an alias:

\alias [alias_name=alias_body] — if you give \alias no arguments, it shows you the list of aliases currently in effect. Otherwise, it creates a new alias with a name of alias_name and a body of alias_body; if alias_name already exists, the body of the existing alias_name gets replaced with the new definition. After you define the new alias, whenever sqsh encounters a line beginning with alias_name, the rest of the line is replaced with alias_body before any further processing happens. If the string !* shows up anywhere within alias_body, the arguments you gave the alias get inserted right at that point; otherwise the arguments get appended to the end of the alias definition. For example:

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

whereas if the alias does not include the !* 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 !* more than once within a given alias_body. Currently there is no way to escape the string !* — if you really need this feature, send me mail. 📬

\unalias alias_name — removes alias_name. Gone. Poof. 🦋

Miscellaneous

The left over commands. The grab-bag, the catch-all, the main character section 💜.

\exit [x] — requests that the current read-eval-print loop cease processing. When the last loop returns, sqsh exit(1)s. You may specify an exit code as a parameter, which gets stored in $exit_value and is used as the return value to the shell when sqsh exits. (0 <= x <= 255).
\abort — causes all nested read-eval-print loops to abort processing, which makes sqsh exit with an exit value of 254 (see the section EXIT STATUS).
\read [-a] [-n] [-h] var_name [< filename] — reads a line of input from the user and drops the text of that line into the variable var_name. If -n is used, the trailing new-line is left on the line of text. If -a is supplied, the text of the line is appended to the existing value of var_name. The -h flag turns off echoing of typed characters back to the user (sneaky, we love 🙈). You can also read the contents of a file using the \< file redirection notation.
\sleep seconds — makes sqsh pause for seconds. Super useful inside scripts or batches that need to pause briefly between batches (it was primarily useful to me for testing background jobs, fr fr).
\echo [-n] [args ...] — just like UNIX echo(1), it prints its arguments to stdout followed by a new-line. If the -n flag is supplied, that newline is omitted.
\warranty — displays the standard GNU warranty.
\help [command] — with no arguments, \help shows a brief list of all available commands; otherwise it gives you specific help for command, if it's available. When you ask for help on a specific command, \help looks for the file $help_dir/command.hlp and displays it to stdout.
\shell [shell command] — if shell command isn't supplied, sqsh executes $SHELL. If the $SHELL variable hasn't been set, then by default /bin/sh gets executed. Otherwise, if shell command is supplied, that gets executed instead. The exit status of the command executed is stored in the special $? read-only environment variable.
\lock — locks the current session until the correct password is typed in. By default, \lock tries to use the UNIX password (from /etc/passwd or /etc/shadow) associated with the user running sqsh — that is, if sqsh is linked with the crypt library. However, if the $lock variable is set, then the contents of that get used for validation instead. 🔒

Aliases

These are the aliases that get established upon startup of sqsh, and they're provided primarily for backward compatibility with isql. You can remove any of them at any time using the \unalias command — either right at the prompt, or inside your .sqshrc file. 👑

! — the ! alias is provided as a csh(1)-like history mechanism, and it's an alias of \buf-append. With release 0.7, this alias is provided only for backwards compatibility with previous releases of sqsh. See SPECIAL VARIABLES, $history_shorthand for the details on the new shorthand mechanism (the new shorthand more closely resembles that of csh).
clear — an alias for the \clear command, which causes the contents of the current work buffer to be cleared and discarded. Bonus: the screen also gets cleared if readline is compiled into sqsh. ✨
reset — an alias for the \reset command, which causes the contents of the current work buffer to be cleared and copied to history (if you're in interactive mode).
exit [x], quit [x] — an alias for the \exit [x] command, which causes the current read-eval-print loop to complete.
edit, vi, emacs — these are provided as aliases for the \buf-edit command. See COMMANDS-Buffers for the info on the interactions between \buf-edit and aliases.
go — provided as an alias for the \go command (for obvious reasons 💅).
help — an alias for the \help command.

In-Line \go

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

1> sp_who;

behaves in the same manner as if you had typed:

1> sp_who
2> \go

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

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

Unlike most other isql replacements, 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 example above, only the second semicolon (the one at the end of the line) gets interpreted. The variable $semicolon_cmd is a string that contains the command that will be substituted by the semicolon, which is \go by default. But you can change that to \bcp for example and execute:

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

In sqsh-2.2.0 you can also set the variable $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 \go command. But of course you can change $semicolon_cmd to your needs, since 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 \go command to a SQL buffer and as a command separator for sqsh commands that will be executed in sequence. Note that $semicolon_hack2 takes precedence over $semicolon_hack. ⚠️

✨ SPECIAL VARIABLES ✨

There are several options that are configurable via the command line options to sqsh — however, these are by no means complete, bestie. There are many aspects of sqsh's behavior that can only be modified by setting special variables. (In fact, the command line options really only set these variables for you. Wild, right? 🫶)

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 a variable with a type of data that it doesn't accept will fail. No exceptions 💅.

string — any sequence of characters.

`boolean` 💖

A positive boolean value can be literally any of these besties: "True", "Yes", "1", or "On" (and yes, case insensitive, we don't judge). A negative boolean value can be "False", "No", "0", or "Off" (also case insensitive, icon behaviour). But internally? The variable is always, always stored as either a "1" or a "0". That's just the vibe it commits to.

`path` 🦋

Must be the path name that is readable by the sqsh program. That's it. If sqsh can't read it, it's not it.

`int` 🔢

Must be one or more digits. Note that some variables also restrict the range of the integer, so don't get cocky.

`date-spec` 🎀

This is a string of the format used to specify dates and times for the date(1) command, or the strftime(3C) and 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. Cute, right? That's the whole thing.

`float-format` 🌸

A string of the format p.s, where p is the total precision of a floating point value (the total number of digits to be displayed, including those following the decimal) and s is the scale of the value (the total number of digits following the decimal to be displayed). Precision and scale, bestie. Know the difference. 💅

✨ Variables ✨

The following variables have special meanings within sqsh and the setting of these variables alter the behavior of the shell. Like, they're the main characters. 👑

💖 `$?` (int)

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

The most recent error number returned from the SQL Server (@@errno) of severity > 10 (above informational messages).
The exit value of a previously executed pipe command.
The return value of the most recently executed sqsh function.
The result of the last executed \if statement:
- 0 - The test evaluated to True.
- 1 - The test evaluated to False.
- 2 - There was an error in the test specification.

💖 `$#` (int)

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

💖 `$` (string list)*

Expands to the complete list of arguments supplied to the function or script when invoked. The whole squad, together. 🫶

💖 `${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. So yes, ${0} is literally the function's own name. Not me being obsessed with self-reference. 😭

💖 `$$`

Expands to the process ID of the current running sqsh session. It's giving... identity. 🪪

💖 `DISPLAY`

May contain the name of the DISPLAY the X-server uses to put the XWindows result window when using \go -x. Defaults to the $DISPLAY shell environment. XWindows girlies, this one's for you. ✨

💖 `appname`

Contains the name 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;

💖 `autouse` (string)

Note: the meaning of this variable has been deprecated. She's retired, but we still document her. 💀

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

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

💖 `banner` (boolean)

Turns off the banner message displayed on startup, this variable defaults to 1 and may also be turned off using the -b command line argument. Say bye to the banner if you want. 🙈

💖 `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 $thresh_fail is encountered. When $batch_failcount reaches $thresh_exit sqsh exits with an exit value of the total number of batches that have failed. Setting $batch_failcount to the string "" will cause it to reset to zero, any other value may have unpredictable results. ⚠️

See EXIT STATUS for details.

💖 `batch_pause` (boolean)

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

💖 `bcp_colsep` (string)

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

💖 `bcp_rowsep` (string)

Used as a separator between rows during BCP style output (see the $style configuration variable and the -m option to the \go command). Note that, a newline ("\n") is automatically appended and should not be supplied. The default setting is "|". Don't double up on the newline, bestie. 🚫

💖 `bcp_trim` (boolean)

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

💖 `builddate` (none)

Read only variable specifying the date when the sqsh executable was compiled. It's her birthday. 🎂

💖 `buildtime` (none)

Read only variable specifying the time when the sqsh executable was compiled. And this is the exact minute of the birthday. ⏰

💖 `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 -n. Read that again: no effect on the current connection. Current one is locked in. 🔒

💖 `charset` (string)

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

💖 `clear_on_fail` (boolean)

Normally, whenever the \go command is run, sqsh clears the current work buffer of its contents, moving them to history. Setting $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. So by default she clears, and if you want her to keep the receipts on failure, set it to 0. 🧾

💖 `colsep` (string)

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

💖 `colwidth` (int)

Used to control the maximum column width displayed by the pretty display style (see $style below). If a row of a column exceeds this width, it will be wrapped in a relatively visually appealing manner at $colwidth characters. Note, however, that if there is enough screen width to hold all columns $colwidth may be exceeded until the width of the screen is reached. So it's a soft limit, okay? She said "relatively visually appealing" and she meant it. 💅

💖 `database` (string)

If this variable is set prior to establishing a connection to the SQL Server, the a "use $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. (Yes, "the a use $database" — we quote the original exactly, typos and all. No cap. 🫡)

💖 `date` (date-spec)

This variable may be set with a date format (see the man page for 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).

💖 `datefmt` (date-spec)

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

💖 `datetime` (date-spec)

This variable may be set with a date format similar to $date and $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 $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 -z flag. For example, if sqsh is run on an operating system configured for US English, but requests French as the language of choice using -z, the use of $datetime will cause all date information to be displayed in US English rather than French. 💀 Locale betrayal, lowkey.

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 strftime manual page.

[] — 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. Sneaky little selective deleter. 🧚
%q — Specifies the milliseconds for DATETIME and TIME and microseconds for BIGDATETIME and BIGTIME data types. In previous versions of sqsh the %u specifier was used for the millisecond part of the datetime datatype. However, as %u is used by strftime to denote the day number of the week, this is replaced by %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

💖 `debug` (string)

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

💖 `debug_tds_capture` (string)

When 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. Trace girlies, this one's yours. 🦋

debug_tds_logdata (string) — so when TDS debugging is turned on and this lil variable is set to a valid filename, OpenClient writes a debugging log there with literally all the debugging info possible (CS_DBG_ALL). check the option above too, bestie 💖
defer_bg (boolean) — normally, when you run a job in the background (that's the & on the command line, you know the one), the output gets deferred to a temp file (chilling in $tmp_dir) until you ask for it with \show jobid. that way your job results don't go crashing into whatever you're doing. set this variable to false and no deferred output files get made at all — output goes straight to the screen immediately. no thoughts, head empty, output now 🫶
echo (boolean) — set $echo to on (1) and every command you send to the database via \go gets displayed before the output. defaults to 0 (or off), and you can also set it with the -e command line option. slay ✨
encryption (boolean) — set the $encryption variable before you establish a connection to the server and the login connection gets initiated with client-side password encryption 💅. you can also set this with the -X command line option. client-side password encryption also kicks in when you use -X on the \connect, \reconnect or \bcp commands. now listen — the Sybase ASE setting for the net password encryption reqd (15.0.2) configuration parameter may force you to use client side password encryption, no cap. sqsh-2.1.9 supports RSA password encryption when the CT-Lib version in use supports it. RSA password encryption is required if that ASE configuration parameter is set to 2. older versions of sqsh only support a value of 1 (CT-Lib internal encryption algorithms). 👑
exit_failcount (boolean) — set this to 1 and sqsh returns an exit status of $batch_failcount instead of 0 upon a non-error termination. see EXIT STATUS for the details. default is 0. ⭐
exit_value (int) — when you exit sqsh by giving an exit code, like \exit 3, or by using one of the aliases exit or quit, then this value gets assigned to the $exit_value variable. you can also assign a value with the \set command. when sqsh figures out this variable has a non-zero value during termination, that value gets used as the exit code. see EXIT STATUS for details. 🎀
expand (boolean) — by default, when \go runs, the contents of the current work buffer get expanded of all environment variables before being sent to the database to execute. set this variable to "0" and the buffer stops getting expanded before it's sent. super useful when you either (1) have strings in the buffer containing a '$' and you do NOT want them expanded, or (2) for performance reasons — it takes time (and an extra copy of the buffer) to do that variable expansion. 💀
filter (boolean) — toggles filtering of the SQL batch through an external program (defined by the $filter_prog variable, below) before it gets sent to the SQL Server. default is '0', or 'off'. 🦋
filter_prog (string) — defines that external program the SQL batch gets filtered through before being sent to the SQL Server. this variable is ignored if $filter is set to '0' or 'off'. the default is 'm4 -'. 🌸
float (float-format) — defines the display format (the precision and scale) for all floating point values sqsh shows you. default is '18.6'. heads up though: 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 😭. choose wisely.
footers (boolean) — toggles the "(%d rows affected)" that follows a result set. default for this variable is '1'. ✨
headers (boolean) — toggles the column headers preceding a result set. default for this variable is '1'. 💖
help_dir (path) — the location of the help files used by the \help command. typically it defaults to something like /usr/local/lib/sqsh/help. 🧚
hist_auto_save (int) — when $histsave is on and $hist_auto_save has a value greater than 0, then the history gets automatically saved to $history after $hist_auto_save modifications of the history buffers. when the value is 0 (default), no automatic history save happens. 💅
histmerge (boolean) — when this option is on, sqsh merges the contents of the history on disk with the history in memory before writing history back to disk. this is to prevent data loss in the history file when other sqsh sessions have also written to that same history file on disk 🫶. this option is off by default, which just overwrites the existing history file when you exit sqsh or when history is being automatically saved because $hist_auto_save is set.
histnum (int) — contains the history number that will be assigned to the current command batch as soon as \go is executed. this variable should be considered read-only. see also the discussion on the $histunique variable. 👑
history (path) — the location of the history file used to store and retrieve a user's history during start-up and shutdown. defaults to $HOME/.sqsh_history. this variable gets expanded each time it's referenced by sqsh, much in the same way $prompt is referenced each time the prompt is displayed. ⭐
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, like '!40'. > be careful with this feature, bestie — sqsh is not terribly intelligent with looking for history shorthand, so it's possible it may get confused (although, it is smart enough to ignore !'s in quoted strings). 😭
histsave (boolean) — the value of this variable is used by sqsh to indicate whether the history should be saved to $history before sqsh terminates. 🌈
histsize (int) — the value of this variable alters the maximum number of history entries sqsh maintains (the default is 10). note that decreasing the value of this variable causes some history entries to be lost 💀.
histunique (boolean) — if set, 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

sp_who is the last executed command and the buffer — originally last in the list — is now on top of the list. when an already existing buffer is reused, the value of the $histnum variable is not changed. 🦋

hostname (string) — used during the connection process to tell SQL Server the name of the host sqsh is connecting from. you can also set this with the -H flag. 💖
ifs (string) — the list of Internal Field Separators. ✨
ignoreeof (boolean) — by default, sqsh terminates if the user presses ^d (control-D) on an empty line and readline support is compiled in. if $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. 🎀

interactive (boolean) — this is a variable used internally and should probably not be altered by the user. if $interactive is '0', then the prompt is not displayed, the history is neither read nor written, and some user messages are suppressed. 🙈
interfaces (path) — the full path name of the interfaces file. defaults to $SYBASE/interfaces. 💅
keytab_file (string) — used in Kerberos and DCE user authentication security mechanisms. corresponds with the -K startup option.

see also the discussion on Kerberos Support. 👑

keyword_completion (int/string) — this variable only applies if GNU Readline support has been compiled into sqsh. $keyword_completion controls the T-SQL keyword completion feature in readline, and you can set it with either an integer between 0 and 4, or one of the strings none, lower, upper, smart, or exact. if it's set to either 0 or none, then no keyword completion is performed (this is the default). lower or 1 makes sqsh complete the keyword in lowercase, regardless of the case the partially completed keyword was typed in. upper or 2 forces completion to happen in upper case. smart, or 3, bases the case decision on the first character of the partial keyword. and exact completes the keyword in exactly the same case as defined in the .sqsh_words (for the built-in T-SQL keywords, this will be lower case). new in sqsh-2.5 is that when regular Readline completion doesn't match the typed string with a keyword, then filename completion gets 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". 🦋✨

💅 keyword_dynamic (boolean)

okay so this one controls whether keywords get dynamically loaded into the completion list — both when you first log in to the server, and whenever the database context changes because you ran use database. 🦋

When it's set to 'On' — and bestie, the default is 'Off', so you gotta turn it on yourself — the query you handed over via $keyword_query gets executed, and the result set gets dumped straight into the Readline completion list. This only works for Sybase ASE and Microsoft SQL servers. No cap.

This variable also controls dynamic loading of a column list for Readline autocomplete. Here's the vibe: when an object name is followed by a dot and you hit TAB, sqsh dynamically builds a list of columns belonging to that object (table, view, procedure) and lets Readline TAB-complete the column or parameter names. ✨

This feature is only available if GNU Readline support has been compiled into sqsh and $keyword_completion is set to a value greater than zero.

In sqsh-2.4 it's also possible to use the object alias in the SQL buffer for auto-completion. Like, watch: 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 gets produced for the sysdatabases table so you can keep completing:

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. And note: sqsh is now able to perform cross database auto-completion as well. iconic tbh. 👑

📂 keyword_file (string)

If Readline support has been compiled into sqsh, and sqsh is running in interactive mode, the contents of this file get used for keyword tab completion by Readline — instead of the default set of T-SQL syntactical keywords.

The default is $HOME/.sqsh_words.

When $keyword_dynamic is enabled, that takes precedence and overrules the list loaded from $keyword_file. periodt.

🔍 keyword_query (string)

This variable holds the query that gets 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 bestie, you can absolutely 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 $keyword_completion and $keyword_dynamic, and is only available if GNU Readline support has been compiled into sqsh. 🌸

🗣️ language (string)

The $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. You can also set it via the -z flag.

🔢 lineno (int)

This is an internal variable and should not be altered by the user. 🚫

It's used to maintain the line number that's being typed into within the current work buffer. That's it, that's the whole job.

➖ linesep (string)

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

🌍 localeconv (boolean)

A boolean variable that, when set to true, makes the Operating System locale be used for displaying all datetime datatypes — that's DATETIME, SMALLDATETIME, TIME, DATE, BIGDATETIME and BIGTIME — plus 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 uses 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 ",". 👀

Note that sqsh does not take the thousands_sep into account when displaying numerical data values.

🔒 lock (string/write-only)

Defines the password to be used by the \lock command.

If unset or set to the string "NULL", then the UNIX password of the user running sqsh is used instead (if possible).

Note that $lock will always expand to the string "*lock*" if referenced. sneaky. 🙈

The number of seconds to wait when attempting to create a new connection to a data server. Default is 0 (infinite wait).

🛑 max_timeout (int)

Controls the number of times a query needs to reach its time-out value before the session is aborted. See $query_timeout and the -Q command line flag.

The following messages will be displayed, and sqsh exits with value 255, when the $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

📏 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).

🪄 newline_go (boolean)

This flag is used as a horrible kludge to support an "empty" alias for the \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 \go command.

This feature is not recommended but is supplied for completeness. you've been warned bestie 💀

🚫 nosepline (boolean)

Suppress the separator lines with the pretty output style.

📤 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.

📨 p2faxm (int)

Implements the feature Print messages to File (P2F). This variable is used in conjunction with $p2fname and specifies a threshold on the number of messages per batch that will be sent to the terminal window. If the threshold is exceeded, the remaining messages get sent 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 it exists to prevent an accidental flood of server messages scrolling all over your 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

🗃️ 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. 📎

📦 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 — it takes effect upon the next \reconnect command.

Specifying a value of NULL indicates that the default packet size is desired.

🤫 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 $password variable will always expand to the string "*password*". 🔐

🔁 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.

🎫 principal (string)

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

See also the discussion on Kerberos Support.

💬 prompt (string)

This variable is used by sqsh to build your current prompt. Any variables contained within $prompt are expanded each time the prompt is displayed.

The default value for this is '${lineno}> '.

See also the discussion on color support below. 🌈

💭 prompt2 (string)

The contents of this prompt are expanded and displayed during interactive use when sqsh requires additional input — such as during a line continuation.

The default value is '--> '.

query_timeout (int) — how many seconds we wait for a query to actually hand back data before we're like "okay bestie, time's up" and abort (cancel) it. Also peep $max_timeout and the -Q command line option, they're in this together. ⏰
rcfile (path) — a colon (:) delimited list of sqsh resource (sqshrc) files. Default vibe is /etc/sqshrc and then $HOME/.sqshrc). 💖
readline_histignore (string) — if readline support got compiled into sqsh, this is how you gatekeep which commands or statements get filtered out of the readline history file named by $readline_history. It's either a colon (:) separated string, or a string holding a regular expression flagged by "RE:". Like, for example:

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

That regex in the second example kicks off with RE: and honestly you should wrap the string in quotes and double quotes so the pipe characters don't cause expansion and display chaos. The regular expression is evaluated case insensitive. 🔍

readline_history (string) — if readline support got compiled into sqsh, the readline line-by-line history gets written into the file this variable names. Default is $HOME/.sqsh_readline. 📜
readline_histsize (int) — if readline support got compiled into sqsh, this value says how many lines get saved in that readline line-by-line history. Set it to 0 and literally every line is saved. Default is 100. 🧮
real (float-format) — sets the display format (the precision and the scale) for every real value sqsh shows you. Default is '18.6'. Heads up tho: values that exceed the defined precision are not truncated, so setting this too low can make columns in a result set go all miss-aligned. 😬
repeat_batch (boolean) — set it to On or True and a \go run with an empty SQL Buffer will re-execute the previous batch. Rerun energy. 🔁
secmech (string) — the name of the security mechanism used for user authentication, for instance csfkrb5. With the -Z option you can name the security mechanism, or if no secmech is supplied the OpenClient falls back to the default, i.e. the first security entry from the $SYBASE/$SYBASE_OCS/config/libtcl.cfg file. Note that whatever name you use has to match the name of the corresponding secmech entry in the objectid.dat file. Once the connection to the server is set up, the actual secmech name gets stashed in the $secmech variable. 🔐
secure_options (string) — describes the security options used for user authentication security mechanisms. Corresponds to the -V startup option.

Also go read the paragraph on Kerberos Support. 🎫

script (string) — if sqsh is run with the -i flag, this variable holds the name of the script being executed. 📄
semicolon_cmd (string) — when $semicolon_hack (below, bestie) is enabled, the contents of this variable get executed whenever a semicolon shows up in the SQL Buffer. Defaults to the string '\go'. ✨
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 the COMMANDS section, In-Line \go. 🙈
semicolon_hack2 (boolean) — toggles on the ability to use multiple ; as an in-line command terminator. See the COMMANDS section, In-Line \go. 💅
session (string) — where the session file lives, the one that gets processed right before connecting to a server. The variable gets expanded, so it's allowed to hold environment variables. 🌸
SHELL (string) — the name of the shell used to execute pipes and used by the \shell command (default '/bin/sh'). 🐚
statistics (boolean) — setting $statistics to 1 makes timing statistics pop up after every batch of SQL runs successfully. You can also set this via the -p command line flag, or by handing -p to the \go command. $statistics defaults to 0. 📊
style (string) — picks the result set display style. Currently eight styles are supported:
- The horiz style (which may also be written as hor or horizontal) closely resembles the output of isql, with that traditional columnar output. 👑
- The vert (or vertical) style rotates the output so every line is a column name followed by a column value. Chef's kiss for staring at particularly wide output. 🔄
- The bcp style shows results in a format that's amenable to bcp'ing the result set back into another table. That is, every column value is separated by $bcp_colsep, with the final column separated by $bcp_rowsep followed by a newline (\n). If $bcp_colsep or $bcp_rowsep aren't defined then '|' is used as the default separator. Note this output does not play nice with COMPUTE columns, and it uses the default conversion methods for all data types (that is, datetime columns may truncate the millisecond). ⚠️
- The csv display style outputs all result sets as a comma separated construct. This mode is ideal for importing result sets into spreadsheet programs, for instance. 📈
- The html display style outputs all result sets as an HTML <TABLE> construct. This mode is ideal for using sqsh as a CGI application. 🌐
- The meta display style outputs only the meta-data information associated with the result and tosses the actual row results. This mode is useful for debugging the result sets generated from a full passthru Open Server gateway, or for anyone curious about what's really coming back from the server. 👀
- The pretty display style generates a fluffy table-like output using regular ASCII characters for borders. This mode does not do any explicit column wrapping, like the horiz display mode does. However, the $colwidth variable can be used to control the maximum width of a given column on the screen. If the column goes past $colwidth characters wide, it gets wrapped in a relatively visually appealing manner. Note that $colwidth may be exceeded if there's enough screen width to hold the columns without wrapping. 🎀
- The 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 paired with the -p flag (or the $statistics variable) for gathering accurate performance statistics. 🚫
term_title (string) — when this variable is set, sqsh will reset the name of the current terminal (xterm) window it's 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 $term_title variable. 🪟
thresh_display (int) — sets the minimum SQL Server error severity that will display a message to the user. Default is 0 and valid ranges are between 0 and 22, inclusive. 🔔
thresh_exit (int) — defines the maximum number of errors of severity level $thresh_fail that may be encountered before sqsh aborts. Useful primarily for non-interactive scripts, but it's allowed on an interactive session. Setting $thresh_exit to 0 disables this feature. See the EXIT STATUS section for details. 🚪
thresh_fail (int) — sets the minimum SQL Server severity level that counts as 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 $thresh_fail is crossed, the variable $batch_failcount gets incremented by 1. See the EXIT STATUS section for details. 💀
time (date-spec) — this variable may be set with a time format (see the man page for 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). ⌚
timefmt (date-spec) — this variable may be set with a time format similar to $time and is used to control the display format of all SQL Server TIME and BIGTIME columns. (Similar to $datetime.) Note that only 63 bytes are available in total for the expanded string and the remainder will be truncated. ✂️
tmp_dir (path) — this holds the directory where temporary files used internally by sqsh get written. Those files are generated either during buffer editing (the \buf-edit command), or to maintain output defer files for background jobs. The default value for this variable is /tmp. 🗂️
usedbcheck (boolean) — when this variable is set to 'On' (default is 'Off') and a database name is provided with the -D parameter while sqsh is run in batch mode, i.e. non-interactive, then a check is performed to see if the specified database is accessible. Otherwise sqsh is aborted with exit code 254. And also this error message shows up:

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

This exists to prevent a SQL script from inadvertently being executed in the wrong database, usually the default database of the login (master for example). 🛑

username (string) — the name of the user currently connected to the database. 🫶
version (none) — this read-only variable holds the current version number. (sqsh-2.1.7) 🏷️
width (int) — the current width of the SQL output. 📏
xgeom (string/int) — if X11 support is compiled into sqsh, this value configures the default window size (in characters) of the X display. This variable has to be in the format WxH or just W, where W is the width of the window and H is the height of the window. If the height of the window isn't supplied, then 25 lines is assumed. If $xgeom isn't set, then $width is used as the default width and the height is assumed to be 25. If neither is set, then 80x25 is assumed. 🖼️
xwin_title (string) — if X11 support is compiled into sqsh and an X11 result window is requested by using the -x parameter of the \go or \rpc commands, then this variable sets the title name of the window. Can be temporarily overridden with the -T option of the \go and \rpc commands. 🌟

✨ Script Execution ✨

okay bestie, so like most shells, sqsh lets you run a whole file full of SQL and script commands directly — using the magical UNIX #! convention. 🪄 On most UNIX platforms, the moment the OS peeks the bytes #! as the first two bytes of an executable file, it literally pipes that file through whatever interpreter you named right after the #!. iconic behaviour, honestly.

So say you wanna make an executable sqsh script that runs sp_who — you just make a file like this:

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

and boom 🎀 if your sp_who script gets executed directly, it'll automatically launch /usr/bin/sqsh -i sp_who for you. no extra steps, no cap.

And because the vibes weren't already immaculate, sqsh also supports positional parameters, just like most shells, written as ${n} — which expands to the *n*th argument to your sqsh script. for example:

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

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

⚠️ important, bestie: positional parameters must be contained between braces to avoid conflicts with the T-SQL money data type. without the braces, the variable will not be expanded. the braces are not optional, they are the whole point. 🫶

🔥 EXIT STATUS 🔥

One of the major complaints of isql is that it provides no facility to detect when an error condition occurred while it is performing processing. sqsh said "not on my watch" and provides a rather complex, but flexible mechanism for returning meaningful information concerning its reason for exit in the form of an exit status (see exit(3)). 💀✨

When 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
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:

$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 set showplan on.
$thresh_fail — This variable is used by the error handler to determine which error severity is considered by 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.
$batch_failcount — This variable should be considered read-only, and contains the total number of times that batches have caused an error of severity $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. 🙈 so like, don't.
$thresh_exit — This variable is used to determine the limit at which $batch_failcount will cause sqsh to exit. If $thresh_exit is 0, then this feature is disabled. In other words, if $batch_failcount = $thresh_exit and $thresh_exit is greater than 0, then sqsh will exit, returning $batch_failcount as an exit status.

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

$exit_failcount — This variable is used only when sqsh would normally exit with a success status (0); this causes it to instead exit with a value of $batch_failcount (which may, itself, be 0).
$exit_value — When this variable is non-zero, it will override the $exit_failcount and $batch_failcount values and just exit the value of $exit_value. This variable may be explicitly set using the \set command, or implicitly using \exit x and its aliased counterparts. main character energy, honestly.

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

0 — No error has been encountered.
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). 😭
254 — An explicit \abort was called, or a SIGINT (^C) was issued during a non-interactive session.
255 — A general error condition has occurred, such as a bad command line argument to sqsh, memory allocation failure, file access error, etc.

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 $exit_value variable contains 0 in the next examples.

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, sqsh will abort with an exit status of 1, or the total number of failures (the $batch_failcount variable). However, if nothing goes wrong during the whole process, a zero is returned. 🌸
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, sqsh will exit with a status of 3. However if 0, 1, or 2 batches fail, then 0 is returned.
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. lowkey the move for scripts. ✨
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 sqsh to exit with a failure condition of 1.
thresh_display=0, thresh_fail=0, thresh_exit=3, exit_failcount=1 — This is identical to the second example, above, however sqsh will return the total number of batches that failed even if $batch_failcount does not reach 3.
exit_value=nonzero — This will override all conditions explained above and just exit the specific value. periodt. 💅

🌈 MISCELLANEOUS 🌈

🎨 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. 🦋

🍓 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 sqsh variables described above.

💀 BUGS 💀

The addition of flow-of-control expressions has extended sqsh 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. 😭 ngl bestie, keep it short.

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, 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

and that's a wrap, bestie 🫶✨ this whole page was sqsh(1), version 2.5, dated 2014-03-12 — go forth and query slayfully 💅👑

📦 where this page comes from

ubuntu jammy: sqsh 2.5.16.1-2build2
💖 girlified by deepseek-ai/DeepSeek-V4.1-Flash on 2026-10-18: a language model's rewrite of the original, under its terms

sqsh(1)

✨ NAME ✨

🌸 SYNOPSIS 🌸

💜 DESCRIPTION 💜

💅 OPTIONS 💅

🌸 Initialization 🌸

💬 Command line 💬

🎀 Comments 🎀

🌈 Quoting 🌈

✨ Expansion ✨

💜 Variable Substitution 💜

🧚 Command Substitution 🧚

Input/Output Redirection 🎀

Pipes 🌈

Background Jobs 💫

Deferred

Non-Deferred

Buffers 🎀

Buffer Short-Hand

Variables

Command Substitution

🌈 FLOW-OF-CONTROL 🌈

🧱 Blocks & SQL Buffers 🧱

🌸 Expressions 🌸

🚫 Unsupported Expressions 🚫

💖 \if statement 💖

🔁 \while statement 🔁

🔂 \for statement 🔂

🔄 \do command 🔄

⚙️ Command line options ⚙️

🗂️ Column variables 🗂️

🛑 Aborting 🛑

🧚 \func command 🧚

⚙️ Command line options ⚙️

🎀 Function variables 🎀

💫 Return value 💫

🎫 Kerberos Support 🎫

🌈 COMMANDS 🌈

💖 Read-Eval-Print 💖

🗄️ Database Access 🗄️

🌀 \reconnect

🏃 \run

🗂️ \lcd dirname

📍 \pwd

📂 \ls

⏰ \snace

🚀 \go [options] [xacts]

🔥 \bcp [bcp_options] table[:slicenumber|:partition name]

\rpc — the "just call the procedure directly" icon 💫

Display Options ✨

Parameter Options 💅

Buffers 🦋

Variables 🌈

🎀 Job Control 🎀

Aliasing

Miscellaneous

Aliases

In-Line \go

✨ SPECIAL VARIABLES ✨

Variable Data types

boolean</p> 💖

path</p> 🦋

int</p> 🔢

date-spec</p> 🎀

float-format</p> 🌸

✨ Variables ✨

💖 $? (int)

💖 $# (int)

💖 $* (string list)

💖 ${0}...${N} (int)

💖 $$

💖 DISPLAY

💖 appname

💖 autouse (string)

💖 banner (boolean)

💖 batch_failcount (int)

💖 batch_pause (boolean)

💖 bcp_colsep (string)

💖 bcp_rowsep (string)

💖 bcp_trim (boolean)

💖 `\if` statement 💖

🔁 `\while` statement 🔁

🔂 `\for` statement 🔂

🔄 `\do` command 🔄

🧚 `\func` command 🧚

🌀 `\reconnect`

🏃 `\run`

🗂️ `\lcd` dirname

📍 `\pwd`

📂 `\ls`

⏰ `\snace`

🚀 `\go` `[options] [xacts]`

🔥 `\bcp` `[bcp_options]` `table[:slicenumber|:partition name]`

`\rpc` — the "just call the procedure directly" icon 💫

`boolean`</p> 💖

`path`</p> 🦋

`int`</p> 🔢

`date-spec`</p> 🎀

`float-format`</p> 🌸

💖 `$?` (int)

💖 `$#` (int)

💖 `$` (string list)*

💖 `${0}...${N}` (int)

💖 `$$`

💖 `DISPLAY`

💖 `appname`

💖 `autouse` (string)

💖 `banner` (boolean)

💖 `batch_failcount` (int)

💖 `batch_pause` (boolean)

💖 `bcp_colsep` (string)

💖 `bcp_rowsep` (string)

💖 `bcp_trim` (boolean)

💖 `builddate` (none)

💖 `buildtime` (none)

💖 `chained` (boolean)

💖 `charset` (string)

💖 `clear_on_fail` (boolean)

💖 `colsep` (string)

💖 `colwidth` (int)

💖 `database` (string)

💖 `date` (date-spec)

💖 `datefmt` (date-spec)

💖 `datetime` (date-spec)

💖 `debug` (string)

💖 `debug_tds_capture` (string)