DaniWeb IT Discussion Community - Issues emailing data from unix/sql script

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/index.php)

- Shell Scripting (http://www.daniweb.com/forums/forum113.html)

- - Issues emailing data from unix/sql script (http://www.daniweb.com/forums/thread115633.html)

skelly16

Mar 25th, 2008 1:27 pm

Issues emailing data from unix/sql script

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" "joe.blogs@hotmail.com"

eggi	Mar 26th, 2008 12:42 am

Re: Issues emailing data from unix/sql script

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" "joe.blogs@hotmail.com"

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

Mar 26th, 2008 6:16 am

Re: Issues emailing data from unix/sql script

no need to use cat

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

skelly16

Mar 26th, 2008 1:05 pm

Re: Issues emailing data from unix/sql script

Quote:

Originally Posted by eggi (Post 569232)

cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "joe.blogs@hotmail.com"

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" "joe.blogs@hotmail.com"

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" "joe.blogs@hotmai.com"

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

eggi	Mar 27th, 2008 12:46 am

Re: Issues emailing data from unix/sql script

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

Mar 27th, 2008 11:30 am

Re: Issues emailing data from unix/sql script

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	Mar 27th, 2008 2:48 pm

Re: Issues emailing data from unix/sql script

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	Mar 27th, 2008 2:56 pm

Re: Issues emailing data from unix/sql script

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

Mar 31st, 2008 5:43 pm

Re: Issues emailing data from unix/sql script

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" "joe.blogs@hotmail.com"

So conclusion:

Dont put hash into your EOF file???

eggi	Mar 31st, 2008 11:17 pm

Re: Issues emailing data from unix/sql script

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

All times are GMT -4. The time now is 6:40 am.