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

Nice! I didn't know about that before - http://sqlfiddle.com/ — benedict_w, Nov 01 '12 at 08:37
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, Nov 01 '12 at 09: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, Nov 01 '12 at 10:04

score 3 · Accepted Answer · edited Aug 29 '13 at 18:31

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 '13 at 18:31

fthiella

48,073
15
90
106

answered Nov 01 '12 at 08:35

ajacian81

7,419
9
51
64

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: ` – Tschallacka Nov 01 '12 at 08:42
3

It worked in HeidiSQL and MySQL command line. In SQL fiddle, try changing the query delimiter (the fourth button below the DDL box). – Salman A Nov 01 '12 at 08:52
Can you try the suggestion in your app and not SQL fiddle: http://stackoverflow.com/questions/12166380/execute-stored-procedure-on-sqlfiddle – ajacian81 Nov 01 '12 at 08:52

mysql syntax error, but where?

1 Answers1