Issues emailing data from unix/sql script

[skelly16]

Hi All

Im having issues with my mail command sending data from my script.
I have a korn shell script which logs onto a database and does a few select statements.
The problem is i think the EOF stops my mail command sending the data. Im able to run the mail command separate which works once the script below has done its job.
Is there anyway i keep this all in the one script?

Script:

#!/bin/ksh

set -o xtrace
set -v
#variables

env='prod'
component='swift'
server=`db_attribute $env $component server`
username=`db_attribute $env $component username`
password=`db_attribute $env $component password`

file=/home/bin/scripts/a.xls

rm -f $file


db_rawamisql $env $component <<EOF > $file


select * from banks
(sql part)

go

EOF

cat $file | uuencode $file | /usr/bin/mailx  -s "TEST" "[e-mail]"

[eggi]

Hey There,

Do you have any error output you can post or does it just die quietly? Also, if you're running this in cron, the problem might be with it finding one of the commands in your cat to mail pipe - perhaps fully qualifying uuencode like you've fully qualified mailx would do the trick?

I don't think the EOF is causing you the issue, since that should complete the write to the file and looks like it's in perfect sequence.

ANother thing you can try is to put the

cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "[e-mail]"

line in an entirely separate script and call it from this script. That would definitely tell you if problem is just on this one line or if any other part of your script is influencing it.

Hope some of that helped :)

, Mike

[ghostdog74]

no need to use cat

uuencode $file $file | /usr/bin/mailx -s "TEST"

[skelly16]

Hey There,

Do you have any error output you can post or does it just die quietly? Also, if you're running this in cron, the problem might be with it finding one of the commands in your cat to mail pipe - perhaps fully qualifying uuencode like you've fully qualified mailx would do the trick?

I don't think the EOF is causing you the issue, since that should complete the write to the file and looks like it's in perfect sequence.

ANother thing you can try is to put the

cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "[e-mail]"

line in an entirely separate script and call it from this script. That would definitely tell you if problem is just on this one line or if any other part of your script is influencing it.

Hope some of that helped :)

, Mike

Hey Mike

I tried just the mail part from the command line and it worked fine

cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "[e-mail]"

I got an email with the excel attachment.

But with it included in the Unix script it just doesnt work, and it doesnt bomb out with an error either, really strange thats why i though EOF was causing the issue.

Also i created another script which does both steps successfully:

1) ./sql_unixscript.ksh (does the select query and unix part)
2) ./mail.sh (basically does what you stated mails the file)

Ideally i would just like the mail part to work within the one script rather than using more scripts to do the email part.

Output

go

EOF

/usr/bin/uuencode /home/bin/script/a.xls /home/bin/script/a.xls | /usr/bin/mailx -s "TEST" "[e-mail]"

1> /home/bin/script/a.xls

[eggi]

Hey Joe,

Looking at your output, I don't think your EOF is being matched and that's causing your issue.

For instance, I just wrote this quickly and ran it:

#!/bin/bash

cat <<EOF > OUTPUT

go

EOF
echo hi

-bash-3.2$ ./eof
hi

-bash-3.2$ cat OUTPUT

go

And everything comes out in order. I noticed in your output that the

"go"

shows up in the wrong place in your output and the file doesn't get created until after you try to send the file.

Even if I put the select statement in, I don't the same disruption in output.

What I'm thinking is it might be a character in the

(sql part)

Would it be possible to post that?

Otherwise, try this also, just in case the Here Document redirection is misinterpreting spaces and/or tabs in your script.

Change:

db_rawamisql $env $component <<EOF > $file

To:

db_rawamisql $env $component <<-EOF > $file

and see if that does anything for you. Give that a try before you make any other mods. The answer might be that simple.

Thanks for replying. Hopefully we'll get this sorted out soon :)

, Mike

[skelly16]

Hi Mike

I made the change to the EOF no difference.

Basically the "go" part is a sybase command to execute whatever you have queried etc

i.e. you connect to the database then put some sql and type go, this will then return data back.

So i.e. some example data for the sql part

select a.keyid,a.id_le,a.currency,wambalance,summarybal
into #COMP1
from #TACBAL a,
where a.keyid *= b.keyid

go

[eggi]

Hmm,

Stumper. It's strange because even the "EOF" is actually showing up in your output, which would suggest that something between the start of the Here Document and the end is short-circuiting it - could be just the xtrace throwing me off.

Can you try piping the commands to your sql and output, changing this:

db_rawamisql $env $component <<EOF > $file
select * from banks
select a.keyid,a.id_le,a.currency,wambalance,summarybal
into #COMP1
from #TACBAL a,
where a.keyid *= b.keyid
go
EOF

To this (just interested if that would work - I've done it this way with Oracle before, although slightly different - never used Sybase which might be a liability here ;)

(echo "select \* from banks";echo "select a.keyid,a.id_le,a.currency,wambalance,summarybal";echo "into #COMP1";echo "from #TACBAL a,";echo "where a.keyid \*= b.keyid";echo "go")|db_rawamisql $env $component >$file

Hope some of this is helpful :) Maybe doing it this way will show the error better (???) Also, you may not need to backslash the *'s, but I did that since I figure, doing it this way, the shell is sure to try and interpret it before it gives it to Sybase.

, Mike

[eggi]

Hey there,

One last suggestion. Could possibly be an issue with the # symbol within the here doc. Just noticed that as I re-read my reply. You may need to backslash those depending on your shell. Also, I believe that I've seen them cause issues in Here Documents before, but I believe the reaction (breaking the Here Document) was dependent on the program being used (for instance ftp vs cat will naturally do different things given the same lines in a Here Document).

I'll need to test some later and see if I can reproduce that in a different setting.

Until then, hope this gets resolved :)

, Mike

[skelly16]

Hi Mike

Good suggestion about the hash #

I did this and the script worked i received the email attachment, good call :)

create table tempdb..test ( A char(1))

insert into tempdb..test values ('A')

select * from tempdb..test

drop table tempdb..test

go > $file

EOF

/usr/bin/uuencode $file $file | /usr/bin/mailx -s "TEST" "[e-mail]"

So conclusion:

Dont put hash into your EOF file???

[eggi]

Sweet :) I'm glad that one got solved :)

I think the Hash mark is really dependent on what you're doing (the program you're using). When I first tried to replicate your results, I foolishly did this:

cat FILE <<EOF >OUTPUT
hi
there # how are ya
EOF

host # cat OUTPUT
hi
there # how are ya
host #

and that worked okay, so I initially dismissed it. It came to mind later because I found I had a problem with comments in an old script where I was doing "ftp" instead of "cat" :P

Anyway, great news :)

, Mike