Solved

Help with emailing MYSQL query results from bash script

Posted on 2014-12-16
9
827 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Using 'screen' for session sharing, The Simple Edition Step 1: user starts session with command: screen Step 2: other user (logged in with same user account) connects with command: screen -x Done. Both users are connected to the same CLI sessio…
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
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.:
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

734 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