How to fix MySQL CREATE FUNCTION query?

Question

I want to add mysql function:

CREATE FUNCTION `chf_get_translation`(translation_id INT, site_lang INT) 
RETURNS text CHARSET utf8
BEGIN

  DECLARE translation TEXT;

  SELECT title 
    INTO translation 
    FROM chf_translations 
   WHERE key_id = translation_id 
     AND lang_id = site_lang;

  RETURN translation;

END

But get error:

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 '' at line 3

Ed I · Accepted Answer · 2011-11-16 22:58:00Z

5

Try...

DELIMITER $$

CREATE FUNCTION `chf_get_translation`(translation_id INT, site_lang INT) 
RETURNS text CHARSET utf8
BEGIN

  DECLARE translation TEXT;

  SELECT title 
    INTO translation 
    FROM chf_translations 
   WHERE key_id = translation_id 
     AND lang_id = site_lang;

  RETURN translation;

END$$

edited Nov 16, 2011 at 22:58

Ed I

7,4183 gold badges47 silver badges52 bronze badges

answered Jun 3, 2010 at 15:39

Gary

2,9161 gold badge18 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Liutas Over a year ago

This helps thanks; But now i get error: ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

Liutas Over a year ago

I found answer. I need "READS SQL DATA" after "RETURNS text CHARSET utf8"

Cosmin · Accepted Answer · 2011-12-29 13:33:24Z

2

DELIMITER $

CREATE FUNCTION `chf_get_translation`(translation_id INT, site_lang INT) 
RETURNS text CHARSET utf8
DETERMINISTIC
BEGIN

  DECLARE translation TEXT;

  SELECT title 
    INTO translation 
    FROM chf_translations 
   WHERE key_id = translation_id 
     AND lang_id = site_lang;

  RETURN translation;

END$

edited Dec 29, 2011 at 13:33

Cosmin

21.5k5 gold badges48 silver badges61 bronze badges

answered Dec 29, 2011 at 13:27

Rahul

211 bronze badge

Collectives™ on Stack Overflow

How to fix MySQL CREATE FUNCTION query?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related