Sqsh is a sql shell and a far superior alternative to the isql
program supplied by Sybase. It's main advantage is that it allows you to combine sql and unix shell commands! Here are a few reason why I love it:
1. Pipe data to other programs
You can create a pipeline to pass SQL results to an external (or unix) program like less
, grep
, head
etc. Here are a few examples:
# pipe to less to browse data
1> select * from data; | less
# a more complex pipeline which gzips data containing a specific word
2> select * from data; | grep -i foo | gzip -c > /tmp/foo.gz
# this example shows the use of command substitution
3> sp_who; | grep `hostname`
2. Redirect output to file
Just like in a standard unix shell, you can redirect output of a sql command to file:
# write the output to file
1> sp_helptext my_proc; > /tmp/my_proc.txt
3. Functions and aliases
You can define aliases and functions in your ~/.sqshrc
file for code that you run frequently. Some of mine are shown below. (Visit my GitHub dotfiles repository to see my full .sqshrc
.)
\alias h='\history'
# shortcut for select * from
\func -x sf
\if [ $# -eq 0 ]
\echo 'usage: sf "[table [where ...]]"'
\return 1
\fi
select * from $*; | less -F
\done
# count rows in a table
\func -x count
\if [ $# -eq 0 ]
\echo 'usage: count "[table [where ...]]"'
\return 1
\fi
select count(*) from $*;
\done
You can invoke them like this:
# select * from data table
1> sf "data where date='20140306'"
# count the rows in the employee table
2> count employee
# list aliases
3> \alias
4. History and reverse search
You can rerun a previous command by using the \history
command or by invoking reverse search with Ctrl+r
:
1> \history
(1) sp_who
(2) select count(*) from data
(3) select top 10 * from data
# invoke the second command from history
2> !2
# invoke the previous command
3> !!
# reverse search
4> <Ctrl+r>
(reverse-i-search)`sp': sp_who
4> sp_who
5. Customisable prompt
The default prompt is ${lineno}>
, but it can be customised to include your username and database, and it even supports colours. It would be nice if there was a way to change the colour based on which database you were connected to (for example, red for a production database), but I haven't been able to figure out if this is possible yet. Here is my prompt, set in my ~/.sqshrc
:
\set prompt_color='{1;33}' # yellow
\set text_color='{0;37}' # white
\set prompt='${prompt_color}[$histnum][$username@$DSQUERY.$database] $lineno >$text_color '
6. Different result display styles
sqsh
supports a number of different output styles which you can easily switch to. The ones I frequently use are csv
, html
and vert
(vertical). Here is an example:
1> select * from employee; -m csv
123,"Joe","Bloggs"
2>select * from employee; -m vert
id: 123
firstName: Joe
lastName: Bloggs
7. For-loops
A for-loop allows you to iterate over a range of values and execute some code. For example, if you want to delete data, in batches, over a range of dates, you can use a for-loop like this:
\for i in 1 2 3 4 5
\loop -e "delete from data where date = '2014020$i';"
\echo "Deleted 2014020$i"
\done
8. Backgrounding long-running commands
If you have a long-running command, you can run it in the background by putting an &
at the end of the command. You can then continue running other commands, whilst this one runs in the background. You will see a message when the background command completes and you can use \show
to see the results. Here is an example:
# run a command in the background
1> select * from data; &
Job #1 running [6266]
Job #1 complete (output pending)
# show the results of the backgrounded command
3> \show 1
Further information:
You can download sqsh
here and then read the man
page for more information.
You can take a look at my .sqshrc
in my GitHub dotfiles repository.