Free Database Tools for Linux - Part Two: SQSH

I ran across SQSH when I was building the Perl DBD:Sybase module to connect to MS SQLServer databases for monitoring purposes. Both SQSH and DBD:Sybase can use a set of free libraries to connect to SQLServer instead of the Sybase client libraries. I thought this was pretty darn cool. I also thought it was cool that a developer or sysadmin working at a Linux workstation could connect to a SQLServer DB using a native command line interface. So let’s rock!

Installing the FreeTDS libraries

First download the FreeTDS libraries. There is also some good documentation on the site. The latest version as of this writing is version 0.61. So download freetds-0.61.tgz and untar it. On our Red Hat 7.3 system, we built it like so:

[usr-3@srv-3 freetds-0.61]$ ./configure --prefix=/usr/local/freetds \
--enable-msdblib --with-tdsvr=7

This will install it under /usr/local/freetds and is built with SQLServer-centric options. Now a simple make, then make install as root. FreeTDS has a file kind of like Oracle’s tnsnames where services are defined. It is (using our install location) /usr/local/freetds/etc/freetds.conf. In this file, you must define each SQLServer or Sybase server you want to connect to. Here is a sample server entry for a server running SQLServer 7:

[MICKEY]
host = mickey.domain.com
port = 1433
tds version = 7.0

Now we’ve got OpenTDS all set up, so let’s install SQSH.

Installing SQSH

Download SQSH at http://www.sqsh.org. We’re going to build it from source, although there are some binary packages available on the site. We are building version 2.1. Untar the package, cd sqsh-2.1. Then…

[usr-3@srv-3 sqsh-2.1]$ export SYBASE=/usr/local/freetds
[usr-3@srv-3 sqsh-2.1]$ ./configure
-- output snipped -- 
[usr-3@srv-3 sqsh-2.1]$ make
--snip--
[usr-3@srv-3 sqsh-2.1]$ su
Password: 
[root@srv-3 sqsh-2.1]# make install
make[1]: Entering directory `/u01/src/sqsh-2.1/src'
make[1]: `sqsh' is up to date.
make[1]: Leaving directory `/u01/src/sqsh-2.1/src'
[root@srv-3 sqsh-2.1]# make install-wrapper
make[1]: Entering directory `/u01/src/sqsh-2.1/src'
make[1]: `sqsh' is up to date.
make[1]: Leaving directory `/u01/src/sqsh-2.1/src'
WARNING: You already have the file /usr/local/etc/sqshrc
installed. This file will be left untouched. The version
supplied with this installation will be installed as
/usr/local/etc/sqshrc.new
root@srv-3 sqsh-2.1]# ls -l /usr/local/bin/sqsh
-rwxr-xr-x    1 root     root          287 May 22 13:25 /usr/local/bin/sqsh
[root@srv-3 sqsh-2.1]# ls -l /usr/local/bin/sqsh.bin
-rwxr-xr-x    1 root     root       651472 May 22 13:25 /usr/local/bin/sqsh.bin

By running make install-wrapper, we created a wrapper script for sqsh which sets up the SYBASE environmental variable and library path for us when we run SQSH. SQSH also includes another file, /usr/local/etc/sqshrc, for configuring preferences. The defaults are fine for our purposes. Let’s fire it up (although it’s not as if the DBA on the MS side is any more likely to reinstate my DB privileges than the Oracle DBA is!) Still, we may be able to complete a query or two before they sound the alarm.
SQSH doesn’t come with a man page, but there is pretty good assistance with the –help flag.

[usr-3@srv-3 sqsh-2.1]$ sqsh -S mickey -U usr-3
sqsh-2.1 Copyright (C) 1995-2001 Scott C. Gray
This is free software with ABSOLUTELY NO WARRANTY
For more information type '\warranty'
Password: 
[3] mickey.tempdb.1> 
[3] mickey.tempdb.1> select count(*) from sysusers;
-----------
13
(1 row affected)
[4] mickey.tempdb.1>

We’re golden. Remember, we can only connect to servers we’ve configured in OpenTDS. Now you have a sqlplus-like tool for SQLServer. Enjoy!

Free Database Tools for Linux – Part Two: SQSH

Information

About