[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Help with emailing MYSQL query results from bash script

Posted on 2014-12-16
9
Medium Priority
?
992 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 1900 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 100 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
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.
Suggested Courses

650 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