Shell Programming and Scripting

We are running a quiz and the data collected from the quiz is submitted to the database. My requirement is to write a shell script to get these submitted records.

I should be able to run this shell script at any time and the records it returns should be the ones submitted after the script was last run.

For eg : Assume i ran a script today and the number of records returned are 19, if i ran this script again tomorrow, the records i shd get should be the one submitted after today (excluding 19 records).

i will prefer using a variable in shell script which will hold the last run time.

for eg :
In my shell script can i declare a variable (last_run_time) in the begining, Use this variable thru the code and before exiting the shell script update this variable with the current date

.... shell script start .....
last_run_time=16/03/2005 10:10:10
..
spool test
select * from table1 where creattion_date > $last_run_time;
spool off
..

****
Here i wanted to update the last_run_time variable (declared above) with the current date, so that the next time i run the script i shd get the records which are inserted after i last ran the query
****
.... shell script end .....


Regards

You can try as,

date >> /tmp/sqllogfile
mysql -q -h <host> -p <passwd> <<EOF
use <database>;
select COUNT(*) from <table>;
\q;
EOF >> /tmp/sqllogfile
echo >> /tmp/sqllogfile

It will log every information with row count in /tmp/sqllogfile file. Automate this script with cron.

HTH.
EOF

Thanks Muthu for the reply...


I repeat my question :
Is it possible for a shell script to modify itself ??


In my shell script can i declare a variable (last_run_time) in the begining, Use this variable thru the code and before exiting the shell script update this variable with the current date

.... shell script start .....
last_run_time=16/03/2005 10:10:10
..
spool test
select * from table1 where creattion_date > $last_run_time;
spool off
..

****
Here i wanted to update the last_run_time variable (declared above) with the current date, so that the next time i run the script i shd get the records which are inserted after i last ran the query
****
.... shell script end .....

Regards

why is it necessary to modify the script?
this is a PPP.

just put the date in a file, date>RunTimeFile, at the end of each run and read it the next time you run the script, e.g.:
last_run_time=$(cat RunTimeFile)

this assumes that there is never more than 1 instance of the script running.

I have done something similar in a college AI class where we looked at the efficiency of different languages for AI. I wrote a script that replaced the file it was run from with new code, then ran the new script. Once the scirpt is running you can blow away it's file on disk since it is running from a copy in memory. I think you could pursue two methods to get the results you want.

1. In the script create a procedure that contains the entire text of the script itself and echo's it to $0 (this is the name of the file that was run in the first place). Then just run the procedure and have another section of code spit the new answers into the file as well.

2. Use a unique comment on the end of the lines containing the answers. Use sed to add/remove/modify lines in this section with the new answers from within the script.

This will work but using an external text file is probably a better way to go.

That is true only for very small scripts. Most scripts will blow up if you try that.