SQSH - Some tips

In this post I would like to discuss about a nifty utility that can run from a Linux server and connect to the SQLserver DB to connect and execute the SQL. The SQSH is shortname for SQshell and is a replacement of SYBASE's isql.

You can download SQSH from - http://www.sqsh.org/

Once you download and install on the Linux machine you can execute the SQSH by "sqsh" command and is located in /usr/bin/sqsh.

Now to connect to the SQL Server from a Linux machine as:

/usr/bin/sqsh -D<name of the DB> -S<IP addres of the SQL Server Machine> -U<username for the DB" -P<password for the DB>

so for example, for a test DB with test user and test password with 10.10.10.10 as an IP address of teh SQL Server machine - it will look like:

/usr/bin/sqsh -DTEST -S10.10.10.10 -Utest -Ptest

There are some output options for teh SQSH that are very good. Like output as HTML or and SPread Sheet. Here is the example for that:

HTML Output:

/usr/bin/sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mhtml

For Spreadsheet, it will be:

/usr/bin/sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mbcp
>\set bcp_colsep=","
>\set bcp_rowsep=""

Now if you need to put the output to a file and wants to execute this in a UNIX Shell, it woul look like:

/usr/bin/sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mhtml <<QRY
use test
select sysdatetime();
\go -f
quit
QRY

In the above example - it is best to display the result in the browser calling the script from CGI-BIN with web server. A typical shell script for the purpose would be:

#!/bin/sh
# Testing the Script
echo "Content-type: text/html"
echo ""
echo "<head></head> <body>"
echo " "
sqsh=/usr/bin/sqsh
$sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mhtml <<QRY
use test
select sysdatetime();
\go -f
quit
QRY
echo "</body>"

The output file example and in the script:

#!/bin/sh
# Testing the Script
echo "Content-type: text/html"
echo ""
aaj=`date +%F`
fl=/tmp/test$aaj.html
fl1=/tmp/test$aaj.html
echo "<head></head> <body>"
echo " "
sqsh=/usr/bin/sqsh
$sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mbcp -o$fl1 <<QRY
use test

\set bcp_colsep=","
\set bcp_rowsep=""

select sysdatetime();
\go -f
quit
QRY
echo " "
$sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mhtml -o$fl <<QRY1
use test
select sysdatetime();
\go -f
quit
QRY1
echo "</body>"

In SQSH, one can create the .sqshrc and put some command like you do not have to type \go to execute the query. However, when you want to access this script from the browser via cgi-bin then one need to do it as stated above or create the .sqshrc in the root directory of the WEBROOT or test it that way.

So let me talk about the turning off and on the feedback "like 100 rows returned - called footer and header in SQSH"

so the go command can be used as:

\go |more -- diaply and see the results one page at a time
\go -mhtml -- html display
\go -mbcp -- csv file display
\go -f -- turn off the footer display
\go |(cd /tmp; gzip >test.gzip) -- Compress the output in a directory
\go & -- In Background
\go -d display == Display in X11
\go -f -- turn off the column header

Then there is a loop command that can be used for user prompts and other things. Details can be found in the man pages or from the web site.

One last think when you direct the output to a file , it is required for you to print the column name - or may be you want some alias column name. To do this, you do something like:

From my shell example above:

$sqsh -DTEST -S10.10.10.10 -Utest -Ptest -mbcp -o$fl1 <<QRY
use test
\echo SystemDateTime
select sysdatetime();
\go -f
quit
QRY

If you have multiple column to display then you do:

\echo column1,column2,column3

Now you can access SQL Server from Linux without logging into Windows machine or remote desktop to it.

Hope it will be helpful a bit !!

SQSH - Some tips

Friday, December 30, 2011

SQSH Tips