How to extract the sybase sql query output in a shell script

Question

I am trying to execute a SQL query on SYBASE database using shell script.
A simple query to count the number of rows in a table.

#!/bin/sh

[ -f /etc/bash.bashrc.local ] && . /etc/bash.bashrc.local
. /gi/base_environ
. /usr/gi/bin/environ
. /usr/gi/bin/path

ISQL="isql <username> guest"    

count() {
VAL=$( ${ISQL} <<EOSQL
set nocount on
go
set chained off
go
select count(*) from table_name
go
EOSQL
)
echo "VAL : $VAL"
echo $VAL | while read line
do
 echo "line : $line"
done
}

count

The above code gives the output as follows

VAL : Password:
-----------
      35
line : Password: ----------- 35

Is there a way to get only the value '35'. What I am missing here? Thanks in advance.

Answer 1

The "select count(*)" prints a result set as output, i.e. a column header (here, that's blank), a line of dashes for each column, and the column value for every row. Here you have only 1 column and 1 row. If you want to get rid of the dashes, you can do various things:

• select the count(*) into a variable and just PRINT the variable. This will remove the dashes from the output
• perform some additional filtering with things like grep and awk on the $VAL variable before using it

As for the 'Password:' line: you are not specifying a password in the 'isql' command, so 'isql' will prompt for it (since it works, it looks like there is no password). Best specify a password flag to avoid this prompt -- or filter out that part as mentioned above. Incidentally, it looks like you may be using the 'isql' from the Unix/Linux ODBC installation, rather than the 'isql' utility that comes with Sybase. Best use the latter (check with 'which isql').