CODEWITHSUNDEEP
mysql
Brenton Thomas
Brenton Thomas

Reputation: 638

mysql stored function syntax for dummies

I am appear to be having a bad case of brain flatulence at the moment. Can someone see the bleedingly obvious error in the following mysql stored function?

delimiter $$

drop function if exists test$$ 

create function test() 
returns boolean
begin
    return true;
end$$

I know it is dancing around the place, laughing its head off and waving its bare buttocks in my direction - but I just cant see it.

The original error was in a much larger hunk of code that I have cut down to the above - but I can't seem to remove much more without running out of code to remove. mysql version 5.7.14

Upvotes: 0

Views: 57

Answers (2)

Brenton Thomas
Brenton Thomas

Reputation: 638

For anyone who comes this way again the problem turned out not to be with the SQL. The issue was with the driver and my understanding of how mysql hangs together.

The delimiter statement is part of the MySQL client (command line) and is not part of any driver (or at least any on node i can find)

So if you are loading your sql by sucking in a string and squirting it at the database via a driver (nodejs mariasql in my case) then delimiter is not understood.

You either have to parse the delimiters out yourself - or be more elaborate and logon to the mysql client shell somehow and load the file. I will probably simply put a line such as %start sql stuff here %end and parse the sql into chunks - and squirt these at the driver.

Upvotes: 1

RiggsFolly
RiggsFolly

Reputation: 94682

Drop the function before setting the new delimiter

DROP function IF EXISTS `test`;

DELIMITER $$
USE `A_DB`$$
CREATE FUNCTION `test` ()
RETURNS BOOLEAN
BEGIN
    RETURN TRUE;
END$$

DELIMITER ;

Upvotes: 2

Related Questions