FruitBatInShades Code

I spent all weekend trying to convert a Sql Server db to a MySql v5 db and found it quite a frustrating experience!  When you’re sytnax is incorrect MySql helpfully says ‘You have an error in your sql syntax near:’ and error 1064. No help with which command causes the error and the position it picks for the start of the problem is usually wrong.  God we’ve been spoilt by Sql Server.

Here are the first problems I found and got past:

delimiter command

To create multiple commands you need to delimit the lines with a semi colon. Problem is that MySql sees the first semi colon as the end of the script so we need to set the delimiter to something else with the delimiter [char] before the Create Procedure command.

delimiter |
CREATE PROCEDURE Name(params)
BEGIN
    SELECT * FROM ...;
    UPDATE ...;
END|
delimiter ; --set it back again 

You can use any character or combination of characters so you can avoid any occurances of characters in the Sql Statement.

If – End If

If and End if need to have their closing tags and lines closed with a semi colon, no BEGIN..END

IF Exists(SELECT * FROM ...) THEN
    SELECT * FROM ...;
ELSE
    SELECT * FROM ...;
END IF;

SCOPE_IDENITY

SET @UniqueID = SCOPE_IDENTITY();

becomes

SET `@UniqueID` = LAST_INSERT_ID();

Parameters

My DataAccessLayer uses the traditional @ParameterName convention for parameters but to get them to work (so the dal can use the same code to call either MSSql or MySql)  in MySql we need to use a containing character which is `, `@ParameterName`.

Parameter direction goes in front of the param and IN is default

INOUT `@ParameterName` bigint
OUT `@ParameterName` varchar(50)
`@ParameterName` bigint -- defaulted as IN 

ALTER PROCEDURE

Alter procedure doesn’t seem to work like MsSql’s. It seems to change attributes but does not allow you actually edit the proc! You need to drop and then re-create a procedure to change its content! Will have to look into this…