Solved

Help with emailing MYSQL query results from bash script

Posted on 2014-12-16
9
712 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
 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Little introduction about CP: CP is a command on linux that use to copy files and folder from one location to another location. Example usage of CP as follow: cp /myfoder /pathto/destination/folder/ cp abc.tar.gz /pathto/destination/folder/ab…
Utilizing an array to gracefully append to a list of EmailAddresses
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.:
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now