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](http://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=7cf721858e42e62dee14a3d173a6187c). — wchiquito, Nov 02 '17 at 12:04

score 3 · Accepted Answer · answered Nov 02 '17 at 16:39

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; //

score 0 · Answer 2 · answered Nov 01 '17 at 20:26

0

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

answered Nov 01 '17 at 20:26

Artem Ilchenko

1,050
9
20

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'... – Doro Nov 02 '17 at 05:26

score 0 · Answer 3 · answered Nov 07 '17 at 14:57

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.

TheOligarch · Answer 4 · 2021-11-26T16:21:08.443

-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 '21 at 16:21

answered Nov 01 '17 at 20:26

TheOligarch

101
3

It shows another error: "...check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IS'". – Doro Nov 02 '17 at 05:27

Mysterious error in CREATE PROCEDURE in MariaDB/MySQL

4 Answers4

Linked