Where did my comments go?

Following on from post about the MySQL forums, I answered a question this morning which might be of use to others. It relates to comments in MySQL Stored Routines (SQL/PSM).

I suppose a quick mention of how to write comments in SQL/PSM would be usefull. There are two types of comments in SQL/PSM firstly there is a comment characteristic value we can use to store general comments about the routine, we will look at that later, then there are in-line comments, thesw will be similar to comments you may have used in other procedural languages or programming in general.

The first is a single line comment, when ever you wish to simply show a single line comment you can use the following syntax

-- This is a comment. 

MySQL will ignore anything after the -- when it compiles the SQL/PSM code. The second type of in-line comment is the /* combination. This can be used to make comments on multiple lines of code like so.

/* This is the first line of the commnet 
   and this the second line. */

MySQL will ignore any text between the /* and */, you must make sure that if you use multiple line comments that a closing */ is present. It's up to you which style you use, personally I tend to use -- as it's a habit I picked up years ago, I also tend to add ++ after the first comment character to denote the start of a section of comments, but thats very much a style choice on my point and doesn't offer anything interms of compilation.

 --++ This is the first line of the commnet 
 --   and this the second line

OK so we know how to write comments in SQL/PSM now on to the problem. Let's create a very simple SQL/PSM procedure.

create procedure CommentTest() 
begin 
   -- this is a comment
   select 'Comment Test';
end;
//
Query OK, 0 rows affected (0.00 sec)

Now here comes the issue, we can issue a command to view the SQL/PSM code stored in the database like so.

mysql> show create procedure commenttest//
+-------------+------------------------------------+
| Procedure   | Create Procedure                   |
+-------------+------------------------------------+
| commenttest | CREATE PROCEDURE `commenttest`()   |
|             | begin                              |
|             |                                    |
|             |   select 'Comment Test';           |
|             | end                                |
+-------------+------------------------------------+
1 row in set (0.00 sec)

The SQL/PSM code is shown but there are no comments, this can also be seen if you are
using the MySQL Administrator, when you click the "Edit Stored Proc" button the code is shown, but no comments are displayed.

Where have they gone? well mysql removes any inline comments when the procedure is loaded into the database. This isn't such a problem for somebody like me who creates scripts to create procedures as I have the comments retained in the script, but if you create your procedures in something like MySQL administrator then those comments will be gone for good.

Unfortunatley there isn't anything that can be done about this at present. The comments have passed on! They are no more! They have ceased to be! they have expired and gone to meet 'their maker! Bereft of life, they rest in peace! their metabolic processes are now 'istory! off the twig! they kicked the bucket, they shuffled off 'their mortal coil, run down the curtain and joined the bleedin' choir invisibile!! THEY ARE EX-COMMENTs!!