I am trying to create a stored procedure in MySQL 5.5.8. using connector 8.0.13. Once this procedure is debugged, it will have to be embedded into a c#.net application, to be created on the fly every time the program is set up. For now, I am doing it manually.
For someone like me, with MS SQL, Oracle, and DB2 background, this is challenging. I honestly read MySQL dev docs and googled to get a hang of MySQL SQL syntax. What I gather is that:
- I have to change delimiter to something like
- I should use
if not existsjust in case
- Parameters do not start with
@, and they have to be parenthesized
- I end the block with the temporary delimiter
//and restore it to
This is what I arrived at:
delimiter // create procedure if not exists `logging`.`logEntry`( in hostName varchar(512) ,in entryDateTime datetime ,in entryText varchar(1024) ,out return_value int ) begin insert into `logging`.`log` (hostName, entryDateTime, entryText) select hostName, entryDateTime, entryText; set return_value = last_insert_id(); select return_value; end// delimiter ;
When I run the above code in SquirrelSQL, the server throws the following error:
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if not exists loging.logEntry`( in hostName varchar(512) ,in entryDat' at line 1 SQLState: 42000 ErrorCode: 1064
Here is what I tried:
- Ran without DELIMITER
if not exists
- Removed backquotes around entity names
selecteven though the standalone insert works
I know that this is something totally obvious for a seasoned MySQL developer, but it escapes someone like me with a different background. Am I using any functionality that does not exist in 5.5.8 or are there bugs in my code?
if not exists. Using something like 5.5.8 is so earlly in the release cycle I wouldn't trust it, bump to latest 5.5 at least (which is still out of support). Its a very Oracle thing to wrap basic things in stored procedures, question if you really need to.