Shell Programming and Scripting

Hi,

I am trying to generate list of employees from emp table who joined yesterday.

emp.sh

Code:

YEST=$(date --date='1 day ago' +%Y-%m-%d)
cat emp.sql | mysql -u <user> -p<pass> -h <host> -P <port>  -D <dbname> > emp.csv

emp.sql

Code:

select * from employee where join_date = '$YEST';

I expected that when i do cat emp.sql in emp.sh, value of YEST will get replaced with 2011-02-15. But the query that is getting passed to mysql is select * from employee where join_date = '$YEST';

I can do sed to replace variable with value in sql file and then run the query. But is there any other easy way of doing it. Why cat done in emp.sh is not replacing the value?

cat dosn't expand environment variables, and even if it did you haven't exported YEST. Best to use sed in this instance.

If you do a bit of this sort of thing and need to replace multiple enviornment variables in template scripts have a look at expand.sh (post #5) in this thread

cat takes the contents of a file and prints them to stdout. That's it. It doesn't care if there are any shell variables in there. It doesn't even know what they would look like. The only program that, by default, interprets shell variables is the shell itself.

And you can get the same result using

Code:

mysql -u <user> -p <pass> -h <host> -P <port> -D <dbname> 'SELECT * FROM employee WHERE join_date = DATE_SUB( DATE(NOW()), INTERVAL "-1 DAY" );'

See "Date and Time Functions" in the MySQL Reference Manual.

Thanks for clarification. I will use sed.