SQL error on existing stored proc

Hi Folks,

My first post so please be gentle.

I have a mariaDB stored procedure on my database. I can open it with HeidiSQL and all is well.

I can drop the procedure no problem.

However, if I try to recreate the procedure I get an SQL error...
/* SQL Error (1064): 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 20 */

I have made no changes to the SQL so I'm struggling to understand.

The lines involved are:

-- -----------------------------------------------------
-- Declare statements
-- -----------------------------------------------------
DECLARE v_sql_command TEXT;
DECLARE v_logging_level INT;

-- -----------------------------------------------------
-- End of declares
-- -----------------------------------------------------

It's definitely complaining about the v_sql_command declaration.

Any ideas?

Do I need to change a setting somewhere? This is a MariaDB database and the same SQL runs fine in Valentina Studio.

Cheers, Graham.

I can create a procedure with these lines successfully, without errors. Also, modifying the lines afterwards and saving the procedure runs fine. I'm on MySQL 5.7.9 but I guess MariaDB behaves the same. So, what's the whole CREATE code for your procedure?

Hi,

Here's the script.

As I said, this runs fine in Valentina and the procedure runs on the database OK.

DROP PROCEDURE IF EXISTS roverLogging;
CREATE PROCEDURE roverLogging(IN in_level INT, in_process TEXT, in_core_system TEXT, in_log_details TEXT)
BEGIN
-- -----------------------------------------------------------------------------------------------------
-- (C) Red Hound Limited 2015
-- -----------------------------------------------------------------------------------------------------
--
-- Title ROVER - Logging
--
-- DB: MARIADB
--
-- Purpose Controls the writing of procedures
--
-- Version V0.1 - Initial build
--
-- -----------------------------------------------------------------------------------------------------
-- TEST
-- -----------------------------------------------------
-- Declare statements
-- -----------------------------------------------------
DECLARE v_sql_command TEXT;
DECLARE v_logging_level INT;

-- -----------------------------------------------------
-- End of declares
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Retrieve the logging level - Default to full logging until control table is implemented
-- -----------------------------------------------------
SET v_logging_level = 5;

-- -----------------------------------------------------
-- Write to the log
-- -----------------------------------------------------
IF in_level <= v_logging_level THEN

SET v_sql_command = '';
SET v_sql_command = concat(v_sql_command,'INSERT INTO logging (date_time, process, core_system, log_details) ');
SET v_sql_command = concat(v_sql_command,' VALUES (now() ');
SET v_sql_command = concat(v_sql_command,' ,''',in_process,'''');
SET v_sql_command = concat(v_sql_command,' ,''',in_core_system,'''');
SET v_sql_command = concat(v_sql_command,' ,''',in_log_details,''')');

SET @s = v_sql_command;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;

-- -----------------------------------------------------------------------------------------------------
END;
-- -----------------------------------------------------------------------------------------------------

Cheers, Graham

Even with that script I get no errors from MySQL in HeidiSQL. When exactly do you get that error?