26 Sep 2013
When it comes to databases, I've tried to keep myself in the know about all the tools, features and best-practices even if I never utilise them. It's useful to be aware of the things you have yet to learn so that should you ever come accross a new problem your ability to think outside of the box is improved by an incling of an idea.
Anyhow, this week I've finally needed to use MySQL PROCEDURES and TRIGGERS to help compliment some of the more complex queries we need to run (the details of which are beyond the scope of this wee blog post). And I quickly came about an issue regarding PARAMETERS that has had me scratching my head for some time.
Lets start with a simple SQL query, you want to increment a column for a specific row:
UPDATE `mytable` SET `mycolumn` = `mycolumn` + 1 WHERE `id` = 1234;
Then you want to make your query more dynamic by utilising User-Defined Variables:
...maybe you are using prepared statements to similar effect...
SET @myid = 1234; UPDATE `mytable` SET `mycolumn` = `mycolumn` + 1 WHERE `id` = @myid;
Finally you decide create a PROCEDURE that you can call like a function:
DELIMITER $$ DROP PROCEDURE IF EXISTS `MyProcedure` $$ CREATE PROCEDURE MyProcedure(IN myid INT) BEGIN UPDATE `mytable` SET `mycolumn` = `mycolumn` + 1 WHERE `id` = myid; END $$ DELIMITER ;
That procedure is then stored within the database and can be used by any connection later:
Works a treat. But to get to that functional PROCEDURE I took a massive, confusing detour...
If you look at the 3 statements above you will notice:
The following PROCEDURE uses BOTH @variables and paramenters:
DELIMITER $$ DROP PROCEDURE IF EXISTS `MyProcedure` $$ CREATE PROCEDURE MyProcedure(IN myvar VARCHAR(255)) BEGIN SELECT myvar, @myvar ; END $$ DELIMITER ; -- SET @myvar then call the procedure SET @myvar = 'variable'; CALL MyProcedure('parameter'); -- +----------+-----------+ -- | myvar | @myvar | -- +----------+-----------+ -- | variable | parameter | -- +----------+-----------+
You can see that the PROCEDURE has access to both the User-Defined variables AND the parameters. Very confusing.
Lessons learned? MySQL PROCEDURES have access to Session scope. I don't know why, but I wouldn't have expected that.