isql

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?
4.5    How can I make isql secure?
4.6    How do I pipe the output of isql to a file?

4.1: Hiding your password to isql

Note: Newer versions of isql will automatically hide the contents of the -P command line switch for isql from the Operating System, so the methods below are unnecessary. However, they are valid for older versions of isql.

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:

Single ASE on host

• Script #1

  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

• Script #2

  #!/bin/sh
  umask 077
  cat <<-endOfCat | isql -Umyuserid -Smyserver
  	mypassword
  	use mydb
  	go
  	sp_who
  	go
  	endOfCat

• Script #3

  #!/bin/sh
  umask 077
  cat <<-endOfCat | isql -Umyuserid -Smyserver
  	`myScriptForGeneratingPasswords myServer`
  	use mydb
  	go
  	sp_who
  	go
  	endOfCat

• Script #3

   
  #!/bin/sh
  umask 077
  isql -Umyuserid -Smyserver <<-endOfIsql
  	mypassword
  	use mydb
  	go
  	sp_who
  	go
  	endOfIsql

• Script #4

   
  #!/bin/sh
  umask 077
  isql -Umyuserid -Smyserver <<-endOfIsql
  	`myScriptForGeneratingPasswords myServer`
  	use mydb
  	go
  	sp_who
  	go
  	endOfIsql

• Script #5

   
  #!/bin/sh
  echo 'mypassword
  use mydb
  go
  sp_who
  go' | isql -Umyuserid -Smyserver

• Script #6

   
  #!/bin/sh
  echo "`myScriptForGeneratingPasswords myServer`
  use mydb
  go
  sp_who
  go" | isql -Umyuserid -Smyserver
  

• Script #7

  #!/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}

Multiple ASEs on host

Again, assuming that you are using bourne shell as your scripting language, you can do the following:

1. Create a global file. This file will contain passwords, generic functions, master device for the respective DSQUERY.
2. In the actual scripts, source in the global file.

• Global File>

  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
  }

• Generic Script

  #!/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

Back to top

4.2: How to remove row affected and dashes

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.

Back to top

4.3: How do I pipe the output of one isql to another?

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.

Back to top

4.4: Are there any alternatives to isql?

sqsh

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:

1. command line editing;
2. command history;
3. ability to pipe to standard filters;
4. ability to redirect output to X window;
5. shell variables
6. background execution;

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 the official SQSH website http://www.sqsh.org.

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.

• dsql
• asql
• ctsql
• qisql

However, I suspect that provided we have sqsh, no other command line version is needed!!

SQL Advantage

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.

jisql

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.

tsql

This is the command line client that comes with FreeTDS. It comes with the FreeTDS client (http://www.freetds.org). It is a very simple client, but it works.

ASSE

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.

wisqlite

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.

ntquery

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

DWB

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.

Back to top

4.5: How do I make isql secure?

isql uses the open/client libraries, which have no built in means of securing the packets that I know of. However, it is possible to use ssh to do all of the work for you. It is really quite straightforward. I saw this first published on the Sybase-L list by Tim Ellis, so all of the credit gos to him.

1. You will need a server running sshd that you have access to, which also has access to the ASE server.
2. Choose a port that you are going to make your secure connection from. Just like all ASE port selections it is totally arbitrary, but you if you were setting up a number of these, then you might want to think about a strategy. Regular server + 100 or something. Just make sure that it does not, and will not, clash with any of your regular servers.
3. Edit the interfaces file on the client side and set up a new server with an IP address of localhost and the port number you chose in the previous point. You might want to call it SERVER_SSH just to make sure that you know that it is the secure one.
4. Run the following ssh command:
   ssh -2 -N -f -L port_chosen_above:remote_server:remote_port user@ssh.server.com
5. Connect to the server using isql -Uuser -SSERVER_SSH

In the ssh line, the -2 means use that version of the protocol (obviously it must be supported by your client and server). -f forces the ssh into the background. Not supported by version 1 only clients. -N means do not prompt for input. Again, this is not supported by version 1 clients.

The user@ssh.server.com refers to the sshd server that you have access to.

Let us look at an example. You have a server running ASE on port 4100. (Make sure that this port is *not* visible from the outside world, otherwise it is wide open to people attacking it directly.) I have not tried all of the ins and outs of this, I am happy to take advice, but on this same machine you have a copy of sshd running that you can see from the outside world.

Choose another port that you are going to have as your secure port. Let's call it 5100 for the sake of argument. Edit the interfaces file on the client machine (which is presumably somewhere in untrusted land, say a client site) and add a new server, lets call it MYSERVER_SSH and have it listen on localhost,5100.

Now execute the ssh magic, again from the client machine:

  ssh -2 -N -f -L 5100:myserver.com:4100 sybase@myserver.com

Now connect to it using

  isql -Usa -SMYSERVER_SSH

and you should get the familiar 1> prompt. All traffic to and from the server is going via an SSH tunnel, and so can be considered relatively secure.

4.6: How do I pipe the output of isql to a file?

Fortunately this is one of the simple ones. For batch sessions, simply use the the -o command line switch to specify the name of the output file. For interactive sessions, the simplest method is turn on the capture functionality of your xterm window while running the query. If this isn't possible (i.e., you are not running on UNIX), then there are a couple of options:

• Use sqsh instead (see: section 4.4)
• Use the tee(1) command which takes the output from a command and writes/appends it to a file, as well as displaying it on the screen. (Again, this may be UNIX-specific; Windows users should research the CYGWIN toolset for similar functionality.)

Back to top