mysql syntax error, but where?

Question

I'm following some tutorials on mysql function creation but I keep getting the following error.

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 5:

 CREATE FUNCTION getstatisticscount (h VARCHAR(35),d date)
  RETURNS INT
  DETERMINISTIC
   BEGIN
    DECLARE tel INT;
    SELECT count(hash) into tel from statsitieken where hash=h and lastvisit between concat(d,' 00:00:00') and concat(d,' 23:59:59') group by hash;  
    RETURN tel;
   END;

I can for my life not find where line 5 is, but no matter which line I put it on, I keep getting this error. If I remove this function from the sql fiddle code it's all fine. I can't find what's wrong with it... except maybe flawed tutorials.

http://sqlfiddle.com/#!2/70f0a

Is it still not working for you? Where are you trying to execute these statements, in the console, in an application, or in a web or desktop based MySQL client? — ajacian81
– ajacian81, Commented Nov 1, 2012 at 9:26
Mysql version throws an error in the end that must be solved by my host. Column count of mysql.proc is wrong. Expected 20, found 16. The table is probably corrupted; — Tschallacka
– Tschallacka, Commented Nov 1, 2012 at 10:04

fthiella · Accepted Answer · 2013-08-29 18:31:49Z

3

Use Delimiter

delimiter //
CREATE FUNCTION getstatistics(h VARCHAR(35),d date)
  RETURNS INT
  DETERMINISTIC
   BEGIN
    DECLARE tel INT;
    SELECT count(hash) INTO tel
    FROM statistics
    WHERE
      hash=h
      AND lastvisit BETWEEN concat(d,' 00:00:00') AND concat(d,' 23:59:59')
    GROUP BY hash;  
    RETURN tel;
   END
//

DELIMITER ;

For more info: http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defining.html

Edit: basically since your stored procedure separates out statements with semicolons (;) and the method that defines your procedure ALSO uses semicolons to separate out statements, it's hard to impossible for MySQL to figure out where your procedure begins and ends. I also edited the SQL statement above to return the delimiter back to the default semicolon.

edited Aug 29, 2013 at 18:31

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

answered Nov 1, 2012 at 8:35

ajacian81

7,6299 gold badges54 silver badges66 bronze badges

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

3 Comments

Tschallacka Over a year ago

I'd wish it would work but it returns

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 'delimeter // CREATE FUNCTION getstatisticscount (h VARCHAR(35),d date)   RETURNS' at line 1:

Salman Arshad Over a year ago

It worked in HeidiSQL and MySQL command line. In SQL fiddle, try changing the query delimiter (the fourth button below the DDL box).

ajacian81 Over a year ago

Can you try the suggestion in your app and not SQL fiddle: stackoverflow.com/questions/12166380/…

Collectives™ on Stack Overflow

mysql syntax error, but where?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related