Since I am new to unix scripting. I am running a SQL statement in ASE ISQL, and if SQL statement gives some result then I need to mail that result to a particular users. And if SQL is not returning any result then mail should not be sent.
The Sample Script I have wriiten is:
#!/bin/ksh
isql -U$DBO -S$DSQUERY -D$DBNAME -P$PASSWORD << END
go
select * from 'Table'
go
if (@@rowcount !=0)
mailx -s "Hello" [email protected]
END
Please let me know where I am going wrong?
I think you need to capture the output of the SQL into a shell variable, and then test the result before sending the email, roughly like:
#!/bin/ksh
num=$(isql -U$DBO -S$DSQUERY -D$DBNAME -P$PASSWORD << END
select count(*) from 'Table'
go
END
)
if [ "$num" -gt 0 ]
then mailx -s "Hello" [email protected] < /dev/null
fi
I am assuming that the isql program will only print the number and not any headings or other information. If it is more verbose, then you have to do a more sensitive test.
Note, too, that COUNT(*) is quicker and more accurately what you're after than your 'select everything and count how many rows there were' version.
Actually my problem is if my SQL statement is returning any result then only that resultset should be sent in a mail.
Then I'd use:
#!/bin/ksh
tmp=${TMPDIR:-/tmp}/results.$$
trap "rm -f $tmp; exit 1" 0 1 2 3 13 15
isql -U$DBO -S$DSQUERY -D$DBNAME -P$PASSWORD << END > $tmp
select * from 'Table'
go
END
if [ -s $tmp ]
then mailx -s "Hello" [email protected] < $tmp || exit 1
fi
rm -f $tmp
trap 0
exit 0
This captures the results in a file. If the file is not empty (-s) then it sends the file as the body of an email. Please change the subject to something more meaningful. Also, are you sure it is a good idea to send corporate email to a Gmail account?