Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

Automatically emailing results of a SQL query

Hello,

I'd like to set up a cron job in unix that would periodically query my MySQL database and email the results to me. So far this is what I've got but it doesnt work...

My unix script contains:
mysql -u<username> -p<password> dbname < batchfile > temp;
mail me@mycompany.com < temp;

My batch file contains:
Select * from tbljobs where jobs_end_date < CURDATE ();

The above statement finds all the expired job postings in the database. Im sure the sql statemtent is correct as I've run it several times.

Do I need to fix/refine this? or is there a better way to do it that will work?

RS
0
rsajoo
Asked:
rsajoo
  • 6
  • 6
1 Solution
 
ajaikumarrCommented:
Hai,

Try this.

mysql -u<username> -p<password> -D dbname < batchfile > temp;

Bye
Ajai
0
 
rsajooAuthor Commented:
Ok that worked thanks....How would I append a customized message to these results and add a subject line to my email?

So I would to do something like:

The following is a list of expired jobs:

<results of query goes here>.

Please let me know.

0
 
ajaikumarrCommented:
Hai,

Try something like this.

mysql -u<username> -p<password> -D dbname < batchfile > temp;
echo "The following is a list of expired jobs:\n" > temp1
cat temp > temp1
mail me@mycompany.com < temp1;

Bye
Ajai
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
ajaikumarrCommented:
Hai,

Sorry Like this

mysql -u<username> -p<password> -D dbname < batchfile > temp;
echo "The following is a list of expired jobs:\n" > temp1
cat temp >> temp1
mail me@mycompany.com < temp1;

Bye
Ajai
0
 
rsajooAuthor Commented:
Hey,

Thanks for your reply! That works just fine. However, I am trying to minimize the number of temp/batch files on the server. How would I modify the script so that I can run the sql stmt instead of feeding the batch file into the mysql server. So far I've got the following but it doesnt work:

mysql -u<username> -p<password> -D dbname < "Select * from tbljobs where jobs_end_date < CURDATE ();"  > temp
echo "The following is a list of expired jobs:\n" > temp1
cat temp >> temp1
mail me@mycompany.com < temp1;

Please let me know.

Thanks.

RS
0
 
ajaikumarrCommented:
Hai,

Better idea would be create temporary files for mail generation asa mailed the files we can remove them.

Try This,

echo Select * from tbljobs where jobs_end_date < CURDATE (); > sql
mysql -u<username> -p<password> -D dbname < sql  > temp
echo "The following is a list of expired jobs:\n" > temp1
cat temp >> temp1
mail me@mycompany.com < temp1;

rm sql
rm temp
rm temp1

Bye
Ajai
0
 
rsajooAuthor Commented:
Hey Ajai,

Do I need to to enclose the sql stmt in quotation marks?

Rahim
0
 
ajaikumarrCommented:
Hai Rahim,

Nope. Try this on shell prompt

echo Select * from tbljobs where jobs_end_date < CURDATE (); > sql
cat sql

if you see the entire select statement then there is no need to add quotes.

Bye
Ajai
0
 
rsajooAuthor Commented:
Ok. Thanks Ajai, It works just fine. Only problem is that the results of the sql query does not append the email msg. RAther, its appears before it. So I've got the following:

job_id      job_title          job_org_name
28      Webmaster    Microsoft

Hello,

This is a notification that the following job postings have have been expired.

Anyway to fix this? perhaps a way to pause until the sql command has been executed?

Rahim
 
0
 
ajaikumarrCommented:
Try this,

echo Select * from tbljobs where jobs_end_date < CURDATE (); > sql
echo "Hello,\n\n" > temp1
echo "The following is a list of expired jobs:\n\n" >> temp1
mysql -u<username> -p<password> -D dbname < sql  >> temp1
mail me@mycompany.com < temp1;

rm sql
rm temp1

Bye
Ajai
0
 
rsajooAuthor Commented:
I got the following error:
temp1: No such file or directory

I swithced the files and still I got that error. Any other options?

RS
0
 
rsajooAuthor Commented:
Ignore my last message. It works just fine!!! I just created a new file and through contents of this script in there. FYI: I had to enclose teh sql stmt in quotes as it kept giving me errors.

Thanks once again,
RS
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now