List:General Discussion« Previous MessageNext Message »
From:Machiel Richards Date:January 5 2011 10:00am
Subject:Stored procedure
View as plain text  
Good day all

                I am hoping that someone can assist me here.

                As per a client requirement, I am writing a
script/stored procedure combination in order to do the following:

                        - Script to be run within a cron once a day
according to a set schedule.
                        - script to connect to mysql and call a stored
procedure
                        - stored to procedure to do the following:

                                    *   retrieve row id of the record
that indicates the last record of a specified date (i.e 00:00 yesterday)
                                                [select max(id) into
max_id from table1 where utc < dt]

                                      * delete records from table2 where
id < max_id
                                       * delete records from table1
where id < max_id

                        After a struggle to get the script and stored
procedure working I am now stuck at the following point.

                        the date that needs to be specified to the
stored procedure must be in the following format:
                                        
                                                        2011-01-04 00:00
(i.e. yesterday 00:00) meaning that everything before this date and time
needs to be deleted.

                            However when trying to run the script with
the date like this, then I get the following message:


                                    ERROR 1064 (42000) at line 1: You
have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '00:00)' at
line 1
                                                         

                                I initially had the script create the
date in a different manner but then the 00:00 was seen as a seperate
argument which didn't work. After some changes the date is now being
read correctly from what I can tell but now I get the message above.


                    Herewith my script and stored procedure definitions:



Script:

#!/bin/bash

DATE="`date --date="1 days ago" +%Y-%m-%d` 00:00"
echo "$DATE"

mysqldump -u root -p<password> --databases <DB>
> /backups/DB_backup.dump

mysql -u root -p<password> -D <DB> -e "call select_delete_id_2($DATE)"

exit
 



Stored Proc:


begin declare max_id int(11); select max(id) into max_id from table1
where utc < dt; delete from table2 where id < max_id; delete from table1
where id < max_id; end


            Does anybody perhaps have any suggestions?

Regards
Machiel

Thread
Stored procedureMachiel Richards5 Jan
  • Re: Stored procedurepetya5 Jan
    • Re: Stored procedureMachiel Richards5 Jan
      • Re: Stored procedurepetya5 Jan
        • Re: Stored procedureMachiel Richards6 Jan