rsajoo
asked on
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
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
ASKER
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.
So I would to do something like:
The following is a list of expired jobs:
<results of query goes here>.
Please let me know.
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
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
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
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
ASKER
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
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
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
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
ASKER
Hey Ajai,
Do I need to to enclose the sql stmt in quotation marks?
Rahim
Do I need to to enclose the sql stmt in quotation marks?
Rahim
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
temp1: No such file or directory
I swithced the files and still I got that error. Any other options?
RS
ASKER
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
Thanks once again,
RS
Try this.
mysql -u<username> -p<password> -D dbname < batchfile > temp;
Bye
Ajai