CODEWITHSUNDEEP
mysqlstored-procedures
user3355182
user3355182

Reputation: 151

Mysql insert from stored procedure gives error 1064

For some strange reason, inserting from stored procedure is not working. This is what Im trying to do:

DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS  test(
id                                          INT(9)                  NOT NULL AUTO_INCREMENT
,name                                       VARCHAR(30)     NOT NULL 
,PRIMARY KEY (id)

) DEFAULT CHARSET=utf8; insert into test (name) values('A');

Inserting from command line works with no problems.

Then I created a stored procedure to do the same kind of insert:

DROP PROCEDURE IF EXISTS storedtest;
DELIMITER $$
CREATE PROCEDURE storedtest()
    BEGIN
        declare insert_sql varchar(200);
        SET insert_sql = 'insert into test (name) values(3)';
        SELECT insert_sql;
        PREPARE mystm FROM @insert_sql;
        EXECUTE mystm;
   END$$
DELIMITER ;

call storedtest();

This gives me the error: ERROR 1064 (42000): 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 'NULL' at line 1

NULL? Where did NULL came from?

I also tried changing the sql-insert to look like this (dont know if it is a good way):

    SET insert_sql = "insert into test (name) values('3')";

But mysql gives me exactly the same error. Anyone has a clue?

Upvotes: 1

Views: 762

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270767

The NULL MySQL is reporting is an empty user variable @insert_sql, which is different from the local stored procedure local variable insert_sql which you allocated with DECLARE.

MySQL's DECLARE is used for variables local to a stored program, but according to the documentation, PREPARE stmt FROM ... expects either a string literal or a user variable, which are the type preceded with @.

PREPARE stmt_name FROM preparable_stmt

preparable_stmt is either a string literal or a user variable that contains the text of the SQL statement.

You can allocate the untyped user variable with SET so there is no need for DECLARE. You may wish to set it to NULL when you're finished.

DROP PROCEDURE IF EXISTS storedtest;
DELIMITER $$
CREATE PROCEDURE storedtest()
BEGIN
  -- Just SET the user variable
  SET @insert_sql = 'insert into test (name) VALUES (3)';
  SELECT @insert_sql;

  -- Prepare & execute
  PREPARE mystm FROM @insert_sql;
  EXECUTE mystm;
  
  -- Deallocate the statement and set the var to NULL
  DEALLOCATE PREPARE mystm;
  SET @insert_sql = NULL;
END$$
DELIMITER ;

Upvotes: 1

Related Questions