使用 UNIX 脚本在 ASEISQL 中执行 SQL 语句

发布于 2024-12-06 23:56:05 字数 440 浏览 1 评论 0原文

因为我是 unix 脚本编写的新手。我正在 ASE ISQL 中运行 SQL 语句，如果 SQL 语句给出一些结果，那么我需要将该结果邮寄给特定用户。如果 SQL 未返回任何结果，则不应发送邮件。
我编写的示例脚本是：

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

请让我知道我哪里出错了？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

瘫痪情歌 2024-12-13 23:56:05

我认为您需要将 SQL 的输出捕获到 shell 变量中，然后在发送电子邮件之前测试结果，大致如下：

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

我假设 isql 程序只会打印数字而不打印数字任何标题或其他信息。如果它更详细，那么你必须做更敏感的测试。

另请注意，COUNT(*) 比“选择所有内容并计算有多少行”版本更快、更准确。

实际上我的问题是，如果我的 SQL 语句返回任何结果，那么只有该结果集应该通过邮件发送。

然后我会使用：

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

这将结果捕获到文件中。如果文件不为空 (-s)，则它将文件作为电子邮件正文发送。请把话题换成更有意义的话题。另外，您确定将公司电子邮件发送到 Gmail 帐户是个好主意吗？

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?

回复收藏 0 原文

~没有更多了~