Mysql Create Function

[The Mask]

Hi

There is a problem with SQLyog whereby functions with a routine_body can not be created.

Example:

CREATE FUNCTION hello (s CHAR(20))

RETURNS CHAR(50)

RETURN CONCAT('Hello, ',s,'!')

The above works fine but if you enclose any statements in a routine body, an error occurs.

CREATE FUNCTION hello (s CHAR(20))

RETURNS CHAR(50)

BEGIN

RETURN CONCAT('Hello, ',s,'!')

END;

…gives: Error Code : 1064

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 'RETURN CONCAT('Hello, ',s,'!')

END' at line 4

(0 ms taken)

Any ideas please?

[ronjeremy_69]

The Mask wrote on May 24 2006, 01:04 PM:

Hi

There is a problem with SQLyog whereby functions with a routine_body can not be created.

Example:

CREATE FUNCTION hello (s CHAR(20))

RETURNS CHAR(50)

RETURN CONCAT('Hello, ',s,'!')

The above works fine but if you enclose any statements in a routine body, an error occurs.

CREATE FUNCTION hello (s CHAR(20))

RETURNS CHAR(50)

BEGIN

RETURN CONCAT('Hello, ',s,'!')

END;

…gives: Error Code : 1064

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 'RETURN CONCAT('Hello, ',s,'!')

END' at line 4

(0 ms taken)

Any ideas please?

This is not an issue with SQLYog! I tried your case in mysql's standard command line program and got the exact same result.

[peterlaursen]

You will need to use the DELIMITER syntax – with SQLyog and cmd-line as well.

With SQLyog the syntax is for instance

DELIMITER $$;

CREATE FUNCTION hello (s CHAR(20))

RETURNS CHAR(50)

BEGIN

RETURN CONCAT('Hello, ',s,'!');

END $$

DELIMITER ;$$

Note that 'DELIMITER' is not an SQL statement and is not understood by the server – it is implemented in the client only, and no 'DELIMITER ..' will ever reach the server (and if it does it raises an error).

From version 5.0 SQLyog supports DELIMITER

'DELIMITER …' changes the DELIMITER to be used between SQL-statements so that ';' can be used inside the ROUTINE.

[Ritesh]

Always use the template that is provided by SQLyog for CREATE/ALTER 5.x objects. Just change the value whereever required.

[Author]

Posts