4.1 How do I hide my password using isql?
4.2 How do I remove row affected and/or dashes when using isql?
4.3 How do I pipe the output of one isql to another?
4.4 What alternatives to isql exist?
Here are a menagerie (I've always wanted to use that word) of different methods to hide your password. Pick and choose whichever fits your environment best:
Assuming that you are using bourne shell sh(1) as your scripting language you can put the password in a file and substitute the file where the password is needed.
#!/bin/sh # invoke say ISQL or something... (cat $HOME/dba/password_file cat << EOD dbcc ... go EOD ) | $SYBASE/bin/isql -Usa -w1000
#!/bin/sh umask 077 cat <<-endOfCat | isql -Umyuserid -Smyserver mypassword use mydb go sp_who go endOfCat
#!/bin/sh umask 077 cat <<-endOfCat | isql -Umyuserid -Smyserver `myScriptForGeneratingPasswords myServer` use mydb go sp_who go endOfCat
#!/bin/sh umask 077 isql -Umyuserid -Smyserver <<-endOfIsql mypassword use mydb go sp_who go endOfIsql
#!/bin/sh umask 077 isql -Umyuserid -Smyserver <<-endOfIsql `myScriptForGeneratingPasswords myServer` use mydb go sp_who go endOfIsql
#!/bin/sh echo 'mypassword use mydb go sp_who go' | isql -Umyuserid -Smyserver
#!/bin/sh echo "`myScriptForGeneratingPasswords myServer` use mydb go sp_who go" | isql -Umyuserid -Smyserver
#!/bin/sh
echo "Password :\c "
stty -echo
read PASSWD
stty echo
echo "$PASSWD
waitfor delay '0:1:00'
go
" | $SYBASE/bin/isql -Usa -S${DSQUERY}
Again, assuming that you are using bourne shell as your scripting language, you can do the following:
SYBASE=/usr/sybase
my_password()
{
case $1 in
SERVER_1) PASSWD="this";;
SERVER_2) PASSWD="is";;
SERVER_3) PASSWD="bogus;;
*) return 1;;
esac
return 0
}
#!/bin/sh -a # # Use "-a" for auto-export of variables # # "dot" the file - equivalent to csh() "source" command . $HOME/dba/global_file DSQUERY=$1 # Determine the password: sets PASSWD my_password $DSQUERY if [ $? -ne 0 ] ; then # error! echo "<do some error catching>" exit 1 fi # invoke say ISQL or something... echo "$PASSWD dbcc ... go" | $SYBASE/bin/isql -U sa -S $DSQUERY -w1000
If you pipe the output of isql then you can use sed(1) to remove this extraneous output:
echo "$PASSWD
sp_who
go" | isql -U sa -S MY_SERVER | sed -e '/affected/d'
-e '/---/d'
If you simply wish to eliminate the row affected line use the set nocount on switch.
The following example queries sysdatabases and takes each database name and creates a string of the sort sp_helpdb dbname and sends the results to another isql. This is accomplished using bourne shell sh(1) and sed(1) to strip unwanted output (see Q4.2):
#!/bin/sh
PASSWD=yuk
DSQUERY=GNARLY_HAIRBALL
echo "$PASSWD print \"$PASSWD\"
go
select 'sp_helpdb ' + name + char(10) + 'go'
from sysdatabases
go" | isql -U sa -S $DSQUERY -w 1000 | \
sed -e '/affected/d' -e '/---/d' -e '/Password:/d' | \
isql -U sa -S $DSQUERY -w 1000
To help you understand this you may wish to comment out any series of pipes and see what output is being generated.
In my opinion, and that of quite a lot of others, this is the most useful (direct) replacement for isql that exists. It combines the usefulness of a good shell with database interaction. Looking for the ability to page the output of a long command? Look no further. Need to search a result set using a regular expression? This is the tool for you.
Like isql, sqsh is a command line tool. It supports all of the features and switches of isql with myriad of its own. There is one feature that isql has the sqsh does not, and that is the ability to read the password as the first line of an input file. If you look at a lot of the examples above, the password is piped in, sqsh does not support this with the latest release. I am not sure if this is a deliberate feature or not.
A quick summary of its features:
Like all good modern shells, sqsh supports command line editing. You need to have the GNU readline library available on your machine, but that is now becoming common. If you have the bash shell, you have it by default I believe.
Sqsh behaves very well if run in an X Windows environment. There is the direct support by way of an output switch to go that sends the results to an X Window, but it is much better than that. If you resize the screen sqsh also resizes its internal width to take advantage of the new size, just like any well behave X application. Doesn't sound like a lot, but when you want to see the results from a query and understand the output easily, much better if the columns all line up and don't wrap. With isql you would have to exit the program, run it again with an adjust '-w' flag and rerun the query.
Enough said. You need to try it! You can grab it from Scott Gray's website http://www.voicenet.com/~gray .
There are a host of others that I have heard about, but can no longer get to. Some are mentioned in various sites, mainly the sqsh site. If any of them are important, still being maintained, are actively supported, and are available somewhere, then let me know and I will update this list.
However, I suspect that provided we have sqsh, no other command line version is needed!!
This was Sybase's second attempt at a true GUI based SQL editor. It was only available for W86 platforms. Quite a lot of people liked it, it came free with Sybase and did just about the minimum necessary for an SQL Editor. Sadly, I cannot find my copy any more, since 12.5 for NT no longer has it. I have heard several unofficial channels say that Sybase will let you have a copy if you ask. I do not know since I have not asked.
Not having a copy, and having a bad memory, I cannot tell you all of its features. I cannot remember syntax highlighting or anything fancy like that, but that does not mean that it was not there. I know that there are some true devotees and if one of you cares to send me some words, I will slap them in here.
There was a GUI before SQL Advantage, but it is/was too dire to mention.
This is the latest release from Sybase for the desktop interactive shell. It uses Java, but you probably guessed that from the name. It works fine and is a little like SQL Advantage (which was a little like Data Work Bench, which was a ...), from what I remember of that tool. Correct me if I am wrong Anthony!!
The best thing about it is that it is available for all platforms that support Java.
The worst thing about it, and this is not so much a fault of jisql as a fault of Java in general, is that it is unable to use the interfaces file. I know that Java is intended to be truly multi-platform and that your average photocopier does not have access to environment variables, but how many photocopiers run Sybase? In most installations I can find my way totally painlessly from ASE server to ASE server, not worrying about ports etc. If you start using jisql regularly you will soon know the port numbers, since it is the only way that you can connect. Personally, until this is solved, I will not use the bloody tool.
Developed by Manish I Shah to be a direct replacement for Data Workbench, but in Java. It is still in alpha, I believe, at Sourceforge. Suffers the same pros and cons as jisql simply because of its Java heritage.
This is similar to jisql in its functionality, but is written in Tcl/Tk. I am not 100% sure of the status, but will update this paragraph when I am. Try Tom Poindexter's site for a starting point.
This is a very lightweight SQL Editor that is someway between Sybase's original offering (whose name I have had cleaned from my brain using hypnosis) and SQL Advantage. I am not sure who wrote it but it is free, runs on W86 platforms only and is available from ftp://ftp.midsomer.org/pub/ntquery.zip
The father of them all. I am not sure if this is officially allowed to circulate, but I know some people that still use it and like it. I am petitioning Sybase to allow me to make it available. It is only available for Sun, or at least the version that I have is Sun only, but it is quite a nice tool all the same.