Solved

Help with emailing MYSQL query results from bash script

Posted on 2014-12-16
9
784 Views
Last Modified: 2015-01-07
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
0
Comment
Question by:doc_jay
  • 4
  • 3
9 Comments
 
LVL 12

Accepted Solution

by:
tel2 earned 475 total points
ID: 40503704
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
 

Author Comment

by:doc_jay
ID: 40504600
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
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 25 total points
ID: 40504777
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:doc_jay
ID: 40504912
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
 

Author Comment

by:doc_jay
ID: 40504913
Great work!
0
 
LVL 12

Expert Comment

by:tel2
ID: 40505666
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
 
LVL 12

Expert Comment

by:tel2
ID: 40530666
Hi doc_jay,

Please respond to the underlined question in my last post.

Thanks.
tel2
0
 

Author Comment

by:doc_jay
ID: 40530691
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the last ten+ years I have seen Linux configuration tools come and go. In the early days there was the tried-and-true, all-powerful linuxconf that many thought would remain the one and only Linux configuration tool until the end of times. Well,…
Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question