Help with emailing MYSQL query results from bash script

OS:  CENTOS 7
MYSQL 5.6.22

Hi,

   I have a MYSQL select statement that I would like to use with a bash script and then have it email the results.  I need to have the results in the body of the message.

MYSQL CODE:

select distinct se.src_aet as "Ripped By", s.created_time as "Date/Time Sent", p.pat_name as "Patient Name", p.pat_id as "Patient ID", s.accession_no as "ACC #", p.pat_birthdate as "DOB", s.mods_in_study as "MOD", s.study_datetime as "Study Date", s.study_desc as "Study Desc", s.study_custom1 as "Inst Name"
from patient p
INNER JOIN study s
on p.pk = s.patient_fk
INNER JOIN series se
on s.pk = se.study_fk
where  s.accession_no like '%OUT%'
and s.created_time >= curdate()
order by s.created_time DESC

Open in new window


  There are an awful lot of ' " ' in the select statement and I was having a lot of issues with some of the bash scripts I found as example while trying to implement them.  Not sure if it was because of the ' " ' or not.  Maybe I could save this code to a .sql file and call it from the script?

I currently use 'mailx' to send mail in CENTOS

Ultimately I'll be wanting to run this as a cron job.

thanks
doc_jayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
Wazzup doc?  (Don't answer that question.)

Try this:

#!/bin/bash

`mysql --host=MyHostname --user=MyUsername --password=MyPassword --database=MyDatabase -e \
"SELECT DISTINCT se.src_aet AS 'Ripped By', s.created_time AS 'Date/Time Sent', p.pat_name AS 'Patient Name', p.pat_id AS 'Patient ID', s.accession_no AS 'ACC #', p.pat_birthdate AS 'DOB', s.mods_in_study AS 'MOD', s.study_datetime AS 'Study Date', s.study_desc AS 'Study Desc', s.study_custom1 AS 'Inst Name'
FROM patient p
INNER JOIN study s ON p.pk = s.patient_fk
INNER JOIN series se ON s.pk = se.study_fk
WHERE s.accession_no LIKE '%OUT%'
AND s.created_time >= curdate()
ORDER BY s.created_time DESC" >report.txt`

cat report.txt | mailx -s "Here's the SQL Report" me@mydomain.com

Open in new window


Parts of this was sourced from: www.e-e.com/Q_26746084.htm#a34617485

I expect the output will be tab delimitted.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
doc_jayAuthor Commented:
tel2,

   Wazzzzzuppppp?  Thanks for your reply.  I tried your script above and filled in my info for 'mysql'. It generates a 'report.txt' but its empty.  I do get an email, but it is empty of course.

I get the following error when I take away the report function:

./query_test.sh: line 3: unexpected EOF while looking for matching ``'

Open in new window

0
MikeOM_DBACommented:
Those are back-ticks and unnecessary for the solution:
mysql --host=MyHostname --user=MyUsername --password=MyPassword --database=MyDatabase -e \
"SELECT DISTINCT se.src_aet AS 'Ripped By', s.created_time AS 'Date/Time Sent', p.pat_name AS 'Patient Name', p.pat_id AS 'Patient ID', s.accession_no AS 'ACC #', p.pat_birthdate AS 'DOB', s.mods_in_study AS 'MOD', s.study_datetime AS 'Study Date', s.study_desc AS 'Study Desc', s.study_custom1 AS 'Inst Name'
FROM patient p
INNER JOIN study s ON p.pk = s.patient_fk
INNER JOIN series se ON s.pk = se.study_fk
WHERE s.accession_no LIKE '%OUT%'
AND s.created_time >= curdate()
ORDER BY s.created_time DESC" >report.txt

cat report.txt | mailx -s "Here's the SQL Report" me@mydomain.com

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

doc_jayAuthor Commented:
Great, that worked out - thank you!  Part of my problem as well was that I was copying a different select statement that contianed ' " ' in it, which obviously it didn't like.
0
doc_jayAuthor Commented:
Great work!
0
tel2Commented:
Hi doc,

Mike is correct that the `back ticks` are unnecessary, and I agree that they should definitely be removed.  They came with the code that I linked to, and I didn't think to remove them.  However, I don't see how they could possibly have had anything to do with the problem you last reported.  They worked fine for me.  Sounds as if your problem was to do with the quotes from the different select statement that you mentioned.  If you want to prove that, try putting them back in temporarily.

Also, I don't think that your 250/250 split of points gives an appropriate spread of credit, especially in the light of my comments above about the `back ticks` not being the issue.  Please explain how you decided to split the points in this way.

BTW, you can do the emailing without "cat" like this:
    mailx -s "Here's the SQL Report" me@mydomain.com <report.txt

Or if you really don't want/need the output to go to a file, you can email it directly like this:
#!/bin/bash

mysql --host=MyHostname --user=MyUsername --password=MyPassword --database=MyDatabase -e \
"SELECT DISTINCT se.src_aet AS 'Ripped By', s.created_time AS 'Date/Time Sent', p.pat_name AS 'Patient Name', p.pat_id AS 'Patient ID', s.accession_no AS 'ACC #', p.pat_birthdate AS 'DOB', s.mods_in_study AS 'MOD', s.study_datetime AS 'Study Date', s.study_desc AS 'Study Desc', s.study_custom1 AS 'Inst Name'
FROM patient p
INNER JOIN study s ON p.pk = s.patient_fk
INNER JOIN series se ON s.pk = se.study_fk
WHERE s.accession_no LIKE '%OUT%'
AND s.created_time >= curdate()
ORDER BY s.created_time DESC" \
| mailx -s "Here's the SQL Report" me@mydomain.com

Open in new window

But for trouble-shooting etc it could be quite good to have the output in a file.
0
tel2Commented:
Hi doc_jay,

Please respond to the underlined question in my last post.

Thanks.
tel2
0
doc_jayAuthor Commented:
tel2,
 
   I apologize, you should have gotten the majority of points for this question.  I won't be so haphazard the next time I award points in this manner.  Everyone here on EE is very knowledgeable in their field of expertise and I'm very thankful for all the help that I have gotten so far from everyone.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.