CODEWITHSUNDEEP
mysqlfunctionsyntax-error
Dave
Dave

Reputation: 19340

Getting error when tryhing to create this MySQL function

I'm using MySQL 5.5.37. I'm trying to create a MySQL function like so ...

CREATE FUNCTION join_book(text VARCHAR) RETURNS text CHARSET utf8 
AS  'SELECT DISTINCT e.subject_id FROM category c, resource_category rc, product_resource pr, product p, product_ebook pe, ebook e WHERE c.id = rc.category_id AND rc.resource_id = pr.resource_id AND pr.product_id = p.id AND p.id = pe.product_id AND pe.ebook_id = e.id AND c.id = $1'  
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

but I'm getting the error

ERROR 1064 (42000) at line 1: 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 ') RETURNS text CHARSET utf8 
AS  'SELECT DISTINCT e.subject_id FROM categ' at line 1

Not sure what htis means. Grateful for any help.

Edit: I changed the function to be the following ...

CREATE FUNCTION join_book(text VARCHAR) RETURNS text CHARSET utf8 
AS  'SELECT DISTINCT e.subject_id FROM category c, resource_category rc, product_resource pr, product p, product_ebook pe, ebook e WHERE c.id = rc.category_id AND rc.resource_id = pr.resource_id AND pr.product_id = p.id AND p.id = pe.product_id AND pe.ebook_id = e.id AND c.id = $1'  
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

but got the error

ERROR 1064 (42000) at line 1: 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 'AS  'SELECT DISTINCT e.subject_id FROM category c, resource_category rc, s' at line 2

Upvotes: 1

Views: 52

Answers (1)

Carson Moore
Carson Moore

Reputation: 1287

You need to specify the VARCHAR size -- it's expecting (text VARCHAR(N)). Generally, MySQL error text starts right where it sees the error; in this case, it's saying "I was expecting something other than ') RETURNS'" -- specifically, it was expecting ")) RETURNS".

Upvotes: 2

Related Questions