CODEWITHSUNDEEP
mysqlstored-proceduresmariadb
Doro
Doro

Reputation: 355

Mysterious error in CREATE PROCEDURE in MariaDB/MySQL

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;

Upvotes: 2

Views: 2999

Answers (4)

TheOligarch
TheOligarch

Reputation: 101

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;

Upvotes: -1

Rick James
Rick James

Reputation: 142296

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.

Upvotes: 0

Doro
Doro

Reputation: 355

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

Upvotes: 3

Artem Ilchenko
Artem Ilchenko

Reputation: 1035

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

Upvotes: 0

Related Questions