Learning MySQL 5 Stored Procedures

I recently have been trying to pick up Stored Procedure in MySQL 5. It has been pretty straight forward to learn if you already have done any programming before. Just a matter of familiarizing yourself with the syntax.

If you are learning Stored Procedure from scratch just like me, here are a few gotchas I’ve found:

• DECLARE statements *must* come at the top of each procedure.
• Each procedure is tied to a database. Therefore, you must specify the database you want to create procedures for by executing USE database_name before you start working. The same goes with calling the procedures: you either execute USE database_name or call the procedure by prefixing it with the database name (ie, CALL database.procedure()).
• To those who use replication, yes, stored procedures get replicated to the slaves as well.
• Avoid using variable names that are actually SQL keywords such as DATE. I haven’t read any documentation that explicitly says not to use the keywords, but I did run into errors when I tried to use one.
• MySQLi extension is required to call stored procedures from PHP. This is a big consideration you need to account for if you work on shared hosts that do not have MySQLi (which in turns need PHP 5 and MySQL 5). You will get this error “PROCEDURE XXX can’t return a result set in the given context” if you attempt to execute a stored procedure with the good ol’ “mysql” extension.

Of course, I have some tips for you as well:

• You can use any text editor to write the procedures, but MySQL’s own Query Browser actually works pretty well as an editor for stored procedures. Once a procedure is crated, you can see it by expanding the database tree on the right. Try it.
• To dynamically generate SQL statements in procedures, you will need to concatenate different parts of the statement together and send it in as an Prepared Statement.

I hope this blog can save headaches for some of you. By the way, I ran into problems while re-compiling PHP for MySQLi support. I will provide my solution soon so stay tuned.

Marijuana

Why is marijuana outlawed while liquors aren’t? We all know how messed up people get when they are liquored up. We have heard of all kinds of incidents/accidents due to alcohol in our blood stream but how many of you have heard of marijuana causing the same kinds of problems? Wouldn’t you think marijuana is a “safer” way to get “high”? However, at the end of the day, everyone (21+) can waltz into a local Safeway and take home a 6-pack while possessing marijuana would get you in jail. Can someone tell me again why the government thinks marijuana is more harmful than alcohol? Someone told me marijuana was outlawed due to racial discrimination which I hope is not true.