Shell Scripting

Shell Scripting RSS

Issues emailing data from unix/sql script

Thread Solved

•

Join Date: Aug 2007

Posts: 34

Reputation: skelly16 is an unknown quantity at this point

Solved Threads: 0

skelly16

Offline

Light Poster

Issues emailing data from unix/sql script

Mar 25th, 2008

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:

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
#!/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"
#!/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"

Last edited by WolfPack; Mar 26th, 2008 at 9:38 am. Reason: Added code tags. USe them when you post code.

•

Join Date: Oct 2007

Posts: 399

Reputation: eggi will become famous soon enough

Solved Threads: 47

eggi

Offline

Posting Whiz

Re: Issues emailing data from unix/sql script

Mar 26th, 2008

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

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "joe.blogs@hotmail.com"
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

Linux and Unix Tips, Tricks and Individual Advice - The Linux and Unix Menagerie!
------------------------------------------------------------------------
The greatest viral marketing idea of all time, get your copy of this Free Report now!

•

Join Date: Apr 2006

Posts: 148

Reputation: ghostdog74 is on a distinguished road

Solved Threads: 39

ghostdog74 ghostdog74 is offline

Offline

Junior Poster

Re: Issues emailing data from unix/sql script

Mar 26th, 2008

no need to use cat

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
uuencode $file $file | /usr/bin/mailx -s "TEST"
uuencode $file $file | /usr/bin/mailx -s "TEST"

•

Join Date: Aug 2007

Posts: 34

Reputation: skelly16 is an unknown quantity at this point

Solved Threads: 0

skelly16

Offline

Light Poster

Re: Issues emailing data from unix/sql script

Mar 26th, 2008

•

Originally Posted by 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
Help with Code Tags

Shell Scripting Syntax (Toggle Plain Text)
cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "joe.blogs@hotmail.com"
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

Hey Mike

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

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
cat $file | uuencode $file | /usr/bin/mailx -s "TEST" "joe.blogs@hotmail.com"
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

•

Join Date: Oct 2007

Posts: 399

Reputation: eggi will become famous soon enough

Solved Threads: 47

eggi

Offline

Posting Whiz

Re: Issues emailing data from unix/sql script

Mar 27th, 2008

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:

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
#!/bin/bash
 
cat <<EOF > OUTPUT
 
go
 
EOF
echo hi
#!/bin/bash

cat <<EOF > OUTPUT

go

EOF
echo hi

Help with Code Tags

Shell Scripting Syntax (Toggle Plain Text)

-bash-3.2$ ./eof
hi

Help with Code Tags

Shell Scripting Syntax (Toggle Plain Text)

-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:

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
db_rawamisql $env $component <<EOF > $file
db_rawamisql $env $component <<EOF > $file

To:

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
db_rawamisql $env $component <<-EOF > $file
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

•

Join Date: Aug 2007

Posts: 34

Reputation: skelly16 is an unknown quantity at this point

Solved Threads: 0

skelly16

Offline

Light Poster

Re: Issues emailing data from unix/sql script

Mar 27th, 2008

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

•

Join Date: Oct 2007

Posts: 399

Reputation: eggi will become famous soon enough

Solved Threads: 47

eggi

Offline

Posting Whiz

Re: Issues emailing data from unix/sql script

Mar 27th, 2008

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:

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
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
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

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
(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
(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

•

Join Date: Oct 2007

Posts: 399

Reputation: eggi will become famous soon enough

Solved Threads: 47

eggi

Offline

Posting Whiz

Re: Issues emailing data from unix/sql script

Mar 27th, 2008

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

•

Join Date: Aug 2007

Posts: 34

Reputation: skelly16 is an unknown quantity at this point

Solved Threads: 0

skelly16

Offline

Light Poster

Re: Issues emailing data from unix/sql script

Mar 31st, 2008

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???

•

Join Date: Oct 2007

Posts: 399

Reputation: eggi will become famous soon enough

Solved Threads: 47

eggi

Offline

Posting Whiz

Re: Issues emailing data from unix/sql script

#10

Mar 31st, 2008

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:

Help with Code Tags

Shell Scripting Syntax (Toggle Plain Text)

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

 Help with Code Tags 
 Shell Scripting Syntax (Toggle Plain Text) 
host # cat OUTPUT
hi
there # how are ya
host #
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"

Anyway, great news

, Mike