MySQL Syntax error

Question

I am unable to run a simple script in MySQL. I have reduced the script to just one line.

DELIMITER $$
DECLARE varLocalityName VARCHAR(50);
$$
DELIMITER ;

The error is:

ERROR 1064 (42000) at line 2: 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 'DECLARE varLocalityName VARCHAR(50)' at line 1

$ mysql --version
mysql  Ver 14.14 Distrib 5.1.63, for debian-linux-gnu (x86_64) using readline 6.2

what are you trying to do? `stored procedure`? functions? or what? — John Woo, Oct 22 '12 at 08:42
It is just a block of statements I am trying to execute. It is a part of a stored procedure I wrote. — Sameer, Oct 22 '12 at 08:44
@John I do not want to create a separate stored proc, because I will have to drop it. I am just trying to see the output from a block of statements. Is it possible? — Sameer, Oct 22 '12 at 08:49

score 1 · Accepted Answer · answered Oct 22 '12 at 13:49

Your code block does not define the scope for the declared variables. If within a procedure, they must be between BEGIN and END. Without them, the statement DECLARE varLocalityName VARCHAR(50); becomes an invalid statement to be executed. This statement is equivalent to the statements shown below:

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2012-10-22     |
+----------------+
1 row in set (0.00 sec)

mysql> declare varLocalityName varchar(50);
ERROR 1064 (42000): 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 'declare varLocalityName varchar(50)' at line 1
mysql>

Either you should declare session variables without keyword DECLARE or follow a syntax defined for a stored procedure to use scoped variables.

Example 1: Using session variables:

mysql> set @x = null;
Query OK, 0 rows affected (0.00 sec)
mysql> select @x;
+------+
| @x   |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> select current_date() into @x;
    -> $$
Query OK, 1 row affected (0.02 sec)

mysql> select @x;
    -> $$
+------------+
| @x         |
+------------+
| 2012-10-22 |
+------------+
1 row in set (0.00 sec)

Note that you can set/define session variables within a procedure but not DECLARE.

Example 2: Using procedure scoped variables:

mysql>
mysql> delimiter $$
mysql> create procedure some_x()
    ->    begin
    ->      declare varLocalityName varchar(50);
    ->
    ->      set @sessionDate = null;
    ->      select @sessionDate;
    ->      set @sessionDate = current_date();
    ->      select @sessionDate;
    ->
    ->      select varLocalityName;
    ->    end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql>
mysql> call some_x();
+--------------+
| @sessionDate |
+--------------+
| NULL         |
+--------------+
1 row in set (0.00 sec)

+--------------+
| @sessionDate |
+--------------+
| 2012-10-22   |
+--------------+
1 row in set (0.00 sec)

+-----------------+
| varLocalityName |
+-----------------+
| NULL            |
+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> select @sessionDate;
+--------------+
| @sessionDate |
+--------------+
| 2012-10-22   |
+--------------+
1 row in set (0.00 sec)

mysql> select varLocalityName;
ERROR 1054 (42S22): Unknown column 'varLocalityName' in 'field list'
mysql>

Also refer to Variables declaration and scope.

Thanks for your explanation. I am able to run a code block using session scope variables in mysql shell. However, same code does not work in Mysql Query Browser. It fails with error 1064. Any idea about that? — Sameer, Oct 22 '12 at 14:37
DELIMITER $$ set @x = null; select current_date() into @x; select @x; $$ DELIMITER ; — Sameer, Oct 22 '12 at 14:44
No. `delimiter` is only useful when you have more than a single statement to be executed. And these statements shall be in blocks defined in procedures or triggers. Your above statements failed just because of improper use of `delimiter`. — Ravinder Reddy, Oct 22 '12 at 14:59
@Sameer Also see more detailed explanation at [`mySQL Delimiters`](http://technikhil.wordpress.com/2007/03/13/nikhil-in-mysql-land-2-2/). — Ravinder Reddy, Oct 22 '12 at 15:00
I will accept this as an answer because it solves my problem. It is still not clear to me why the last code block does not work in QueryBrowser but works in mysql shell. As you mentioned, I am using delimiter for more than one statement. — Sameer, Oct 22 '12 at 17:15

user1581900 · Answer 2 · 2012-10-22T08:50:55.073

0

I think the problem is that you define $$ as delimiter and then you still try to use ; as the delimiter

I believe this should work:

DELIMITER $$
DECLARE varLocalityName VARCHAR(50)$$
DELIMITER ;

As default MySQL delimiter is ;. You can change it with DELIMITER call and you must use it in the new syntax from then on.

DELIMITER $$ 
DECLARE varLocalityName VARCHAR(50)$$ 
SET varLocalityName="xx"$$ 
SELECT varLocalityName$$ 
DELIMITER ;

So basically $$ replaces every occurrence of ; (as long as it is not inside the String). If you set DELIMITER **, you would use double stars instead of ;.

edited Oct 22 '12 at 08:50

answered Oct 22 '12 at 08:43

user1581900

3,680
4
18
21

This works. However, if I add more statements in the block, I start getting the error. e.g. DELIMITER $$ DECLARE varLocalityName VARCHAR(50); SET varLocalityName="xx"; SELECT varLocalityName; $$ DELIMITER ; – Sameer Oct 22 '12 at 08:47

MySQL Syntax error

2 Answers2

Linked