Mysql code, keep getting syntax error

Question

0.00/5 (No votes)

See more:

Hey guys,
I'm a beginner at MySQL, the task is to write a procudure that increases value of 'egysegar' where the 'alapanyag.nev' contains the input ('alapag'). I keep getting a 'Syntax error' message. I just keep checking examples on different websites, but can't see the problem. Can anybody help me? Thank you.

DELIMITER //
CREATE PROCEDURE emel (IN alapag VARCHAR(45))
DECLARE aaa VARCHAR(45);
DECLARE notFound CONDITION FOR
	SQLSTATE '02000';
DECLARE c CURSOR FOR (SELECT nev FROM alapanyag);
BEGIN
	OPEN c;
    thisLoop:LOOP
        FETCH c INTO aaa;
        IF notFound THEN
            LEAVE thisLoop;
        END IF;
        IF aaa LIKE alapag THEN
            UPDATE alapanyag;
            SET egysegar=egysegar*1.5;
        END IF;
    END LOOP;
    CLOSE c;
END //
DELIMITER ;

The Error message is:

Error Code: 1064
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 aa VARCHAR(45)' at line 2

MySQL Server Version: 10.4.8-MariaDB

What I have tried:

Keep checking websites. I use phpMyAdmin if it helps.
Also added Delimiter to the code (edited), and removing DECLARE as someone suggested on another site.

Posted 7-Apr-20 11:28am

m_smith

Updated 7-Apr-20 21:47pm

v5

Add a Solution

Comments

Patrice T 7-Apr-20 17:41pm

Exact error message please
Use Improve question to update your question.

m_smith 7-Apr-20 18:03pm

Added to the end. Thanks for your efforts.

2 solutions

Solution 2

Check this out: mysql - Mariadb syntax error 1064 (42000) - Stack Overflow[^]

There are two possible solutions:

Answer #1

Copy&pasting your code into NotePad++ and then viewing it in the hex editor shows that you have 80 8b 0a in each of those empty lines between the statements.

That byte sequence is the UTF-8 encoded form of the zero-width space character.

See to it that you remove those – then it should work.

(If you’re using NotePad++ and the hex editor plugin, then in hex view you can simply replace e2 80 8b with an empty string. Otherwise, in any other text editor, going to the end of the previous line, selecting everything from there over the empty line until the beginning of the next line, and then replacing the selection by pressing enter should also work.)

Answer #2:

I had a similar frustration and discovered I needed to change my delimiter around my procedure. Perhaps it'll work here too.

SQL
DELIMITER //
CREATE PROCEDURE
....
BEGIN
END //
DELIMITER ;

This have been writen in the documentation under the link[^] you provided in the comment to the solution #1 by MadMyche.

Posted 7-Apr-20 19:46pm

Maciej Los

Updated 7-Apr-20 19:49pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

m_smith · Accepted Answer · 2020-04-07T21:47:00

Ok, thank you guys for your efforts, it seems like I found the solution. Apparently DECLARE has to be in the block (BEGIN-END), and also had to change the way to handle SQLSTATE '02000'.

DELIMITER //
CREATE PROCEDURE emel (IN alapag VARCHAR(45))
BEGIN
	DECLARE aaa VARCHAR(45);
    DECLARE atEnd SMALLINT DEFAULT 0;
    DECLARE notFound CONDITION FOR SQLSTATE '02000';
	DECLARE c CURSOR FOR (SELECT nev FROM alapanyag);
    DECLARE CONTINUE HANDLER FOR notFound            
      SET atEnd = 1;
	OPEN c;
    thisLoop:LOOP
        FETCH c INTO aaa;
        IF atEnd<>0 THEN
            LEAVE thisLoop;
        END IF;
        IF aaa LIKE alapag THEN
            UPDATE alapanyag
            SET egysegar=egysegar*1.5;
        END IF;
    END LOOP;
    CLOSE c;
END //
DELIMITER ;