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?

YES !!
Many thanks. That fixed it.

Yes, I removed the change of delimiter and reverted to ; instead of $$ in the SQL script that defines the stored procedure.

It isn't a nice solution because it means the SQL code is not directly portable between projects which use the C API and projects which use other alternatives. At least it is working though.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.