Mysterious error in CREATE PROCEDURE in MariaDB/MySQL

Question

I tried to make a simple procedure in MariaDB 10.2 but I encountered an issue regarding variables defining.

I am receiving (conn:107) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3 message when I declare a variable.

I read the MariaDB documentation and I it says that a variable is defined like this DECLARE var_name [, var_name] ... type [DEFAULT value]

Where I am wrong? I am coming from Oracle SQL and some sintax is wired for me.

I use Eclipse with MariaDB JDBC to connect on SQL.

CREATE PROCEDURE nom_jobs_insert(IN p_name varchar(100) CHARACTER SET 'utf8')
BEGIN
    DECLARE counter INT DEFAULT 0;

    SELECT count(*) INTO counter
    FROM nom_jobs
    WHERE lower(name) = lower(p_name)

    IF counter = 1 THEN
        INSERT INTO nom_jobs(name) VALUES (p_name);
    END IF;
END;

I can't reproduce the problem, see dbfiddle.

wchiquito
– wchiquito

2017-11-02 12:04:17 +00:00
Commented Nov 2, 2017 at 12:04 — wchiquito
– wchiquito, Commented Nov 2, 2017 at 12:04

Doro · Accepted Answer · 2017-11-02 16:39:22Z

3

I found the solution.

In MariaDB you have to define a delimiter before create a procedure and you need to mark where the procedure code is finished.

DELIMITER //
CREATE PROCEDURE nom_jobs_insert(IN p_name varchar(100) CHARACTER SET 'utf8')
BEGIN
    DECLARE counter INT DEFAULT 0;

    SELECT count(*) INTO counter
    FROM nom_jobs
    WHERE lower(name) = lower(p_name);

    IF counter = 1 THEN
        INSERT INTO nom_jobs(name) VALUES (p_name);
    END IF;
END; //

answered Nov 2, 2017 at 16:39

Doro

4552 gold badges7 silver badges25 bronze badges

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

Comments

Artem Ilchenko · Accepted Answer · 2017-11-01 20:26:54Z

0

You have error not in DECLARE expression, add ; after SELECT statement

answered Nov 1, 2017 at 20:26

Artem Ilchenko

1,0259 silver badges20 bronze badges

1 Comment

Doro Over a year ago

It doesn't work. I tried in MySQL Workbench and it tell me at the '0' from declare statement that it missing semicolon even it's a semicolon after '0'...

Rick James · Accepted Answer · 2017-11-07 14:57:46Z

0

Here are the clues that point to a missing DELIMITER:

near '' at line 3
Line 3 contains the first ;
When the error says near '', the parser thinks it has run off the end of the "statement".

Put those together -- it thinks that there is one 3-line statement ending with ;. But the CREATE PROCEDURE should be longer than that.

answered Nov 7, 2017 at 14:57

Rick James

144k15 gold badges144 silver badges255 bronze badges

Comments

TheOligarch · Accepted Answer · 2021-11-26 16:21:08Z

-1

CREATE PROCEDURE nom_jobs_insert(IN p_name varchar(100) CHARACTER SET 'utf8')
IS
DECLARE counter INTEGER DEFAULT 0;
BEGIN
    

    SELECT count(*) INTO counter
    FROM nom_jobs
    WHERE lower(name) = lower(p_name)

    IF counter = 1 THEN
        INSERT INTO nom_jobs(name) VALUES (p_name);
    END IF;
END;

edited Nov 26, 2021 at 16:21

answered Nov 1, 2017 at 20:26

TheOligarch

1013 bronze badges

1 Comment

Doro Over a year ago

It shows another error: "...check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IS'".

Collectives™ on Stack Overflow

Mysterious error in CREATE PROCEDURE in MariaDB/MySQL

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related