March 28, 2006
MySQL 5.0 Stored Procedures
MySQL 5.0 comes with some really cool additions like stored procedures, functions and views.
I was experimenting with writing a procedure at work and, this is what i came up with.
DELIMITER $$;
DROP PROCEDURE IF EXISTS `vms_database`.`FixSafety`$$
CREATE PROCEDURE `FixSafety`()
BEGIN
DECLARE id, b int ;
DECLARE serial_no1 varchar(255) ;
DECLARE track_id varchar(255) ;
DECLARE cur_1 CURSOR FOR SELECT MAX(SAFETY_VALVE_REPAIR_ID) AS id , SERIAL_NO , SAFETY_VALVE_EQUIPMENT_TRACK_ID AS track_id FROM safety_valve_repair WHERE SERIAL_NO IS NOT NULL AND trim(SERIAL_NO)!=‘’ GROUP BY SERIAL_NO;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1 ;
OPEN cur_1 ;
REPEAT
FETCH cur_1 INTO id, serial_no1, track_id ;
UPDATE safety_valve_repair SET SAFETY_VALVE_EQUIPMENT_TRACK_ID=id,MOST_RECENT_FLAG=‘N’ WHERE SERIAL_NO = serial_no1 AND SAFETY_VALVE_REPAIR_ID!=id ;
UPDATE safety_valve_repair SET MOST_RECENT_FLAG=‘Y’ WHERE SAFETY_VALVE_REPAIR_ID = id ;
UNTIL b = 1
END REPEAT ;
CLOSE cur_1 ;
END$$
DELIMITER ;$$
DROP PROCEDURE IF EXISTS `vms_database`.`FixSafety`$$
CREATE PROCEDURE `FixSafety`()
BEGIN
DECLARE id, b int ;
DECLARE serial_no1 varchar(255) ;
DECLARE track_id varchar(255) ;
DECLARE cur_1 CURSOR FOR SELECT MAX(SAFETY_VALVE_REPAIR_ID) AS id , SERIAL_NO , SAFETY_VALVE_EQUIPMENT_TRACK_ID AS track_id FROM safety_valve_repair WHERE SERIAL_NO IS NOT NULL AND trim(SERIAL_NO)!=‘’ GROUP BY SERIAL_NO;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1 ;
OPEN cur_1 ;
REPEAT
FETCH cur_1 INTO id, serial_no1, track_id ;
UPDATE safety_valve_repair SET SAFETY_VALVE_EQUIPMENT_TRACK_ID=id,MOST_RECENT_FLAG=‘N’ WHERE SERIAL_NO = serial_no1 AND SAFETY_VALVE_REPAIR_ID!=id ;
UPDATE safety_valve_repair SET MOST_RECENT_FLAG=‘Y’ WHERE SAFETY_VALVE_REPAIR_ID = id ;
UNTIL b = 1
END REPEAT ;
CLOSE cur_1 ;
END$$
DELIMITER ;$$
The procedure that i wrote was 2-3 times faster, than a piece of java code that, i wrote to perform the same operation.
Eventhough i still don’t like much of how procedures are written in MySQL 5.0, its still a good start.
. He showed us some of the workings of Seam, that got me hooked into it. And i am still experimenting with a lot of interesting stuff in it, not to mention JBoss jBPM which i had no idea about untill i knew about JBoss Seam. EJB3 uses Hibernate for persistance so you got best of both worlds :p. Maybe after a while i might just post some code for download regrading Seam. Eventhough Seam is still in beta its still a handfull. So check it out at 
, i had to go search for a plugin that would make my life a bit easier. Codesnippet is a great plugin to get syntax highlighting done for your code. Similarly Editormonkey replaces the default WYSIWYG editor in Wordpress 2.x with a much more advanced WYSIWYG editor with choices to either use 
Comments(2)
