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

score 5 · Accepted Answer · edited Nov 16 '11 at 22:58

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 '11 at 22:58

Ed I

7,008
3
41
50

answered Jun 03 '10 at 15:39

Gary

2,866
1
17
20

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 Jun 15 '10 at 08:44
5

I found answer. I need "READS SQL DATA" after "RETURNS text CHARSET utf8" – Liutas Jun 15 '10 at 08:55

score 2 · Answer 2 · edited Dec 29 '11 at 13:33

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 '11 at 13:33

Cosmin

21,216
5
45
60

answered Dec 29 '11 at 13:27

Rahul

21
1

How to fix MySQL CREATE FUNCTION query?

2 Answers2