Ad:

Shell Scripting Discussion Thread
Marked Solved
Views: 2967

Mar 25th, 2008

Issues emailing data from unix/sql script

Expand Post »

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:

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.

skelly16

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

34 posts
since Aug 2007

Permalink

Mar 26th, 2008

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

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

eggi

Reputation Points: 102

Solved Threads: 47

Posting Whiz

Offline

399 posts
since Oct 2007

Permalink

Mar 26th, 2008

Re: Issues emailing data from unix/sql script

no need to use cat

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

ghostdog74

Reputation Points: 75

Solved Threads: 44

Junior Poster

Offline

156 posts
since Apr 2006

Permalink

Mar 26th, 2008

Re: Issues emailing data from unix/sql script

Click to Expand / Collapse Quote 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
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

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

skelly16

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

34 posts
since Aug 2007

Permalink

Mar 27th, 2008

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:

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

cat <<EOF > OUTPUT

go

EOF
echo hi

Shell Scripting Syntax (Toggle Plain Text)

-bash-3.2$ ./eof
hi

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:

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

To:

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

eggi

Reputation Points: 102

Solved Threads: 47

Posting Whiz

Offline

399 posts
since Oct 2007

Permalink

Mar 27th, 2008

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

skelly16

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

34 posts
since Aug 2007

Permalink

Mar 27th, 2008

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:

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

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

eggi

Reputation Points: 102

Solved Threads: 47

Posting Whiz

Offline

399 posts
since Oct 2007

Permalink

Mar 27th, 2008

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

eggi

Reputation Points: 102

Solved Threads: 47

Posting Whiz

Offline

399 posts
since Oct 2007

Permalink

Mar 31st, 2008

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

skelly16

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

34 posts
since Aug 2007

Permalink

Mar 31st, 2008

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:

Shell Scripting Syntax (Toggle Plain Text)

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

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

eggi

Reputation Points: 102

Solved Threads: 47

Posting Whiz

Offline

399 posts
since Oct 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Shell Scripting Forum Timeline: Help needed in converting natural numbers to bit

Next Thread in Shell Scripting Forum Timeline: Need help in converting multiple text files into excel worksheets in a workbook

DaniWeb Message

Cancel Changes

User Name
Password