I am using the MySQL C API and having problems defining a stored procedure. MY SQL script works fine in MySQL Workbench but the same script fails in my own software. A test script which shows the problem is:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; CREATE SCHEMA IF NOT EXISTS `Junk` ; USE `Junk` ; DROP procedure IF EXISTS `sp_test`; DELIMITER $$ USE `Junk` $$ CREATE PROCEDURE `sp_test` (IN param1 tinyint, IN param2 varchar(10), OUT NewID INT) BEGIN INSERT INTO test (field1 , field2) VALUES(param1, param2) ; SET NewID = LAST_INSERT_ID() ; END $$ DELIMITER ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
The error message I get is:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER $$ CREATE PROCEDURE 'sp_test' (IN param1 tinyi' at line 1.
I have studied the docs and tried several variations on the above theme, but I am stuck. Perhaps I have been staring at this for too long to see an obvious problem. Any suggestions?