3

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 exists just 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 ; afterwards

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
  • Removed if not exists
  • Removed backquotes around entity names
  • Inserted values() instead of select even 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?

2
  • delimiter is a construct processed by the client SquirrelSQL (or at least should be). pulling down the MySQL-5.5 pdf (groan) shows this version doesn't include the 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.
    – danblack
    Oct 24 '20 at 1:01
  • updateing it to the last subversion mst times helps see dbfiddle.uk/…
    – nbk
    Oct 24 '20 at 10:14
0

When I gave up on creating the stored procedure through SquirrelSQL and installed MySQL GUI Tools, then I was able to create the procedure with the exact same source, by using Catalogs, selecting my schema, switching to the Stored procedures tab, and clicking Create Stored Proc button at the very bottom.

1
  • Sounds like the UIs are causing more confusion then help.
    – Rick James
    Jan 21 at 20:41
0

Check the manual for the correct syntax.

1064 is pointing exactly at "if not exists"; this syntax is not available in MySQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.