« 44 Days of Uptime on 12" PowerBook | Main | Another Cool thing about Open Source Projects »

November 5, 2004

At Home with MySQL Stored Procedures

It's been almost 4 years since I left the Oracle-backed Jenzabar. I didn't realize until this past week, when I started playing with MySQL 5.0.1, how much I enjoyed all the PL/SQL stored-procs I wrote for my project there.

It started this past week as I was working on an "experimental" Linux installation of our code (we're mainly Solaris). I decided to push the experimental nature of the project by installing MySQL 5.0.1 and just seeing what issues arose.

I haven't gotten to the point where there's any real data on the box just yet, but I have found that in the evenings I've been lured to playing with stored procedures, and have gotten fairly excited about calling up knowledge I haven't used for some time. Found myself thinking of the old job and the cool stuff we did in the database to simplify the interaction. Also am thinking about the Perl and PHP I've written over the years that could have (and will) utilized this.

My simple proc for tonight allows you to call with a login name and the procedure will create the entry if it doesn't exist (yea, kind of a hokey example):

DELIMITER $$

DROP PROCEDURE IF EXISTS `shopping`.`checkUserLogin`$$
CREATE PROCEDURE `shopping`.`checkUserLogin` (IN in_login varchar(20))
BEGIN
declare existing_id integer;
select id into existing_id from user where login=in_login limit 1;
IF existing_id THEN
select existing_id;
ELSE
insert into user set login=in_login;
select id from user where login = in_login;
END IF;

END$$

DELIMITER ;

Stored procedures in MySQL aren't there just yet (5.0.1 is an alpha release). It might make sense for a user to have permission to "call" a stored proc on a database they don't have permission to "use." It doesn't seem to make sense that they can drop the procedure. Will have to go poking around the bug database and see what's been submitted.

Posted by mike at November 5, 2004 7:21 PM