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
– John Woo, Commented Oct 22, 2012 at 8:42
It is just a block of statements I am trying to execute. It is a part of a stored procedure I wrote. — Sameer
– Sameer, Commented Oct 22, 2012 at 8: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
– Sameer, Commented Oct 22, 2012 at 8:49

Ravinder Reddy · Accepted Answer · 2012-10-22 13:49:35Z

1

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.

answered Oct 22, 2012 at 13:49

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

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

5 Comments

Sameer Over a year ago

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 Over a year ago

DELIMITER $$ set @x = null; select current_date() into @x; select @x; $$ DELIMITER ;

Ravinder Reddy Over a year ago

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 Over a year ago

@Sameer Also see more detailed explanation at mySQL Delimiters.

Sameer Over a year ago

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.

user1581900 · Accepted Answer · 2012-10-22 08:50:55Z

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, 2012 at 8:50

answered Oct 22, 2012 at 8:43

user1581900

3,7704 gold badges20 silver badges21 bronze badges

1 Comment

Sameer Over a year ago

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 ;

Collectives™ on Stack Overflow

MySQL Syntax error

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related